juliadatabases/odbc.jl

An ODBC interface for the Julia programming language

odbc.jl's Issues

ODBC v0.3.0

Hey guys,

I'm wondering if I can call on some help for testing. I've been meaning to do an update for a while, particularly focusing on the resultset fetching process, and I just pushed a new branch that features drastically simpler code. For now, there aren't any feature enhancements, but I have a to do list to implement once this refresh has been tested a bit.

Anyway, if you'd be willing to help, run Pkg.checkout("ODBC","v0.3.0") in julia and that should bring in the new branch. Feel free to run queries, pulling in as many data types as you can and let me know if you run into any issues.

cc: @kmsquire @randyzwitch @rogerjbos @MarkCWirt

SQL Server TINYINT values imported wrong into DataFrame (ODBC 0.3.8 using Julia 0.3.0-prerelease)

Not sure about other databases, but I believe that TINYINT values coming from SQL Server really are Uint8 and not Int8 as this module appears to have mapped in "ODBC_Types.jl"

What this means in my testing is TINYINT values when querying SQL Server look like 183, for example, but Julia ODBC 0.3.8 imports this value as -73 (as well as exports to text file as -73 if including the optional output parameter when running query() )

However, simply changing the mapping in "ODBC_Types.jl" for SQL_TINYINT => Uint8 doesn't seem to work because all the internal plumbing in "backend.jl" appears to assume any Uint fields represent character codes and not actual literal "numbers". Also, just trying re-mapping SQL_TINYINT => Int16 just seems to make the converted TINYINT's even further wrong.

A workaround - probably not the right one - that seems to work here is to go ahead and consume the incoming Uint8 values from SQL Server, but tucked inside a custom type that Julia then treats and sees differently than pure Uint8. Then when it's time to turn around and fill the data frame and/or export to text file, convert/upsize it to Int16 (which can handle the positive 128-255 values without issue). It is, of course, "wasting space" upsizing the values from Uint8 to Int16 but does seem to work out and I now see a value of ,say, 183 in SQL Server query as 183 in Julia DataFrame (and/or exported text file) from the resultant query() call.

So, following the model somewhat for how you already handle your custom SQLDate type:

1. Define new type in "ODBC_Types.jl":
   note: two custom convert() methods also seem to need being defined here:

immutable SQLTinyInt
    value::Uint8
end
string(x::SQLTinyInt) = "$(x.value)"
convert(::Type{Int16}, x::SQLTinyInt) = int16(x.value)
convert(::Type{SQLTinyInt}, x::Int16) = SQLTinyInt(x)

2. Alter the "SQL2Julia" assignment in "ODBC_Types.jl" to this custom type
   SQL_TINYINT => SQLTinyInt

3. Add an extra ODBCAllocate() method accounting for this parameter type in "backend.jl"
   ODBCAllocate(x::Array{SQLTinyInt,1},y) = Array(Int16,y)

4. Add an extra ODBCClean() method accounting for this parameter type in "backend.jl"
   ODBCClean(x::Array{SQLTinyInt,1},y,z) = int16(x[y].value)

5. Lastly, add an extra ODBCCopy!() method also in "backend.jl"

function ODBCCopy!{D<:Int16}(dest::Array{D},dsto,src::Array{SQLTinyInt},n,ind,nas)
    for i = 1:n
        nas[i+dsto-1] = ind[i] < 0
        dest[i+dsto-1] = int16(src[i].value)
    end
end

SQL Server Data Type Test Results

Very small table with small samples and here's what I see:

VARCHAR(n) .... works with workaround I mentioned in issue #51
VARCHAR(MAX) ... throws an out of bounds error
NTEXT .............. blows up my machine per Issue #53
Integer ............ good
BigInt ............... good
SmallInt ........... good
TinyInt ............. works with workaround I mentioned in Issue #52
Float ............... good
Decimal .......... good
Date ............... good
Time ,,,,,,,,,,,,,,, good
DateTime ........ good (makes it UTC rather than local, but can live with that)

query() documentation wrong about copying results to file ODBC 0.3.8 (both Julia 0.2 and 0.3-prerelease)

It took me a while to figure out (more like guess) that if you want to copy the query results to an external file then it's really query(querystring::ASCIIString, connection::Connection=conn, output::ASCIIString="PathToOutputFile.txt", delim::Char='\t') and not what the documentation here on the main page shows.

That is, the parameter called "file" needs to be called "output" . Also, if supplying an explicit connection parameter, looks like it needs to go after the querystring, not before

For example (on Windows, to copy results to pipe delimited file):

query("select * from dbo.MyTable", output="C:\\Temp\\test.txt", delim='|')

or

query("select * from dbo.MyTable", conn, output="C:\\Temp\\test.txt", delim='|')

p.s. even if you don't care about writing a copy of the results to file, the optional connection parameter looks like it's supposed to go second rather than first, like so::

query("select * from dbo.MyTable")

or

query("select * from dbo.MyTable", conn)

Over-allocation in large query resultsets

I'm still very new to julia and am not sure if the issue below should be filed under ODBC or DataFrame.

Issue: Returned DataFrame for queries with a result > 65535 rows actually contains an inflated number of rows.

Steps to reproduce:

1. Connect to database using ODBC.
2. Run a query that returns 65535 rows: df = query("select * from tbl_name limit 65535;")
3. Verify rows returned: assert(length(df[1]) == 65536)
4. Run a query that returns 65536 rows: df_bad = query("select * from tbl_name limit 65536;")
5. Run test that should fail: assert(length(df_bad[1] == 65536)

I verified the behavior on 64bit Windows 7 and 32bit Ubuntu 12.04. In both cases the returned DataFrame had 131070 rows.

Furthermore, continuing to increase the limit of the query will produce the 131070 row dataframe until the limit reaches 131071. At that point the dataframe size grows to 196605.

If you'd prefer for me to post these directly to the julia-users forum, I'd be happy to do that. I just figured that you'd be the guy that could pinpoint this issue best.

querymeta() function broken in ODBC.jl 0.3.8 with Julia 0.3.0-prerelease?

I get the following with any query I try with Julia 0.3.0-prerelease, but had no problems with Julia 0.02 as of last week:

julia> querymeta(qry)
Resultset metadata for executed query
------------------------------------
Query: SELECT MyVarChar, MyInt FROM dbo.ExampleDataColumns: 2
Rows: -1
WARNING: DataFrame(::Matrix, ::Vector)) is deprecated, use convert(DataFrame, Matrix) instead
 in depwarn at deprecated.jl:36
 in DataFrame at C:\Julia64\.julia\v0.3\DataFrames\src\deprecated.jl:54
 in show at C:\Julia64\.julia\v0.3\ODBC\src\ODBC.jl:37
Error showing value of type Metadata:
ERROR: no method Index(Array{ASCIIString,1})
 in DataFrame at C:\Julia64\.julia\v0.3\DataFrames\src\deprecated.jl:61
 in show at C:\Julia64\.julia\v0.3\ODBC\src\ODBC.jl:37

advancedconnect with failure typo evn -> env

Hello Jacob,

first of all: Thanks for ODBC.jl and other contributions to julia.

There is a typo in userfacing.lj function advancedconnect().

global ..., evn -> should be: global ..., env

https://github.com/quinnj/ODBC.jl/blob/master/src/userfacing.jl#L22

It's my first time github usage. Sorry for not providing a patch.

Kind regards, Oliver

MemoryError on Ubuntu with small query

When I try to connect to a large MySQL db, but doing a small query on a small table I get a MemoryError:

julia> Pkg.add("ODBC")
julia> using ODBC
julia> conn=ODBC.connect("markets")
ODBC Connection Object
----------------------
Connection Data Source: markets
markets Connection Number: 1
Contains resultset? No
julia> df=query("select * from markets_trace limit 1")
elapsed time: 0.042409949 seconds
1x7 DataFrame
|-------|-------------|-------------|---------|
| Col # | Name        | Eltype      | Missing |
| 1     | id          | Int32       | 0       |
| 2     | name        | UTF16String | 0       |
| 3     | description | UTF16String | 0       |
| 4     | code        | UTF16String | 0       |
| 5     | exchange_id | Int32       | 0       |
| 6     | type_id     | Int32       | 0       |
| 7     | testing     | Int8        | 0       |
julia> df=query("select * from markets_trace limit 5")
ERROR: MemoryError()
 in ODBCColumnAllocate at /home/samuel/.julia/v0.3/ODBC/src/backend.jl:107
 in ODBCBindCols at /home/samuel/.julia/v0.3/ODBC/src/backend.jl:90
 in query at /home/samuel/.julia/v0.3/ODBC/src/userfacing.jl:54
 in query at /home/samuel/.julia/v0.3/ODBC/src/userfacing.jl:43

The first query with limit 1 uses up 8gb of ram, the second even with that memory freed always crashes.

Calling the query from the prompt returned by sqli markets is absolutely fine.

System Details:

Ubuntu: 14.04 64bit
unixodbc: 2.2.14p2-5ubuntu5
libmyodbc: 5.1.10-3
julia: latest

sporadic errors

con = ODBC.connect("test")
res = query("select 1",con)

gives me random errors such as:

invalid UTF-8 sequence
while loading In[24], in expression starting on line 2

 in convert at utf8.jl:162
 in ODBCMetadata at  ..... \.julia\v0.3\ODBC\src\backend.jl:70

or

[ODBC] 42000: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '}'.
[ODBC] 42000: [Microsoft][SQL Server Native Client 10.0][SQL Server]'...' is an invalid name because it contains a NULL character or an invalid unicode character.
[ODBC] 42000: [Microsoft][SQL Server Native Client 10.0][SQL Server]Unclosed quotation mark after the character string '...'.
[ODBC]: SQLExecDirect failed; Return Code: SQL_DEFAULT_ERROR
while loading In[36], in expression starting on line 2

Sometimes it gives the expected result. Any ideas?
I am running julia v0.3.1.

UTF-16 problems

Hi, whenever I try to make a query, I get the following error:

julia> query("select * from table")
ERROR: no method unsafe_copy!(Ptr{UTF16String}, Ptr{Uint16}, Int64)
 in ODBCCopy! at /home/alessandro/.julia/ODBC/src/backend.jl:129
 in ODBCFetchDataFrame at /home/alessandro/.julia/ODBC/src/backend.jl:151
 in query at /home/alessandro/.julia/ODBC/src/userfacing.jl:57
 in query at /home/alessandro/.julia/ODBC/src/userfacing.jl:43

julia> versioninfo()
Julia Version 0.3.0-prerelease+741
Commit 941c6e3 (2013-12-30 07:22 UTC)
Platform Info:
  System: Linux (x86_64-linux-gnu)
  CPU: Intel(R) Core(TM) i3-2120 CPU @ 3.30GHz
  WORD_SIZE: 64
  BLAS: libopenblas (USE64BITINT DYNAMIC_ARCH NO_AFFINITY)
  LAPACK: libopenblas
  LIBM: libopenlibm

This is on 64-bit Ubuntu, and I'm using libmyodbc as a connector to a MySQL server. What am I missing?

ODBC.jl not working on MacOS

In the ODBC API.jl file, there is an assumption on OSX that the driver manager will be named "libiodbc.dylib"

On my system (with unixodbc installed through homebrew) this is not the case: the file is named libodbc.2.dylib.

With the wrong filename the following is returned when a connection attempt is made:

ERROR: [ODBC]: SQLConnect failed; Return Code: SQL_DEFAULT_ERROR
in ODBCConnect at /Users/mwirt/.julia/ODBC/src/backend.jl:23
in connect at /Users/mwirt/.julia/ODBC/src/userfacing.jl:9
in connect at /Users/mwirt/.julia/ODBC/src/userfacing.jl:20

When I hard-code the correct file name the connection works fine.

I don't know how would make things more flexible, but this may warrant a mention in the documentation.

Thanks!

ODBC issue with Julia studio on Windows

Hi can you find out whats the problem with this .I used to connect to ODBC but today i couldn't .
I have tried all the ways that i can do . I have reinstalled Julia even but this is not yet solved .

Here's what i have done

julia> using ODBC
LoadError("C:\Users\rajesh.julia\ODBC\src\ODBC.jl",3,LoadError("C:\Users\rajesh.julia\DataFrames\src\DataFrames.jl",5,ErrorException("Stats not found")))

julia> ODBC.connect(dsn; usr="rajesh", pwd="top@123")
ErrorException("dsn not defined")

julia> co = ODBC.connect("dsn",usr="rajesh",pwd="top@123")
ErrorException("function connect does not accept keyword arguments")

julia> ODBC.advancedconnect("DSN=dsn;UID=rajesh;PWD=top@123;")
ErrorException("advancedconnect not defined")

julia> advancedconnect("DSN=dsn;UID=rajesh;PWD=top@123;")
ErrorException("advancedconnect not defined")

ulia> Pkg.add("ODBC")
INFO: Nothing to be done.

julia> ODBC.connect(dsn)
ErrorException("dsn not defined")

sql"show tables" only gets the first letter of each table

Example:

julia> sql"show tables"
23x1 DataFrame:
         Tables_in_sample_analysis
[1,]                           "b"
[2,]                           "c"
[3,]                           "d"
[4,]                           "f"
[5,]                           "i"
[6,]                           "i"
[7,]                           "i"
...

This is on linux, using libodbc.so.2, with a mysql database.

Wrong "Rows Number" results return when call method "querymeta" connect Oracle with ODBC method,

Mysql do not have this issue, but Oracle exists. When I connect oracle database with ODBC, I call "query" method it always return error. I find the "querymeta" method always return "Rows: -1' when connect Oracle, but it works well when connect Mysql database.
And my computer environment is 32 bit, Line 174 rowsfetched = zeros(Int64,1), do not support my environment it I change it to "rowsfetched = zeros(Int32,1)" it works well. This module is very powpul. Thank you.

SQL Return Type Mapping

In backend.jl, the type SQL_REAL is mapped to an integer:

        elseif contains((SQL_REAL,SQL_INTEGER),type_value)
            ctype = SQL_C_LONG
            juliatype = Int

Using the Postgres ODBC drivers on Linux and Mac, this does not work, casting real values to integers and getting strange results. But if the mapping is changed thus:

        elseif contains((SQL_REAL,SQL_DECIMAL,SQL_NUMERIC,SQL_FLOAT,SQL_DOUBLE),type_value)
            ctype = SQL_C_DOUBLE
            juliatype = Float64

The results are as expected (although 0's sometimes come back not exactly zero, but around 1e-313).

Bug in ODBCDirectToFile() function (using ODBC 0.3.8 with Julia 0.3.0-prelease)

If trying to export to file like so:

query(qry, conn, output="C:\Temp\test.txt", delim='|')

you will get an error message:

ERROR: no method ODBCClean(Array{Uint8,2}, Int64)
in ODBCDirectToFile at C:\Julia64.julia\v0.3\ODBC\src\backend.jl:234

What appears to be happening behind the scenes when the above query() function (defined in "userfacing.jl") is called with the optional parameter "output" and a file path, is the query() function then employs the ODBCDirectToFile() function in "backend.jl" and inside this function there's a call to ODBCClean() function, with varying signatures, but none that match the signature of what is being called here.

So, what seems to fix it is supplying the length of the first parameter as the third parameter (currently missing in 0.3.8);

currently:
write(out_file,ODBCEscape(ODBCClean(columns[col],row)))

should be (?):
write(out_file,ODBCEscape(ODBCClean(columns[col],row,length(columns[col]))))

ALSO, when writing out from a SQL VARCHAR(n) field to file, it appends a bunch of trailing NUL ASCII character codes (\0) such that the entire string written out is n characters long. In other words, it doesn't "rtrim" the trailing empty characters. It looks like by wading through "ODBC_Types/jl" that SQL VARCHAR(n) fields are interally stored an array of uint8 character codes. So, to get rid of the trailing null character codes (really, to get rid of them all, which seems safe to do), if you were to change the following line in "backend.jl" then it exports as expected:

currently:
ODBCClean(x::Array{Uint8},y,z) = bytestring(x[1:z,y])

should be(?):
ODBCClean(x::Array{Uint8},y,z) = replace(bytestring(x[1:z,y]),r"\0","")

[PkgEval] ODBC may have a testing issue on Julia 0.3 (2014-10-02)

PackageEvaluator.jl is a script that runs nightly. It attempts to load all Julia packages and run their tests (if available) on both the stable version of Julia (0.3) and the nightly build of the unstable version (0.4). The results of this script are used to generate a package listing enhanced with testing results.

On Julia 0.3

• On 2014-10-01 the testing status was Tests pass.
• On 2014-10-02 the testing status changed to Package doesn't load.

Tests pass. means that PackageEvaluator found the tests for your package, executed them, and they all passed.

Package doesn't load. means that PackageEvaluator did not find tests for your package. Additionally, trying to load your package with using failed.

This issue was filed because your testing status became worse. No additional issues will be filed if your package remains in this state, and no issue will be filed if it improves. If you'd like to opt-out of these status-change messages, reply to this message saying you'd like to and @IainNZ will add an exception. If you'd like to discuss PackageEvaluator.jl please file an issue at the repository. For example, your package may be untestable on the test machine due to a dependency - an exception can be added.

Test log:

>>> 'Pkg.add("ODBC")' log
INFO: Cloning cache of ODBC from git://github.com/quinnj/ODBC.jl.git
INFO: Installing ArrayViews v0.4.6
INFO: Installing DataArrays v0.2.2
INFO: Installing DataFrames v0.5.9
INFO: Installing Dates v0.3.2
INFO: Installing GZip v0.2.13
INFO: Installing ODBC v0.3.9
INFO: Installing Reexport v0.0.1
INFO: Installing SortingAlgorithms v0.0.2
INFO: Installing StatsBase v0.6.6
INFO: Package database updated

>>> 'using ODBC' log
Julia Version 0.3.1
Commit c03f413* (2014-09-21 21:30 UTC)
Platform Info:
  System: Linux (x86_64-unknown-linux-gnu)
  CPU: Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz
  WORD_SIZE: 64
  BLAS: libopenblas (USE64BITINT DYNAMIC_ARCH NO_AFFINITY Sandybridge)
  LAPACK: libopenblas
  LIBM: libopenlibm
  LLVM: libLLVM-3.3

ERROR: syntax: extra token ")" after end of expression
 in include at ./boot.jl:245
 in include_from_node1 at ./loading.jl:128
 in include at ./boot.jl:245
 in include_from_node1 at ./loading.jl:128
 in reload_path at loading.jl:152
 in _require at loading.jl:67
 in require at loading.jl:51
 in include at ./boot.jl:245
 in include_from_node1 at loading.jl:128
 in process_options at ./client.jl:285
 in _start at ./client.jl:354
 in _start_3B_1712 at /home/idunning/julia03/usr/bin/../lib/julia/sys.so
while loading /home/idunning/pkgtest/.julia/v0.3/ODBC/src/backend.jl, in expression starting on line 129
while loading /home/idunning/pkgtest/.julia/v0.3/ODBC/src/ODBC.jl, in expression starting on line 92
while loading /home/idunning/pkgtest/.julia/v0.3/ODBC/testusing.jl, in expression starting on line 2

>>> test log
no tests to run
>>> end of log

NULL values create "weird" situation when exporting query to file

It looks like NULL values in database come over into DataFrame as NA fine, but if you try exporting to text file (i.e. query(qty, output="test.txt", delim='|') then wherever there were NULL values, they're replaced in the text file with whatever was last populated from a previous record.

For example,

123
456
NULL
NULL
ABC

gets exported like this:

123
456
456
456
ABC

unicode problem

I can't do queries with unicode characters, not sure if this is related to OCBC.jl, had this problem with SQLite too

julia> query("insert into foo values ('é')");
[ODBC] 42601: ERRO: erro de sintaxe no fim da entrada;
Error while executing the query
ERROR: [ODBC]: SQLExecDirect failed; Return Code: SQL_DEFAULT_ERROR
in error at error.jl:21
in ODBCQueryExecute at /home/paulo/.julia/ODBC/src/backend.jl:41
in query at /home/paulo/.julia/ODBC/src/userfacing.jl:47
in query at /home/paulo/.julia/ODBC/src/userfacing.jl:43

julia> query("insert into foo values ('e')");

advanceconnect fatal error

I've been having great success using DSNs to connect to MySQL and Postgres databases using ODBC.connect. However, when I try to do the equivalent connections using advancedconnect, I get the following errors (usually shutting down Julia completely):

Postgres (Amazon Redshift): this connection string worked once, but generally does the following

julia> test = advancedconnect("Driver={psqlODBC};ServerName=reporting.XXXXX.us-east-1.redshift.amazonaws.com;Username=XXXX;Password=XXXX;Database=XXXX;Port=5439"); 
env allocateddbc allocatedconnectedstmt allocatedSegmentation fault: 11

julia> test = advancedconnect("Driver={psqlODBC};ServerName=reporting.XXXXX.us-east-1.redshift.amazonaws.com;Username=XXXX;Password=XXXX;Database=XXXX;Port=5439"); 
env allocateddbc allocatedconnectedstmt allocatedconn madefatal: error thrown and no exception handler available.

MySQL

julia> advancedconnect("Driver={MySQL};user=root;server=localhost;database=airline;")
env allocateddbc allocatedSegmentation fault: 11

ArgumentError("UTF16String data must be NULL-terminated") with NVARCHAR

On Julia 0.3 connected to Microsoft SQL Server 2008r2
df = query("select cast('asdf' as nvarchar(50)) ")
gives ArgumentError("UTF16String data must be NULL-terminated")
while
df = query("select cast('asdf' as varchar(50)) ")
works.
Both used to work.

[PackageEvaluator.jl] Your package ODBC may have a testing issue.

This issue is being filed by a script, but if you reply, I will see it.

PackageEvaluator.jl is a script that runs nightly. It attempts to load all Julia packages and run their test (if available) on both the stable version of Julia (0.2) and the nightly build of the unstable version (0.3).

The results of this script are used to generate a package listing enhanced with testing results.

The status of this package, ODBC, on...

• Julia 0.2 is 'Package doesn't load.'
• Julia 0.3 is 'Tests pass.'

'No tests, but package loads.' can be due to their being no tests (you should write some if you can!) but can also be due to PackageEvaluator not being able to find your tests. Consider adding a test/runtests.jl file.

'Package doesn't load.' is the worst-case scenario. Sometimes this arises because your package doesn't have BinDeps support, or needs something that can't be installed with BinDeps. If this is the case for your package, please file an issue and an exception can be made so your package will not be tested.

This automatically filed issue is a one-off message. Starting soon, issues will only be filed when the testing status of your package changes in a negative direction (gets worse). If you'd like to opt-out of these status-change messages, reply to this message.

Querying a SQL Server NTEXT field hangs my machine (ODBC 0.3.8 with Julia 0.3.0-prerelease)

Even just querying a 5-row table via a contrived query such as "SELECT MyNTextField FROM dbo.TestTable" where the MyNtextField holds the small string of "Hello World" in each of the 5 records hangs my 64-bit, Windows 7 machine with 8GB RAM and I have to kill julia.exe from Task Manager (which takes about 5 minutes just to get into to kill the hung julia.exe process)

It looks to "immediately" consumes all available physical RAM and then promptly moves over to starting to gobble up additional virtual memory - even after killing Julia.exe, it looks like it takes about 3-4 minutes for my machine to calm down and get back to normal.

String quoting/escaping

This is more of a discussion point than a bug, and not sure if this isn't even a base Julia issue/characteristic.

In Postgres, you can have double-quoted strings around column names when the column name contains a non-standard character. In my database, I've got a bunch of hyphens in column names, because my data is transformed from a NoSQL database and the developers don't think about anything beyond closing their JIRA tickets :)

The solution is easy enough, but escaping double-quotes can get pretty hairy if your query is complicated. Is there any way to avoid needing to do string escaping?

julia> ua_df = query("select distinct coalesce("http_user-agent", ua) from leads;");
ERROR: @agent_str not defined

julia> ua_df = query("select distinct coalesce(\"http_user-agent\", ua) from leads;");

How do I get connection string back?

If you call advancedconnect() on Windows with no arguments, it pops up the ODBC manager and lets you pick a DSN. I am able to connect to my database just fine, but I'd like to be able to script my connection later on.

Is there a way to get the full connection string back? I've tried conn and Connections objects at the REPL, but all I get back are PTR references.

(@quinnj, I figured out my DSN issue which was leading me to ask this question in the first place, so this isn't a critical issue, just a nice-to-have in the future)

Query results containing Datetime/Timestamp errors

Attempting to return datetime or timestamp field results in errors.

In each case below "dt" is a Timestamp field. I initially tried with a Datetime field and it failed also.

julia> d1 = query("select dt from mytbl limit 5;")
ERROR: no method unsafe_copy!(Ptr{DateTime{ISOCalendar,Zone0}}, Ptr{SQLTimestamp}, Int64)
in ODBCCopy! at /home/bp/.julia/ODBC/src/backend.jl:129
in ODBCFetchDataFrame at /home/bp/.julia/ODBC/src/backend.jl:147
in query at /home/bp/.julia/ODBC/src/userfacing.jl:57
in query at /home/bp/.julia/ODBC/src/userfacing.jl:43

Date returned:

julia> d1 = query("select date(dt) from mytbl limit 5;")
ERROR: type does not have a native size
in sizeof at operators.jl:149
in ODBCCopy! at /home/bp/.julia/ODBC/src/backend.jl:129
in ODBCFetchDataFrame at /home/bp/.julia/ODBC/src/backend.jl:147
in query at /home/bp/.julia/ODBC/src/userfacing.jl:57
in query at /home/bp/.julia/ODBC/src/userfacing.jl:43

Time returned:

julia> d1 = query("select time(dt) from mytbl limit 5;")
elapsed time: 2.2847e-5 seconds
5x1 DataFrame:
time(dt)
[1,] "\x16\0\0\0\0\0"
[2,] "\0\0\0\x16\0\n"
[3,] "\x16\0\x0f\0\0\0"
[4,] "\0\0\0\0\0\0"
[5,] "\0\0\0\0\0\0"

julia> typeof(d1[1])
Array{UTF8String,1}

unixODBC not correctly loaded

This issue references this SO post. The gist is this: on my 64 bit Ubuntu 12.04 machine, I have unixODBC 2.3.2 set up and working via several non-Julia tools. ODBC.jl, however, fails to pull in my configured DSNs:

julia> ODBC.listdsns()
0x2 Array{String,2}

julia>ODBC.odbc_dm
"libodbc"

I've tested this against both Julia 0.20 and a 0.30 nightly. What should ODBC.odbc_dm return, if it's working correctly?

[PkgEval] ODBC may have a testing issue on Julia 0.3 (2014-07-14)

PackageEvaluator.jl is a script that runs nightly. It attempts to load all Julia packages and run their tests (if available) on both the stable version of Julia (0.2) and the nightly build of the unstable version (0.3). The results of this script are used to generate a package listing enhanced with testing results.

On Julia 0.3

• On 2014-07-12 the testing status was Tests pass.
• On 2014-07-14 the testing status changed to Package doesn't load.

Tests pass. means that PackageEvaluator found the tests for your package, executed them, and they all passed.

Package doesn't load. means that PackageEvaluator did not find tests for your package. Additionally, trying to load your package with using failed.

This issue was filed because your testing status became worse. No additional issues will be filed if your package remains in this state, and no issue will be filed if it improves. If you'd like to opt-out of these status-change messages, reply to this message saying you'd like to and @IainNZ will add an exception. If you'd like to discuss PackageEvaluator.jl please file an issue at the repository. For example, your package may be untestable on the test machine due to a dependency - an exception can be added.

Test log:

INFO: Installing ArrayViews v0.4.6
INFO: Installing DataArrays v0.1.12
INFO: Installing DataFrames v0.5.6
INFO: Installing Datetime v0.1.6
INFO: Installing GZip v0.2.13
INFO: Installing ODBC v0.3.8
INFO: Installing Reexport v0.0.1
INFO: Installing SortingAlgorithms v0.0.1
INFO: Installing StatsBase v0.5.3
INFO: Building Datetime
INFO: Package database updated
Warning: could not import Sort.sortby into DataFrames
Warning: could not import Sort.sortby! into DataFrames
ERROR: repl_show not defined
 in include at ./boot.jl:245
 in include_from_node1 at ./loading.jl:128
 in include at ./boot.jl:245
 in include_from_node1 at ./loading.jl:128
 in reload_path at loading.jl:152
 in _require at loading.jl:67
 in require at loading.jl:54
 in include at ./boot.jl:245
 in include_from_node1 at ./loading.jl:128
 in reload_path at loading.jl:152
 in _require at loading.jl:67
 in require at loading.jl:51
 in include at ./boot.jl:245
 in include_from_node1 at loading.jl:128
 in process_options at ./client.jl:285
 in _start at ./client.jl:354
while loading /home/idunning/pkgtest/.julia/v0.3/DataFrames/src/dataframe/reshape.jl, in expression starting on line 163
while loading /home/idunning/pkgtest/.julia/v0.3/DataFrames/src/DataFrames.jl, in expression starting on line 110
while loading /home/idunning/pkgtest/.julia/v0.3/ODBC/src/ODBC.jl, in expression starting on line 3
while loading /home/idunning/pkgtest/.julia/v0.3/ODBC/testusing.jl, in expression starting on line 1
INFO: Package database updated

Note this is possibly due to removal of deprecated functions in Julia 0.3-rc1: JuliaLang/julia#7609

bounds error in ODBCFetchDataFramePush!

Working from #46, I'm seeing a bounds error in ODBCCopy! called from ODBCFetchDataFramePush! here. I'm seeing that r = 2 while length(temp) == length(tempna) == 1. Still tracking how this is happening, but I thought I'd post an issue since it may be apparent to someone else.

Query only returns first character in string

I'm seeing a weird error when running a query, only the first character for each row is being saved. Here's a comparison between pandas results (correct) and ODBC.

accounts = pd.io.sql.read_frame("Select name from accounts", con)

In [14]: accounts.head()
Out[14]:
name
0    Lead Intel Number Two
1    Natalie Stopko Web Design and Illustration
2    undamped interactive, llc
3    Ross fake Company
4    All Web Leads

julia> query("select name from accounts")
988x1 DataFrame:
          name
[1,]       "d"
[2,]       "B"
[3,]       "M"
[4,]       "G"
[5,]       "A"

Both have the same number of rows in the query, so that part is working correctly.

Writing query to file appends one too many tabs

If I run query() with a tab delimiter, the file writes fine to disk. However, a tab is placed after the right-most column, then the CRLF line breaks are written. For a 4 column file, this implies 5 columns.

When I tried to load this to a database, it complains about "extra information" being contained in the file. Removing the trailing tab from the first couple of records allows me to load it in the database.

Timing on SQL query

Hello,

I'm using ODBC to connect to an MSSQL database and running a complex query. I used tic() and toc() to measure the elapsed time, which was ~3162 seconds, or approximately 52 minutes. This same query takes 50 seconds in R, using RODBC. I was wondering what could be causing the significant delay. The result set is 1.5 million rows and 23 columns... so not small, but not enormous. Perhaps I need to allocate more memory to the julia process itself?

ODBC query errors

I do not kown how to do. Thanks for your help.

julia> using ODBC

julia> conn = ODBC.connect("DSN1",usr="wangfeng",pwd="wangfeng")
ODBC Connection Object
Connection Data Source: DSN1
DSN1 Connection Number: 7
Connection pointer: Ptr{Void} @0x000000000bb47b10
Statement pointer: Ptr{Void} @0x000000000bb47e90
Contains resultset? No

julia> query(connection::Connection=conn,"SHOW TABLES;"; file=:DataFrame,delim='
\t')
ERROR: syntax: keyword argument is not a symbol: (:: connection Connection)

Can't disconnect, write to dataframe

I've been able to connect to a MySQL database on localhost and get results using querymeta

julia> using ODBC

julia> conn = ODBC.connect("MySQL")
Connection 1 to MySQL successful.
julia> querymeta("Select * from a1987";)
Resultset metadata for executed query
------------------------------------
Columns: 29
Rows: 1310811
Column Names: ["Year","Month","DayofMonth","DayOfWeek","DepTime","CRSDepTime","ArrTime","CRSArrTime","UniqueCarrier","FlightNum","TailNum","ActualElapsedTime","CRSElapsedTime","AirTime","ArrDelay","DepDelay","Origin","Dest","Distance","TaxiIn","TaxiOut","Cancelled","CancellationCode","Diverted","CarrierDelay","WeatherDelay","NASDelay","SecurityDelay","LateAircraftDelay"]
Column Types: [("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_INTEGER",4),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12),("SQL_VARCHAR",12)]
Column Sizes: [10,10,10,10,255,10,255,10,255,10,255,255,10,255,255,255,255,255,10,255,255,10,255,10,255,255,255,255,255]
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,0,0,0,0,0]
Column Nullable: [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 a1987

However, when I try to execute my query, I get the following error:

julia> df1987 = query("Select * from a1987";)
ERROR: OUTPUT_STREAM not defined
 in next! at /Users/randyzwitch/.julia/ProgressMeter/src/ProgressMeter.jl:27
 in ODBCFetch at /Users/randyzwitch/.julia/ODBC/src/backend.jl:128
 in query at /Users/randyzwitch/.julia/ODBC/src/userfacing.jl:54

Am I using the wrong syntax? I've also tried query("Select * from a1987";testdf), query("Select * from a1987"; file="/User/randyzwitch/output.txt",delim='\t') and query("Select * from a1987"; file="output.txt",delim='\t') without success.

Also, when I try to disconnect from the database, I get the following error:

julia> disconnect()
WARNING: delete!(a::Vector,x) is deprecated, use splice!(a,x) instead.
 in disconnect at /Users/randyzwitch/.julia/ODBC/src/userfacing.jl:91
ERROR: no method length(Connection,)
 in disconnect at /Users/randyzwitch/.julia/ODBC/src/userfacing.jl:93
 in disconnect at no file

Any help you can provide would be great.

odbc_dm is overwritten even if set

When using unixODBC on OSX, the driver manager is libodbc.dylib. However, even if I set odbc_dm manually, its value is overwritten in https://github.com/karbarcca/ODBC.jl/blob/837fac18b6729387e6aa0d262597fe94f3f065b6/src/ODBC_Types.jl#L17
Proposed solution: check if odbc_dm is set to something, and try dlopen the value first.

UTF16String API updates

When JuliaLang/julia#7016 or similar is merged, it looks like your usage of UTF16String will break, as we discussed. You will probably want to use the utf16(...) constructor instead of UTF16String(...), or use the latter only if your data is already NUL terminated.

Prevent SQL injection (prepared statement + escape meta)

[mirrored from https://groups.google.com/d/msg/julia-users/fRFr20atCbc/SqUVy8kvwdAJ]

This is an extension of Issue #15 which already covers prepared statements.

I'm using the ODBC package to connect to a database and make a few SQL queries. For some of these queries, the table name or values in the WHERE clause come from variables, and I need a way to safely quote them for use in an SQL string.

For values in the WHERE clause, prepared statements will help get rid of SQL injection.

For table names (and column names as well), a simple sql escape function would help (one that is database character set aware). Something similar to PHP's mysqli_real_escape_string: http://php.net/manual/en/mysqli.real-escape-string.php

Funky UTC values

Using Redshift (Postgres) and doing an int to UTC calculation, I'm getting back weird results. All dates should be between 4/1/13 and 8/31/13:

julia  > results = query(
"select token, timestamp 'epoch' + leads.created * interval '1 second' as created_utc, ip 
from leads
left join accounts on (accounts.code = leads.account_code)
where timestamp 'epoch' + leads.created * interval '1 second' between '2013-04-01' and '2013-08-31' and accounts.name = '<redacted>';")

All of the results below should have a timestamp of 2013-04-01 for the first few minutes of the morning.

julia > results["created_utc"]
327675-element Array{DateTime{ISOCalendar,Zone0},1}:
 2013-04-01T00:00:15 UTC   
 -0003-08-12T-1:-27:-56 UTC
 2013-04-01T00:07:04 UTC   
 -0053-12-10T00:-27:-56 UTC
 2013-04-01T00:11:02 UTC   
 0033-09-06T04:33:04 UTC   
 2013-04-01T00:19:27 UTC   
 0035-09-14T02:33:04 UTC   
 2013-04-01T00:23:41 UTC   
 0094-05-20T05:33:04 UTC   
 2013-04-01T00:27:09 UTC   
 -0031-05-17T-9:-27:-56 UTC
 2013-04-01T00:32:50 UTC                       
 -0001-12-31T00:00:00 UTC ```

Unable to connect

I don't seem to be able to connect as of commit: 9e0180e

I created a fresh julia install from julianightlies (Version 0.3.0-prerelease+422 (2013-12-12 23:13 UTC)).
I'm running a 64bit Ubuntu 12.04 VM on a windows 7 machine.

connect("mysql-connector")
ERROR: connect: no such file or directory (ENOENT)
in yield at multi.jl:1540
in wait at task.jl:117
in wait_connected at stream.jl:263
in connect at stream.jl:878
in connect at stream.jl:882

Trying with user name and password results in:

julia> ODBC.connect("mysql-connector", usr="****", pwd="****")
ERROR: function connect does not accept keyword arguments

running behind the scenes connection code shows that dsn is configured correctly

using ODBC
dsn = "mysql-connector"
username = "****" #empty string "" if none
password = "****" #empty string "" if none
env = ODBC.ODBCAllocHandle(ODBC.SQL_HANDLE_ENV,ODBC.SQL_NULL_HANDLE)
dbc = ODBC.ODBCAllocHandle(ODBC.SQL_HANDLE_DBC,env)
ODBC.SQLConnect(dbc,dsn, username,password)

returns: 0

Warnings on 'using' statement

Not quite sure where to put this. When I do using ODBC as the first command in a clean session, a series of warnings are thrown, but all referring to other packages. So even though DataFrames gets referenced, using DataFrames as the first command of a clean session causes no errors.

Warning: New definition 
    |(NAtype,Any) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:385
is ambiguous with 
    |(Any,SynchronousStepCollection) at /Users/randyzwitch/.julia/BinDeps/src/BinDeps.jl:286.
Make sure 
    |(NAtype,SynchronousStepCollection)
is defined first.
Warning: New definition 
    |(Any,NAtype) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:385
is ambiguous with 
    |(SynchronousStepCollection,Any) at /Users/randyzwitch/.julia/BinDeps/src/BinDeps.jl:283.
Make sure 
    |(SynchronousStepCollection,NAtype)
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    -(DataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    -(DataArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    -(AbstractArray{T,N},DataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    -(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},DataArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    -(AbstractDataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    -(AbstractDataArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    -(AbstractArray{T,N},AbstractDataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    -(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractDataArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    +(DataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    +(DataArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    +(AbstractArray{T,N},DataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    +(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},DataArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    +(AbstractDataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    +(AbstractDataArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N}) at operators.jl:245
is ambiguous with 
    +(AbstractArray{T,N},AbstractDataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    +(AbstractArray{T1<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N},AbstractDataArray{T2<:Union(DateTime{C<:Calendar,T<:Offsets},Date{C<:Calendar},Period),N})
is defined first.
WARNING: contains(collection, item) is deprecated, use in(item, collection) instead
 in contains at reduce.jl:238
Warning: New definition 
    +(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    +(DataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    +(DataArray{T1<:Period,N},AbstractArray{T2<:Period,N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    +(AbstractArray{T,N},DataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    +(AbstractArray{T1<:Period,N},DataArray{T2<:Period,N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    +(AbstractDataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    +(AbstractDataArray{T1<:Period,N},AbstractArray{T2<:Period,N})
is defined first.
Warning: New definition 
    +(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    +(AbstractArray{T,N},AbstractDataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    +(AbstractArray{T1<:Period,N},AbstractDataArray{T2<:Period,N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    -(DataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    -(DataArray{T1<:Period,N},AbstractArray{T2<:Period,N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    -(AbstractArray{T,N},DataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:201.
Make sure 
    -(AbstractArray{T1<:Period,N},DataArray{T2<:Period,N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    -(AbstractDataArray{T,N},AbstractArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    -(AbstractDataArray{T1<:Period,N},AbstractArray{T2<:Period,N})
is defined first.
Warning: New definition 
    -(AbstractArray{T1<:Period,N},AbstractArray{T2<:Period,N}) at operators.jl:245
is ambiguous with 
    -(AbstractArray{T,N},AbstractDataArray{T,N}) at /Users/randyzwitch/.julia/DataFrames/src/operators.jl:220.
Make sure 
    -(AbstractArray{T1<:Period,N},AbstractDataArray{T2<:Period,N})
is defined first.

ERROR: UTF16String not defined

https://github.com/karbarcca/ODBC.jl/blob/master/src/backend.jl#L135

I'm using julia 0.2.0, how VERSION shows up in julia development version?

Perhaps adding a version verification before creating this function could fix that, ex: VERSION < v"0.2.0"

ODBC in Julia Studio on Windows

I am trying to use ODBC within Julia Studio on windows and it is not working as the documentation suggests. I don't know if its a problem with Julia, Julia Studio, or ODBC, but just wanted to ask somewhere. Here is what I have tried:

julia> ODBC.connect("xfdr",usr="xfl",pwd="xfl")
ODBC not defined

julia> co = connect("xfdr",usr="xfl",pwd="xfl")
unsupported or misplaced expression keywords

julia> advancedconnect()
advancedconnect not defined

REPL crash on large write to file operations

I recently added the functionality to return a query resultset direct to a file, but found that it crashes the REPL sometimes during the retrieval process. Not sure if this is a bug in my code specifically or related to JuliaLang/julia#2676.

Errors and warnings with SQL Server + OSX + FreeTDS

Before running the code that produced the following output, I completely wiped ~/.julia/v0.3. I really have no idea where to start troubleshooting, so I tried cloning ODBC.jl from master, but that didn't help either.

Attached is a complete console log.

julia> import ODBC
Warning: ignoring conflicting import of StatsBase.range into DataFrames

julia> conn   = ODBC.connect("server"; usr="***", pwd="***")
ODBC Connection Object
----------------------
Connection Data Source: server
server Connection Number: 1
Contains resultset? No

julia> sqlRes = ODBC.query("SELECT TOP(1) * FROM NotImportantDB.dbo.TestTable")
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
elapsed time: 0.055881255 seconds
1x4 DataFrame
|-------|--------------|-------------|---------|-----|
| Row # | StringColumn | FloatColumn | Numeric | Int |
| 1     | "First Row"  | 1.1         | 1.0     | 1   |

julia> sqlRes = ODBC.query("SELECT TOP(10) * FROM NotImportantDB.dbo.TestTable")
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:50
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43
ERROR: access to undefined reference
 in ODBCFetchDataFramePush! at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:219
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:57
 in query at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:43

julia> sqlRes = ODBC.querymeta("SELECT TOP(10) * FROM NotImportantDB.dbo.TestTable")
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:92
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:85
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:92
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:85
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:92
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:85
WARNING: convert{T}(p::Type{Ptr{T}},a::Array) is deprecated, use convert(p,pointer(a)) instead.
 in SQLDescribeCol at /Users/aUser/.julia/v0.3/ODBC/src/ODBC_API.jl:549
 in ODBCMetadata at /Users/aUser/.julia/v0.3/ODBC/src/backend.jl:65
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:92
 in querymeta at /Users/aUser/.julia/v0.3/ODBC/src/userfacing.jl:85
Resultset metadata for executed query
------------------------------------
Query: SELECT TOP(10) * FROM NotImportantDB.dbo.TestTableColumns: 4
Rows: -1
WARNING: DataFrame(::Matrix, ::Vector)) is deprecated, use convert(DataFrame, Matrix) instead
 in DataFrame at /Users/aUser/.julia/v0.3/DataFrames/src/deprecated.jl:41
 in show at /Users/aUser/.julia/v0.3/ODBC/src/ODBC.jl:37
 in anonymous at show.jl:1042
 in showlimited at show.jl:1041
 in writemime at replutil.jl:2
 in display at REPL.jl:108
 in print_response at REPL.jl:123
 in print_response at REPL.jl:674
 in print_response at REPL.jl:675
 in anonymous at REPL.jl:431
Error showing value of type Metadata:
ERROR: no method Index(Array{ASCIIString,1})
 in DataFrame at /Users/aUser/.julia/v0.3/DataFrames/src/deprecated.jl:48
 in show at /Users/aUser/.julia/v0.3/ODBC/src/ODBC.jl:37
 in anonymous at show.jl:1042
 in showlimited at show.jl:1041
 in writemime at replutil.jl:2
 in display at REPL.jl:108
 in print_response at REPL.jl:123
 in print_response at REPL.jl:674
 in print_response at REPL.jl:675
 in anonymous at REPL.jl:431

julia> Pkg.status()
1 required packages:
 - ODBC                          0.3.7
7 additional packages:
 - Blocks                        0.0.2
 - DataArrays                    0.1.6
 - DataFrames                    0.5.3
 - Datetime                      0.1.2
 - GZip                          0.2.12
 - SortingAlgorithms             0.0.1
 - StatsBase                     0.3.11

For database connectivity I have FreeTDS+ODBC installed via MacPorts:

machine:~ aUser$ port installed | grep odbc
  freetds @0.92.405_0+mssql+odbc (active)

MySQL ODBC on Ubuntu 12.04

From the discussion here: https://groups.google.com/forum/#!topic/julia-users/pNKBrhArRkI

invalid UTF-8 sequence

Sorry, today is cause @karbarcca heartburn day...

I'm getting an error about UTF-8 errors; I suspect there is malformed data in this field, but it's causing Julia to choke:

#Get lots of user-agent strings
ua_df = query("Select distinct ua from leads limit 1000000;");

invalid UTF-8 sequence
while loading In[2], in expression starting on line 2
 in convert at utf8.jl:155
 in ODBCClean at /Users/randyzwitch/.julia/ODBC/src/backend.jl:119
 in ODBCFetchDataFrame at /Users/randyzwitch/.julia/ODBC/src/backend.jl:135
 in query at /Users/randyzwitch/.julia/ODBC/src/userfacing.jl:57
 in query at /Users/randyzwitch/.julia/ODBC/src/userfacing.jl:43

querymeta("Select distinct ua from leads limit 1000000;")

Resultset metadata for executed query
------------------------------------
Query: Select distinct ua from leads limit 1000000;Columns: 1
Rows: 1000000
1x5 DataFrame:
        Column Names                    Types Sizes Digits Nullable
[1,]            "ua" ("SQL_WLONGVARCHAR",-10)   512      0        1

implement `prepare`, `execute` functions

Currently, query immediately sends information to the database and returns the response. It would be nice to have prepare and execute statements as well, for queries which will be used repeatedly.

ODBC supports this, of course, and ODBC.jl already implements the low level calls

Unless you already have some code for this, I'll work on implementing it, as I have use for this now.

how to call sql procedure to fetch required data through ODBC.jl

I want to call certain sql procedures written in sql environment to fetch required data but could not find how to do it using ODBC.jl

Regards,
Rakesh

Writing query to file issues

When trying to write query results out to file, I'm seeing the following issues (MySQL database on localhost, OSX)

julia> ODBC.connect("MySQL")
Connection 1 to MySQL successful.

julia> query("Select * from a1987;");
Progress: done

julia> query("Select * from a1987;", file="output.tab",delim='\t');
ERROR: no method string(Int32,)
 in ODBCDirectToFile at /Users/randyzwitch/.julia/ODBC/src/backend.jl:188
 in ODBCFetch at /Users/randyzwitch/.julia/ODBC/src/backend.jl:153
 in query at /Users/randyzwitch/.julia/ODBC/src/userfacing.jl:49
 in query#g17 at no fileo file

arrayset not defined

When querying Postgres using ODBC I get an arrayset not defined error:

Connection to the database works fine because I can make count queries without raising an error. However, if I try to select entire (or parts) of rows...

query("SELECT * FROM table;")
ERROR: arrayset not defined
in ODBCCopy! at /Users/admin/.julia/v0.3/ODBC/src/backend.jl:138
in ODBCFetchDataFrame at /Users/admin/.julia/v0.3/ODBC/src/backend.jl:194
in query at /Users/admin/.julia/v0.3/ODBC/src/userfacing.jl:57
in query at /Users/admin/.julia/v0.3/ODBC/src/userfacing.jl:43

I've looked through the source and I can't figure out where arrayset should be defined. It's always given with @inbounds.

About my setup:

versioninfo(true)
Julia Version 0.3.0-prerelease+2997
Commit 6de4d3b* (2014-05-12 08:52 UTC)
Platform Info:
System: Darwin (x86_64-apple-darwin12.5.0)
CPU: Intel(R) Core(TM) i7 CPU M 620 @ 2.67GHz
WORD_SIZE: 64
uname: Darwin 13.1.0 Darwin Kernel Version 13.1.0: Thu Jan 16 19:40:37 PST 2014; root:xnu-2422.90.20~2/RELEASE_X86_64 x86_64 i386

Please let me know if there's any other information that will help resolve the issue.