ODBC help

[lukegr]

Hello,

I hope this is the correct place for this post. I very new to julia and have been asked to help with ODBC connectivity to our Microsoft SQL database. I was able to get the correct packages installed and I am able to use ODBC.connect() to connect to our SQL db. 

I seem to be doing something wrong with ODBC.query().

Please let me know what other information would be helpful.

Thanks again for any help with this.

Luke

[lukegr@ ~]$ julia

Warning: redefining constant DL_LOAD_PATH

               _

   _       _ _(_)_     |  A fresh approach to technical computing

  (_)     | (_) (_)    |  Documentation: http://docs.julialang.org

   _ _   _| |_  __ _   |  Type "help()" to list help topics

  | | | | | | |/ _` |  |

  | | |_| | | | (_| |  |  Version 0.2.0-prerelease+3437

_/ |\__'_|_|_|\__'_|  |  Commit d227ddc* 2013-08-25 17:22:51 UTC

|__/                   |  x86_64-redhat-linux

 

julia> using DataFrames
Warning: static parameter R does not occur in signature for PooledDataArray at /home/lukegr/.julia/DataFrames/src/pooleddataarray.jl:112.
The method will not be callable.

Warning: static parameter O does not occur in signature for DFPerm at /home/lukegr/.julia/DataFrames/src/dataframe.jl:1638.
The method will not be callable.

Warning: static parameter T does not occur in signature for eltype at /home/lukegr/.julia/DataFrames/src/indexing.jl:255.
The method will not be callable.

 julia> using ODBC

julia> ODBC.connect("xxx",usr="xxx",pwd="xxx")

Connection 1 to xxx successful.

julia> ODBC.query("select top 50 * from tablename")
ERROR: invalid Array dimensions
in Array at base.jl:149
in ODBCFetch at /home/lukegr/.julia/ODBC/src/backend.jl:89
in query at /home/lukegr/.julia/ODBC/src/userfacing.jl:49
in query#g17 at no file

[Jacob Quinn]

I think I actually ran into this the other day as well, but didn't dig into it.

Can you run the same query string with the `querymeta()` function and post what returns?

e.g.

querymeta("select top 50 * from tablename")

Thanks.

-Jacob

[lukegr]

Jacob - Here are the results. Thank you for the quick reply.

Luke

julia> querymeta("select top 50 * from scorereport")

[ODBC] 08S01: [Microsoft][ODBC Driver 11 for SQL Server]Communication link failure

ERROR: [ODBC]: SQLExecDirect failed; Return Code: SQL_DEFAULT_ERROR

 in error at error.jl:21

 in ODBCQueryExecute at /home/gracie/.julia/ODBC/src/backend.jl:41

 in querymeta at /home/gracie/.julia/ODBC/src/userfacing.jl:68

 in querymeta#g19 at no file

julia> conn

ODBC Connection Object

----------------------

Connection Data Source: lgsql2012

lgsql2012 Connection Number: 1

Connection pointer: Ptr{Void} @0x000000000503bae0

Statement pointer: Ptr{Void} @0x0000000004e434c0

Contains resultset? No

[Jacob Quinn]

Hmmm.......if you reconnect and try to run it again what happens? It seems like your connection is going stale.

-Jacob

[lukegr]

Jacob - That did it. If I close everything out and go back in I get successful results with TOP 50 or TOP 100. I did this previously and got successful results followed by failed results. 

If I do a select * - I get an invalid Array dimensions. My result set should only be 2314 records. querymeta() works for both SQL statements.

Can I change the time a connection is held onto?

Luke

[Jacob Quinn]

I don't know the nitty-gritty of every DBMS out there, but I believe the connection timeout is a parameter you can set with your connection if you use the `advancedconnect()` function. (See the documentation here: https://github.com/karbarcca/ODBC.jl  particularly the link mentioned to a great website for creating connection strings to specific DBMS).

Can you get querymeta() to run successfully and post the results here? That would help me in tracking down the intermittent bug.

-Jacob

[lukegr]

Yes, your documentation has help me a lot already. Thank you!

Here is the querymeta() for the SQL statement that throws an "invalid Array dimensions." I removed the column names - if you need them I can email them to you.

julia> querymeta("Select * from scorereport where st = 'other' and mnid > 600")

Resultset metadata for executed query

------------------------------------

Columns: 37

Rows: -1

Column Names: ["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37"]

Column Types: [("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_TYPE_DATE",91),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3),("SQL_DECIMAL",3)]

Column Sizes: [10,15,10,10,50,10,50,10,10,50,10,10,30,30,10,30,5,20,10,10,10,10,10,50,18,18,9,9,18,18,18,18,18,18,18,18,18]

Column Digits: [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,2,5,5,2,2,2,2,2,2,2,2,2]

Column Nullable: [0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]

Query: Select * from scorereport where st = 'other' and mnid > 600

julia>