jdbc connection to Sybase

Thread view:

Wayne Happ - 24 May 2007 16:56 GMT

Hi

I have an application that's sending the below SQL to a Sybase
database. Version 12.5

---
String proc is set to

exec p_setPERunType 1, 'CorpMarks', "set rowcount 100
select distinct symbol = rtrim(y.symbol) from yieldcurve y where
pe_run_type = 1 and exists(select * from yieldcurvecomponent yc where
y.symbol = yc.symbol) and exists(select * from gridpointhist h where
y.symbol = h.symbol) and industrycode != 'AG'", 'outfile.data', null,
'PECorpMarks', 'update', 'vladimirn', ''

Then I call

Statement deststmt = dest.createStatement();
deststmt.execute(proc);

---
The result is an error message that says

SQL Exception generated. The identifier that starts with '"set
rowcount 100
select dis' is too long. Maximum length is 28.

I'm new to JDBC, there's nothing wrong with the SQL itself, when I cut
and paste it into an isql session it executes just fine. Is there some
parameter Inbeed to set to make JDBC take the whole thing?

As a side note, I'm not the author of the SQL.

Reply to this Message

joeNOSPAM@BEA.com - 24 May 2007 18:54 GMT

Hi. The trouble is the double-quotes you're using to delineate the
long string parameter. The ISQL client will interpret that, but
JDBC just sends the string as-is. Better to use all single-quotes,
and escape the ones inside the big string. I believe the escape is
to just have an immediate second single-quote:

"exec p_setPERunType 1, 'CorpMarks', 'set rowcount 100
select distinct symbol = rtrim(y.symbol) from yieldcurve y where
pe_run_type = 1 and exists(select * from yieldcurvecomponent yc where
y.symbol = yc.symbol) and exists(select * from gridpointhist h where
y.symbol = h.symbol) and industrycode != ''AG''', 'outfile.data',
null,
'PECorpMarks', 'update', 'vladimirn', ''"

However, the good JDBC style for running stored procedures
would be with a PreparedStatement and parameters:

String jdbc_sql = "{ call setPERunType( ?, ?, ?, ?, ?, ?, ?, ?, ? )}";
PreparedStatement p = c.prepareStatement(jdbc_sql);
p.setInt(1, 1);
p.setString(2, "CorpMarks");

String big_arg = "set rowcount 100 select distinct symbol =
rtrim(y.symbol) from yieldcurve y where "
+ "pe_run_type = 1 and exists(select * from yieldcurvecomponent yc
where y.symbol = yc.symbol) and "
+ "exists(select * from gridpointhist h where y.symbol = h.symbol) and
industrycode != 'AG'";

p.setString(3, big_arg );
p.setString(4, "outfile.data");
p.setObject(5, null);
p.setString(6, "PECorpMarks");
p.setString(7, "update");
p.setstring(8, "vladimirn");
p.setString(9, "");

p.execute();

Joe Weinstein at BEA Systems

Reply to this Message

jdbc connection to Sybase

Free Magazines