h2o import table issue with SQL Server 2016

[exalate-issue-sync[bot]]

When trying to connect H2O to SQL Server 2016 to import a table, H2O returns a SQL Exception relating to FETCH Statement.

Environment:

• Ubuntu VM running H2O Version 3.22.0.2
• Windows instance with SQL Server 2016

JDBC Driver/s tested: MSSQL-jdbc-7 , MSSQL-jdbc-6.2.1 and sqljdbc4

Error Message: ERROR MESSAGE: SQLException: Invalid usage of the option NEXT in the FETCH statement. Failed to connect and read from SQL database with connection_url: jdbc:sqlserver://10.10.10.10:1433;databaseName=DataStage;

Connection String: importSqlTable {"connection_url":"jdbc:sqlserver://10.10.10.10:1433;databaseName=DataStage;","table":"dbo.TestTable","columns":"","username":"xxxx","password":"XXXX"}

Log file: attached [^h2o sql server error.txt]

[exalate-issue-sync[bot]]

Michal Kurka commented: related SO post: https://stackoverflow.com/questions/53718511/h2o-import-table-issue-with-sql-server-2016/53731254

FIY: Downgraded to regular priority because a workaround exists.

[exalate-issue-sync[bot]]

Sebastien Poirier commented: fixed import queries, now using following syntax, both fully supported from SQL Server 2012:

• for column names retrieval (supported from SQLS 2008): bq. select top(1) from ;
  for data chunks retrieval (supported from SQLS 2012): bq. select from
  order by row_number() over (order by (select 0)) offset rows fetch next rows only;
  exalate-issue-sync[bot] commented 1 year ago

  Sebastien Poirier commented: tested locally with SQLServer running on local VM.

  starting h2o with MSSQL Server driver on classpath: bq. java -ea -XX:OnOutOfMemoryError="kill -9 %p" -cp ~/repos/h2o/ext/mssql-jdbc-7.0.0.jre8.jar:build/h2o.jar water.H2OApp -log_level INFO > ~/tmp/out.log 2>&1 &

  importing docs table from local test database {code:python} import h2o

  imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") {code}

  Result before fix: {noformat} In [2]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan")

  H2OServerError Traceback (most recent call last)

  in () ----> 1 imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in import_sql_table(connection_url, table, username, password, columns, optimize, fetch_mode) 478 if columns: 479 p["columns"] = ", ".join(columns) --> 480 j = H2OJob(api("POST /99/ImportSQLTable", data=p), "Import SQL Table").poll() 481 return get_frame(j.dest_key) 482 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in api(endpoint, data, json, filename, save_to) 101 # type checks are performed in H2OConnection class 102 _check_connection() --> 103 return h2oconn.request(endpoint, data=data, json=json, filename=filename, save_to=save_to) 104 105 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in request(self, endpoint, data, json, filename, save_to) 405 auth=self._auth, verify=self._verify_ssl_cert, proxies=self._proxies) 406 self._log_end_transaction(start_time, resp) --> 407 return self._process_response(resp, save_to) 408 409 except (requests.exceptions.ConnectionError, requests.exceptions.HTTPError) as e: /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in _process_response(response, save_to) 746 # Note that it is possible to receive valid H2OErrorV3 object in this case, however it merely means the server 747 # did not provide the correct status code. --> 748 raise H2OServerError("HTTP %d %s:\n%r" % (status_code, response.reason, data)) 749 750 H2OServerError: HTTP 500 Server Error: Server error java.lang.RuntimeException: Error: SQLException: Invalid usage of the option NEXT in the FETCH statement. Failed to connect and read from SQL database with connection_url: jdbc:sqlserver://192.168.59.162;databaseName=test; Request: None {noformat} Result after fix: {noformat} In [1]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") Import SQL Table progress: |██████████████████████████████████████████████████████████████████| 100% In [2]: imp Out[2]: id rev content ---- ----- --------- 1 1 mercury 2 8 uranus 3 7 neptun 4 4 mars 5 3 venus 6 6 saturn 7 5 jupiter 8 3 earth [8 rows x 3 columns] {noformat}
  exalate-issue-sync[bot] commented 1 year ago

  Michal Kurka commented: related SO post: https://stackoverflow.com/questions/53718511/h2o-import-table-issue-with-sql-server-2016/53731254

  FIY: Downgraded to regular priority because a workaround exists.

  exalate-issue-sync[bot] commented 1 year ago

  Sebastien Poirier commented: fixed import queries, now using following syntax, both fully supported from SQL Server 2012:

  • for column names retrieval (supported from SQLS 2008): bq. select top(1) from
  ;
  for data chunks retrieval (supported from SQLS 2012): bq. select from
  order by row_number() over (order by (select 0)) offset rows fetch next rows only;
  exalate-issue-sync[bot] commented 1 year ago

  Sebastien Poirier commented: tested locally with SQLServer running on local VM.

  starting h2o with MSSQL Server driver on classpath: bq. java -ea -XX:OnOutOfMemoryError="kill -9 %p" -cp ~/repos/h2o/ext/mssql-jdbc-7.0.0.jre8.jar:build/h2o.jar water.H2OApp -log_level INFO > ~/tmp/out.log 2>&1 &

  importing docs table from local test database {code:python} import h2o

  imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") {code}

  Result before fix: {noformat} In [2]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan")

  H2OServerError Traceback (most recent call last)

  in () ----> 1 imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in import_sql_table(connection_url, table, username, password, columns, optimize, fetch_mode) 478 if columns: 479 p["columns"] = ", ".join(columns) --> 480 j = H2OJob(api("POST /99/ImportSQLTable", data=p), "Import SQL Table").poll() 481 return get_frame(j.dest_key) 482 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in api(endpoint, data, json, filename, save_to) 101 # type checks are performed in H2OConnection class 102 _check_connection() --> 103 return h2oconn.request(endpoint, data=data, json=json, filename=filename, save_to=save_to) 104 105 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in request(self, endpoint, data, json, filename, save_to) 405 auth=self._auth, verify=self._verify_ssl_cert, proxies=self._proxies) 406 self._log_end_transaction(start_time, resp) --> 407 return self._process_response(resp, save_to) 408 409 except (requests.exceptions.ConnectionError, requests.exceptions.HTTPError) as e: /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in _process_response(response, save_to) 746 # Note that it is possible to receive valid H2OErrorV3 object in this case, however it merely means the server 747 # did not provide the correct status code. --> 748 raise H2OServerError("HTTP %d %s:\n%r" % (status_code, response.reason, data)) 749 750 H2OServerError: HTTP 500 Server Error: Server error java.lang.RuntimeException: Error: SQLException: Invalid usage of the option NEXT in the FETCH statement. Failed to connect and read from SQL database with connection_url: jdbc:sqlserver://192.168.59.162;databaseName=test; Request: None {noformat} Result after fix: {noformat} In [1]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") Import SQL Table progress: |██████████████████████████████████████████████████████████████████| 100% In [2]: imp Out[2]: id rev content ---- ----- --------- 1 1 mercury 2 8 uranus 3 7 neptun 4 4 mars 5 3 venus 6 6 saturn 7 5 jupiter 8 3 earth [8 rows x 3 columns] {noformat}
  hasithjp commented 1 year ago

  JIRA Issue Migration Info

  Jira Issue: PUBDEV-6123 Assignee: Sebastien Poirier Reporter: Jake Rapinett State: Resolved Fix Version: 3.22.0.3 Attachments: Available (Count: 1) Development PRs: Available

  Linked PRs from JIRA

  https://github.com/h2oai/h2o-3/pull/3150 https://github.com/h2oai/h2o-3/pull/3198

  Attachments From Jira

  Attachment Name: h2o sql server error.txt Attached By: Jake Rapinett File Link:https://h2o-3-jira-github-migration.s3.amazonaws.com/PUBDEV-6123/h2o sql server error.txt

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.

[exalate-issue-sync[bot]]

Sebastien Poirier commented: tested locally with SQLServer running on local VM.

starting h2o with MSSQL Server driver on classpath: bq. java -ea -XX:OnOutOfMemoryError="kill -9 %p" -cp ~/repos/h2o/ext/mssql-jdbc-7.0.0.jre8.jar:build/h2o.jar water.H2OApp -log_level INFO > ~/tmp/out.log 2>&1 &

importing docs table from local test database {code:python} import h2o

imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") {code}

Result before fix: {noformat} In [2]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan")

H2OServerError Traceback (most recent call last)

in () ----> 1 imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in import_sql_table(connection_url, table, username, password, columns, optimize, fetch_mode) 478 if columns: 479 p["columns"] = ", ".join(columns) --> 480 j = H2OJob(api("POST /99/ImportSQLTable", data=p), "Import SQL Table").poll() 481 return get_frame(j.dest_key) 482 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in api(endpoint, data, json, filename, save_to) 101 # type checks are performed in H2OConnection class 102 _check_connection() --> 103 return h2oconn.request(endpoint, data=data, json=json, filename=filename, save_to=save_to) 104 105 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in request(self, endpoint, data, json, filename, save_to) 405 auth=self._auth, verify=self._verify_ssl_cert, proxies=self._proxies) 406 self._log_end_transaction(start_time, resp) --> 407 return self._process_response(resp, save_to) 408 409 except (requests.exceptions.ConnectionError, requests.exceptions.HTTPError) as e: /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in _process_response(response, save_to) 746 # Note that it is possible to receive valid H2OErrorV3 object in this case, however it merely means the server 747 # did not provide the correct status code. --> 748 raise H2OServerError("HTTP %d %s:\n%r" % (status_code, response.reason, data)) 749 750 H2OServerError: HTTP 500 Server Error: Server error java.lang.RuntimeException: Error: SQLException: Invalid usage of the option NEXT in the FETCH statement. Failed to connect and read from SQL database with connection_url: jdbc:sqlserver://192.168.59.162;databaseName=test; Request: None {noformat} Result after fix: {noformat} In [1]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") Import SQL Table progress: |██████████████████████████████████████████████████████████████████| 100% In [2]: imp Out[2]: id rev content ---- ----- --------- 1 1 mercury 2 8 uranus 3 7 neptun 4 4 mars 5 3 venus 6 6 saturn 7 5 jupiter 8 3 earth [8 rows x 3 columns] {noformat}

[exalate-issue-sync[bot]]

Michal Kurka commented: related SO post: https://stackoverflow.com/questions/53718511/h2o-import-table-issue-with-sql-server-2016/53731254

FIY: Downgraded to regular priority because a workaround exists.

[exalate-issue-sync[bot]]

Sebastien Poirier commented: fixed import queries, now using following syntax, both fully supported from SQL Server 2012:

• for column names retrieval (supported from SQLS 2008): bq. select top(1) from

[exalate-issue-sync[bot]]

Sebastien Poirier commented: tested locally with SQLServer running on local VM.

starting h2o with MSSQL Server driver on classpath: bq. java -ea -XX:OnOutOfMemoryError="kill -9 %p" -cp ~/repos/h2o/ext/mssql-jdbc-7.0.0.jre8.jar:build/h2o.jar water.H2OApp -log_level INFO > ~/tmp/out.log 2>&1 &

importing docs table from local test database {code:python} import h2o

imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") {code}

Result before fix: {noformat} In [2]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan")

H2OServerError Traceback (most recent call last)

in () ----> 1 imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in import_sql_table(connection_url, table, username, password, columns, optimize, fetch_mode) 478 if columns: 479 p["columns"] = ", ".join(columns) --> 480 j = H2OJob(api("POST /99/ImportSQLTable", data=p), "Import SQL Table").poll() 481 return get_frame(j.dest_key) 482 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/h2o.py in api(endpoint, data, json, filename, save_to) 101 # type checks are performed in H2OConnection class 102 _check_connection() --> 103 return h2oconn.request(endpoint, data=data, json=json, filename=filename, save_to=save_to) 104 105 /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in request(self, endpoint, data, json, filename, save_to) 405 auth=self._auth, verify=self._verify_ssl_cert, proxies=self._proxies) 406 self._log_end_transaction(start_time, resp) --> 407 return self._process_response(resp, save_to) 408 409 except (requests.exceptions.ConnectionError, requests.exceptions.HTTPError) as e: /Users/seb/repos/h2o/h2o-3/h2o-py/h2o/backend/connection.pyc in _process_response(response, save_to) 746 # Note that it is possible to receive valid H2OErrorV3 object in this case, however it merely means the server 747 # did not provide the correct status code. --> 748 raise H2OServerError("HTTP %d %s:\n%r" % (status_code, response.reason, data)) 749 750 H2OServerError: HTTP 500 Server Error: Server error java.lang.RuntimeException: Error: SQLException: Invalid usage of the option NEXT in the FETCH statement. Failed to connect and read from SQL database with connection_url: jdbc:sqlserver://192.168.59.162;databaseName=test; Request: None {noformat} Result after fix: {noformat} In [1]: imp = h2o.import_sql_table(connection_url="jdbc:sqlserver://192.168.59.162;databaseName=test;", table="docs", username="mac", password="allan") Import SQL Table progress: |██████████████████████████████████████████████████████████████████| 100% In [2]: imp Out[2]: id rev content ---- ----- --------- 1 1 mercury 2 8 uranus 3 7 neptun 4 4 mars 5 3 venus 6 6 saturn 7 5 jupiter 8 3 earth [8 rows x 3 columns] {noformat}

[hasithjp]

JIRA Issue Migration Info

Jira Issue: PUBDEV-6123 Assignee: Sebastien Poirier Reporter: Jake Rapinett State: Resolved Fix Version: 3.22.0.3 Attachments: Available (Count: 1) Development PRs: Available

Linked PRs from JIRA

https://github.com/h2oai/h2o-3/pull/3150 https://github.com/h2oai/h2o-3/pull/3198

Attachments From Jira

Attachment Name: h2o sql server error.txt Attached By: Jake Rapinett File Link:https://h2o-3-jira-github-migration.s3.amazonaws.com/PUBDEV-6123/h2o sql server error.txt