OpenVMS Source Code Demos
mysql_demo02.c
//=============================================================================== // title : mysql_demo02.c // author : Neil Rieck // : Waterloo, Ontario, Canada. // http://neilrieck.net MAILTO:[email protected] // created : 2014-01-31 // purpose : real world demo for MariaDB (an alternative fork of MySQL) // target : for MariaDB (from Mark Berryman) on OpenVMS-8.4 // build : @mysql_demo.com mysql_demo2.c (see DCL script 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 140131 1. original effort // 101 NSR 140308 1. moved SQL commands to a variable // 102 NSR 170420 1. added code to enable connecting to a remote host // 2. replaced primitive scanf() with better gets() // 103 NSR 170421 1. added code to (optionally) read connect params from a file // 2. added code to (optionally) read connect params from logicals // 104 NSR 240415 1. changes for use with "LibMariaDB for VSI OpenVMS" //=============================================================================== #define __NEW_STARLET 1 // enable strict starlet (>= OpenVMS70) // #define PROGRAM_NAME "mysql_demo2" // #define DEFAULT_HOST 0 // #define PARAM_FILE "mysql_demo.ini" // optional parameter file // // includes // // ----------------------------------------------------------------------------- // 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> // // 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 // printf("-i-test-01 (mysql_init)\n"); 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) } // printf("-i-test-02 (mysql_real_connect)\n"); 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 some SQL commands (do not terminate with a semicolon) // sprintf(sql_cmd,"show databases"); // send_sql_cmd(); // // if (strlen(db_base)==0){ // if database not provided... sprintf(sql_cmd,"use mysql"); // ...then select one send_sql_cmd(); // sprintf(db_base,"mysql"); // change local variable } sprintf(sql_cmd,"show tables"); // send_sql_cmd(); // // // display some stuff from mysql.user // if (strcasecmp(db_base,"MYSQL")==0) { sprintf(sql_cmd,"select user,password,host from user"); // send_sql_cmd(); // } // // issue an SQL statement that will fail // sprintf(sql_cmd,"show nonsense"); // 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.