CODEWITHSUNDEEP
julia
Cristian_S
Cristian_S

Reputation: 25

Connection string for SQL Server in Julia ODBC

In Julia, I use this code to connect to a Sql Server database, with no credentials All good!

ODBC.adddsn("SQL_Server_DSN", "SQL Server"; SERVER="x", DATABASE ="x", Trusted_Connection="True")
conn = DBInterface.connect(ODBC.Connection, "SQL_Server_DSN")
cursor = DBInterface.execute(conn, "SELECT * FROM dbo.users")|> DataFrame

But when I try to use credentials, somehow I am not able to get how to adapt the connection string. Any sugestions?

Upvotes: 1

Views: 2886

Answers (2)

Jim Maas
Jim Maas

Reputation: 1729

I finally got a working solution to this problem but it was quite difficult, primarily due to a lack of documentation for most of this. I'm not convinced that the ODBC drivers are set up correctly but it works! Thx for all suggestions.

julia> using ODBC

julia> using DBInterface

julia> using DataFrames

julia> ODBC.drivers()
Dict{String, String} with 4 entries:
  "unixODBC"                          => "Driver=/usr/lib/x86_64-linux-gnu/libodbc.so.2\0UsageCount=1\0"
  "ODBC Drivers"                      => ""
  "ODBC Driver 17 for SQL Server"     => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1\0UsageCount=6\0"
  "unixODBC/usr/lib/x86_64-linux-gnu" => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1\0UsageCount=1\0"

julia> conn2 = ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=ip#;DATABASE=DBName;UID=UserName;PWD=Passwd")
ODBC.Connection(Driver=ODBC Driver 17 for SQL Server;SERVER=ip#;DATABASE=DBName;UID=UserName;PWD=Passwd)

julia> results=DBInterface.execute(conn2, "SELECT TOP 15 variable FROM table")|> DataFrame
15×1 DataFrame
 Row │ variable 
     │ String      
─────┼─────────────
   1 │ A81064
   2 │ A82027
   3 │ A82046
   4 │ A82055
   5 │ A83011
  ⋮  │      ⋮
  12 │ A84027
  13 │ A84030
  14 │ A84032
  15 │ A84033
     6 rows omitted

julia>

Upvotes: 2

Przemyslaw Szufel
Przemyslaw Szufel

Reputation: 42234

This should work:

ODBC.adddsn("SQL_Server_DSN", "SQL Server"; SERVER="x", DATABASE ="x", UID="sa", PWD="passwd")

I am not having currently SQL Server installed to test - however this should work. See also the notes below.

Notes:

  • In Windows Control Panel -> ODBC you cab create a DSN manually and then use it from Julia (instead of adddsn command)
  • DSN can be added manually in Windows Control Panel -> ODBC -> File DSN. In case of connection issues it is a good idea to start there, create something called "File DSN" and then have a look a at the created *.dsn file. This is the most convenient way to debug any ODBC connection.
  • You normally do not want to create a DSN but you should use an ODBC connection directly. In your case this could look in the following way: 
    conn = ODBC.Connection("Driver=SQL Server;SERVER=myServerAddress;DATABASE=myDataBase;UID=sa;PWD=passwd")

Upvotes: 0

Related Questions