Difficulties with JDBC package

With help from the community I was able to connect to my Oracle database through JDBC drivers using the following code.

using JDBC

oracle_connection = JDBC.DriverManager.getConnection("jdbc:oracle:thin:@HOST_NAME:PORT/SERVICE_NAME”, Dict(“user” => “USERNAME”, “password” => “PASSWORD”))

I want to run query through Julia and collect results into a JuliaDB or DataFrame object. Following JDBC’s documentation and examples from Julia 1.0 Programming Cookbook (https://github.com/PacktPublishing/Julia-1.0-Programming-Cookbook/blob/master/Chapter09/01.%20Working%20with%20databases%20in%20Julia/commands.txt), I can successfully from the following code snippets.

oracle_statement = JDBC.createStatement(oracle_connection) # Specify SQL Connection
oracle_query = executeQuery(oracle_statement, "select * from schema.datatable") # Specify SQL Query

for rows in JDBCRowIterator(oracle_query)

However, I do not know to collect the printed results in the loop into an object. Any assistance on this would be tremendous! I also tried to create a cursor using JDBC, but get the following error.

julia> oracle_cursor = JDBC.cursor(oracle_connection)
ERROR: MethodError: no method matching cursor(::JavaCall.JavaObject{Symbol("java.sql.Connection")})
Closest candidates are:
  cursor(::JDBC.Connection) at C:\Users\jmbyars\.julia\packages\JDBC\gUKZV\src\interface.jl:117
  cursor(::AbstractString; props, connectorpath) at C:\Users\jmbyars\.julia\packages\JDBC\gUKZV\src\interface.jl:119
 [1] top-level scope at none:0

Am I doing something wrong when trying to link Julia and Oracle? Thanks

As for your connection, could you try doing

oracle_connection = JDBC.Connection("jdbc:oracle:thin:@HOST_NAME:PORT/SERVICE_NAME", props=Dict(“user” => “USERNAME”, “password” => “PASSWORD”))

This should give you a nice Julia object whereas DriverManager.getConnection gives you the most minimal wrapping of a JavaCall (the Julia Java interface) object.

You can than do something like the following, see the JDBC readme

cnxn = JDBC.Connection("jdbc:derby:test/juliatest") # create connection
csr = cursor(cnxn) # create cursor from connection

# if you don't need access to the connection you can create the cursor directly
csr = cursor("jdbc:derby:test/juliatest")

# execute some SQL
execute!(csr, "insert into pi_table (pi_value) values (3.14);")
execute!(csr, "select * from my_table;")

# to iterate over rows
for row ∈ rows(csr)
    # do stuff with row

close(csr)  # closes Connection, can be called on Connection or Cursor

As for constructing a DataFrame, I had written a DataStreams interface for JDBC which I believe should still work even though I believe it’s deprecated in favor of Tables.jl. Try doing

JDBC.load(DataFrame, csr)

That should give you a full DataFrame.

Alternatively, you can construct a DataFrame by pushing to columns:

using JDBC: coltypes, colnames

df = DataFrame(coltypes(JDBC.Source(csr)), colnames(JDBC.Source(csr)))
foreach(r -> push!(df, r), rows(csr))

This worked, thank you. I swear that ran code similar to your suggestions and was connecting getting connection issues. I did not include the props = argument in the JDBC.Connection which may have been the problem.

I do occasionally get JavaCall errors and exceptions that are not necessarily constant. Is the JavaCall brittle on occassion?


In my experience JavaCall itself is pretty reliable. It has the virtue of being extremely simple, but this can also be disadvantage: it’s nowhere near as nice to work with as, for example PyCall.jl which is almost seamless (will be almost totally so if they ever get to overloading getproperty). I don’t recommend using JavaCall directly if you can avoid it. Not because it’s unstable, but simply because it is such a minimal wrapper that it’s unpleasant to work with.

Feel free to provide specific errors either here in discourse or in the JavaCall issues.

I thought we got get/setproperty 3 months ago, then on master, then more recently in 1.90.0 version, and a newer version tagged yesterday with e.g.:

Make PyCall.jl AOT-compilable (#651)

I’ve not looked much into JavaCall and JDBC with (assumed both just work); would some Python package for (non-JDBC) db access be helpful, if not?

I have had a successful production implementation using PyCall.jl with cx_Oracle. Maybe you can give that a try. Using the latest PyCall syntax, running queries is fairly straightforward e.g.

julia> using PyCall

julia> cxo = pyimport("cx_Oracle")

julia> con = cxo.connect("user/pass@server")

julia> cur = con.cursor()

julia> let rs = cur.execute("select * from schema.table_name")

julia> cur.close()

julia> con.close()
1 Like

I don’t think you should avoid JDBC. If you are having a specific problem, please open an issue.

That said, SQLAlchemy with PyCall probably works fine as well.