次のデモ・プログラムには、Oracle動的SQL方法4を使用したスクロール可能カーソル機能が記述されています。このプログラムは、demoディレクトリのファイルscdemo1.pc
として、オンラインで使用可能です。
scdemo1.pc
/* * This demo program exhibits the scrollable cursor feature * used with oracle dynamic method 4. The scrollable cursor * feature can also be used with ANSI dynamic method 4. * * This program takes as argument the username/passwd. Once * logged in, it prompts for a select query. It then prompts * for the orientation and prints the results of the query. * * Before executing this example, make sure that the HR * schema exists. */ #include <oci.h> #include <stdio.h> #include <sqlca.h> #include <sqlda.h> #include <string.h> #include <ctype.h> #include <sqlcpr.h> #include <stdlib.h> #include <setjmp.h> #define MAX_SELECT_ITEMS 200 #define MAX_CHARS 20 /* Maximum size of a select-list item name */ #define MAX_NAME_SIZE 50 SQLDA *selda; SQLDA *bind_des; jmp_buf beginEnv; jmp_buf loopEnv; /* Data buffer */ char c_data[MAX_SELECT_ITEMS][MAX_CHARS]; char username[60]; char stmt[500]; char stmt2[500]; /* Print the generic error message & exit */ void sql_error() { char msgbuf[512]; size_t msgbuf_len, msg_len; msgbuf_len = sizeof(msgbuf); sqlglm(msgbuf, &msgbuf_len, &msg_len); printf ("\n\n%.*s\n", msg_len, msgbuf); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; exit(EXIT_FAILURE); } /* Print the error message and continue to query the user */ void sql_loop_error() { char msgbuf[512]; size_t msgbuf_len, msg_len; int code = sqlca.sqlcode; msgbuf_len = sizeof(msgbuf); sqlglm(msgbuf, &msgbuf_len, &msg_len); printf ("\n%.*s\n", msg_len, msgbuf); printf("The error code is %d\n", sqlca.sqlcode); if(code!=0) longjmp(beginEnv, 1); longjmp(loopEnv, 1); } /* FETCH has returned the "no data found" error code. This means that either we have reached the end of the active set or the offset refers to a row beyond the active set */ void no_data_found() { printf("\nNo Data available at the specified offset\n"); longjmp(loopEnv, 1); } void main(int argc, char *argv[]) { int i, n; int sli; /* select-list item */ int offset; int contFlag; char bindVar[20]; char *u, temp[3]; char choice; /* Error Handler */ EXEC SQL WHENEVER SQLERROR DO sql_error(); if (argc == 1) { printf("Logging in as default user hr\n"); strcpy(username, "hr/hr"); } else strcpy(username, argv[1]); /* Establish a connection to the data base */ EXEC SQL CONNECT :username; u = username; while(*++u != '/'); *u = '\0'; /* Error Handler */ EXEC SQL WHENEVER SQLERROR DO sql_loop_error(); for (;;) { setjmp(beginEnv); printf("[%s] SQL > ", username); gets(stmt); if (!strlen(stmt)) continue; for (i=0 ; i < strlen(stmt) ; i++) stmt2[i] = tolower(stmt[i]) ; stmt2[i]=0 ; if(!strcmp(stmt2, "exit")) break; selda = SQLSQLDAAlloc(SQL_SINGLE_RCTX, MAX_SELECT_ITEMS, MAX_NAME_SIZE, 0); bind_des = SQLSQLDAAlloc(SQL_SINGLE_RCTX, MAX_SELECT_ITEMS, MAX_NAME_SIZE, 30); /* prepare a sql statement for the query*/ EXEC SQL PREPARE S FROM :stmt; /* Declare a cursor as scrollable */ EXEC SQL DECLARE C SCROLL CURSOR FOR S; for (i=0; i<MAX_SELECT_ITEMS; i++) { bind_des->I[i] = (short *) malloc(sizeof (short)); bind_des->V[i] = (char *) malloc(1); } bind_des->N = MAX_SELECT_ITEMS; EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_des; /* set up the bind variables */ if (bind_des->F < 0) { printf("Bind descriptor, value exceeds the limit\n"); exit(-1); } bind_des->N = bind_des->F; for (i=0; i<bind_des->F; i++) { printf("Enter the value for bind variable %.*s: ", (int)bind_des->C[i], bind_des->S[i]); fgets(bindVar, sizeof(bindVar), stdin); n = strlen(bindVar) - 1; bind_des->L[i] = n; bind_des->V[i] = (char *) realloc(bind_des->V[i], (bind_des->L[i] +1)); strncpy(bind_des->V[i], bindVar, n); if ((strncmp(bind_des->V[i], "NULL", 4) == 0) || (strncmp(bind_des->V[i], "null", 4) == 0)) *bind_des ->I[i] = -1; else *bind_des ->I[i] = 0; bind_des->T[i] = 1; } /* open the cursor */ EXEC SQL OPEN C USING DESCRIPTOR bind_des; EXEC SQL DESCRIBE SELECT LIST FOR S INTO selda; if (selda->F < 0) { printf("Select descriptor, value exceeds the limit\n"); exit(-1); } selda->N = selda->F; for (sli = 0; sli < selda->N; sli++) { /* Set addresses of heads of the arrays in the V element. */ selda->V[sli] = c_data[sli]; /* Convert everything to varchar on output. */ selda->T[sli] = 1; /* Set the maximum lengths. */ selda->L[sli] = MAX_CHARS; } contFlag = 'Y'; setjmp(loopEnv); while(1) { while(contFlag != 'Y' && contFlag != 'N') { printf("\nContinue with the current fetch? [y/n] :"); contFlag = toupper(getchar()); /* To flush the input buffer */ getchar(); } if(contFlag != 'Y') break; contFlag = 'x'; printf("\n\nEnter the row number to be fetched \n"); printf("1.ABSOLUTE\n"); printf("2.RELATIVE\n"); printf("3.FIRST \n"); printf("4.NEXT \n"); printf("5.PREVIOUS \n"); printf("6.LAST \n"); printf("7.CURRENT \n"); printf("Enter your choice --> "); scanf("%c",&choice); /* To flush the input buffer */ getchar(); EXEC SQL WHENEVER NOT FOUND DO no_data_found(); switch(choice) { case '1': printf("\nEnter Offset :"); scanf("%d",&offset); getchar(); EXEC SQL FETCH ABSOLUTE :offset C USING DESCRIPTOR selda; break; case '2': printf("\nEnter Offset :"); scanf("%d",&offset); getchar(); EXEC SQL FETCH RELATIVE :offset C USING DESCRIPTOR selda; break; case '3': EXEC SQL FETCH FIRST C USING DESCRIPTOR selda; break; case '4': EXEC SQL FETCH NEXT C USING DESCRIPTOR selda; break; case '5': EXEC SQL FETCH PRIOR C USING DESCRIPTOR selda; break; case '6': EXEC SQL FETCH LAST C USING DESCRIPTOR selda; break; case '7': EXEC SQL FETCH CURRENT C USING DESCRIPTOR selda; break; default : printf("Invalid choice\n"); contFlag = 'Y'; continue; } /* print the row */ for(sli=0; sli<selda->N; sli++) printf("%.20s ", c_data[sli]); puts(""); } EXEC SQL CLOSE C; } EXEC SQL ROLLBACK RELEASE; exit(EXIT_SUCCESS); }