Prepared Statement Not Being Properly Interpolated for PostgreSQL `reflect`

[TheCedarPrince]

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:

1. 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

2. Download the SQLite and PostgreSQL JDBC drivers
3. SQLite: https://github.com/xerial/sqlite-jdbc/releases/tag/3.41.0.0
4. PostgreSQL: https://jdbc.postgresql.org
5. 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:

FunSQL.jl/src/reflect.jl

Lines 17 to 28 in 2741b75

	const postgresql_reflect_clause =
	FROM(:n => (:pg_catalog, :pg_namespace)) |>
	JOIN(:c => (:pg_catalog, :pg_class), on = FUN("=", (:n, :oid), (:c, :relnamespace))) |>
	JOIN(:a => (:pg_catalog, :pg_attribute), on = FUN("=", (:c, :oid), (:a, :attrelid))) |>
	WHERE(FUN(:and, FUN("=", (:n, :nspname), FUN(:coalesce, VAR(:schema), "public")),
	FUN(:in, (:c, :relkind), "r", "v"),
	FUN(">", (:a, :attnum), 0),
	FUN(:not, (:a, :attisdropped)))) |>
	ORDER((:n, :nspname), (:c, :relname), (:a, :attnum)) |>
	SELECT(:schema => (:n, :nspname),
	:name => (:c, :relname),
	:column => (:a, :attname))

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!

[xitology]

When you connect to PostgreSQL using JDBC (or ODBC) rather than using the standard LibPQ driver, you need to customize the dialect object. This is because JDBC expects query parameters to be represented as ? while the standard PostgreSQL expects parameters to be in the form $1, $2, and so on. Internally, JDBC driver rewrites the query into the standard PostgreSQL form before submitting it to the server.

To create a custom dialect in FunSQL, run:

const postgresql_jdbc_dialect = FunSQL.SQLDialect(:postgresql, variable_prefix = '?', variable_style = :positional)

Then use this object in place of :postgresql with all FunSQL functions that take a dialect parameter.

By the way, you will likely have the same problem with JDBC driver for SQLite as soon as you run any query with a parameter. The reason why your SQLite code haven't triggered an error is because SQLite does not support schemas, and therefore the reflect query does not have any parameters.