[freetds] freetds - invalid cursor state
jezekj at centrum.cz
jezekj at centrum.cz
Fri Feb 12 01:26:37 EST 2016
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.
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>>
> 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
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>
More information about the FreeTDS
mailing list