Please Help! No ODBC Results! :(

Anon

I have written some SQL code that executes without any problems in Query Analyzer, but when I try to use it in my PHP code, it does not return any results. I believe the culprits may be the variable assignment lines since everything else seems to work when I comment them out.

Here is a very simplified example, with the names changed to protect the innocent... er... my job:

[NON-WORKING CODE]

$Database_Query = "
DECLARE @Store VARCHAR(64)
SET @Store = 'Jelly Bean Emporium'
SELECT * FROM Some_Table
";

[/NON-WORKING CODE]

[FULLY-FUNCTIONAL CODE]

$Database_Query = "
DECLARE @Store VARCHAR(64)
/SET @Store = 'Jelly Bean Emporium'/
SELECT * FROM Some_Table
";

[/FULLY-FUNCTIONAL CODE]

I am using single-quotes for the store name because they are within double-quotes and escaping them does not seem to work in this context. The table doesn't have to have anything to do with the variable assignment; if the SET line is there--with or without a semi-colon--it breaks. Any ideas what might be causing this and/or how I can fix it?

I am running PHP 4.0.4pl1 on Windows 2000 Server with Microsoft SQL Server 7.0 in case any of that matters.

Thanks in advance for any help! 🙂

steved

Why do you need to set the @Store variable?

Anon

I don't actually need that particular variable in my final code--it is just there to illustrate the problem (in fact, that is not a real variable in my code; like I said, I changed the names to protect my job 😉 )--but basically, what I want to do is grab a database-generated identification number that corresponds to a store's (not really a store in my code, but for the purpose of figuring out the problem...) name then use that to grab some information from other tables all in one transaction so I don't have to go back and forth between PHP and SQL Server.

Let's imagine that I have a table named "Stores" that looks something like this:

Name | ID

We Be Toys | 1
Kitchen Junk | 2
The Bike Guy | 3
Cookie House | 4

"ID" is automatically generated for each new entry. The first thing I am doing is putting the ID into an SQL variable then using that to get information from other tables.

The problem does not seem to be the SQL code, though. Everything works fine in Query Analyzer, but when I try to connect to SQL Server via PHP's ODBC functions, I get no results if I am using a SET statement. Other queries (SELECT * FROM Some_Table, etc.) work as expected as long as they do not contain SET statements.

odbc_prepare did not seem to work for me, so I am back to where I started. Is there something I need to escape in there? Backslashes are just passed on to the database engine, so if so, there must be something else I have to do. 😛

Anon

It looks like the SQL variables I am using are not part of the standard SQL specification, so while supported by SQL Server, they may not be supported by the ODBC driver. It looks like I will have to either make multiple calls to the database after all (I would really rather not do that) or use a stored procedure.

Thanks anyway to anyone who took the time to read about and ponder my problem! 🙂

Anon

The same code that worked through Query Analyzer works in a stored procedure, so it was probably an ODBC driver limitation after all. At least it works now! 🙂