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
16 AbsSqlConnection
*con
= createConnection();
17 rv
= con
->connect("root","manager");
22 printf("Connection opened\n");
23 AbsSqlStatement
*stmt
= createStatement();
24 stmt
->setConnection(con
);
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);");
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; }
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; }
46 strcpy(statement
,"GETALLTABLES;");
48 rv
= stmt
->prepare(statement
);
49 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 6; }
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)
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');");
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');");
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");
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
);
100 rv
= con
->beginTrans();
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());
129 printf("%d rows selected\n",count
);
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");
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
);
156 rv
= con
->beginTrans();
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());
185 printf("%d rows selected\n",count
);
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");
196 printf("Connection Closed\n");
198 delete stmt
; delete con
;