OpenVMS Source Code Demos
mysql_api_demo14
//============================================================================== // title : mysql_api_demo14.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 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.