Prepared Statement Not Being Properly Interpolated for PostgreSQL `reflect`

Hi @xitology and co!

I am in the process of making an all-in-one package to support connecting to a variety of databases. It is unregistered, but the draft is here: https://github.com/JuliaDatabases/DBConnector.jl

In short, I am running into a problem with the reflect statement for PostgreSQL when using a JDBC driver and JDBC.jl. I have a working example of the package here for SQLite:

Create a test environment and add the following packages:

pkg> add OMOPCDMCohortCreator
pkg> add HealthSampleData
pkg> add DBInterface
pkg> add DataFrames
pkg> add https://github.com/JuliaDatabases/DBConnector.jl

Download the SQLite and PostgreSQL JDBC drivers
1. SQLite: https://github.com/xerial/sqlite-jdbc/releases/tag/3.41.0.0
2. PostgreSQL: https://jdbc.postgresql.org
Run this test to see it work correctly:

using DBConnector, DataFrames, OMOPCDMCohortCreator, HealthSampleData

eunomia = HealthSampleData.Eunomia()

conn = DBConnector.DBConnection(connection_string = "jdbc:sqlite:$(eunomia)", driver_path = 
"path/to/sqlite-jdbc-3.41.0.0.jar", connectivity = "jdbc")

GenerateDatabaseDetails(:sqlite, "main")

GenerateTables(conn)

GetDatabasePersonIDs(conn)

As you can see, the above works perfectly for SQLite. However, when doing something similar for PostgreSQL, it fails. Sadly, I don't have a PostgreSQL DB set-up for reproducibility that you could use, but here is the script:

using DBConnector, DataFrames, OMOPCDMCohortCreator, HealthSampleData

eunomia = HealthSampleData.Eunomia()

conn = DBConnector.DBConnection(connection_string = "jdbc:postgresql:db/path", driver_path = 
"path/to/postgresql-jdbc.jar", connectivity = "jdbc")

GenerateDatabaseDetails(:postgresql, "synpuf5")

GenerateTables(conn)

And I get the following error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: bind message suppl
ies 0 parameters, but prepared statement "" requires 1
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va: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.executeQuery(PgStatement.java:236)
ERROR: JavaCall.JavaCallError("Error calling Java: org.postgresql.util.PSQLException: E
RROR: bind message supplies 0 parameters, but prepared statement \"\" requires 1")
Stacktrace:
  [1] geterror(allow::Bool)
    @ JavaCall ~/.julia/packages/JavaCall/MlduK/src/core.jl:418
  [2] geterror
    @ ~/.julia/packages/JavaCall/MlduK/src/core.jl:403 [inlined]
  [3] _jcall(obj::JavaCall.JavaObject{Symbol("java.sql.Statement")}, jmethodId::Ptr{Not
hing}, callmethod::Ptr{Nothing}, rettype::Type, argtypes::Tuple{DataType}, args::String
)
    @ JavaCall ~/.julia/packages/JavaCall/MlduK/src/core.jl:373
  [4] jcall(obj::JavaCall.JavaObject{Symbol("java.sql.Statement")}, method::String, ret
type::Type, argtypes::Tuple{DataType}, args::String)
    @ JavaCall ~/.julia/packages/JavaCall/MlduK/src/core.jl:245
  [5] executeQuery
    @ ~/.julia/packages/JDBC/2ruzk/src/JDBC.jl:146 [inlined]
  [6] #prepare#8
    @ ~/FOSS/DBConnector.jl/src/jdbc.jl:20 [inlined]
  [7] prepare
    @ ~/FOSS/DBConnector.jl/src/jdbc.jl:18 [inlined]
  [8] reflect(conn::JavaCall.JavaObject{Symbol("java.sql.Connection")}; schema::String,
 dialect::Symbol, cache::Int64)
    @ FunSQL ~/.julia/packages/FunSQL/Ufc3L/src/reflect.jl:89
  [9] GenerateTables(conn::JavaCall.JavaObject{Symbol("java.sql.Connection")}; inplace:
:Bool, exported::Bool)
    @ OMOPCDMCohortCreator ~/.julia/packages/OMOPCDMCohortCreator/0hUCQ/src/generators.
jl:168
 [10] GenerateTables(conn::JavaCall.JavaObject{Symbol("java.sql.Connection")})
    @ OMOPCDMCohortCreator ~/.julia/packages/OMOPCDMCohortCreator/0hUCQ/src/generators.
jl:166
 [11] top-level scope
    @ REPL[6]:1

Doing some more digging in the reflect source code, I discovered that for some reason here: https://github.com/MechanicalRabbit/FunSQL.jl/blob/2741b750118b2f21b5e2d2fae271f9bae9c148b9/src/reflect.jl#L17-L28

My assigned schema is not being interpolated correctly into the SQL block despite my prepare statement in DBConnector accepting the interpolation syntax. I have isolated the error to my extension of the function DBInterface.prepare in DBConnector as I can confirm that using my dispatch for DBInterface.connect and DBInterface.execute does work with code querying a PostgreSQL DB like DBInterface.execute(conn, "SELECT * FROM person LIMIT 1;") |> DataFrame

Here is the source code for my JDBC DBInterface.prepare dispatch (link here too: https://github.com/JuliaDatabases/DBConnector.jl/blob/main/src/jdbc.jl):

"""
Dispatch for JDBC interface to DBInterface `prepare` function
BUG: Doesn't seem to work for all JDBC versions yet
"""
function DBInterface.prepare(conn::JDBC.JavaObject{Symbol("java.sql.Connection")}, args...; kws...)
    stmt = JDBC.createStatement(conn)
    result = executeQuery(stmt, args...)
    return result
end

Any thoughts as to what I may be doing wrong?

Thanks!

MechanicalRabbit / FunSQL.jl

Prepared Statement Not Being Properly Interpolated for PostgreSQL `reflect` #41