How to execute additional SQL in After Execute family events in CCS

October 23rd, 2010

Hi! This article is about how to execute some addtitional SQL after the primary SQL in the CodeCharge Studio’s Record has been executed.

Very often I have to perform some additional manipulations with the database after the Record submission. In this article I want to share the approach I use to reach the desired result. If you use a different approach to reach the same result I’m waiting your comments!

So, here’re some steps:

1. You should have already a Record that performs Insert, Update or Delete.

2. Choose the event you will work with. It may be After Execute Insert, After Execute Update or After Execute Delete. I’ve chosen After Execute Insert to test with it in this example.

3. Add the Custom Code to the After Execute Insert event of the Record.

4. Try this code:

    /* $master is a Record we work with.
     * In the next line condition we check that no errors have been occured.
     */
    if($master->ds->Errors->Count() == 0 && $master->ds->CmdExecution) {
        // In the code below, we get the ID that has been inserted last time.
        $db = new clsDBTest(); 
        $db->SQL = 'SELECT LAST_INSERT_ID() AS master_id'; 
        $db->query($db->SQL);
        $result = $db->next_record();
 
        if ($result) {
                $master_id = $db->f("master_id");
        } else { 
                $master_id = -1; 
        }
 
        $db->close();
 
        /* In this code snippet, we insert a new record to
         * another "details" table with the "master id" value.
         */
        if ($master_id != -1) {
                $db = new clsDBTest();
                $db->SQL = "INSERT INTO  details (`master`, `test_field`) ";
                $db->SQL += "VALUES ($master_id, 'test')";
                $db->query($db->SQL);
                $db->close();
        }
}

By the way, in this example I use PHP and MySQL. If you have an another configuration I think you can simply adapt this example to your configuration.

Posted in codecharge, mysql, php

3 Responses to “How to execute additional SQL in After Execute family events in CCS”

Pocok:

December 8, 2010 at 5:58 pm

Great tutorial, thanks for sharing!
Eydun:

February 10, 2011 at 5:51 pm

What happens if two users execute the form simultaneously?
Vacheslav:

February 18, 2011 at 4:06 pm

Hi, Eydun!
Yes, you are right. Here’s a little bug! Instead of using a new connection to MySql you should use an existing connection. In this context, you should use $master->DataSource->query(“some sql”). Using the Record’s connection is safe because LAST_INSERT_ID() works on a per-connection basis.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id

I’ll modify this example as soon as possible. Thanks for comment!

STARIKOVS

How to execute additional SQL in After Execute family events in CCS

3 Responses to “How to execute additional SQL in After Execute family events in CCS”

Leave a Reply