test/odbc/Metadata/metadata7.c

   1 /*  CREATE TABLE "T1" WITH 10 FIELDS.
   2     CREATE TABLE "T2" WITH 10 FIELDS.
   3     CREATE TABLE "T3" WITH 10 FIELDS.
   4     SQLTables()  Should return all table information
   5     CatalogName="csql", SchemaName="csql", TableType="table"
   6 */
   7
   8 #include<stdio.h>
   9 #include<stdlib.h>
  10 #include<sql.h>
  11 #include<sqlext.h>
  12 #include<string.h>
  13 #include<CSql.h>
  14 //*************************************************************************
  15 // ERROR CHECK FUNCTION
  16 inline void checkrc(int rc,int line)
  17 {
  18    if(rc)
  19    {
  20        printf("ERROR %d at line %d\n",rc,line);
  21        exit(1);
  22    }
  23 }
  24
  25 //*************************************************************************
  26 // FUNCTION FOR INSERTING ROWS IN IT.
  27
  28 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
  29 {
  30       int ret;
  31       char f1[50]= "praba";
  32       char f2[50]= "praba";
  33       char f3[50]= "praba";
  34       char f4[50]= "praba";
  35       char f5[50]= "praba";
  36
  37      ret = SQLTables(stmt,(SQLCHAR*)"Csql", SQL_NTS, (SQLCHAR*)"Csql", SQL_NTS, NULL, SQL_NTS, (SQLCHAR*)"table", SQL_NTS );
  38 //     ret = SQLTables(stmt, (SQLCHAR*)"Csql", NULL,(SQLCHAR*)"cSQL", NULL, NULL, NULL, (SQLCHAR*)"view", NULL );
  39      checkrc(ret,__LINE__);
  40     short totalFields=0;
  41     ret = SQLNumResultCols (stmt, &totalFields);
  42     checkrc(ret,__LINE__);
  43     printf( "No of columns in resultset = %d\n",totalFields);
  44
  45     ret = SQLBindCol(stmt,1,SQL_C_CHAR,f1,sizeof(f1),NULL);
  46     checkrc(ret,__LINE__);
  47     ret = SQLBindCol(stmt,2,SQL_C_CHAR,f2,sizeof(f2),NULL);
  48     checkrc(ret,__LINE__);
  49     ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL);
  50     checkrc(ret,__LINE__);
  51     ret = SQLBindCol(stmt,4,SQL_C_CHAR,f4,sizeof(f4),NULL);
  52     checkrc(ret,__LINE__);
  53     ret = SQLBindCol(stmt,5,SQL_C_CHAR,f5,sizeof(f5),NULL);
  54     checkrc(ret,__LINE__);
  55     while(SQL_SUCCEEDED(ret = SQLFetch(stmt))){
  56        printf("\tF1=%s \t F2=%s \t F3=%s \t F4= %s \t F5=%s \n ",f1,f2,f3,f4,f5);
  57   }
  58       ret = SQLCloseCursor(stmt);
  59       checkrc(ret,__LINE__);
  60
  61     ret = SQLTransact(env,dbc,SQL_COMMIT);
  62        checkrc(ret,__LINE__);
  63     return 0;
  64
  65 }
  66
  67
  68
  69 int main()
  70 {
  71   SQLHENV env;
  72   SQLHDBC dbc;
  73   SQLHSTMT stmt;
  74   SQLRETURN ret;
  75   SQLCHAR outstr[1024];
  76   SQLSMALLINT outstrlen;
  77
  78   // Aloocate an environment handle
  79   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  80   checkrc(ret,__LINE__);
  81
  82    //we need odbc3 support
  83    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  84
  85   //ALLOCATE A Connection handle
  86   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  87   checkrc(ret,__LINE__);
  88
  89   // connect to the DSN mydsn
  90    ret = SQLConnect (dbc,
  91                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  92                    (SQLCHAR *) "root",
  93                    (SQLSMALLINT) strlen ("root"),
  94                    (SQLCHAR *) "manager",
  95                    (SQLSMALLINT) strlen (""));
  96
  97 // ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)"DSN=myodbc3", SQL_NTS, outstr, sizeof(outstr), &outstrlen,SQL_DRIVER_NOPROMPT);
  98
  99   if(SQL_SUCCEEDED(ret))
 100   {
 101      printf("\nConnected to the Data Source..\n");
 102
 103
 104
 105   }
 106    else
 107    {
 108         printf("error in conenction\n");
 109
 110          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 111          checkrc(ret,__LINE__);
 112
 113          ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 114          checkrc(ret,__LINE__);
 115          return 1;
 116    }
 117
 118
 119    //*****************************************************
 120    // TABLE CREATED
 121    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 122    checkrc(ret,__LINE__);
 123
 124    SQLCHAR table[200]=
 125      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT);";
 126
 127    ret = SQLPrepare(stmt,table,SQL_NTS);
 128    checkrc(ret,__LINE__);
 129    ret = SQLExecute(stmt);
 130    checkrc(ret,__LINE__);
 131    ret = SQLPrepare(stmt,(SQLCHAR*)"create table t2 (f1 int,primary key(f1));",SQL_NTS);
 132    checkrc(ret,__LINE__);
 133    ret = SQLExecute(stmt);
 134    checkrc(ret,__LINE__);
 135    ret = SQLPrepare(stmt,(SQLCHAR*)"create table t3 (f1 int);",SQL_NTS);
 136    checkrc(ret,__LINE__);
 137    ret = SQLExecute(stmt);
 138    checkrc(ret,__LINE__);
 139
 140    printf("\nTABLE CREATED\n");
 141    //***************************
 142    FetchTest(env,dbc,stmt);
 143    //**************************************************
 144     SQLCHAR drop[100]="DROP TABLE T1";
 145     ret = SQLPrepare(stmt,drop,SQL_NTS);
 146     checkrc(ret,__LINE__);
 147     ret = SQLExecute(stmt);
 148     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 149     {
 150       printf("Statement failed\n");
 151        return 1;
 152     }
 153
 154     ret = SQLPrepare(stmt,(SQLCHAR*)"drop table t2",SQL_NTS);
 155     checkrc(ret,__LINE__);
 156     ret = SQLExecute(stmt);
 157     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 158     {
 159       printf("Statement failed\n");
 160        return 2;
 161     }
 162
 163
 164     ret = SQLPrepare(stmt,(SQLCHAR*)"drop table t3",SQL_NTS);
 165     checkrc(ret,__LINE__);
 166     ret = SQLExecute(stmt);
 167     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 168     {
 169       printf("Statement failed\n");
 170        return 2;
 171     }
 172        printf("Tables  dropped successfully\n");
 173
 174     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 175     checkrc(ret,__LINE__);
 176
 177    ret = SQLDisconnect(dbc);
 178    checkrc(ret,__LINE__);
 179
 180    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 181    checkrc(ret,__LINE__);
 182
 183    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 184    checkrc(ret,__LINE__);
 185    return 0;
 186 }