databaselayer ODBC "invalid precision value" error Topic is solved

If you are using the main C++ distribution of wxWidgets, Feel free to ask any question related to wxWidgets development here. This means questions regarding to C++ and wxWidgets, not compile problems.
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

databaselayer ODBC "invalid precision value" error

Post by manteez »

Hello everybody,

I got this problem when I want to store blob in microsoft Access using ODBC from databaselayer.

I have read some articles talking about this. This error happens when we want to store string more than 255 characters.

But, I don't know how to overcome this problem in databaselayer. Any suggestion?

Thanks
vtararin
Knows some wx things
Knows some wx things
Posts: 37
Joined: Thu May 24, 2007 4:43 pm
Location: Ukraine
Contact:

Re: databaselayer ODBC "invalid precision value" e

Post by vtararin »

DatabaseLayer has a bug in this part.
In the attachment "diff" for fix this problem. Tested only in UNICODE against MS SqlServer. I think should fork for MS access too.
Attachments
OdbcPreparedStatement.cpp.diff.zip
Diff against cvs to fix this problem.
(837 Bytes) Downloaded 118 times
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez »

Thanks vtararin.

I'll try it again using your attachment file.
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez »

I still got same problem after replacing this code. :(

I used static ansi monolithic build.

here is the code of odbcPreparedStatement after I've replaced it

Code: Select all

#include "../include/OdbcPreparedStatement.h"
#include "../include/OdbcResultSet.h"
#include "../include/OdbcDatabaseLayer.h"

// ctor
OdbcPreparedStatement::OdbcPreparedStatement(SQLHENV sqlEnvHandle, SQLHDBC sqlHDBC)
 : PreparedStatement()
{
    m_sqlEnvHandle = sqlEnvHandle;
    m_sqlHDBC = sqlHDBC;
    m_bOneTimeStatement = false;
}

OdbcPreparedStatement::OdbcPreparedStatement(SQLHENV sqlEnvHandle, SQLHDBC sqlHDBC, SQLHSTMT sqlStatementHandle)
 : PreparedStatement()
{
    m_sqlEnvHandle = sqlEnvHandle;
    m_sqlHDBC = sqlHDBC;
    m_bOneTimeStatement = false;
    m_Statements.push_back(sqlStatementHandle);
}

OdbcPreparedStatement::OdbcPreparedStatement(SQLHENV sqlEnvHandle, SQLHDBC sqlHDBC, StatementVector statements)
 : PreparedStatement()
{
    m_sqlEnvHandle = sqlEnvHandle;
    m_sqlHDBC = sqlHDBC;
    m_bOneTimeStatement = false;
    m_Statements = statements;
}

// dtor
OdbcPreparedStatement::~OdbcPreparedStatement()
{
  FreeParameters();
  Close();
}

void OdbcPreparedStatement::Close()
{
  CloseResultSets();

  StatementVector::iterator start = m_Statements.begin();
  StatementVector::iterator stop = m_Statements.end();
  while (start != stop)
  {
    if ((*start) != NULL)
    {
      SQLRETURN nRet = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHSTMT)*start);
      if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
      {
        InterpretErrorCodes(nRet, (SQLHSTMT)*start);
        ThrowDatabaseException();
        return;
      }
      (*start) = NULL;
    }
    start++;
  }
  m_Statements.Clear();
}

void OdbcPreparedStatement::FreeParameters()
{
  ArrayOfOdbcParameters::iterator start = m_Parameters.begin();
  ArrayOfOdbcParameters::iterator stop = m_Parameters.end();

  while (start != stop)
  {
    if ((*start) != NULL)
    {
      OdbcParameter* pParameter = *start;
      delete(pParameter);
      (*start) = NULL;
    }
    start++;
  }
  m_Parameters.Clear();
}

void OdbcPreparedStatement::AddPreparedStatement(SQLHSTMT sqlStatementHandle)
{
  m_Statements.push_back(sqlStatementHandle);
}

// get field
void OdbcPreparedStatement::SetParamInt(int nPosition, int nValue)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter(nValue);
    SetParam(nPosition, pParameter);
}

void OdbcPreparedStatement::SetParamDouble(int nPosition, double dblValue)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter(dblValue);
    SetParam(nPosition, pParameter);
}

void OdbcPreparedStatement::SetParamString(int nPosition, const wxString& strValue)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter(strValue);
    SetParam(nPosition, pParameter);
}

void OdbcPreparedStatement::SetParamNull(int nPosition)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter();
    SetParam(nPosition, pParameter);
}

void OdbcPreparedStatement::SetParamBlob(int nPosition, const void* pData, long nDataLength)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter(pData, nDataLength);
    SetParam(nPosition, pParameter);
}

void OdbcPreparedStatement::SetParamDate(int nPosition, const wxDateTime& dateValue)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter(dateValue);
    SetParam(nPosition, pParameter);
}

void OdbcPreparedStatement::SetParamBool(int nPosition, bool bValue)
{
    ResetErrorCodes();

    OdbcParameter* pParameter = new OdbcParameter(bValue);
    SetParam(nPosition, pParameter);
}

int OdbcPreparedStatement::GetParameterCount()
{
    ResetErrorCodes();

    int nReturn = 0;
    StatementVector::iterator start = m_Statements.begin();
    StatementVector::iterator stop = m_Statements.end();
    while (start != stop)
    {
        SQLSMALLINT num = 0;
        SQLRETURN nRet = SQLNumParams(((SQLHSTMT)(*start)), &num);
        if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
        {
            InterpretErrorCodes(nRet, (SQLHSTMT)(*start));
            ThrowDatabaseException();
            return -1;
        }
        nReturn += num;
        start++;
    }
    return nReturn;
}

void OdbcPreparedStatement::RunQuery()
{
    ResetErrorCodes();

    BindParameters();

    StatementVector::iterator start = m_Statements.begin();
    StatementVector::iterator stop = m_Statements.end();
    while (start != stop)
    {
        SQLRETURN nRet = 0;
        nRet = SQLFreeStmt((SQLHSTMT)(*start), SQL_CLOSE);
        if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
        {
            InterpretErrorCodes(nRet, (SQLHSTMT)(*start));
            ThrowDatabaseException();
            return;
        }

        nRet = SQLExecute((SQLHSTMT)(*start));
         if ( nRet != SQL_SUCCESS 
           && nRet != SQL_SUCCESS_WITH_INFO 
            && nRet != SQL_NO_DATA 
           && nRet != SQL_NEED_DATA){

		 if ( nRet == SQL_NEED_DATA) {
		     PTR pParmID;
		     nRet = SQLParamData((SQLHSTMT)(*start), &pParmID);
		     while (nRet == SQL_NEED_DATA) {
		     // Find the parameter
			 for ( unsigned int i = 0; i < m_Parameters.size(); i++ ) {
			      OdbcParameter* pParameter = m_Parameters[i];
			      if ( pParmID == pParameter->GetDataPtr() ) {
				 // We found it.  Store the parameter.
				 nRet = SQLPutData((SQLHSTMT)(*start), pParmID, pParameter->GetDataLength());
				 if ( nRet != SQL_SUCCESS ) {
				     InterpretErrorCodes(nRet, (SQLHSTMT)(*start));
				     ThrowDatabaseException();
				     return;
				 }
				 break;
			     }
			 }
			 nRet = SQLParamData((SQLHSTMT)(*start), &pParmID);
		     }
		     if ( nRet != SQL_SUCCESS 
			 || nRet != SQL_NO_DATA_FOUND 
			 || nRet != SQL_SUCCESS_WITH_INFO)
		     {
			 InterpretErrorCodes(nRet, (SQLHSTMT)(*start));
			 ThrowDatabaseException();
			 return;
		     }
		 }
		start++;
	    }
    }
}

DatabaseResultSet* OdbcPreparedStatement::RunQueryWithResults()
{
  return RunQueryWithResults(true);
}

DatabaseResultSet* OdbcPreparedStatement::RunQueryWithResults(bool bLogForCleanup)
{
    ResetErrorCodes();
    SQLSMALLINT ncol = 0;

    if (m_Statements.size() > 0)
    {
        BindParameters();

        for (unsigned int i=0; i<m_Statements.size(); i++)
        {
            SQLRETURN nRet = 0;
            nRet = SQLFreeStmt(m_Statements[i], SQL_CLOSE);
            if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
            {
                InterpretErrorCodes(nRet, m_Statements[i]);
                ThrowDatabaseException();
                return NULL;
            }

            nRet = SQLExecute(m_Statements[i]);
            if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
            {
                InterpretErrorCodes(nRet, m_Statements[i]);
                ThrowDatabaseException();
                return NULL;
            }

            nRet = SQLNumResultCols(m_Statements[i], &ncol);
            if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
            {
                InterpretErrorCodes(nRet, m_Statements[i]);
                ThrowDatabaseException();
                return NULL;
            }
        }
    }

    // Work off the assumption that only the last statement will return result
    DatabaseResultSet* pResultSet = new OdbcResultSet(this, m_bOneTimeStatement, (int)ncol);
    if (bLogForCleanup)
      LogResultSetForCleanup(pResultSet);
    return pResultSet;
}

void OdbcPreparedStatement::BindParameters()
{
  // Iterate through all of the parameters and bind them to the prepared statement
  for (unsigned int i = 1; i <= m_Parameters.size(); i++)
  {
    int nPosition = i;
    OdbcParameter* pParameter = m_Parameters[i-1];
    int nIndex = FindStatementAndAdjustPositionIndex(&nPosition);

    if ((nIndex > -1) && (pParameter != NULL))
    {
      SQLRETURN nRet = SQLBindParameter(m_Statements[nIndex], nPosition, SQL_PARAM_INPUT,
        pParameter->GetValueType(), pParameter->GetParameterType(),
        pParameter->GetColumnSize(), pParameter->GetDecimalDigits(), pParameter->GetDataPtr(),
        pParameter->GetDataLength(), pParameter->GetParameterLengthPtr() );

      if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
      {
        InterpretErrorCodes(nRet, m_Statements[nIndex]);
        ThrowDatabaseException();
        return;
      }
    }
  }
}

int OdbcPreparedStatement::FindStatementAndAdjustPositionIndex(int* pPosition)
{
    // Don't mess around if there's just one entry in the vector
    if (m_Statements.size() == 0)
        return 0;
        
    // Go through all the elements in the vector
    // Get the number of parameters in each statement
    // Adjust the nPosition for the the broken up statements
    for (unsigned int i=0; i<m_Statements.size(); i++)
    {
        SQLSMALLINT num = 0;
        SQLRETURN nRet = SQLNumParams(m_Statements[i], &num);
        if ( nRet != SQL_SUCCESS && nRet != SQL_SUCCESS_WITH_INFO )
        {
            InterpretErrorCodes(nRet, m_Statements[i]);
            ThrowDatabaseException();
            return -1;
        }

        int nParametersInThisStatement = (int)num;
        if (*pPosition > nParametersInThisStatement)
        {
            *pPosition -= nParametersInThisStatement;    // Decrement the position indicator by the number of parameters in this statement
        }
        else
        {
            // We're in the correct statement, return the index
            return i;
        }
    }
    return -1;
}

void OdbcPreparedStatement::SetParam(int nPosition, OdbcParameter* pParameter)
{
  // First make sure that there are enough elements in the collection
  while (m_Parameters.size() < (unsigned int)(nPosition))
  {
    m_Parameters.push_back(NULL);//EmptyParameter);
  }
  // Free up any data that is being replaced so the allocated memory isn't lost
  if (m_Parameters[nPosition-1] != NULL)
  {
    delete (m_Parameters[nPosition-1]);
  }
  // Now set the new data
  m_Parameters[nPosition-1] = pParameter;
}

void OdbcPreparedStatement::InterpretErrorCodes( long nCode, SQLHSTMT stmth_ptr )
{
  wxLogDebug(_("OdbcPreparedStatement::InterpretErrorCodes()\n"));

  //if ((nCode != SQL_SUCCESS) ) // && (nCode != SQL_SUCCESS_WITH_INFO))
  {
    SQLINTEGER iNativeCode;
    SQLTCHAR strState[ERR_STATE_LEN];
    SQLTCHAR strBuffer[ERR_BUFFER_LEN];
    SQLSMALLINT iMsgLen;

    memset(strState, 0, ERR_STATE_LEN*sizeof(SQLTCHAR));
    memset(strBuffer, 0, ERR_BUFFER_LEN*sizeof(SQLTCHAR));

    if (stmth_ptr)
      SQLGetDiagRec(SQL_HANDLE_STMT, stmth_ptr, 1, strState, &iNativeCode, 
        strBuffer, ERR_BUFFER_LEN, &iMsgLen);  
    else
      SQLGetDiagRec(SQL_HANDLE_DBC, m_sqlHDBC, 1, strState, &iNativeCode,
        strBuffer, ERR_BUFFER_LEN, &iMsgLen);  
    
    SetErrorCode((int)iNativeCode);
    //SetErrorMessage(ConvertFromUnicodeStream((char*)strBuffer));
    SetErrorMessage(wxString((wxChar*)strBuffer));
  }
}

Maybe I missed something...but i'm still curious in storing blob via odbc.

actually, I've also tried with sql server and got same problem.

Any suggestion?

Thanks before
vtararin
Knows some wx things
Knows some wx things
Posts: 37
Joined: Thu May 24, 2007 4:43 pm
Location: Ukraine
Contact:

Post by vtararin »

Code: Select all

SQLSMALLINT OdbcParameter::GetParameterType()
{
  switch (m_nParameterType)
  {

 ...... skiped .... 

    case OdbcParameter::PARAM_BLOB:
      nReturn = SQL_LONGVARBINARY; // was SQL_BINARY
      break;
cofounder of LogicLand Ltd.
my interest Kamenets-Podolskiy Online
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez »

vtararin wrote:

Code: Select all

SQLSMALLINT OdbcParameter::GetParameterType()
{
  switch (m_nParameterType)
  {

 ...... skiped .... 

    case OdbcParameter::PARAM_BLOB:
      nReturn = SQL_LONGVARBINARY; // was SQL_BINARY
      break;
Thanks for your answer.
but where should I put these codes?
vtararin
Knows some wx things
Knows some wx things
Posts: 37
Joined: Thu May 24, 2007 4:43 pm
Location: Ukraine
Contact:

Post by vtararin »

You should put these codes in ODBCParameter.cpp of cause
cofounder of LogicLand Ltd.
my interest Kamenets-Podolskiy Online
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez »

vtararin wrote:You should put these codes in ODBCParameter.cpp of cause
I'll try it vtararin...

and I'll give the report of the result :o
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez »

Yeah, it worked. Thanks vtatarin

if anybody have same problem with me, here is file attachments after I changed it...

Just put these files in src directory and overwrite the old files.

And rebuild your odbc databaselayer library.


Thanks
Attachments
OdbcParameter.cpp
(5.47 KiB) Downloaded 107 times
OdbcPreparedStatement.cpp
(11 KiB) Downloaded 121 times
vtararin
Knows some wx things
Knows some wx things
Posts: 37
Joined: Thu May 24, 2007 4:43 pm
Location: Ukraine
Contact:

Post by vtararin »

Another BLOB related bug found and fixed in PreparedStatement:


void PreparedStatement::SetParamBlob(int nPosition, const wxMemoryBuffer& buffer)
{
SetParamBlob(nPosition, buffer.GetData(), buffer.GetDataLen());
}
cofounder of LogicLand Ltd.
my interest Kamenets-Podolskiy Online
jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Post by jb_coder »

Using these contributions and a few other tweaks, I've got the ODBC backend passing all the DatabaseLayer unit tests with SQL Server and Postgresql locally. I'll try to commit these changes by the end of this weekend.

Thanks!
jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Post by jb_coder »

The latest code in subversion for DatabaseLayer should fix any issues. Please let me know if there are any more issues.
manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez »

wow...great. I can't wait to try the latest version. :)
vtararin
Knows some wx things
Knows some wx things
Posts: 37
Joined: Thu May 24, 2007 4:43 pm
Location: Ukraine
Contact:

Post by vtararin »

jb_coder wrote:The latest code in subversion for DatabaseLayer should fix any issues. Please let me know if there are any more issues.
There are many bugs in the DatabaseLayer.
ODBC part has bugs on extracting double and datetime values if use Russian, Ukrainian and possible other locales. My quick fix for this in the bottom of the post.

There is transaction handling inside PreparedStatement in the FireBird driver.

There is many problems with handling database resources if exception enabled. The source of the problem is way for closing Statement and ResultSet.

Code: Select all

void OdbcResultSet::RetrieveFieldData(int nField)
{
    if (nField != -1)
    {

        SQLRETURN rc;
        SQLSMALLINT buflen;
        unsigned long int numreturn;
        rc = SQLColAttribute(m_pOdbcStatement, nField, SQL_DESC_TYPE,
                    NULL, 0, &buflen, &numreturn);

        if ( SQL_FLOAT == numreturn || SQL_DOUBLE == numreturn ) {
            SQLFLOAT ret;
            SQLINTEGER sqptr;
            rc = SQLGetData(m_pOdbcStatement, nField, SQL_C_DOUBLE, &ret, 0, &sqptr);
            if ( rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO ) {
                wxLogError(_T("Error with RunQueryWithResults\n"));
                InterpretErrorCodes(rc, m_pOdbcStatement);
                ThrowDatabaseException();
            }
            m_fieldValues[nField-1] = ret;
        } else if ( SQL_DATETIME == numreturn ) {
            TIMESTAMP_STRUCT ret;
            SQLINTEGER sqptr;
            rc = SQLGetData(m_pOdbcStatement, nField, SQL_C_TIMESTAMP, &ret, sizeof(ret), &sqptr);
            if ( rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO ) {
                wxLogError(_T("Error with RunQueryWithResults\n"));
                InterpretErrorCodes(rc, m_pOdbcStatement);
                ThrowDatabaseException();
            }
            wxDateTime dt(ret.day, static_cast<wxDateTime::Month>(ret.month-1), ret.year, ret.hour, ret.minute, ret.second, ret.fraction );
            m_fieldValues[nField-1] = dt;

        } else {
         
        }
I can provide my version, but it has incompatible interface and implementation tested only for ODBC.
cofounder of LogicLand Ltd.
my interest Kamenets-Podolskiy Online
jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Post by jb_coder »

Do I need to change my OS to use Russian or Ukrainian or just the data that's saved in the database? I'm assuming that I'll need to compile wxWindows and DatabaseLayer with UNICODE as well.
Post Reply