When I send the following query to our Microsoft SQL Server database using odbc_exec:
use mydatabase
select 1 from mytable
it works fine. But if I use odbc_prepare followed by odbc_execute, the query fails, saying that table "mytable" was not found. The "USE" statement is not having an effect. Does anyone have an explanation?
We are using Easysoft's ODBC-to-SQL-Server driver, if it matters. Also this is PHP 5.1.6 and SQL Server 2005 & 2008. The specific error message is:
SqlServerQuery: [unixODBC][Easysoft][SQL Server Driver][SQL Server]Invalid object name 'mytable'.
Note: I did test that multiple statements do work in a single odbc_execute call, so the problem is not caused simply by having multiple statements:
select 1 from mytable
select 2 from mytable
USE statement and odbc_execute vs. odbc_exec
Moderator: General Moderators
-
maiden_taiwan
- Forum Newbie
- Posts: 1
- Joined: Wed Sep 30, 2009 8:54 am
Re: USE statement and odbc_execute vs. odbc_exec
Hi,
The problem occurs because that the driver will perform a TDS prepare operation on the supplied SQL. However as the "use" is only prepared not executed, the current database will not be changed, so the select * from table will fail as the table is not present in the current database (or even worst it is, and PHP end up using the definition of the inncorrect table).
If a exec_direct is called, the use is actioned before the select, so the correct database is in use before the select so all is well.
The problem occurs because that the driver will perform a TDS prepare operation on the supplied SQL. However as the "use" is only prepared not executed, the current database will not be changed, so the select * from table will fail as the table is not present in the current database (or even worst it is, and PHP end up using the definition of the inncorrect table).
If a exec_direct is called, the use is actioned before the select, so the correct database is in use before the select so all is well.