# Difficulties with JDBC package

**URL:** <https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091>\
**Category:** New to Julia\
**Tags:** question, package\
**Created:** [April 12, 2019, 6:36pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091 "2019-04-12T18:36:39Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![alphasig329](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alphasig329/32/9794_2.png) [@alphasig329](https://discourse.julialang.org/u/alphasig329)\
**Post date:** [April 12, 2019, 6:36pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/1 "2019-04-12T18:36:39Z")

</div>

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

```julia
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](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.

```julia
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
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

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [April 12, 2019, 7:32pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/2 "2019-04-12T19:32:26Z")

</div>

As for your connection, could you try doing

```julia
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

```julia
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

```julia
JDBC.load(DataFrame, csr)

```

That should give you a full DataFrame.

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

```julia
using JDBC: coltypes, colnames

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

```

---

<div class="post-metadata">

**Author:** ![alphasig329](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alphasig329/32/9794_2.png) [@alphasig329](https://discourse.julialang.org/u/alphasig329)\
**Post date:** [April 12, 2019, 8:20pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/3 "2019-04-12T20:20:13Z")

</div>

> [@ExpandingMan](#):
>
> JDBC.load(DataFrame, 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?

Thanks!

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [April 12, 2019, 8:36pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/4 "2019-04-12T20:36:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Palli](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/palli/32/3380_2.png) [@Palli](https://discourse.julialang.org/u/Palli)\
**Post date:** [April 12, 2019, 9:55pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/5 "2019-04-12T21:55:45Z")

</div>

> [@ExpandingMan](#):
>
> PyCall.jl which is almost seamless (will be almost totally so if they ever get to overloading `getproperty` )

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 (](https://github.com/JuliaPy/PyCall.jl/commit/2a7bb25e19c08806fbf2c806d65dfcb8dafcd3e4)[#651](https://github.com/JuliaPy/PyCall.jl/pull/651)[)](https://github.com/JuliaPy/PyCall.jl/commit/2a7bb25e19c08806fbf2c806d65dfcb8dafcd3e4)

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?

---

<div class="post-metadata">

**Author:** ![tk3369](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tk3369/32/2824_2.png) [@tk3369](https://discourse.julialang.org/u/tk3369)\
**Post date:** [April 14, 2019, 5:41pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/6 "2019-04-14T17:41:40Z")

</div>

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
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()

```

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [April 15, 2019, 1:30pm UTC](https://discourse.julialang.org/t/difficulties-with-jdbc-package/23091/7 "2019-04-15T13:30:12Z")

</div>

> [@Palli](#):
>
> 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 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.