php sqlsrv insert failed

Question

I'm attempting to insert a row into Microsoft dynamics RMS database through a web interface. I can run the insert from the Store Operations Manager (command line?) and it works great, but when i try to run it from my PHP script using sqlsrv it dumps an 515 error saying that it cannot insert a null value into an ID column.

I feel like the error is something with the sqlsrv talking to the database rather than the database itself because i can run the same line directly and it creates the new row.

I tried this with params the first few times but took it out in the interest of trouble shooting. i got the same error with both methods.

error message:

( [0] => Array ( [0] => 23000 [SQLSTATE] => 23000 [1] => 515 [code] => 515 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'ID', table 'OMGHQ.dbo.Customer'; column does not allow nulls. INSERT fails. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'ID', table 'OMGHQ.dbo.Customer'; column does not allow nulls. INSERT fails. ) [1] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 3621 [code] => 3621 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. ) )

    $conn = sqlConnection();

if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$itlquery = "DECLARE @itemID INT
            DECLARE @newQuantity INT
            SET @itemID = $itemID
            SET @newQuantity = $newQuantity
            INSERT INTO inventorytransferlog (itemID, quantity, cashierID, type, cost) VALUES (@itemID, @newQuantity - (SELECT TOP 1 quantity FROM item WHERE id = @itemID), 6, 5, (SELECT TOP 1 cost FROM item WHERE id = @itemID));";

echo $itlquery;

$itlstatement = sqlsrv_query($conn,$itlquery);

if($itlstatement === false)
{
    die(print_r(sqlsrv_errors(),true));
}

Tom Kay Tom Kay · Accepted Answer · 2015-12-17T18:02:55

without knowing what your php variables are ($itemID and $newQuantity) it is much safer to use parameters as follows. This is a prepared statement, and has the added benefit protecting against some SQL injection.

$itlquery = "INSERT INTO inventorytransferlog (itemID, quantity, cashierID, type, cost) VALUES (?, ? - (SELECT TOP 1 quantity FROM item WHERE id = ?), 6, 5, (SELECT TOP 1 cost FROM item WHERE id = ?));";

echo $itlquery;

$itlstatement = sqlsrv_query($conn,$itlquery,array($itemId,$newQuantity,$itemId,$itemId));

EDIT: While the above is correct, it's not the answer to your question. You must have an ID column on your Customer table which is not nullable. perhaps it is the primary key and is not auto-increment. So you must either specify it in your insert, or change the schema to make it nullable or auto-increment (if it is not primary key)

php sqlsrv insert failed

1 Answers