Problem with updating a BLOB in Oracle database v.11.2/12 us

Posted by progressuser on 10-Jul-2015 12:56

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).

All Replies

Posted by jhobson on 10-Jul-2015 13:21

What is being specified to SQLBindParameter for the BLOB column? Thanks.
 
[collapse]
From: progressuser [mailto:bounce-progressuser@community.progress.com]
Sent: Friday, July 10, 2015 1:57 PM
To: TU.DD.Connect@community.progress.com
Subject: [Technical Users - DataDirect Connect] Problem with updating a BLOB in Oracle database v.11.2/12 using ODBC Data Direct driver v.7.1 SP5
 
Thread created by progressuser

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).

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by progressuser on 13-Jul-2015 05:55

#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++)
            dataIdea = '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;
}

Posted by jhobson on 13-Jul-2015 06:17

SQLBindParameter(updateStatement, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, length, 0, (SQLCHAR*)data, length, &length)
 
Oracle’s RAW [SQL_VARBINARY] type is limited to 2000 bytes, which is why the driver returns an error if you specify a length greater than 2000. I believe switching the SQL type to SQL_LONGVARBINARY will fix the problem.
 
Thanks,
John
 
[collapse]
From: progressuser [mailto:bounce-progressuser@community.progress.com]
Sent: Monday, July 13, 2015 6:56 AM
To: TU.DD.Connect@community.progress.com
Subject: RE: [Technical Users - DataDirect Connect] Problem with updating a BLOB in Oracle database v.11.2/12 using ODBC Data Direct driver v.7.1 SP5
 
Reply by progressuser
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++)
            dataIdea = '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;
}
Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by progressuser on 13-Jul-2015 06:56

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.

This thread is closed