WinSQL » Knowledge base
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 '1924-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, ?)}
or
{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.
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?
Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our
public forums to post questions.
