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
}
}
//==============================================================================
Back to HomeNeil Rieck
Waterloo, Ontario, Canada.