Opening and Updating Data onAfterUpdate

Developers Hot tips
#1

I am getting the following error message
“Transactions not supported in ‘mysql’ driver. Use ‘mysqlt’ or ‘mysqli’ driver”

Can anybody help - I looking at a way to update or insert data into another table after
a Form is update or a new item is added…

Looking for some HOT Tips …!

here is the code that I use :

$dsn = ‘mysql:host=localhost;mydatabase;’;
$user = ‘fredy’;
$password = ‘xxxxxxxxxxxxxxxx’;
try
{
$dbh = new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = ‘INSERT INTO syspara
(SysID, Xcomp, Xadd1)
VALUES (:SysID, :Xcomp, :Xadd1)’;
$stmt = $dbh->prepare($sql);
$stmt->bindParam(’:SysId’, $SysID);
$stmt->bindParam(’:Xcomp’, $Xcomp);
$stmt->bindParam(’:Xadd1’, $Xadd1);
$stmt->execute();

}
catch (PDOException $e)
{
echo 'PDO Exception Caught.
';
echo ‘Error with the database: <br />’;
echo 'SQL Query: ', $sql;
echo 'Error: ’ . $e->getMessage();
}

#2

Re: Opening and Updating Data onAfterUpdate

is the table you are trying to update in a different database than the one scriptcase is connected to?

#3

Re: Opening and Updating Data onAfterUpdate

I have the same scenerio. I’m using V5. I placed this code at onBeforeInsert Event.
Form Settings Procedure were set to a dummyProc with one input parameter, just to bypass the normal insert operation of SC.


 try {
   $myConnStrDSN = "mysql:host=localhost;dbname=mydb";
   $myConnStrUid = "myuserId";
   $myConnStrPwd = "myuserPassword";

   $dbh = new PDO($myConnStrDSN, $myConnStrUid, $myConnStrPwd); 
	 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	 $dbh->beginTransaction();

	 
   $myquery = "INSERT INTO table (personId, LastName, FirstName, NickName)
	 VALUES ({personId},{LastName},{FirstName},{NickName})"; 

   $count = $dbh->exec($myquery);

  $pId=$dbh->lastInsertId();

 $count = $dbh->exec("INSERT INTO table2(TableNum, ModifiedBy, Operation) VALUES (6,1,1)"); 
	 $RowVersionId = $dbh->lastInsertId();

	 $myquery = "Update table SET RowVersionID = ".$RowVersionId." WHERE personId =". $pId . " LIMIT 1"; 
   $count = $dbh->exec($myquery); 

   $dbh->commit();	 

		  }
  catch(PDOException $e)
  {  
     $dbh->rollback();
	 $srchword = Array("SQLSTATE", "`mydb`.", "CONSTRAINT", "FOREIGN KEY", "REFERENCES");
	 $wordreplace = "";
	 $errmsg = str_replace($srchword, $wordreplace, $e->getMessage());
	 sc_error_message($errmsg);
	
  }

When running it gives error message saying “Could not find driver”
Is there any example in SC on how to run custom codes??? pls. help…

#4

Re: Opening and Updating Data onAfterUpdate

As asked by Vivid, are you wanting to update another table in a different DB, or just add a record to a different table.

There is no need to create a new connection, just issue a INSERT to the table.

// insert other_names with new entry
$sql = ‘INSERT INTO my_other_table SET field = “value”’;
sc_exec_sql($sql);

If you need to insert into a different table, in a different DB, create a new connection in the IDE and then access that in the call

sc_exec_sql(“SQL Command”, “Connection”)

Regards,
Scott.

#5

Re: Opening and Updating Data onAfterUpdate

Hi Scott,

same connection, however, I need to:

  • open a transaction
  • Insert a record in a table with AutoIncrement field.
  • Retrieve that Last Inserted Id, store it as personID
  • Insert a record in another table with AutoIncrement field.
  • Retrieve that Last Inserted Id, store it as rowversionID
  • Update the first table rowversionID with the Last Inserted ID value.
  • If no Error, Commit Transation and display notification “Data inserted successfully”
  • if has error Roll back and display error message.

How can I achieve this in SC without creating new connection?
I have done the above algorithm but using a new connection.

#6

Re: Opening and Updating Data onAfterUpdate

Seems like a lot of traffic back and and forth for updates to server data. Would a STORED PROC work in this case? No need for the client to get involved until success/fail.

Just make a call from your current connection to the PROC?

Regards,
Scott.