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;")
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):
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…):
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.
@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.
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 :
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
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`
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.
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)