Connecting Tidier with MSSQL

Specific Domains Data
,
1

Hi, all. I’m hoping to get some Tidier officiandos here to help me out. I’m trying to connect to a MSSQL database using Tidier because I want to test out that package. I have followed all the info in the docs as far as I can tell but I can’t get the connect method to work.

Here’s my code, a simple connection is erroring out:

using Tidier
using ODBC
db = DB.connect(:mssql, "Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")

and here’s the error message:

MethodError: no method matching connect(::Symbol, ::String)



Closest candidates are:

  connect(::Symbol, ::String, !Matched::String, !Matched::String, !Matched::String, !Matched::String)

   @ TidierDB C:\Users\[userid]\.julia\packages\TidierDB\XJFly\src\parsing_snowflake.jl:160

  connect(::Symbol, ::String, !Matched::String)

   @ TidierDB C:\Users\[userid]\.julia\packages\TidierDB\XJFly\src\parsing_gbq.jl:9

  connect(::Symbol; kwargs...)

   @ TidierDB C:\Users\[userid]\.julia\packages\TidierDB\XJFly\src\TidierDB.jl:331

Any help is appreciated!

2

I would first try to connect some way without Tidier (and then you might only need TiderDB):

Also you’re doing something more than me, since I get less specific error message:

julia> using Tidier

julia> using ODBC

julia> db = DB.connect(:mssql, "Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")
ERROR: UndefVarError: `DB` not defined

Something like this errors for me (expectedly) but should work for you:

julia> julia> con = ODBC.Connection("Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")  # or similar to ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=ip#,DATABASE=dbname,UID=username,PWD=passwrod")
ERROR: 01000: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

You might want to make sure if you have latest versions with st (though I doubt it was the problem), I noticed I didn’t have latest (and ironically trying to fix downgraded):

pkg> add Tidier@v1.4.0

⌅ [4acbeb90] ↓ Stipple v0.30.7 ⇒ v0.28.14
  [2b41e42f] ↑ StippleMarkdown v0.0.1 ⇒ v0.2.0
  [f0413319] ↑ Tidier v1.3.0 ⇒ v1.4.0
⌅ [86993f9b] ↓ TidierDB v0.4.1 ⇒ v0.1.9
    Updating `~/Manifest.toml`
...
⌅ [458c3c95] ↓ OpenSSL_jll v3.0.15+1 ⇒ v1.1.23+1 [and many more than this, what I don't like about Julia's defaults]

pkg> add TidierDB@v0.4.1
   Resolving package versions...
   Installed JuliaFormatter ─ v1.0.61
    Updating `~/Project.toml`
⌃ [f0413319] ↓ Tidier v1.4.0 ⇒ v1.3.0
  [86993f9b] ↑ TidierDB v0.1.9 ⇒ v0.4.1
...

I’m not exactly sure what’s blocking, I see in “[compat]” (and should be ok?): TidierDB = “0.1, 1”:

(pharaldsson) pkg> add TidierDB@v0.4.1 Tidier@v1.4.0
   Resolving package versions...
ERROR: Unsatisfiable requirements detected for package TidierDB [86993f9b]:
 TidierDB [86993f9b] log:
 ├─possible versions are: 0.1.0-0.4.1 or uninstalled
 ├─restricted to versions 0.4.1 by an explicit requirement, leaving only versions: 0.4.1
 └─restricted by compatibility requirements with Tidier [f0413319] to versions: 0.1.0-0.1.9 — no versions left
   └─Tidier [f0413319] log:
     ├─possible versions are: 0.3.0-1.4.0 or uninstalled
     └─restricted to versions 1.4.0 by an explicit requirement, leaving only versions: 1.4.0

I think on Windows you have ODBC preinstalled (the non-Julia support), elsewhere you also likely need to do something like (I even gave up trying to set up in R…):

$ apt-get install unixodbc-dev
3

Thanks for taking a look. I’ve used ODBC before and was able to connect and run queries. I was just attempted to get Tidier to work without using and explicit ODBC connection on my end. The docs show an mssql example but it doesn’t seem work with just importing Tidier as I expected.

Regarding what I did that gave more info than the UndefVarError for DB, those 3 lines of code in a Pluto notebook are all that I used. I am starting from a project space created by the DrWatson package, not sure if that is doing anything.

If I don’t get any answers on fixing Tidier, I’ll probably do as you suggested and connect using ODBC, possibly pull data like that, and then using Tidier on a dataframe or something.

4

@jjAtNxtPt do you know what version you are using? based on the error message, I would guess >v.3 (which is good!). however this means, i moved most of the backends to package extensions to reduce direct dependencies in tidierdb. this means that to use MsSQL, you will have to do the following

using Tidier
using ODBC

this will load the MsSQL extension.

From there to connect
it will look like this (the :mssql is now mssql()

db = DB.connect(DB.mssql(), "Driver={ODBC Driver 17 for SQL Server};Server=my.sqlserver=Reporting;Trusted_Connection=yes;")

I did not introduce a connect for MsSQL until v.3.2 tho so can always set up your connection as you historically have and then pass that to DB.db_table() for you connection.

finally, I would rec using .3.4 or greater (ideally >.4) as I fixed an MsSQL issue join in v.3.4.

please let me know if that fixes your connection issue.

as far as the dependency issue with a downgrade. im not sure why that happened, but we will hopefully release an updated version of tidier soon to avoid that.

5

Well, I was playing around a bit more and tried using a “connection_string” kwarg and received a different error that let me to try using a dsn name instead of a connection string like this :

db = DB.connect(:mssql; dsn="ODBCNAME")
6

I’m using Tidier v 1.4, so whatever TidierDB version that comes with that meta package.

I tried using the code you specified, fixing the connection string which was probably my fault, and here’s what I got from the REPL:

julia> using Tidier

julia> using ODBC

julia> db = DB.connect(DB.mssql(), "Driver={ODBC Driver 17 for SQL Server};Server=<my db>;Database=Reporting;Trusted_Connection=yes;")
ERROR: UndefVarError: `mssql` not defined
Stacktrace:
 [1] getproperty(x::Module, f::Symbol)
   @ Base .\Base.jl:31
 [2] top-level scope
   @ REPL[13]:1
7

hmm , can you try just isolating TidierDB and seeing that? I would love to get this to work for you

so

import TidierDB as DB 
using ODBC # you should get an ext is loaded message in repel
] status TidierDB

and then can you try connecting via just ODBC the way you used to and then passing that to DB.db_table(conn, "table_name").

The connect for MsSQL was not made to be super robust yet, so that could be the block

8

Ok, I added TidierDB by itself from the REPL and then tried your code.

Here’s the REPL after I restarted it to give it a clean slate:

julia> import TidierDB as DB

julia> import ODBC

(@v1.10) pkg> status TidierDB
Status `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
⌅ [86993f9b] TidierDB v0.1.9
Info Packages marked with ⌅ have new versions available but compatibility constraints restrict them from upgrading. To see why use `status --outdated`
9

Ok, so
mssql() is only in .3.0 and above which is what I would recommend using and why you got that alternate error message about mssql() not existing

Can you do

]up TidierDB

and the make sure its > .3.0 ideally > .3.3 and then try again with

import TidierDB as DB
using ODBC
10

I tried, but some compatability issue is causing it to fail:

(@v1.10) pkg> up TidierDB
    Updating registry at `C:\Users\<userid>\.julia\registries\General.toml`
  No Changes to `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
  No Changes to `C:\Users\<userid>\.julia\environments\v1.10\Manifest.toml`

(@v1.10) pkg> st TidierDB
Status `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
⌅ [86993f9b] TidierDB v0.1.9
Info Packages marked with ⌅ have new versions available but compatibility constraints restrict them from upgrading. To see why use `status --outdated`

I tried the status --outdated command and all it says is:

(@v1.10) pkg> status --outdated TidierDB
Status `C:\Users\<userid>\.julia\environments\v1.10\Project.toml`
⌅ [86993f9b] TidierDB v0.1.9 (<v0.4.1): Tidier

I’m not sure what that actually means, though, or how to get around it.

11

ahh, ok, until we can get a newer version of Tidier released, you may have to uninstall Tidier and install the individual packages you need.

You can also try

using Pkg; Pkg.add(url = "https://github.com/TidierOrg/TidierDB.jl")

or use an old version of tidierdb which does not have extensions so you would have use ODBC to create your own connection, and then
set_sql_mode(:mssql)

and then use tidierdb like that.

2 Likes
12

Thanks!

1 Like
13

Thank you, you saved my day.

2 Likes