Forum Discussion

JamieBrydges's avatar
JamieBrydges
New Contributor
9 years ago

How do i return a table from a sql server query using Testcomplete?

Hi,

i'm trying to return a table from a sql server query.  I have the folowing method but when i look at the results that are return i get the error = error: cannot perfom this operation on a closed dataset: 

 

function ReturnSqlQuery(sqlQuery, connectionString)
{
try
{
var query = ADO.CreateADOQuery();
query.ConnectionString = connectionString;
query.SQL = sqlQuery;

query.Open();

var queryResult = query;

return queryResult;
}

finally
{
Log.Message("Closing the connection.");
query.Close();
}
}

  • Colin_McCrae's avatar
    Colin_McCrae
    9 years ago

    The key here is you're writing out all your returned results BEFORE closing the connection. (I drop them into an array rather than the log, but same thing.)

     

    Same deal.

     

    (I use both - query & execute. Mostly query. But I have to use execute to get back number of affected rows for some calls. It's not requrned as a parameter of a RecordSet object for a standard query. Which is annoying.)

  • ghuff2's avatar
    ghuff2
    Contributor
    9 years ago

    I've never used the CreateADOQuery. Here's the function I use to open a connection (it's in python):

     

    def open_db_conn(conn_str):
  db_conn = ADO.CreateADOConnection()
  db_conn.ConnectionString = conn_str
  db_conn.LoginPrompt = False
  db_conn.open()
  return db_conn

    Here's an example of using the connection to execute a query:

     

    def query_example():
  conn = open_db_conn('ImagineARealConnectionStringHere')
  
  query = 'SELECT * FROM Table'
  resultset = conn.Execute_(query)
  
  #Log all the records for a certain column 
  while not resultset.EOF:
    Log.Message(resultset.Fields.Item['ColumnName'].Value)
    resultset.MoveNext()

  conn.close()

    Obviously if you'd rather a function like yours that just returns a resultset you can modify the first function to do that. And I didn't wrap mine in a try block even though that's a smart thing to do.

    • Colin_McCrae's avatar
      Colin_McCrae
      Community Hero
      9 years ago

      The key here is you're writing out all your returned results BEFORE closing the connection. (I drop them into an array rather than the log, but same thing.)

       

      Same deal.

       

      (I use both - query & execute. Mostly query. But I have to use execute to get back number of affected rows for some calls. It's not requrned as a parameter of a RecordSet object for a standard query. Which is annoying.)

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    9 years ago

    From memory, you have to perform any actions on the RecordSet object (what your SQL query returns) before you close the connection.

     

    Normally, I just dump the results into an array and pass that back instead. If nothing else, it's a lot easier to work with than a RecordSet object and it's horrible pointer. And you can deal with it after the connection is closed of course.

     

    (Be careful with bit-fields though as these can return some funny results .....)