It’s the quotes in the data field data that cause the problem.  A Param use
will not help since you cant set the param

"Adam Brett" wrote in message
news:E6112874-8626-4E6E-B2DC-539C7887BC58@news.elevatesoft.com...

Ben

I usually use EDB's PARAMs in this situation as it cleanly copes with all
issues such as single & double quotes which might confuse the compiler:

EDBQuery.SQL.Text:= 'SELECT * FROM sometable WHERE somefield = :Str';
EDBQuery.Prepare;
EDBQuery.ParamByName('Str').asString:= 'Someone''s ';

The point is that if you do it like this the compiler will spot the single
quote when you set the Param, so you can correct it. If it works as a string
in code, EDB will happily run it.

The "Engine.QuotedSQLStr" is producing a double quote.  The data in the data
field has a single quote

"Raul"  wrote in message
news:1B71002E-C8B9-46BA-BEE0-BBA0CD47F98D@news.elevatesoft.com...

On 4/9/2014 10:55 PM, Ben wrote:
> I did some further test utilizing this function and these were the
> results:
>
>    vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value);
>
> Value Input
>      A456 23' 24
>
> Value Output
>      'A456 23'' 24'
>
> This function is not working.  Is there any other way around this.

What do you mwan it's not working ? This is exactly what it's supposed
to do.

Here's an example as requested.

- for this sample i assume the SKU input is from edit field named
"Edit1" but of course it can come from any where (file, another table, etc).
- q1 is a TEDBQuery
- code sample code that queries the table for specific SKU and displays it :

  q1.SQL.Clear;
  q1.SQL.Add('SELECT * FROM Inv WHERE SKU=' +
Engine.QuotedSQLStr(Edit1.Text));
  q1.Open;
  if not q1.Eof then
    ShowMessage( Format('Sku=%s.
Count=%d',[q1.FieldByName('SKU').AsString,q1.FieldByName('Num').AsInteger]));
  q1.Close;


Raul

Ben


I'm getting baffled by what your problem actually is. Can you post a small demo project to the binaries so that we can see what's going on.

Roy Lambert

The question is very simple.  I have a file named "InvMas".  One field in
that file is "SKU".  The value of that field is "A456 23' 24".
The double quotes are NOT part of the field value.  The single quote IS part
of the field value.
If I write a query (exactly as I am writing it)  Select * from Invmas where
SKU = 'A456 23' 24', it will return an error because
of the single quote in the middle of the data string (field Value).
If I use the following code in my program:
vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value);
Q1.SQL.Add(format(Select * from Invmas where SKU = ''%s''',[vSKU];
the query will not return any records because the value of vSKU is 'A456
23'' 24' (note the 2 single quotes in middle of the data).
How do I get around this.

Ben Sprei

"Roy Lambert"  wrote in message
news:02A020CE-4947-4A9C-BE85-CFEC98F2D77F@news.elevatesoft.com...

Ben


I'm getting baffled by what your problem actually is. Can you post a small
demo project to the binaries so that we can see what's going on.

Roy Lambert

Ben,

If the following doest work, then just like Roy, I can't understand what your problem actually is:

Q1.SQL.Add(format('Select * from Invmas where SKU = %s',[vSKU]));


--
Fernando Dias
[Team Elevate]

Ben


>The question is very simple. I have a file named "InvMas". One field in
>that file is "SKU". The value of that field is "A456 23' 24".
>The double quotes are NOT part of the field value. The single quote IS part
>of the field value.
>If I write a query (exactly as I am writing it) Select * from Invmas where
>SKU = 'A456 23' 24', it will return an error because
>of the single quote in the middle of the data string (field Value).
>If I use the following code in my program:
>vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value);
>Q1.SQL.Add(format(Select * from Invmas where SKU = ''%s''',[vSKU];
>the query will not return any records because the value of vSKU is 'A456
>23'' 24' (note the 2 single quotes in middle of the data).
>How do I get around this.

My first suggestion is to shoot whoever came up with that approach. However, I appreciate that that is not very helpful.

What you have to do is double the quotes. QUOTEDSTR is not going to work because it will see one and a half quoted strings and bomb out.

The best approach would be to develop your own function (as I've shown you on the other thread) to walk through the SKU and double any internal quotes. You would then alter your sql to something along the lines of

SELECT * FROM table WHERE SKU = ExtraQuotes(:SKUValue)

And simply supply the value of the SKU field as a parameter

Roy Lambert