database connection and using in script

Archive
1

Hello,

I have added DB related details in project settings -> database screen. It shows connection successful.

Now, I want to use that in my script to execute query, get result at script level and use it in next / further test steps.

Can you please help to share link / example for the same?

Thank you in advance.

Regards,

Ketul Shah

1 Like
2

I connect to SQL server and run a stored procedure to return the values I need. In this example I create a connection, call the Stored procedure, then clean up the response to a string I can use.

This is setup as a Custom keyword so I can simply call it when needed. You could add a variable for the Stored Procedure name, if you have many different call to make.

import groovy.sql.Sql //you need this

@Keyword
def Useyourname()

{
def configuration = [
‘dbInstance’ : ‘Server Name’,
‘dbPort’ : 1433,
‘dbName’ : ‘DBname’,
‘dbUser’ : ‘username’,
‘dbPass’ : ‘password’
]
def sql = Sql.newInstance(
“jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};” +
“databaseName=” + configuration.dbName,
configuration.dbUser, configuration.dbPass,
‘com.microsoft.sqlserver.jdbc.SQLServerDriver’)

new Sql(sql).call (“Sequence;”) //name of my stored proc
def res = (sql.rows(“select WebCallSequence from WebCallSequence;”).toString())
def numb = res.substring(res.lastIndexOf(":") + 1) //cleaning the return string
def counter = (numb.substring(0, numb.length()-2)) //cleaning the return string
return counter //return the value

}

3

Hi I am wondering about this too. I set up the db user, pwd and connection url in Project Settings>Database. How do i call this global variable from within my code?

4

Hafiz said:

Hi I am wondering about this too. I set up the db user, pwd and connection url in Project Settings>Database. How do i call this global variable from within my code?

WebUI.setText(findTestObject(‘Username’), GlobalVariable.Username)

WebUI.setText(findTestObject(‘Password’), GlobalVariable.Password)

5

Mark Peterson said:

I connect to SQL server and run a stored procedure to return the values I need. In this example I create a connection, call the Stored procedure, then clean up the response to a string I can use.

This is setup as a Custom keyword so I can simply call it when needed. You could add a variable for the Stored Procedure name, if you have many different call to make.

import groovy.sql.Sql //you need this

@Keyword
def Useyourname()

{
def configuration = [
‘dbInstance’ : ‘Server Name’,
‘dbPort’ : 1433,
‘dbName’ : ‘DBname’,
‘dbUser’ : ‘username’,
‘dbPass’ : ‘password’
]
def sql = Sql.newInstance(
“jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};” +
“databaseName=” + configuration.dbName,
configuration.dbUser, configuration.dbPass,
‘com.microsoft.sqlserver.jdbc.SQLServerDriver’)

new Sql(sql).call (“Sequence;”) //name of my stored proc
def res = (sql.rows(“select WebCallSequence from WebCallSequence;”).toString())
def numb = res.substring(res.lastIndexOf(“:”) + 1) //cleaning the return string
def counter = (numb.substring(0, numb.length()-2)) //cleaning the return string
return counter //return the value

}

Hello Mark,

I want to execute Stored Procedure by passing parameter value in it. How to execute in above script? Database checkpoint with such stored procedure not working for me.