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   }
  71 //***********************************************************************
  72
  73
  74
  75
  76 int main()
  77 {
  78   SQLHENV env;
  79   SQLHDBC dbc;
  80   SQLHSTMT stmt;
  81   SQLRETURN ret;
  82   SQLCHAR outstr[1024];
  83   SQLSMALLINT outstrlen;
  84
  85   // Aloocate an environment handle
  86   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  87   checkrc(ret,__LINE__);
  88
  89    //we need odbc3 support
  90    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  91
  92   //ALLOCATE A Connection handle
  93   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  94   checkrc(ret,__LINE__);
  95
  96   // connect to the DSN mydsn
  97    ret = SQLConnect (dbc,
  98                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  99                    (SQLCHAR *) "root",
 100                    (SQLSMALLINT) strlen ("root"),
 101                    (SQLCHAR *) "manager",
 102                    (SQLSMALLINT) strlen (""));
 103
 104
 105   if(SQL_SUCCEEDED(ret))
 106   {
 107      printf("\nConnected to the Data Source..\n");
 108
 109    }
 110
 111
 112    else
 113    {
 114         printf("error in connection\n");
 115          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 116         checkrc(ret,__LINE__);
 117
 118         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 119         checkrc(ret,__LINE__);
 120
 121         return 1;
 122    }
 123
 124
 125    //******************************************************************
 126    // TABLE CREATED
 127    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 128    checkrc(ret,__LINE__);
 129
 130    SQLCHAR table[200]=
 131      "CREATE TABLE T1(F1 INT,F2 char(30))";
 132    ret = SQLPrepare(stmt,table,SQL_NTS);
 133    checkrc(ret,__LINE__);
 134
 135    ret = SQLExecute(stmt);
 136    checkrc(ret,__LINE__);
 137    printf("\nTABLE CREATED\n");
 138    //****************************************************************
 139
 140    //  INSERT FUNCTION
 141    InsertTest(env,dbc,stmt);
 142    //*****************************************************************
 143    // DELETE FROM T1;
 144    printf("delete from T1 where F1=10\n");
 145    SQLCHAR delete1[50]="DELETE FROM T1 WHERE F1=10;";
 146
 147   ret = SQLPrepare(stmt,delete1,SQL_NTS);
 148    checkrc(ret,__LINE__);
 149
 150    ret = SQLExecute(stmt);
 151    checkrc(ret,__LINE__);
 152
 153
 154
 155    SQLINTEGER nor;
 156    ret = SQLRowCount(stmt,&nor);
 157    printf("sqlrowcount() returns=%d\n",nor); //  nor should return 1.
 158    if(nor!=1)return 1;
 159    ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 160    checkrc(ret,__LINE__);
 161    //************************************************
 162
 163    printf("\ndelete from T1 where F1 > 900\n");
 164    ret = SQLPrepare(stmt,(unsigned char*)"DELETE FROM T1 WHERE F1 > 900",SQL_NTS);
 165    checkrc(ret,__LINE__);
 166
 167    ret = SQLExecute(stmt);
 168    checkrc(ret,__LINE__);
 169
 170    SQLINTEGER nor1;
 171    ret = SQLRowCount(stmt,&nor1);
 172    printf("sqlrowcount() returns=%d\n",nor1);
 173    if(nor1!=100)return 1;
 174
 175    ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 176    checkrc(ret,__LINE__);
 177    //*************************************************************
 178    printf("\ninsert into T1 values(1001,'1000')\n");
 179    ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(1001,'1000')",SQL_NTS);
 180    checkrc(ret,__LINE__);
 181
 182    ret = SQLExecute(stmt);
 183    checkrc(ret,__LINE__);
 184
 185    SQLINTEGER nor2;
 186    ret = SQLRowCount(stmt,&nor2);
 187    checkrc(ret,__LINE__);
 188
 189    printf("sqlrowcount() returns=%d\n",nor2);
 190    if(nor2!=1)return 1;
 191    ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 192
 193    //*****************************************************
 194    printf("\nUPDATE T1 SET F2='THOUSAND' WHERE F1<10\n");
 195    ret = SQLPrepare(stmt,(unsigned char*)"UPDATE T1 SET F2='THOUSAND' WHERE F1<10",SQL_NTS);
 196    checkrc(ret,__LINE__);
 197
 198    ret = SQLExecute(stmt);
 199    checkrc(ret,__LINE__);
 200
 201    SQLINTEGER nor3;
 202    ret = SQLRowCount(stmt,&nor3);
 203
 204    checkrc(ret,__LINE__);
 205    printf("sqlrowcount()  returns=%d\n",nor3);
 206    if(nor3!=9)return 1;
 207    /*ret = SQLFreeStmt(stmt,SQL_RESET_PARAMS);
 208    checkrc(ret,__LINE__);*/
 209    //***********************************************
 210
 211    //FetchTest(env,dbc,stmt);
 212
 213    //****************************************************************
 214    //drop the table
 215    ret = SQLPrepare(stmt,(unsigned char*)"drop table T1;",SQL_NTS);
 216    checkrc(ret,__LINE__);
 217
 218    ret = SQLExecute(stmt);
 219    checkrc(ret,__LINE__);
 220    printf("Table 'T1' dropped\n");
 221
 222    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 223    checkrc(ret,__LINE__);
 224
 225    ret = SQLDisconnect(dbc);
 226    checkrc(ret,__LINE__);
 227
 228    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 229    checkrc(ret,__LINE__);
 230
 231    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 232    checkrc(ret,__LINE__);
 233    return 0;
 234 }