MS SQL problems and testing, ODM2 timeseries DAO

[sreeder]

I am trying to run this on a MSSQL server and I was wondering if you guys had completed any testing against this database type?

I am receiving an error when I run the code. It is similar to an issue we were getting when we started running against PostgreSql

[lsetiawan]

We do not support MSSQL as of now. We have no way of testing and developing with that DB system. Currently, we are "officially" supporting MySQL, SQLite, and PostgreSQL.

[emiliom]

Just to add to what @lsetiawan said: we are not able to do any testing or development on MSSQL. So, to the extent that odm2api works with MSSQL (and to the extent that odm2api actually used in wofpy), and that the explicit SQL statements in the wofpy odm2timeseries DAO work with MSSQL (which we can't test), things "should" work with MSSQL ...

Maybe worth discussing this at next week's BiGCZ call.

[horsburgh]

Hi guys - I feel like we've done a MASSIVE amount of work over the past several years to make sure that the tools we are building for ODM2 are platform and database agnostic. I think it would be a real shame if this tool locked us into a particular way of doing things. There has always been a free version of SQL Server, and Windows is pretty easy to get your hands on.

That said - according to Stephanie, this error may have nothing to do with the fact that we are trying to use SQL Server as it is similar to other errors that have popped up when running with PostgreSQL. Any chance you could look at this error more closely with @sreeder ?

[lsetiawan]

Hi @sreeder Could you please go through your dao, and try adding an or on the lines where it checks for the engine as 'postgresql'.

if self.engine.name is 'postgresql' or self.engine.name is 'mssql':

https://github.com/ODM2/WOFpy/blob/master/wof/examples/flask/odm2/timeseries/odm2_timeseries_dao.py#L131, https://github.com/ODM2/WOFpy/blob/master/wof/examples/flask/odm2/timeseries/odm2_timeseries_dao.py#L142

See if that changes anything. Thanks.

[sreeder]

@lsetiawan I was able to add those changes last Friday, but have since been getting another error that I am unsure how to fix. Is this something you have come across before?

[lsetiawan]

I think that pops up when you don't have [WOF] in the .cfg file. https://github.com/ODM2/WOFpy/blob/master/wof/examples/flask/odm2/timeseries/odm2_config_timeseries.cfg#L1

[sreeder]

@lsetiawan That is strange, because my .cfg file looks nearly identical to that one.

[sreeder]

@lsetiawan Okay, I was running the code from another folder, so it seems to have difficulty finding the path unless you run it from the same folder as the runserver.py file.

[lsetiawan]

@sreeder Hmm.. interesting. If the .cfg files are in a different folder. You should be able to make an absolute path. See below.

M_CONFIG_FILE = os.path.join('/home/ubuntu', 'wofpy_prod', 'odm2_config_mysql.cfg')

[sreeder]

@lsetiawan Thanks for your help, I was able to get this up and running! So with your code fix from above

if self.engine.name is 'postgresql' or self.engine.name is 'mssql':

https://github.com/ODM2/WOFpy/blob/master/wof/examples/flask/odm2/timeseries/odm2_timeseries_dao.py#L131, https://github.com/ODM2/WOFpy/blob/master/wof/examples/flask/odm2/timeseries/odm2_timeseries_dao.py#L142

I was able to get the code running against an MSSQL database.

As a note: I was able to follow the instructions on the included document to set up a driver to connect to MSSQL from a linux server. It specifically states the instructions for ODMTools but that only affects the last step on how to enter the connection string, so it is a pretty useful document.
UsingODMToolswithMSSQL.pdf

On Ubuntu version 16.04 there is an issue downloading one of the libraries, libmydobc but you can get it from the mysql website.

I will post the full set of instructions on how to get this library soon.

1. After installing freetds-dev search for the libtdsodbc.so file

find / -name libtdsodbc.so

2. Set the odbc.ini file to point to that file in the default driver section.

the connection string is set up like so in the runserver.py file:

import urllib
connection =  "mssql+pyodbc:///?odbc_connect="+ urllib.quote_plus(DRIVER={FreeTDS};DSN=%s;UID=%s;PWD=%s;' % (address, user, password))

[emiliom]

@sreeder, I'm really glad you made it work! Thanks so much for these detailed wrap-up comments and the additional set of instructions you've said you'll post as well. We'll want to capture the MSSQL-specific instructions in the general instructions for WOFpy.

It's instructive (for those of us who don't use MSSQL) to see that MSSQL SQL dialect is more similar to PostgreSQL than to MySQL. That's effectively what the code change you implemented from Don does:

if self.engine.name is 'postgresql' or self.engine.name is 'mssql':

@lsetiawan, please make this change/update on master (submit PR) when you get a chance.

BTW, I'll rename this issue to something that refers to MS SQL specifically in the title, to make it easier to locate in the future.