Thread: proposal for CallableStatements to handle multiple out parms
I'm in the process of writing a patch to get the driver to handle multiple out parms. One mechanism is to create a composite type and a function such as: create type Numeric_Proc_RetType as(it1 numeric(30,15),it2 numeric(30,15),it3 numeric(30,15));"); create function Numeric_Proc(numeric(30,15),numeric(30,15), numeric(30,15)) returns Numeric_Proc_RetType as 'declare work_ret record; begin select * into work_ret from Numeric_Tab; return work_ret; end;' language 'plpgsql'; Are there any problems with this ? Dave -- Dave Cramer 519 939 0336 ICQ # 14675561
On Fri, 18 Jun 2004, Dave Cramer wrote:
> I'm in the process of writing a patch to get the driver to handle
> multiple out parms.
>
> One mechanism is to create a composite type and a function such as:
>
> create type Numeric_Proc_RetType as(it1 numeric(30,15),it2
> numeric(30,15),it3 numeric(30,15));");
>
> create function Numeric_Proc(numeric(30,15),numeric(30,15),
> numeric(30,15)) returns Numeric_Proc_RetType as 'declare work_ret
> record; begin select * into work_ret from Numeric_Tab; return work_ret;
> end;' language 'plpgsql';
>
To expand on this the key part of the plan is that a CallableStatement
like "{ call Numeric_Proc(?,?,?)}" where all three arguments are
registered as output parameters would be transformed by the driver into:
SELECT f.it1, f.it2, f.it3 FROM (SELECT Numeric_Proc(1,2.2,3)) f;
The key is using the intermediate composite type to hold multiple outputs
so they can be extracted by the select. The interface is reasonably clear
from a Java perspective, but rather awkward for a backend function writer.
Some of the implementaion issues involved are:
1) How can you determine the correct column names to use with the
transformed SELECT statement. This will require some metadata lookup
which could be especially tricky given our ability to have overloaded
functions.
2) Are CallableStatements with only one output parameter supposed to
return a simple value or a composite type of only one column? Does it
make a difference if called like "{ call f(?)}" or "{?= call f()}"?
3) Now that composite types have full backend support we need to make sure
this doesn't conflict with using them as input or output types in
CallableStatements.
Kris Jurka
Dave Cramer wrote: > I'm in the process of writing a patch to get the driver to handle > multiple out parms. > > One mechanism is to create a composite type and a function such as: > > create type Numeric_Proc_RetType as(it1 numeric(30,15),it2 > numeric(30,15),it3 numeric(30,15));"); > > create function Numeric_Proc(numeric(30,15),numeric(30,15), > numeric(30,15)) returns Numeric_Proc_RetType as 'declare work_ret > record; begin select * into work_ret from Numeric_Tab; return work_ret; > end;' language 'plpgsql'; > > > Are there any problems with this ? Could you describe the driver side of your proposal? -O
See Kris's addition. At this point however I've done some work and realized that most of the parsing, binding and rewriting of the queries will need to be encapsulated in two classes, one for regular statements, and one for callable statements. This will allow me to totally encapsulate the logic for this inside the CallableStatement implementations, instead of mucking up the current AbstractJdbc1Statement code. Dave On Fri, 2004-06-18 at 19:10, Oliver Jowett wrote: > Dave Cramer wrote: > > I'm in the process of writing a patch to get the driver to handle > > multiple out parms. > > > > One mechanism is to create a composite type and a function such as: > > > > create type Numeric_Proc_RetType as(it1 numeric(30,15),it2 > > numeric(30,15),it3 numeric(30,15));"); > > > > create function Numeric_Proc(numeric(30,15),numeric(30,15), > > numeric(30,15)) returns Numeric_Proc_RetType as 'declare work_ret > > record; begin select * into work_ret from Numeric_Tab; return work_ret; > > end;' language 'plpgsql'; > > > > > > Are there any problems with this ? > > Could you describe the driver side of your proposal? > > -O > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > > !DSPAM:40d3774e134381905984059! > > -- Dave Cramer 519 939 0336 ICQ # 14675561
Dave Cramer wrote: > See Kris's addition. > > At this point however I've done some work and realized that most of the > parsing, binding and rewriting of the queries will need to be > encapsulated in two classes, one for regular statements, and one for > callable statements. This will allow me to totally encapsulate the logic > for this inside the CallableStatement implementations, instead of > mucking up the current AbstractJdbc1Statement code. You might want to look at my pending patch first -- I've overhauled modifyJdbcCall and added a method that translates between user-provided parameter indexes and actual query parameter indexes that sounds like it'd be useful for this case. -O
I'd like to move all of that stuff into it's own class, as the rest of my patch ends up with alot of if( instanceof CallableStatement) which I'd like to avoid by putting it all in a CallableStatement implementation. Thoughts? --dc-- On Fri, 2004-06-18 at 20:21, Oliver Jowett wrote: > Dave Cramer wrote: > > See Kris's addition. > > > > At this point however I've done some work and realized that most of the > > parsing, binding and rewriting of the queries will need to be > > encapsulated in two classes, one for regular statements, and one for > > callable statements. This will allow me to totally encapsulate the logic > > for this inside the CallableStatement implementations, instead of > > mucking up the current AbstractJdbc1Statement code. > > You might want to look at my pending patch first -- I've overhauled > modifyJdbcCall and added a method that translates between user-provided > parameter indexes and actual query parameter indexes that sounds like > it'd be useful for this case. > > -O > > > > !DSPAM:40d386ac236762102998748! > > -- Dave Cramer 519 939 0336 ICQ # 14675561
Dave Cramer wrote:
> I'd like to move all of that stuff into it's own class, as the rest of
> my patch ends up with alot of if( instanceof CallableStatement) which
> I'd like to avoid by putting it all in a CallableStatement
> implementation.
If I understand what you're suggesting, you want to do this:
AbstractJdbc1Statement
-> Jdbc1Statement extends AbstractJdbc1Statement
-> AbstractJdbc1CallableStatement extends AbstractJdbc1Statement
-> Jdbc1CallableStatement extends AbstractJdbc1CallableStatement
-> AbstractJdbc2Statement extends AbstractJdbc1Statement
The problem is then that you can't reasonably implement
AbstractJdbc2CallableStatement: it needs to extend both
AbstractJdbc2Statement (to get the extra JDBC2 Statement methods) and
AbstractJdbc1CallableStatement (to get the CallableStatement
functionality) simultaneously, and Java doesn't let you do mixins or
multiple inheritance..
A strategy-object approach would work, though. I'm not sure if it would
be much of a win over the current approach though -- you still need all
the CallableStatement methods in AbstractJdbc1Statement, they just turn
into hooks that call the strategy object's implementation.
Have you looked at my patch? It localizes most of the conditionalization
of the CallableStatement case into one place (there is an
adjustParameterIndex() method that does the index translation; it's an
identity transform if the statement isn't a CallableStatement).
I'd also prefer an explicit field on the statement to say "this
statement is callable!" rather than relying on instanceof, it always
struck me as too fragile to rely on exactly which classes the concrete
JdbcN classes implement.
-O
No, What I was planning on doing is encapsulating all the code that parses the sql, and rewrites it into a small class, StatementSqlParser implement SqlParser, then changing the contstructor for AbstractJdbc1Statment to AbstractJdbc1Statement( Connection, SqlParser ) Then I could create a second class CallableSqlParser which would deal with the differences in CallableStatements. Dave On Fri, 2004-06-18 at 21:00, Oliver Jowett wrote: > Dave Cramer wrote: > > I'd like to move all of that stuff into it's own class, as the rest of > > my patch ends up with alot of if( instanceof CallableStatement) which > > I'd like to avoid by putting it all in a CallableStatement > > implementation. > > If I understand what you're suggesting, you want to do this: > > AbstractJdbc1Statement > -> Jdbc1Statement extends AbstractJdbc1Statement > -> AbstractJdbc1CallableStatement extends AbstractJdbc1Statement > -> Jdbc1CallableStatement extends AbstractJdbc1CallableStatement > -> AbstractJdbc2Statement extends AbstractJdbc1Statement > > The problem is then that you can't reasonably implement > AbstractJdbc2CallableStatement: it needs to extend both > AbstractJdbc2Statement (to get the extra JDBC2 Statement methods) and > AbstractJdbc1CallableStatement (to get the CallableStatement > functionality) simultaneously, and Java doesn't let you do mixins or > multiple inheritance.. > > A strategy-object approach would work, though. I'm not sure if it would > be much of a win over the current approach though -- you still need all > the CallableStatement methods in AbstractJdbc1Statement, they just turn > into hooks that call the strategy object's implementation. > > Have you looked at my patch? It localizes most of the conditionalization > of the CallableStatement case into one place (there is an > adjustParameterIndex() method that does the index translation; it's an > identity transform if the statement isn't a CallableStatement). > > I'd also prefer an explicit field on the statement to say "this > statement is callable!" rather than relying on instanceof, it always > struck me as too fragile to rely on exactly which classes the concrete > JdbcN classes implement. > > -O > > > > !DSPAM:40d38fcd300218619219928! > > -- Dave Cramer 519 939 0336 ICQ # 14675561
Dave Cramer wrote: > No, > > What I was planning on doing is encapsulating all the code that parses > the sql, and rewrites it into a small class, StatementSqlParser > implement SqlParser, then changing the contstructor for > AbstractJdbc1Statment to > > AbstractJdbc1Statement( Connection, SqlParser ) > > Then I could create a second class CallableSqlParser which would deal > with the differences in CallableStatements. So essentially the SqlParser is a strategy object, or maybe strategy + some state? Which object holds what state? -O
Oliver, It is more of a Strategy object. As you pointed out I have to use a strategy object. I'm not interested in "winning"; I'm more interested in implementing this in such a way as to not muck up the current code, and add CallableStatement functionality in such a way that if, in the future postgres supports multiple out parms, it will be easy to change. Dave On Sun, 2004-06-20 at 10:17, Oliver Jowett wrote: > Dave Cramer wrote: > > No, > > > > What I was planning on doing is encapsulating all the code that parses > > the sql, and rewrites it into a small class, StatementSqlParser > > implement SqlParser, then changing the contstructor for > > AbstractJdbc1Statment to > > > > AbstractJdbc1Statement( Connection, SqlParser ) > > > > Then I could create a second class CallableSqlParser which would deal > > with the differences in CallableStatements. > > So essentially the SqlParser is a strategy object, or maybe strategy + > some state? Which object holds what state? > > -O > > > > !DSPAM:40d59c25234101391917376! > > -- Dave Cramer 519 939 0336 ICQ # 14675561