Difficulties with JDBC package

New to Julia
,
1

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

using JDBC

JDBC.usedriver("ojdbc8.jar")
JDBC.init()
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)
    println(rows)
end

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
Stacktrace:
 [1] top-level scope at none:0

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

2

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
end

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))
2 Likes
3

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?

Thanks!

4

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.

5

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?

6

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")
         rs.fetchall()
       end

julia> cur.close()

julia> con.close()
1 Like
7

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.

2 Likes