Variables to use db connection name based upon form field value

Applications Forms
#1

Hello all,
I am trying to change existing connections during event onAfterInsert using the sc_exec_sql. Example:
If {myfield} = value ‘1’ then $connection = “databse1”
If {myfield} = value ‘2’ then $connection = “databse2”
If {myfield} = value ‘3’ then $connection = “databse3”

Used in event as follows.

/**

  • Update a record on another table
    */

// SQL statement parameters
$update_table = ‘recording_log’; // Table name
$update_where = “recording_id = ‘{recording_id}’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“audited = ‘Y’”,
);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ‘, $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql, $connection);
sc_commit_trans();
sc_redir(grid_audit1.php,’’,’_self’);

#2

I know that SC has done a webinar regarding connections a while ago. They haven’t published it yet on the webinar page (they are a bit behind), but you could ask them to send you a link to that webinar. I was on holliday so didn’t see it. In general I think you have to play with the sc_change_connection(“Old_Connection”, “New_Connection”) macro. I never used it though.

#3

I don’t think you have to mess around with sc_change_connection().
Just an idea:



$update_sql = "UPDATE ...";

switch({myfield})
{
    case 1:
        sc_exec_sql($update_sql,"conn_db1");
        break;
    case 2:
        sc_exec_sql($update_sql,"conn_db2");
        break;
}

jsb

#4

What the code is not liking is the double quotes around “conn_db1” in the sc_exec_sql string. Still working on it though. If I figure it out, I will post it here. This application must update records in different databases dependent upon a calculated field result in the form.

#5

You must have created a connection for each database you want to access in your project.
“conn_db1” is the actual connection name for the first db when you created the connection.
“conn_db2” is the connection name for the second db and so on.

You can not substitute those names with a variable and they have to be in quotes.

jsb

#6

Yes, connections exist in project. And as stated, in the sc_exec_sql, it is not liking using variable. So, end result, I cannot do this?

#7

Sc_change_connection allows vars.

Maybe a double change (before And after) could work

#8

Just tested as outlined in post #3.
It is working, no need for variables.

jsb