test/sqlapi/Parameter/paratest8now.c

   1 /* select tuple with WHERE clause having param for some fields
   2    Create a table having f1 date, f2 tnt and f3 string
   3    Insert 5-6 records
   4    Execute SELECT * FROM T1 WHERE f1='now' AND f2=? AND f3=?;
   5    Author : Nihar Paital.
   6 */
   7
   8 #include"common.h"
   9
  10 int main()
  11 {
  12     DbRetVal rv = OK;
  13     AbsSqlConnection *con = createConnection();
  14     rv = con->connect("root","manager");
  15     if(rv!=OK)return 1;
  16     AbsSqlStatement *stmt = createStatement();
  17     stmt->setConnection(con);
  18     char statement[200];
  19     strcpy(statement,"CREATE TABLE T1(F1 DATE,F2 INT,F3 CHAR(10));");
  20     int rows = 0;
  21     rv = stmt->prepare(statement);
  22     if(rv!=OK) { delete stmt; delete con; return 2; }
  23     rv = stmt->execute(rows);
  24     if(rv!=OK) { delete stmt; delete con; return 3; }
  25     stmt->free();
  26     printf("Table created\n");
  27     con->beginTrans();
  28     stmt->prepare("INSERT INTO T1 values('2008-10-21',0,'Nihar0');");
  29     stmt->execute(rows);
  30     stmt->prepare("INSERT INTO T1 values('now',1,'Nihar1');");
  31     stmt->execute(rows);
  32     stmt->prepare("INSERT INTO T1 values('2008-10-24',2,'Nihar2');");
  33     stmt->execute(rows);
  34     stmt->prepare("INSERT INTO T1 values('now',3,'Nihar3');");
  35     stmt->execute(rows);
  36     stmt->prepare("INSERT INTO T1 values('2008-10-21',4,'Nihar4');");
  37     stmt->execute(rows);
  38     stmt->prepare("INSERT INTO T1 values('now',5,'Nihar5');");
  39     stmt->execute(rows);
  40     con->commit();
  41     stmt->free();
  42     //**********************************************************
  43     // SELECT * FROM T1;
  44     Date f1var;
  45     f1var.set(2001,01,01);
  46     int f2var=0;
  47     char f3var[32]="CSQL";
  48     printf("SELECT * FROM T1;\n");
  49     strcpy(statement,"SELECT * FROM T1;");
  50     stmt->prepare(statement);
  51     stmt->bindField(1,&f1var);
  52     stmt->bindField(2,&f2var);
  53     stmt->bindField(3,f3var);
  54     int count=0;
  55     con->beginTrans();
  56     stmt->execute(rows);
  57     while(stmt->fetch() !=NULL) {
  58        printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var.year(), f1var.month(), f1var.dayOfMonth(), f2var,f3var);
  59         printf("\n");
  60         count++;
  61     }
  62     stmt->close();
  63     con->commit();
  64     printf("%d Rows fetched\n",count);
  65     stmt->free();
  66
  67     printf("SELECT * FROM T1 WHERE F1='now';\n");
  68     strcpy(statement,"SELECT * FROM T1 WHERE F1='now';");
  69     stmt->prepare(statement);
  70     stmt->bindField(1,&f1var);
  71     stmt->bindField(2,&f2var);
  72     stmt->bindField(3,f3var);
  73     count=0;
  74     con->beginTrans();
  75     stmt->execute(rows);
  76     while(stmt->fetch() !=NULL) {
  77        printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var.year(), f1var.month(), f1var.dayOfMonth(), f2var,f3var);
  78         printf("\n");
  79         count++;
  80     }
  81     stmt->close();
  82     con->commit();
  83     printf("%d Rows fetched\n",count);
  84     stmt->free();
  85
  86     printf("SELECT * FROM T1 where F2 >= ? and F3 <= ?;\n");
  87     strcpy(statement,"SELECT * FROM T1 where F2 >= ? and F3 <= ?;");
  88     stmt->prepare(statement);
  89     f2var=1;
  90     strcpy(f3var,"Nihar3");
  91     stmt->setIntParam(1,f2var);
  92     stmt->setStringParam(2,f3var);
  93     stmt->bindField(1,&f1var);
  94     stmt->bindField(2,&f2var);
  95     stmt->bindField(3,f3var);
  96     count=0;
  97     con->beginTrans();
  98     stmt->execute(rows);
  99     while(stmt->fetch() !=NULL) {
 100        printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var.year(), f1var.month(), f1var.dayOfMonth(), f2var,f3var);
 101         printf("\n");
 102         count++;
 103     }
 104     stmt->close();
 105     con->commit();
 106     printf("%d Rows fetched\n",count);
 107     stmt->free();
 108
 109     printf("SELECT * FROM T1 where F1='now' and F2 >= ? and F3 <= ?;\n");
 110     strcpy(statement,"SELECT * FROM T1 where F1='now' and  F2 >= ? and F3 <= ?;");
 111     stmt->prepare(statement);
 112     f2var=1;
 113     strcpy(f3var,"Nihar3");
 114     stmt->setIntParam(1,f2var);
 115     stmt->setStringParam(2,f3var);
 116     stmt->bindField(1,&f1var);
 117     stmt->bindField(2,&f2var);
 118     stmt->bindField(3,f3var);
 119     count=0;
 120     con->beginTrans();
 121     stmt->execute(rows);
 122     while(stmt->fetch() !=NULL) {
 123        printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var.year(), f1var.month(), f1var.dayOfMonth(), f2var,f3var);
 124         printf("\n");
 125         count++;
 126     }
 127     stmt->close();
 128     con->commit();
 129     printf("%d Rows fetched\n",count);
 130     stmt->free();
 131
 132     strcpy(statement,"DROP TABLE T1;");
 133     rv = stmt->prepare(statement);
 134     if(rv!=OK) { delete stmt; delete con; return 4; }
 135     rv = stmt->execute(rows);
 136     if(rv!=OK) { delete stmt; delete con; return 5; }
 137     printf("Table dropped\n");
 138     stmt->free(); delete stmt; delete con;
 139     return 0;
 140 }