I have a table with a BLOB field and I'm inserting a binary data into this table. Everything goes well unless I specify a binary data with more than 2000 bytes. In that case I'm getting the following error:
ERROR [HY104][ODBC Oracle Wire Protocol driver]Invalid precision value. Error in parameter 1
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
I wonder why would I get this error for BLOB field. I checked multiple times that I'm using BLOB data type and my query works for small byte arrays. Is it some well-known issue? Or should I configure a buffer length somewhere? (I'm using default settings).
I have a table with a BLOB field and I'm inserting a binary data into this table. Everything goes well unless I specify a binary data with more than 2000 bytes. In that case I'm getting the following error:
ERROR [HY104][ODBC Oracle Wire Protocol driver]Invalid precision value. Error in parameter 1
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
I wonder why would I get this error for BLOB field. I checked multiple times that I'm using BLOB data type and my query works for small byte arrays. Is it some well-known issue? Or should I configure a buffer length somewhere? (I'm using default settings).
Flag this post as spam/abuse.
#include "stdafx.h" #include <iostream> #include <windows.h> #include <sqltypes.h> #include <sql.h> #include <sqlext.h> void ShowError(unsigned int handletype, const SQLHANDLE& handle){ SQLWCHAR sqlstate[1024]; SQLWCHAR message[1024]; if(SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL)) std::wcout<<"Message: "<<message<<"\nSQLSTATE: "<<sqlstate<<std::endl; } void UpdateData() { try { SQLHENV environmentHandle; SQLHDBC dataSourceHandle; SQLHSTMT connectStatement,updateStatement; SQLSMALLINT columns; SQLLEN length; const int size = 1999; char* data = new char[size]; for(int i = 0; i < size; i++) data = '1'; data[size-1] = '\0'; length = size + 1; SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &environmentHandle); SQLSetEnvAttr(environmentHandle, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0); SQLAllocHandle(SQL_HANDLE_DBC, environmentHandle, &dataSourceHandle); SQLDriverConnectW(dataSourceHandle, NULL, L"DSN=MYDSN;LogonID=vasya;Password=pupkin;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE); if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, dataSourceHandle, &connectStatement)) ShowError(SQL_HANDLE_STMT, connectStatement); std::cout << "Binding parameters..." << std::endl; SQLAllocHandle(SQL_HANDLE_STMT, dataSourceHandle, &updateStatement); if (SQL_SUCCESS!=SQLBindParameter(updateStatement, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, length, 0, (SQLCHAR*)data, length, &length)) ShowError(SQL_HANDLE_STMT, updateStatement); std::cout << "Preparing statement..." << std::endl; if(SQL_SUCCESS!=SQLPrepare(updateStatement, L"UPDATE TS_BLOBS set TS_BLOB=? WHERE TS_ID=4442", SQL_NTS)) ShowError(SQL_HANDLE_STMT, updateStatement); std::cout << "Executing statement..." << std::endl; if(SQL_SUCCESS!=SQLExecute(updateStatement)) ShowError(SQL_HANDLE_STMT, updateStatement); std::cout << "Complete." << std::endl; SQLFreeHandle(SQL_HANDLE_STMT, updateStatement ); SQLDisconnect(connectStatement); SQLFreeHandle(SQL_HANDLE_DBC, dataSourceHandle); SQLFreeHandle(SQL_HANDLE_ENV, environmentHandle); std::cin.get(); } catch(std::exception& e) { std::cout << "Standard exception: " << e.what() << std::endl; std::cin.get(); } } int _tmain(int argc, _TCHAR* argv[]) { UpdateData(); return 0; }
Originally the problem was found in .NET application, however I wrote a small example using C++. If I set the size to something larger than 2000 I get the invalid precision error.
#include "stdafx.h"
#include <iostream>
#include <windows.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>
void ShowError(unsigned int handletype, const SQLHANDLE& handle){
SQLWCHAR sqlstate[1024];
SQLWCHAR message[1024];
if(SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL))
std::wcout<<"Message: "<<message<<"\nSQLSTATE: "<<sqlstate<<std::endl;
}
void UpdateData()
{
try
{
SQLHENV environmentHandle;
SQLHDBC dataSourceHandle;
SQLHSTMT connectStatement,updateStatement;
SQLSMALLINT columns;
SQLLEN length;
const int size = 1999;
char* data = new char[size];
for(int i = 0; i < 1000; i++)
data = '1';
data[size-1] = '\0';
length = size + 1;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &environmentHandle);
SQLSetEnvAttr(environmentHandle, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, environmentHandle, &dataSourceHandle);
SQLDriverConnectW(dataSourceHandle, NULL, L"DSN=MYDSN;LogonID=vasya;Password=pupkin;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, dataSourceHandle, &connectStatement))
ShowError(SQL_HANDLE_STMT, connectStatement);
std::cout << "Binding parameters..." << std::endl;
SQLAllocHandle(SQL_HANDLE_STMT, dataSourceHandle, &updateStatement);
if (SQL_SUCCESS!=SQLBindParameter(updateStatement, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, length, 0, (SQLCHAR*)data, length, &length))
ShowError(SQL_HANDLE_STMT, updateStatement);
std::cout << "Preparing statement..." << std::endl;
if(SQL_SUCCESS!=SQLPrepare(updateStatement, L"UPDATE TS_BLOBS set TS_BLOB=? WHERE TS_ID=4442", SQL_NTS))
ShowError(SQL_HANDLE_STMT, updateStatement);
std::cout << "Executing statement..." << std::endl;
if(SQL_SUCCESS!=SQLExecute(updateStatement))
ShowError(SQL_HANDLE_STMT, updateStatement);
std::cout << "Complete." << std::endl;
SQLFreeHandle(SQL_HANDLE_STMT, updateStatement );
SQLDisconnect(connectStatement);
SQLFreeHandle(SQL_HANDLE_DBC, dataSourceHandle);
SQLFreeHandle(SQL_HANDLE_ENV, environmentHandle);
std::cin.get();
}
catch(std::exception& e)
{
std::cout << "Standard exception: " << e.what() << std::endl;
std::cin.get();
}
}
int _tmain(int argc, _TCHAR* argv[])
{
UpdateData();
return 0;
}
Flag this post as spam/abuse.
Thanks! If I change SQL_VARBINARY to SQL_LONGVARBINARY everything works, sorry for wasting your time.
Originally I found this error in .NET application. In .NET framework source code I see that they switch to LONGVARBINARY type if the size of the parameter is greater than 8000. Hopefully I can make it work.