wxWidgets Discussion Forum

Official forum for the wxWidgets Cross-Platform GUI Toolkit
https://forums.wxwidgets.org/

databaselayer ODBC "invalid precision value" error

https://forums.wxwidgets.org/viewtopic.php?f=1&t=19174

Page 1 of 2

databaselayer ODBC "invalid precision value" error

Posted: Mon Apr 28, 2008 8:00 am
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

Re: databaselayer ODBC "invalid precision value" e

Posted: Mon Apr 28, 2008 12:12 pm
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.

Posted: Fri Jun 13, 2008 2:08 am
by manteez
Thanks vtararin.

I'll try it again using your attachment file.

Posted: Thu Jan 22, 2009 4:26 am
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

Posted: Thu Jan 22, 2009 8:32 am
by vtararin

Code: Select all

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

 ...... skiped .... 

    case OdbcParameter::PARAM_BLOB:
      nReturn = SQL_LONGVARBINARY; // was SQL_BINARY
      break;

Posted: Thu Jan 22, 2009 3:48 pm
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?

Posted: Thu Jan 22, 2009 3:54 pm
by vtararin
You should put these codes in ODBCParameter.cpp of cause

Posted: Mon Jan 26, 2009 11:07 pm
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

Posted: Wed Jan 28, 2009 5:14 am
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

Posted: Wed Jan 28, 2009 11:10 am
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());
}

Posted: Wed Jan 28, 2009 12:48 pm
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!

Posted: Sun Feb 01, 2009 8:55 pm
by jb_coder
The latest code in subversion for DatabaseLayer should fix any issues. Please let me know if there are any more issues.

Posted: Mon Feb 02, 2009 7:02 am
by manteez
wow...great. I can't wait to try the latest version. :)

Posted: Mon Feb 02, 2009 1:57 pm
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.

Posted: Tue Feb 03, 2009 8:49 am
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.
All times are UTC
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited