FreeTDS odbc_exec SQL STATE 00000 PHP Error
September 13, 2006
This is just for posterity’s sake and a fix anyone who runs into this problem.
LAMP Box—we need to do queries on an external SQL Server Box.
Enter FreeTDS—not going to get into the details of the install, but you’ll have to reconfigure PHP and add a DSN to your php.ini or use php_set.
Let’s say you’re doing an insert to a system that isn’t really stellar in it’s database design. The tables primary key (ID) is set to a type of INTEGER, and it’s not an identity field. Translation for the MySQL folks: “no auto_increment”. Translation for the MSAccess crowd: “no Autonumber”. Translation for people who don’t understand relational databases: “I can’t believe you’re still reading. The rest of this post will make you want to gouge your eyes out, visit Wikipedia:Unusual Articles for some decent reading”.
Instead of being able have the system determine the value of ID, we have to do it ourself.
$result = odbc_exec(“SELECT MAX ( ID ) as INSERT_ID from TABLE;”);
Since we’re only retrieving one row, it’s not really appropriate to loop through our result, so we obtain the value of the result thusly:
$data = odbc_result($result, “INSERT_ID”);
Now none of your queries will work from this point on in your code. To fix this:
Just free the result.
Ah, the lessons learned today.