OpenVMS Source Code Demos
mysql_api_demo13
//============================================================================== // title : mysql_api_demo13.c (derived from mysql_demo12.c) // author : Neil Rieck // : Waterloo, Ontario, Canada. // : https://neilrieck.net MAILTO:[email protected] // 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 170821 0. mysql_api_demo13.c (copied from mysql_demo12.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_demo13" // #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 gMaxElements; // 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; unsigned long element_count; struct dsc$descriptor_d *ptr; struct dsc$descriptor_d vms_misc2; unsigned long rc; // printf("---------------------------------------- start of response\n"); row_count = 0; element_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++) { // old way printf("[%.*s] ", (int) lengths[i], gRow1[i] ? gRow1[i] : "NULL"); // data is single-bracket delimited } // printf("\n"); // signal end of row //---------------------------------------------------------------------- // // in the following scheme, we will store each field in its own array subscript // for(i = 0; i < num_fields; i++) { // new way VMSIFY2(vms_misc2, gRow1[i]); // okay, we need this string in VMS format *(ptr+element_count) = vms_misc2; // element_count++; // if (element_count >= gMaxElements) // looks like we need to do an early exit goto hack; // } // // num_rows++; // // //---------------------------------------------------------------------- } 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_elements, 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; // gMaxElements = *max_elements; // copy to global (hack for demo) gBufArray = strbuf; // copy to global (hack for demo) printf("\n-i-hack addr %p\n",gBufArray); // // convert passed VMS-string to c-string // strncpy(sql_cmd1, sqlcmd->dsc$a_pointer, sqlcmd->dsc$w_length); // copy data into 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.