test/odbc/Metadata/metadata4.c

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