//  Script Name:  Clicked fo cb_pipe_1
// taken from CH20.PBL PB5. 0  into dialog window 'w_pipe_1'
//  Use the pipeline object pipe_1 to pipe a table from
//  a source database to a destination database.
//  Local variables:
Integer     li_error    // pipeline error code
Pipeline    lpipe_1     // pipeline variable
String      ls_dbms     // database type
String      ls_error    // pipeline error message
String      ls_inifile  // PB.INI, or other ini file with connect params
String      ls_section  // section in ini file with connect params
Transaction ltrans1     // transaction object for source database
Transaction ltrans2     // transaction object for target database

//  Initialization

SetPointer(HourGlass!)
st_status.Text = "Initializing..."
ls_inifile = "PB.INI"

//  Connect to the source database

st_status.Text = "Connecting to source database..."
ltrans1 = CREATE Transaction

ls_section = "Profile Video Store (SQL Server)"
ls_dbms = ProfileString(ls_inifile,ls_section,"DBMS","")
IF ls_dbms = "" THEN GOTO ErrorSourceDBMS

ltrans1.DBMS       = ls_dbms
ltrans1.Database   = &
      ProfileString(ls_inifile,ls_section,"Database","")
ltrans1.ServerName = &
      ProfileString(ls_inifile,ls_section,"ServerName","")
ltrans1.UserId     = &
      ProfileString(ls_inifile,ls_section,"UserId","")
ltrans1.DBPass     = &
      ProfileString(ls_inifile,ls_section,"DatabasePassword","")
ltrans1.LogId      = &
      ProfileString(ls_inifile,ls_section,"LogId","")
ltrans1.LogPass    = &
      ProfileString(ls_inifile,ls_section,"LogPassword","")
ltrans1.Lock       = &
      ProfileString(ls_inifile,ls_section,"Lock","")
ltrans1.DBParm     = &
      ProfileString(ls_inifile,ls_section,"DbParm","")

ltrans1.AutoCommit = False

CONNECT Using ltrans1;

IF ltrans1.SqlCode <>  0  THEN GOTO ErrorSourceConnect

//  Connect to the destination database

st_status.Text = "Connecting to destination database..."
ltrans2 = CREATE Transaction

ls_section = "Profile Video Store DB"
ls_dbms = ProfileString(ls_inifile,ls_section,"DBMS","")
IF ls_dbms = "" THEN GOTO ErrorDestinationDBMS

ltrans2.DBMS       = ls_dbms
ltrans2.Database   = &
      ProfileString(ls_inifile,ls_section,"Database","")
ltrans2.ServerName = &
      ProfileString(ls_inifile,ls_section,"ServerName","")
ltrans2.UserId     = &
      ProfileString(ls_inifile,ls_section,"UserId","")
ltrans2.DBPass     = &
      ProfileString(ls_inifile,ls_section,"DatabasePassword","")
ltrans2.LogId      = &
      ProfileString(ls_inifile,ls_section,"LogId","")
ltrans2.LogPass    = &
      ProfileString(ls_inifile,ls_section,"LogPassword","")
ltrans2.Lock       = &
      ProfileString(ls_inifile,ls_section,"Lock","")
ltrans2.DBParm     = &
      ProfileString(ls_inifile,ls_section,"DbParm","")

ltrans2.AutoCommit = False

CONNECT Using ltrans2;

IF ltrans2.SqlCode <>  0  THEN GOTO ErrorDestinationConnect

//  Delete the target table

st_status.Text = "Dropping old destination table..."
EXECUTE IMMEDIATE "DROP TABLE new_customers" Using ltrans2;

//  Set up the pipeline variable

st_status.Text = "Setting up the pipeline..."
lpipe_1 = CREATE Pipeline      // instantiate the variable
lpipe_1.DataObject = "pipe_1"  // associate the variable with the
                               // pipeline pipe_1 created with the
                               // Pipeline Painter

//  Execute the pipeline

st_status.Text = "Executing the pipeline..."
li_error = lpipe_1.Start(ltrans1, ltrans2, dw_1)

IF li_error <>  1  THEN GOTO ErrorPipeStart

//  Successful completion

st_status.Text = "Pipeline finished successfully."
MessageBox("Success", &
   "Pipeline executed successfully."                + &
   "~nRowsRead="    + String(lpipe_1.RowsRead)      + &
   "~nRowsWritten=" + String(lpipe_1.RowsWritten)   + &
   "~nRowsInError=" + String(lpipe_1.RowsInError))

RETURN

//  Error Handling

ErrorSourceDBMS:
MessageBox("Error on Connect to Source Database",   &
   "Unable to read DBMS name from the "      + &
   ls_section + " section of the INI file "  + &
   ls_inifile,                                 &
   stopsign!)
GOTO ErrorExit

ErrorSourceConnect:
MessageBox("Error on Connect to Source Database",     &
   "Database Profile Name: " + ls_section           + &
	"~nDBMS: " + ls_dbms                             + &
   "~nError Code: " + String(ltrans1.SqlDbCode)     + &
   "~nError Message: " + ltrans1.SqlErrText,          &
   stopsign!)
GOTO ErrorExit

ErrorDestinationDBMS:
MessageBox("Error on Connect to Destination Database",   &
   "Unable to read DBMS name from the "                + &
   ls_section + " section of the INI file "            + &
   ls_inifile,                                           &
   stopsign!)
GOTO ErrorExit

ErrorDestinationConnect:
MessageBox("Error on Connect to Destination Database",   &
   "Database Profile Name: " + ls_section              + &
	"~nDBMS: " + ls_dbms                                + &
   "~nError Code: " + String(ltrans2.SqlDbCode)        + &
   "~nError Message: " + ltrans2.SqlErrText,             &
   stopsign!)
GOTO ErrorExit

ErrorPipeStart:
CHOOSE CASE li_error
   CASE - 1 
      ls_error = "Pipe open failed (error # -1)"
   CASE - 2 
      ls_error = "Too many columns (error # -2)"
   CASE - 3 
      ls_error = "Table already exists (error # -3)"
   CASE - 4 
      ls_error = "Table does not exist (error # -4)"
   CASE - 5 
      ls_error = "Missing connection (error # -5)"
   CASE - 6 
      ls_error = "Wrong arguments (error # -6)"
   CASE - 7 
      ls_error = "Column mismatch (error # -7)"
   CASE - 8 
      ls_error = "Fatal SQL error in source (error # -8)"
   CASE - 9 
      ls_error = "Fatal SQL error in destination (error # -9)"
   CASE - 10 
      ls_error = "Maximum number of errors exceeded (error # -10)"
   CASE - 12 
      ls_error = "Bad table syntax (error # -12)"
   CASE - 13 
      ls_error = "Key required but not supplied (error # -13)"
   CASE - 15 
      ls_error = "Pipe already in progress (error # -15)"
   CASE - 16 
      ls_error = "Error in source database (error # -16)"
   CASE - 17 
      ls_error = "Error in destination database (error # -17)"
   CASE - 18 
      ls_error = "Destination database is read-only (error # -18)"
   CASE ELSE
      ls_error = "Error #" + String(li_error)
END CHOOSE
MessageBox("Error on Starting the Pipeline", ls_error, stopsign!)
GOTO ErrorExit

ErrorExit:
st_status.Text = "An error occurred."
SetPointer(Arrow!)
RETURN