Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sun Oct 22, 2017 5:45 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: SQL - ODBC - Escaping characters - double quotes.
PostPosted: Mon May 20, 2013 8:45 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
I'm writing SQL Update statements using ODBC to an Access database. I have the choice of enclosing the entire string value data in either single or double quotes. I'm preferring single quotes and so far so good.

Within a string data value I can escape out an apostrophe (single quote) by doubling up with another apostrophe. When the Update statement is executed I can confirm by checking within the Access database the the update completed AND I only see a single quote; that is, the double single quote was stripped back to a single quote. This I like.

I escape a double quote by preceding it with an escape backslash character "\". The Update statement is valid and after executing I check the result at the Access database. This time though I can see the escape character as part of the text. I'm not overly happy about this though I know upon retrieving the data I can un-escape the escape character.

For testing purposes, rather than using the escape backslash character to escape double quotes, I doubled up on the double quotes and it made for a valid SQL Update statement. Again though, after execution, checking the data in Access the two double quotes are present. Yes, upon retrieval of the data the additional double quotes can be removed for display purposes.

My query is firstly, is this the norm? Secondly, and more of an observation, if another application ie. Access, is retrieving that data by default the escape character (or additional double quotes) will be present. This doesn't seem quite right to me. Are there any other options available?

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL - ODBC - Escaping characters - double quotes.
PostPosted: Mon May 20, 2013 3:07 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
I think you need to triple it or use chr(34) it's been too many years since I last used Access but I think if you want


'Bob is a "nice" person'

you would use

'Bob is a """nice""" person' or use chr(34)


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL - ODBC - Escaping characters - double quotes.
PostPosted: Tue May 21, 2013 2:36 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
I'm querying why the escape character is ultimately embedded in the data.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL - ODBC - Escaping characters - double quotes.
PostPosted: Tue May 21, 2013 10:04 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
This is from memory so it may be off, but I believe you only have to escape the quote mark you are using to enclose the string. Eg., if you're using single quotes around the string, you don't need to escape the double quote, and vice versa.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL - ODBC - Escaping characters - double quotes.
PostPosted: Tue May 21, 2013 4:07 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
timhare wrote:
This is from memory so it may be off, but I believe you only have to escape the quote mark you are using to enclose the string. Eg., if you're using single quotes around the string, you don't need to escape the double quote, and vice versa.

Thanks Tim, yep that did the trick (and now I remember that from years ago).

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 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:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group