We recently had the same requirement, so knocked up the below code:
function StrFormat(template, args())
dim value
dim result: result = template
dim i: i = 0
for each value In args
result = Replace(result , "{" & i & "}", value)
i = i + 1
next
StrFormat = result
end function
function CreateConnectionString(dbInstance, dbCatalog, dbUser, dbPass)
if dbUser > "" then
CreateConnectionString = StrFormat(connectionStringSqlCredentials, Array(dbInstance, dbCatalog, dbUser, dbPass))
else
CreateConnectionString = StrFormat(connectionStringWinCredentials, Array(dbInstance, dbCatalog))
end if
end function
function ExecuteSqlReturnScalar(connectionString, query)
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
con.Open connectionString
rs.Open query, con
ExecuteSqlReturnScalar = rs.Fields(0).Value
Set rs = Nothing
Set con = Nothing
end function
public function RunSql(dbInstance,dbCatalog,user,pass,query,pipeDelimArgs)
dim args: args = split(pipeDelimArgs,"|")
RunSql = ExecuteSqlReturnScalar(CreateConnectionString(dbInstance,dbCatalog,user,pass), StrFormat(qry, args))
end function
This code is added to TestComplete as a script.
We call the code with the following arguments (line breaks added for improved readibility):
"dbServer\instanceName"
, "myDatabase"
, "sqlUser"
, "sqlPassword"
, "select sum(Amount)
from CustomerInvoiceTable
where CustomerId='{0}'
and TransactionDate='{1}'"
, "CUS123|2015-03-01"
i.e. the first 4 arguments specify the DB connection.
The fifth gives the SQL statement to run (with {n} placeholders for any string substitution)
The sixth is a string containing a pipe delimited set of parameters, used in the string substitution.
i.e.
"select sum(Amount)
from CustomerInvoiceTable
where CustomerId='{0}'
and TransactionDate='{1}'"
, "CUS123|2015-03-01"
Becomes
"select sum(Amount)
from CustomerInvoiceTable
where CustomerId='CUS123'
and TransactionDate='2015-03-01'"
Taking this approach means
- We have 1 script for all (mssql) database queries (where we only want 1 scalar value result).
- We can point it at any database; controlling the database connection details within TestComplete itself (i.e. putting in variables to hold these).
- We can keep a library of boilerplate SQLs also in TestComplete variables; with the SQL being easily readible.
- We can easily add variables in to the SQL; for the sixth parameter we just use TestComplete's code expression to concatenate our variables with pipe separators.
- We then use the Last Command Result to get the script's result back into a variable.
Hope this helps you.
