tmptest/winodbctest/odbctestcsql.cpp

   1 // odbctestcsql.cpp : Defines the entry point for the console application.
   2 //
   3
   4 int mainfn();
   5
   6 int main(int argc, char* argv[])
   7 {
   8         return mainfn();
   9 }
  10 #include <stdio.h>
  11 #include <string.h>
  12 #include<stdlib.h>
  13 #include<sql.h>
  14 #include<sqlext.h>
  15 #include<time.h>
  16
  17 void checkrc(int rc,int line)
  18 {
  19     if(rc)
  20     {
  21         printf("Error %d at line %d\n", rc, line);
  22         exit(1);
  23     }
  24 }
  25
  26 int mainfn()
  27 {
  28     SQLHENV env;
  29     SQLHDBC dbc;
  30     SQLHSTMT stmt;
  31     SQLRETURN ret;
  32     SQLCHAR outstr[1024];
  33     SQLSMALLINT outstrlen;
  34         printf("STARTED\n");
  35
  36     //Allocate an environment handle
  37     ret = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  38     checkrc(ret,__LINE__);
  39         printf("Allocate env handle\n");
  40
  41     //we need ODBC3 support
  42     SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  43         printf("Allocate setenv handle\n");
  44
  45     //Allocate a Connection handle
  46     ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  47     checkrc(ret,__LINE__);
  48         printf("Allocate conn handle\n");
  49
  50         char dsn[1024];
  51         strcpy(dsn, "DSN=mycsql;MODE=csql;SERVER=192.168.1.3;PORT=5678;");
  52     //connect to Data source
  53     ret = SQLConnect (dbc,
  54                    (SQLCHAR *) dsn, (SQLSMALLINT) strlen (dsn),
  55                    (SQLCHAR *) "root",
  56                    (SQLSMALLINT) strlen ("root"),
  57                    (SQLCHAR *) "manager",
  58                    (SQLSMALLINT) strlen ("manager"));
  59         printf("connect called\n");
  60 /*
  61     //connect using unixODBC Driver Manager
  62     ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)
  63                      "DSN=mycsql;USER=root;PASSWORD=manager;", SQL_NTS,
  64                       outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
  65
  66                                           */
  67
  68     if(SQL_SUCCEEDED(ret))
  69     {
  70         printf("Connected to CSQL\n");
  71     }
  72     else
  73     {
  74         printf("error in connection\n");
  75         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
  76         checkrc(ret,__LINE__);
  77         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
  78         checkrc(ret,__LINE__);
  79         return 2;
  80     }
  81
  82     // Allocation of  statement handle for DDL nad DML Operation
  83     ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
  84     checkrc(ret,__LINE__);
  85
  86     // create table 'T1' with two fields, F1 INTEGER AND F2 CHAR.
  87     SQLCHAR table[200]= "CREATE TABLE T1(F1 INT,F2 CHAR(20))";
  88
  89     ret = SQLPrepare(stmt,table,SQL_NTS);
  90     checkrc(ret,__LINE__);
  91
  92     ret = SQLExecute(stmt);
  93     checkrc(ret,__LINE__);
  94
  95     printf("Table T1 created\n");
  96
  97     //Insert 10 Tuples into the table 'T1'
  98     int id=10;
  99     char name[20]="THIRU";
 100     char names[10][20]={"Gopal", "Aruna", "Kanchana", "Vijay", "Ganga",
 101                        "XieLiang", "Rajesh", "Steve", "Veda", "Jitendra" };
 102
 103
 104     SQLINTEGER slen = SQL_NTS;
 105
 106     ret  = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?);",SQL_NTS);
 107     checkrc(ret,__LINE__);
 108
 109     ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id,0,NULL);
 110     checkrc(ret,__LINE__);
 111
 112     ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)name,0,&slen);
 113     checkrc(ret,__LINE__);
 114
 115     int i,count=0;
 116     for(i=0;i<10;i++)
 117     {
 118         id++;
 119         strcpy(name,names[i]);
 120
 121         ret = SQLExecute(stmt);
 122         checkrc(ret,__LINE__);
 123
 124         ret = SQLTransact(env,dbc,SQL_COMMIT);
 125         checkrc(ret,__LINE__);
 126         count++;
 127     }
 128     printf("%d Rows inserted\n",count);
 129
 130
 131     //Fetch rows from the table 'T1'
 132     int id1=10;
 133
 134     ret = SQLPrepare(stmt,(unsigned char*)"SELECT * FROM T1;",SQL_NTS);
 135     checkrc(ret,__LINE__);
 136
 137     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&id1,0,NULL);
 138     checkrc(ret,__LINE__);
 139
 140     ret = SQLBindCol(stmt,2,SQL_C_CHAR,name,sizeof(name),NULL);
 141     checkrc(ret,__LINE__);
 142
 143     count=0;
 144     ret = SQLExecute(stmt);
 145     checkrc(ret,__LINE__);
 146
 147     printf("Fetching starts on table T1 :\n");
 148     while(SQL_SUCCEEDED(ret=SQLFetch(stmt)))
 149     {
 150         printf("F1:%d\tF2:%s\n",id1,name);
 151         count++;
 152     }
 153     ret = SQLCloseCursor(stmt);
 154     checkrc(ret,__LINE__);
 155
 156     ret = SQLTransact(env,dbc,SQL_COMMIT);
 157     checkrc(ret,__LINE__);
 158
 159     printf("%d rows fetched\n",count);
 160
 161     //Delete all the rows from the table 'T1'
 162     ret = SQLPrepare(stmt,(unsigned char*)"DELETE FROM T1 WHERE F1=?;",SQL_NTS);
 163     checkrc(ret,__LINE__);
 164
 165     ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id1,0,NULL);
 166     checkrc(ret,__LINE__);
 167
 168
 169     count=0;
 170     for(i=0;i<10;i++)
 171     {
 172        id++;
 173        ret = SQLExecute(stmt);
 174        checkrc(ret,__LINE__);
 175        count++;
 176     }
 177
 178     ret = SQLTransact(env,dbc,SQL_COMMIT);
 179     checkrc(ret,__LINE__);
 180     printf("%d Rows deleted\n",count);
 181
 182
 183     // drop the table 'T1'
 184     SQLCHAR drop[50]="DROP TABLE T1";
 185     ret = SQLPrepare(stmt,drop,SQL_NTS);
 186     checkrc(ret,__LINE__);
 187
 188     ret = SQLExecute(stmt);
 189     checkrc(ret,__LINE__);
 190     printf("Table T1 dropped\n");
 191
 192     //Free the statement handle
 193     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 194     checkrc(ret,__LINE__);
 195
 196     //Disconnect from the Data source
 197     ret = SQLDisconnect(dbc);
 198     checkrc(ret,__LINE__);
 199     printf("Disconnected from CSQL\n");
 200
 201     //Free the connection handle
 202     ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 203     checkrc(ret,__LINE__);
 204
 205     //Free the environment handle
 206     ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 207     checkrc(ret,__LINE__);
 208
 209     return 0;
 210 }
 211