test/sqlapi/Csql/DDLStmt/copystatement.c

   1 /*
   2   CopyStatement Testing.(field level and table level).
   3   CREATE TABLE MASTER(f1 TINYINT ,f2 SMALLINT ,f3 INT ,f4 BIGINT ,f5 CHAR(20) ,f6 VARCHAR(30) ,f7 FLOAT ,f8 DOUBLE ,f9 DATE ,f10 TIME ,f11 TIMESTAMP);
   4   CREATE TABLE COPYMASTER AS SELECT * FROM MASTER;
   5   CREATE TABLE COPYMASTER_FIELDLEVEL AS SELECT f2,f5,f1,f9,f11,f10,f6,f4,f7,f3 FROM MASTER;
   6   CREATE TABLE COPYMASTER_DUPLICATEFIELD AS SELECT f2,f5,f2 FROM MASTER; This should be failed
   7 */
   8
   9 #include"common.h"
  10
  11 int main()
  12 {
  13     DbRetVal rv = OK;
  14     AbsSqlConnection *con = createConnection();
  15     rv = con->connect("root","manager");
  16     if(rv !=OK) {
  17         delete con;
  18         return 1;
  19     }
  20     printf("Connection opened\n");
  21     AbsSqlStatement *stmt = createStatement();
  22     stmt->setConnection(con);
  23 //Creating Table
  24     char statement[400];
  25     strcpy(statement,"CREATE TABLE MASTER(f1 TINYINT ,f2 SMALLINT ,f3 INT ,f4 BIGINT ,f5 CHAR(20) ,f6 VARCHAR(30) ,f7 FLOAT ,f8 DOUBLE ,f9 DATE ,f10 TIME ,f11 TIMESTAMP);");
  26     int rows=0;
  27     rv = stmt->prepare(statement);
  28     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  29     rv = stmt->execute(rows);
  30     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  31     printf("Table MASTER Created\n");
  32     stmt->free();
  33
  34     strcpy(statement,"CREATE TABLE COPYMASTER AS SELECT * FROM MASTER;");
  35     rows=0;
  36     rv = stmt->prepare(statement);
  37     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  38     rv = stmt->execute(rows);
  39     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  40     printf("Table COPYMASTER Created\n");
  41     stmt->free();
  42
  43     strcpy(statement,"CREATE TABLE COPYMASTER_FIELDLEVEL AS SELECT f2,f5,f1,f9,f11,f10,f6,f4,f7,f3 FROM MASTER;");
  44     rows=0;
  45     rv = stmt->prepare(statement);
  46     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
  47     rv = stmt->execute(rows);
  48     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
  49     printf("Table COPYMASTER_FIELDLEVEL Created\n");
  50     stmt->free();
  51
  52     strcpy(statement,"CREATE TABLE COPYMASTER_DUPLICATEFIELD AS SELECT f2,f5,f2 FROM MASTER;");
  53     rows=0;
  54     rv = stmt->prepare(statement);
  55     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 8; }
  56     printf("Table COPYMASTER_DUPLICATEFIELD can not be Created because of duplicate fields\n");
  57
  58 // Show all tables
  59     strcpy(statement,"GETALLTABLES;");
  60     rows=0;
  61     rv = stmt->prepare(statement);
  62     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
  63     stmt->execute(rows);
  64     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
  65     printf("\t TABLES\n");
  66     printf("\t--------\n");
  67     while(stmt->next() !=NULL) {
  68         printf("\t%s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  69     }
  70     stmt->free();
  71
  72 //Droping tables
  73     rv = stmt->prepare("DROP TABLE MASTER;");
  74     rv = stmt->execute(rows);
  75     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 11; }
  76     printf("MASTER Table Dropped\n");
  77     stmt->free();
  78     rv = stmt->prepare("DROP TABLE COPYMASTER;");
  79     rv = stmt->execute(rows);
  80     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
  81     printf("COPYMASTER Table Dropped\n");
  82     stmt->free();
  83     rv = stmt->prepare("DROP TABLE COPYMASTER_FIELDLEVEL;");
  84     rv = stmt->execute(rows);
  85     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
  86     printf("COPYMASTER_FIELDLEVEL Table Dropped\n");
  87     stmt->free();
  88
  89 // Show all tables
  90     strcpy(statement,"GETALLTABLES;");
  91     rows=0;
  92     rv = stmt->prepare(statement);
  93     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
  94     stmt->execute(rows);
  95     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
  96     while(stmt->next() !=NULL) {
  97         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  98     }
  99
 100     stmt->free();
 101     con->disconnect();
 102     printf("Connection Closed\n");
 103
 104     delete stmt; delete con;
 105     return 0;
 106 }
 107