test/sqlapi/Csql/DMLStmt/autoincrement.c

   1 /*
   2   Autoincrement test
   3   CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT AUTO_INCREMENT,f4 BIGINT);
   4   Insert 5 records into t1.
   5   INSERT INTO t1 VALUES(1, 10, 100, 1000);
   6   INSERT INTO t1 VALUES(1, 10, NULL, 1000);
   7   INSERT INTO t1 VALUES(1, 10, 200, 1000);
   8   INSERT INTO t1 VALUES(1, 10, 150, 1000);
   9   INSERT INTO t1 VALUES(1, 10, 100, 1000); it should be failed due to primary key viloation
  10   SELECT * FROM t1;
  11 */
  12 #include"common.h"
  13
  14 int main()
  15 {
  16     DbRetVal rv = OK;
  17     AbsSqlConnection *con = createConnection();
  18     rv = con->connect("root","manager");
  19     if(rv !=OK) {
  20         delete con;
  21         return 1;
  22     }
  23     printf("Connection opened\n");
  24     AbsSqlStatement *stmt = createStatement();
  25     stmt->setConnection(con);
  26 //Creating Table
  27     char statement[400];
  28     printf("CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT AUTO_INCREMENT,f4 BIGINT);\n");
  29     strcpy(statement,"CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT AUTO_INCREMENT,f4 BIGINT);");
  30     int rows=0;
  31     rv = stmt->prepare(statement);
  32     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  33     rv = stmt->execute(rows);
  34     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  35     stmt->free();
  36 // Show all tables
  37     strcpy(statement,"GETALLTABLES;");
  38     rows=0;
  39     rv = stmt->prepare(statement);
  40     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  41     stmt->execute(rows);
  42     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  43     while(stmt->next() !=NULL) {
  44         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  45     }
  46     stmt->free();
  47
  48 //Inserting Records
  49     con->beginTrans();
  50     rows=0;
  51     printf("INSERT INTO t1 VALUES(1, 10, 100, 1000);\n");
  52     stmt->prepare("INSERT INTO t1 VALUES(1, 10, 100, 1000);");
  53     stmt->execute(rows);
  54     printf("INSERT INTO t1 VALUES(1, 10, NULL, 1000);\n");
  55     stmt->prepare("INSERT INTO t1 VALUES(1, 10, NULL, 1000);");
  56     stmt->execute(rows);
  57     printf("INSERT INTO t1 VALUES(1, 10, 200, 1000);\n");
  58     stmt->prepare("INSERT INTO t1 VALUES(1, 10, 200, 1000);");
  59     stmt->execute(rows);
  60     printf("INSERT INTO t1 VALUES(1, 10, 150, 1000);\n");
  61     stmt->prepare("INSERT INTO t1 VALUES(1, 10, 150, 1000);");
  62     stmt->execute(rows);
  63     printf("INSERT INTO t1 VALUES(1, 10, 100, 1000);\n");
  64     stmt->prepare("INSERT INTO t1 VALUES(1, 10, 100, 1000);");
  65     rv=stmt->execute(rows);
  66     if(rv=OK) { delete stmt; con->disconnect(); delete con; return 6; }
  67     con->commit();
  68     stmt->free();
  69     char f1var;
  70     short int f2var;
  71     int f3var;
  72     long long f4var;
  73     char f5var[20];
  74     char f6var[32];
  75     float f7var;
  76     double f8var;
  77     Date f9var;
  78     Time f10var;
  79     TimeStamp f11var;
  80
  81 //Fetching records after insert
  82     strcpy(statement,"SELECT * FROM t1;");
  83     rv = stmt->prepare(statement);
  84     if(rv!=OK) { delete stmt; delete con; return 7; }
  85     stmt->bindField(1,&f1var);
  86     stmt->bindField(2,&f2var);
  87     stmt->bindField(3,&f3var);
  88     stmt->bindField(4,&f4var);
  89     int count=0;
  90     rv = con->beginTrans();
  91     if(rv!=OK)return 8;
  92     stmt->execute(rows);
  93     while(stmt->fetch() !=NULL) {
  94         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
  95         else printf("f1(tinyint)=%d | ", f1var);
  96         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
  97         else printf("f2(smallint)=%i | ", f2var);
  98         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
  99         else printf("f3(int)=%d | ", f3var);
 100         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 101         else printf("f4(bigint)=%lld | ", f4var);
 102         printf("\n");
 103         count++;
 104     }
 105     stmt->free();
 106     rv = con->commit();
 107     printf("%d rows selected\n",count);
 108 //Droping table
 109     strcpy(statement,"DROP TABLE t1;");
 110     rv = stmt->prepare(statement);
 111     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
 112     rv = stmt->execute(rows);
 113     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
 114     printf("Table dropped\n");
 115     stmt->free();
 116     con->disconnect();
 117     printf("Connection Closed\n");
 118
 119     delete stmt; delete con;
 120     return 0;
 121 }
 122