OpenVMS Source Code Demos
mysql_api_demo12
//==============================================================================
// title : mysql_api_demo12.c (derived from mysql_demo11.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 170817 0. mysql_api_demo12.c (copied from mysql_demo11.c)
// 1. quick hack to demo how to pass strings back to BASIC
// 101 NSR 240422 1. changes for use with "LibMariaDB for VSI OpenVMS"
//==============================================================================
#define PROGRAM_NAME "mysql_api_demo12" //
#define DEFAULT_HOST 0 //
#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
//
// VMSIFY1
// 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 VMSIFY1(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); \
}
//
// global variables
//
static MYSQL *gCon; // connection stuff
static MYSQL_RES *gRes; // Result
static MYSQL_ROW gRow1; // used in reading response
static long gRows, gCols; // stuff for hacking
static char sql_cmd[999]; // not too mental
static char sql_cmd1[999]; //
static char sql_data[1999]; // big enough for one whole record
static long gMaxRows; //
static char db_user[128]; // db parameters
static char db_pass[128]; //
static char db_host[128]; //
static char db_base[128]; //
static long db_bits; // db parameter bookkeeping
static struct dsc$descriptor_d *gBufArray; //
static struct dsc$descriptor_d vms_misc2; //
static long mysql_err; // sql err number
static char mysql_ert[255]; // sql err text
static long rc; //
//------------------------------------------------------------------------------
// display results (of sql query)
//------------------------------------------------------------------------------
void display_results() {
unsigned int num_fields;
unsigned int i;
unsigned long *lengths;
unsigned int num_rows;
unsigned long row_count;
struct dsc$descriptor_d *ptr;
struct dsc$descriptor_d vms_misc2;
unsigned long rc;
//
printf("---------------------------------------- start of response\n");
row_count = 0;
ptr = gBufArray;
if (mysql_field_count( gCon )==0){
printf("-w-no result data to parse\n");
goto hack; // going to c-programmer hell for this :-)
}
gRes = mysql_use_result( gCon ); // get the result
num_fields = mysql_num_fields(gRes); // how many fields?
num_rows = mysql_num_rows(gRes); // how many rows?
printf("-i-fields:%d\n",num_fields); // x-dimension
printf("-i-rows :%d\n",num_rows); // y-dimension
//
while ((gRow1 = mysql_fetch_row(gRes))) { //
lengths = mysql_fetch_lengths(gRes); //
row_count++; //
for(i = 0; i < num_fields; i++) { //
printf("[%.*s] ", (int) lengths[i], gRow1[i] ? gRow1[i] : "NULL"); // bracket-delimited data
} //
printf("\n"); // signal end-of-row
//----------------------------------------------------------------------
//
// 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]]]
// or 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_fields; i++) { //
if (i==0) { // if first pass thru
strcpy(sql_data,"[[["); //
}else{ //
strcat(sql_data,",[[["); //
} //
strcat(sql_data,gRow1[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 >= gMaxRows) { // looks like we need to do an early exit
goto hack; //
} //
//----------------------------------------------------------------------
}
hack:;
// mysql_free_result(gRes); // release memory
gCols = num_fields; // copy to global (hack for demo)
gRows = row_count; // copy to global (hack for demo)
//
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);
//
// 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_CONNECTOR( long *rows,
long *cols,
long *stat,
long *max_rows,
struct dsc$descriptor_d *sqlcmd, // address of BASIC string
struct dsc$descriptor_d *strbuf, // address of BASIC string array (err, list)
struct dsc$descriptor_d *sqlmsg) {
//
// init variables
//
sql_cmd[0] = '\0'; //
mysql_ert[0] = '\0'; //
db_bits = 0; //
gMaxRows = *max_rows; // copy to global (hack for demo)
gBufArray = strbuf; // copy to global (hack for demo)
//
// convert passed VMS-string to c-string
//
strncpy(sql_cmd1, sqlcmd->dsc$a_pointer, sqlcmd->dsc$w_length); // copy BASIC data into C memory
sql_cmd1[sqlcmd->dsc$w_length] = '\0'; // null terminate the c-string
printf("-i-passed data: %s\n", sql_cmd1); //
//
// get connect parameters 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)
}
//
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{
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)); //
}
//
// optional stuff (like set the database)
//
if (strlen(db_base)==0){ // if database not provided...
sprintf(sql_cmd,"use mysql"); // ...then select one
sprintf(db_base,"mysql"); // change local variable
}else{ //
sprintf(sql_cmd,"use %s", db_base); //
} //
send_sql_cmd(); //
//
// use the passed sql statement -or- display some stuff from mysql.user
//
if (strcasecmp(db_base,"MYSQL")==0) { //
if (strlen(sql_cmd1)==0){ //
sprintf(sql_cmd,"select user,password,host from user"); //
}else{
sprintf(sql_cmd,sql_cmd1);
}
send_sql_cmd(); //
}
sprintf(mysql_ert,"Status: %u (text: %s)", mysql_err, mysql_error(gCon)); //
//
// adios (this is just a one-command demo so close the channel)
//
printf("-i-closing connection\n"); //
mysql_free_result(gRes); //
mysql_close( gCon); //
//
// pass some stuff back to BASIC
//
*rows = gRows; // pass field count back to BASIC
*cols = gCols; // pass rows count back to BASIC
*stat = mysql_err; // pass sql status back to BASIC
VMSIFY2(vms_misc2, mysql_ert); // convert c-string to vms-string
*sqlmsg = vms_misc2; // pass sql message back to BASIC
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(){
FILE *opt_file; //
char opt_buff[32767]; //
char tmp0[20]; //
char tmp1[20]; //
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?
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);
}
//==============================================================================
Back to HomeNeil Rieck
Waterloo, Ontario, Canada.