#18 Parameter Definitions for sp_cursoropen not correct?

[Jason Deabill]

Hi,

I've been trying to solve a problem I have (see
http://sourceforge.net/forum/forum.php?
thread_id=1122176&forum_id=104389) and may have
stumbled across a bug in the MSCursorResultSet class. I
am using 0.9 rc1.

Within the method 'private void cursorCreate(String sql,
String procName, ParamInfo[] procedureParams)' an
attempt is made to open a server cursor using
sp_opencursor. Paramters 6 of this procedure is the
parameter definitions which as set up using the following
code:

        // Parameter declarations
        for (int i = 0; ; ) {
            TdsData.getNativeType

(statement.connection,
procedureParams[i]);

            if (procedureParams[i].name == null) {
                buf.append("@P").append(i + 1);
            } else {
                buf.append(procedureParams[i].name);
            }

            buf.append(' ').append(procedureParams

[i].sqlType);

    // New line required here?

            if (++i == procedureParams.length) {
                break;
            }

            buf.append(',');
        }

        param[5] = new ParamInfo();
        param[5].isSet = true;
        param[5].jdbcType =

java.sql.Types.LONGVARCHAR;
param[5].value = buf.toString();
param[5].isUnicode = true;

I think this code does not define output parameters
correctly and something along the lines of the following
needs to be inserted where I have marked.

if (procedureParams[i].isOutput) { buf.append("
OUTPUT"); }

This is what seems to be giving me the error I
mentioned in my forum post 'Formal parameter '@P6' was
defined as OUTPUT but the actual parameter not
declared OUTPUT.'

I'll try and put together a sample table, sp and some
code to recreate this problem but I'm a bit short on
time :-)

[Jason Deabill]

Test Class

[Jason Deabill]

Test table (will require ~ 10 rows of data adding)

[Jason Deabill]

Test stored procedure

[Jason Deabill]

Logged In: YES
user_id=1097492

I have added a test class and sql scripts for a table and
stored procedure. The table will require populating with ~10
rows of data. Hope this helps. Thanks, Jason.

[Mike Hutchinson]

Logged In: YES
user_id=641437

The driver uses the sp_cursoropen function to actually create
the cursor result set. This function will accept either a select
statement or a stored procedure that contains just one select
statement. There is a minor bug in the driver in that the
parameter spec that it passes to the server looks like @P2
int when it should look like @P2 int OUTPUT.
Correcting this error will not actually allow your code to
succeed as it will then fail with the server
error java.sql.SQLException: A server cursor is not allowed
on a remote stored procedure or stored procedure with more
than one SELECT statement. Use a default result set or client
cursor.

I think that you will have to accept that server cursors are
incompatible with the stored procedure that you wish to use.
That being the case this is not a jTDS specific issue.

Mike.

[Brian Heineman]

Logged In: YES
user_id=716174

Jason,

It would appear that there are a number of problems
withcorrecting this bug. I am going to close the issue
since the current belief is that this cannot be fixed.
However, if you happen to discover some way of solving this
problem (or you find that this functionality works with
another driver), please let us know.

-Brian

[Jason Deabill]

Logged In: YES
user_id=1097492

Hi Brian,

Thanks for looking at this. The functionality does work with
the MS driver. Other than the suggestion above, which I see
has been tried, I'm stumped as to how to make it work. If I
get any bright ideas I'll let you know.

Thanks again,
Jason

[Brian Heineman]

Logged In: YES
user_id=716174

Jason,

I am re-opening this issue since the functionality works with
other drivers (namely the Microsoft driver). See:
http://sourceforge.net/forum/message.php?msg_id=2742113

Mike, Alin,

We may end up having to implement client side cursors to
solve this. However, it seems like we could also create a
temporary stored procedure wrapper that has the same
signature as the stored procedure being called minus the
output parameters. This "wrapper" could define variables for
the output parameters and execute the main stored
procedure. The output parameters could then be "select"ed
out of the wrapper to return the output parameters. I don't
have the time to try this right not, but it may be a possible
solution... thoughts?

-Brian

[Mike Hutchinson]

Logged In: YES
user_id=641437

Brian,

I am not sure that the wrapper idea will actually work in
practice, as it does not solve the basic problem that the
server will not let you build a scrollable result set from
anything other than a simple proc.

I think the client side scrollable resultset option is the most
promising idea and has the advantage of adding some real
value to users of Sybase. The other advantage is that, by
reading the entire result set, we can fix that other stored
procedure bugbear of not being able to access output
parameter values until the end.

I do have a simple prototype of a scrollable / updateable
result set. I did not include this code with the original rewrite
because I could not think of an efficient way of caching a
large result set to disk in the driver. The problem, as I see it,
is how to efficiently serialise the row data items to or from a
disk cache. I did think of reusing the TDS protocol for this but
in practice it is not very efficient for java objects. Maybe we
could just cache in memory to begin with.

For updateable result sets, the other problem was how to
work out the base table names so that one can format an
update or delete statement. When dealing with a simple
select statement we can use with browse to get the server
to tell us the table names but I am not sure how one can
determine the table name in a result set returned from a
stored procedure. I have an idea that this might be at the
root of the restrictions that the server places on using stored
procs in cursors.

I will tidy up the code a bit so that it works with the current
version of the driver and post it as a patch in the next few
days.

Mike.

[Alin Sinpalean]

Logged In: YES
user_id=564978

Guys,

I think this is more of a feature request than a bug, so I'm
moving it to the RFE list.

Alin.