Groovy insert and select query together using sql server database

Question

I have some issues executing the sql server query in groovy code. My requirement is , I need to read data from a table ABC.customer and store the data into a temporary table test.

My query is :

DECLARE @throughDate datetime,
        @startDate datetime
DECLARE @test TABLE (
  test_id char(50)
)
SET @throughDate = '5-27-2015'
SET @startDate = DATEADD(YEAR, -1, @throughDate)
INSERT INTO @test
  SELECT
    test_id
  FROM ABC.customer
  WHERE ISNULL(create_date, '1-1-1900') BETWEEN @startDate AND @throughDate
  AND DATEDIFF(MINUTE, ISNULL(create_date, '1-1-1900'), ISNULL(last_login, '1-1-1900')) < 1

I don't have connection issues. I can query other tables and data. But the combination of insert and select is not working for me.

My groovy code is :

def sql=Sql.newInstance("jdbc:sqlserver://localhost","test","test","com.microsoft.sqlserver.jdbc.SQLServerDriver")


sql.eachRow("""DECLARE  @throughDate DATETIME, @startDate DATETIME
DECLARE @test TABLE(test_id CHAR(50))
SET @throughDate = '5-27-2015' 
SET @startDate = DATEADD(YEAR,-1,@throughDate) 
INSERT INTO @test  SELECT test_id FROM ABC.customer  
WHERE ISNULL(create_date,'1-1-1900') BETWEEN @startDate AND @throughDate  
AND DATEDIFF(minute,ISNULL(create_date,\'1-1-1900\'),ISNULL(last_login,\'1-      
1-1900\')) < 1
select test_id FROM @test
"""){
   println "Value is ${it}"
 }

The code is throwing the following exception :

Caught: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:800) 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 DataVisualization.SqlServerTest.main(SqlServerTest.groovy:104)

It would be great if some one help me to resolve this issue.

Thanks.

Tim3880 Tim3880 · Accepted Answer · 2015-06-04T16:30:04

Try add "SET NOCOUNT ON" in your query statement before the first SET:

DECLARE  @throughDate DATETIME, @startDate DATETIME
DECLARE @test TABLE(test_id CHAR(50))
SET NOCOUNT ON
SET @throughDate = '5-27-2015'

sql-server will print out some text messages for each insert/update/select statement without the "NOCOUNT", which break your record set.

Groovy insert and select query together using sql server database

1 Answers