Hi.I have let Test complete run a query in SQL Server. It is a select count(*) query which I can successfully run and has just one row and column as result. Now I want to read this result and save it to a variable in Test complete. Can anyone suggest a solution please?
GetSQLFieldValue('select * from AusOptions WHERE VBName = ''drugScan'' ' ,'VALUE',Project.Variables.ConnectionString);
function DBCommand(AConnectionString, sql: string, action: string;
IsLogRowsAffected, MinTrueValue: integer = 1; ForceDefaultDB: Boolean = false): OleVariant;
var
qry: OleVariant;
qryDetails: string;
begin
qry := ADO.CreateADOQuery;
if ForceDefaultDB or
((Project.Variables.VariableExists('GSDBConnectionString')) and
(AConnectionString = Project.Variables.GSDBConnectionString)) then
qry.ConnectionString := AConnectionString
else
qry.ConnectionString := CorrectConnectionString(AConnectionString);
qry.SQL := sql;
try
qryDetails := 'Connection String:' + Crlf + qry.ConnectionString + Crlf + Crlf +
'SQL:' + Crlf + sql;
if aqString.ToLower(action) = 'query' then //query
begin
qry.open;
result := qry;
end
else //execute
begin
qry.ExecSQL;
if IsLogRowsAffected then //return true only if any record updated/deleted
begin
result := qry.RowsAffected >= MinTrueValue;
if result then
Log.Message('Executing SQL below successful. ' + VarToStr(qry.RowsAffected) + ' row(s) affected.', qryDetails)
else
Log.Warning('Executing SQL below successful. ' + VarToStr(qry.RowsAffected) + ' row(s) affected.', qryDetails); //0 row affected
end
else //return true regardless if any record updated/deleted
result := true;
CloseQuery(qry);
end;
except
Log.Error('Executing SQL below failed.', qryDetails + Crlf + Crlf + 'Error:' + Crlf + ExceptionMessage);
result := false;
end;
end;
function GetSQLFieldValue(SQL, FieldName, ConnStr: string): OleVariant;
var
qry: OleVariant;
begin
result := false;
qry := DBCommand(pass all params)
if VarType(qry) = 9 then //object; boolean = 11
result := qry.FieldByName(FieldName).AsString;
CloseQuery(qry);
end;
What did you use to run the query? Did you use ADO connection object?
Assuming you've used an ADO connection to run the query, you should be getting a recordset object back if the query runs successfully.
https://msdn.microsoft.com/en-us/library/ms681510(v=vs.85).aspx
Personally, I find it easier to dump the field values into an array and pass them back that way. Recordset objects are bit klunky in terms of their handling. However, you may sometimes need to go through it field by field. Depends on the data types and what you want to do with them. For example, if I get a field back thats a bit (bool) type, it returns "0" as a string for FALSE (fine), but you get "-1" (or "not zero") back for a TRUE. Which is not ideal as viewed through SQL manager (what all our testers use), these fields show a "1" for true. So I have to check the field types before string conversion and explicity filter out the bit fields to return "1" if they are true.
