adding test scripts
[csql.git] / test / sqlapi / Select / selecttest10.c
blob8a79b5b136299d5c04ecbc0c56e431023a3834bb
1 /* create table T1 with 10 fields,( F2 NOT NULL , F3 CHAR(20) DEFAULT 'LAKSHYA')
2 *
3 * insert 10 rows for 9 Fields except F3 Field into the table
4 * noofprojFields should return 5.
5 * getProjFldInfo() for all fields.
6 * select with 5 parameters withIwhere clause.(WHERE F1=100)
8 * Author : Jitendra Lenka
9 */
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)return 1;
19 AbsSqlStatement *stmt = createStatement();
20 stmt->setConnection(con);
21 char statement[200];
22 strcpy(statement,"CREATE TABLE T1(F1 INT,F2 SMALLINT NOT NULL,F3 CHAR(30) DEFAULT 'LAKSHYA',F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 INT,F10 BIGINT);");
23 int rows=0;
24 rv = stmt->prepare(statement);
25 if(rv!=OK) { delete stmt; delete con; return 2; }
26 rv = stmt->execute(rows);
27 if(rv!=OK) { delete stmt; delete con; return 3; }
28 stmt->free();
29 printf("'T1' Table created\n");
30 // insert records
31 strcpy(statement,"INSERT INTO T1(F1,F2,F4,F5,F6,F7,F8,F9,F10)VALUES(?,?,?,?,?,?,?,?,?);");
32 int f1var = 99;
33 short int f2var = 1;
34 // char f3var[20] = "prabakaran";
35 float f4var = 5.5;
36 float f5var = 10.50;
37 Date f6var;
38 f6var.set(2008,1,21);
39 Time f7var;
40 f7var.set(12,29,30);
41 TimeStamp f8var;
42 f8var.setDate(2008,1,21);
43 f8var.setTime(12,29,30);
44 int f9var = 20;
45 long long f10var = 12000;
46 rv = stmt->prepare(statement);
47 if(rv!=OK) { delete stmt; delete con; return 4; }
48 int count=0;
49 for(int i=0;i<2;i++) {
50 f1var = f1var+i;
51 for(int j=0;j<5;j++) {
52 rv = con->beginTrans();
53 if(rv!=OK)break;
54 stmt->setIntParam(1,f1var);
55 stmt->setShortParam(2,f2var);
56 //stmt->setStringParam(3,f3var);
57 stmt->setFloatParam(3,f4var);
58 stmt->setFloatParam(4,f5var);
59 stmt->setDateParam(5,f6var);
60 stmt->setTimeParam(6,f7var);
61 stmt->setTimeStampParam(7,f8var);
62 stmt->setIntParam(8,f9var);
63 stmt->setLongLongParam(9,f10var);
64 rv = stmt->execute(rows);
65 if(rv!=OK)break;
66 rv = con->commit();
67 if(rv!=OK)break;
68 count++;
71 printf("%d Rows Inserted\n",count);
72 stmt->free();
73 //*******************************************
74 strcpy(statement,"SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100;");
75 rv = stmt->prepare(statement);
76 int nop;
77 nop = stmt->noOfProjFields();
78 if(nop!=5)return 1;
79 printf("Total number of projection fields in the select statement=%d\n",nop);
80 // getParamFldInfo()
81 FieldInfo *field = new FieldInfo();
82 printf("\nFIELD INFORMATION :\n");
83 char fldName[20];
84 DataType type;
85 size_t length;
86 int offset;
87 char defaultValueBuf[10];
88 bool isNull;
89 bool isDefault;
90 for(int i=0;i<=4;i++) {
91 printf("\n***********************************************\n");
92 rv = stmt->getProjFldInfo(i+1,field);
93 if(rv!=OK)return 1;
94 printf("Field Name :%s\n", field->fldName);
95 printf("Data type of the field :%d\n",field->type);
96 printf("length of the field :%d\n",field->length);
97 printf("Offset :%d\n",field->offset);
98 printf("Default value Buffer :%s\n",field->defaultValueBuf);
99 printf("Null field :%d\n",field->isNull);
100 printf("Default value :%d\n",field->isDefault);
101 printf("***********************************************\n");
103 char f3var[32]="jitu";
104 stmt->bindField(1,&f8var);
105 stmt->bindField(2,&f10var);
106 stmt->bindField(3,&f2var);
107 stmt->bindField(4,&f6var);
108 stmt->bindField(5,f3var);
109 count = 0;
110 rv = con->beginTrans();
111 if(rv!=OK)return 6;
112 stmt->execute(rows);
113 while(stmt->fetch()!=NULL) {
114 printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIME=%d:%d:%d | TIMESTAMP=%d-%d-%d %d:%d:%d | F9=%d | F10=%lld \n",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f7var.hours(),f7var.minutes(),f7var.seconds(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
115 count++;
117 stmt->close();
118 rv = con->commit();
119 if(rv!=OK) { delete stmt; delete con; return 6; }
120 printf("Total row fetched=%d\n",count);
121 stmt->free();
122 strcpy(statement,"DROP TABLE T1;");
123 rv = stmt->prepare(statement);
124 if(rv!=OK) { delete stmt; delete con; return 7; }
125 rv = stmt->execute(rows);
126 if(rv!=OK) { delete stmt; delete con; return 8; }
127 printf("Table dropped\n");
128 stmt->free(); delete stmt; delete con;
129 return 0;