ODBC is now working for me with the documented interface (after updates).
Thanks! I do get a lot of chatter as it loads, mostly from the DecFP package.
The documentation doesn't mention how to shutdown. I used disconnect!(dsn)
after peeking at the code. Does it clean up the connection if you just exit
the julia session without disconnect?
Also, in this sequence
1. datatable = ODBC.query(dsn, "show databases")
# convert result to a DataFrame for additional data manipulation functionality
2. df = DataFrame(datatable)
3. do stuff with df
at what point is it safe to disconnect?
Is the odbc interface in general, or particularly with postgresql, thread safe?
On a more general topic....
I got lots of notices my packages were out of date, even immediately after I
installed them. What's that about? E.g.,
julia> Pkg.add("Cairo")
INFO: Nothing to be done
INFO: METADATA is out-of-date — you may not have the latest version of Cairo
INFO: Use 'Pkg.update()' to get the latest versions of your packages
julia> Pkg.update()
INFO: Updating METADATA...
INFO: Updating DBI...
INFO: Updating PostgreSQL...
INFO: Updating Postgres...
INFO: Computing changes...
INFO: No packages to install, update or remove
Ross
From: [email protected] [[email protected]] on behalf of Jacob Quinn
[[email protected]]
Sent: Wednesday, May 04, 2016 7:47 AM
To: [email protected]
Subject: Re: [julia-users] Can't add PostgreSQL
Hey Ross,
It actually sounds like you're still on the older release of ODBC (you can
check by running Pkg.installed()). To get the latest version (that matches the
current documentation), you'll have to run:
Pkg.update()
Then restart Julia to make sure you have the latest code.
-Jacob
On Tue, May 3, 2016 at 9:58 PM, Boylan, Ross
<[email protected]> wrote:
I got it working before your update. Yay! Thank you!
There were a couple of issues, mostly documentation, leaving aside getting ODBC
setup outside of julia.
The docs (the help at
https://github.com/JuliaDB/ODBC.jl) say to use DSN to get things going. As far
as I can tell, it doesn't exist. I used connect.
The docs mention a connection string, but provide no pointers to its content.
It would be nice to have one; the odbc packages I installed were almost
documentation free.
The docs imply the results of a query need to be turned into a DataFrame, but
it seems to come out as one.
The source code suggested one could get a dataframe with the output argument.
I'm not really sure what that's supposed to be or do, but when I tried
julia> raw = ODBC.query("SELECT isim, id, x, t, y, regular, censor, wait from
obs2619 where isim=1", conn, output=df )
ERROR: TypeError: typeassert: expected Union{AbstractString,DataType}, got
Function
I had not set df to anything before the call, AFAIK.
Ross
From:
[email protected] [[email protected]] on behalf
of Jacob Quinn [[email protected]]
Sent: Tuesday, May 03, 2016 7:57 PM
To:
[email protected]
Subject: Re: [julia-users] Can't add PostgreSQL
Hey Ross,
Just confirmed that connecting to Postgres does indeed work; haven't seen any
issues yet. I'm using the PostgreSQL ODBC Driver from their website with
unixODBC on a mac. I'm using a connection string I built by following the
guidelines here:
http://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
Here's a little sample code (I have more testing all the various data types in
Postgres, but I won't post the full snippet here):
using ODBC
dsn =
ODBC.DSN("Driver={PostgreSQL};Server=[server];Port=5432;Database=testdb;Uid=username;Pwd=password")
# Check some basic queries
dbs = ODBC.query(dsn, "SELECT datname FROM pg_database WHERE datistemplate =
false;")
data = ODBC.query(dsn, "SELECT table_schema,table_name FROM
information_schema.tables ORDER BY table_schema,table_name;")
The latest ODBC release was just merged in METADATA.jl, so if you do
Pkg.add("ODBC") you should get the latest release/code. If you already had it
installed, just run Pkg.update() to get the latest release.
Good luck!
-Jacob
On Tue, May 3, 2016 at 7:11 PM, Boylan, Ross
<[email protected]> wrote:
Attempting to post comments inline, even though my mailer is allergic. Look
for >>
From:
[email protected] [[email protected]] on behalf
of Jacob Quinn [[email protected]]
Sent: Tuesday, May 03, 2016 5:47 PM
I'm not sure I understand: I don't see DBI.jl nor PostgreSQL.jl at
pkg.julialang.org, which tells me they
aren't officially registered as packages. The difficulties you're seeing then
aren't that surprising because they haven't felt comfortable enough to
officially register.
>> You're right. I got to them through
https://github.com/JuliaDB
ODBC actually works fine on Linux/OSX through the unixODBC library. It can be
installed through a variety of means, but works quite well from what I've seen.
Postgres is actually next on my list to test with ODBC (so far the testing has
focused
on Teradata, SQL Server, and MySQL), so if I find time in the next few days,
I'll try to put together an example and share it here.
>> The status page indicates errors with julia 0.4 and Linux. Does it matter?
>> Clicking on the build logs it appears the most recent build for 0.4, 124.4
>> (https://travis-ci.org/JuliaDB/ODBC.jl)
succeeded, so I'm not sure why the problem is showing (also the previous error
looks like a problem with the configuration of the build machine rather than a
problem with the package).
>> No joy with the alternate Postgresl package;
https://github.com/NCarson/Postgres.jl/issues/3 has the details.
>> So I guess I'll give ODBC a whirl. Thanks for your help.
