Java – Sybase stored procedure through JDBC

databasejavajdbcstored-proceduressybase-asa

This one has baffled me for a long time, so I am trying to get some help here 🙂

I am using JDBC to connect to an age old Sybase Adaptive server 6 (!!!)
I couldn't even find the JDBC drivers for it online, so I copied them from the installation directory 🙂

Now, inserting and querying and all the rest of the db operations work fine, but I encounter problems when calling a stored procedure. Let's start first with a snippet of code:

CallableStatement loginProcedure = connection.prepareCall("{call Login}");
loginProcedure.executeUpdate();

This is some normal procedure calling code. I must add that the Login procedure does not take any paramaters nor it outputs something back. The only thing that it does is to create a varibale in the db named AiCol. I will update this post soon with the procedure code as well.

When executing the above code I get the standard syntax exception:

com.sybase.jdbc.SybSQLException: ASA Error -131: Syntax error near 'Login'
    at com.sybase.tds.Tds.processEed(Tds.java)
    at com.sybase.tds.Tds.nextResult(Tds.java)
    at com.sybase.jdbc.ResultGetter.nextResult(ResultGetter.java)
    at com.sybase.jdbc.SybStatement.nextResult(SybStatement.java)
    at com.sybase.jdbc.SybStatement.executeLoop(SybStatement.java)
    at com.sybase.jdbc.SybCallableStatement.execute(SybCallableStatement.java)

Anyone encountered this problem before? It is my first time calling a stored procedure with no IN/OUT parameters, so I might be doing something wrong :/

Thanks in advance!
/ivo

Best Answer

Just a guess from the jConnect Documentation, but you may need to use a SybCallableStatement.

The demo uses parameters, but I would try calling it with executeUpdate.

I know back in the day, the Sybase standard JDBC support was lacking and they did things their own way for a while. You might want to see if you can track down an old jConnect Manual.

I also thought there was another Java Library at the time before jConnect. It's been a long time since ASE 6! 

import com.sybase.jdbcx.*;

....

// prepare the call for the stored procedure to execute as an RPC

String execRPC = "{call " + procName + " (?, ?)}";
SybCallableStatement scs = (SybCallableStatement) con.prepareCall(execRPC);

// set the values and name the parameters

// also (optional) register for any output parameters
scs.setString(1, "xyz");
scs.setParameterName(1, "@p3");
scs.setInt(2, 123);
scs.setParameterName(2, "@p1");

// execute the RPC
// may also process the results using getResultSet()
// and getMoreResults()

// see the samples for more information on processing results
ResultSet rs = scs.executeQuery();
Related Topic