CODEWITHSUNDEEP
sql-server
David I. McIntosh
David I. McIntosh

Reputation: 2130

SQLBindParameter with SQL_VARBINARY(MAX) gives "Invalid precision value"

Using a C++ code, I am trying to insert a large binary blob into a MS SQL server using a stored procedure. The table into which I am inserting is has 5 columns, types:

int
varchar
datetime
varbinary(max)
datetime

The stored procedure takes 4 parameters:

PROCEDURE [dbo].[spr_fff] 
     @act           AS INT
    ,@id            AS VARCHAR(255)
    ,@timestamp     AS DATETIME
    ,@blob          AS VARBINARY(MAX)

I set up my statement (with checks on return values that I am not showing):

const std::string queryString("{Call [spr_fff](?,?,?,?)}");
SQLHSTMT handle = NULL;
SQLAllocHandle(SQL_HANDLE_STMT, m_hConn, &handle);
SQLPrepare(handle, (SQLCHAR *)queryString.c_str(), SQL_NTS);

I bind the first three parameters with no problem, but I seem unable to figure out how to bind the 4th parameter. The code is essentially:

std::string sData; getData(sData);  //fills sData with the binary data
SQLLEN len1 = ???;
SQLBindParameter( handle, (SQLUSMALLINT)4, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, len1, 0, (SQLCHAR*)&sData.c_str(), (SQLLEN)sData.size(), NULL);

and the trick seems to be figuring out what len1 should be. If sData.size() < 8000, then len1 = sData.size() works fine. But if sData.size() > 8000, nothing seems to work. If I set len1 = sData.size(), or len1 = 2147483647 the call to SQLBindParameter results in the error code "Invalid precision value". If I set len1 = 0 as some of the (horrible) documentation seems to suggest, the call to SQLBindParameter works (for the 2008 native driver), but executing the statement results in a blob of size two, i.e. the two default 0 bytes with all the input blob data truncated to 0 bytes. I have tried all these combinations with with all the client drivers listed below, all to no avail. What am I doing wrong?????

Environment
Client OS: Windows XP sp3

SQL Server is
Microsoft SQL Server 09.00.3042

SQL Clients tried:
Microsoft SQL Server Native Client Version 10.00.5500 (sqlncli10.dll, 2007.100.5500.00)
Microsoft SQL Native Client Version 09.00.5000 (sqlncli.dll, 2005.90.5000.00)
Microsoft SQL Server ODBC Driver Version 03.85.1132 (sqlsrv32.dll 2000.85.1132.0)

Upvotes: 3

Views: 5699

Answers (2)

David I. McIntosh
David I. McIntosh

Reputation: 2130

OK, the answer to my question is actually that I screwed up in the call to SQLBindParameter. If you look at my code above, I have the final parameter as NULL. A careful reading of the documentation - and believe me, it needs much careful reading! - shows that if the final parameter is NULL, the data is treated as zero-terminated. (the documentation for SQLBindParameter says "If StrLen_or_IndPtr is a null pointer, the driver assumes that all input parameter values are non-NULL and that character and binary data is null-terminated." - emphasis mine) And, by unfortunate coincidence, the data I was supplying had a zero as the second or third byte, so the blob that was actually stored was only 1 or 2 bytes. Not sure why it worked with a size under 8000 - there may have been some interplay with size<8000 and various driver versions, but I haven't taken the time to tease that out.

Also, in the code above I state "If I set len1 = 0 as some of the (horrible) documentation seems to suggest,". This is in fact the correct thing to do.

The correct code is thus

SQLLEN len1 = 0;
SQLLEN nThisLen = (SQLLEN)sData.size();
SQLBindParameter( handle, (SQLUSMALLINT)4, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, len1, 0, (SQLCHAR*)&sData.c_str(), nThisLen, &nThisLen );

Upvotes: 4

Zee
Zee

Reputation: 1890

Jeff O was on the right track. Try changing your sproc to...

PROCEDURE [dbo].[spr_fff] 
    @act           AS INT
    ,@id           AS VARCHAR(255)
    ,@timestamp    AS DATETIME
    ,@preblob      AS nvarchar(MAX)

    DECLARE @blob varbinary(MAX) = CAST(@preblob as varbinary(MAX))
    /* Continue using varbinary blob as before */

Note the change of your sproc's parameter datatype and the subsequent cast to varbinary.

Cheers.

Upvotes: 0

Related Questions