test/odbc/NonSelectDML/nonselectdml1nw.c

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