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...
  • 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.)