/*
 * adload.cpc - Lade/Entlade eine ASCII-Datei in eine ADABAS-DB-Tabelle ein
 *

eSQL/C Adabas Table Loader/Unloader
from http://sapdb.automatix.de/

Compilieren

1. cpc -H nocheck adload
2. cpclnk adload


3. erzeuge Unload durch:  ln adload adunload

--> adload lädt eine Datei in eine AdabasD-Tabelle
--> adunload führ ein Selectstatement durch und schreibt das
    Ergebnis adload-verständlich auf stdout


Tipps und Tricks

Enviroment:

JSWSERVERDB - Wenn belegt, nimm diese SERVERDB, sonst JSW
JSWSERVERNODE - Wenn belegt, nimm diesen SERVER, sonst nix
JSWDBUSER - Wenn belegt, login als hinterlegten User, sonst JSWDBA
JSWDBUSERWW - Wenn belegt, nehme dieses Passwort, sonst floyd


Usage:

adload <infile> <tablename>
Bsp: adload  mydata.unl adressen
     Lädt den Inhalt der Datei mydata.unl in die Tabelle adressen

adunload <selectstmt>
Bsp: adunload "select hier.dies, da.das, hier.jenes from hier, da where hier.plz like '28%' and hier.ident = da.ident"

Ausgabe erfolgt auf stdout.


*/

#include <stdio.h>
#include <stdlib.h>

#define COLNAMELEN   19
#define INBUFFLEN    8193
#define DBUFFLEN     4097

#define DELIMITER    '|'

#define JSW_UNKNOWN     0
#define JSW_CHAR        1
#define JSW_DATE        2
#define JSW_TIME        3
#define JSW_TIMESTAMP   4
#define JSW_BOOLEAN     10
#define JSW_INTEGER     11
#define JSW_FLOAT       12

FILE *indat;
FILE *outfile;

sqldatype udesc;
char workbuff[INBUFFLEN];
char dbuff[DBUFFLEN];

long count;

///////////////////////// UTILITIES ///////////////////////////////////

void dbfehler(char *wo)
{
 fprintf(stderr, "DB-Fehler %ld (%s)\n\t%s (%ld)\n", 
                 sqlca.sqlcode, 
                 sqlca.sqlerrmc, 
                 wo,
                 count);
}

struct colinfo {
 char colName[COLNAMELEN + 1];
 unsigned char *colData;
};

struct colList {
 struct colinfo column;
 struct colList *next;
};

struct colList *cols;

struct colList *newColInfo()
{
  struct colList *curr;

  if(!cols) {
   cols = (struct colList *) calloc(1, sizeof(struct colList));
   curr = cols;
  }
  else {
   curr = cols;
   while(curr->next) {
    curr = curr->next;
   }
   curr->next = (struct colList *) calloc(1, sizeof(struct colList));
   curr = curr->next;
  }

  return curr;
}

void delete_cols()
{
  struct colList *curr, *next;

  curr = cols;
 
  while(curr->next) {
   next = curr->next;
   if(curr->column.colData) {
    free(curr->column.colData);
   }
   free(curr);
   curr = next;
  }
  if(curr) {
   free(curr);
  }
}

struct colList *findCol(char *name)
{
 struct colList *curr;

 curr = cols;

 while(curr) {
  if(!strncmp(name, curr->column.colName, strlen(curr->column.colName))) {
   return curr;
  }
  curr = curr->next;
 }
 return curr;
}

unsigned char *colDataAdr(char *name)
{
 struct colList *column;

 column = findCol(name);

 if(column) {
   return column->column.colData;
 }
 
 return (unsigned char *) 0;
}

int connectToDB()
{
EXEC SQL BEGIN DECLARE SECTION;
  char connectstr[128];
  char db[19];
  char node[65];
EXEC SQL END DECLARE SECTION;
  char *p;
  char *user; 
  char *passw;

  p = user = passw = 0;

  p = getenv("JSWSERVERDB");
  if((p) && (*p)) {
   strncpy(db, p, 18);
   db[18] = '\0';
  }
  else {
   strcpy(db, "JSW");
  }

  p = (char *) getenv("JSWSERVERNODE");

  if((!p) || (!*p)) {
   strcpy(node, "\0");
  }
  else {
   strcpy(node, p);
 }


EXEC SQL set serverdb :db on :node;

 if(sqlca.sqlcode) {
  dbfehler("set serverdb");
  fprintf(stderr, "ggf. JSWSERVERDB und JSWSERVERNODE exportieren\n");
  return 1;
 }

 user = getenv("JSWDBUSER");
 passw = getenv("JSWDBUSERPW");

 sprintf(connectstr, "connect %s identified by %s isolation level 0", 
         (user) ? user : "JSWDBA",
         (passw) ? passw : "floyd");

 EXEC SQL execute immediate :connectstr;

 if(sqlca.sqlcode) {
  dbfehler("connect");
  fprintf(stderr, "ggf. JSWDBUSER und JSWDBUSERPW exportieren\n");
  return 1;
 }

 if(getenv("ADATRACE")) {
  EXEC SQL set trace long;
 }

 return 0;
}

int set_data_area()
{
   sqlvartype *actvar;
   int i;
   int rv = 0;

   for(i = 0; i < udesc.sqln; i++) {

     actvar = &udesc.sqlvar[i];

     switch (actvar->coltype) {            /* welcher DDB/4-Variablen-Typ ? */
        case 0:                            /* Typ ist fixed oder      */
        case 16:                           /* Typ ist integer oder    */
        case 19:                           /* Typ ist integer oder    */
        case 1:                            /* Typ ist float           */
        case 23:                           /* Typ ist Boolean         */

         if(actvar->colfrac == 0)  {       /* keine Nachkommastellen  */
            actvar->hostvartype = 1;       /* SQL-Variablen-Typ: int  */

            actvar->hostvaraddr =          /* Zuweisen der Adresse    */
            colDataAdr(actvar->colname);

         }
         else {                            /* mit Nachkommastellen    */
            actvar->collength = 18;        /* max. Laenge eines fixed */
            actvar->colfrac = 8;           /* Laenge Nachkommastellen */
                                           /* kein Effekt fuer float  */
            actvar->hostvartype = 3;       /* SQL-Var-Typ: double     */

            actvar->hostvaraddr =
            colDataAdr(actvar->colname);
         }
         break;

        case 2:                            /* Typ ist char            */
        case 4:                            /* Typ ist date            */
        case 5:                            /* Type ist TIME           */
        case 8:                            /* Type ist TIMESTAMP      */

         actvar->hostvartype = 7;        /* SQL-Var-Typ: char ...[] */
         actvar->collength++;

         actvar->hostvaraddr =
            colDataAdr(actvar->colname);

         break;

        default:               /* Noch??.. sind keine anderen Daten-  */
                               /* typen erlaubt (z.B.time)            */

         fprintf(stderr, "Fehlerhafter Datentyp in setdataarea !\n(%s: %d)",
                    actvar->colname, actvar->coltype);
         rv = 1;

       break;
    }
  }

  return rv;
}

void prewrite()
{
 int i;

 for(i = 0; i < udesc.sqln; i++) {
  if((udesc.sqlvar[i].coltype == 4) ||
     (udesc.sqlvar[i].coltype == 5) ||
     (udesc.sqlvar[i].coltype == 8)) {
   if(!*(char *) udesc.sqlvar[i].hostvaraddr) {
     udesc.sqlvar[i].hostindicator = -1;
   }
   else {
     udesc.sqlvar[i].hostindicator = 0;
   }
  }
 }
}

void postread()
{
 int i;

 for(i = 0; i < udesc.sqln; i++) {
  if((udesc.sqlvar[i].hostindicator == (-1)) &&
     (udesc.sqlvar[i].hostvaraddr)) {
   udesc.sqlvar[i].hostindicator = 0;
   switch(udesc.sqlvar[i].hostvartype) {
    case 1:
     *(long *) udesc.sqlvar[i].hostvaraddr = 0L;
     break;
    case 3:
     *(double *) udesc.sqlvar[i].hostvaraddr = 0.0;
      break;
    case 7:
     strncpy((char *) udesc.sqlvar[i].hostvaraddr, "\0", udesc.sqlvar[i].collength);
     break;
   }
  }
 }

}


//////////// LOAD ///////////////////////////////////////////////////////

int buildcolList(char *table)
{
EXEC SQL BEGIN DECLARE SECTION;
 char tabname[19];
 char colname[19];
 char coltype[11];
 int collen, colfrac;
 int ind[4];
EXEC SQL END DECLARE SECTION;
 int colCount;
 int jswColType;
 struct colList *cEntry;
 char *p;

 strcpy(tabname, table);
 p = tabname;
 while(*p) {
  *p = toupper(*p);
  p++;
 }

 EXEC SQL
   select columnname, datatype, len, dec
     from columns
    where owner = 'JSWDBA'
      and tablename = :tabname;

  EXEC SQL fetch into :colname :ind[0], :coltype :ind[1],
                      :collen :ind[2], :colfrac :ind[3];

  colCount = 0;

  while(!sqlca.sqlcode) {

   cEntry = newColInfo();
   if(!cEntry) {
    return 1;
   }

   strcpy(cEntry->column.colName, colname);

   jswColType = JSW_UNKNOWN;

   if(ind[3] < 0) {
    colfrac = 0;
   }

   if(!strcmp(coltype, "CHAR")) {
    jswColType = JSW_CHAR;
   }
   else if(!strcmp(coltype, "DATE")) {
    jswColType = JSW_DATE;
   }
   else if(!strcmp(coltype, "TIME")) {
    jswColType = JSW_TIME;
   }
   else if(!strcmp(coltype, "TIMESTAMP")) {
    jswColType = JSW_TIMESTAMP;
   }
   else if(!strcmp(coltype, "FIXED")) {
    if(!colfrac) {
     jswColType = JSW_INTEGER;
    }
    else {
     jswColType = JSW_FLOAT;
    }
   }
   else if(!strcmp(coltype, "FLOAT")) {
    jswColType = JSW_FLOAT;
   }
   else if(!strcmp(coltype, "INTEGER")) {
    jswColType = JSW_INTEGER;
   }
   else if(!strcmp(coltype, "BOOLEAN")) {
    jswColType = JSW_BOOLEAN;
   }

   switch(jswColType) {
    case JSW_CHAR:
    case JSW_DATE:
    case JSW_TIME:
    case JSW_TIMESTAMP:
     cEntry->column.colData = (unsigned char *) calloc(1, (collen + 1));
     break;
    case JSW_BOOLEAN:
    case JSW_INTEGER:
     cEntry->column.colData = (unsigned char *) calloc(1, sizeof(long ));
     break;
    case JSW_FLOAT:
     cEntry->column.colData = (unsigned char *) calloc(1, sizeof(double ));
     break;
    default:
     fprintf(stderr, "Falscher Datentyp: Col: %s Typ: %s\n", colname, coltype);
     return 1;
   }

   colCount++;

   EXEC SQL fetch into :colname :ind[0], :coltype :ind[1],
                       :collen :ind[2], :colfrac :ind[3];
  }

  if(sqlca.sqlcode != 100) {
   dbfehler("buildcolList -fetch-");
   return 1;
  }

  if(!colCount) {
   fprintf(stderr, "Keine Tabellinfo für %s gefunden !\n", tabname);
   return 1;
  }

EXEC SQL
  close;

  udesc.sqlmax = colCount;

  return 0;
}



int readData(char *table)
{
EXEC SQL BEGIN DECLARE SECTION;
 char istmt[1024];
EXEC SQL END DECLARE SECTION;
 int i;
 char *rv;
 unsigned char *p;
 sqlvartype *actvar;

 sprintf(istmt, "select * from %s", table);
 p = istmt;
 while(*p) {
  *p = toupper(*p);
  p++;
 }

EXEC SQL
 prepare statement from :istmt;

 if(sqlca.sqlcode) {
  dbfehler("prepare statement");
  return 1;
 }

EXEC SQL
 execute statement;

 if((sqlca.sqlcode) && (sqlca.sqlcode != 100)) {
  dbfehler("execute statement");
  return 1;
 }

EXEC SQL
 prepare res_fetch from 'fetch using descriptor &udesc';

 if(sqlca.sqlcode) {
  dbfehler("prepare res_fetch");
  return 1;
 }


EXEC SQL
 describe res_fetch into &udesc;

 if(sqlca.sqlcode) {
  dbfehler("describe res_fetch");
  return 1;
 }

 if(set_data_area()) {
  return 1;
 }

 sprintf(istmt, "insert into %s values (?", table);
 for(i = 1; i < udesc.sqln; i++) {
  strcat(istmt, ",?");
 }
 strcat(istmt, ")");
 p = istmt;
 while(*p) {
  *p = toupper(*p);
  p++;
 }


EXEC SQL prepare ins from :istmt;

 if(sqlca.sqlcode) {
  dbfehler("prepare ins"); 
  return 1;
 }

 while(rv = fgets(workbuff, INBUFFLEN,  indat)) {

  for(i = 0; i < udesc.sqln; i++) {
 
   actvar = &udesc.sqlvar[i];
   extrakt(i);
 
   switch (actvar->hostvartype) {   
      case 1:                            /* LONG, BOOL etc          */
       *(long *) actvar->hostvaraddr = atol(dbuff);
       break;
      case 3:                            /* DOUBLE                  */
      p = dbuff;
       while(*p) {
        if(*p == ',')
         *p = '.';
        p++;
       }
       *(double *) actvar->hostvaraddr = atof(dbuff);
       break;
      case 7:                            /* Typ ist char date etc   */

       strncpy(actvar->hostvaraddr, dbuff, actvar->collength);
       break;
 
       default:  
 
        fprintf(stderr, "Fehlerhafter Datentyp in readdata !\n(%s: %d)",
                        actvar->colname, actvar->hostvartype);
        return 1;
 
     }
   }
 
   prewrite();

EXEC SQL
   execute ins using descriptor &udesc;

   if(sqlca.sqlcode) {
    dbfehler("execute ins");
    return 1;
   }

   count++;

  }

  fprintf(stderr, "ADLOAD: %ld Sätze eingelesen\n", count);

  return 0;
}

int extrakt(int i)
{
   char *p;
   char *p2;
   int counter;
   int skip;

   p = dbuff;
   p2 = workbuff;

   counter = 0;
   skip  = 0;
   *p = '\0';

   while(*p2 != '\0') {

    if(counter == i) {               // Feld Nr. x gefunden

     if((*p2 == '\\') &&             // Ende des Feldes suchen
        ((*(p2 + 1) == '|') ||
         (*(p2 + 1) == '\\'))) {
      p2++;
      skip = 1;
     }
     if((*p2 == '|') &&
        (!skip)) {
      *p = '\0';                     // Ende gefunden

      p = p2 = dbuff;                // ersetze '\n';
      while(*p) {
       if((*p == '\\') &&
          (*(p + 1) == 'n')) {
        p++;
        *p = '\n';
       }
       *p2 = *p;
       p++;
       p2++;
      }
      *p2 = *p;

      return(0);
     }
     skip = 0;
     *p++ = *p2++;

    }
    else {                             // Feld Nr. n noch nicht gefunden
     if((*p2 == '\\') &&
        (*(p2 + 1) == '|')) {
       p2++;
       skip = 1;
     }
     if((*p2 == '|') &&
        (!skip)) {
       counter++;
     }
     skip = 0;
     p2++;
    }
   }
}

int load(int argc, char **argv) 
{
 int rv;

 cols = 0;

 if(argc != 3) {
  fprintf(stderr, "LOAD: usage: load flatfile table\n");
  exit(1);
 }

 indat = fopen(argv[1],"rb");
 if(!indat) {
  fprintf(stderr,"LOAD: Datei %s konnte nicht geoeffnet werden !\n",argv[1]);
  exit(1);
 }

 if(connectToDB()) {
  fclose(indat);
  exit(1);
 }
  
 if(buildcolList(argv[2])) {
  fclose(indat);
  exit(1);
 }

 if(readData(argv[2])) {
  fclose(indat);
  exit(1);
 }

 fclose(indat);

EXEC SQL COMMIT WORK RELEASE;
  return 0;
}

/////////////////////////////////////////////////////////////////////////

////////// UNLOAD ///////////////////////////////////////////////////////

int buildColListfromSqlda()
{
   sqlvartype *actvar;
   int i;
   int rv = 0;
   struct colList *cEntry;
 
   for(i = 0; i < udesc.sqln; i++) {

     actvar = &udesc.sqlvar[i];

     cEntry = newColInfo();
     if(!cEntry) {
      return 1;
     }

     strcpy(cEntry->column.colName, actvar->colname);

     switch (actvar->coltype) {            /* welcher DDB/4-Variablen-Typ ? */
        case 0:                            /* Typ ist fixed oder      */
        case 16:                           /* Typ ist integer oder    */
        case 19:                           /* Typ ist integer oder    */
        case 1:                            /* Typ ist float           */
        case 23:                           /* Typ ist Boolean         */

         if(actvar->colfrac == 0)  {       /* keine Nachkommastellen  */
            cEntry->column.colData = (unsigned char *) 
                                     calloc(1, sizeof(long ));

         }
         else {                            /* mit Nachkommastellen    */
            cEntry->column.colData = (unsigned char *) 
                                     calloc(1, sizeof(double ));

         }
         break;

        case 2:                            /* Typ ist char            */
        case 4:                            /* Typ ist date            */
        case 5:                            /* Type ist TIME           */
        case 8:                            /* Type ist TIMESTAMP      */

         cEntry->column.colData = (unsigned char *) 
                                  calloc(1, (actvar->collength + 1));
 
         break;

        default:               /* Noch??.. sind keine anderen Daten-  */
                               /* typen erlaubt (z.B.time)            */

         fprintf(stderr, "Fehlerhafter Datentyp in setdataarea !\n(%s: %d)",
                    actvar->colname, actvar->coltype);
         rv = 1;

       break;
    }
  }

  return rv;
}

void outstr(char *str, int len)
{
  char *cp;

  cp = str;
  
  while(*cp) {

   if(((*cp == DELIMITER) ||
       (*cp == '\\')) &&
       (DELIMITER != '\\')) {
        fputc('\\', outfile);
   }

   if(*cp == '\n') {
    fputc('\\', outfile);
    *cp = 'n';
   }

   fputc(*(cp++), outfile);
 }

}


void outputRow()
{
   sqlvartype *actvar;
   int i;
   int rv = 0;
   char numstr[81];

   for(i = 0; i < udesc.sqln; i++) {

     actvar = &udesc.sqlvar[i];

     switch (actvar->hostvartype) { 
        case 1:                            /* integer     */
         sprintf(numstr, "%ld", *(long *) actvar->hostvaraddr);
         fputs(numstr, outfile);
         break;
        case 3:
         sprintf(numstr, "%f", *(double *) actvar->hostvaraddr);
         fputs(numstr, outfile);
         break;
        case 7:
         outstr(actvar->hostvaraddr, actvar->collength);

         break;
        default:               /* Noch??.. sind keine anderen Daten-  */
                               /* typen erlaubt (z.B.time)            */

         fprintf(stderr, "Fehlerhafter Datentyp in outputRow !\n(%s: %d)",
                    actvar->colname, actvar->hostvartype);
       break;
    }

    fputc(DELIMITER, outfile);
  }
  fputc('\n', outfile);
}


int doUnload(char *selectstmt)
{
EXEC SQL BEGIN DECLARE SECTION;
  char *stmt;
EXEC SQL END DECLARE SECTION;

  stmt = selectstmt;

EXEC SQL 
  prepare unlselect from :stmt;

  if(sqlca.sqlcode) {
   dbfehler("prepare unlselect");
   return 1;
  }

EXEC SQL 
  describe unlselect into &udesc;

  if(sqlca.sqlcode) {
   dbfehler("describe unlselect");
   return 1;
  }

  if(buildColListfromSqlda()) {
   return 1;
  }

  if(set_data_area()) {
   return 1;
  }

EXEC SQL
 execute unlselect using descriptor &udesc;

 if((sqlca.sqlcode) && (sqlca.sqlcode != 100)) {
  dbfehler("execute unlselect");
  return 1;
 }

 if(sqlca.sqlcode == 100) {
  fprintf(stderr, "ADUNLOAD: keine Datensätze gefunden\n");
  return 0;
 }

EXEC SQL
 prepare unlfetch from 'fetch using descriptor &udesc';

 if(sqlca.sqlcode) {
  dbfehler("prepare unlfetch");
  return 1;
 }

 udesc.sqlmax = 200;

EXEC SQL
 describe unlfetch into &udesc;

 if(sqlca.sqlcode) {
  dbfehler("describe unlfetch");
  return 1;
 }

 if(buildColListfromSqlda()) {
  return 1;
 }

 if(set_data_area()) {
  return 1;
 }

EXEC SQL
 execute unlfetch;

 while(!sqlca.sqlcode) {

  count++;

  postread();

  outputRow();

  EXEC SQL
   execute unlfetch;
 }

 if(sqlca.sqlcode != 100) {
  dbfehler("execute unlfetch");
  return 1;
 }

 fprintf(stderr, "ADUNLOAD: %ld Datensätze ausgelagert\n", count);
 return 0;

}


int unload(int argc, char **argv)
{
  if(argc != 2) {
   fprintf(stderr, "UNLOAD: usage: unload <selectstatement>\n");
   return 1;
  }

  if(connectToDB()) {
   return 1;
  }

  if(doUnload(argv[1])) {
   return 1;
  }

EXEC SQL COMMIT WORK RELEASE;
  return 0;
}

/////////////////////////////////////////////////////////////////////////

 

/////////////////////////////////////////////////////////////////////////

main(int argc, char **argv) 
{
  count = 0;
  outfile = stdout;

  if(!strcmp(argv[0], "adload")) {
   exit(load(argc, argv));
  }

  if(!strcmp(argv[0], "adunload")) {
   exit(unload(argc, argv));
  }
 
  exit(1);
}