OpenVMS Source Code Demos
mysql_demo04.c
//=============================================================================================================================== // title : mysql_demo04.c // author : Neil Rieck // : Waterloo, Ontario, Canada. // http://neilrieck.net MAILTO:[email protected] // created : 2014-03-08 // purpose : real work demo for MariaDB (an alternative fork of MySQL) // read a TSV file of tab-delimited data then use it to populate a MySQL table // caveat : Do not import data into MySQL this way unless you wish to developing additional programming skills // See this link for a better way: http://dev.mysql.com/doc/refman/5.1/en/load-data.html // target : for MariaDB on OpenVMS-8.4 // vms-build : @mysql_demo.com mysql_demo4.c (see mysql_demo.com for details) // references: http://zetcode.com/db/mysqlc/ (MySQL C API programming tutorial) // http://www.yolinux.com/TUTORIALS/MySQL-Programming-C-API.html // http://code-reference.com/c/mysql/mysql.h/mysql_real_connect // ver who when what // --- --- ------ --------------------------------------------------------------------------------------------------------------- // 100 NSR 140308 1. original effort (only reads then parses a TSV file for tabs) // 101 NSR 140309 1. inserts parsed data into a MySQL database // NSR 140310 2. one bug-fix and a few tweaks // 102 NSR 140311 1. added a debug mode // 2. added a verbose mode // 3. now escape a single quote (O'NEILL) by sending two (as per the SQL spec) // 103 NSR 240415 1. changes for use with "LibMariaDB for VSI OpenVMS" //=============================================================================================================================== #define __NEW_STARLET 1 // enable strict starlet (>= OpenVMS70) #define PROGRAM_VER "mysql_demo4_102.3" // <<<--- change as required #define BUFFER_SIZE 32700 // RMS file lines can't be larger than this #define MAX_FIELDS 99 // would we ever see more than 99 fields? #define EXPECTED_FIELDS 33 // sanity test #1 #define WRITE_FIELDS 28 // because we don't want to write the last 5 (in real life // we might xfer all fields then drop columns via SQL) // // includes // // ----------------------------------------------------------------------------- // CAVEAT: // 1) original API instructions said to include <my_global.h> // 2) newer API instructions say to include <ma_global.h> and invoke it first. // 3) other instructions say to only include these files in server apps. Since // these are client apps, I disabled both for simplicity (everythings seems // to compile properly without them in 2024). Enable one if something is // missing during compile. // -------------------------------------------------------------- //#include <ma_global.h> // LibMariaDB v3 for VSI OpenVMS // -------------------------------------------------------------- //#include <my_global.h> // LibMariaDB v2 for VSI OpenVMS // MariaDB-5.5-63 from VSI // MariaDB from Mark Berryman // ----------------------------------------------------------------------------- #include <stdio.h> #include <stdlib.h> #include <string.h> #include <mysql.h> // // forward declarations // long breakout_fields(char*,char*[],long*); // void display_results(); // display results of MySQL query void send_sql_cmd(); // void my_fgets(char* a, int b, FILE* c); // void remove_trailing_ws(char* a); // void escape_single_quotes(char* a); // // // MYSQL global variables // MYSQL *gCon; // connection stuff MYSQL_RES *gRes; // Result MYSQL_ROW gRow; // used in reading response long mysql_err; // long mysql_total_errors; // // // my global variables for MySQL // char db_user[128]; // database user name char db_pass[128]; // database password char database[128]; // desired database char db_table[128]; // desired table (to insert into) char sql_cmd[999]; // the name says it all long db_t_mode; // // // my global variables for importing flat file data // long lcount; // line count long fcount; // field count long wcount; // write count long vfymode; // verify mode long dbgmode; // debug mode long verbose; // long max_fields; // filebuf char filebuf[BUFFER_SIZE]; // char *fields[MAX_FIELDS]; // char keyboard[128]; // char fs[] = "profile_main.tsv"; // a tab-delimited file created by profiledb.exe FILE *fp; // file pointer //======================================================================================== // Main //======================================================================================== int main( int *argc, char **argv) { fprintf(stderr,"-i-\nProgram: %s\n",PROGRAM_VER); // // // init variables // db_user[0] = '\0'; // db_pass[0] = '\0'; // database[0] = '\0'; // db_table[0] = '\0'; // db_t_mode = 0; // mysql_total_errors = 0; // verbose = 0; // dbgmode = 0; // // printf("debug level:\n"); // printf(" 0 off\n"); // printf(" 1 low\n"); // printf(" 2 medium\n"); // printf(" 3 high\n"); // printf("choice? (default=0) "); // keyboard[0] = '\0'; // fgets(keyboard,sizeof(keyboard), stdin); // switch (keyboard[0]) { // case '0': // case '1': // case '2': // case '3': // dbgmode = atoi(&keyboard[0]); // break; // default: // dbgmode = 0; // } // // // make sure mariadb is running // gCon = mysql_init( NULL); // hello, are you there? if ( gCon == NULL) { // nope fprintf(stderr,"-e-can't connect to MySQL\n"); // return -6; // exit with VMS-E (error) } // // get database specific params // printf("db user? (eg. root) : "); // my_fgets(db_user,sizeof(db_user),stdin); // // printf("db pass? : "); // my_fgets(db_pass,sizeof(db_pass),stdin); // // printf("note: mysql=master database\n"); // printf("database? (eg. mysql) : "); // mysql is the master table my_fgets(database,sizeof(database),stdin); // // if (mysql_real_connect( // connect to Maria/MySQL gCon, // NULL, // localhost db_user, // user db_pass, // pass database, // database 0, // port NULL, // unix_socket 0) // client_flag == NULL) // NULL means failure { mysql_err = mysql_errno( gCon); // fprintf(stderr,"-e-mysql_real_connect() failed:\nError: %u (%s)\n", mysql_err, mysql_error(gCon)); return -6; // exit with VMS-E (error) }else{ printf("-i-database connected\n"); // printf("MySQL Connection Info : %s \n", mysql_get_host_info(gCon)); // printf("MySQL Client Info : %s \n", mysql_get_client_info()); // printf("MySQL Server Info : %s \n", mysql_get_server_info(gCon)); // } // // get the name of the table we will inserting into // printf("table? (eg. profile) : "); // my_fgets(db_table,sizeof(db_table),stdin); // if (strlen(db_table)==0) { // printf("-e-oops, table is blank\n"); // exit(1); // } // // issue an SQL "describe" to make sure the table exists // sprintf(sql_cmd,"%s%s","describe ",db_table); verbose = 1; // send_sql_cmd(); // verbose = 0; // // // let him see the results of the table DESCRIBE // printf("continue with data importation? (y/N) "); // my_fgets(keyboard,sizeof(keyboard), stdin); // switch (keyboard[0]) { case 'Y': case 'y': break; default: exit(EXIT_FAILURE); // } // // get the name of the table we will inserting into // get_t_mode: printf("note: transaction mode is the opposite of auto-commit\n"); printf("transaction mode ? (Y/n) "); // my_fgets(keyboard,sizeof(keyboard),stdin); // switch (keyboard[0]){ case 'Y': case 'y': sprintf(sql_cmd,"start transaction"); // send_sql_cmd(); // db_t_mode = 1; // we will need to COMMIT break; case 'N': case 'n': db_t_mode = 0; // auto-commit is enabled by default break; default: printf("-e-bas input\n"); goto get_t_mode; } // // okay so let's get on with inserting data (or not) // lcount = 0; // line count fcount = 0; // field count wcount = 0; // write count vfymode= 0; // verify mode max_fields = MAX_FIELDS; // filebuf char filebuf[BUFFER_SIZE]; // char *fields[MAX_FIELDS]; // char keyboard[128]; // // printf("-i-program: MYSQL_DEMO4\n"); // code starts here printf("-i-opening file: %.60s\n", fs); // string output is limited to 60 characters fp = fopen( fs, // fully qualified file spec (directory + name) "r", // access: read "mrs=32700" // maxiumum record size: 32700 ); if (fp == NULL) { // perror("-e-could not open file"); // exit(EXIT_FAILURE); // } // rewind(fp); // probably not necessary but do it anyway filebuf[0] = '\0'; // init for first pass (just good form) while (fgets(filebuf, sizeof(filebuf), fp)!=NULL){ // lcount++; // printf("-i-rec-num: %ld\n", lcount); // printf("-i-dat-rec: %s\n", &filebuf[0]); // fcount = breakout_fields(&filebuf[0],&fields[0],&max_fields); // if (fcount != EXPECTED_FIELDS) { // printf("-e-sanity test failure: detected %ld fields but found %ld\n",fcount,EXPECTED_FIELDS); printf("hit <enter> to continue..."); // fgets(keyboard,sizeof(keyboard), stdin); // vfymode = 0; // reset (back to prompting) }else{ // printf("-i-sanity test pass : detected %ld fields\n",fcount); // } // if (vfymode<=1) { // if (fcount>0) { // if we stored anything... for (int i=0; i<fcount; i++) { // printf("-i-field: %3ld data: %s\n",i,fields[i]); // if (i>=(WRITE_FIELDS-1)) // in this version we do not push all fields break; // so allow an early exit } // }else{ // printf("-w-nothing to write\n"); // } // printf("=======================================================\n"); try_again2: // printf("action menu:\n"); // printf(" 0 skip this line (perhaps its column-title line)\n"); // printf(" 1 write this line (but keep prompting)\n"); // printf(" 2 skip this line and all the ones behind it (no more prompting unless error)\n"); printf(" 3 write this line and all after it (no more prompting unless error)\n"); printf(" Q quit (something has gone horribly wrong)\n"); // printf("enter nothing to default to: %ld\n",vfymode); // printf("choice? "); // keyboard[0] = '\0'; // fgets(keyboard,sizeof(keyboard), stdin); // switch (keyboard[0]) { // case '0': // case '1': // case '2': // case '3': // vfymode = atoi(&keyboard[0]); // printf("-i-new mode: %ld\n",vfymode); // break; // case 'Q': // case 'q': // return(1); // default: // if (atoi(&keyboard[0])==0) { // he just hit <enter> so stay in same mode }else{ // printf("-i-hack2: %ld\n",atoi(&keyboard[0])); // printf("-?-bad input, try again\n"); // goto try_again2; // } // } // } // // // okay, time to push the data to mysql // if ( (fcount>0) && // ((vfymode==1) || (vfymode==3)) ) { // sprintf(sql_cmd,"insert into %s values(", db_table); // INSERT INTO for (int i=0; i<fcount; i++) { // if (i>0) strcat(sql_cmd, ","); // inter-field separator strcat(sql_cmd, "'"); // opening quote remove_trailing_ws(fields[i]); // remove trailing white space escape_single_quotes(fields[i]); // strcat(sql_cmd, fields[i]); // data strcat(sql_cmd, "'"); // closing quote if (i>=(WRITE_FIELDS-1)) // in this version we do not push all fields break; // so allow an early exit } // strcat(sql_cmd, "); "); // end of INSERT INTO if (dbgmode>=3) // printf("-i-sql: %s\n", sql_cmd); // send_sql_cmd(); // really do it !!! wcount++; // record this as a put (but it could fail) } } // // okay we are done so display our stats // printf("==================================================\n"); // printf("conversion complete\n"); printf("-i- lines read from flat file: %ld\n",lcount); printf("-i- records written to mysql : %ld\n",wcount); printf("-i- total mysql errors : %ld\n",mysql_total_errors ); if ((wcount>0)&&(db_t_mode==1)) { my_loop3: printf("commit or rollback? (c/r) "); // my_fgets(keyboard,sizeof(keyboard), stdin); // switch (keyboard[0]) { case 'C': case 'c': sprintf(sql_cmd,"%s","commit;"); // send_sql_cmd(); break; case 'R': case 'r': sprintf(sql_cmd,"%s","rollback;"); // send_sql_cmd(); break; default: printf("-e-bad choice\n"); goto my_loop3; } } // // time to go home // printf("-i-closing connection\n"); // mysql_free_result(gRes); // mysql_close( gCon); // // return(1); // exit with VMS-S (success) } //-------------------------------------------------------------------------------- // scan string 's' looking for character 'c' or EOL marker //-------------------------------------------------------------------------------- strscan(char* s, char c, long x) { long i; // i = strlen(s); // if (x < i) // i = x; // for (; s[i]!=c && s[i]!='\0'; i++); // return i; // } //-------------------------------------------------------------------------------- // breakout_fields() // scan line (our input line of tab-delimited data) looking for no more than max_fields // stuff the results into fields[] // caveat: like everything else in c, we will update our subscript after insertion (insert into 0) //------------------------------------------------------------------------------- long breakout_fields(char* line, char* fields[], long* max_fields) { char fdata[999]; // fdata[0] = '\0'; // init long i = 0; // long f = 0; // long m1 = 0; // long m2 = 0; // long skip0 = 0; // bad data flag long x; long z = strlen(line); // // // drop any trailing paper commands // if (z>0) { switch (line[z-1]) { case '\r': case '\n': line[z-1] = '\0'; z--; break; default: } } // // scan the line looking for tabs // while (i<=z) { // switch(line[i]) { // test a character case '\0': // EOL marker z = 0; // force loop to exit (after next chunk) case '\t': // htab if (i==0) { // if first character is a tab (it could happen) skip0 = 1; // then we must skip over it when we do the extract } m2 = i; // set marker 2 if ((m2>0)&&(m2>m1)){ // if ((f==0)&&(skip0==0)){ // if first field AND data looks okay x = m2-m1; // strncpy(fdata,&line[m1] ,x); // fdata[x] = '\0'; // }else{ // x = m2-m1-1; // strncpy(fdata,&line[m1+1],x); // fdata[x] = '\0'; // } fields[f] = strdup(fdata); // fdata[0] = '\0'; // f++; // update our field counter } m1 = m2; // slide m1 up to m2 break; // default: break; // } i++; // next character please if (*max_fields<f) // if we have hit our limit z = 0; // then force loop to exit } #define DEBUG9 0 #if (DEBUG9==1) // // debug code: let's see the contents of our data array // if (f>0){ // if we stored anything... for (i=0; i<f; i++){ // printf("-i-debug: idx: %3ld data: %s\n",i,fields[i]); // } // } // #endif return(f); } //-------------------------------------------------------------------------------- // display_results (of sql query) //-------------------------------------------------------------------------------- void display_results() { unsigned int num_fields; unsigned int i; unsigned long *lengths; // printf("---------------------------------------- start of response\n"); gRes = mysql_use_result( gCon ); num_fields = mysql_num_fields(gRes); while ((gRow = mysql_fetch_row(gRes))) { lengths = mysql_fetch_lengths(gRes); for(i = 0; i < num_fields; i++) { printf("[%.*s] ", (int) lengths[i], gRow[i] ? gRow[i] : "NULL"); } printf("\n"); } printf("------------------------------------------ end of response\n"); } //-------------------------------------------------------------------------------- // send sql cmd (to mysql engine) //-------------------------------------------------------------------------------- void send_sql_cmd(){ if ((dbgmode>0)||(verbose>0)) // printf("-i-issuing SQL command: %s\n",sql_cmd); // // mysql_err = mysql_query( gCon, sql_cmd); // printf("-i-mysql_query() status: %u\n",mysql_err); // if (mysql_err==0){ // display_results(); // }else{ // mysql_err = mysql_errno( gCon); // fprintf(stderr,"-e-mysql_query() failed:\nError: %u (%s)\n", mysql_err, mysql_error(gCon)); printf("-i-related to SQL command: %s\n",sql_cmd); // mysql_total_errors++; // maintain a running count printf("hit <enter> to continue..."); // fgets(keyboard,sizeof(keyboard), stdin); // } } //-------------------------------------------------------------------------------- // my fgets (read a string; don't overflow the variable; don't store trailing paper command) //-------------------------------------------------------------------------------- void my_fgets(char* a, int b, FILE* c){ int x; fgets(a,b,c); // x = strlen(a); // switch (a[x-1]) { // case '\r': // carriage return case '\n': // line-feed a[x-1] = '\0'; // default: // } // } // //-------------------------------------------------------------------------------- // remove trailing white-space //-------------------------------------------------------------------------------- void remove_trailing_ws(char* a){ int x = strlen(a); // loop: // if (x==0) return; // switch (a[x-1]) { // test last character case ' ' : // <space> case '\r': // carriage return case '\n': // line-feed case '\t': // tab a[x-1] = '\0'; // x--; // goto loop; // c-programmers will be horrified by this :-) default: // return; // } // } // //-------------------------------------------------------------------------------- // escape single quotes // SQL will not allow us to push a single quote into row data (O'NEILL needs to be sent as O''NEILL) //-------------------------------------------------------------------------------- void escape_single_quotes(char* a){ int x = strlen(a); // if (x==0) return; // char b[260]; // varchar is usually limited to 255 int y = 0; // start of b[] for(int i=0;i<x;i++){ // scan a if (a[i]=='\'') { // if a single quote b[y++]='\''; // then insert an extra quote now } // b[y++]=a[i]; // } // b[y]='\0'; // null terminate sprintf(a,b); // } // // this is the end //==============================================================================
Back to HomeNeil Rieck
Waterloo, Ontario, Canada.