2 primary key and foreign key constraint checking(for all datatypes).
3 CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP, primary key(f3));
4 CREATE TABLE t2(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP, foreign key(f3) references t1(f3));
5 Insert 5 records into t1.
6 INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.00,1111.00,'2001-01-01','01:01:01','2001-01-01 01:01:01');
7 INSERT INTO t2 VALUES(10,110,111,11110,'CSQL10','LAKSHYA10',110.00,1111.00,'2010-10-10','10:10:10','2010-10-10 10:10:10');
8 DELETE FROM t1 WHERE f3=111; It 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 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP,primary key(f3));\n");
28 strcpy(statement
,"CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP,primary key(f3));");
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; }
35 printf("CREATE TABLE t2(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP, foreign key(f3) references t1(f3));\n");
36 strcpy(statement
,"CREATE TABLE t2(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP, foreign key(f3) references t1(f3));");
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; }
44 strcpy(statement
,"GETALLTABLES;");
46 rv
= stmt
->prepare(statement
);
47 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 6; }
49 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 7; }
50 while(stmt
->next() !=NULL
) {
51 printf("Table Name is %s\n",stmt
->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
58 printf("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.00,1111.00,'2001-01-01','01:01:01','2001-01-01 01:01:01');\n");
59 stmt
->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.00,1111.00,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
62 printf("INSERT INTO t2 VALUES(10,110,111,11110,'CSQL10','LAKSHYA10',110.00,1111.00,'2010-10-10','10:10:10','2010-10-10 10:10:10');\n");
63 stmt
->prepare("INSERT INTO t2 VALUES(10,110,111,11110,'CSQL10','LAKSHYA10',110.00,1111.00,'2010-10-10','10:10:10','2010-10-10 10:10:10');");
66 printf("DELETE FROM t1 WHERE f3=111;\n");
67 rv
= stmt
->prepare("DELETE FROM t1 WHERE f3=111;");
68 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 8; }
69 rv
= stmt
->execute(rows
);
70 if(rv
==OK
) { delete stmt
; con
->disconnect(); delete con
; return 9; }
71 printf("Record can not be deleted from parrent because there exist a record in child record\n");
74 strcpy(statement
,"DROP TABLE t2;");
75 rv
= stmt
->prepare(statement
);
76 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 10; }
77 rv
= stmt
->execute(rows
);
78 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 11; }
79 printf("Table dropped\n");
81 strcpy(statement
,"DROP TABLE t1;");
82 rv
= stmt
->prepare(statement
);
83 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 12; }
84 rv
= stmt
->execute(rows
);
85 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 13; }
86 printf("Table dropped\n");
89 printf("Connection Closed\n");
91 delete stmt
; delete con
;