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
  53     //connect to Data source
  54     ret = SQLConnect (dbc,
  55                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  56                    (SQLCHAR *) "root",
  57                    (SQLSMALLINT) strlen ("root"),
  58                    (SQLCHAR *) "manager",
  59                    (SQLSMALLINT) strlen (""));
  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     if(SQL_SUCCEEDED(ret))
  67     {
  68         printf("Connected to CSQL\n");
  69     }
  70     else
  71     {
  72         printf("error in connection\n");
  73         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
  74         checkrc(ret,__LINE__);
  75         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
  76         checkrc(ret,__LINE__);
  77         return 2;
  78     }
  79
  80     // Allocation of  statement handle for DDL nad DML Operation
  81     ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
  82     checkrc(ret,__LINE__);
  83
  84     // create table 'T1' with two fields, F1 INTEGER AND F2 CHAR.
  85     SQLCHAR table[200]= "CREATE TABLE T1(F1 INT,F2 CHAR(20))";
  86
  87     ret = SQLPrepare(stmt,table,SQL_NTS);
  88     checkrc(ret,__LINE__);
  89
  90     ret = SQLExecute(stmt);
  91     checkrc(ret,__LINE__);
  92
  93     printf("Table T1 created\n");
  94
  95     //Insert 10 Tuples into the table 'T1'
  96     int id=10;
  97     char name[20]="THIRU";
  98     char names[10][20]={"Gopal", "Aruna", "Kanchana", "Vijay", "Ganga",
  99                        "XieLiang", "Rajesh", "Steve", "Veda", "Jitendra" };
 100
 101
 102     SQLINTEGER slen = SQL_NTS;
 103
 104     ret  = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?);",SQL_NTS);
 105     checkrc(ret,__LINE__);
 106
 107     ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id,0,NULL);
 108     checkrc(ret,__LINE__);
 109
 110     ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)name,0,&slen);
 111     checkrc(ret,__LINE__);
 112
 113     int i,count=0;
 114     for(i=0;i<10;i++)
 115     {
 116         id++;
 117         strcpy(name,names[i]);
 118
 119         ret = SQLExecute(stmt);
 120         checkrc(ret,__LINE__);
 121
 122         ret = SQLTransact(env,dbc,SQL_COMMIT);
 123         checkrc(ret,__LINE__);
 124         count++;
 125     }
 126     printf("%d Rows inserted\n",count);
 127
 128
 129     //Fetch rows from the table 'T1'
 130     int id1=10;
 131
 132     ret = SQLPrepare(stmt,(unsigned char*)"SELECT * FROM T1;",SQL_NTS);
 133     checkrc(ret,__LINE__);
 134
 135     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&id1,0,NULL);
 136     checkrc(ret,__LINE__);
 137
 138     ret = SQLBindCol(stmt,2,SQL_C_CHAR,name,sizeof(name),NULL);
 139     checkrc(ret,__LINE__);
 140
 141     count=0;
 142     ret = SQLExecute(stmt);
 143     checkrc(ret,__LINE__);
 144
 145     printf("Fetching starts on table T1 :\n");
 146     while(SQL_SUCCEEDED(ret=SQLFetch(stmt)))
 147     {
 148         printf("F1:%d\tF2:%s\n",id1,name);
 149         count++;
 150     }
 151     ret = SQLCloseCursor(stmt);
 152     checkrc(ret,__LINE__);
 153
 154     ret = SQLTransact(env,dbc,SQL_COMMIT);
 155     checkrc(ret,__LINE__);
 156
 157     printf("%d rows fetched\n",count);
 158
 159     //Delete all the rows from the table 'T1'
 160     ret = SQLPrepare(stmt,(unsigned char*)"DELETE FROM T1 WHERE F1=?;",SQL_NTS);
 161     checkrc(ret,__LINE__);
 162
 163     ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&id1,0,NULL);
 164     checkrc(ret,__LINE__);
 165
 166
 167     count=0;
 168     for(i=0;i<10;i++)
 169     {
 170        id++;
 171        ret = SQLExecute(stmt);
 172        checkrc(ret,__LINE__);
 173        count++;
 174     }
 175
 176     ret = SQLTransact(env,dbc,SQL_COMMIT);
 177     checkrc(ret,__LINE__);
 178     printf("%d Rows deleted\n",count);
 179
 180
 181     // drop the table 'T1'
 182     SQLCHAR drop[50]="DROP TABLE T1";
 183     ret = SQLPrepare(stmt,drop,SQL_NTS);
 184     checkrc(ret,__LINE__);
 185
 186     ret = SQLExecute(stmt);
 187     checkrc(ret,__LINE__);
 188     printf("Table T1 dropped\n");
 189
 190     //Free the statement handle
 191     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 192     checkrc(ret,__LINE__);
 193
 194     //Disconnect from the Data source
 195     ret = SQLDisconnect(dbc);
 196     checkrc(ret,__LINE__);
 197     printf("Disconnected from CSQL\n");
 198
 199     //Free the connection handle
 200     ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 201     checkrc(ret,__LINE__);
 202
 203     //Free the environment handle
 204     ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 205     checkrc(ret,__LINE__);
 206     return 0;
 207 }