December 11, 2015 at 3:04 am
Using SQL Native Client ODBC 11 with SQL Server 2014, trying to fetch VARBINARY(4) data
with SQLGetData() after binding with SQLBindCol (... SQL_DATA_AT_EXEC), the server
returns error 16911 "fetch: The fetch type refresh cannot be used with forward only cursor"
The same code works find with VARBINARY(MAX) and VARCHAR(MAX), fetch LOBs data
in pieces... So why does the server produce this error with a VARBINARY(N) ???
Here the Transac-SQL code (simulating ODBC client calls) to reproduce the problem:
Try following SQL (works with table t11 using VARBINARY(MAX)):
create table t11 ( pk int, vb_4 varbinary(max))
insert into t11 values ( 1, cast(256 as varbinary(max)) )
insert into t1 values ( 2, cast(4096 as varbinary(max)) )
declare @p1 int, @p2 int, @p3 int, @p4 int, @p5 int
set @p1=0
exec sp_cursorprepare @p1 output,NULL,N'select * from t11 order by pk', 1, 16400, 8193
set @p2=0
set @p3=16
set @p4=1
set @p5=-1
exec sp_cursorexecute @p1 ,@p2 output,@p3 output,@p4 output,@p5 output
exec sp_cursoroption @p2,2,N'mycurs1'
exec sp_cursorfetch @p2,2,0,1
exec sp_cursor @p2,40,1
exec sp_cursorunprepare @p1
But this sequence fails (using t12 with VARBINARY(4))
create table t12 ( pk int, vb_4 varbinary(4))
insert into t12 values ( 1, cast(256 as varbinary(4)) )
insert into t12 values ( 2, cast(4096 as varbinary(4)) )
declare @p1 int, @p2 int, @p3 int, @p4 int, @p5 int
set @p1=0
exec sp_cursorprepare @p1 output,NULL,N'select * from t12 order by pk', 1, 16400, 8193
set @p2=0
set @p3=16
set @p4=1
set @p5=-1
exec sp_cursorexecute @p1 ,@p2 output,@p3 output,@p4 output,@p5 output
exec sp_cursoroption @p2,2,N'mycurs2'
exec sp_cursorfetch @p2,2,0,1
exec sp_cursor @p2,40,1
exec sp_cursorunprepare @p1
Seb
December 22, 2015 at 6:14 am
interesting.
The top query also fails when you do this
exec sp_cursorprepare @p1 output,NULL,N'select pk from t11 order by pk', 1, 16400, 8193
but not when you do this
exec sp_cursorprepare @p1 output,NULL,N'select * from t11 order by pk', 1, 16400, 8193
So it looks like...........
It throws an error when I restrict the data to in_row data and make it like the t12 table which should also all be inrow.
So the only difference is the LOB, from what I can see.
Just curious.
Is there a specific problem that you want to solve or is this for your own interest?
December 22, 2015 at 7:29 am
In fact we have implemented a database driver for our proprietary VM, and this driver is based on ODBC (SQL Native Client).
To make it short, we provide a development language called Genero BDL, with our own pcode and VM. Our customers write applications in this language, and our SQL Server driver is just preparing / executing SQL statements through ODBC / SQL Native Client. We need to write generic code that can execute any sort of SQL statements, with any sort of data types as param or in the result set.
As you can imagine, we don't have the control over the sp_cursor* calls, this is all behind ODBC.
I thought it was interesting to report this issue to SQL Server implementers (I have also posted a ticket on Microsoft connect: https://connect.microsoft.com/SQLServer/feedback/details/2109209, but there is not much activity over there)
We could workaround this problem by detecting [VAR]BINARY(N) usage in statements, and fetch the data into pre-allocated buffers, instead of doing SQL_DATA_AT_EXEC / SQLGetData()... so far so good (even better to do it this way for small [VAR]BINARY(N) data)...
Still, the sp_cursor* behavior reported here looks like a bug to me.
December 26, 2015 at 11:54 pm
Error is likely being raised due to an implicit cursor conversion - see https://msdn.microsoft.com/en-us/library/ms131660.aspx. Consider URL's discussion of SQL_SUCCESS_WITH_INFO and
The application can determine what type of cursor is now being used by calling SQLGetStmtOption set to SQL_CURSOR_TYPE. The cursor type conversion applies to only one statement.
I believe sp_cursoroption returns the cursor type (to ODBC) - see Return Codes in https://msdn.microsoft.com/en-us/library/ff848779.aspx. Still doesn't fully explain why refresh works for the LOB, but it appears the LOB example is using a keyset cursor that supports refresh.
December 27, 2015 at 1:31 am
Thank you for that, this is interesting! ... and will certainly also help in another area, related to cursor type / SQL statement options:
It appears that we need to set ODBC SQL statement options (such as SQL_SOPT_SS_CURSOR_OPTIONS = SQL_CO_FFO) before doing the SQLPrepare()... Since we want to support any sort of SQL statement, we need to identify the type of SQL command (with or without result set, stored procedure call, or temp table creation) to set the appropriate cursor options. But we would like to keep the code generic and not parse the SQL text to detect what sort of SQL statement it is...
I better start a new discussion about this => here it is
December 27, 2015 at 2:25 am
There are several things wrong in this code, none of which have anything to do with the varbinary N or MAX. The most obvious ones are the wrong parameters passed to the sp_cursorprepare procedure, neither the scrollopt nor the ccopt match any documented options. Secondly, why is the option set to 1 RETURN_METADATA? The third issue is that there is no error handling and this code as it is will leave cursors open on the server.
😎
The cursor code in T-SQL
IF OBJECT_ID(N'dbo.t11') IS NOT NULL DROP TABLE dbo.t11;
CREATE TABLE dbo.t11
(
pk int
,vb_4 varbinary(max)
);
INSERT INTO dbo.t11 (pk,vb_4)
VALUES ( 1, CONVERT(VARBINARY(MAX), 256,0))
,( 2, CONVERT(VARBINARY(MAX),4096,0))
;
DECLARE @pk INT = 0;
DECLARE @VB VARBINARY(4) = 0x00;
DECLARE R_SET CURSOR FAST_FORWARD FOR
SELECT
T.pk
,T.vb_4
FROM dbo.t11 T
ORDER BY T.pk;
OPEN R_SET;
FETCH NEXT FROM R_SET INTO @pk,@VB;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VB;
FETCH NEXT FROM R_SET INTO @pk,@VB;
END
CLOSE R_SET;
DEALLOCATE R_SET;
December 27, 2015 at 6:18 am
As I wrote in the initial post, this code is produced by the SNAC ODBC client.
I have used the SQL Profiler to see what sp_cursor* calls are produced by the SNAC ODBC client...
Then I did a copy/paste of these commands, to reproduce in the SQL query interpreter.
I had to adapt a bit the code to make this test, but I used the original cursor options / flags.
So if there is something wrong in this code, then it's maybe a SNAC ODBC client bug...
However, according to https://msdn.microsoft.com/en-us/library/ff848767.aspx, the options passed to sp_cursorprepare seem to be ok:
scrollopt: (dec) 16400 = 0x4010 = 0x4000 (AUTO_CLOSE) | 0x0010 (FAST_FORWARD)
ccopt : (dec) 8193 = 0x2001 = 0x2000 (ALLOW_DIRECT) | 0x0001 (READ_ONLY)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply