OpenVMS Source Code Demos
mysql_demo09
//===============================================================================================================================
// title : mysql_demo09.c (do not use this program)
// author : Neil Rieck ( https://neilrieck.net MAILTO:n.rieck@bell.net )
// : Waterloo, Ontario, Canada.
// 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
// vms-refs: your-vms-system::sys$sysroot:[syshlp.examples.crtl]
// http://h71000.www7.hp.com/doc/732final/5763/5763pro_006.html (c-rtl)
// 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 (eg. "O'NEILL") by sending two (as per the SQL spec)
//===============================================================================================================================
#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)
//
// ANSI headers
//
#include <stdio.h> //
#include <stdlib> //
#include <string.h> //
#include <errno.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 headers (directory: 'mysql055_root:[include.mysql]')
//
// #include <my_global.h> // -F- Cannot find file <fenv.h> specified in #include
#include <mysql.h> //
//
// 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); //
} //
Back to HomeNeil Rieck
Waterloo, Ontario, Canada.