Document information

Document ID:	2148
Subject:	Using ODBC Escape Clause in your SQL syntax
Creation date:	8/20/12 9:43 AM
Last modified on:	8/20/12 9:57 AM

Using ODBC Escape Clause

An ODBC escape clause is a way to talk to the ODBC driver you are using in a DBMS independent manner. The escape clause is represented by a pair of curly braces ({}) surrounding the standard form of the SQL syntax and a one- character or two-character token that specifies the type of the escape clause.

For example, if you want to specify a date value and are not sure about the format expected by the back-end DB, you can use the Escape clause in this construct, {d 'yyyy-mm-dd'} and it will be translated by the ODBC driver manager into the form the backend can utilize. The following select statement will work with any ODBC driver:

SELECT * FROM table1 WHERE datefield = {d '1995-09-12'}

There are also other areas where you can use ODBC escape clauses:

Date and Time

Date      {d 'yyyy-mm-dd'}
Time      {t 'hh:mm:ss'}
Timestamp   {ts 'yyyy-mm-dd hh:mm:ss[.f...]'}

where [.f...] allows you to specify fractions of a second if you wish.

Stored Procedures

For stored procedures a parameter marker (?) must be used for the return value (if any) and any output arguments because it is bound to a program variable. Input arguments can be either literals or parameters, for example,

{? = call procedure_name(arg1, arg2, ?)}

{call procedure_name(arg1, arg2, ?)}

The first example is used when a return parameter is expected and the second example ignores the returned value. Note that in WinSQL, the only way to capture a returned value from a stored procedure is to use the Stored Procedure Wizard

String Functions

ASCII(), CHAR(), CONCAT(), DIFFERENCE(), INSERT(), LCASE(), LEFT(), LENGTH(), LOCATE(), LTRIM(), REPEAT(), REPLACE(), RIGHT(), RTRIM(), SOUNDEX(), SPACE(), SUBSTRING() and UCASE().

Consider the following example:

Select * from authors where {fn LEFT(author, 3)} = 'dat'

This example will retrieve a resultset where the first three characters of author field is 'dat' from the authors table.

Math Functions

ABS(), ACOS(), ASIN(), ATAN(), ATAN2(), CEILING(), COS(), COT(), DEGREES(), EXP(), FLOOR(), LOG(), LOG10(), MOD(), PI(), POWER(), RADIANS(), RAND(), ROUND(), SIGN(), SIN(), SQRT(), TAN() and TRUNCATE().

System Functions

DATABASE(), IFNULL() and USER(). This example returns all the orders entered by the current user:

Select ordnum From orders Where employee = {fn User()}

Time and Date Functions

CURDATE(), CURTIME(), DAYNAME(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), HOUR(), MINUTE(), MONTH(), MONTHNAME(), NOW(), QUARTER(), SECOND() and TIMESTAMPADD()

Following example returns the current date from the database.

select {fn CURDATE()}

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.

Your name:
Your email:	Hide my email address
Verification code:	Enter the verification code you see above more submitting your tip
Tip:	Please limit tips to 1000 characters