PHP SQL Server Insert issue

Question

The following statement runs fine in the MS SQL server management studio. However when I try to execute via PHP the insert does not occur and no errors are returned. I know my connection is valid, all my select statements return properly. What am I missing?

DECLARE @id bigint; SET @id = (SELECT MAX(application_track_id) + 1 FROM application_track_data); INSERT INTO application_track_data (application_track_id,user_id, action_key, action, ip_address, session_id, application) VALUES (@id,1,'584','login','192.168.37.60','05sn3618p61dvmml6pkefuteg2','akamata');

Here is the code I am using to execute the sql.

$result = mssql_query($sql);
if(!$result)
{
print "Error:" . mssql_get_last_message();
}
else
print "Success";

Answer 1

You might want to put this in a procedure. Since you have multiple commands in your SQL statement I believe most db drivers only can call a single statement (or single T-SQL statement). So if you need multiple things to happen turn it into a stored procedure or execute each individual statement based on return value etc.

UPDATE: Try this: http://us3.php.net/manual/en/function.mssql-next-result.php

I guess it does allow multiple queries but you need to get the right one. There are also some db libraries that have a *_query_multiple function but only in the mysqli libraries or maxdb libraries.

Answer 2

My guess would be that you are running with autocommit off, and not committing the transaction.

Answer 3

This kind of activity really should be in a stored procedure. Easier to manage and easier to call from php. You also need to commit those changes everytime.

Answer 4

Try the insert statement alone. If that works then you know having multiple statements is causing an error (and you'll need to replace your queries with a stored procedure). If it fails then jsight is right, it's a problem with autocommit.

Personally I'd place my bet with the multiple statements, but this should give you a route to determine the root cause (it's possible both are a problem).

Answer 5

Ok, I have it working. I switched to using the SQL server driver provided by Microsoft available here http://www.microsoft.com/downloads/details.aspx?FamilyId=61BF87E0-D031-466B-B09A-6597C21A2E2A&displaylang=en .

Apparently the mssql driver cannot work with certain data types, like bigint.