CODEWITHSUNDEEP
javapostgresql
Edwards Nick
Edwards Nick

Reputation: 95

Works well in pgadmin but doesn't work in java code

with recursive
Ancestor(a,d) as (select parent as a, child as d from ParentOf
union
select Ancestor.a , ParentOf.child as d 
from Ancestor, ParentOf
where Ancestor.d = ParentOf.parent)

enter image description here

Hi I ran the above code in pgadmin and it worked fine so I tried moving this to my java code the same way. But here it is called an error in the SQL syntax. What is the reason for this? I also put ; in the SQL statement, but the same error occurred.

stmt.executeUpdate("with recursive " +
                    "Ancestor(a,d) as (select parent as a, child as d from ParentOf " +
                    "union " +
                    "select Ancestor.a , ParentOf.child as d  " +
                    "from Ancestor, ParentOf " +
                    "where Ancestor.d = ParentOf.parent)");

Below is the error

Exception in thread "main" org.postgresql.util.PSQLException: error: syntax error, at the end of input
  Position: 185
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
    at SqlTest3.main(SqlTest3.java:44)

Upvotes: 0

Views: 168

Answers (1)

user330315
user330315

Reputation:

You have two errors. First, to run a query, you need to use executeQuery(). Second, your SQL string in Java doesn't contain the final SELECT statement, it only contains the CTE.

ResultSet rs = stmt.executeQuery(
         "with recursive "Ancestor(a,d) as (" + 
         "select parent as a, child as d from ParentOf " +
         "union " +
         "select Ancestor.a  ParentOf.child as d  " +
         "from Ancestor " +
         " join ParentOf on Ancestor.d = ParentOf.parent " + 
         ") "  + 
         "select * from ancestor"); // <<< this SELECT was missing

I also replaced your ancient and outdated implicit join in the where clause with a "modern" (over 25 years old) explicit JOIN operator which is the recommended way to write a join these days.

Upvotes: 3

Related Questions