Stratio/deep-spark

jdbc sql server does not work

lockwobr opened this issue · 1 comments

Two issues that I found connecting to sql server are:

The connetion string that is created with fluet builder do not work, you have to set it like this

val conn_str = "jdbc:sqlserver://xxxxxxxx.database.windows.net:1433;database=master;user=insights@oxub00w1l9;password=xxxxxxxxxx;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

val tableConfig: JdbcDeepJobConfig[Cells] = JdbcConfigFactory
  .createJdbc
  .database("master") 
  .table("information_schema.tables")
  .driverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver")
  .connectionUrl(conn_str)
  .initialize

I found this hard to fingure out how to use, if you do not set the connection url it will create the connection string that will not work.

The second thing that I found that does not work right is the query that the code above creates is not valid.

tableConfig.getQuery() gets yous you a query of
SELECT information_schema.tables.* FROM insights-playland-db.information_schema.tables information_schema.tables

if you execute this query even without spark you get an error of:

val conn = DriverManager.getConnection(tableConfig.getConnectionUrl, tableConfig.getUsername, tableConfig.getPassword)
val statement: Statement = conn.createStatement
val query: SelectQuery = tableConfig.getQuery

val resultSet = statement.executeQuery(query.toString)

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at TestSQLStatements$.main(TestSQLStatements.scala:40)
at TestSQLStatements.main(TestSQLStatements.scala)

I also found that if the database name has a "-" in then you get this error

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at TestSQLStatements$.main(TestSQLStatements.scala:40)
at TestSQLStatements.main(TestSQLStatements.scala)

I tried on a database that was not master that does not "-" in the name and on a table that does not "." in the table name and it created a query like this

 SELECT errorlevelcounts.* FROM playland2.errorlevelcounts errorlevelcounts

this query give an error of
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'playland2.errorlevelcounts'.

if you remove the database name of playland2

 SELECT errorlevelcounts.* FROM errorlevelcounts errorlevelcounts

this query is valid in sql server

Hi Brian,

Currently, the development of Stratio Deep is deprecated. However, Stratio Crossdata has inherited part of the features of Stratio Deep. Please, visit link in order to get more information.

Regards