Just a suggestion. I wrote a cross-database repository (supporting DB2 UDB,
DB2 iSeries, Oracle 8i/9i, and MSSQL 2k by using the following design:

a- A super class (called the "StdRepository") which had 90% of the code in
it. It had statics defined for all the stored procedure calls in the form of
"protected static String GETREPORTS", and an initializer that would
initialize them like: GETREPORTS = "{call GETREPORTS(?)}";

b- For MSSQL and DB2, I could return resultsets with no problem like this.
But for Oracle, I needed to have them be stored functions, not stored
procedures. So, the overridden initializer for Oracle used GETREPORTS="{? =
call GETREPORTS(?)}";

c- With Oracle, you have to register the output parameter before you submit
the call. So, the superclass defined a method called setupStmt looks like
this:

protected void setupStmt(CallableStatement stmt) throws SQLException{
// do nothing...
}

In Oracle though, it looks like this:

protected void setupStmt(CallableStatement stmt) throws SQLException{
stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
}

d- This works fine, except that the parameter numbers differ between the two
calls. This was mitigated by a variable called offset. The offset was 1 for
Oracle, and 0 for MSSQL/DB2. So, the StdRepository was able to bind
parameters using x+offset notation.

d- Getting the resultset is also different between these because in Oracle.
So, the superclass defined a processQuery method as follows:

protected ResultSet processQuery(CallableStatement stmt)
throws SQLException{
// Simply return executeQuery in the super class. This is
// the default behavior.
return stmt.executeQuery();
}

In Oracle though, it looks like this:

protected ResultSet processQuery(CallableStatement stmt) throws
SQLException{
// Returns the output parameter which is the resultset.
stmt.execute();
return (ResultSet)stmt.getObject(1);
}


To put this all together, the code in stdrepository would look like this for
any particular call:


public Collection getGetReports(String aParm) throws SQLException{
Collection rtn = null;
Connection con = Repository.getConnection();
try {
CallableStatement stmt = con.prepareCall(GETREPORTS);
try {
setupStmt(stmt);
stmt.setString(1+offset, aParm);
ResultSet rs = processQuery(stmt);
try {
rtn = new ArrayList();
long tmpInt;
while (rs.next()) {
tmpInt = rs.getInt(1);
rtn.add(new Integer(tmpInt));
}
} finally {
rs.close();
}
} finally {
stmt.close();
}
} finally {
con.close();
}
return rtn;
}

My subclasses for DB2 were necessary because of how blobs get handled. My
Oracle subclass has some blob handling stuff in it as well.

But the bottom line - my StdRepository has 4500+ line of code in it. The
Oracle-specific subclass has only 140 lines of code, and the DB2-specific
subclass has only 210 lines of code.

This is easily adaptable to what you want to do with regards to Firebird, or
any other database.

I hope this helps.

Marc

> -----Original Message-----
> From: Uno Engborg [mailto:uno@...]
> Sent: Monday, December 20, 2004 11:42 AM
> To: Firebird-Java@yahoogroups.com
> Subject: Re: [Firebird-Java] setSelectableProcedure(true)
>
>
>
> Roman Rokytskyy wrote:
>
> >>So, is there any way to get a multirow ResultSet from a non
> >>selectable procedure?
> >>
> >>
> >
> >No. You can make non-selectable procedure selectable by
> including a SUSPEND;
> >command at the end of procedure body.
> >
> >
> I suspected that. This means that multiple rows from a
> CallableStatement
> can't be achieved using standard JDBC API.
> Or am I still missing something?
>
> The reason I asked, is that I'm writing an application that
> is supposed
> to work using various database engines on the
> back end side.
>
> As stored procedures triggers etc varies a lot from one
> database vendor
> to the other, I won't have much choice but writing separate
> SQL/procedure stuff for each database engine, that much is
> clear. But I
> was hoping to be able to keep the java code unchanged regardless of
> database back end. After all, that is the purpose of the JDBC
> abstraction layer. If that doesn't
> work we end up in a PHP-like situation where each database
> was handled
> in a way of its own.
>
> That's why I was suggesting that this behavior should be
> controlled by
> some property accessed from standard JDBC API
> e.g. as a property set when making the connection or when
> setting up a
> DataSource connection factory. E.g. it could be hooked on to the
> database url as a property. That way it would be possible for
> Firebird
> returning multiple rows without using nonstandard JDBC calls
> and still
> be backward compatible.
>
> As you say, setting such selectable property to true, would mean that
> calls to non selectable procedures would return empty
> ResultSets. But couldn't that be fixed by a wrapping them in a
> selectable procedure. Or people could use two connections
> with different selectability settings. This is not good
> solutions but,
> it would probably be better than the current state where you have to
> write non JDBC code to get this behavior. It also makes it
> hard for web
> designers using tools like dreamweaver
> to get working pages from FireBird stored procedures.
>
>
> Regards
> Uno Engborg
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
> Now with Pop-Up Blocker. Get it for free!
> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/saFolB/TM
> --------------------------------------------------------------
> ------~->
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>