load_table.pl

{
/* __________________________________________________________________________
                                         Copyright (C) DataAspects, Inc. 1998
Objective: To load a relational database table with data from tab delimited 
           ASCII file.
Data file: Two columns separted by a tab.  Column 1 is a salt name and 
           Column 2 is the MW expressed as an integer.  The string 'NULL'
           is used for missing data.
Compile:   trimgen load_table.pl -a
           trimrun load_table
_____________________________________________________________________________ */
{
int      i;
int      x;
int      good_records;
int      bad_records;
int      cnt;
list     CL,LL;
list     outlist;

list_mod(CL,1,"salt.txt salt_trim.err");                 /* data file/error file     */
list_mod(CL,1,"variable");                               /* variable length records  */
list_mod(CL,1,chr(9)^^" NULL");                          /* delimiting tab           */
list_mod(CL,1,"C");                                      /* define salt name as char */
list_mod(CL,1,"N");                                      /* define MW as int         */ 

LL = list_open(CL,160);
list_view(LL,0);

connect(0,"net:user/pass@1958:xxx.xxx.xx.xx!/usr2/trim/bin/vtxhost.net,TRIM_HOME=/usr2/trim@@1959:xxx.x.x.xx(user/pass)!/usr2/trim/bin/vtxhost.ora,ORACLE_HOME=/usr4/oracle,ORACLE_SID=A");

commit(update);	                                            /* set db update mode  */
x=exec_sql("delete from salt");                             /* delete old rows     */
printf("deletes = "^^x);			            /* print rows deleted  */

commit();
for (i=0;i<list_rows(LL);i++) {	                            /*loop list            */
  cnt++;

  x= exec_sql("insert into salt values(:1,:2)",
               list_curr(LL,0),
               list_curr(LL,1));

   if (x==-1){ 
      printf("insert error on row "^^cnt);
      list_mod(cnt^^" "^^outlist,1,list_curr(LL,0));
      bad_records++;
      }  
  else
    good_records++;

  if(!(cnt %50)){                                         /* commit every 50 records */
    commit(update);
    printf("rows processed: "^^cnt); 
    printf("rows read to Oracle: "^^good_records);
    printf("rows read to salt.err: "^^bad_records);
    printf(to_char(SYSDATE,"YYYYMMDD HH:SS"));
    printf(""); 
   } 
list_next(LL);
}
commit();
printf("");
printf("Final insert results follow: ");
printf(to_char(SYSDATE,"YYYYMMDD HH:SS"));
printf("rows processed: "^^cnt); /* update user   */
printf("rows read to Oracle: "^^good_records);
printf("rows read to salt_db.err: "^^bad_records);

if (list_rows(outlist))
  list_file(outlist,"salt_db.err","a",0);
}