1 Reply Latest reply: Nov 1, 2012 4:27 AM by 943610 RSS

    Pro*C: logging embedded SQL

    943610
      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
          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 ;