Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Really Basic SQL Question - quotes (Real Studio network user group Mailinglist archive)

Back to the thread list
Previous thread: Re: Windows Interface
Next thread: SpecialFolder.SharedDocuments exists, but it's nil?


Reading *CSV Files versus *.Txt Files   -   Claude Stone
  Really Basic SQL Question - quotes   -   wagnerj proaxis.com
   Re: Really Basic SQL Question - quotes   -   Keith DeLong
   Re: Really Basic SQL Question - quotes   -   Fargo Holiday
   Re: Really Basic SQL Question - quotes   -   Joe Strout
    Re: Really Basic SQL Question - quotes   -   wagnerj proaxis.com
     Re: Really Basic SQL Question - quotes   -   Joe Strout
     Re: Really Basic SQL Question - quotes   -   Kim Kohen
    Re: Really Basic SQL Question - quotes   -   wagnerj proaxis.com

Really Basic SQL Question - quotes
Date: 01.08.08 20:25 (Fri, 1 Aug 2008 12:25:56 -0700 (PDT))
From: wagnerj proaxis.com
Greets -

I've used databases only a little. One of the things that leaves me
mystified is the use of single and double quotes in SQL command strings. I
end up muddling through by trying to copy examples and never get an
understanding of what is going on.

I've looked for tutorials to fill this in, but have been unsuccessful. Can
someone point me toward a useful source, or perhaps explain how it works?

Many thanks
Jim Wagner
Oregon Research Electronics
Tangent, OR

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Really Basic SQL Question - quotes
Date: 01.08.08 20:49 (Fri, 01 Aug 2008 12:49:11 -0700)
From: Keith DeLong
> Greets -
>
> I've used databases only a little. One of the things that leaves me
> mystified is the use of single and double quotes in SQL command strings. I
> end up muddling through by trying to copy examples and never get an
> understanding of what is going on.
>
> I've looked for tutorials to fill this in, but have been unsuccessful. Can
> someone point me toward a useful source, or perhaps explain how it works?
>
> Many thanks
> Jim Wagner
> Oregon Research Electronics
> Tangent, OR

Hi Jim,
Perhaps you'll find this SQL formatting method snip useful:

Function SQLFormat(Extends Value as Variant) As String
const Quote = "'"
dim sqlString as String

Select Case Value.Type
Case Variant.TypeBoolean
'Ensures SQLite db always gets 0/1 for boolean values
sqlString = Str( Value.IntegerValue )

Case Variant.TypeInteger, Variant.TypeDouble, Variant.TypeCurrency,
Variant.TypeLong, Variant.TypeSingle
sqlString = Value.StringValue

Case Variant.TypeDate
sqlString = Quote + Value.DateValue.SQLDate + Quote

Case Variant.TypeString
' escape any ' quote values within the string with double quotes
sqlString = Quote + Value.StringValue.ReplaceAll( "'" , "''" ) + Quote

Else
sqlString = "UNKNOWN SQLFORMAT VARIANT TYPE " + str( Value.Type )

End Select

Return sqlString
End Function

HTH,

Keith DeLong


_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Really Basic SQL Question - quotes
Date: 01.08.08 20:54 (Fri, 01 Aug 2008 12:54:01 -0700)
From: Fargo Holiday
<email address removed> wrote:
> Greets -
>
> I've used databases only a little. One of the things that leaves me
> mystified is the use of single and double quotes in SQL command strings. I
> end up muddling through by trying to copy examples and never get an
> understanding of what is going on.
>
> I've looked for tutorials to fill this in, but have been unsuccessful. Can
> someone point me toward a useful source, or perhaps explain how it works?
>
> Many thanks
> Jim Wagner
> Oregon Research Electronics
> Tangent, OR

Bear in mind that I have no formal education on the matter, but here's
my experience, primarily drawn from website work with MySQL. I
encapsulate the statement in double quotes, and any string type data,
such as chars, text, and so on, have to be single quoted. Most SQL type
systems don't mind if you single quote anything, so when it doubt you
may as well try it. That in mind, make sure to escape any single quotes
that will appear in the data, or it'll likely make a mess of your SQL
statement. I don't know what the escape mechanism is for SQLLite, or if
it's needed.

Best of luck,
Fargo

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Really Basic SQL Question - quotes
Date: 01.08.08 21:29 (Fri, 1 Aug 2008 14:29:21 -0600)
From: Joe Strout
Fargo's summary is pretty much spot-on. The SQL standard is for
string literals to be in single quotes, and if such a literal itself
requires an apostrophe, you "escape" it by doubling it -- that is, by
using two single quotes in a row (not by using a double quote).
That's all there is to it.

Some databases also provide other ways of escaping things, often
involving a backslash, but that's not part of the SQL standard.

Best,
- Joe

Re: Really Basic SQL Question - quotes
Date: 01.08.08 22:53 (Fri, 1 Aug 2008 14:53:35 -0700 (PDT))
From: wagnerj proaxis.com
>Fargo's summary is pretty much spot-on. The SQL standard is for string
literals to be in single >quotes, and if such a literal itself requires
an apostrophe, you "escape" it by doubling it -- that is, >by using two
single quotes in a row (not by using a double quote). That's all there
is to it.
>
>Some databases also provide other ways of escaping things, often
involving a backslash, but that's >not part of the SQL standard.
>
>Best,
>- Joe
>
>--
>Joe Strout
>Inspiring Applications, Inc.
>http://www.InspiringApps.com

And to make it completely certain, in this context, "string literals" are
the strings used to specify values (as in search terms)?

If you supply that in the form property as in the following example,

sql = "SELECT Name FROM Distributor WHERE AreaCode = MyAreaCode"

If I were to supply the areacode search term as a fixed string, say 541,
then, by Fargo's description, I would delimit it with single quotes as in
'541'.

Is it the same if its a property, so that the sql string should be
'MyAreaCode'? Or, is it correct as written, above?

Thanks

Jim Wagner
Oregon Research Electronics

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Really Basic SQL Question - quotes
Date: 01.08.08 23:11 (Fri, 1 Aug 2008 16:11:27 -0600)
From: Joe Strout
On Aug 1, 2008, at 3:53 PM, <email address removed> wrote:

> And to make it completely certain, in this context, "string
> literals" are
> the strings used to specify values (as in search terms)?

Right. SQL is a programming language, just like REALbasic. It has
variables and literals. A literal is a value specified directly,
right there in the code, as it is. It's not a reference to some data
stored somewhere else.

> If you supply that in the form property as in the following example,
>
> sql = "SELECT Name FROM Distributor WHERE AreaCode = MyAreaCode"

Here, since MyAreaCode is not in quotes, it would be an SQL variable
(most likely a column name).

> If I were to supply the areacode search term as a fixed string, say
> 541,
> then, by Fargo's description, I would delimit it with single quotes
> as in
> '541'.

Correct.

> Is it the same if its a property, so that the sql string should be
> 'MyAreaCode'? Or, is it correct as written, above?

Neither, if by "property" you mean something in your REALbasic code.
The SQL code is a completely separate environment from your REALbasic
code; neither one knows anything about the other. If you write
"MyAreaCode" in your SQL without quotes, then you've specified an SQL
variable (column name). If you use "'MyAreaCode'" instead, then you'd
be looking for records whose AreaCode is literally "MyAreaCode".
That's not what you want either.

If you want to search by the value of a REALbasic variable, then you
need to convert it into an SQL literal, like so:

sql = "SELECT Name FROM Distributor WHERE AreaCode = '" +
MyAreaCode + "'"

Work through what value sql gets from this assignment statement, and
you'll see that it's correct. The one hitch is if the value of
MyAreaCode might contain an apostrophe. If that's possible, you need
to do this:

sql = "SELECT Name FROM Distributor WHERE AreaCode = '" _
+ ReplaceAll( MyAreaCode, "'", "''" ) + "'"

so that any such apostrophes are doubled, so they don't terminate the
SQL string literal. I usually keep around an "SQLify" function that
does this for me (and can also convert other RB data types to their
proper SQL literals).

Best,
- Joe

Re: Really Basic SQL Question - quotes
Date: 01.08.08 23:25 (Sat, 2 Aug 2008 08:25:16 +1000)
From: Kim Kohen

On 02/08/2008, at 7:53 AM, <email address removed> wrote:

> If I were to supply the areacode search term as a fixed string, say
> 541,
> then, by Fargo's description, I would delimit it with single quotes
> as in
> '541'.

correct, but keep in mind some db engines will happily accept integer
values without quotes so in your example they may not be needed.

> Is it the same if its a property, so that the sql string should be
> 'MyAreaCode'?

no, this is incorrect. If you want to include a variable value, you
need to bounce in and out of RB/SQL syntax like so:

sql = "SELECT Name FROM Distributor WHERE AreaCode = ' " + MyAreaCode
+ " ' "

I've added extra spaces near the quotes so you can see what's needed.

/k
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>

Re: Really Basic SQL Question - quotes
Date: 01.08.08 23:38 (Fri, 1 Aug 2008 15:38:16 -0700 (PDT))
From: wagnerj proaxis.com
Thanks, Joe -

That cleaned things up for me!

Jim Wagner
Oregon Research Electronics
Tangent, OR

_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>