Stored procedure via JDBC?

[Jack Woehr]

I tried some Sybase Java newsgroups but the looked pretty ancient ... so I ask here: Is it possible
to define a stored procedure via Sybase JDBC?

--
Jack J. Woehr # "[F]ar in the empty sky a solitary esophagus slept
http://www.well.com/~jax # upon motionless wing; everywhere brooded stillness,
http://www.softwoehr.com # serenity, and the peace of God." - Mark Twain

[Michael Peppler]

On Fri, 02 Apr 2004 13:17:31 -0800, Jack Woehr wrote:

> I tried some Sybase Java newsgroups but the looked pretty ancient ... so I
> ask here: Is it possible to define a stored procedure via Sybase JDBC?

Define the proc, or call it?

Defining it is just a question of sending the appropriate SQL to the
server - there shouldn't be anything special about that.

Calling procs - you can always call stored procs via normal language
commands. My java's pretty rusty, but you should be able to execute a
stored proc the same way that you'd run a normal SELECT.

There's probably also a Sybase specific API call to execute an RPC call,
equivalent to the C ct_command(CS_RPC_CMD)/ct_param() calls.

The news://forums.sybase.com/sybase.public.jConnect5 should have more
information for you, I think.

Michael
--
Michael Peppler Data Migrations, Inc.
mpep...@peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

[Jack Woehr]

Michael Peppler wrote:

> On Fri, 02 Apr 2004 13:17:31 -0800, Jack Woehr wrote:
>
> > I tried some Sybase Java newsgroups but the looked pretty ancient ... so I
> > ask here: Is it possible to define a stored procedure via Sybase JDBC?
>
> Define the proc, or call it?

Defining it. The b**** is that I have a procedure defined for ISQL and translating
it into something JDBC will accept is non-obvious.

[Michael Peppler]

On Mon, 05 Apr 2004 08:00:47 -0800, Jack Woehr wrote:

> Michael Peppler wrote:
>
>> On Fri, 02 Apr 2004 13:17:31 -0800, Jack Woehr wrote:
>>
>> > I tried some Sybase Java newsgroups but the looked pretty ancient ...
>> > so I ask here: Is it possible to define a stored procedure via Sybase
>> > JDBC?
>>
>> Define the proc, or call it?
>
> Defining it. The b**** is that I have a procedure defined for ISQL and
> translating it into something JDBC will accept is non-obvious.

I'm far from being a java expert, but wouldn't that just be

// Execute the desired DML statement and then call dispResultSet to
// display the rows and columns

Statement stmt = _con.createStatement();;
output("Executing: " + query + "\n");
ResultSet rs = stmt.executeQuery (query);

where "query" holds the text of the stored proc definition? (that's taken
from $SYBASE/jConnect-5_5/sample2/ExecuteQuery.java, btw)

[Jeff Tallman]

I'm still a bit confused about what is asked:

1) you have proc that you can call via isql and want to call it from
jdbc ala:

// standard JDBC call for proc - but pass a java object as param
CallableStatement cs = curConn.prepareCall("{call addStoreOrder (?)}");
cs.setObject(1, myBookSale);
cs.registerOutParameter(1,java.sql.Types.JAVA_OBJECT);
boolean b = cs.execute();

// JDBC requires that we skip all results before we can get the output
parameters.
while(true)
{
if (!b && cs.getUpdateCount() == -1) break;
b = cs.getMoreResults();
}

myBookSale = (BookSale)cs.getObject(1);
cs.close();

2) ..or, you have a proc you want to recreate in java load as class in
the server and make internal JDBC calls ala the UDF below (apologizing
in advance for the line wrap - if this is what you meant, let me know an
I can post with word doc):

// Data caching SQLJ example

import java.util.*;
import java.sql.*;
import java.lang.*;

class FGAC_SQLJ_Demo {
static String curLogin;
static TreeSet curTitleAccessList;
static HashMap masterTitleAuthorMap;
public static String debug_loc;

public static int init_TitleAuthor_chk () throws SQLException {

curLogin = " "; // initialize to an illegal user name
masterTitleAuthorMap = new HashMap();
curTitleAccessList = new TreeSet();
return 0;
}

public static boolean chk_TitleAuthor (String TitleID, String
LoginName) throws SQLException {

boolean rtnValue;
String title_id;

// Check to see if login is the same as the current login
if (LoginName.equals(curLogin))
{
rtnValue = curTitleAccessList.contains(TitleID);
}
else
{
// Since the login names weren't equal - the first thing is to set
the current login variable
curLogin = LoginName;

// Check to see if login name is in login map.
boolean hasLogin = masterTitleAuthorMap.containsKey(LoginName);
if (hasLogin)
{
// If so, we don't need to read a new collection - just locate the
existing one
curTitleAccessList = (TreeSet) masterTitleAuthorMap.get(LoginName);
rtnValue = curTitleAccessList.contains(TitleID);
}
else
{
// We need to construct a new collection
curTitleAccessList = new TreeSet();

// connect to the database

Connection conn = null;

try {
Class.forName("sybase.asejdbc.ASEDriver");
conn = DriverManager.getConnection("jdbc:default:connection");
}
catch (Exception e) {
System.err.println(e.getMessage() + ": error in connection" );
}

// submit query
try {
String theSQL = "select ta.title_id from login_auid l, titleauthor ta"
+ " where l.login_name = ? and l.au_id = ta.au_id"
+ " order by ta.title_id";
PreparedStatement stmt = conn.prepareStatement(theSQL);
//Statement stmt = conn.createStatement();
//stmt.setFetchSize(20);

stmt.setString(1, LoginName);
ResultSet rs = stmt.executeQuery(theSQL);

// populate collection from results
while (rs.next()) {
title_id=rs.getString("title_id");
curTitleAccessList.add(title_id);
}

stmt.close();
conn.close();
}
catch (SQLException e) {
System.err.println("SQLException: " + e.getErrorCode() +
e.getMessage());
}

// add map to masterTitleAuthorMap
masterTitleAuthorMap.put(LoginName,curTitleAccessList);

// now, finally check the list
rtnValue = curTitleAccessList.contains(TitleID);
}
}

return rtnValue;
}

public static int remove_TitleAuthor (String LoginName) throws
SQLException {

boolean hasLogin = masterTitleAuthorMap.containsKey(LoginName);
if (hasLogin)
{
masterTitleAuthorMap.remove(LoginName);
curLogin = "@@@@@"; // initialize to an illegal user name
curTitleAccessList = new TreeSet();
}

return 0;
}

public static String where_was_I () throws SQLException {
return debug_loc;
}

}

-- SQLJ Function definitions:

use pubs2
go

sp_unbindrule "salesdetail.title_id", null, "accessrule"
go

--public static int init_TitleAuthor_chk () throws SQLException {}
create function init_TitleAuthor_chk () returns int
language java
parameter style java
external name 'FGAC_SQLJ_Demo.init_TitleAuthor_chk()'
go

--public static boolean chk_TitleAuthor (String TitleID, String
LoginName) throws SQLException {}
create function chk_TitleAuthor (TitleID varchar(30), LoginName
varchar(255)) returns bit
returns null on null input
language java
parameter style java
external name 'FGAC_SQLJ_Demo.chk_TitleAuthor(java.lang.String,
java.lang.String)'
go

-- public static int remove_TitleAuthor (String LoginName) throws
SQLException {}
create function remove_TitleAuthor (LoginName varchar(30)) returns int
returns null on null input
language java
parameter style java
external name 'FGAC_SQLJ_Demo.remove_TitleAuthor(java.lang.String)'
go

--public static String where_was_I () throws SQLException {}
create function where_was_I () returns varchar(500)
language java
parameter style java
external name 'FGAC_SQLJ_Demo.where_was_I()'
go

--public static boolean chk_TitleAuthor2 (String TitleID, String
LoginName) throws SQLException {}
create function chk_TitleAuthor2 (TitleID varchar(30), LoginName
varchar(255)) returns bit
returns null on null input
language java
parameter style java
external name 'FGAC_SQLJ_Demo2.chk_TitleAuthor2(java.lang.String,
java.lang.String)'
go

[Jack Woehr]

Jeff Tallman wrote:

> 2) ..or, you have a proc you want to recreate in java load as class in
> the server and make internal JDBC calls ala the UDF below (apologizing
> in advance for the line wrap - if this is what you meant, let me know an
> I can post with word doc):

I want to use client JDBC to create a classic stored procedure on the server. Currently
I am piping text to ISQL to create the procedure. I want to use JDBC instead from
the client.

I was using the Sql task in ANT (http://ant.apache.org) in conjunction with the jconn2.jar
drivers from Adaptive Server Enterprise/12.5/B/Linux Intel/Linux 2.2.14-5.0smp i686/main/1444/32bit/OPT/Wed Nov 1 00:59:53 2000

Ant failed on the following line:

select @querystring = "alter table " + @tmptablename + " drop constraint " + @constraintname

which works okay in ISQL. So today I wrote a java class to use JDBC directly. It fails the same way:

com.sybase.jdbc2.jdbc.SybSQLException: Invalid column name ' drop constraint '.

at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2796)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2126)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1683)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1666)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:430)
at SybStoredProc.doSQL(SybStoredProc.java:107)
at SybStoredProc.dropAddProcedure(SybStoredProc.java:125)
at SybStoredProc.do_it(SybStoredProc.java:152)
at SybStoredProc.main(SybStoredProc.java:181)

I conclude that this is a bug in the Sybase jconn2.jar unless my syntax is illegal and ISQL is accepting it anyway.

Thanks for posting in response to my query.