test/sqlapi/Csql/DMLStmt/uniquekeyviolation.c

   1 /*
   2  Unique key violation test
   3  CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);
   4  create index idxbigint on t1(f4);
   5  INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');
   6  INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');
   7  INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02'); Insertion should fail
   8  UPDATE t1 SET f4=1111 WHERE f4=2222; Should fail
   9  select * from t1;
  10 */
  11 #include"common.h"
  12
  13 int main()
  14 {
  15     DbRetVal rv = OK;
  16     AbsSqlConnection *con = createConnection();
  17     rv = con->connect("root","manager");
  18     if(rv !=OK) {
  19         delete con;
  20         return 1;
  21     }
  22     printf("Connection opened\n");
  23     AbsSqlStatement *stmt = createStatement();
  24     stmt->setConnection(con);
  25 //Creating Table
  26     char statement[400];
  27     printf("CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);\n");
  28     strcpy(statement,"CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);");
  29     int rows=0;
  30     rv = stmt->prepare(statement);
  31     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  32     rv = stmt->execute(rows);
  33     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  34     stmt->free();
  35
  36     printf("create index idxbigint on t1(f4) unique;\n");
  37     strcpy(statement,"create index idxbigint on t1(f4) unique;");
  38     rv = stmt->prepare(statement);
  39     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  40     rv = stmt->execute(rows);
  41     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  42     stmt->free();
  43
  44
  45 // Show all tables
  46     strcpy(statement,"GETALLTABLES;");
  47     rows=0;
  48     rv = stmt->prepare(statement);
  49     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
  50     stmt->execute(rows);
  51     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
  52     while(stmt->next() !=NULL) {
  53         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  54     }
  55     stmt->free();
  56
  57 //Inserting Records
  58     con->beginTrans();
  59     rows=0;
  60     printf("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');\n");
  61     stmt->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
  62     stmt->execute(rows);
  63     printf("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');\n");
  64     stmt->prepare("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
  65     stmt->execute(rows);
  66     printf("INSERT INTO t1 VALUES(2,22,333,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');\n");
  67     stmt->prepare("INSERT INTO t1 VALUES(2,22,333,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
  68     rv = stmt->execute(rows);
  69     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 8; }
  70     printf("Insertion of duplicate record failed\n");
  71     con->commit();
  72     stmt->free();
  73
  74     char f1var;
  75     short int f2var;
  76     int f3var;
  77     long long f4var;
  78     char f5var[20];
  79     float f6var;
  80     double f7var;
  81     Date f8var;
  82     Time f9var;
  83     TimeStamp f10var;
  84
  85 //Fetching records after insert
  86     strcpy(statement,"SELECT * FROM t1;");
  87     rv = stmt->prepare(statement);
  88     if(rv!=OK) { delete stmt; delete con; return 9; }
  89     stmt->bindField(1,&f1var);
  90     stmt->bindField(2,&f2var);
  91     stmt->bindField(3,&f3var);
  92     stmt->bindField(4,&f4var);
  93     stmt->bindField(5,f5var);
  94     stmt->bindField(6,&f6var);
  95     stmt->bindField(7,&f7var);
  96     stmt->bindField(8,&f8var);
  97     stmt->bindField(9,&f9var);
  98     stmt->bindField(10,&f10var);
  99     int count=0;
 100     rv = con->beginTrans();
 101     if(rv!=OK)return 10;
 102     stmt->execute(rows);
 103     while(stmt->fetch() !=NULL) {
 104         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 105         else printf("f1(tinyint)=%d | ", f1var);
 106         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 107         else printf("f2(smallint)=%d | ", f2var);
 108         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 109         else printf("f3(int)=%d | ", f3var);
 110         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 111         else printf("f4(bigint)=%lld | ", f4var);
 112         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 113         else printf("f5(char)=%s | ", f5var);
 114         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 115         else printf("f6(float)=%f | ", f6var);
 116         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 117         else printf("f7(double)=%lf | ", f7var);
 118         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 119         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 120         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 121         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 122         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 123         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 124         printf("\n");
 125         count++;
 126     }
 127     stmt->free();
 128     rv = con->commit();
 129     printf("%d rows selected\n",count);
 130
 131 //Updating records.
 132     con->beginTrans();
 133     printf("UPDATE t1 SET f4=1111 WHERE f4=2222;\n");
 134     strcpy(statement,"UPDATE t1 SET f4=1111 WHERE f4=2222;");
 135     rv = stmt->prepare(statement);
 136     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 11; }
 137     printf("Update Failed due to unique key can not be updated\n");
 138     con->commit();
 139     stmt->free();
 140
 141 //Fetching records after update
 142     strcpy(statement,"SELECT * FROM t1;");
 143     rv = stmt->prepare(statement);
 144     if(rv!=OK) { delete stmt; delete con; return 13; }
 145     stmt->bindField(1,&f1var);
 146     stmt->bindField(2,&f2var);
 147     stmt->bindField(3,&f3var);
 148     stmt->bindField(4,&f4var);
 149     stmt->bindField(5,f5var);
 150     stmt->bindField(6,&f6var);
 151     stmt->bindField(7,&f7var);
 152     stmt->bindField(8,&f8var);
 153     stmt->bindField(9,&f9var);
 154     stmt->bindField(10,&f10var);
 155     count=0;
 156     rv = con->beginTrans();
 157     if(rv!=OK)return 14;
 158     stmt->execute(rows);
 159     while(stmt->fetch() !=NULL) {
 160         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 161         else printf("f1(tinyint)=%d | ", f1var);
 162         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 163         else printf("f2(smallint)=%d | ", f2var);
 164         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 165         else printf("f3(int)=%d | ", f3var);
 166         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 167         else printf("f4(bigint)=%lld | ", f4var);
 168         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 169         else printf("f5(char)=%s | ", f5var);
 170         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 171         else printf("f6(float)=%f | ", f6var);
 172         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 173         else printf("f7(double)=%lf | ", f7var);
 174         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 175         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 176         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 177         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 178         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 179         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 180         printf("\n");
 181         count++;
 182     }
 183     stmt->free();
 184     rv = con->commit();
 185     printf("%d rows selected\n",count);
 186
 187 //Droping table
 188     strcpy(statement,"DROP TABLE t1;");
 189     rv = stmt->prepare(statement);
 190     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 19; }
 191     rv = stmt->execute(rows);
 192     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 20; }
 193     printf("Table t1 dropped\n");
 194     stmt->free();
 195     con->disconnect();
 196     printf("Connection Closed\n");
 197
 198     delete stmt; delete con;
 199     return 0;
 200 }
 201