OpenVMS Source Code Demos

mysql_demo03.c

//==============================================================================
// title     : mysql_demo03.c
// author    : Neil Rieck
//           : Waterloo, Ontario, Canada.
//           : http://neilrieck.net   MAILTO:n.rieck@bell.net
// created   : 2018-04-19
// purpose   : real world demo for MariaDB (an alternative fork of MySQL)
// target    : for MariaDB on OpenVMS-8.4 from Mark Berryman
// build     : @mysql_demo.com mysql_demo2.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 180419 1. original effort ( derived from mysql_demo02.c )
// 101 NSR 240415 1. changes for use with "LibMariaDB for VSI OpenVMS"
//==============================================================================
#define __NEW_STARLET 1                 // enable strict starlet (>= OpenVMS70)
#define PROGRAM_NAME  "mysql_demo3"     //
#define DEFAULT_HOS   0                 // 
#define PARAM_FILE    "mysql_demo.ini"  // optional parameter file
//
//      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>
//
//      global variables
//
MYSQL		*gCon;								// connection stuff
MYSQL_RES       *gRes;								// Result
MYSQL_ROW	gRow;								// used in reading response
char		sql_cmd[999];							// not too mental
long		mysql_err;							//
long		db_bits;							// used in bookkeeping
char		db_user[128];							//
char		db_pass[128];							//
char		db_host[128];							//
char		db_base[128];							//
//------------------------------------------------------------------------------
//	display results (of sql query)
//------------------------------------------------------------------------------
void display_results() {
    unsigned int	num_fields;
    unsigned int	i;
    unsigned long	*lengths;
    //
    printf("---------------------------------------- start of response\n");
    if (mysql_field_count( gCon )==0){
	printf("-w-no result data to parse\n");
	goto hack;
    }
    gRes = mysql_use_result( gCon );						// get the result
    num_fields = mysql_num_fields(gRes);					// how many fields?
    printf("-i-fields:%d\n",num_fields);
    //
    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");								//
    }
    hack:;
    mysql_free_result(gRes);							//
    printf("------------------------------------------ end of response\n");
}
//------------------------------------------------------------------------------
//	send_sql_cmd
//------------------------------------------------------------------------------
void send_sql_cmd(){
    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);						// get the error number
	fprintf(stderr,"-e-mysql_query() failed: Error: %u (%s)\n",		//
		mysql_err, mysql_error( gCon ));				// display with error text
    }
}
//
//	forward declarations
//
void load_params_keyboard();							//
void load_params_environment();							//
void load_params_file();							//

//==============================================================================
//	Main
//==============================================================================
int main( int *argc, char **argv) {
    printf("%s%s\n", "program:", PROGRAM_NAME);
    //
    //	init variables
    //
    sql_cmd[0]	= '\0';								//
    db_bits	= 0;								//
    //
    //	get connect params from somewhere
    //
    if ((db_bits & 3)!=3)							// if no username or password... 
	load_params_environment();						// try loading params from the environment
    if ((db_bits & 3)!=3)							// if no username or password... 
	load_params_file();							// try loading params from a file
    if ((db_bits & 3)!=3)							// if no username or password... 
	load_params_keyboard();							// try loading params from the keyboard
    if ((db_bits & 3)!=3){
	printf("-e-error, could not determine username or password\n");
	exit(2);								// exit with DCL error
    }
    //
    //	make sure mariadb routines are available for init
    //
    gCon = mysql_init( NULL);							// hello, are you there?
    if ( gCon == NULL) {							// nope
        fprintf(stderr,"-e-can't connect to MySQL client routines\n");		//
        return -6;								// exit with VMS-E (error)
    }

#define SSL 1									// 0=disable, 1=enable
#if SSL==1
    printf("-i-enabling SSL\n");
    //
    // if SSL is not enabled on your database engine then this next chunk will not work so
    // pre-test by interactively connecting to it your then issuing one or both of these
    // commands:
    //		show global variables like 'have_%ssl';
    //		status;	(or '\s')
    //
    // only used hard-coded SSL params for now
    // two ways to go at this point:
    //	1) one of these:	mysql_ssl_set(gCon, key, cert, ca, capath, cipher)	-or-
    //  2) a bunch of these:	mysql_options(gCon, param)			but not for MySQL-5.5
    //
    mysql_ssl_set(gCon, "/MYSQL055_ROOT/certs/server-key.pem",
			"/MYSQL055_ROOT/certs/server-cert.pem",
			"/MYSQL055_ROOT/certs/ca-cert.pem",
			0,0);
//  mysql_options(gCon, MYSQL_OPT_SSL_KEY,    "/mysql055_root/certs/server-key.pem");
//  mysql_options(gCon, MYSQL_OPT_SSL_CERT,   "/mysql055_root/certs/server-cert.pem");
//  mysql_options(gCon, MYSQL_OPT_SSL_CA,     "/mysql055_root/certs/ca-cert.pem");
//  mysql_options(gCon, MYSQL_OPT_SSL_CAPATH, capath);
//  mysql_options(gCon, MYSQL_OPT_SSL_CIPHER, cipher);
#endif
    //
    //	okay, let's connect to the server
    //
    if (mysql_real_connect(							// connect to Maria/MySQL
	    gCon,								// 
	    db_host,								// localhost
	    db_user,								// user
	    db_pass,								// pass
	    db_base,								// 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));	//
    }
    //
    //	issue 'status' to see if we've setup an encrypted connection
    //
    sprintf(sql_cmd,"\\s");							// in 'c' the slash is escaped
    send_sql_cmd();								//
    //
    //	adios
    //
    printf("-i-closing connection\n");						//
    mysql_free_result(gRes);							//
    mysql_close( gCon);								//
    //
    return(1);									// exit with VMS-S (success)
}
//------------------------------------------------------------------------------
//	load params from keyboard
//------------------------------------------------------------------------------
void load_params_keyboard(){
    printf("-i-load_params_keyboard\n");
    db_user[0]	= '\0';
    db_pass[0]	= '\0';
    db_base[0]	= '\0';
    db_host[0]	= '\0';
    db_bits	= 0;								// init
    //
    //	username
    //
    printf("db user? (eg. root)   :");						//
    fgets(db_user,sizeof(db_user), stdin);					//
    db_user[strcspn(db_user,"\n")] = 0;						// drop trailing <lf>
    if (strlen(db_user)>0)
	db_bits = db_bits | 1;
    //
    //	password
    //
    printf("db pass?              :");						//
    fgets(db_pass,sizeof(db_pass), stdin);					//
    db_pass[strcspn(db_pass,"\n")] = 0;						// drop trailing <lf>
    if (strlen(db_pass)>0)
	db_bits = db_bits | 2;
    //
    //	host (leave blank to connect to engine on local host)
    //
    printf("note: host is optional\n");						//
    printf("host? (eg. 127.0.0.1) :");						//
    fgets(db_host, sizeof(db_host), stdin);					//
    db_host[strcspn(db_host,"\n")] = 0;						// drop trailing <lf>
  #if (DEFAULT_HOST!=0)
    if (strlen(db_host)==0){							//
	sprintf(db_host,"%s","127.0.0.1");					//
	printf("-i-defaulting to: %s\n",db_host);				//
    }
  #endif
    if (strlen(db_host)>0)
	db_bits = db_bits | 4;
    //
    //	database
    //	note: some accounts may only connect to a specified database
    //
    printf("note: database is optional\n");					//
    printf("database? (eg. mysql) :");						// mysql is the master database
    fgets(db_base,sizeof(db_base),stdin);					//
    db_base[strcspn(db_base,"\n")] = 0;						// drop trailing <lf>
    if (strlen(db_base)>0)
	db_bits = db_bits | 8;
}
//------------------------------------------------------------------------------
//	load params from environment
//------------------------------------------------------------------------------
void load_params_environment(){
    char *tmpPtr = NULL;							//
    //
    printf("-i-load_params_environment\n");
    db_user[0]	= '\0';
    db_pass[0]	= '\0';
    db_base[0]	= '\0';
    db_host[0]	= '\0';
    db_bits	= 0;
    //
    //	caveat: on OpenVMS, getenv() will first look for a logical name
    //		if not found, it will look for a DCL symbol
    //
    tmpPtr = getenv("DB_USER");
    if	(tmpPtr!=NULL) {							//
	sprintf(db_user, "%s", tmpPtr);						//
	db_bits = db_bits | 1;							//
    }										//
    tmpPtr = getenv("DB_PASS");
    if	(tmpPtr!=NULL) {							//
	sprintf(db_pass, "%s", tmpPtr);						//
	db_bits = db_bits | 2;							//
    }										//
    tmpPtr = getenv("DB_HOST");
    if	(tmpPtr!=NULL) {							//
	sprintf(db_host, "%s", tmpPtr);						//
	db_bits = db_bits | 4;							//
    }										//
    tmpPtr = getenv("DB_BASE");
    if	(tmpPtr!=NULL) {							//
	sprintf(db_base, "%s", tmpPtr);						//
	db_bits = db_bits | 8;							//
    }										//
}
//------------------------------------------------------------------------------
//	load params from file
//------------------------------------------------------------------------------
void load_params_file(){
    char	*tmpPtr = NULL;							//
    FILE	*opt_file;							//
    char	opt_buff[32767];						//
    char	tmp0[20];							//
    char	tmp1[20];							//
    char	*junk;								//
    long	temp, i, j, k;							//
    long	param_state;
    long	param_line=0;
    long	param_reject=0;
    //
    printf("-i-load_params_file\n");
    db_user[0]	= '\0';
    db_pass[0]	= '\0';
    db_base[0]	= '\0';
    db_host[0]	= '\0';
    db_bits	= 0;
    //
    // -------------------------------------------------------------------------
    printf("-i-opening file: %s\n",PARAM_FILE);					//
    opt_file = fopen(PARAM_FILE, "r");						//
    while (fgets(opt_buff, sizeof(opt_buff), opt_file) != NULL) {
	param_line++;								//
	if (opt_buff[0]=='!')							// if 1st char is <exclamtion>
	    continue;								// then ignore this line
	temp = strlen(opt_buff);;
	while ((temp>0) && (opt_buff[temp]<=32)) {				// drop trailing <space> and controls
	    opt_buff[temp]=0;							//
	    temp--;								//
	}									//
	if (strlen(opt_buff)<4){						//
	    param_reject++;							//
	    printf("-w-skipping line number %d (too short)\n",param_line);	//
	    continue;								// then ignore this line
	}
	if (strlen(opt_buff) > (sizeof(tmp0)+sizeof(tmp1)+1)) {			//
	    printf("-w-skipping line number %d (too long)\n",param_line);	//
	    param_reject++;							//
	    continue;								//
	}
	printf("-i-line : %s\n"	,opt_buff);					//
        temp = strlen(opt_buff);						// get true length
	for (j=0; j<temp; j++) {						// scan string
	    if (opt_buff[j]=='=') {						// if this is the equals sign?
 printf("-i-debug j %d\n",j);
		if (j>sizeof(tmp0)){						//
		    printf("-w-skipping line number %d (no room(a))\n",param_line);//
		    param_reject++;						//
		    continue;     						//
		}
		if ( (strlen(opt_buff)-j) > sizeof(tmp1)){			//
		    printf("-w-skipping line number %d (no room(b))\n",param_line);//
		    param_reject++;						//
		    continue;     						//
		}
		strncpy(tmp0, opt_buff ,j);					//
		tmp0[j]	= '\0';							// make sure we're null terminated
		printf("-i-label: %s\n"	,tmp0);					//
		k = temp - j - 1;						//
		strncpy(tmp1, opt_buff+j+1,k);					//
		tmp1[k]	= '\0';							// make sure we're null terminated
		printf("-i-data : %s\n"	,tmp1);					//
		//
		//	now store the extracted data (if possible)
		//
		param_state=0;
		if (strcasecmp(tmp0,"USERNAME")==0){
		    strcpy(db_user	,tmp1);
		    if (strlen(db_user)>0)
			db_bits = db_bits | 1;
		    param_state++;
		}
		if (strcasecmp(tmp0,"PASSWORD")==0){
		    strcpy(db_pass	,tmp1);
		    if (strlen(db_pass)>0)
			db_bits = db_bits | 2;
		    param_state++;
		}
		if (strcasecmp(tmp0,"HOST")==0){
		    strcpy(db_host	,tmp1);
		    if (strlen(db_host)>0)
			db_bits = db_bits | 4;
		    param_state++;
		}
		if (strcasecmp(tmp0,"DATABASE")==0){
		    strcpy(db_base	,tmp1);
		    if (strlen(db_base)>0)
			db_bits = db_bits | 8;
		    param_state++;
		}
		if (param_state==0){
		    printf("-w-skipping line number %d (no logic)\n",param_line);
		    param_reject++;						//
		    continue;     						//
		}
		if (param_state>=2){
		    printf("-w-skipping line number %d (multiple)\n",param_line);
		    param_reject++;						//
		    continue;     						//
		}
		goto no_more;							//
	    }
	}
	no_more:;
    }
    printf("-i-debug bits %d\n", db_bits);
}
// this is the end
//==============================================================================

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