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.
