ct_results() and ct_cmd_drop() error with Sybase::CTlib

I'm using Sybase::CTlib to query a Sybase server. However when I execute the following:

while( $dbr->ct_results($restype) == CS_SUCCEED ) {
    if( $restype == CS_CMD_FAIL ) {
    warn "Update Check Failed...";
    next;
    }
    next unless $dbr->ct_fetchable($restype);
    $ts = $dbr->ct_fetch;
}

My query returns exactly one value. Thats why I'm reading into one variable.

I'm getting errors:

Open Client Message:

Message number:开发者_开发知识库 LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (163)

Message String: ct_results(): user api layer: external error: This routine cannot be called until all fetchable results have been completely processed.

Open Client Message:

Message number: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (159)

Message String: ct_cmd_drop(): user api layer: external error: This routine can be called only if the command structure is idle.

What's going wrong?

You can not call ct_fetch only once (as your code does), even if your SQL returns one row. You must call it in a loop till false.

brian d foy's approach seems to be the most consise solution (1 while (my @data = $dbh->ct_fetch); so I won't bother providing alternatives. What I will provide is the documentation showing why your code failed.

This behavior is documented in SyBooks docs for ct_fetch:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sdk_12.5.1.ctref/html/ctref/X65123.htm

If an application does not cancel a result set, it must completely process the result set by calling ct_fetch as long as ct_fetch continues to indicate that rows are available.

The simplest way to do this is in a loop that terminates when ct_fetch fails to return either CS_SUCCEED or CS_ROW_FAIL. After the loop terminates, an application can use a switch-type statement against ct_fetch’s final return code to find out what caused the termination.

If a result set contains zero rows, an application’s first ct_fetch call will return CS_END_DATA.

Note: An application must call ct_fetch in a loop even if a result set contains only a single row. An application must call ct_fetch until it fails to return either CS_SUCCEED or CS_ROW_FAIL.

My un-edicated guess as to the reason is that there's some "result set completed" flag set internally in CTLib to false and it doesn't get re-set to true till ct_fetch finds that no more rows are left in the result set (basically, what brian d foy said); and the rest of CTLib code checks that flag and errors out with error 163 when the flag is false.

I can't confirm that 100% without looking at actual CTLib source code, I wasn't able to find exact reason in the documentation

What happens when you call ct_fetch again? I don't use Sybase, but from looking at the docs it looks like the module expects you to make an additional call, at which point it realizes it has fetched all the data and shuts down whatever it is tracking internally.

while( $dbr->ct_results($restype) == CS_SUCCEED ) {
    if( $restype == CS_CMD_FAIL ) {
        warn "Update Check Failed...";
        next;
        }
    next unless $dbr->ct_fetchable($restype);
    $ts = $dbr->ct_fetch;
    1 while(my @data = $dbh->ct_fetch); # discard the rest
}