[freetds] freetds - invalid cursor state

jezekj at centrum.cz jezekj at centrum.cz
Thu Feb 25 15:17:15 EST 2016


Hello Frediano,
 
I was busy with another tasks, but today I had some spare time so I've generated ODBC logs when using freetds and easysoft drivers. Both are attached. I would really apreciate if you can take a look at these logs and try to identify the root cause. I really would like to have this issue fixed.
 
Thanks,
Jakub
 
______________________________________________________________
> Od: Frediano Ziglio <freddy77 at gmail.com>
> Komu: FreeTDS Development Group <freetds at lists.ibiblio.org>
> Datum: 13.02.2016 09:20
> Předmět: Re: [freetds] freetds - invalid cursor state
>
2016-02-12 6:26 GMT+00:00  <jezekj at centrum.cz>:
> Hi again,
> I was investigating further a bit:
>
> 1) Have tried to use easysoft driver and tried to compare unixodbc log. Logs
> are same except the log when easysoft driver is used does continue with
> fetching of other data.
>

Could you post these logs?

Frediano

> 2) I have played with stored procedures and I have found that you do not
> need to call second procedure with print, or raiserror, these commands can
> be in one proc only. i.e.:
>
> Create procedure testproc as
> select getdate()
> print test
> select getdate()
>
> The invalid cursor state is thrown only when you do attempt to fetch data
> from procedure.
>
> This is fetching of first getdate of procedure:
> odbc.c:3615:SQLExecute returns SQL_SUCCESS
> odbc.c:4626:_SQLRowCount(0xb6130a0, 0xb612588), -1 rows
> odbc.c:4637:SQLRowCount returns 0, row count -1
> odbc.c:4545:SQLNumResultCols(0xb6130a0, 0x7fff718bda50)
> odbc.c:4545:SQLNumResultCols(0xb6130a0, 0x7fff718bd9f6)
> odbc_export.h:195:SQLDescribeCol(0xb6130a0, 1, 0xb61d8d0, 128, 0xb61d870,
> 0xb61d882, 0xb61d878, 0xb61d880, 0xb61d884)
> odbc.c:2047:_SQLColAttribute(0xb6130a0, 1, 6, (nil), 0, (nil), 0xb61d890)
> odbc.c:2089:SQLColAttribute: fDescType is 6
> odbc.c:2047:_SQLColAttribute(0xb6130a0, 1, 1003, (nil), 0, (nil), 0xb61d888)
> odbc.c:2089:SQLColAttribute: fDescType is 1003
> odbc.c:1803:SQLBindCol(0xb6130a0, 1, 1, 0xb41c870, 24, 0xb61d8a8)
> odbc.c:4033:SQLFetch(0xb6130a0)
> odbc.c:3768:_SQLFetch(0xb6130a0, 1, 0)
> token.c:549:tds_process_tokens(0xb5ce7d0, 0x7fff718bd794, 0x7fff718bd790,
> 0x6508)
> util.c:165:Changed query state from PENDING to READING
>
> When you try to fetch second getdate log continues with:
> odbc.c:3697:odbc_process_tokens: processed TDS_DONEINPROC_RESULT
> odbc.c:895:SQLMoreResults: result_type=4054, row_count=-1, lastrc=1
> error_export.h:6:SQLGetDiagRec(3, 0xb6130a0, 1, 0x7fff718bd870,
> 0x7fff718bd88c, 0x7fff718bd670, 512, 0x7fff718bd888)
> error.c:565:SQLGetDiagRec: "[FreeTDS][SQL Server]test"
> error_export.h:6:SQLGetDiagRec(3, 0xb6130a0, 2, 0x7fff718bd870,
> 0x7fff718bd88c, 0x7fff718bd670, 512, 0x7fff718bd888)
> odbc.c:4263:SQLFreeStmt(0xb6130a0, 2)
> odbc.c:4179:_SQLFreeStmt(0xb6130a0, 2, 0)
> odbc.c:4545:SQLNumResultCols(0xb6130a0, 0x7fff718bd9d6)
> odbc.c:4033:SQLFetch(0xb6130a0)
> odbc.c:3768:_SQLFetch(0xb6130a0, 1, 0)
> error.c:414:odbc_errs_add: "Invalid cursor state"
> error_export.h:6:SQLGetDiagRec(3, 0xb6130a0, 1, 0x7fff718bd870,
> 0x7fff718bd88c, 0x7fff718bd670, 512, 0x7fff718bd888)
> error.c:565:SQLGetDiagRec: "[FreeTDS][SQL Server]Invalid cursor state"
> error_export.h:6:SQLGetDiagRec(3, 0xb6130a0, 2, 0x7fff718bd870,
> 0x7fff718bd88c, 0x7fff718bd670, 512, 0x7fff718bd888)
> odbc.c:4263:SQLFreeStmt(0xb6130a0, 0)
> odbc.c:4179:_SQLFreeStmt(0xb6130a0, 0, 0)
> query.c:2189:tds_send_cancel: not in_cancel and not idle
>
> I have tried to rewrite the procedure:
>
> Create procedure testproc as
> print test
> select getdate()
> select getdate()
>
> And the tracefile is different and procedure runs fine:
> odbc.c:3615:SQLExecute returns SQL_SUCCESS_WITH_INFO
> error_export.h:6:SQLGetDiagRec(3, 0x2e4b0b0, 1, 0x7fff9ea22de0,
> 0x7fff9ea22dfc, 0x7fff9ea22be0, 512, 0x7fff9ea22df8)
> error.c:565:SQLGetDiagRec: "[FreeTDS][SQL Server]test"
> error_export.h:6:SQLGetDiagRec(3, 0x2e4b0b0, 2, 0x7fff9ea22de0,
> 0x7fff9ea22dfc, 0x7fff9ea22be0, 512, 0x7fff9ea22df8)
> odbc.c:4626:_SQLRowCount(0x2e4b0b0, 0x2e4a598), -1 rows
> odbc.c:4637:SQLRowCount returns 0, row count -1
> odbc.c:4545:SQLNumResultCols(0x2e4b0b0, 0x7fff9ea22fa0)
> odbc.c:4545:SQLNumResultCols(0x2e4b0b0, 0x7fff9ea22f46)
> odbc_export.h:195:SQLDescribeCol(0x2e4b0b0, 1, 0x2e5a5d0, 128, 0x2e5a570,
> 0x2e5a582, 0x2e5a578, 0x2e5a580, 0x2e5a584)
> odbc.c:2047:_SQLColAttribute(0x2e4b0b0, 1, 6, (nil), 0, (nil), 0x2e5a590)
> odbc.c:2089:SQLColAttribute: fDescType is 6
> odbc.c:2047:_SQLColAttribute(0x2e4b0b0, 1, 1003, (nil), 0, (nil), 0x2e5a588)
> odbc.c:2089:SQLColAttribute: fDescType is 1003
> odbc.c:1803:SQLBindCol(0x2e4b0b0, 1, 1, 0x2c54880, 24, 0x2e5a5a8)
> odbc.c:4033:SQLFetch(0x2e4b0b0)
> odbc.c:3768:_SQLFetch(0x2e4b0b0, 1, 0)
> token.c:549:tds_process_tokens(0x2e067e0, 0x7fff9ea22ce4, 0x7fff9ea22ce0,
> 0x6508)
> util.c:165:Changed query state from PENDING to READING
>
> The main difference is that SQLExecute returns SQL_SUCCESS_WITH_INFO, but in
> case of failure it returns odbc.c:3615:SQLExecute returns SQL_SUCCESS
>
> Will this information be sufficient to get the issue fixed? Or do you want
> some more information from me to help you with investigation?
> Thanks & Regards,
> JJ
>
> ______________________________________________________________
>>
>> Od: <jezekj at centrum.cz>
>> Komu: FreeTDS Development Group <freetds at lists.ibiblio.org>
>> Datum: 10.02.2016 08:57
>>
>> Předmět: Re: [freetds] freetds - invalid cursor state
>>
> Hello Frediano,
>
> thank you for looking into this issue. In the meantime I will rebuild our
> Perl::DBD to use latest unixODBC (2.3.4) nad let you know results. Please
> let me know your findings about status of SQLNumResultCols and
> SQLMoreResults when using Windows driver.
>
> Thank you,
> JJ
>
> ______________________________________________________________
>>
>> Od: Frediano Ziglio <freddy77 at gmail.com>
>> Komu: FreeTDS Development Group <freetds at lists.ibiblio.org>
>> Datum: 10.02.2016 01:05
>> Předmět: Re: [freetds] freetds - invalid cursor state
>>
> 2016-02-09 20:22 GMT+00:00  <jezekj at centrum.cz>:
>>
>>
>> Hi guys, we have to find some solution.
>>
>> We have perl script using DBD::ODBC to connect to MSSQL using unixODBC
>> driver and FreeTDS. When we execute stored procedure and this stored
>> procedure is executing another stored procedure with raiserror or print
>> functions we get invalid cursor state error.
>>
>> i.e.:
>> CREATE PROCEDURE [dbo].[dummyproc] as
>> RAISERROR('test', 0, 0)
>> return 0
>>
>> CREATE PROCEDURE [dbo].[testproc] as
>> select GETDATE()
>> exec dummyproc
>> select GETDATE()
>> return 0
>>
>> This happens ONLY when we try to fetch results from second getdate()
>> function.
>> i.e.:
>> my $query = 'exec testproc';
>> my $sth   = $dbh->prepare($query);
>> $sth->execute || die "Error running query $query";
>> while ( my $d = $sth->fetchrow_arrayref ) {
>>     print Dumper $d; #should return first getdate()
>> }
>> while ( my $d = $sth->fetchrow_arrayref ) {
>>     print Dumper $d; #should return second getdate()
>> }
>>
>> We’re using:
>> Old perl 5.8.8.
>> DBD::ODBC version 1.45
>> unixODBC 2.3.1 (also tried newest 2.3.4.)
>> FreeTDS 0.91.dev.20110409RC1 (also tried FreeTDS 0.95.81)
>>
>> In case we use Easysoft database driver instead of FreeTDS the error does
>> not appear.
>> When I call out the procedure from tsql error does not appear.
>>
>> The log using latest FreeTDS can be found here:
>> http://jezecek.dyndns.org/freetds.log <http://jezecek.dyndns.org/freetds.log>
>> <http://jezecek.dyndns.org/freetds.log <http://jezecek.dyndns.org/freetds.log>>
>> <http://jezecek.dyndns.org/freetds.log <http://jezecek.dyndns.org/freetds.log>
>> <http://jezecek.dyndns.org/freetds.log <http://jezecek.dyndns.org/freetds.log>>>
>> The unixODBC log (not sure it will help) can be found here:
>> http://jezecek.dyndns.org/odbc.log <http://jezecek.dyndns.org/odbc.log> <http://jezecek.dyndns.org/odbc.log <http://jezecek.dyndns.org/odbc.log>>
>> <http://jezecek.dyndns.org/odbc.log <http://jezecek.dyndns.org/odbc.log> <http://jezecek.dyndns.org/odbc.log <http://jezecek.dyndns.org/odbc.log>>>
>>
>> I would really really appreciate if you can let me know what to focus on.
>> Thank you for any hints& supprt from you.
>>
>> Regards,
>> JJ
>>
>
> Does not look so weird. In ODBC 3 resultset can have no records, for
> instance they can contains an error. You got an error in between two
> recordsets with rows. It seems that DBD::ODBC calls
> SQLFreeStmt(SQL_UNBIND) but this should not skip the error.
> I'll check exactly in this condition what SQLNumResultCols and
> SQLMoreResults returns with a Windows driver. I remember a similar
> problem using an old unixODBC version.
>
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>
> <http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>>
> <http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>
> <http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>>>
>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>
> <http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds <http://lists.ibiblio.org/mailman/listinfo/freetds>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: odbc_freetds.log
Type: text/x-log
Size: 12539 bytes
Desc: odbc_freetds.log
URL: <http://lists.ibiblio.org/pipermail/freetds/attachments/20160225/646b9116/attachment-0002.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: odbc_easysoft.log
Type: text/x-log
Size: 14926 bytes
Desc: odbc_easysoft.log
URL: <http://lists.ibiblio.org/pipermail/freetds/attachments/20160225/646b9116/attachment-0003.bin>


More information about the FreeTDS mailing list