Problem with SQLColumns in ODBC API
-
Greetings I am writing an app to import data from an Access DB. I am fetching the information about columns of a table by using the following method - SQLCHAR colname[SIZE_128+1]={0}; SQLINTEGER colsize=0; SQLSMALLINT decimaldigit=0; // allocate. VERIFY ( (retcode = SQLAllocHandle ( SQL_HANDLE_STMT, sqlhdbc, &hstmt )) == SQL_SUCCESS ); retcode = SQLColumns ( hstmt, NULL, 0, NULL, 0, (SQLCHAR*)szTable, SQL_NTS, NULL, 0 ); if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { /* Bind columns in result set to buffers */ SQLBindCol(hstmt, 4, SQL_C_CHAR, colname, SIZE_128, &size ); SQLBindCol(hstmt, 7, SQL_C_SLONG, &colsize, 0, &size ); SQLBindCol(hstmt, 9, SQL_C_SSHORT, &decimaldigit, 0, &decsize ); } while( TRUE ) { retcode = SQLFetch(hstmt); if ( retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ) { // do stuff } else { break; } } Now the problem is that even if a column is defined as DOUBLE in Access and has data like 2.345, 3.001, 0.0001 etc. The decimaldigit is always returned with value 0. Therefore I am not able to get the correct decimal point for the column. So instead of getting colsize = 15 and decimaldigit = 2, I am always getting colsize = 15 and decimaldigit = 0? Am I doing something wrong? Thanks in advance. Karam
-
Greetings I am writing an app to import data from an Access DB. I am fetching the information about columns of a table by using the following method - SQLCHAR colname[SIZE_128+1]={0}; SQLINTEGER colsize=0; SQLSMALLINT decimaldigit=0; // allocate. VERIFY ( (retcode = SQLAllocHandle ( SQL_HANDLE_STMT, sqlhdbc, &hstmt )) == SQL_SUCCESS ); retcode = SQLColumns ( hstmt, NULL, 0, NULL, 0, (SQLCHAR*)szTable, SQL_NTS, NULL, 0 ); if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { /* Bind columns in result set to buffers */ SQLBindCol(hstmt, 4, SQL_C_CHAR, colname, SIZE_128, &size ); SQLBindCol(hstmt, 7, SQL_C_SLONG, &colsize, 0, &size ); SQLBindCol(hstmt, 9, SQL_C_SSHORT, &decimaldigit, 0, &decsize ); } while( TRUE ) { retcode = SQLFetch(hstmt); if ( retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ) { // do stuff } else { break; } } Now the problem is that even if a column is defined as DOUBLE in Access and has data like 2.345, 3.001, 0.0001 etc. The decimaldigit is always returned with value 0. Therefore I am not able to get the correct decimal point for the column. So instead of getting colsize = 15 and decimaldigit = 2, I am always getting colsize = 15 and decimaldigit = 0? Am I doing something wrong? Thanks in advance. Karam
Perhaps you should have something like:
SQL_C_DOUBLE decimaldigit; SQLBindCol(hstmt, 9, SQL_C_DOUBLE, &decimaldigit, 0, &decsize); -
Perhaps you should have something like:
SQL_C_DOUBLE decimaldigit; SQLBindCol(hstmt, 9, SQL_C_DOUBLE, &decimaldigit, 0, &decsize);SQL_C_DOUBLE is defined as 8. So I used SQLDOUBLE decimaldigit; SQLBindCOl ( hstmt, 9, SQL_C_DOUBLE, &decimaldigit, 0, &decsize ); For the double column the decimaldigit value is 0.000000000 and decsize it is -1. I am confused. Even the MSDN docs tell me to use the function this way :confused:
-
SQL_C_DOUBLE is defined as 8. So I used SQLDOUBLE decimaldigit; SQLBindCOl ( hstmt, 9, SQL_C_DOUBLE, &decimaldigit, 0, &decsize ); For the double column the decimaldigit value is 0.000000000 and decsize it is -1. I am confused. Even the MSDN docs tell me to use the function this way :confused:
I have never been a fan of SQLColumns.... usually I just do a 'select * from where 1=0' and use the following fcns to get metadata (sorry for the code bloat...): short DBStatement::GetColDBType(int iColumnNum) { short iStatus; char caDummy[2]; short iDummy; long lColType = -1; if (m_hStmtHdl) { ResetError(); iStatus = ::SQLColAttributes(m_hStmtHdl,(UWORD)iColumnNum,(UWORD)SQL_COLUMN_TYPE,(PTR)caDummy,(SWORD)1,(SWORD *)&iDummy,(SDWORD *)&lColType); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) { UpdateDBError(); lColType = -1; } } else NoActiveStatement(); return (short)lColType; } LPSTR DBStatement::GetColName(int iColumnNum) { short iStatus; long lDummy; short iDummy; InitDataBuffer(); if (m_hStmtHdl) { ResetError(); iStatus = ::SQLColAttributes(m_hStmtHdl,(UWORD)iColumnNum,(UWORD)SQL_COLUMN_NAME,(PTR)m_caDataBuf,(SWORD)1000,(SWORD *)&iDummy,(SDWORD *)&lDummy); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) { UpdateDBError(); m_caDataBuf[0] = 0; } } else NoActiveStatement(); return m_caDataBuf; } long DBStatement::GetColWidth(int iColumnNum) { short iStatus; char caDummy[2]; long lColWidth = -1; short iDummy; if (m_hStmtHdl) { ResetError(); iStatus = ::SQLColAttributes(m_hStmtHdl,(UWORD)iColumnNum,(UWORD)SQL_COLUMN_LENGTH,(PTR)caDummy,(SWORD)1,(SWORD *)&iDummy,(SDWORD *)&lColWidth); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) UpdateDBError(); } else NoActiveStatement(); return lColWidth; } int DBStatement::GetNumCols() { short iStatus; short iRetval = -1; if (m_hStmtHdl) { ResetError(); iStatus = ::SQLNumResultCols(m_hStmtHdl,(short *)&iRetval); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) UpdateDBError(); } else NoActiveStatement(); return (int)iRetval; }
-
I have never been a fan of SQLColumns.... usually I just do a 'select * from where 1=0' and use the following fcns to get metadata (sorry for the code bloat...): short DBStatement::GetColDBType(int iColumnNum) { short iStatus; char caDummy[2]; short iDummy; long lColType = -1; if (m_hStmtHdl) { ResetError(); iStatus = ::SQLColAttributes(m_hStmtHdl,(UWORD)iColumnNum,(UWORD)SQL_COLUMN_TYPE,(PTR)caDummy,(SWORD)1,(SWORD *)&iDummy,(SDWORD *)&lColType); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) { UpdateDBError(); lColType = -1; } } else NoActiveStatement(); return (short)lColType; } LPSTR DBStatement::GetColName(int iColumnNum) { short iStatus; long lDummy; short iDummy; InitDataBuffer(); if (m_hStmtHdl) { ResetError(); iStatus = ::SQLColAttributes(m_hStmtHdl,(UWORD)iColumnNum,(UWORD)SQL_COLUMN_NAME,(PTR)m_caDataBuf,(SWORD)1000,(SWORD *)&iDummy,(SDWORD *)&lDummy); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) { UpdateDBError(); m_caDataBuf[0] = 0; } } else NoActiveStatement(); return m_caDataBuf; } long DBStatement::GetColWidth(int iColumnNum) { short iStatus; char caDummy[2]; long lColWidth = -1; short iDummy; if (m_hStmtHdl) { ResetError(); iStatus = ::SQLColAttributes(m_hStmtHdl,(UWORD)iColumnNum,(UWORD)SQL_COLUMN_LENGTH,(PTR)caDummy,(SWORD)1,(SWORD *)&iDummy,(SDWORD *)&lColWidth); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) UpdateDBError(); } else NoActiveStatement(); return lColWidth; } int DBStatement::GetNumCols() { short iStatus; short iRetval = -1; if (m_hStmtHdl) { ResetError(); iStatus = ::SQLNumResultCols(m_hStmtHdl,(short *)&iRetval); if (iStatus != SQL_SUCCESS && iStatus != SQL_SUCCESS_WITH_INFO) UpdateDBError(); } else NoActiveStatement(); return (int)iRetval; }
The point being that you can develop a generic function to return your metadata using the SQLColAttributes ODBC call.