OpenVMS Source Code Demos

mysql_demo04

//===============================================================================================================================
// title     : mysql_demo04.c
// 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);								//
}										//

home Back to Home
Neil Rieck
Waterloo, Ontario, Canada.