Hi,
I'm not sure if this recursive statement would work in H2 as is. In H2,
such statements are quite limited (see the documentation). If the problem
is really only that the parameters are swapped, then you could use
parameter indexes as in
select ?2, ?1 from dual;
The second parameter will be bound to the first bind variable.
Regards,
Thomas
On Wednesday, April 11, 2012, Noel Grandin wrote:
> you could use something like this:
>
> http://www.javaworld.com/**javaworld/jw-04-2007/jw-04-**jdbc.html<http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html>
>
> On 2012-04-11 17:17, Colin McCormack wrote:
>
>> Hi,
>>
>> I have a prepared statement in oracle using several '?' params - the
>> oracle statement uses 'connect by prior'
>>
>> I have created a H2 compliant version of this oracle statement using
>> 'WITH LINK' recursive statement.
>>
>> However due to how WITH LINK works, i.e. with the 'with link' command
>> before the select, when plugging the preparing the statement in plain
>> old jdbc code - the parameters are swapped essentially.
>>
>> Oracle statement:
>>
>> SELECT Id FROM myObjects
>> WHERE status='C' AND ParentId=?
>> AND type IN
>> (SELECT name FROM myTypes CONNECT BY PRIOR name = extends START WITH
>> name=?)
>>
>> H2 statement:
>>
>> WITH LINK( name, extends, LEVEL ) AS (
>> SELECT name, extends, 0
>> FROM myTypes where name = ?
>> UNION ALL
>> SELECT myTypes.name, myTypes.extends, LEVEL + 1
>> FROM LINK INNER JOIN myTypes ON LINK.name = myTypes.extends
>> )
>> SELECT O.ID FROM myObjects O
>> JOIN LINK L ON O.type = L.name
>> WHERE O.status='C' AND O.ParentId=?
>>
>> So because 'parentid' at bind position 1 is and int in oracle and
>> 'name' at bind position 2 is a varchar - in my java jdbc code i bind
>> appropriately...
>>
>> But using my H2 statement:
>> 'parentid' is at bind position 2 and 'name' is at bind position 1
>>
>> Resultant error as expected using my h2 statement was a 'data
>> conversion' error.
>>
>> I don't want to have to refactor all my existing oracle statements to
>> align with my new H2 statements. Can i write 'WITH LINK' statements
>> any other way???
>>
>>
>>
>> Alternatively i could:
>> - re-factor my oracle statements
>> - somehow use parameter names (but can't do this in plain jdbc i
>> think...)
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at http://groups.google.com/**
> group/h2-database?hl=en <http://groups.google.com/group/h2-database?hl=en>
> .
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
