Proc 动态SQL实现SQLPlus功能源代码

#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>

#define MAX_ITEMS 40
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

int parse_flag = 0;
jmp_buf jmp_continue;
char * dml_command[] = {"SELECT", "select", "UPDATE", "update", "DELETE", "INSERT", "insert"};
SQLDA * bind_dp;
SQLDA * select_dp;

EXEC SQL BEGIN DECLARE SECTION;
    char dyn_statement[1024];
 EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;

void sql_error();
int oracle_connect();
int alloc_descriptors(int, int, int);
int get_dyna_statement();
void set_bind_variables();
void process_select_list();
void help();

void main(int argc, char * argv[])
{
 int i;
 if (oracle_connect() != 0)
  exit(1);

 if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
  exit(1);

 while (1)
 {
  setjmp(jmp_continue);

  if (get_dyna_statement() != 0)
   break;

  EXEC SQL WHENEVER SQLERROR DO sql_error();
  parse_flag = 1;

  EXEC SQL PREPARE S FROM :dyn_statement;
  parse_flag = 0;

  EXEC SQL DECLARE C CURSOR FOR S;

  set_bind_variables();

  EXEC SQL OPEN C USING DESCRIPTOR bind_dp;

  process_select_list();

  for (i = 0; i < 8; i++)
  {
   if (strncmp(dyn_statement, dml_command[i], 6) == 0)
   {
    printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
    sqlca.sqlerrd[2] == 1 ? '\0' : 's');
    break;
   }
  }
  
 }

 //释放资源
 //printf("start free 1...\n");
 for (i = 0; i < MAX_ITEMS; i++)
 {
  if (bind_dp->V[i] != (char *) NULL)
   free(bind_dp->V[i]);
  free(bind_dp->I[i]);

  if (select_dp->V[i] != (char *) NULL)
   free(select_dp->V[i]);
  free(select_dp->I[i]);
 }
 //printf("free 1 ok!\n");

 SQLSQLDAFree(NULL, bind_dp);
 SQLSQLDAFree(NULL, select_dp);

 EXEC SQL WHENEVER SQLERROR CONTINUE;
 EXEC SQL CLOSE C;
 EXEC SQL COMMIT WORK RELEASE;

 puts("\nprogram complete normal!\n");
 EXEC SQL WHENEVER SQLERROR DO sql_error();
 return;
}

void sql_error()
{
 printf("\n\n%.70s", sqlca.sqlerrm.sqlerrmc);
 if (parse_flag)
  printf("parse error at character offset %d in sql statement.\n", sqlca.sqlerrd[4]);
 EXEC SQL WHENEVER SQLERROR CONTINUE;
 EXEC SQL ROLLBACK WORK;
 longjmp(jmp_continue, 1);
}

int oracle_connect()
{
 char * cp;

 EXEC SQL BEGIN DECLARE SECTION;
  varchar username[20];
  varchar password[40];
 EXEC SQL END DECLARE SECTION;
 printf("username:");
 fflush(stdin);
 fgets((char *)username.arr, sizeof(username.arr), stdin);
 cp = strchr(username.arr, '\n');
 if (cp)
  *cp = '\0';
 username.len = strlen((char*) username.arr);

 printf("password:");
 fflush(stdin);
 fgets((char*)password.arr, sizeof(password.arr), stdin);
 cp = strchr(password.arr, '\n');
 if (cp)
  *cp = '\0';
 password.len = strlen((char*) password.arr);
 
 EXEC SQL WHENEVER SQLERROR GOTO connect_error;
 printf("start connect database...\n");
 EXEC SQL CONNECT :username IDENTIFIED BY :password;
 printf("connect database successfully!\n");
 return 0;

connect_error:
 printf("can not connect database as user: %.*s\n", username.len, (char*) username.arr);
 return -1;
 
}

int alloc_descriptors(int size, int max_vname_len, int max_iname_len)
{
 int i;
 bind_dp = SQLSQLDAAlloc(NULL, size, max_vname_len, max_iname_len);
 if (bind_dp == (SQLDA*)NULL)
 {
  printf("can not allocate memory for bind descriptor\n");
  return -1;
 }
 bind_dp->N = MAX_ITEMS;

    select_dp = SQLSQLDAAlloc(NULL, size, max_vname_len, max_iname_len);
 if (select_dp == (SQLDA *)NULL)
 {
  printf("can not allocate memory for select descriptor\n");
  return -1;
 }

 select_dp->N = MAX_ITEMS;

 for (i = 0; i < MAX_ITEMS; i++)
 {
  bind_dp->I[i] = (short *) malloc(sizeof(short));
  select_dp->I[i] = (short *) malloc(sizeof(short));
  bind_dp->V[i] = (char *) malloc(1);
  select_dp->V[i] = (char *) malloc(1);
 }
 return 0;
}

int get_dyna_statement()
{
 char * cp, linebuf[256];
 int iter, plsql;

 for (plsql = 0, iter = 1; ;)
 {
  if (iter == 1)
  {
   printf("\nSQL> ");
   dyn_statement[0] = '\0';
  }

  fgets(linebuf, sizeof(linebuf), stdin);
  cp = strchr(linebuf, '\n');

  if (cp && cp != linebuf)
   *cp = '\0';
  else if (cp == linebuf)
   continue;
 
  if ((strncmp(linebuf, "EXIT", 4) == 0) || (strncmp(linebuf, "exit", 4) == 0))
  {
   return -1;
  }
  else if ((linebuf[0] == '?') || (strncmp(linebuf, "help", 4) == 0) || (strncmp(linebuf, "HELP", 4) == 0))
  {
   help();
   iter = 1;
   continue;
  }

  if (strstr(linebuf, "BEGIN") || strstr(linebuf, "begin"))
   plsql = 1;

  strcat(dyn_statement, linebuf);
  if ((plsql && (cp = strchr(dyn_statement, '/'))) || (!plsql && (cp = strchr(dyn_statement, ';'))))
  {
   *cp = '\0';
   break;
  }
  else
  {
   iter++;
   printf("%3d ", iter);
  }
 }

 return 0;
}

void set_bind_variables()
{
 int i, n;
 char * cp;
 char bind_var[64];
 EXEC SQL WHENEVER SQLERROR DO sql_error();
 bind_dp->N = MAX_ITEMS;

 EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

 if (bind_dp->F < 0)
 {
  printf("\ntoo many bind variables (%d) maximum is %d.\n", -bind_dp->F, MAX_ITEMS);
  return;
 }
 bind_dp->N = bind_dp->F;

 for (i = 0; i < bind_dp->F; i++)
 {
  printf("\nEnter value for bind variables %.*s", (int) bind_dp->C[i], bind_dp->S[i]);
  fgets(bind_var, sizeof(bind_var), stdin);
  cp = strchr(bind_var, '\n');
  if (cp)
   *cp = '\0';
  n = strlen(bind_var);
  bind_dp->L[i] = n;
  bind_dp->V[i] = (char*) realloc(bind_dp->V[i], bind_dp->L[i]);
  strncpy(bind_dp->V[i], bind_var, n);
  if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) || (strncmp(bind_dp->V[i], "null", 4) == 0))
   *(bind_dp->I[i]) = -1;
  else
   *(bind_dp->I[i]) = 0;
  bind_dp->T[i] = 1;
 }
}

void process_select_list()
{
 int i, null_ok, precision, scale;
 char title[MAX_VNAME_LEN];

 if ((strncmp(dyn_statement, "SELECT", 6) != 0) && (strncmp(dyn_statement, "select", 6) != 0))
 {
  select_dp->F = 0;
  return;
 }

 select_dp->N = MAX_ITEMS;
 EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

 if (select_dp->F < 0)
 {
  printf("\nToo many select_list items (%d) maximum is %d\n", -select_dp->F, MAX_ITEMS);
  return;
 }

 select_dp->N = select_dp->F;
 printf("\n");

 for (i = 0; i < select_dp->F; i++)
 {
  SQLColumnNullCheck(NULL, &(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
  switch (select_dp->T[i])
  {
   case 1: break;  //VARCHAR2
   case 2: //NUMBER
    SQLNumberPrecV6(NULL, &(select_dp->L[i]), &precision, &scale);
    if (precision == 0)
     precision = 40;
    if (scale > 0)
     select_dp->L[i] = sizeof(float);
    else
     select_dp->L[i] = sizeof(int);
    break;

   case 8: //LONG
    select_dp->L[i] = 240;
    break;
   case 11: //ROW ID
    select_dp->L[i] = 18;
    break;
   case 12: //DATE
    select_dp->L[i] = 9;
    break;
   case 23: //RAW
    break;
   case 24: //LONG RAW
    select_dp->L[i] = 240;
    break;
  }

  if (select_dp->T[i] != 2)
   select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i] + 1);
  else
   select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i]);

  memset(title, '\0', MAX_VNAME_LEN);
  strncpy(title, select_dp->S[i], select_dp->C[i]);

  if (select_dp->T[i] == 2)
   if (scale > 0)
    printf("%-*.*s ", select_dp->L[i] + 3, select_dp->C[i], title);
   else
    printf("%-*.*s ", select_dp->L[i], select_dp->C[i], title);
  else
   printf("%-*.*s ", select_dp->L[i], select_dp->C[i], title);

  if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
   select_dp->T[i] = 1;
     else if (select_dp->T[i] == 2)
  {
   if (scale > 0)
    select_dp->T[i] = 4;     //float 类型
   else
    select_dp->T[i] = 3;     //int 类型
  }
 }
 printf("\n\n");

 EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;

 while (1)
 {
  EXEC SQL FETCH C USING DESCRIPTOR select_dp;
  for (i = 0; i < select_dp->F; i++)
  {
   if (*(select_dp->I[i]) < 0)
   {
    if (select_dp->T[i] == 4)
     printf("%-*c", (int) select_dp->L[i] + 3, '\0');
    else
     printf("%-*c", (int) select_dp->L[i], '\0');
   }
   else
   {
    if (select_dp->T[i] == 3)  //integer
     printf("%-*d ", (int) select_dp->C[i] > (int) select_dp->L[i] ? (int) select_dp->C[i] : (int) select_dp->L[i], *((int*) select_dp->V[i]));
    else if (select_dp->T[i] == 4) //float
     printf("%-*.2f ", (int) select_dp->C[i] > (int) select_dp->L[i] ? (int) select_dp->C[i] : (int) select_dp->L[i], *((float*) select_dp->V[i]));
    else //string
     printf("%-*.*s ", (int) select_dp->L[i], (int) select_dp->L[i], select_dp->V[i]);

   }
  }
  printf("\n");
 }

end_select_loop:
 return;
}

void help()
{
 puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
 puts("Statements can be continued over several lines, except");
 puts("within string literals.");
 puts("Terminate a SQL statement with a semicolon.");
 puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
 puts("with a slash (/).");
 puts("Typing \"exit\" (no semicolon needed) exits the program.");
 puts("You typed \"?\" or \"help\" to get this message.\n\n");
}