Macros, MySQL and lastInsertID

Scriptcase 8 Discussion
#1

I have a project where I need to do 4 inserts in a MySQL database in a single transaction. One of the requirements is using lastInsertID to obtain the primary key (it’s an auto_increment field), so that subsequent inserts will properly link records between tables. For example, in table A, after an insert, the primary key (ID) might be 57 for the last record inserted. In Table B, when a new record was inserted, a field called table_a_id would need to use the value 57 (lastinsertID obtained from the Table A insert). A similar step is required after a Table B insert when inserting a new record in Table C.

I can do this in an external PHP page, but I would like to use a SC form to ask a question (Yes or No), record the response in a different table, and based on that response, complete the transaction described above (on a different set of tables). Pseudo code: if ({answer} == 'Y") { Do Transactional Inserts}. I would place this code inside the onAfterInsert event in the form.

Can I access lastinsertID using a SC macro? I am not sure that sc_select, which exposes an ADO recordset has a lastinsertID method. Any advice would be appreciated.

#2

First of all you cannot generate an in-between form for that action in scriptcase without a lot of hassle. The question should be on the main form so you can use the onafterinsert to complete the things you want to do. You can use all db statements in your macro’s, it depends on the event you are calling the macro if the data is available.

#3

If you insert the first record in table A, then the records in the other tables can be inserted through database Triggers AFTER INSERT using the key in table A.

#4

@albert - I tried what you suggested (if I understood your suggestion). sc_select($db, $sql) where $sql contained INSERT… I received this error message… “Fatal error: Call to undefined method
ADORecordSet_pdo::lastinsertid()”. Is there a SC PDO object (obviously not an ADORecordset) where lastinsertid() method is defined?

@landame - I will explore whether a DB trigger could achieve the result I am looking for. Unfortunately, not all inserts into Table A require the subsequent process I described in my example (insert table B, insert table C). Also, I might need to pass some variables into the trigger, unless I hardcoded the values into separate triggers (only 5 unique inserts).

#5

Well, I may have solved my own problem… seems there is an SQL function for lastinsertid besides a PDO method. The code below works (at least inserting one record in one table and showing the proper lastinsertid. I will see if I can chain multiple inserts and lastinsertid functions. But here is the working code:

// Define SQL statements
$sql1 = “INSERT INTO …”; // Note: Fill in fields and values
$sql2 = “SELECT LAST_INSERT_ID();”; // Use SQL function instead of PDO method
// Start transaction
sc_begin_trans(“my_conn”);
// Do the insert
sc_exec_sql($sql1,“my_conn”);
// Get the value of last_insert_id
sc_lookup(rs, $sql2,“my_conn”);
if(count({rs}) == 0)
{
// Problem
sc_error_message(‘No last insert id’);
sc_rollback_trans(“my_conn”); // Can’t continue, so rollback insert
sc_error_exit();
}
else {
// Good to go
$lastinsert = {rs[0][0]}; // Do something with this
// More statements (as needed)
sc_commit_trans(“my_conn”); // Commit
}

#6

Cool. Thanks for the info.