Issue with MSSQL ODM2 database when querying various endpoints

[emiliom]

Posted by @Elijahwalkerwest originally on the ODM2 repo (here). Reposted here b/c it's where it belongs, as it's about ODM2 REST API.

When querying various tables with an MSSQL database, returning this error:

TypeError at /v1/samplingfeatures/
'NoneType' object is not iterable

Request Method: GET
Request URL: http://127.0.0.1:8000/v1/samplingfeatures/
Django Version: 1.11.6
Python Executable: C:\ProgramData\Anaconda2\envs\odm2restenv\python.exe
Python Version: 2.7.14
Python Path: ['C:\Users\Elijah\Desktop\Projects\odm2restapi', 'C:\Users\Elijah\Desktop\Projects\odm2restapi', 'C:\Users\Elijah\Desktop\Projects\odm2restapi\odm2rest\settings', 'C:\Users\Elijah\Desktop\Projects\odm2restapi\odm2rest', 'C:\Users\Elijah\Desktop\Projects\odm2restapi\odm2proj\odm2proj', 'C:\Users\Elijah\Desktop\Projects\odm2restapi\odm2proj', 'C:\Users\Elijah\Desktop\Projects\odm2restapi\odm2proj\odm2rest', 'C:\Users\Elijah\Desktop\Projects\odm2restapi\api', 'C:\ProgramData\Anaconda2\envs\odm2restenv\python27.zip', 'C:\ProgramData\Anaconda2\envs\odm2restenv\DLLs', 'C:\ProgramData\Anaconda2\envs\odm2restenv\lib', 'C:\ProgramData\Anaconda2\envs\odm2restenv\lib\plat-win', 'C:\ProgramData\Anaconda2\envs\odm2restenv\lib\lib-tk', 'C:\ProgramData\Anaconda2\envs\odm2restenv', 'C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages', 'c:\users\elijah\desktop\projects\odm2pythonapi']
Server time: Mon, 23 Oct 2017 23:14:04 +0000
Installed Applications:
['django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'rest_framework',
'api']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback:

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\django\core\handlers\exception.py" in inner
41. response = get_response(request)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\django\core\handlers\base.py" in _get_response
187. response = self.process_exception_by_middleware(e, request)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\django\core\handlers\base.py" in _get_response
185. response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\django\views\decorators\csrf.py" in wrapped_view
58. return view_func(*args, **kwargs)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\django\views\generic\base.py" in view
68. return self.dispatch(request, *args, **kwargs)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\rest_framework\views.py" in dispatch
489. response = self.handle_exception(exc)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\rest_framework\views.py" in handle_exception
449. self.raise_uncaught_exception(exc)

File "C:\ProgramData\Anaconda2\envs\odm2restenv\lib\site-packages\rest_framework\views.py" in dispatch
486. response = handler(request, *args, **kwargs)

File "C:\Users\Elijah\Desktop\Projects\odm2restapi\api\views.py" in get
151. sfs = get_samplingfeatures(**get_kwargs)

File "C:\Users\Elijah\Desktop\Projects\odm2restapi\api\core.py" in get_samplingfeatures
183. for sf in sampling_features:

Exception Type: TypeError at /v1/samplingfeatures/
Exception Value: 'NoneType' object is not iterable
Request information:
USER: AnonymousUser

GET: No GET data

POST: No POST data

FILES: No FILES data

When querying all samplingfeatures, or filtering by ID, type, or any other parameter

[lsetiawan]

@emiliom The problem is not the rest api, but the python api.

[lsetiawan]

@Elijahwalkerwest have showed me the problem, before I suggested to post in ODM2Python API.

[lsetiawan]

ODM2PythonAPI isn't able to query SamplingFeatures from MSSQL, but it works for postgresql. I have tested this.

[emiliom]

The problem is not the rest api, but the python api.
@Elijahwalkerwest have showed me the problem, before I suggested to post in ODM2Python API.

Ah, ok. Still, the issue was posted on the ODM2 repo, not the ODM2PythonAPI repo.

Also, as @lsetiawan mentioned earlier over email, we really think you should start your ODM2 REST API testing with a setup as similar to ours as possible, to minimize uncertainties at the start. For that reason we strongly recommend you start with the EnviroDIY postgresql database, since that's what Don is using for ODM2 REST API. Working with an MS SQL ODM2 database could bring up other unrelated issues, plus we can't help you with MS SQL.

It's worth posting this issue on the ODM2PythonAPI repo, but please switch over to using the EnviroDIY postgresql database instead, for ODM2 REST API work.

[horsburgh]

@emiliom - We have an existing ODM2 database with water quality sample data in it that is the major dataset for our specimen data viewer. I need Eli to be programming against this database as I need to be able to supply those data through the REST API to the specimen data viewer web application. The Envirodiy database isn't a good test case for our use case.

To avoid the SQL Server issues that @Elijahwalkerwest has been having, I asked him to work with @fryarludwig to convert our SQL Server database to PostgreSQL and deploy it on a sandbox server here at USU. If Eli can get past his current issues by just working with a PostgreSQL database that is fine until @sreeder gets back and we can figure out the SQL Server issue. There's no reason why ODM2 Python API should not work with a SQL Server database.

[emiliom]

To avoid the SQL Server issues that @Elijahwalkerwest has been having, I asked him to work with @fryarludwig to convert our SQL Server database to PostgreSQL and deploy it on a sandbox server here at USU. If Eli can get past his current issues by just working with a PostgreSQL database that is fine until @sreeder gets back and we can figure out the SQL Server issue.

That sounds good.

There's no reason why ODM2 Python API should not work with a SQL Server database.

True, yet ... It's one more uncertainty to introduce, which isn't a good thing at this early stage. And we can't help at all with SQL Server. BTW, MySQL would be fine too -- it sounds like @Elijahwalkerwest has more experience with that than with PostgreSQL?