About statement IDs, dynamic SQL and parameter Metadata

[newf]

I'm trying to use execution of dynamic SQL feature and providing my
own metadata parameter adapter for oracle jdbc driver..
After some code diggng several questions arised:
1. Should statement ID (Symbol type) be unique? Or it must be?
2. If it should be unique across all statements, it might be a problem
for dynamic SQLs.
3. MetadataAdapter.procedureMetadata, which i need to override for
realizing custom MetadataAdapter, recieves id: Symbol, cs:
CallableStatement. CallableStatement i can't use for getting parameter
metadata because of oracle jdbc driver restriction. And when using
Symbol with dynamic SQL, i fall into question 2.
4. procedureMetadata should return some data based on index of
parameter. It's good if i can use CallableStatement, but if i need to
provide custom parameter description it's better to operate on param
names (because it's orbroker's task to map names to param indexes)
5. Maybe better solution is to pass param info with statement's Token
object (when user constructs Token)? And MetadataAdapter will retrieve
param metadata from Token object if it exists or will take it from
CallableStatement in the other case...

Thank you,
Nicholas

[Nils Kilden-Pedersen]

On Mon, Jan 24, 2011 at 1:54 PM, newf <nust...@gmail.com> wrote:

 I'm trying to use execution of dynamic SQL feature and providing my
own metadata parameter adapter for oracle jdbc driver..
After some code diggng several questions arised:
1. Should statement ID (Symbol type) be unique? Or it must be?

The Symbol id should be unique per statement. It is what distinguishes the statements.

2. If it should be unique across all statements, it might be a problem
for dynamic SQLs.

Stored procedure calls cannot be dynamic, if that's what you are trying to do.

 

3. MetadataAdapter.procedureMetadata, which i need to override for
realizing custom MetadataAdapter, recieves id: Symbol, cs:
CallableStatement. CallableStatement i can't use for getting parameter
metadata because of oracle jdbc driver restriction.

You first need to figure out if you can even do what you're trying to do in plain JDBC. I cannot work around incomplete JDBC implementations. If you can do it in JDBC, then let me know and I can work from that.

 

And when using
Symbol with dynamic SQL, i fall into question 2.

Again, I assume this is a problem with dynamic stored procedure calls, which cannot be supported.

 

4. procedureMetadata should return some data based on index of
parameter. It's good if i can use CallableStatement, but if i need to
provide custom parameter description it's better to operate on param
names (because it's orbroker's task to map names to param indexes)

I can provide a mapping of parameter name to index or vice versa. Let me know your desired implementation of MetadataAdapter.

 

5. Maybe better solution is to pass param info with statement's Token
object (when user constructs Token)?

That may be an option. What should "param info" look like?

 

[Nicholas Ustinov]

2011/1/24 Nils Kilden-Pedersen <nil...@gmail.com>:

> On Mon, Jan 24, 2011 at 1:54 PM, newf <nust...@gmail.com> wrote:
>>
> The Symbol id should be unique per statement. It is what distinguishes the
> statements.

Hmm.. ok. So what should i pass when using on-fly created SQLs?

> Stored procedure calls cannot be dynamic, if that's what you are trying to
> do.

Arrrgh.!) Yes, that's possible. Can i overcome it in some way? maybe
via providing custom Registrant?

> You first need to figure out if you can even do what you're trying to do in
> plain JDBC. I cannot work around incomplete JDBC implementations. If you can
> do it in JDBC, then let me know and I can work from that.

I understand)

> I can provide a mapping of parameter name to index or vice versa. Let me
> know your desired implementation of MetadataAdapter.
>
>>
>> 5. Maybe better solution is to pass param info with statement's Token
>> object (when user constructs Token)?
>
> That may be an option. What should "param info" look like?

I propose to add paramInfo to Token object (which should consist of
paramName, SQL type and paramMode - IN or OUT), and modify
DefaultMetadataAdapter for using this info if it's presented for some
of parameters

[Nils Kilden-Pedersen]

On Mon, Jan 24, 2011 at 2:21 PM, Nicholas Ustinov <nust...@gmail.com> wrote:

2011/1/24 Nils Kilden-Pedersen <nil...@gmail.com>:

> On Mon, Jan 24, 2011 at 1:54 PM, newf <nust...@gmail.com> wrote:
>>

> The Symbol id should be unique per statement. It is what distinguishes the
> statements.

Hmm.. ok. So what should i pass when using on-fly created SQLs?

You can't, they are named <anonymous>.

 

> Stored procedure calls cannot be dynamic, if that's what you are trying to
> do.

Arrrgh.!) Yes, that's possible. Can i overcome it in some way? maybe
via providing custom Registrant?

I doubt it. I don't even understand the use case. Why are dynamic stored procedure calls needed? Stored procedures are by definition well-defined.

 

I propose to add paramInfo to Token object (which should consist of

paramName,  SQL type and paramMode - IN or OUT), and modify
DefaultMetadataAdapter for using this info if it's presented for some
of parameters

Hmm, I will think about it, but I don't see how that's different in any substantive way compared to what you can already do with MetadataAdapter. Your own metadata adapter could just take a map of id->paramInfo, no?

[Nicholas Ustinov]

> I doubt it. I don't even understand the use case. Why are dynamic stored
> procedure calls needed? Stored procedures are by definition well-defined.

It can be useful in two situations: the first, if there are a lot of
autogenerated procedures in DB (or autogenerated with changed body
manually later), where procedure name deferred from, for example,
table name and some action (insertRecord, updateRecord, deleteRecord,
selectOneRow, lockRecord, AfterEdit etc.)
Usually this is specific for legacy systems, originally developed for
2-tier architecture.
The second case, for example, if procedure descriptions (maybe not
only procedures, but plain select & modify SQLs too) are stored in
special tables in DB (maybe because of design, maybe because of legacy
system too), so it's really different user-specific registrant.
In fact, these use cases i saw. Maybe there are more, i don't know

> Hmm, I will think about it, but I don't see how that's different in any
> substantive way compared to what you can already do with MetadataAdapter.
> Your own metadata adapter could just take a map of id->paramInfo, no?

It's just more convenient and not depended on statement ID.
Also some inconvenience appears due to unclear transformations between
names and indexes.
So let you decide - just give a way to calculate indexes by name and
vice versa, or include param info in Token object.

Thank you,
Nicholas

[newf]

i think param name to index mapping will be useful anycase. One of
possible ways to overcome oracle's jdbc driver limitations is to
include info about parameter mode and type to param name.

[Nils Kilden-Pedersen]

I made the change this morning (not committed yet), and since I already have the parameter names in an IndexedSeq, that's what I'm passing. The implementer can then transform it to a Map[String, Int] if that's easier.