Forum Discussion

swapnaliuk's avatar
swapnaliuk
Occasional Contributor
9 years ago

Test complete run a count(*) query in SQL server 2012.

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?

3 Replies

  • 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;
    
    

     

  • djadhav's avatar
    djadhav
    Regular Contributor

    What did you use to run the query? Did you use ADO connection object?

    • Colin_McCrae's avatar
      Colin_McCrae
      Community Hero

      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.