databaselayer ODBC "invalid precision value" error Topic is solved
databaselayer ODBC "invalid precision value" error
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
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
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.
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 121 times
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
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
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));
}
}
actually, I've also tried with sql server and got same problem.
Any suggestion?
Thanks before
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.vtararin wrote:Code: Select all
SQLSMALLINT OdbcParameter::GetParameterType() { switch (m_nParameterType) { ...... skiped .... case OdbcParameter::PARAM_BLOB: nReturn = SQL_LONGVARBINARY; // was SQL_BINARY break;
but where should I put these codes?
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
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 110 times
-
- OdbcPreparedStatement.cpp
- (11 KiB) Downloaded 124 times
There are many bugs in the DatabaseLayer.jb_coder wrote:The latest code in subversion for DatabaseLayer should fix any issues. Please let me know if there are any more issues.
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 {
}