Announcement

Collapse
No announcement yet.

Opening and Updating Data onAfterUpdate

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Opening and Updating Data onAfterUpdate

    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();
    }


    Reginald Bailly

  • #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?

    Comment


    • #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.
      Code:
       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....

      Comment


      • #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.

        Comment


        • #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.

          Comment


          • #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.

            Comment

            Working...
            X