Calling stored procedures with multiple parameters

[justinosbaldiston]

First of all, thankyou so much for creating this library! It is the only pure go sybase library I could find

I am trying to call a Stored Procedure with multiple parameters, but it is returning a errors

_, err := c.db.ExecContext(ctx, exec my_proc ?, ?, param1, param2) returns the following response tds: Prepare failed: Msg: 7332, Level: 15, State: 1 Server: HORIZON, Procedure: gtds1, Line: 1: The untyped variable ? is allowed only in in a WHERE clause or the SET clause of an UPDATE statement or the VALUES list of an INSERT statement

If I take out the exec eg _, err := c.db.ExecContext(ctx, my_proc ?, ?, param1, param2) it returns this tds: Prepare failed: Msg: 102, Level: 15, State: 1 Server: HORIZON, Procedure: gtds1, Line: 1: Incorrect syntax near 'my_proc'

If I manually add the params it works fine eg _, err := c.db.ExecContext(ctx, exec my_proc 'param1', 'param2') However I need to be able to pass the params through

I have tested this using a perl library (https://metacpan.org/pod/DBD::Sybase#Stored-Procedures-and-Placeholders) against our Sybase server and that works fine so it seems to be an issue with the library?

[thda]

Hi, and thanks for reporting.

This one is difficult to fix. There are several ways to send prepared statements, one is to use dynamic prepared statements messages, which this driver uses, and it has the limitations you can read in your error message.

Another one is to use DBRPC or language messages. One must however give the type of the parameter as the server cannot guess it.

The mssql driver uses language messages and tries to deduce the parameters' type. However a good implementation would parse the statement, and transparently use dynamic messages when '?' is used, and use DBRPC when '\@param' is used.

This is quite complex as it requires writing a lexer for T-SQL or importing the one from mssql's driver.

I do not have the bandwidth to work on this right now. I'll try to revisit this a bit later and will keep you posted.

[nvx]

That explains the difference in behaviour. Is the type of the parameters required when the query is prepared, or when it is executed? Looking at the perl module they default to all params being SQL_CHAR by default but provide an option to explicitly specify type, but it looks like specifying the type happens after the prepare?

my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?");
$sth->bind_param(1, 'one', SQL_CHAR);
$sth->bind_param(2, 2.34, SQL_FLOAT);
$sth->execute;
....
$sth->execute('two', 3.456);
etc...

Also is @param style needed for DBRPC, or can ? style be used if appropriate type information is provided? Looking at the second example in the Perl module it looks like they were able to call a stored procedure with ? placeholders as well.

Thinking about how to implement this, if the type information is required at prepare time this would be a lot more difficult to support an explicit prepare, but should be doable with reflection when using Exec() methods since the parameters are known at that point. If it's only needed when it would be a bit more straightforward.

[thda]

I would need to capture network traffic with ? placeholders, and with named parameters to check the différence. As far as I know dbrpc and language messages require named parameters, so maybe dbd sybase does some parsing/rewrite. Will check the source code.

[thda]

The best solution is to lex the statement, I think. If it only contains an exec procedure, the we switch to dbrpc. If not then a regular dynamic statement will work.
Btw the type for rpc statements is needed only when sending parameters. I'll analyze some traffic and will provide some more information this weekend.

[thda]

Just checked the jconnect documentation. It looks like it replaces the placeholders by @p1...@pn and uses dbrpc or language tokens. I will try to mimic this behaviour and use reflection to determine the type and build the parameter message. A good implementation would only use reflection for the first batch of non-nil parameters, then cache the metadata