That does look cool - thanks for that!
On Apr 11, 4:40 pm, Noel Grandin <[email protected]> wrote: > you could use something like this: > > 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 'connectbyprior' > > > 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 myTypesCONNECTBYPRIORname = 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.
