test/odbc/NonSelectDML/nonselectdml1.c

   1 /* create table Ti with one integer field and one char field. insert some records into it.
   2    delete from t1 where f1=10.
   3    sqlrowcount() should return 1.
   4
   5    delete from t1 where f1 > 900
   6    sqlrowcount shpuld return 100.
   7
   8    insert one record with values(1001,'1000')
   9    sqlrowcount should return 1.
  10
  11    update T1 set f2='thousand' where f1<10.
  12    sqlrowcount() should return 9
  13
  14
  15    AUTHOR : Jitendra Lenka
  16
  17 */
  18
  19
  20 #include<stdio.h>
  21 #include<stdlib.h>
  22 #include<sql.h>
  23 #include<sqlext.h>
  24 #include<string.h>
  25 inline void checkrc(int rc,int line)
  26 {
  27    if(rc)
  28    {
  29        printf("ERROR %d at line %d\n",rc,line);
  30        exit(1);
  31    }
  32 }
  33
  34 //*************************************************************************
  35 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  36 {
  37
  38       SQLRETURN  ret;
  39       int f1=0; // f1 field
  40       char f2[15]="jitendra";//f2 field
  41       SQLINTEGER slen=SQL_NTS;
  42
  43       ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?)",SQL_NTS);
  44       checkrc(ret,__LINE__);
  45
  46       // BIND PARAMETER
  47
  48       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&f1,0,NULL);
  49       checkrc(ret,__LINE__);
  50
  51       ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)f2,0,&slen);
  52       checkrc(ret,__LINE__);
  53
  54
  55
  56       int i,count=0;
  57       for(i=0;i<1000;i++)
  58       {
  59          f1++;
  60
  61
  62          ret = SQLExecute(stmt);
  63          checkrc(ret,__LINE__);
  64
  65          ret = SQLTransact(env,dbc,SQL_COMMIT);
  66          checkrc(ret,__LINE__);
  67          count++;
  68       }
  69       printf("Total row inserted=%d\n",count);
  70       return 0;
  71   }
  72 //***********************************************************************
  73
  74
  75
  76
  77 int main()
  78 {
  79   SQLHENV env;
  80   SQLHDBC dbc;
  81   SQLHSTMT stmt;
  82   SQLRETURN ret;
  83   SQLCHAR outstr[1024];
  84   SQLSMALLINT outstrlen;
  85
  86   // Aloocate an environment handle
  87   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  88   checkrc(ret,__LINE__);
  89
  90    //we need odbc3 support
  91    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  92
  93   //ALLOCATE A Connection handle
  94   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  95   checkrc(ret,__LINE__);
  96
  97   // connect to the DSN mydsn
  98    ret = SQLConnect (dbc,
  99                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 100                    (SQLCHAR *) "root",
 101                    (SQLSMALLINT) strlen ("root"),
 102                    (SQLCHAR *) "manager",
 103                    (SQLSMALLINT) strlen (""));
 104
 105
 106   if(SQL_SUCCEEDED(ret))
 107   {
 108      printf("\nConnected to the Data Source..\n");
 109
 110    }
 111
 112
 113    else
 114    {
 115         printf("error in connection\n");
 116          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 117         checkrc(ret,__LINE__);
 118
 119         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 120         checkrc(ret,__LINE__);
 121
 122         return 1;
 123    }
 124
 125
 126    //******************************************************************
 127    // TABLE CREATED
 128    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 129    checkrc(ret,__LINE__);
 130
 131    SQLCHAR table[200]=
 132      "CREATE TABLE T1(F1 INT,F2 char(30))";
 133    ret = SQLPrepare(stmt,table,SQL_NTS);
 134    checkrc(ret,__LINE__);
 135
 136    ret = SQLExecute(stmt);
 137    checkrc(ret,__LINE__);
 138    printf("\nTABLE CREATED\n");
 139    //****************************************************************
 140
 141    //  INSERT FUNCTION
 142    InsertTest(env,dbc,stmt);
 143    //*****************************************************************
 144    // DELETE FROM T1;
 145    printf("delete from T1 where F1=10\n");
 146    SQLCHAR delete1[50]="DELETE FROM T1 WHERE F1=10;";
 147
 148   ret = SQLPrepare(stmt,delete1,SQL_NTS);
 149    checkrc(ret,__LINE__);
 150
 151    ret = SQLExecute(stmt);
 152    checkrc(ret,__LINE__);
 153
 154
 155
 156    SQLINTEGER nor;
 157    ret = SQLRowCount(stmt,&nor);
 158    printf("sqlrowcount() returns=%d\n",nor); //  nor should return 1.
 159    if(nor!=1)return 1;
 160    ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 161    checkrc(ret,__LINE__);
 162    //************************************************
 163
 164    printf("\ndelete from T1 where F1 > 900\n");
 165    ret = SQLPrepare(stmt,(unsigned char*)"DELETE FROM T1 WHERE F1 > 900",SQL_NTS);
 166    checkrc(ret,__LINE__);
 167
 168    ret = SQLExecute(stmt);
 169    checkrc(ret,__LINE__);
 170
 171    SQLINTEGER nor1;
 172    ret = SQLRowCount(stmt,&nor1);
 173    printf("sqlrowcount() returns=%d\n",nor1);
 174    if(nor1!=100)return 1;
 175
 176    ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 177    checkrc(ret,__LINE__);
 178    //*************************************************************
 179    printf("\ninsert into T1 values(1001,'1000')\n");
 180    ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(1001,'1000')",SQL_NTS);
 181    checkrc(ret,__LINE__);
 182
 183    ret = SQLExecute(stmt);
 184    checkrc(ret,__LINE__);
 185
 186    SQLINTEGER nor2;
 187    ret = SQLRowCount(stmt,&nor2);
 188    checkrc(ret,__LINE__);
 189
 190    printf("sqlrowcount() returns=%d\n",nor2);
 191    if(nor2!=1)return 1;
 192    ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 193
 194    //*****************************************************
 195    printf("\nUPDATE T1 SET F2='THOUSAND' WHERE F1<10\n");
 196    ret = SQLPrepare(stmt,(unsigned char*)"UPDATE T1 SET F2='THOUSAND' WHERE F1<10",SQL_NTS);
 197    checkrc(ret,__LINE__);
 198
 199    ret = SQLExecute(stmt);
 200    checkrc(ret,__LINE__);
 201
 202    SQLINTEGER nor3;
 203    ret = SQLRowCount(stmt,&nor3);
 204
 205    checkrc(ret,__LINE__);
 206    printf("sqlrowcount()  returns=%d\n",nor3);
 207    if(nor3!=9)return 1;
 208    /*ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 209    checkrc(ret,__LINE__);*/
 210    //***********************************************
 211
 212    //FetchTest(env,dbc,stmt);
 213
 214    //****************************************************************
 215    //drop the table
 216    ret = SQLPrepare(stmt,(unsigned char*)"drop table T1;",SQL_NTS);
 217    checkrc(ret,__LINE__);
 218
 219    ret = SQLExecute(stmt);
 220    checkrc(ret,__LINE__);
 221    printf("Table 'T1' dropped\n");
 222
 223    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 224    checkrc(ret,__LINE__);
 225
 226    ret = SQLDisconnect(dbc);
 227    checkrc(ret,__LINE__);
 228
 229    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 230    checkrc(ret,__LINE__);
 231
 232    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 233    checkrc(ret,__LINE__);
 234    return 0;
 235 }