sql tools quote marks

[tom kroto]

sSQLStatement = "SELECT item, bag " + _
"FROM EDC0001 " + _
"WHERE item like '" + sNameSearch + "'"

what I don't understand is the single quote after like and at the end of the line

like ' " sNameSearch + ' "

I would like to add "ordered by bag asc " so I don't know how to use the quote marks

is there a section in help that covers the proper punctuation for creating a string for the sSQLStatement

[Michael Mattias]

==> http://www.w3schools.com/Sql/default.asp

Best online SQL tutorials I've found.

[Stuart McLachlan]

Two simple rules for building SQL strings:

1. You need to surround any string variable with single quotes.
2. If a single quote appears in a string, you need to "escape" it by converting it to two single quotes.

So in your case it would be just be :
sSQLStatement = "SELECT item, bag " + _
"FROM EDC0001 " + _
"WHERE item like '" + sNameSearch + "' order by bag asc "

[Michael Mattias]

FWIW.....

The delimiter character "can" be anything.. but I have never seen it "be" anything BUT the 'single quote'

I know there is an ODBC function you can use to query what the current escape character is; so I assume there is an SQL Tools function to do the same.

MCM

[tom kroto]

what I see

'" + sNameSearch + "' I see single, double then double, single (quote marks)
You are saying that at the end there are 3 single quotes?

===================
I got it to work doing this

sSQLStatement = "SELECT item, bag " + _
"FROM EDC0001 " + _
"WHERE item like '" + sNameSearch + "'" + _ 'note how this looks
"order by item "

But I will try what you sent. I appreciate the feedback. I have only had sql tools for a couple of days.

[Eric Pearson]

Tom --

This isn't a SQL Tools thing, as such. It's really a SQL Statement Syntax thing.

When you submit a SQL Statement to a database, whether you use SQL Tools or not, it must follow certain rules. The most important thing is the final string, so I find that the easiest way to troubleshoot a SQL Statement is to PRINT it or display it in a MSGBOX. In most cases I PRINT# it to a text file so I can examine it in detail.

I also find it useful to use PowerBASIC's $SQ (Single Quote) equate. Instead of...

"WHERE item like '" + sNameSearch + "'" + _

... I would use...

"WHERE item like " + $SQ+sNameSearch+$SQ + _

... to make it clearer that single quotes must surround the string value in the SQL statement.

Finally, there is a missing space in your statement, before the keyword order, which may or may not be important (depending on how picky the driver is).

"WHERE item like " + $SQ+sNameSearch+$SQ + " order by item " 
                                            ^                                                
                                          space

Again, you should focus more on the final string, rather than the PB syntax that is used to build the string.

-- Eric Pearson, Perfect Sync Software

[tom kroto]

ok

Thanks for info and tips especial $sq and sample they are a lot easier to read with the equate

[Michael Mattias]

> especial $sq and sample they are a lot easier to read with the equate

...and an outstanding example of why sometimes "top speed" is not everything.

Apparently the cost for the string concatenation is worth the maintainability benefit, huh?

MCM

[Christopher Carroll]

so I assume there is an SQL Tools function to do the same

Local lDbsNum As Long
Local sQ As String
sQ = SQL_DatabaseInfoStr(lDbsNum, %DB_IDENTIFIER_QUOTE_CHAR)

For MS Access, this returns ` Asc(96).

When used in a SQL statement (in an Access database), ODBC error 07002 (too few parameters) occurs; so I use ' Asc(39) instead.

[Eric Pearson]

Christopher --

sQ = SQL_DatabaseInfoStr(lDbsNum, %DB_IDENTIFIER_QUOTE_CHAR)

That function asks the database (in this case MS Access) for the %DB_IDENTIFIER_QUOTE_CHAR, which is the character that is used to delimit SQL identifiers such as table names and column names in a SQL statement. In my experience it always works. The SQL string delimiter is always CHR$(39) as far as I know so your program doesn't need to query for that.

BTW, that's why I recommend the use of the back-tick character instead of square brackets (see http://www.powerbasic.com/support/pb...ad.php?t=41177). Not only that, it's easier -- especially when writing code to create a statment -- to use one character for both ends of a delimited string.


-- Eric Pearson, Perfect Sync, Inc.