Can't INSERT/UPDATE with MSSQL

bteravsk

Hello,

I'm currently running PHP 4.0.6 and freeTDS 5.1 on a Red Hat Linux system (6.1) I am accessing a Microsoft SQL 7.0pl1 server. I can do all kinds of selects against the server with no problems. When I try to do an insert/update it does not work.

Here is the code I am testing with:

<BODY>
<?

$CID=mssql_connect("test", "sa", "");

mssql_select_db("test",$CID);

$IPID=array(1,2,3,4);
$Desc=array("Test 1", "Test 2", "Test 3", "Test 4");

foreach ($IPID as $key=>$value)
{
print ("Row #$key: \n");
$SQLCommand1 = "INSERT INTO IllegalRouteExclusionTable ".
"(SubnetID, Description) ".
"VALUES ($IPID[$key],'$Desc[$key]')";
$SQLCommand2 = "UPDATE IllegalRouteExclusionTable ".
"SET Description='$Desc[$key]' ".
"WHERE SubnetID=$IPID[$key]";
print ("$SQLCommand1 \n");
$result=mssql_query($SQLCommand1, $CID);
if (!$result)
{
print ("$SQLCommand2 \n");
$result=mssql_query($SQLCommand2, $CID);
if (!$result)
{
mssql_close($CID);
print ("INSERT and UPDATE FAILED!!! \n");
exit;
}
}
print ("Updated/Inserted: $IPID[$key]: $Desc[$key] \n");
}

mssql_close($CID);

?>
</BODY>

When I execute this code the first time all seems to work well. Here is the output:

Row #0:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (1,'Test 1')
Updated/Inserted: 1: Test 1

Row #1:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (2,'Test 2')
Updated/Inserted: 2: Test 2

Row #2:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (3,'Test 3')
Updated/Inserted: 3: Test 3

Row #3:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (4,'Test 4')
Updated/Inserted: 4: Test 4

Here is what is in the database:

select * from IllegalRouteExclusionTable;

SubnetID Description

1 Test 1
2 Test 2
3 Test 3
4 Test 4

(4 row(s) affected)

OK, everything looks good so far. Now I change the arrays in the code to:

$IPID=array(1,2,3,4,5,6);
$Desc=array("Test 1 change", "Test 2", "Test 3 change", "Test 4", "Add 5", "Add
6");

This introduces a couple of adds and changes.

Now I execute the code and the output is:

Row #0:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (1,'Test 1 change')

Warning: Sybase error: Violation of PRIMARY KEY constraint 'PK_IllegalRouteExclusionTable'. Cannot insert duplicate key in object 'IllegalRouteExclusionTable'. (severity 14) in /home/httpd/html/bteravsk/test2.php on line 22
UPDATE IllegalRouteExclusionTable SET Description='Test 1 change' WHERE SubnetID=1
Updated/Inserted: 1: Test 1 change

Row #1:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (2,'Test 2')
Updated/Inserted: 2: Test 2

Row #2:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (3,'Test 3 change')

Warning: Sybase error: Violation of PRIMARY KEY constraint 'PK_IllegalRouteExclusionTable'. Cannot insert duplicate key in object 'IllegalRouteExclusionTable'. (severity 14) in /home/httpd/html/bteravsk/test2.php on line 22
UPDATE IllegalRouteExclusionTable SET Description='Test 3 change' WHERE SubnetID=3
Updated/Inserted: 3: Test 3 change

Row #3:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (4,'Test 4')

Row #4:
INSERT INTO IllegalRouteExclusionTable (SubnetID, Description) VALUES (5,'Add 5')
UPDATE IllegalRouteExclusionTable SET Description='Add 5' WHERE SubnetID=5
INSERT and UPDATE FAILED!!!

You can see that row 1(0) does what I would expect. Row 2(1) says it did a successfull Insert ?!? Rows 3(2) and 4(3) do what I would expect (updates), and then row 5(4) fails all together.

Here is what Query Analyzer shows:

SubnetID Description

1 Test 1 change
2 Test 2
3 Test 3
4 Test 4

(4 row(s) affected)

Only row 1 was changed.

Am I missing something here? This kind of algorithm works in Perl/C...

Any help would be greatly appreciated.

Regards,

Brian