juliadatabases / odbc.jl Goto Github PK
View Code? Open in Web Editor NEWAn ODBC interface for the Julia programming language
Home Page: https://odbc.juliadatabases.org/stable
License: Other
An ODBC interface for the Julia programming language
Home Page: https://odbc.juliadatabases.org/stable
License: Other
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.
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:
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)
Alter the "SQL2Julia" assignment in "ODBC_Types.jl" to this custom type
SQL_TINYINT => SQLTinyInt
Add an extra ODBCAllocate() method accounting for this parameter type in "backend.jl"
ODBCAllocate(x::Array{SQLTinyInt,1},y) = Array(Int16,y)
Add an extra ODBCClean() method accounting for this parameter type in "backend.jl"
ODBCClean(x::Array{SQLTinyInt,1},y,z) = int16(x[y].value)
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
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)
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)
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:
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.
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
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
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
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.
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?
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!
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")
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.
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.
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).
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","")
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.
Tests pass.
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
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
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')");
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
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.
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...
'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.
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.
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;");
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)
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}
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?
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.
Tests pass.
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
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.
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.
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?
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)
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.
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.
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.
[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
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 ```
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
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.
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"
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
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.
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)
From the discussion here: https://groups.google.com/forum/#!topic/julia-users/pNKBrhArRkI
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
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.
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
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
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.