Wednesday, March 21, 2007

ODBC Escape sequences!

In other words: those curly bracket things in SQL. What? curly brackets in SQL? Yes! Imagine that :)

The idea is that most database systems adhere to the ODBC standard, at least ODBC 1.0. That means that, when you communicated with a database, you can send so called ODBC escape sequences that are translated into the SQL engine native objects.

Quick example: SELECT {d '2007-03-15'} will work in all ODBC 1.0 compliant DBMSs, including Microsoft SQL Server, MySql, PostgreSQL, Oracle, etc. and select a date object from 15 of March 2007, no matter the server configured country or language.

Interested yet? You can read the ODBC Programmer's Reference for more details. Short story shorter, here are the working and interesting parts (to me) of the ODBC escape sequences:
select {d '2007-02-13' }
select {t '22:20:30' }
select {ts '2007-02-13 22:20:30' }
select {fn curdate()}
select {fn curtime()}
select {fn User()}
select {fn Database()}
select {fn week(getdate())}
select {fn quarter(getdate())}
select {fn monthname(getdate())}
select {fn dayname(getdate())}
select {fn curdate()}
select {fn dayofweek(getdate())}
select {fn dayofyear(getdate())}
select {guid '12345678-1234-1234-1234-123456789012'}