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 the 105 SQL statement. */ 106 set_bind_variables(); 107 108 /* Open the cursor and execute the statement. 109 * If the statement is not a query (SELECT), the 110 * statement processing is completed after the 111 * 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 function 118 * 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 for 135 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 variables 197 * in each descriptor. The pointers to the actual bind 198 * variables and the select-list items are realloc'ed in 199 * 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 of 215 * array elements in each variable in the descriptor. In 216 * other words, it determines the maximum number of bind 217 * variables or select-list items in the SQL statement. 218 * 219 * The third parameter determines the maximum length of 220 * strings used to hold the names of select-list items 221 * or placeholders. The maximum length of column 222 * names in ORACLE is 30, but you can allocate more or less 223 * as needed. 224 * 225 * The fourth parameter determines the maximum length of 226 * strings used to hold the names of any indicator 227 * variables. To follow ORACLE standards, the maximum 228 * length of these should be 30. But, you can allocate 229 * 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 the 252 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 else 316 { 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 variables 338 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 the 347 descriptor to the number found. */ 348 bind_dp->N = bind_dp->F; 349 350 /* Get the value of each bind variable as a 351 * character string. 352 * 353 * C[i] contains the length of the bind variable 354 * name used in the SQL statement. 355 * S[i] contains the actual name of the bind variable 356 * used in the SQL statement. 357 * 358 * L[i] will contain the length of the data value 359 * entered. 360 * 361 * V[i] will contain the address of the data value 362 * entered. 363 * 364 * T[i] is always set to 1 because in this sample program 365 * data values for all bind variables are entered 366 * as character strings. 367 * ORACLE converts to the table value from CHAR. 368 * 369 * I[i] will point to the indicator value, which is 370 * 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 for 386 V[i] but does not allocate the full space for 387 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 else 400 *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 the 422 select-list items. The DESCRIBE function returns 423 their names, datatypes, lengths (including precision 424 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-list 431 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 the 440 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 scale 446 from the length (select_dp->L[i]). 447 448 sqlcolumnNullCheck() is used to reset the high-order bit of 449 the datatype and to check whether the column 450 is NOT NULL. 451 452 CHAR datatypes have length, but zero precision and 453 scale. The length is defined at CREATE time. 454 455 NUMBER datatypes have precision and scale only if 456 defined at CREATE time. If the column 457 definition was just NUMBER, the precision 458 and scale are zero, and you must allocate 459 the required maximum length. 460 461 DATE datatypes return a length of 7 if the default 462 format is used. This should be increased to 463 9 to store the actual date character string. 464 If you use the TO_CHAR function, the maximum 465 length could be 75, but will probably be less 466 (you can see the effects of this in SQL*Plus). 467 468 ROWID datatype always returns a fixed length of 18 if 469 coerced to CHAR. 470 471 LONG and 472 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 length 490 needed, except possibly for TO_CHAR 491 conversions (not handled here). */ 492 break; 493 case 2 : /* NUMBER datatype: use SQLNumberPrecV6() to 494 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 and 500 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 else 506 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 for 530 V[i] but does not allocate the full space for 531 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 else 537 select_dp->V[i] = (char *) realloc(select_dp->V[i], 538 select_dp->L[i]); 539 540 /* Print column headings, right-justifying number 541 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 else 550 printf ("%.*s ", select_dp->L[i], title); 551 else 552 printf("%-.*s ", select_dp->L[i], title); 553 554 /* Coerce ALL datatypes except for LONG RAW and NUMBER to 555 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 on 560 the scale. */ 561 if (select_dp->T[i] == 2) 562 if (scale > 0) 563 select_dp->T[i] = 4; /* float */ 564 else 565 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 a 577 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 else 586 printf ("%-*c ",(int)select_dp->L[i], ' '); 587 else 588 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 printf 625 ("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 }
1.实现描述区动态扩容
2.包含两个缓冲区：1）输出描述区（存储select结果集）2）输入描述区（存储sql语句）。
3.难点在于自实现动态扩容。
4.如何处理select语句 和 非select语句。---》二者的区别在于：应用程序是否能处理select结果集。
5.输出描述区：（保证存下返回的结果集，并一行一行的解析数据库返回的结果集）
这是一段对上面代码的精简；带注释。
1 /* 包含C头文件 */ 2 #include <stdio.h> 3 #include <string.h> 4 #include <stdlib.h> 5 #include <setjmp.h> 6 #include <sqlcpr.h> 7 8 /* 包含SQLDA和SQLCA结构 */ 9 #include <sqlda.h> 10 #include <sqlca.h> 11 12 /* 定义绑定变量和选择列表项的最大个数 */ 13 #define MAX_ITEMS 40 14 15 /* 定义绑定变量和选择列表项名称的最大长度 */ 16 #define MAX_VNAME_LEN 30 17 18 /* 定义指示变量名称的最大长度 */ 19 #define MAX_INAME_LEN 30 20 21 void connect(); 22 void sql_error(); 23 void alloc_descriptors(int , int , int); 24 void dealloc_descriptors(); 25 void set_bind_variables(); 26 void process_select_list(); 27 28 /* 定义绑定描述区和选择描述区 */ 29 SQLDA* bind_dp; 30 SQLDA* select_dp; 31 32 /* 定义输入宿主变量：存放动态SQL语句 */ 33 char sql_stat[100]; 34 char current_date[20]; 35 36 int main() 37 { 38 /* 安装错误处理句柄 */ 39 exec sql whenever sqlerror do sql_error(); 40 41 /* 连接到数据库 */ 42 connect2(); 43 44 /* 分配绑定描述区和选择描述区 */ 45 alloc_descriptors(MAX_ITEMS , MAX_VNAME_LEN , MAX_INAME_LEN); 46 47 for( ; ; ) 48 { 49 printf("请输入动态SQL语句(exit:退出):"); 50 gets(sql_stat); 51 52 /* EXIT(exit):退出 */ 53 if(0 == strncmp(sql_stat , "exit" , 4) || 0 == strncmp(sql_stat , "EXIT" , 4)) 54 break; 55 56 /* 准备动态SQL语句 */ 57 exec sql prepare s from :sql_stat; 58 59 /* 定义游标 */ 60 exec sql declare c cursor for s; 61 62 /* 出错，继续下一循环 */ 63 if(0 != sqlca.sqlcode) 64 continue; 65 66 /* 设置绑定变量 */ 67 set_bind_variables(); 68 69 /* 70 * 打开游标 71 * select语句：生成结果集 72 * 其他SQL语句：执行语句 73 */ 74 exec sql open c using descriptor bind_dp; 75 76 /* 77 * select语句 78 */ 79 if(0 == strncmp(sql_stat , "select" , 6) || 0 == strncmp(sql_stat , "SELECT" , 6)) 80 { 81 process_select_list(); 82 } 83 /* 关闭游标 */ 84 exec sql close c; 85 } 86 87 /* 释放选择描述区和选择描述区 */ 88 dealloc_descriptors(); 89 90 /* 提交事务，断开连接 */ 91 exec sql commit work release; 92 puts("谢谢使用Oracle动态SQL方法四!\n"); 93 94 return 0; 95 } 96 97 98 void connect2() 99 { 100 /* 定义宿主变量 */ 101 char username[20] , password[20] , server[20]; 102 103 /* 输入用户名、口令和网络服务名 */ 104 printf("username:"); 105 gets(username); 106 107 printf("password: "); 108 gets(password); 109 110 printf("server:"); 111 gets(server); 112 113 /* 连接到数据库 */ 114 EXEC SQL CONNECT :username identified by :password using :server; 115 } 116 117 118 void sql_error() 119 { 120 /* 显示SQL错误信息 */ 121 printf("%.*s\n" , sqlca.sqlerrm.sqlerrml , sqlca.sqlerrm.sqlerrmc); 122 } 123 124 //struct SQLDA { 125 // ub4 // int N; // Descriptor size in number of entries // 126 // text** // char **V; // Ptr to Arr of addresses of main variables // 127 // ub4* // int *L; // Ptr to Arr of lengths of buffers // 128 // sb2* // short *T; // Ptr to Arr of types of buffers // 129 // sb2** // short **I; // Ptr to Arr of addresses of indicator vars // 130 // sb4 // int F; // Number of variables found by DESCRIBE // 131 // text** // char **S; // Ptr to Arr of variable name pointers // 132 // ub2* // short *M; // Ptr to Arr of max lengths of var. names // 133 // ub2* // short *C; // Ptr to Arr of current lengths of var. names // 134 // text** // char **X; // Ptr to Arr of ind. var. name pointers // 135 // ub2* // short *Y; // Ptr to Arr of max lengths of ind. var. names// 136 // ub2* // short *Z; // Ptr to Arr of cur lengths of ind. var. names// 137 // }; 138 139 void alloc_descriptors(int size , int max_vname_len , int max_iname_len) 140 { 141 142 int i; 143 144 /* 分配绑定描述区和选择描述区 */ 145 bind_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN); 146 select_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN); 147 148 149 /* 为指示变量、绑定变量和选择列表项分配内存 */ 150 for(i = 0 ; i != MAX_ITEMS ; ++i) 151 { 152 bind_dp->I[i] = (short*)malloc(sizeof(short)); 153 select_dp->I[i] = (short*)malloc(sizeof(short)); 154 155 bind_dp->V[i] = (char*)malloc(1); 156 select_dp->V[i] = (char*)malloc(1); 157 } 158 } 159 160 void dealloc_descriptors() 161 { 162 int i; 163 164 /* 释放指示变量、绑定变量和选择列表项占用的内存 */ 165 for(i = 0 ; i != MAX_ITEMS ; ++i) 166 { 167 if(bind_dp->V[i] != (char*)0) 168 free(bind_dp->V[i]); 169 free(bind_dp->I[i]); 170 171 if(select_dp->V[i] != (char*)0) 172 free(select_dp->V[i]); 173 free(select_dp->I[i]); 174 } 175 176 /* 释放绑定描述区和选择描述区 */ 177 SQLSQLDAFree(0 , bind_dp); 178 SQLSQLDAFree(0 , select_dp); 179 } 180 181 void set_bind_variables() 182 { 183 int i; 184 char bind_var[64]; 185 186 /* 设置绑定变量最大个数 */ 187 bind_dp->N = MAX_ITEMS; 188 189 /* 绑定变量名称: 绑定描述区 */ 190 exec sql describe bind variables for s into bind_dp; 191 192 /* 设置绑定变量实际个数 */ 193 bind_dp->N = bind_dp->F; 194 195 /* 循环处理绑定变量 */ 196 for(i = 0 ; i != bind_dp->F ; ++i) 197 { 198 /* 显示绑定变量名 */ 199 printf("请输入绑定变量%.*s的值：" , (int)bind_dp->C[i] , bind_dp->S[i]); 200 201 /* 输入绑定变量的值 */ 202 gets(bind_var); 203 204 /* 设置绑定变量的长度成员 */ 205 bind_dp->L[i] = strlen(bind_var); 206 207 /* 为绑定变量数据缓冲区重新分配内存(多一位，留给'\0') */ 208 bind_dp->V[i] = (char*)realloc(bind_dp->V[i] , bind_dp->L[i] + 1); 209 210 /* 绑定变量数据: 数据缓冲区 */ 211 strcpy(bind_dp->V[i] , bind_var); 212 213 /* 设置指示变量，处理NULL */ 214 if(0 == strncmp(bind_var , "NULL" , 4) || 0 == strncmp(bind_var , "null" , 4)) 215 *bind_dp->I[i] = -1; 216 else 217 *bind_dp->I[i] = 0; 218 219 /* 设置数据缓冲区数据类型代码->char */ 220 bind_dp->T[i] = 1; 221 } 222 } 223 224 void process_select_list() 225 { 226 int i , null_ok , precision , scale; 227 char title[MAX_VNAME_LEN]; 228 229 /* 设置选择列表项的最大个数 */ 230 select_dp->N = MAX_ITEMS; 231 232 /* 选择列表项: 选择描述区 */ 233 exec sql describe select list for s into select_dp; 234 235 /* 设置选择列表项的实际个数 */ 236 select_dp->N = select_dp->F; 237 238 /* 循环处理选择列表项 */ 239 for(i = 0 ; i != select_dp->F ; ++i) 240 { 241 /* 清除select_dp->T[i]的高位->null */ 242 SQLColumnNullCheck(0 , (unsigned short*)&select_dp->T[i] 243 , (unsigned short*)&select_dp->T[i] , &null_ok); 244 245 /* 根据内部数据类型确定外部类型数据长度(显示长度) */ 246 switch(select_dp->T[i]) 247 { 248 case 2: 249 /* number类型，取得精度与标度 */ 250 //SQLNumberPrecV6(0 , (unsigned short*)&select_dp->T[i] , &precision , &scale); 251 SQLNumberPrecV6(0 , (unsigned long *)&select_dp->L[i] , &precision , &scale); //wangbaoming modify 201409 252 253 if(scale > 0) 254 /* 实数: 显示长度:float */ 255 select_dp->L[i] = sizeof(float); 256 else 257 /* 整数: 显示长度 int */ 258 select_dp->L[i] = sizeof(int); 259 break; 260 case 12: 261 /* DATA数据类型(DD-MON-YY) */ 262 select_dp->L[i] = 9; 263 break; 264 } 265 266 /* 根据变量长度，重新为选择列表项数据缓冲区分配内存 */ 267 if(2 != select_dp->T[i]) 268 /* 其他类型 */ 269 select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i] + 1); 270 else 271 /* number类型 */ 272 select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i]); 273 274 /* 初始化title */ 275 memset(title , ' ' , MAX_VNAME_LEN); 276 277 /* 选择列表项名称: title */ 278 strncpy(title , select_dp->S[i] , select_dp->C[i]); 279 280 /* 显示列名 */ 281 if(2 == select_dp->T[i]) 282 if(scale > 0) 283 printf("\t%.*s" , select_dp->L[i] + 3, title); 284 else 285 printf("\t%.*s" , select_dp->L[i] , title); 286 else 287 printf("\t%-.*s" , select_dp->L[i] , title); 288 289 /* 根据Oracle内部类型确定外部数据类型(显示类型) */ 290 if( 2 == select_dp->T[i]) 291 { 292 /* number 类型*/ 293 if(scale > 0) 294 /* float */ 295 select_dp->T[i] = 4; 296 else 297 /* int */ 298 select_dp->T[i] = 3; 299 } 300 else 301 /* char */ 302 select_dp->T[i] = 1; 303 } 304 305 printf("\n"); 306 307 /* 提取数据完毕->结束循环 */ 308 exec sql whenever not found do break; 309 310 /* 循环处理选择列表数据 */ 311 for( ; ; ) 312 { 313 /* 数据->选择描述区 */ 314 exec sql fetch c using descriptor select_dp; 315 316 /* 显示数据 */ 317 for( i = 0 ; i != select_dp->F ; ++i) 318 { 319 if(*select_dp->I[i] < 0){ 320 /* 处理NULL */ 321 printf("\tNULL"); 322 }else{ 323 if(3 == select_dp->T[i]) { 324 /* int */ 325 printf("\t%d" , *(int*)select_dp->V[i]); 326 }else if(4 == select_dp->T[i]){ 327 /* float */ 328 printf("\t%8.2f" , *(float*)select_dp->V[i]); 329 }else{ 330 /* char */ 331 printf("\t%.*s" , select_dp->L[i] , select_dp->V[i]); 332 } 333 } 334 } 335 printf("\n"); 336 } 337 }
401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
