OpenVMS Source Code Demos

mysql_api_demo14

//==============================================================================
// title     : mysql_api_demo14.c
// author    : Neil Rieck
//           : Waterloo, Ontario, Canada.
//           : https://neilrieck.net MAILTO:n.rieck@bell.net
// created   : 2014-01-31
// purpose   : real world demo for MariaDB (an alternative fork of MySQL)
// target    : for MariaDB on OpenVMS-8.4 (Alpha or Itanium)
// ver who when   what
// --- --- ------ --------------------------------------------------------------
// 100 NSR 170529 1. original effort
//     NSR 170530 2. the saga continues
//     NSR 170817 3. renamed NSR_MOVE_FROM() to NSR_FETCH()
//     NSR 171207 4. added a hook to deal with NULL data (oops)
//		  5. now call mysql_free_result() after the end of NSR_FETCH
// 101 NSR 240422 1. changes for use with "LibMariaDB for VSI OpenVMS"
//==============================================================================
#define PROGRAM_NAME	"mysql_api_demo14"					//
#define	PARAM_FILE	"mysql_demo.ini"					// optional parameter file
#define	__NEW_STARLET	1							// enable strict starlet (>= OpenVMS70)
//
//      included headers
//
// -----------------------------------------------------------------------------
// 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>
#include <descrip.h>								// for VMS string descriptors in C
#include <str$routines.h>							// for VMS string descriptors in VMS
//
//	VMSIFY
//      a macro for use in the VMS world (VMS strings employ this structure)
//	notes:	1. this macro can be used to create VMS strings in c space
//		2. the $DESCRIPTOR macro does something similar employing sizeof-1
//		3. this macro combines two operations
//		4. use str$copy_dx() to copy string data up to the calling program
//	
#define VMSIFY(a,b) {						\
    a.dsc$b_dtype = DSC$K_DTYPE_T;				\
    a.dsc$b_class = DSC$K_CLASS_S;				\
    a.dsc$w_length = strlen(b);					\
    a.dsc$a_pointer = (char *) malloc(strlen(b));		\
    strncpy(a.dsc$a_pointer,b,a.dsc$w_length);			\
}
//	VMSIFY2
//      a macro for use in the VMS world (VMS strings employ this structure)
//	notes:	1. this macro can be used to create VMS strings in VMS space
//		2. the $DESCRIPTOR macro does something similar employing sizeof-1
//		3. this macro combines two operations
//		4. unlike malloc, memory allocated via "str$get1_dx" will survive
//		   after this module exits.
//
#define VMSIFY2(a,b) {						\
    a.dsc$b_dtype = DSC$K_DTYPE_T;				\
    a.dsc$b_class = DSC$K_CLASS_D;				\
    a.dsc$w_length = strlen(b);					\
    a.dsc$a_pointer = NULL;					\
    rc = str$get1_dx(&a.dsc$w_length,&a);			\
    if ((rc & 7)!=1) printf("-e-str$get1_dx-rc: %ld\n",rc);	\
    strncpy(a.dsc$a_pointer,b,a.dsc$w_length);			\
}
//
//	This struct must match a COMMON declared in BASIC
//
#pragma member_alignment save							// 
#pragma nomember_alignment							// build the next struct like a BASIC common
struct xyz {									//
	long	SANITY;								// holds struct size determined by BASIC
	struct dsc$descriptor_d *ADDR[10];					// 10 items (array addresses)
	long	SIZE[10];							// 10 items (array maximum sizes)
	long	STAT[10];							// 10 items (mysql status code)
	long	ROWS[10];							// 10 items (actual number of rows)
	long	COLS[10];							// 10 items (actual number of columns)
	long	MORE[10];							// 10 items (more data available?)
	long	STATUS;								// 10 items (mysql status)
	long	MSG_LEN;							//
	char	MSG_TXT[256];							//
	long	LAST;								// this is used in my sanity check
};
#pragma member_alignment restore
//
//	1) Declared variables are usually placed psect (program section) $code$
//	2) Each so-called DEC program has its own psect (so that BASIC variables variables won't clash with C variables)
//	3) BASIC declarations via MAP or COMMON force variables into a named psect
//	4) This next directive tells the C-compiler to look for a psect named "CMN" 
//
#pragma extern_model save							//
#pragma extern_model common_block						//
    extern struct xyz CMN;							// now declare it external (must exist in BASIC)
#pragma extern_model restore							//
//
//	global variables
//
static MYSQL		*gCon;							// connection stuff
static MYSQL_RES	*gRes;							// Result
static MYSQL_ROW	gRow;							// used in reading response
static char		sql_data[1999];						// big enough for one whole record
static char		sql_cmd[999];						// this is not very good
static char		*gSql;							// this is better
static long		mysql_err;						//
static long		db_bits;						// used in bookkeeping
static char		db_user[128];						//
static char		db_pass[128];						//
static char		db_host[128];						//
static char		db_base[128];						//
//=============================================================================
//
//	forward declarations
//
long maria_connect(long);
void send_sql_cmd(long);
void fetch_results(long,long);
void load_params_keyboard();							//
void load_params_environment();							//
void load_params_file(long);							//

//==============================================================================
//	Main
//==============================================================================
//
//	Important notes for calling from BASIC:
//	1) This c-based API is compiled with cli switch "/name=(as_is,shorten)" 
//	2) BASIC always upcases published symbols and there is no way to disable this
//	3) So functions called from BASIC must be declared in C as uppercase
//	4) a void function here must be referenced from BASIC via "call sub"
//
long NSR_QUERY( struct dsc$descriptor_s *sql, long verbose, long buffer){
    gSql = 0;									// init to undefined
    if (sql->dsc$w_length>0){							//
	gSql = malloc(sql->dsc$w_length+1);					//
	strncpy(gSql, sql->dsc$a_pointer ,sql->dsc$w_length);			//
	gSql[sql->dsc$w_length]='\0';						//
	if (verbose>0)								//
	    printf("-i-issuing SQL command: %s\n",gSql);			//
	mysql_err = mysql_query( gCon, gSql);					//
	if (verbose>0)
	    printf("-i-mysql_query() status: %u\n",mysql_err);			//
	CMN.STAT[buffer] = mysql_err;						//
	CMN.STATUS = mysql_err;
	if (mysql_err==0){							//
	    CMN.MSG_LEN = 0;							// nothinh in the msg buffer
	}else{									//
	    mysql_err = mysql_errno( gCon);					// get the error number
	    sprintf(CMN.MSG_TXT,"-e-mysql_query() failed: Error: %u (%s)\n",	//
			mysql_err, mysql_error( gCon ));			// display with error text
	    CMN.MSG_LEN = strlen(CMN.MSG_TXT);					//
	}									//
	return mysql_err;							//
    }else{									//
	printf("-e-refused; command is blank\n");				//
	return(-6);								// VMS-e-
    }
}
//
//	connect to MySQL/MariaDB using parameters from BASIC
//
long NSR_CONNECT_PARAMS(	struct dsc$descriptor_s *user,
				struct dsc$descriptor_s *pass,
				struct dsc$descriptor_s *host,
				struct dsc$descriptor_s *base,
				long verbose) {
    long param_bits = 0;
    if (sizeof(CMN) != CMN.SANITY){
	printf("-e-sanity error\n");
	return(-6);
    }
    //
    if (user->dsc$w_length>0){							//
	strncpy(db_user, user->dsc$a_pointer ,user->dsc$w_length);		//
	param_bits |= 1;
    }
    db_user[user->dsc$w_length] = '\0';
    //
    if (pass->dsc$w_length>0){							//
	strncpy(db_pass, pass->dsc$a_pointer ,pass->dsc$w_length);		//
	param_bits |= 2;
    }
    db_base[pass->dsc$w_length] = '\0';
    //
    if (host->dsc$w_length>0){							//
	strncpy(db_host, host->dsc$a_pointer ,host->dsc$w_length);		//
	param_bits |= 4;
    }
    db_host[host->dsc$w_length] = '\0';
    //
    if (base->dsc$w_length>0){							//
	strncpy(db_base, base->dsc$a_pointer ,base->dsc$w_length);		//
	param_bits |= 8;
    }
    db_base[base->dsc$w_length] = '\0';
    //
    if ((param_bits & 3) != 3){
	printf("-e-insufficient data to connect\n");
	return (-6);								// VMS-e-
    }
    return (maria_connect(verbose));
}
//==============================================================================
//	connect to MySQL/MariaDB without params (C will attempt to find them)
//==============================================================================
long NSR_CONNECT( long verbose) {
    printf("%s%s\n", "program:", PROGRAM_NAME);
    //
    sql_cmd[0]	= '\0';								// inits
    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(verbose);						// 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(-6);								// exit with VMS-e- (error)
    }
    return maria_connect(verbose);
}
//==============================================================================
//	close
//==============================================================================
long NSR_CLOSE(long verbose) {
    //
    //	adios
    //
    if (verbose>0){								//
	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 (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(long verbose){
    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;							//
    //
    if (verbose>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;
    //
    // -------------------------------------------------------------------------
    if (verbose>0){
	printf("-i-load_params_file\n");					//
	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++;							//
	    if (verbose>0)
		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)) {			//
	    if (verbose>0)
		printf("-w-skipping line number %d (too long)\n",param_line);	//
	    param_reject++;							//
	    continue;								//
	}
        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?
		if (j>sizeof(tmp0)){						//
		    if (verbose>0)						//
			printf("-w-skipping line number %d (no room(a))\n",param_line);
		    param_reject++;						//
		    continue;     						//
		}
		if ( (strlen(opt_buff)-j) > sizeof(tmp1)){			//
		    if (verbose>0)						//
			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
		if (verbose>0)
		    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
		//
		//	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){
		    if (verbose>0)
			printf("-w-skipping line number %d (no logic)\n",param_line);//
		    param_reject++;						//
		    continue;     						//
		}
		if (param_state>=2){
		    if (verbose>0)
			printf("-w-skipping line number %d (multiple)\n",param_line);//
		    param_reject++;						//
		    continue;     						//
		}
		goto no_more;							//
	    }
	}
	no_more:;
    }
}
//==============================================================================
//	maria connect
//==============================================================================
long maria_connect(long verbose) {
    //
    //	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 library\n");		//
        return -6;								// exit with VMS-E (error)
    }
    //
    if (mysql_real_connect(							// connect to Maria/MySQL
	    gCon,								// 
	    db_host,								// localhost
	    db_user,								// user
	    db_pass,								// pass
	    db_base,								// database
	    3306,								// 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{
	if (verbose>0) {
	    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));//
	}
    }
    //
    if (strlen(db_base)>0){							// if database was provided...
	sprintf(sql_cmd,"use %s",db_base);					// ...then select one
	send_sql_cmd(verbose);							//
	sprintf(db_base,"mysql");						// change local variable
    }
    return 1;
}
//------------------------------------------------------------------------------
//	fetch results (of sql query)
//------------------------------------------------------------------------------
void NSR_FETCH(long verbose, long buffer) {
    unsigned int		num_cols;
    unsigned int		i;
    unsigned long		*lengths;
    unsigned int		num_rows;
    unsigned long		limit;
//  long			*ptr;
    struct dsc$descriptor_d	*ptr;
    struct dsc$descriptor_d	vms_misc2;
    long			rc;
    unsigned short		yada;
    //
    ptr =  CMN.ADDR[buffer];							// get pointer to desired array
printf("\n-i-hack addr %p\n",ptr);
    num_rows = 0;								//
    limit = CMN.SIZE[buffer];							// we can't return more rows more than this
    if (verbose>0)
	printf("---------------------------------------- start of response\n");
    if (mysql_field_count( gCon )==0){
	if (verbose>0)
	    printf("-w-no result data to parse\n");
	goto hack;								// command all 'c' programmer's to faint
    }
    gRes = mysql_use_result( gCon );						// get the result
    num_cols = mysql_num_fields(gRes);						// how many fields?
    if (verbose>0)								//
	printf("-i-fields:%ld\n",num_cols);					//
//------------------------------------------------------------------------------   this does not work
//  num_rows   = mysql_num_rows(gRes);						// how many rows?
//  printf("-i-rows  :%ld\n",num_rows);						//
//------------------------------------------------------------------------------
    num_rows = 0;								//
    while ((gRow = mysql_fetch_row(gRes))) {					//
	lengths = mysql_fetch_lengths(gRes);					//
	if (verbose>0){
	    for(i = 0; i < num_cols; i++) {					//
		printf("[%.*s] ", (int) lengths[i], gRow[i] ? gRow[i] : "NULL");//
	    }									//
	    printf("\n");							//
	}
	//
	//	in the following scheme, we will collect all fields into a single row which
	//	would then need to be parsed in BASIC. For example, this demo could yield
	//	something like this:
	//		[[[alpha]]],[[[beta]]],[[[gamma*]]],[[[epsilon]]]
	//	caveat:
	//	1. while okay for a demo, this would add too much overhead for production
	//	2. a better way would be to store each field in a single-dimension array
	//	   (a list) then return the fields-per-row count to the caller
	//
	sql_data[0] = '\0';							// init
	for(i = 0; i < num_cols; i++) {						//
	    if (sql_data[0]=='\0') {						// if first pass thru
		strcpy(sql_data,"[[[");						//
	    }else{								//
		strcat(sql_data,",[[[");					//
	    }									//
	    if (gRow[i]==NULL){							// if null...				bf_100.2
		strcat(sql_data, "NULL");					// ...then say so
	    }else{								//
		strcat(sql_data,gRow[i]);					//
	    }									//
	    strcat(sql_data,"]]]");						//
	}									//
	//
	VMSIFY2(vms_misc2, sql_data);						// okay, we need this string in VMS format
	rc = str$copy_dx(ptr+num_rows,& vms_misc2);				//
	if ((rc & 7)!=1){							//
	    printf("-e-str$copy_dx-rc: %ld\n",rc);				//
	}									//
	num_rows++;								//
	//
	if (num_rows >= limit) {						// looks like we need to do an early exit
	    CMN.MORE[buffer] = 1;						// indicate more data is waiting
	    goto hack;								//
	}									//
    }
    CMN.MORE[buffer] = 0;							// 
    hack:;
    if (verbose>0)								//
	printf("-i-rows  :%ld\n",num_rows);					//
    CMN.ROWS[buffer] = num_rows;						//
    CMN.COLS[buffer] = num_cols;						//
    mysql_free_result(gRes);							// free this resource
    //
    if (verbose>0)								//
	printf("------------------------------------------ end of response\n");	//
}
//------------------------------------------------------------------------------
//	send_sql_cmd
//------------------------------------------------------------------------------
void send_sql_cmd(long verbose){
    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(verbose);
    }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
    }
}
//==============================================================================


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