1 /*******************************************************************  2 Sample Program 10:  Dynamic SQL Method 4  3   4 This program connects you to ORACLE using your username and  5 password, then prompts you for a SQL statement.  You can enter  6 any legal SQL statement.  Use regular SQL syntax, not embedded SQL.  7 Your statement will be processed.  If it is a query, the rows  8 fetched are displayed.  9 You can enter multiline statements.  The limit is 1023 characters. 10 This sample program only processes up to MAX_ITEMS bind variables and 11 MAX_ITEMS select-list items.  MAX_ITEMS is #defined to be 40. 12 *******************************************************************/ 13  14 #include <stdio.h> 15 #include <string.h> 16 #include <setjmp.h> 17 #include <sqlda.h> 18 #include <stdlib.h> 19 #include <sqlcpr.h> 20  21 #define    SQL_SINGLE_RCTX  ((void *)0)  22  23 /* Maximum number of select-list items or bind variables. */ 24 #define MAX_ITEMS         40 25  26 /* Maximum lengths of the _names_ of the 27    select-list items or indicator variables. */ 28 #define MAX_VNAME_LEN     30 29 #define MAX_INAME_LEN     30 30  31 #ifndef NULL 32 #define NULL  0 33 #endif 34  35 /* Prototypes */ 36 #if defined(__STDC__) 37   void sql_error(void); 38   int oracle_connect(void); 39   int alloc_descriptors(int, int, int); 40   int get_dyn_statement(void); 41   void set_bind_variables(void); 42   void process_select_list(void); 43   void help(void); 44 #else 45   void sql_error(/*_ void _*/); 46   int oracle_connect(/*_ void _*/); 47   int alloc_descriptors(/*_ int, int, int _*/); 48   int get_dyn_statement(/* void _*/); 49   void set_bind_variables(/*_ void -*/); 50   void process_select_list(/*_ void _*/); 51   void help(/*_ void _*/); 52 #endif 53  54 char *dml_commands[] = {"SELECT", "select", "INSERT", "insert", 55                         "UPDATE", "update", "DELETE", "delete"}; 56  57 EXEC SQL INCLUDE sqlda; 58 EXEC SQL INCLUDE sqlca; 59  60 EXEC SQL BEGIN DECLARE SECTION; 61     char    dyn_statement[1024]; 62     EXEC SQL VAR dyn_statement IS STRING(1024); 63 EXEC SQL END DECLARE SECTION; 64   65 SQLDA *bind_dp; 66 SQLDA *select_dp; 67  68 /* Define a buffer to hold longjmp state info. */ 69 jmp_buf jmp_continue; 70  71 /* A global flag for the error routine. */ 72 int parse_flag = 0; 73  74 void main() 75 { 76     int i; 77  78     /* Connect to the database. */ 79     if (oracle_connect() != 0) 80         exit(1); 81  82     /* Allocate memory for the select and bind descriptors. */ 83     if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0) 84         exit(1); 85  86     /* Process SQL statements. */ 87     for (;;)  88     { 89         (void) setjmp(jmp_continue); 90  91         /* Get the statement.  Break on "exit". */ 92         if (get_dyn_statement() != 0) 93             break; 94  95         /* Prepare the statement and declare a cursor. */ 96         EXEC SQL WHENEVER SQLERROR DO sql_error(); 97  98         parse_flag = 1;     /* Set a flag for sql_error(). */ 99         EXEC SQL PREPARE S FROM :dyn_statement;100         parse_flag = 0;     /* Unset the flag. */101 102         EXEC SQL DECLARE C CURSOR FOR S;103 104         /* Set the bind variables for any placeholders in the105            SQL statement. */106         set_bind_variables();107 108         /* Open the cursor and execute the statement.109          * If the statement is not a query (SELECT), the110          * statement processing is completed after the111          * OPEN.112          */113 114         EXEC SQL OPEN C USING DESCRIPTOR bind_dp;115 116         /* Call the function that processes the select-list.117          * If the statement is not a query, this function118          * just returns, doing nothing.119          */120         process_select_list();121 122         /* Tell user how many rows processed. */123         for (i = 0; i < 8; i++)124         {125            if (strncmp(dyn_statement, dml_commands[i], 6) == 0)126            {127                printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],128                        sqlca.sqlerrd[2] == 1 ? ‘\0‘ : ‘s‘);129                break;130            }131         }132     }       /* end of for(;;) statement-processing loop */133 134     /* When done, free the memory allocated for135        pointers in the bind and select descriptors. */136     for (i = 0; i < MAX_ITEMS; i++)137     {    138         if (bind_dp->V[i] != (char *) 0)139             free(bind_dp->V[i]);140         free(bind_dp->I[i]);   /* MAX_ITEMS were allocated. */141         if (select_dp->V[i] != (char *) 0)142             free(select_dp->V[i]);143         free(select_dp->I[i]); /* MAX_ITEMS were allocated. */144     }145 146     /* Free space used by the descriptors themselves. */147     SQLSQLDAFree(SQL_SINGLE_RCTX, bind_dp);148     SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);149 150     EXEC SQL WHENEVER SQLERROR CONTINUE;151     /* Close the cursor. */152     EXEC SQL CLOSE C;153 154     EXEC SQL COMMIT WORK RELEASE;155     puts("\nHave a good day!\n");156 157     EXEC SQL WHENEVER SQLERROR DO sql_error();158     return;159 }160 161 162 int oracle_connect()163 {164     EXEC SQL BEGIN DECLARE SECTION;165         VARCHAR  username[128];166         VARCHAR  password[32];167     EXEC SQL END DECLARE SECTION;168 169     printf("\nusername: ");170     fgets((char *) username.arr, sizeof username.arr, stdin);171     username.arr[strlen((char *) username.arr)-1] = ‘\0‘;172     username.len = (unsigned short)strlen((char *) username.arr);173 174     printf("password: ");175     fgets((char *) password.arr, sizeof password.arr, stdin);176     password.arr[strlen((char *) password.arr) - 1] = ‘\0‘;177     password.len = (unsigned short)strlen((char *) password.arr);178 179 180     EXEC SQL WHENEVER SQLERROR GOTO connect_error;181 182     EXEC SQL CONNECT :username IDENTIFIED BY :password;183 184     printf("\nConnected to ORACLE as user %s.\n", username.arr);185 186     return 0;187 188 connect_error:189     fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);190     return -1;191 }192 193 194 /*195  *  Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().196  *  Also allocate the pointers to indicator variables197  *  in each descriptor.  The pointers to the actual bind198  *  variables and the select-list items are realloc‘ed in199  *  the set_bind_variables() or process_select_list()200  *  routines.  This routine allocates 1 byte for select_dp->V[i]201  *  and bind_dp->V[i], so the realloc will work correctly.202  */203 204 alloc_descriptors(size, max_vname_len, max_iname_len)205 int size;206 int max_vname_len;207 int max_iname_len;208 {209     int i;210 211     /*212      * The first SQLSQLDAAlloc parameter is the runtime context.213 214      * The second parameter determines the maximum number of215      * array elements in each variable in the descriptor. In216      * other words, it determines the maximum number of bind217      * variables or select-list items in the SQL statement.218      *219      * The third parameter determines the maximum length of220      * strings used to hold the names of select-list items221      * or placeholders.  The maximum length of column 222      * names in ORACLE is 30, but you can allocate more or less223      * as needed.224      *225      * The fourth parameter determines the maximum length of226      * strings used to hold the names of any indicator227      * variables.  To follow ORACLE standards, the maximum228      * length of these should be 30.  But, you can allocate229      * more or less as needed.230      */231 232     if ((bind_dp =233        SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) == 234          (SQLDA *) 0)235     {236         fprintf(stderr,237             "Cannot allocate memory for bind descriptor.");238         return -1;  /* Have to exit in this case. */239     }240 241     if ((select_dp =242         SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) == 243            (SQLDA *) 0)244     {245         fprintf(stderr,246             "Cannot allocate memory for select descriptor.");247         return -1;248     }249     select_dp->N = MAX_ITEMS;250 251     /* Allocate the pointers to the indicator variables, and the252        actual data. */253     for (i = 0; i < MAX_ITEMS; i++) {254         bind_dp->I[i] = (short *) malloc(sizeof (short));255         select_dp->I[i] = (short *) malloc(sizeof(short));256         bind_dp->V[i] = (char *) malloc(1);257         select_dp->V[i] = (char *) malloc(1);258     }259        260     return 0;261 }262 263 264 int get_dyn_statement()265 {266     char *cp, linebuf[256];267     int iter, plsql;268 269 270     for (plsql = 0, iter = 1; ;)271     {272         if (iter == 1)273         {274             printf("\nSQL> ");275             dyn_statement[0] = ‘\0‘;276         }277         278         fgets(linebuf, sizeof linebuf, stdin);279 280         cp = strrchr(linebuf, ‘\n‘);281         if (cp && cp != linebuf)282             *cp = ‘ ‘;283         else if (cp == linebuf)284             continue;285 286         if ((strncmp(linebuf, "EXIT", 4) == 0) ||287             (strncmp(linebuf, "exit", 4) == 0))288         {289             return -1;290         }291 292         else if (linebuf[0] == ‘?‘ ||293             (strncmp(linebuf, "HELP", 4) == 0) ||294             (strncmp(linebuf, "help", 4) == 0))295         {296             help();297             iter = 1;298             continue;299         }300 301         if (strstr(linebuf, "BEGIN") ||302             (strstr(linebuf, "begin")))303         {304             plsql = 1;305         }306 307         strcat(dyn_statement, linebuf);308 309         if ((plsql && (cp = strrchr(dyn_statement, ‘/‘))) ||310             (!plsql && (cp = strrchr(dyn_statement, ‘;‘))))311         {312             *cp = ‘\0‘;313             break;314         }315         else316         {317             iter++;318             printf("%3d  ", iter);319         }320     }321     return 0;322 }323 324 325 326 void set_bind_variables()327 {328     int i, n;329     char bind_var[64];330 331     /* Describe any bind variables (input host variables) */332     EXEC SQL WHENEVER SQLERROR DO sql_error();333 334     bind_dp->N = MAX_ITEMS;  /* Initialize count of array elements. */335     EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;336 337     /* If F is negative, there were more bind variables338        than originally allocated by SQLSQLDAAlloc(). */339     if (bind_dp->F < 0)340     {341         printf ("\nToo many bind variables (%d), maximum is %d\n.",342                     -bind_dp->F, MAX_ITEMS);343         return;344     }345 346     /* Set the maximum number of array elements in the347        descriptor to the number found. */348     bind_dp->N = bind_dp->F;349  350     /* Get the value of each bind variable as a351      * character string.352      *   353      * C[i] contains the length of the bind variable354      *      name used in the SQL statement.355      * S[i] contains the actual name of the bind variable356      *      used in the SQL statement.357      *358      * L[i] will contain the length of the data value359      *      entered.360      *361      * V[i] will contain the address of the data value362      *      entered.363      *364      * T[i] is always set to 1 because in this sample program365      *      data values for all bind variables are entered366      *      as character strings.367      *      ORACLE converts to the table value from CHAR.368      *369      * I[i] will point to the indicator value, which is370      *      set to -1 when the bind variable value is "null".371      */372     for (i = 0; i < bind_dp->F; i++)373     {374         printf ("\nEnter value for bind variable %.*s:  ",375                (int)bind_dp->C[i], bind_dp->S[i]);376         fgets(bind_var, sizeof bind_var, stdin);377 378         /* Get length and remove the new line character. */379         n = strlen(bind_var) - 1;380 381         /* Set it in the descriptor. */382         bind_dp->L[i] = n;383 384         /* (re-)allocate the buffer for the value.385            SQLSQLDAAlloc() reserves a pointer location for386            V[i] but does not allocate the full space for387            the pointer. */388 389          bind_dp->V[i] = (char *) realloc(bind_dp->V[i],390                          (bind_dp->L[i] + 1));            391 392         /* And copy it in. */393         strncpy(bind_dp->V[i], bind_var, n);394 395         /* Set the indicator variable‘s value. */396         if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||397                 (strncmp(bind_dp->V[i], "null", 4) == 0))398             *bind_dp->I[i] = -1;399         else400             *bind_dp->I[i] = 0;401     402         /* Set the bind datatype to 1 for CHAR. */403         bind_dp->T[i] = 1;404     }405   return;406 }407 408 409 410 void process_select_list()411 {412     int i, null_ok, precision, scale;413 414     if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&415         (strncmp(dyn_statement, "select", 6) != 0))416     {417         select_dp->F = 0;418         return;419     }420 421     /* If the SQL statement is a SELECT, describe the422         select-list items.  The DESCRIBE function returns423         their names, datatypes, lengths (including precision424         and scale), and NULL/NOT NULL statuses. */425 426     select_dp->N = MAX_ITEMS;427     428     EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;429 430     /* If F is negative, there were more select-list431        items than originally allocated by SQLSQLDAAlloc(). */432     if (select_dp->F < 0)433     {434         printf ("\nToo many select-list items (%d), maximum is %d\n",435                 -(select_dp->F), MAX_ITEMS);436         return;437     }438 439     /* Set the maximum number of array elements in the440        descriptor to the number found. */441     select_dp->N = select_dp->F;442 443     /* Allocate storage for each select-list item.444   445        SQLNumberPrecV6() is used to extract precision and scale446        from the length (select_dp->L[i]).447 448        sqlcolumnNullCheck() is used to reset the high-order bit of449        the datatype and to check whether the column450        is NOT NULL.451 452        CHAR    datatypes have length, but zero precision and453                scale.  The length is defined at CREATE time.454 455        NUMBER  datatypes have precision and scale only if456                defined at CREATE time.  If the column457                definition was just NUMBER, the precision458                and scale are zero, and you must allocate459                the required maximum length.460 461        DATE    datatypes return a length of 7 if the default462                format is used.  This should be increased to463                9 to store the actual date character string.464                If you use the TO_CHAR function, the maximum465                length could be 75, but will probably be less466                (you can see the effects of this in SQL*Plus).467 468        ROWID   datatype always returns a fixed length of 18 if469                coerced to CHAR.470 471        LONG and472        LONG RAW datatypes return a length of 0 (zero),473                so you need to set a maximum.  In this example,474                it is 240 characters.475 476        */477     478     printf ("\n");479     for (i = 0; i < select_dp->F; i++)480     {481         char title[MAX_VNAME_LEN]; 482         /* Turn off high-order bit of datatype (in this example,483            it does not matter if the column is NOT NULL). */484         SQLColumnNullCheck (0, (unsigned short *)&(select_dp->T[i]), 485              (unsigned short *)&(select_dp->T[i]), &null_ok);486 487         switch (select_dp->T[i])488         {489             case  1 : /* CHAR datatype: no change in length490                          needed, except possibly for TO_CHAR491                          conversions (not handled here). */492                 break;493             case  2 : /* NUMBER datatype: use SQLNumberPrecV6() to494                          extract precision and scale. */495                 SQLNumberPrecV6( SQL_SINGLE_RCTX, 496                       (unsigned long *)&(select_dp->L[i]), &precision, &scale);497                       /* Allow for maximum size of NUMBER. */498                 if (precision == 0) precision = 40;499                       /* Also allow for decimal point and500                          possible sign. */501                 /* convert NUMBER datatype to FLOAT if scale > 0,502                    INT otherwise. */503                 if (scale > 0)504                     select_dp->L[i] = sizeof(float);505                 else506                     select_dp->L[i] = sizeof(int);507                 break;508 509             case  8 : /* LONG datatype */510                 select_dp->L[i] = 240;511                 break;512 513             case 11 : /* ROWID datatype */514                 select_dp->L[i] = 18;515                 break;516 517             case 12 : /* DATE datatype */518                 select_dp->L[i] = 9;519                 break;520  521             case 23 : /* RAW datatype */522                 break;523 524             case 24 : /* LONG RAW datatype */525                 select_dp->L[i] = 240;526                 break;527         }528         /* Allocate space for the select-list data values.529            SQLSQLDAAlloc() reserves a pointer location for530            V[i] but does not allocate the full space for531            the pointer.  */532 533          if (select_dp->T[i] != 2)534            select_dp->V[i] = (char *) realloc(select_dp->V[i],535                                     select_dp->L[i] + 1);  536          else537            select_dp->V[i] = (char *) realloc(select_dp->V[i],538                                     select_dp->L[i]);  539 540         /* Print column headings, right-justifying number541             column headings. */542         543         /* Copy to temporary buffer in case name is null-terminated */544         memset(title, ‘ ‘, MAX_VNAME_LEN);545         strncpy(title, select_dp->S[i], select_dp->C[i]);546         if (select_dp->T[i] == 2)547            if (scale > 0)548              printf ("%.*s ", select_dp->L[i]+3, title);549            else550              printf ("%.*s ", select_dp->L[i], title);551         else552           printf("%-.*s ", select_dp->L[i], title);553 554         /* Coerce ALL datatypes except for LONG RAW and NUMBER to555            character. */556         if (select_dp->T[i] != 24 && select_dp->T[i] != 2)557             select_dp->T[i] = 1;558 559         /* Coerce the datatypes of NUMBERs to float or int depending on560            the scale. */561         if (select_dp->T[i] == 2)562           if (scale > 0)563              select_dp->T[i] = 4;  /* float */564           else565              select_dp->T[i] = 3;  /* int */566     }567     printf ("\n\n");568 569     /* FETCH each row selected and print the column values. */570     EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;571 572     for (;;)573     {574         EXEC SQL FETCH C USING DESCRIPTOR select_dp;575 576         /* Since each variable returned has been coerced to a577            character string, int, or float very little processing 578            is required here.  This routine just prints out the 579            values on the terminal. */580         for (i = 0; i < select_dp->F; i++)581         {582             if (*select_dp->I[i] < 0)583                 if (select_dp->T[i] == 4) 584                   printf ("%-*c ",(int)select_dp->L[i]+3, ‘ ‘);585                 else586                   printf ("%-*c ",(int)select_dp->L[i], ‘ ‘);587             else588                 if (select_dp->T[i] == 3)     /* int datatype */589                   printf ("%*d ", (int)select_dp->L[i], 590                                  *(int *)select_dp->V[i]);591                 else if (select_dp->T[i] == 4)     /* float datatype */592                   printf ("%*.2f ", (int)select_dp->L[i], 593                                  *(float *)select_dp->V[i]);594                 else                          /* character string */595                   printf ("%-*.*s ", (int)select_dp->L[i],596                             (int)select_dp->L[i], select_dp->V[i]);597         }598         printf ("\n");599     }600 end_select_loop:601     return;602 }603 604 605 606 void help()607 {608     puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");609     puts("Statements can be continued over several lines, except");610     puts("within string literals.");611     puts("Terminate a SQL statement with a semicolon.");612     puts("Terminate a PL/SQL block (which can contain embedded semicolons)");613     puts("with a slash (/).");614     puts("Typing \"exit\" (no semicolon needed) exits the program.");615     puts("You typed \"?\" or \"help\" to get this message.\n\n");616 }617 618 619 void sql_error()620 {621     /* ORACLE error handler */622     printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);623     if (parse_flag)624         printf625         ("Parse error at character offset %d in SQL statement.\n",626            sqlca.sqlerrd[4]);627 628     EXEC SQL WHENEVER SQLERROR CONTINUE;629     EXEC SQL ROLLBACK WORK;630     longjmp(jmp_continue, 1);631 }