The SKU field value is 4563 25' 90
select * from InvMas where sku = '4563 25' 90'

if I run the above statement I will get an error.  Is there any workaround
???

On 4/8/2014 3:38 PM, Ben wrote:
> The SKU field value is 4563 25' 90
> select * from InvMas where sku = '4563 25' 90'
>
> if I run the above statement I will get an error.  Is there any workaround
> ???
>
>

select * from name where name='4563 25'' 90'

see manual for "literals" :
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=String_Types

Raul

That would be fine if I would enter manually.  However during runtime I have
the program get the string from a data file and incorporate into a sql
string.  Automatically a single apostrophie will appear

"Raul" <raul@removethis.raul.ca> wrote in message
news:FD496FF9-2C2F-497C-9712-6DA0C244C1CC@news.elevatesoft.com...
> On 4/8/2014 3:38 PM, Ben wrote:
>> The SKU field value is 4563 25' 90
>> select * from InvMas where sku = '4563 25' 90'
>>
>> if I run the above statement I will get an error.  Is there any
>> workaround
>> ???
>>
>>
>
> select * from name where name='4563 25'' 90'
>
> see manual for "literals" :
> http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=String_Types
>
> Raul

On 4/8/2014 7:35 PM, Ben wrote:
> That would be fine if I would enter manually.  However during runtime I have
> the program get the string from a data file and incorporate into a sql
> string.  Automatically a single apostrophie will appear
>

That's not what your original question asked though solution is still
the same.

Just use the QuotedSQLStr engine function for your literal strings when
building the SQL:

http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=rsdelphiwin32&version=XE5&comp=TDBISAMEngine&method=QuotedSQLStr

Raul

Thanks for your reply.
Can you give me a usage example, please.  Whatever I try errors out

"Raul" <raul@removethis.raul.ca> wrote in message
news:D2EBE223-8618-46D3-9F98-DD3BB19105D9@news.elevatesoft.com...
> On 4/8/2014 7:35 PM, Ben wrote:
>> That would be fine if I would enter manually.  However during runtime I
>> have
>> the program get the string from a data file and incorporate into a sql
>> string.  Automatically a single apostrophie will appear
>>
>
> That's not what your original question asked though solution is still the
> same.
>
> Just use the QuotedSQLStr engine function for your literal strings when
> building the SQL:
>
> http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=rsdelphiwin32&version=XE5&comp=TDBISAMEngine&method=QuotedSQLStr
>
> Raul

Ben,

This is not an SQL issue. Sometimes you do have to write some code.
Just scan the input string and add an extra quote character.

--
Fernando Dias
[Team Elevate]

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.


"Ben" <arrow1432@verizon.net> wrote in message
news:21816081-371F-4A51-9CA7-70FD0CBF77F5@news.elevatesoft.com...
> Thanks for your reply.
> Can you give me a usage example, please.  Whatever I try errors out
>
> "Raul" <raul@removethis.raul.ca> wrote in message
> news:D2EBE223-8618-46D3-9F98-DD3BB19105D9@news.elevatesoft.com...
>> On 4/8/2014 7:35 PM, Ben wrote:
>>> That would be fine if I would enter manually.  However during runtime I
>>> have
>>> the program get the string from a data file and incorporate into a sql
>>> string.  Automatically a single apostrophie will appear
>>>
>>
>> That's not what your original question asked though solution is still the
>> same.
>>
>> Just use the QuotedSQLStr engine function for your literal strings when
>> building the SQL:
>>
>> http://www.elevatesoft.com/manual?action=viewmethod&id=dbisam4&product=rsdelphiwin32&version=XE5&comp=TDBISAMEngine&method=QuotedSQLStr
>>
>> Raul
>
>

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


Raul is right in what he's been advising you to do.

<<
 vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value);

Value Input
   A456 23' 24

Value Output
   'A456 23'' 24'
>>

I'm with Raul on this. The results are exactly what I would expect.

Can you post the table structure and the code you're using to construct the sql statement?

Roy Lambert

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.