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";

Community · Accepted Answer · 2023-11-21 15:10:29Z

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: https://www.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.

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Jul 16, 2009 at 14:24

Ryan Christensen

7,9531 gold badge29 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Ok, I put it in a stored procedure. Now I get the following error: Error converting data type varchar to numeric. The SP runs fine from the Management Studio. The only thing that I see with the table structure is that one of the columns is a bigint. Will that cause an issue.

James · Accepted Answer · 2009-07-16 18:07:15Z

1

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.

answered Jul 16, 2009 at 18:07

James

1191 gold badge1 silver badge7 bronze badges

Comments

jsight · Accepted Answer · 2009-07-16 14:24:48Z

0

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

answered Jul 16, 2009 at 14:24

jsight

28.5k25 gold badges111 silver badges140 bronze badges

Comments

Troggy · Accepted Answer · 2009-07-16 14:28:02Z

0

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.

answered Jul 16, 2009 at 14:28

Troggy

6442 gold badges9 silver badges23 bronze badges

Comments

acrosman · Accepted Answer · 2009-07-16 14:28:57Z

0

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).

answered Jul 16, 2009 at 14:28

acrosman

12.9k10 gold badges42 silver badges56 bronze badges

1 Comment

James Over a year ago

If I run the insert statement by itself. I get "Error converting datatype varchar to numeric". If i reduce the insert statement to the only required field I still get the error. Will a bigint data type cause an issue?

Collectives™ on Stack Overflow

PHP SQL Server Insert issue

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related