sql dialects

[jdv_m...@my-deja.com]

How to handle different dialects of sql.

For what I’ve seen it appears that the only technique that allows one
to send truly generic sql statements is DAO. As long as the sql that
passes through JET works, it will give the same result back whether you
are writing to sql server, access, et al. For example the syntax I use
in the like clause will work for Access, SQl Server, …

Now, I know RDO, ADO, and ODBC use standard sql syntax. So if you
write general sql using these methods you are probably fairly safe.
However, it appears that you still need to worry about sql dialects in
some cases. For example if you are writing to access you would use a #
for dates, whereas in sql server you would use ‘ around dates.

Are my assumptions correct? Is there a way other than DAO to write
truly generic sql??

Sent via Deja.com http://www.deja.com/
Before you buy.

[Sergio Cortese]

<jdv_m...@my-deja.com> wrote in message 8o6j10$e55$1...@nnrp1.deja.com...

> How to handle different dialects of sql.
>
> For what I've seen it appears that the only technique that allows one
> to send truly generic sql statements is DAO. As long as the sql that
> passes through JET works, it will give the same result back whether you
> are writing to sql server, access, et al. For example the syntax I use

> in the like clause will work for Access, SQl Server, .
>
Are you sure that DAO is universal? the answer is: depends on the
PassThrough switch.
If PassThrough is off, the query is universal, but the where condition is
implemented by local JET.
Would you put an Oracle Server at work under this condition? I won't.

If PassThrough is on, the query is NOT universal. Period.

> Now, I know RDO, ADO, and ODBC use standard sql syntax. So if you
> write general sql using these methods you are probably fairly safe.
> However, it appears that you still need to worry about sql dialects in
> some cases. For example if you are writing to access you would use a #
> for dates, whereas in sql server you would use ' around dates.
>
> Are my assumptions correct? Is there a way other than DAO to write
> truly generic sql??
>
>

If you use no funcion an no Outer joins, you probably are safe enough.

Sergio

[Carlos J. Quintero]

Hi,

ODBC provides a ODBC syntax for dates ( { d 'yyyy-mm-dd'} ), timestamps
{ ts '...'}), functions ( { fn RIGHT() } ... ) and even outer joins ( { oj
.... }). So, if you write using ODBC syntax :

SELECT * FROM MyTable WHERE MyField = { d '2000-12-31' }

each ODBC driver translates it into native syntax of its underlying engine
before sending the statement. You can see the documentation of ODBC syntax
in the Appendixes of ODBC Programmer's Reference (MDAC documentation).

ODBC is or can be used from DAO, RDO, ADO with OLE DB Provider for ODBC and
even most native OLEDB providers support ODBC syntax (Oracle, SQL Server,
but native OLEDB Jet Provider if MS has not enhanced it).

The only problem you will find is with bugs in ODBC drivers / OLEDB
Providers translating ODBC syntax to native syntax (specially ODBC outer
joins).

Best regards,

Carlos J. Quintero

<jdv_m...@my-deja.com> escribió en el mensaje de noticias

8o6j10$e55$1...@nnrp1.deja.com...
> How to handle different dialects of sql.
>
> For what I've seen it appears that the only technique that allows one
> to send truly generic sql statements is DAO. As long as the sql that
> passes through JET works, it will give the same result back whether you
> are writing to sql server, access, et al. For example the syntax I use
> in the like clause will work for Access, SQl Server, .
>

> Now, I know RDO, ADO, and ODBC use standard sql syntax. So if you
> write general sql using these methods you are probably fairly safe.
> However, it appears that you still need to worry about sql dialects in
> some cases. For example if you are writing to access you would use a #
> for dates, whereas in sql server you would use ' around dates.
>
> Are my assumptions correct? Is there a way other than DAO to write
> truly generic sql??
>
>
>