Re: freetds - invalid cursor state

2016-02-12 6:26 GMT+00:00  <[email protected]>:
> 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: <[email protected]>
>> Komu: FreeTDS Development Group <[email protected]>
>> 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 <[email protected]>
>> Komu: FreeTDS Development Group <[email protected]>
>> Datum: 10.02.2016 01:05
>> Předmět: Re: [freetds] freetds - invalid cursor state
>>
> 2016-02-09 20:22 GMT+00:00  <[email protected]>:
>>
>>
>> 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>>
>> 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>>
>>
>> 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
> [email protected]
> 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
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
> <http://lists.ibiblio.org/mailman/listinfo/freetds>
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds