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