This discussion is archived
1 Reply Latest reply: Nov 1, 2012 2:27 AM by 943610 RSS

Pro*C: logging embedded SQL

943610 Newbie
Currently Being Moderated
I want to log my queries. If possible, before executing them, because a query can take long time. Also, I think sqlglst() is bad for getting the statement text because it will work only after the execution and it requires extra buffer.
EXEC SQL insert into dual values (1);
turns into this:
  /* EXEC SQL insert into dual values (1); */ 
{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 4;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.stmt = "insert into dual  values (1)";
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )104;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlcxt(&ctx, &sqlctx, &sqlstm, &sqlfpn);
  SQLCODE = sqlca.sqlcode;
  if (sqlca.sqlcode < 0) sql_error();
}
This line interests me: sqlstm.stmt = "insert into dual  values (1)";
So, at the beginning of the .pc file I place this:
static void _sqlcxt (void ** a, unsigned int * b,
                    struct sqlexd * c, const struct sqlcxp * d) {
  printf("%s\n", c->stmt);
  sqlcxt(a,b,c,d);
}
#define  sqlcxt _sqlcxt
But the problem is: sqlcxt() function is used for everything, not just statements and sqlstm.stmt may be uninitialized. For example:
  /* EXEC SQL rollback; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 1;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )39;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlcxt(&ctx, &sqlctx, &sqlstm, &sqlfpn);
}
How to distinguish them?
  • 1. Re: Pro*C: logging embedded SQL
    943610 Newbie
    Currently Being Moderated
    You useless. Had to dig myself.
    enum eStmtType {
      SQLCUD0_CONTEXT_ALLOCATE = 58,
      SQLCUD0_CONTEXT_FREE     = 59,
      SQLCUD0_CONNECT          = 27,
      SQLCUD0_DELETE           = 2,
      SQLCUD0_INSERT           = 3,
      SQLCUD0_SELECT           = 4,
      SQLCUD0_OPEN             = 9,
      SQLCUD0_FETCH            = 13,
      SQLCUD0_CLOSE            = 15,
      SQLCUD0_OPEN_PREPARED    = 45,
      SQLCUD0_PREPARE          = 17,
      SQLCUD0_CALL             = 122,
      SQLCUD0_ROLLBACK         = 31,
      SQLCUD0_ROLLBACK_RELEASE = 32
    };
    
    struct _sqlcud0_st {
      short offset;
      short x1;
      short x2;
      short nstmt;
      short stmt_len;
      short x5;
      short stmt_type;
    
      unsigned short line_lo; /* 0 - 8191; not a bit field */
      unsigned short line_hi;
    #define _SQLCUD0_ST_LINE(st) ((st)->line_hi * 8192 + (st)->line_lo)
    
      short x9;
      short nparams;
      short n_in_params;
      short x12;
      short x13;
      short x14;
    };
    
    void _sqlcxt (const char *file, void ** a, unsigned int * b,
                        struct sqlexd * c, const struct sqlcxp * d)
    {
      #define FMT_UNKNOWN_TYPE "<unknown type>: %d"
      #define FMT_PREPARE      "PREPARE stmt_%d FROM"
      #define FMT_OPEN     "OPEN stmt_%d_cur"
      #define FMT_FETCH        "FETCH stmt_%d_cur"
      #define FMT_CLOSE        "CLOSE stmt_%d_cur"
    
      union {
        char unktyp[sizeof(FMT_UNKNOWN_TYPE "-32768")];
        char prep[sizeof(FMT_PREPARE "-32768")];
        char open[sizeof(FMT_OPEN "-32768")];
        char fetch[sizeof(FMT_FETCH "-32768")];
        char close[sizeof(FMT_CLOSE "-32768")];
      } bufs;
      const struct _sqlcud0_st *magic = (const struct _sqlcud0_st *)(c->cud + c->offset);
      int line = _SQLCUD0_ST_LINE(magic);
      const char *stmt;
      const char *s_stmt_type = "";
      size_t stmt_len = (size_t)-1;
    
      if (magic->stmt_len > 0) {
        stmt = c->stmt;
      } else {
        stmt = "";
      }
      
      switch(magic->stmt_type) {
        case SQLCUD0_CONTEXT_ALLOCATE :
          s_stmt_type = "CONTEXT ALLOCATE";
          break;
        case SQLCUD0_CONTEXT_FREE     :
          s_stmt_type = "CONTEXT FREE";
          break;
        case SQLCUD0_CONNECT          :
          s_stmt_type = "CONNECT";
          break;
        case SQLCUD0_ROLLBACK         :
          s_stmt_type = "ROLLBACK";
          break;
        case SQLCUD0_ROLLBACK_RELEASE :
          s_stmt_type = "ROLLBACK RELEASE";
          break;
        case SQLCUD0_PREPARE:
          sprintf(bufs.prep, FMT_PREPARE, magic->nstmt);
          s_stmt_type = bufs.prep;
          if (magic->n_in_params == 1 && magic->stmt_len == 0) {
            stmt = c->sqhstv[0];
            stmt_len = c->sqhstl[0];
          }
          break;
        case SQLCUD0_OPEN:
          printf("%s:%d EXEC SQL DECLARE stmt_%d_cur CURSOR FOR %s;\n", file, line, magic->nstmt, c->stmt);
          stmt = "";
          goto L1;
        case SQLCUD0_OPEN_PREPARED:
          printf("%s:%d EXEC SQL DECLARE stmt_%d_cur CURSOR FOR stmt_%d;\n", file, line, magic->nstmt, magic->nstmt);
          L1:
          sprintf(bufs.open, FMT_OPEN, magic->nstmt);
          s_stmt_type = bufs.open;
          break;
        case SQLCUD0_FETCH:
          sprintf(bufs.fetch, FMT_FETCH, magic->nstmt);
          s_stmt_type = bufs.fetch;
          break;
        case SQLCUD0_CLOSE:
          sprintf(bufs.fetch, FMT_CLOSE, magic->nstmt);
          s_stmt_type = bufs.close;
          break;
        default:
          sprintf(bufs.unktyp, FMT_UNKNOWN_TYPE, magic->stmt_type);
          s_stmt_type = bufs.unktyp;
          /* fallthrough */
        case SQLCUD0_DELETE:
        case SQLCUD0_INSERT:
        case SQLCUD0_SELECT:
        case SQLCUD0_CALL:
          ;
      }
    
      printf("%s:%d EXEC SQL %s %.*s;\n", file, line, s_stmt_type, stmt_len, stmt);
    
      printf("\n");
    
      sqlcxt(a,b,c,d);
    }
    
    #define  sqlcxt(a,b,c,d) _sqlcxt(__FILE__, a,b,c,d);
      EXEC SQL PREPARE stmt1 from "select 123 from dual";
      EXEC SQL PREPARE stmt2 from "select 123 from dual";
    
      EXEC SQL DECLARE cur1 CURSOR FOR stmt1; EXEC SQL OPEN cur1;
      EXEC SQL DECLARE cur2 CURSOR FOR stmt2; EXEC SQL OPEN cur2;
      EXEC SQL declare cur3 cursor for select 123 from dual; EXEC SQL OPEN cur3;
    
      EXEC SQL FETCH cur1 into :i;
      EXEC SQL FETCH cur2 into :i;
      EXEC SQL FETCH cur3 into :i;
      EXEC SQL CLOSE cur1;
      EXEC SQL CLOSE cur2;
      EXEC SQL CLOSE cur3;
    
      EXEC SQL DELETE FROM T1;
      EXEC SQL INSERT INTO T1(A) VALUES(1);
      EXEC SQL select 123 into :i from dual;
    test-oracle-proc.pc:14 EXEC SQL PREPARE stmt_1 FROM select 123 from dual;
    
    test-oracle-proc.pc:15 EXEC SQL PREPARE stmt_2 FROM select 123 from dual;
    
    test-oracle-proc.pc:17 EXEC SQL DECLARE stmt_1_cur CURSOR FOR stmt_1;
    test-oracle-proc.pc:17 EXEC SQL OPEN stmt_1_cur ;
    
    test-oracle-proc.pc:18 EXEC SQL DECLARE stmt_2_cur CURSOR FOR stmt_2;
    test-oracle-proc.pc:18 EXEC SQL OPEN stmt_2_cur ;
    
    test-oracle-proc.pc:19 EXEC SQL DECLARE stmt_3_cur CURSOR FOR select 123  from dual            ;
    test-oracle-proc.pc:19 EXEC SQL OPEN stmt_3_cur ;
    
    test-oracle-proc.pc:21 EXEC SQL FETCH stmt_1_cur ;
    
    test-oracle-proc.pc:22 EXEC SQL FETCH stmt_2_cur ;
    
    test-oracle-proc.pc:23 EXEC SQL FETCH stmt_3_cur ;
    
    test-oracle-proc.pc:24 EXEC SQL CLOSE stmt_1_cur ;
    
    test-oracle-proc.pc:25 EXEC SQL CLOSE stmt_2_cur ;
    
    test-oracle-proc.pc:26 EXEC SQL CLOSE stmt_3_cur ;
    
    test-oracle-proc.pc:28 EXEC SQL  delete  from T1 ;
    
    test-oracle-proc.pc:29 EXEC SQL  insert into T1 (A) values (1);
    
    test-oracle-proc.pc:30 EXEC SQL  select 123 into :b0  from dual ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points