Select "problem" Howto

Select "problem" Howto

Post by GTi » Sun, 01 Feb 2004 23:15:23


ODBC Beginner

Normal I use:
sprintf(qu,"SELECT Name, phone FROM PersonTable WHERE info='%s'",sText);
SQLExecDirect(hstmt1,qu,SQL_NTS);

This works fine, except when the sText is:
Test on 'data' field
Then I have the ' in the string.
What else can I use - SQLPrepare?.
Please give me some samples of using this in SQLPrepare.
 
 
 

Select "problem" Howto

Post by Johan Sven » Mon, 02 Feb 2004 03:03:21

The solution is to make sure you never have a single '-character in your
text string. You need to replace it with two single qoute characters instead
(not a double qoute character), because this will then be interpreted as the
text representation of the single qoute instead if the end of string
character.

Some examples:
Text is
Test on 'data' field
replace with
Test on ''data'' field

resulting query
SELECT Name, phone FROM PersonTable WHERE info='Test on ''data'' field'

Text is
This is my 'data'
replace with
This is my ''data''

resulting query
SELECT Name, phone FROM PersonTable WHERE info='This is my ''data'''


Note the existens of three single qoute characters in the second example.
This means that the first two are represting the single qoute character
string and the last one means end of text string.


Regards,
Johan




ODBC Beginner

Normal I use:
sprintf(qu,"SELECT Name, phone FROM PersonTable WHERE info='%s'",sText);
SQLExecDirect(hstmt1,qu,SQL_NTS);

This works fine, except when the sText is:
Test on 'data' field
Then I have the ' in the string.
What else can I use - SQLPrepare?.
Please give me some samples of using this in SQLPrepare.

 
 
 

Select "problem" Howto

Post by GTi » Mon, 02 Feb 2004 20:59:12

So, for this to work I have to add a single qoute character
to all single qoute characters in the string (...!)
Then the driver will remove the two single qoute character
with one single qoute character.

OK..?

"Johan Svensson" <hej(dot)johan(at)telia(dot) XXXX@XXXXX.COM > wrote in

instead
the
field'
 
 
 

Select "problem" Howto

Post by Johan Sven » Mon, 02 Feb 2004 23:11:37

Hi again,

Your conclusion in the first sentence is correct.

About your second comment, I do not know exactly where the transalation
takes place, but it is most likely not any network interface driver, more
likely it is in the query parser in the database itself. But once again, I
do not know this for sure, but I do know this is the only way you can get it
to work...


Regards,
Johan




So, for this to work I have to add a single qoute character
to all single qoute characters in the string (...!)
Then the driver will remove the two single qoute character
with one single qoute character.

OK..?

"Johan Svensson" <hej(dot)johan(at)telia(dot) XXXX@XXXXX.COM > wrote in

instead
the
field'