Re: [julia-users] load a Julia dataframe from Microsoft SQL Server table

[Stefan Karpinski]

I've been working through encoding issues with this as well. To connect to
Microsoft SQL Server, this patch made things work:

diff --git a/src/backend.jl b/src/backend.jl
index b5f24af..bf4ee11 100644
--- a/src/backend.jl
+++ b/src/backend.jl
@@ -40,7 +40,7 @@ end

 # Send query to DMBS
 function ODBCQueryExecute(stmt::Ptr{Void}, querystring::AbstractString)
-    if @FAILED SQLExecDirect(stmt, utf16(querystring))
+    if @FAILED SQLExecDirect(stmt, utf8(querystring))
         ODBCError(SQL_HANDLE_STMT,stmt)
         error("[ODBC]: SQLExecDirect failed; Return Code: $ret")
     end


The query string gets passed through to SQLExecDirect:

#SQLExecDirect
#
http://msdn.microsoft.com/en-us/library/windows/desktop/ms713611(v=vs.85).aspx
#Description: executes a preparable statement
#Status:
function SQLExecDirect(stmt::Ptr{Void},query::AbstractString)
    @windows_only ret = ccall( (:SQLExecDirect, odbc_dm), stdcall,
        Int16, (Ptr{Void},Ptr{UInt8},Int),
        stmt,query,sizeof(query))
    @unix_only ret = ccall( (:SQLExecDirect, odbc_dm),
            Int16, (Ptr{Void},Ptr{UInt8},Int),
            stmt,query,sizeof(query))
    return ret
end


This function just convert whatever it's argument is to a pointer. Looking
at the docs
<https://msdn.microsoft.com/en-us/library/windows/desktop/ms713611(v=vs.85).aspx>
for
this function, the signature is this:

SQLRETURN SQLExecDirect(
     SQLHSTMT     StatementHandle,
     SQLCHAR *    StatementText,
     SQLINTEGER   TextLength);

and SQLCHAR is defined
<https://msdn.microsoft.com/en-us/library/ms714556(v=vs.85).aspx> as
unsigned char. So this would seem to be a non-wide character string – i.e.
ASCII or UTF-8. And indeed, that's what the Microsoft SQL driver seems to
be expecting.

The question I have is this: how the heck is this working for other ODBC
drivers? How are they getting pointers to UTF-16 data and interpreting it
correctly? The correct fix would seem to be to make this always send UTF-8
strings. But when I made a PR <https://github.com/JuliaDB/ODBC.jl/pull/71>
that did that, it seemed to break other setups.


On Wed, Feb 3, 2016 at 2:46 AM, Terry Seaward <terry.seaw...@gmail.com>
wrote:

> Is there a DBI compliant version of this? Also I have endless string
> conversion issues with this package.
>
> On Tuesday, 17 March 2015 21:22:50 UTC+2, Jacob Quinn wrote:
>>
>> Check out the https://github.com/quinnj/ODBC.jl package for connecting
>> to DSN defined in your ODBC manager.
>>
>> On Tue, Mar 17, 2015 at 1:17 PM, Charles Brauer <charle...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> I'm considering diving into Julia. However, all of my data is in a
>>> Microsoft SQL Server database.
>>> I would really appreciate a Julia code example on how to load a Julia
>>> dataframe from SQL Server table.
>>>
>>> Thanks
>>> Charles
>>>
>>
>>