Recursive Queries with JDBI

[balachandar sundaresan]

Hi ,

I am trying to execute a recursive query with SQLObject. However i am unable to bind a variable using @Bind annotation.

like

  @SqlQuery("select * from testrange where oid = :id")

  TestRange getRange(@Bind("id") long id);

The above query executes properly. At the same time when i try to do binding for a recursive query, i got the following error

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Unable to execute, no named parameter matches "id" and no positional param for place 0 (which

is 1 in the JDBC 'start at 1' scheme) has been set. [statement:

And at the end of the query statement in the error i could see the following message too

, arguments:{ positional:{}, named:{it:2}, finder:[]}]

should we need to do additionaly for executing recursive queries

Regards,

Bala

[Brian McCallister]

When you say "recursive query" what exactly do you mean? Can you paste in an example of the one that fails?

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Steven Schlansker]

There either is a typo, or you have not pasted the relevant code, because your parameter is named "it" in the error message but "id" in your @SqlQuery.

[balachandar sundaresan]

Hi ,

I can explain the problem with a simple example

We can use the query 

"WITH LINK(ID, NAME, LEVEL) AS (

    SELECT ID, NAME, 0 FROM FOLDER WHERE PARENT IS NULL
    UNION ALL
    SELECT FOLDER.ID, IFNULL(LINK.NAME || '/', '') || FOLDER.NAME, LEVEL + 1
    FROM LINK INNER JOIN FOLDER ON LINK.ID = FOLDER.PARENT
) 

SELECT NAME FROM LINK WHERE NAME IS NOT NULL ORDER BY ID;"

that is available in the URL :

http://www.h2database.com/html/advanced.html#recursive_queries

I have a DAO that has the following method

@SqlQuery("WITH LINK(ID, NAME, LEVEL) AS (SELECT ID, NAME, 0 FROM FOLDER WHERE PARENT IS NULL UNION ALL SELECT FOLDER.ID, IFNULL(LINK.NAME || '/', '') || FOLDER.NAME, LEVEL + 1 FROM LINK INNER JOIN FOLDER ON LINK.ID = FOLDER.PARENT AND LINK.ID < :id )SELECT NAME FROM LINK WHERE NAME IS NOT NULL ORDER BY ID;")

    List<Folder> getFolders(@Bind("id") long id);

i have got the corresponding FolderMapping class and POJO Folder

However the id not not bound to the sqlQuery object, 

that is when i call 

List<Folder> treeNodes = dao.getFolders(4);

the result is empty. However when i just replace the :id with constant value 4 in the query the same method call provide me the desired result

ie the bind value is not injected in to sqlquery object.

Your advice on resolving this issue will be very much helpful

Regards,

Bala

[Steven Schlansker]

Hi,

Unfortunately, I don't see anything obviously wrong with your code. I would suggest tracing through with a debugger until you find something unexpected, or if you can produce a runnable self contained test case someone on the list could track it down.

Best,
Steven

[balachandar sundaresan]

Hi ,

I would check it again. However due to time constraint, i have just proceeded with handle.createQuery. 

I would go through the same and lookout for issues when time permits. Or else as you mentioned i would try to get you test case.

Thanks,

Bala