Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
V4 problem creating INSERT SQL with single quotes / memos |
Fri, Mar 9 2007 9:25 AM | Permanent Link |
adam | I am really banging my head against a wall at the moment.
I am loving v4 ... faster, better cleaner etc. BUT I have a lot of situations where I do things like: - concatentate: field1 (number) field2 (string) field3 (memo) into 1 long string - insert this long string into a "changelog" table. -- The problem is that I cannot for the life of me get the strings through in formats that work. I repeatedly get "SQL can't run" type messages. Sample data: ProductID : 1 UserName : Mark Recipe: Add product 1 and 2 Mix all the other product's Cook it. -- Note that the Memo field "Recipe" contains carriage returns AND single quotes. Both of which are handled differently in v4 compared to v3. The following: Str := field1.asString + ' ' + Field2.asString + ' ' + Field3.asString Query.SQL.Text := 'INSERT INTO ChangeLog (ChangeMemo) VALUES("'+Str+'")'; Query.ExecSQL; Used to work a DREAM. Now it just doesn't & it doesn't matter how I try I can't get it to!!! I am writing functions to add #10#13 fields to the end of each line in the memo. I am trying various methods of doubling up single quotes to double quotes ... but nothing is working. The most painful thing is that I am often creating Strings in my app which generate an error, BUT which if run in DBSys work perfectly. Adam |
Fri, Mar 9 2007 9:48 AM | Permanent Link |
"Jose Eduardo Helminsky" | Adam
> Str := field1.asString + ' ' + Field2.asString + ' ' + Field3.asString > Query.SQL.Text := 'INSERT INTO ChangeLog (ChangeMemo) VALUES("'+Str+'")'; > Query.ExecSQL; What you need is solved with use of parameters: Str := field1.asString + ' ' + Field2.asString + ' ' + Field3.asString Query.SQL.Text := 'INSERT INTO ChangeLog (ChangeMemo) VALUES ()'; Query.ParamByName('p1).AsBlob := Str; Query.ExecSQL; Eduardo |
Fri, Mar 9 2007 10:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | adam
CRLF pairs have to be coded as #13#10 and (I think, can't remember) be surrounded by quotes. To give the right syntax just reverse engineer a small sample with data. I also recommend the use of QuotedStr rather than knitting your own. Roy Lambert |
Sun, Mar 11 2007 12:01 PM | Permanent Link |
adam | Thanks Edwardo ... Params are a really good idea, as I can see in onther instances DBISAM
handles adding the single quotes in a number of situations within complex strings. I was partly asking as there are times when params are problematic & I hoped there might be an easy way round (aside from using params! ... Adam Brett |
Sat, Mar 17 2007 6:00 PM | Permanent Link |
"Ralf Mimoun" | adam wrote:
> I am really banging my head against a wall at the moment. > > I am loving v4 ... faster, better cleaner etc. BUT > > I have a lot of situations where I do things like: > > - concatentate: field1 (number) field2 (string) field3 (memo) into 1 > long string > > - insert this long string into a "changelog" table. > > -- > > The problem is that I cannot for the life of me get the strings > through in formats that work. I repeatedly get "SQL can't run" type > messages. Take a look at ConvertToLiteral in DBISAMLb. That not only handles quotes, but also CR/LF and other special chars. Imagine what happens if someone calls himself "Joe';DROP TABLE Orders;DROP TABLE Customers" Ralf |
Sun, Mar 18 2007 6:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ralf
Have a go and report back Roy Lambert |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |