ODBC 'Execute' method too data-source specific

[BrendanK]

A Quick prob for anyone who knows:

I'm using VB6, ADO/ODBC to connect to a MySQL server, and want to run a SQL 'Insert' statement on the Database.

The only way I can get a Date field into the database okay is to specify it in MySQL server's format (yyyy-mm-dd).

Eg. cnDB.Execute "INSERT INTO tablename VALUES(1,'BOB','2001-01-28')"

Shouldn't ODBC be translating the date for me? I want to be able to point the data-source at another type of database without changing my app code.

Cheers

Brendan

 

[tlbroadbent]

When you use Execute, you are bypassing ODBC and passing the query directly to MySQL. Thus you have created a pass-through query which is MySQL specific. Terry

http://www.defendscouting.com/

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[BrendanK]

Thanks for that man - that makes sense.

No way to execute a query that ISN'T a 'pass through' query I suppose?

Thanks

Brendan

 

[Cagliostro]

In ODBC API exists two functions do work directly with database
1. SQLDriverConnect, give you an ODBC less dependent execution. You deppend there more on the driver.
2. SQLExecDirect and SQLExecute. By default hey are pass through. Also there should be some connection options to send sql directly to database driver. John Fill

ivfmd@mail.md

 

[foxdev]

I'm not a VBer, so this may not apply, but when sending SQL pass-through statements via ODBC in Visual FoxPro, you can use a bind variable, and ODBC seems to do some intelligent processing on this.

An example:

[tt]MyDate = '06/15/2001'
MySQLStatement = "INSERT INTO tablename VALUES(1, 'BOB', ?MyDate)"
SQLExec(handle, MySQLStatement)[/tt]

The ?MyDate is interpreted by ODBC and given special handling; whether this will work for you or not I am not sure.
Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[BrendanK]

Awesome - thanks Robert, I'll try that. I might have to learn Foxpro sometime!

Cheers

Bredan

 

[foxdev]

We Foxheads are becoming the COBOLers of the future. Its a mixed bag; like Delphi aficionados, we love and believe in the product, but the publisher and/or market relegates it to the fringes.

Here's an article that might be of use:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_04_4pyr.asp

Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com