test/odbc/Metadata/metadata5.c

   1 /*  CREATE TABLE "T1" WITH 10 FIELDS.
   2     SQLPrimaryKeys()  Should return primary key information
   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 //*************************************************************************
  13 // ERROR CHECK FUNCTION
  14 inline void checkrc(int rc,int line)
  15 {
  16    if(rc)
  17    {
  18        printf("ERROR %d at line %d\n",rc,line);
  19        exit(1);
  20    }
  21 }
  22
  23 //*************************************************************************
  24 // FUNCTION FOR INSERTING ROWS IN IT.
  25
  26 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt,char *tablename)
  27 {
  28       int ret;
  29       char f1[50]= "praba";
  30       char f2[50]= "praba";
  31       char f3[50]= "praba";
  32       char f4[50]= "praba";
  33       short f5;
  34       char f6[50]= "praba";
  35
  36      ret = SQLPrimaryKeys(stmt, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)tablename,SQL_NTS);
  37      checkrc(ret,__LINE__);
  38     short totalFields=0;
  39     ret = SQLNumResultCols (stmt, &totalFields);
  40     checkrc(ret,__LINE__);
  41     printf( "No of columns in resultset = %d\n",totalFields);
  42
  43     ret = SQLBindCol(stmt,1,SQL_C_CHAR,f1,sizeof(f1),NULL);
  44     checkrc(ret,__LINE__);
  45     ret = SQLBindCol(stmt,2,SQL_C_CHAR,f2,sizeof(f2),NULL);
  46     checkrc(ret,__LINE__);
  47     ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL);
  48     checkrc(ret,__LINE__);
  49     ret = SQLBindCol(stmt,4,SQL_C_CHAR,f4,sizeof(f4),NULL);
  50     checkrc(ret,__LINE__);
  51     SQLBindCol(stmt, 5, SQL_C_SHORT, &f5, 0, NULL);
  52     checkrc(ret,__LINE__);
  53     ret = SQLBindCol(stmt,6,SQL_C_CHAR,f6,sizeof(f6),NULL);
  54     checkrc(ret,__LINE__);
  55     while(SQL_SUCCEEDED(ret = SQLFetch(stmt))){
  56        printf("catalogName%s \t SchemaName=%s \t TableName=%s \t ColumnName = %s \t KEY_SEQ=%hd\t Primarykey = %s \n ",f1,f2,f3,f4,f5,f6);
  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 int main()
  69 {
  70   SQLHENV env;
  71   SQLHDBC dbc;
  72   SQLHSTMT stmt;
  73   SQLRETURN ret;
  74   SQLCHAR outstr[1024];
  75   SQLSMALLINT outstrlen;
  76
  77   // Aloocate an environment handle
  78   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  79   checkrc(ret,__LINE__);
  80
  81    //we need odbc3 support
  82    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  83
  84   //ALLOCATE A Connection handle
  85   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  86   checkrc(ret,__LINE__);
  87
  88   // connect to the DSN mydsn
  89    ret = SQLConnect (dbc,
  90                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  91                    (SQLCHAR *) "root",
  92                    (SQLSMALLINT) strlen ("root"),
  93                    (SQLCHAR *) "manager",
  94                    (SQLSMALLINT) strlen (""));
  95
  96
  97   if(SQL_SUCCEEDED(ret))
  98   {
  99      printf("\nConnected to the Data Source..\n");
 100
 101
 102
 103   }
 104    else
 105    {
 106         printf("error in conenction\n");
 107
 108          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 109          checkrc(ret,__LINE__);
 110
 111          ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 112          checkrc(ret,__LINE__);
 113          return 1;
 114    }
 115
 116
 117    //*****************************************************
 118    // TABLE CREATED
 119    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 120    checkrc(ret,__LINE__);
 121
 122    SQLCHAR table[200]=
 123      "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));";
 124
 125    ret = SQLPrepare(stmt,table,SQL_NTS);
 126    checkrc(ret,__LINE__);
 127    ret = SQLExecute(stmt);
 128    checkrc(ret,__LINE__);
 129
 130    printf("\nTABLE CREATED\n");
 131    //***************************
 132    FetchTest(env,dbc,stmt,"T1");
 133    //**************************************************
 134     SQLCHAR drop[100]="DROP TABLE T1";
 135     ret = SQLPrepare(stmt,drop,SQL_NTS);
 136     checkrc(ret,__LINE__);
 137     ret = SQLExecute(stmt);
 138     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 139     {
 140       printf("Statement failed\n");
 141        return 1;
 142     }
 143
 144
 145     printf("Tables  dropped successfully\n");
 146
 147     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 148     checkrc(ret,__LINE__);
 149
 150    ret = SQLDisconnect(dbc);
 151    checkrc(ret,__LINE__);
 152
 153    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 154    checkrc(ret,__LINE__);
 155
 156    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 157    checkrc(ret,__LINE__);
 158    return 0;
 159 }