2 Default value testing with all datatypes.(TINYINT,SMALLINT,INT,BIGINT,CHAR,VARCHAR,FLOAT,DOUBLE,DATE,TIME,TIMESTAMP))
3 CREATE TABLE t1(f1 TINYINT DEFAULT 1,f2 SMALLINT DEFAULT 11,f3 INT DEFAULT 111,f4 BIGINT DEFAULT 1111,f5 CHAR(20) DEFAULT 'CSQL1',f6 VARCHAR(30) DEFAULT 'Lakshya1',f7 FLOAT DEFAULT 11.11,f8 DOUBLE DEFAULT 1111.11,f9 DATE DEFAULT '2001/01/01',f10 TIME DEFAULT '01:01:01',f11 TIMESTAMP DEFAULT '2001/01/01 01:01:01');
4 Insert a record say (insert into t1 (f1) values(1); It should insert default values in all other fields.
5 Update all the inserted default field values to null. it should update.
6 Insert null values in all the fields , it should accept null values.
15 AbsSqlConnection
*con
= createConnection();
16 rv
= con
->connect("root","manager");
21 printf("Connection opened\n");
22 AbsSqlStatement
*stmt
= createStatement();
23 stmt
->setConnection(con
);
26 printf("CREATE TABLE t1(f1 TINYINT DEFAULT 1,f2 SMALLINT DEFAULT 11,f3 INT DEFAULT 111,f4 BIGINT DEFAULT 1111,f5 CHAR(20) DEFAULT 'CSQL1',f6 VARCHAR(30) DEFAULT 'Lakshya1',f7 FLOAT DEFAULT 11.11,f8 DOUBLE DEFAULT 1111.11,f9 DATE DEFAULT '2001-01-01',f10 TIME DEFAULT '01:01:01',f11 TIMESTAMP DEFAULT '2001-01-01 01:01:01');\n");
27 strcpy(statement
,"CREATE TABLE t1(f1 TINYINT DEFAULT 1,f2 SMALLINT DEFAULT 11,f3 INT DEFAULT 111,f4 BIGINT DEFAULT 1111,f5 CHAR(20) DEFAULT 'CSQL1',f6 VARCHAR(30) DEFAULT 'Lakshya1',f7 FLOAT DEFAULT 11.11,f8 DOUBLE DEFAULT 1111.11,f9 DATE DEFAULT '2001-01-01',f10 TIME DEFAULT '01:01:01',f11 TIMESTAMP DEFAULT '2001-01-01 01:01:01');");
29 rv
= stmt
->prepare(statement
);
30 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 2; }
31 rv
= stmt
->execute(rows
);
32 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 3; }
35 strcpy(statement
,"GETALLTABLES;");
37 rv
= stmt
->prepare(statement
);
38 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 4; }
40 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 5; }
41 while(stmt
->next() !=NULL
) {
42 printf("Table Name is %s\n",stmt
->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
49 printf("INSERT INTO t1(f1) VALUES(1);\n");
50 stmt
->prepare("INSERT INTO t1(f1) VALUES(1);");
52 printf("INSERT INTO t1 VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);\n");
53 stmt
->prepare("INSERT INTO t1 VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);");
55 printf("INSERT INTO t1 VALUES(NULL,33,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);\n");
56 stmt
->prepare("INSERT INTO t1 VALUES(NULL,33,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);");
72 //Fetching records after insert
73 strcpy(statement
,"SELECT * FROM t1;");
74 rv
= stmt
->prepare(statement
);
75 if(rv
!=OK
) { delete stmt
; delete con
; return 6; }
76 stmt
->bindField(1,&f1var
);
77 stmt
->bindField(2,&f2var
);
78 stmt
->bindField(3,&f3var
);
79 stmt
->bindField(4,&f4var
);
80 stmt
->bindField(5,f5var
);
81 stmt
->bindField(6,f6var
);
82 stmt
->bindField(7,&f7var
);
83 stmt
->bindField(8,&f8var
);
84 stmt
->bindField(9,&f9var
);
85 stmt
->bindField(10,&f10var
);
86 stmt
->bindField(11,&f11var
);
88 rv
= con
->beginTrans();
91 while(stmt
->fetch() !=NULL
) {
92 if(stmt
->isFldNull(1)) printf("f1(tinyint)=NULL | ");
93 else printf("f1(tinyint)=%d | ", f1var
);
94 if(stmt
->isFldNull(2)) printf("f2(smallint)=NULL | ");
95 else printf("f2(smallint)=%d | ", f2var
);
96 if(stmt
->isFldNull(3)) printf("f3(int)=NULL | ");
97 else printf("f3(int)=%d | ", f3var
);
98 if(stmt
->isFldNull(4)) printf("f4(bigint)=NULL | ");
99 else printf("f4(bigint)=%lld | ", f4var
);
100 if(stmt
->isFldNull(5)) printf("f5(char)=NULL | ");
101 else printf("f5(char)=%s | ", f5var
);
102 if(stmt
->isFldNull(6)) printf("f6(varchar)=NULL | ");
103 else printf("f6(varchar)=%s | ", f6var
);
104 if(stmt
->isFldNull(7)) printf("f7(float)=NULL | ");
105 else printf("f7(float)=%f | ", f7var
);
106 if(stmt
->isFldNull(8)) printf("f8(double)=NULL | ");
107 else printf("f8(double)=%lf | ", f8var
);
108 if(stmt
->isFldNull(9)) printf("f9(date)=NULL | ");
109 else printf("f9(date)=%02d-%02d-%02d | ", f9var
.year(),f9var
.month(),f9var
.dayOfMonth());
110 if(stmt
->isFldNull(10)) printf("f10(time)=NULL | ");
111 else printf("f10(time)=%02d:%02d:%02d | ", f10var
.hours(),f10var
.minutes(),f10var
.seconds());
112 if(stmt
->isFldNull(11)) printf("f11(timestamp)=NULL | ");
113 else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var
.year(),f11var
.month(),f11var
.dayOfMonth(),f11var
.hours(),f11var
.minutes(),f11var
.seconds());
119 printf("%d rows selected\n",count
);
120 //Updating Default fields to NULL
122 strcpy(statement
,"UPDATE t1 SET f1=NULL,f2=NULL,f3=NULL,f4=NULL,f5=NULL,f6=NULL,f7=NULL,f8=NULL,f9=NULL,f10=NULL,f11=NULL where f1=1;");
123 rv
= stmt
->prepare(statement
);
124 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 8; }
125 printf("Prepared\n");
126 rv
= stmt
->execute(rows
);
127 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 9; }
128 printf("Executed\n");
132 //Fetching records after Update
133 strcpy(statement
,"SELECT * FROM t1;");
134 rv
= stmt
->prepare(statement
);
135 if(rv
!=OK
) { delete stmt
; delete con
; return 10; }
136 stmt
->bindField(1,&f1var
);
137 stmt
->bindField(2,&f2var
);
138 stmt
->bindField(3,&f3var
);
139 stmt
->bindField(4,&f4var
);
140 stmt
->bindField(5,f5var
);
141 stmt
->bindField(6,f6var
);
142 stmt
->bindField(7,&f7var
);
143 stmt
->bindField(8,&f8var
);
144 stmt
->bindField(9,&f9var
);
145 stmt
->bindField(10,&f10var
);
146 stmt
->bindField(11,&f11var
);
148 rv
= con
->beginTrans();
151 while(stmt
->fetch() !=NULL
) {
152 if(stmt
->isFldNull(1)) printf("f1(tinyint)=NULL | ");
153 else printf("f1(tinyint)=%d | ", f1var
);
154 if(stmt
->isFldNull(2)) printf("f2(smallint)=NULL | ");
155 else printf("f2(smallint)=%d | ", f2var
);
156 if(stmt
->isFldNull(3)) printf("f3(int)=NULL | ");
157 else printf("f3(int)=%d | ", f3var
);
158 if(stmt
->isFldNull(4)) printf("f4(bigint)=NULL | ");
159 else printf("f4(bigint)=%lld | ", f4var
);
160 if(stmt
->isFldNull(5)) printf("f5(char)=NULL | ");
161 else printf("f5(char)=%s | ", f5var
);
162 if(stmt
->isFldNull(6)) printf("f6(varchar)=NULL | ");
163 else printf("f6(varchar)=%s | ", f6var
);
164 if(stmt
->isFldNull(7)) printf("f7(float)=NULL | ");
165 else printf("f7(float)=%f | ", f7var
);
166 if(stmt
->isFldNull(8)) printf("f8(double)=NULL | ");
167 else printf("f8(double)=%lf | ", f8var
);
168 if(stmt
->isFldNull(9)) printf("f9(date)=NULL | ");
169 else printf("f9(date)=%02d-%02d-%02d | ", f9var
.year(),f9var
.month(),f9var
.dayOfMonth());
170 if(stmt
->isFldNull(10)) printf("f10(time)=NULL | ");
171 else printf("f10(time)=%02d:%02d:%02d | ", f10var
.hours(),f10var
.minutes(),f10var
.seconds());
172 if(stmt
->isFldNull(11)) printf("f11(timestamp)=NULL | ");
173 else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var
.year(),f11var
.month(),f11var
.dayOfMonth(),f11var
.hours(),f11var
.minutes(),f11var
.seconds());
179 printf("%d rows selected\n",count
);
181 strcpy(statement
,"DROP TABLE t1;");
182 rv
= stmt
->prepare(statement
);
183 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 12; }
184 rv
= stmt
->execute(rows
);
185 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 13; }
186 printf("Table dropped\n");
189 printf("Connection Closed\n");
191 delete stmt
; delete con
;