Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Sep 19, 2017 3:46 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: A little problem with SQLite and the LIKE statement
PostPosted: Tue May 21, 2013 7:36 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
I'm attempting to do a search based on the user's input but have a little problem with the following SQL statement:

SELECT * FROM Products WHERE DescriptionShort LIKE '%let''s%' OR ProductCode LIKE '%let''s%' AND CompanyID = 1 ORDER BY DescriptionShort ASC ;


When Saving the record to the database I'm escaping the single quote to double quotes so in fact the db is storing the word -let''s- but for some reason the above query does not seem to find anything.

I have run the query in a db manager and of course it doesn't return anything either. I'm a bit confused.

Any ideas on what I'm doing wrong?

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: A little problem with SQLite and the LIKE statement
PostPosted: Tue May 21, 2013 7:41 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Is the data really in the database with 2 quote marks? If so, how did you get it stored that way? If you say

update sometable set somecolumn = 'let''s'

the data will be stored as let's, with a single quote. If you use as prepared statement, then you shouldn't be escaping the quote, as it will be stored verbatim. So if you escape a single quote and then store it via prepared statement, you will get 2 quote marks in the data: let''s. Is that what happened?

If the data really has 2 quote marks (let''s), then you need to escape both of them:

where productcode like 'let''''s'

(4 ' marks = 2 ' marks in the data)


Top
 Profile  
Reply with quote  
 Post subject: Re: A little problem with SQLite and the LIKE statement
PostPosted: Tue May 21, 2013 7:46 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Thanks for your answer Tim. This comes directly from the database, of course it's just dummy data

This is the short description for product 1, Let''s test for single quotes.


That's what's being stored in the database.

I was testing to make sure the search for a word with single quote was working and nope, double quotes seem to be working properly.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: A little problem with SQLite and the LIKE statement
PostPosted: Tue May 21, 2013 7:52 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Thanks Tim, Using 4 quotes did the trick. Don't know why I missed that but your help was invaluable.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
 Post subject: Re: A little problem with SQLite and the LIKE statement
PostPosted: Tue May 21, 2013 8:01 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
There is some bug somewhere that is storing the text with 2 quote marks. At least I assume that isn't what you intended to do.


Top
 Profile  
Reply with quote  
 Post subject: Re: A little problem with SQLite and the LIKE statement
PostPosted: Tue May 21, 2013 8:06 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Actually I am storing the text like that, escaping the single quote. For cases like O'Brian or something similar. I used the word let's just as something that popped in my head when testing but the real intention is to handle single quotes.

I know... Prepared statements... But this is just a quick prototype and the SQL will get replaced with prepared statements.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group