Announcement

Collapse
No announcement yet.

Variables to use db connection name based upon form field value

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

  • Variables to use db connection name based upon form field value

    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      I don't think you have to mess around with sc_change_connection().
      Just an idea:

      Code:
      $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

      Comment


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

        Comment


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

          Comment


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

            Comment


            • #7
              Sc_change_connection allows vars.

              Maybe a double change (before And after) could work
              /Giuseppe

              Professional Scriptcase Services
              Some Customers opinions

              Comment


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

                jsb

                Comment

                Working...
                X