Groovy SQL.execute help

[alibaba82]

Hello,
On your example at
http://www.eviware.com/index.php?option ... view&id=57
you have a groovy statement
sql = Sql.newInstance("jdbc:mysql://localhost:3306/eviware_demo_datainsert", "evidemo", "evipass", "com.mysql.jdbc.Driver")

I tried to do something similar but i get an error when I try to run the groovy script. I use the same parameters in the datasource step without any problem. Can you tell me if my groovy script is wrong.

DataSource parameters
Driver-  com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection String
jdbc:sqlserver://:1433;databaseName=;user=;password=;

corresponding sql.execute statement:
sql = Sql.newInstance("jdbc:sqlserver://:1433/", "", "","com.microsoft.sqlserver.jdbc.SQLServerDriver")

when I run the above groovy statement, I get the following error.

Thu May 24 18:12:46 PDT 2007:ERROR:com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/is not valid.

Can you tell me what I am doing wrong.

Thanks

Ali

[NiclasReimertz]

No,

It looks perfectly fine.

Hmm, what happens if you Telnet to the database?

If you choose start/run... and write cmd
and then write "telnet computer port" (for example "telnet srv-myServer 1433" or "telnet localhost 1433") does it connect?

/niclas

[alibaba82]

Ole,
I tried 'telnet and it seemed like it connected.

Have I specified the password correctly. I think '$' is escape character in groovy but it is also part of my password. my password (which is changed a little is 'passdev1$'. The actual statement which gives the error is below. I have changed some sensitive information.

--------------------------------------------------
import groovy.sql.Sql
sql = Sql.newInstance("jdbc:sqlserver://exampledb1:1433/intwebp2", "username", "passdev1\$","com.microsoft.sqlserver.jdbc.SQLServerDriver")

Tue May 29 16:37:41 PDT 2007:ERROR:com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/intwebp2 is not valid.

----------------------------------------------------------------


Ali

[NiclasReimertz]

I digged into it, and it's really weird. It seems there is a conflict between Groovy SQL and MS SQL. I am not sure yet, but it seems that Groovy passes connection info wrong to SQl Server. Specifically, it sends the database name into the port number.

Here is my workaround.

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


sql.execute("insert into eviware_demo_datainsert.dbo.eviware_tb_datainsert (Author, NumberOfBooks) values (${AuthorName}, ${ResultCount})")

That is; I removed the database name from the connection string (jdbc:sqlserver://localhost:1898" insted of jdbc:sqlserver://localhost:1898/eviware_demo_datainsert" )

and moved it to the query itself

(eviware_demo_datainsert.dbo.eviware_tb_datainsert (Author, NumberOfBooks) values (${AuthorName}, ${ResultCount})") instead of eviware_tb_datainsert (Author, NumberOfBooks) values (${AuthorName}, ${ResultCount})"))

Here is the full Groovy code to replace the code in the example:

--------------------------------------------------------------------------------

import groovy.sql.Sql

def authorStep = testRunner.testCase.getTestStepByName( "GetData" )
def resultStep = testRunner.testCase.getTestStepByName( "ResultProperties" )
    AuthorName = authorStep.getPropertyValue( "Author" )
ResultCount = resultStep.getPropertyValue( "TotalResults" )


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


sql.execute("insert into eviware_demo_datainsert.dbo.eviware_tb_datainsert (Author, NumberOfBooks) values (${AuthorName}, ${ResultCount})")

[NiclasReimertz]

DOH!

I thought this was the first i tried. Try the following:

import groovy.sql.Sql

def authorStep = testRunner.testCase.getTestStepByName( "GetData" )
def resultStep = testRunner.testCase.getTestStepByName( "ResultProperties" )
    AuthorName = authorStep.getPropertyValue( "Author" )
ResultCount = resultStep.getPropertyValue( "TotalResults" )

sql = Sql.newInstance("jdbc:sqlserver://localhost:1898;
databaseName=eviware_demo_datainsert;user=eviware;password=eviware;")

sql.execute("insert into dbo.eviware_tb_datainsert (Author, NumberOfBooks) values (${AuthorName}, ${ResultCount})")

What I've corrected is the connection string to be exactly like the one in the datasource tutorial.

/niclas