Forum Discussion

dwong_smartbear's avatar
dwong_smartbear
Occasional Contributor
7 years ago

Python - Using ADO and RecordSet in testcomplete scripts

Two questions I have regarding the ADO interface with Python:   1. When I do: rec_set = conn.Execute_("SELECT A, B FROM C WHERE D='E") ... there's no problem, but if I do:   rec_set = conn.Exec...
  • cunderw's avatar
    cunderw
    7 years ago

    Here is an approach I used for something similar. It basically will take a record set and build an array of objects with their properties being the column names / values. It's javascript, but shouldn't be hard to do a similar approach in python. 

     

    function recordToObjectArray(rec) {
  let retVal = [];
  try {
   while(!rec.EOF) {
      let currObj = {};
      for(var i = 0; i < rec.Fields.Count; i++) {
        currObj[rec.Fields.Item(i).Name] = rec.Fields.Item(i).Value;        
      }
      retVal.push(currObj);
      rec.MoveNext();
    }
  } catch(err) {
    Log.Warning("There was an error building object. See additional information", err.message + "\n" + err.stack);
  } finally {
    return retVal;
  }
}
dwong_smartbear's avatar
dwong_smartbear
Occasional Contributor
7 years ago

Hi tristaanogre thanks for your reply.

Yes I noticed all the examples I see are of this flavor (using my example):

 

rec_set = conn.Execute_("SELECT * FROM C WHERE D='E'")
rec_set.MoveFirst()
while not rec_set.EOF:
  Log.Message(rec_set.Fields.Item["A"].Value, rec_set.Fields.Item["B"].Value)
  rec_set.MoveNext()

 

But what if I don't want just the values for columns "A" and "B", but for all the columns.  Also, I'm still having trouble with the "SELECT *...." statement, which is strange.

  • cunderw's avatar
    cunderw
    Community Hero
    7 years ago

    Here is an approach I used for something similar. It basically will take a record set and build an array of objects with their properties being the column names / values. It's javascript, but shouldn't be hard to do a similar approach in python. 

     

    function recordToObjectArray(rec) {
  let retVal = [];
  try {
   while(!rec.EOF) {
      let currObj = {};
      for(var i = 0; i < rec.Fields.Count; i++) {
        currObj[rec.Fields.Item(i).Name] = rec.Fields.Item(i).Value;        
      }
      retVal.push(currObj);
      rec.MoveNext();
    }
  } catch(err) {
    Log.Warning("There was an error building object. See additional information", err.message + "\n" + err.stack);
  } finally {
    return retVal;
  }
}
    • dwong_smartbear's avatar
      dwong_smartbear
      Occasional Contributor
      7 years ago

      cunderw thanks! that was the solution.  Just for completeness this is translation in Python:

       

        l_recset = []
  rec_set.MoveFirst()
  while not rec_set.EOF:
    d_row = {}
    for i in range(rec_set.Fields.Count):
      d_row[rec_set.Fields.Item[i].Name] = rec_set.Fields.Item[i].Value
    l_recset.append(d_row)
    rec_set.MoveNext()
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    7 years ago

    To access all the columns, you need to include all the columns in your loop.  You will either need to designate each one individually or you will need to embed a for loop within the while loop to write out the values of the columns based upon index (0 to ColumnCount).  There is no way to say "Write out the row" as far as I know... you need to access the data in a record set via column.

     

    As for your select query problems.... might be helpful to see your EXACT code rather than the pseudo-code.  It could be some way that you're formatting stuff in the SQL query string that is not translating nicely.  

    • dwong_smartbear's avatar
      dwong_smartbear
      Occasional Contributor
      7 years ago

      tristaanogre thanks again for responding.  Here're my select statements, showing one that worked, and another one that didn't:

       

      This one works:

      rec_set = conn.Execute_("SELECT ACTIONTYPE, BID, TYPE, ETD FROM ADMD.TT_ADJUST WHERE ETD in ('AZ1014')")

       

      This one doesn't:

      rec_set = conn.Execute_("SELECT * FROM ADMD.TT_ADJUST WHERE ETD in ('AZ1014')")

       

      I get this exception:

       

      RuntimeError
      Unspecified error
      Error location:
      Unit: "pyADMD\pyADMD\Script\sandbox"
      Line: 33 Column: 1.
      • dwong_smartbear's avatar
        dwong_smartbear
        Occasional Contributor
        7 years ago

        tristaanogre I've narrowed down the problem with the "SELECT *...." statement.  So, it turns out if any of the columns in my TT_ADJUST table is of type TIMESTAMP(6), that exception will be thrown.

        The "solution" is to do a TO_CHAR conversion on a timestamp field, i.e. 'SELECT TO_CHAR(SENTDATE, 'YYYY-MM-DD') FROM ....'

        However, that means I can't blindly use "SELECT *" on any table, which is what I'd ideally want.  Is there another way to set a connection, cursor or recordset property to allow this conversion to happen automatically? From what I've found by searching, it was mentioned in https://docs.oracle.com/cd/E47955_01/win.121/e18594/using.htm that using a CursorLocation property was needed.  So I tried the following, but it still gave the same error on SELECT *:

         

        def test_oracle():
  conn = ADO.CreateADOConnection()
  conn_str = "Driver={%s};Server=%s;Uid=%s;Pwd=%s;" % (
                                Common.conf['GLOBAL.DB.DRIVER'],
                                Common.conf['GLOBAL.DB.SERVER'],
                                Common.conf['GLOBAL.DB.UID'],
                                Common.conf['GLOBAL.DB.PWD'])
  Log.Message("conn_str: " + conn_str)
  conn.ConnectionString = conn_str
  conn.LoginPrompt = False
  conn.Open()
  rec_set = ADO.CreateRecordset()
  rec_set.CursorLocation = 2
  rec_set = conn.Execute_("SELECT * FROM ADMD.TT_ADJUST WHERE ETD in ('AZ1014')")