/* * 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 Bsp: adload mydata.unl adressen Lädt den Inhalt der Datei mydata.unl in die Tabelle adressen adunload 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 #include #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 \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); }