test/sqlapi/Csql/DMLStmt/pkeyfkeydml.c

   1 /*
   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
   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 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));");
  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     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));");
  37     rows=0;
  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 // Show all tables
  44     strcpy(statement,"GETALLTABLES;");
  45     rows=0;
  46     rv = stmt->prepare(statement);
  47     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
  48     stmt->execute(rows);
  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)
  52     }
  53     stmt->free();
  54
  55 //Inserting Records
  56     con->beginTrans();
  57     rows=0;
  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');");
  60     stmt->execute(rows);
  61
  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');");
  64     stmt->execute(rows);
  65
  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");
  72
  73 //Droping table
  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");
  80     stmt->free();
  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");
  87     stmt->free();
  88     con->disconnect();
  89     printf("Connection Closed\n");
  90
  91     delete stmt; delete con;
  92     return 0;
  93 }
  94