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

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

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 64 times

manteez
Experienced Solver
Experienced Solver
Posts: 58
Joined: Fri Dec 07, 2007 7:54 am

Post by manteez » Fri Jun 13, 2008 2:08 am

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

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 » Thu Jan 22, 2009 8:32 am

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

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 » Thu Jan 22, 2009 3:54 pm

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

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

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 48 times
OdbcPreparedStatement.cpp
(11 KiB) Downloaded 62 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 » Wed Jan 28, 2009 11:10 am

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

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 » Sun Feb 01, 2009 8:55 pm

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 » Mon Feb 02, 2009 7:02 am

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

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 » Tue Feb 03, 2009 8:49 am

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