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
//
#ifndef NSR_SOURCE_OPTION // ------------------------------------------
#define NSR_SOURCE_OPTION 0 // defaults to <my_global.h>
#endif //
#if (NSR_SOURCE_OPTION>0) // 1: LibMariaDB for VSI OpenVMS v3
#include <ma_global.h> // should be included 'first' in every C file
#else // 0: LibMariaDB for VSI OpenVMS v2
#include <my_global.h> // 0: MariaDB from Mark Berryman
#endif // ------------------------------------------
#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
//==============================================================================
Back to HomeNeil Rieck
Waterloo, Ontario, Canada.