Write to DB, then Read value. Not working.

Code Examples Control
#1

I want to write some values to two tables, then read the value immediately after, but this isn’t working. I have no idea why.

Here is my code:

// Transaction insert into two tables...    
$insert_transaction = "
start transaction;
insert into Shipping.ShipmentHeader(SONum, User,DateofReq)
values('".{SalesOrderNo}."','".[usr_login]."', now());
select @NextRecNbr := max(RecNbr) from Shipping.ShipmentHeader;
insert into Shipping.ShipmentPackages(HeaderRecNbr,Length,Width,Height, Weight)
values(@NextRecNbr,".{Length}.", ".{Width}.", ".{Height}.", ".{Weight}."); 
commit;
SELECT MAX(RecNbr) FROM Shipping.ShipmentHeader; 
";
    
sc_lookup(rs1, $insert_transaction);
            
if (isset({rs1[0][0]}))     // Row found
{
    $ShipRateHeaderRecNbr = {rs1[0][0]};
}
        else     // No row found
{
        
 echo "This Should not run ever";
 exit;
}

I would expect to get the value into $ShipRateHeaderRecNbr… but instead its always going to the the else and I get “This Should not run ever”. Am I missing something here?
​

#2

Well… Im not happy with the solution… but I was able to write another check to the DB below this one and it sees the data that was written. I have NOIDEA why it wont write the data and return it in one transaction like I was trying to do.

Note: This code is inside another database reading loop… perhaps that has something to do with it… I havent tried running this code stand alone to see if it works or not.

#3

This still bothers me… as I dont understand why it didn’t work… but in trying other methods of writing to MySQL via scriptcase I came across this error message.

[SIZE=12px]Access error. Message=Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

So I am trying to figure out now if I am actually using the best method to write into two tables at once… a header and a detail… where the detail table has the record number (autonumber) from the header.

Anyone care to offer their method for writing a header and detail table to where a foreign key contraint can be set on the detail so no orphaned detail records when you delete a header record?[/SIZE]