search

Stratio / deep-spark

Connecting Apache Spark with different data stores [DEPRECATED]
http://stratio.github.io/deep-spark
Apache License 2.0
197 stars 42 forks source link

jdbc sql server does not work #14

Closed lockwobr closed 8 years ago

lockwobr commented 9 years ago

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

mafernandez-stratio commented 8 years ago

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