test/odbc/Metadata/metadata4nw.c

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