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

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

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

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

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

Ralf


Have a go and report back

Roy Lambert