adding test scripts
[csql.git] / test / sqlapi / Csql / DMLStmt / insertonalldatatype.c
blob02718b42a6816221e40f9454c0c4585c6229790a
1 /*
2 Insert records into table in 2 ways(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.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');
7 INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11) VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');
8 INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10) VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03');
9 SELECT * FROM t1;
11 #include"common.h"
13 int main()
15 DbRetVal rv = OK;
16 AbsSqlConnection *con = createConnection();
17 rv = con->connect("root","manager");
18 if(rv !=OK) {
19 delete con;
20 return 1;
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);\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);");
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();
36 // Show all tables
37 strcpy(statement,"GETALLTABLES;");
38 rows=0;
39 rv = stmt->prepare(statement);
40 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
41 stmt->execute(rows);
42 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
43 while(stmt->next() !=NULL) {
44 printf("Table Name is %s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
46 stmt->free();
48 //Inserting Records
49 con->beginTrans();
50 rows=0;
51 printf("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');\n");
52 stmt->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
53 stmt->execute(rows);
55 printf("INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11) VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');\n");
56 stmt->prepare("INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11) VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
57 stmt->execute(rows);
59 printf("INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10) VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03');\n");
60 stmt->prepare("INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10) VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03');");
61 stmt->execute(rows);
62 stmt->free();
63 con->commit();
65 char f1var;
66 short int f2var;
67 int f3var;
68 long long f4var;
69 char f5var[20];
70 char f6var[32];
71 float f7var;
72 double f8var;
73 Date f9var;
74 Time f10var;
75 TimeStamp f11var;
77 //Fetching records after insert
78 strcpy(statement,"SELECT * FROM t1;");
79 rv = stmt->prepare(statement);
80 if(rv!=OK) { delete stmt; delete con; return 6; }
81 stmt->bindField(1,&f1var);
82 stmt->bindField(2,&f2var);
83 stmt->bindField(3,&f3var);
84 stmt->bindField(4,&f4var);
85 stmt->bindField(5,f5var);
86 stmt->bindField(6,f6var);
87 stmt->bindField(7,&f7var);
88 stmt->bindField(8,&f8var);
89 stmt->bindField(9,&f9var);
90 stmt->bindField(10,&f10var);
91 stmt->bindField(11,&f11var);
92 int count=0;
93 rv = con->beginTrans();
94 if(rv!=OK)return 7;
95 stmt->execute(rows);
96 while(stmt->fetch() !=NULL) {
97 if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
98 else printf("f1(tinyint)=%d | ", f1var);
99 if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
100 else printf("f2(smallint)=%d | ", f2var);
101 if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
102 else printf("f3(int)=%d | ", f3var);
103 if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
104 else printf("f4(bigint)=%lld | ", f4var);
105 if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
106 else printf("f5(char)=%s | ", f5var);
107 if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
108 else printf("f6(varchar)=%s | ", f6var);
109 if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
110 else printf("f7(float)=%f | ", f7var);
111 if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
112 else printf("f8(double)=%lf | ", f8var);
113 if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
114 else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
115 if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
116 else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
117 if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
118 else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
119 printf("\n");
120 count++;
122 stmt->free();
123 rv = con->commit();
124 printf("%d rows selected\n",count);
126 //Droping table
127 strcpy(statement,"DROP TABLE t1;");
128 rv = stmt->prepare(statement);
129 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
130 rv = stmt->execute(rows);
131 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
132 printf("Table dropped\n");
133 stmt->free();
134 con->disconnect();
135 printf("Connection Closed\n");
137 delete stmt; delete con;
138 return 0;