examples/odbc/odbcexample.c

   1 /**************************************************************************
   2  *   Copyright (C) 2008 by www.databasecache.com                          *
   3  *   Contact : praba_tuty@databasecache.com                               *
   4  *                                                                        *
   5  *   THis program is free software; you can redistribute it and/or modify *
   6  *   it under the terms of the GNU General Public License as Published by *
   7  *   the Fre Software Foundation;either version 2 of the License, or      *
   8  *   (at your option)any later version.                                   *
   9  *                                                                        *
  10  *   This program is distributed in the hope that will be useful,         *
  11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of       *
  12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, See the         *
  13  *   GNU General License for more details.                                *
  14  *                                                                        *
  15  **************************************************************************/
  16
  17 #include<stdio.h>
  18 #include<stdlib.h>
  19 #include<sql.h>
  20 #include<sqlext.h>
  21 #include<string.h>
  22
  23 void checkrc(int rc,int line)
  24 {
  25     if(rc)
  26     {
  27         printf("Error %d at line %d\n", rc, line);
  28         exit(1);
  29     }
  30 }
  31
  32 int main()
  33 {
  34     SQLHENV env;
  35     SQLHDBC dbc;
  36     SQLHSTMT stmt;
  37     SQLRETURN ret;
  38     SQLCHAR outstr[1024];
  39     SQLSMALLINT outstrlen;
  40
  41     //Allocate an environment handle
  42     ret = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  43     checkrc(ret,__LINE__);
  44
  45     //we need ODBC3 support
  46     SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  47
  48     //Allocate a Connection handle
  49     ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  50     checkrc(ret,__LINE__);
  51
  52     //connect to the DSN Data source
  53     ret = SQLConnect (dbc,
  54                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  55                    (SQLCHAR *) "root",
  56                    (SQLSMALLINT) strlen ("root"),
  57                    (SQLCHAR *) "manager",
  58                    (SQLSMALLINT) strlen (""));
  59     if(SQL_SUCCEEDED(ret))
  60     {
  61         printf("Connected to CSQL\n");
  62     }
  63     else
  64     {
  65         printf("error in connection\n");
  66         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
  67         checkrc(ret,__LINE__);
  68         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
  69         checkrc(ret,__LINE__);
  70         return 2;
  71     }
  72
  73     // Allocation of  statement handle for DDL nad DML Operation
  74     ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
  75     checkrc(ret,__LINE__);
  76
  77     // create table 'T1' with two fields, F1 INTEGER AND F2 CHAR.
  78     SQLCHAR table[200]= "CREATE TABLE T1(F1 INT,F2 CHAR(20))";
  79
  80     ret = SQLPrepare(stmt,table,SQL_NTS);
  81     checkrc(ret,__LINE__);
  82
  83     ret = SQLExecute(stmt);
  84     checkrc(ret,__LINE__);
  85
  86     printf("Table T1 created\n");
  87
  88     //Insert 10 Tuples into the table 'T1'
  89     int id=10;
  90     char name[20]="THIRU";
  91     char names[10][20]={"Gopal", "Aruna", "Kanchana", "Vijay", "Ganga",
  92                        "XieLiang", "Rajesh", "Steve", "Veda", "Jitendra" };
  93
  94
  95     SQLINTEGER slen = SQL_NTS;
  96
  97     ret  = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?);",SQL_NTS);
  98     checkrc(ret,__LINE__);
  99
 100     ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id,0,NULL);
 101     checkrc(ret,__LINE__);
 102
 103     ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)name,0,&slen);
 104     checkrc(ret,__LINE__);
 105
 106     int i,count=0;
 107     for(i=0;i<10;i++)
 108     {
 109         id++;
 110         strcpy(name,names[i]);
 111
 112         ret = SQLExecute(stmt);
 113         checkrc(ret,__LINE__);
 114
 115         ret = SQLTransact(env,dbc,SQL_COMMIT);
 116         checkrc(ret,__LINE__);
 117         count++;
 118     }
 119     printf("%d Rows inserted\n",count);
 120
 121
 122     //Fetch rows from the table 'T1'
 123     int id1=10;
 124
 125     ret = SQLPrepare(stmt,(unsigned char*)"SELECT * FROM T1;",SQL_NTS);
 126     checkrc(ret,__LINE__);
 127
 128     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&id1,0,NULL);
 129     checkrc(ret,__LINE__);
 130
 131     ret = SQLBindCol(stmt,2,SQL_C_CHAR,name,sizeof(name),NULL);
 132     checkrc(ret,__LINE__);
 133
 134     count=0;
 135     ret = SQLExecute(stmt);
 136     checkrc(ret,__LINE__);
 137
 138     printf("Fetching starts on table T1 :\n");
 139     while(SQL_SUCCEEDED(ret=SQLFetch(stmt)))
 140     {
 141         printf("F1:%d\tF2:%s\n",id1,name);
 142         count++;
 143     }
 144     ret = SQLCloseCursor(stmt);
 145     checkrc(ret,__LINE__);
 146
 147     ret = SQLTransact(env,dbc,SQL_COMMIT);
 148     checkrc(ret,__LINE__);
 149
 150     printf("%d rows fetched\n",count);
 151
 152     //Delete all the rows from the table 'T1'
 153     ret = SQLPrepare(stmt,(unsigned char*)"DELETE FROM T1 WHERE F1=?;",SQL_NTS);
 154     checkrc(ret,__LINE__);
 155
 156     ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id1,0,NULL);
 157     checkrc(ret,__LINE__);
 158
 159
 160     count=0;
 161     for(i=0;i<10;i++)
 162     {
 163        id++;
 164        ret = SQLExecute(stmt);
 165        checkrc(ret,__LINE__);
 166        count++;
 167     }
 168
 169     ret = SQLTransact(env,dbc,SQL_COMMIT);
 170     checkrc(ret,__LINE__);
 171     printf("%d Rows deleted\n",count);
 172
 173
 174     // drop the table 'T1'
 175     SQLCHAR drop[50]="DROP TABLE T1";
 176     ret = SQLPrepare(stmt,drop,SQL_NTS);
 177     checkrc(ret,__LINE__);
 178
 179     ret = SQLExecute(stmt);
 180     checkrc(ret,__LINE__);
 181     printf("Table T1 dropped\n");
 182
 183     //Free the statement handle
 184     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 185     checkrc(ret,__LINE__);
 186
 187     //Disconnect from the Data source
 188     ret = SQLDisconnect(dbc);
 189     checkrc(ret,__LINE__);
 190     printf("Disconnected from CSQL\n");
 191
 192     //Free the connection handle
 193     ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 194     checkrc(ret,__LINE__);
 195
 196     //Free the environment handle
 197     ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 198     checkrc(ret,__LINE__);
 199     return 0;
 200 }