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

Question

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?

Answer 1

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
}

Answer 2

You need to understand the basics of ClientLibrary⇠⇢Server communication: get your hands on DBISQL (it's on the Open Client/PC CD) and run that in order to get familiar with the interaction and the two buffers. Then it will be easier to understand, the concept in perl, of filling a command buffer [prepare()], then executing() it, which loads the ENTIRE result set into an array, previously bound to column_names, which you then navigate or peruse.

This (your post, your problem) looks like you have either a large result set which you have not finished getting from the server, or more probably, a cursor which is returning one-row-at-a-time.

If you are using a cursor, you need to close it.

If you want one value, do not use a cursor PLEASE, just get the single value via ordinary SELECT, and do().

Alternately, use a second db_handle.

Responses re comments

As other comments ahve confirmed, this is just simple result set handling on your side. There is no reason why CT-Lib should be expected to perform what anyone outside Sybase Engineering thinks. Users should avail themselves to how a product works, in order to use it.

(Love the way the stalker gives herself airs, suggesting checking proprietary source code, while at the same time appearing to be modest. Further evidence of abject dishonesty.)

Answer 3

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