Help with copying data from one table to another using different connections

Scriptcase 9 General
#1

I have 2 identical tables Table Remote and Table Local (apart from the name) on 2 different connections and want to overwrite the ‘Table Local’ with the content of ‘Table Remote’.
So I have a blank application and the process I am trying to use is… [LIST=1]

  • Truncate '[I][B]Table Local'[/B][/I] ([I][B]Done[/B][/I] using sc_exec_sql)
  • Look up all records from '[I][B]Table Remote[/B][/I]'. [I][B]Outstanding[/B][/I]
  • Insert all records into 'Table Local'. [I][B]Outstanding[/B][/I] [/LIST] I have gone round in circles and need some guidance if someone could help on the principle of using 2 connections in this situation.
    • #2

    I wrote a conversion routine (a bunch of them … it wasn’t easy) to convert an entire database - import from an old application written in something else to a new application written in SC. I created two connections - conn_mysql (target db) and conn_mysql1 (source db). When specifying tables in the source database, I prefixed the table name with the dbname. (dbname.table) You can prefix BOTH if you like but I found if I made the target the primary connection (mysql) it worked better. Here’s a sample:

    
//Truncate Target
$sql = "TRUNCATE TABLE entity_contact" ;
sc_exec_sql("$sql") ;

sc_log_add("Delete","Truncate contact") ;

//Initialize count
$rec_count = 0 ;

//array1 - acx_users
//========= Users List =========
$arr_contact = array();
sc_select(rs, "select id from acx4.acx_users");
while(!$rs->EOF)
{
    $arr_contact[] = $rs->fields[0];
    $rs->MoveNext();
}
$rs->Close();

//Show all records selected -- can comment this out later
//print_r($arr_contact) ;

//Process records found
foreach($arr_contact as $k => $user_id) // Sets the value of each record
{
    //get user info acx_users==============================
    $check_sql = "SELECT id, state, company_id, first_name, last_name, email, created_on, created_by_id"
       . " FROM acx4.acx_users"
       . " WHERE id = '$user_id'";
    sc_lookup(rs, $check_sql);

    if (isset({rs[0][0]}))     // Row found
    {
        $contact_id = {rs[0][0]};
        $state = {rs[0][1]};
        $entity_id = {rs[0][2]};
        $first_name = addslashes({rs[0][3]});        
        $last_name = addslashes({rs[0][4]});    
        $email = addslashes({rs[0][5]});            
        $created_on = {rs[0][6]};    
        $created_by_id = {rs[0][7]};
    }
            else     // No row found
    {
        $contact_id = '';
        $state = '';
        $entity_id = '';
        $first_name = '';
        $last_name = '';    
        $email = '';                    
        $created_on = '';
        $created_by_id = '';    
    }

    $newname = trim($first_name).' '.trim($last_name) ;    

        switch($state) {
            case 2:     //archived - not active
                $status = 0 ;
                $trash = 0 ;
            break;

            case 3:        //visible - active
                $status = 1 ;
                $trash = 0 ;
            break;

            default:     //others are in the trash
                $status = 0 ;
                $trash = 1 ;                    
            break;    
        }

    //get mobile info from acx_config_option_values============================
    $check_sql = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(value, ';', 1), ':', -1)"
       . " FROM acx4.acx_config_option_values"
       . " WHERE parent_id = '$user_id' and parent_type = 'user' and name = 'phone_mobile' ";
    sc_lookup(rs, $check_sql);

    if (isset({rs[0][0]}))     // Row found
    {
        $phone_mobile = {rs[0][0]};
    }
            else     // No row found
    {
        $phone_mobile = '';
    }

    //get work info from acx_config_option_values============================
    $check_sql = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(value, ';', 1), ':', -1)"
       . " FROM acx4.acx_config_option_values"
       . " WHERE parent_id = '$user_id' and parent_type = 'user' and name = 'phone_work' ";
    sc_lookup(rs, $check_sql);

    if (isset({rs[0][0]}))     // Row found
    {
        $phone_work = {rs[0][0]};
    }
            else     // No row found
    {
        $phone_work = '';
    }

    //get title info from acx_config_option_values============================
    $check_sql = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(value, ';', 1), ':', -1)"
       . " FROM acx4.acx_config_option_values"
       . " WHERE parent_id = '$user_id' and parent_type = 'user' and name = 'title' ";
    sc_lookup(rs, $check_sql);

    if (isset({rs[0][0]}))     // Row found
    {
        $title = addslashes({rs[0][0]});
    }
            else     // No row found
    {
        $title = '';
    }

//2016-0604:bh echo "$id, $active, $entity_id, $first_name, $last_name, $email, $created_date, $created_by, $phone_mobile, $phone_work, $title <br>" ;


    $insert_table  = 'entity_contact';      // Table name
    $insert_fields = array(   // Field list, add as many as needed
         'id' => "'$user_id'",
         'active' => "'$status'",
         'trash' => "'$trash'",            
         'entity_id' => "'$entity_id'",
         'name' => "'$newname'",        
         'email' => "'$email'",    
         'created_on' => "'$created_on'",    
         'created_by_id' => "'$created_by_id'",        
         'mobile' => "'$phone_mobile'",
         'work' => "'$phone_work'",                    
         'title' => "'$title'",            
     );

    // Insert record
    $insert_sql = 'INSERT INTO ' . $insert_table
        . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
        . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';

    sc_exec_sql($insert_sql);

    $rec_count = $rec_count + 1 ;

}//End foreach

sc_log_add("Insert","$rec_count rows inserted into entity_contact from acx_users") ;
    #3

    Thank you for your time on this, I will study with interest.
    Andy