Forum Discussion

jgoetz's avatar
jgoetz
Occasional Contributor
9 years ago

Query database with VBScript help please...

Some help would be appreciated please.  I am trying to write a VBScript to query a database and return a result to the log, but I am having trouble getting the result back.  I am newish to VBScript so not sure what the code needs to be. 

 

Here is what I have so far.  At Log.Message Qry it is returning a blank value and I am sure there needs to be more in that code, I just don't know what.

 

Also, I am using the TestComplete ADO example, but don't have to if you have an easier way to do it.  Eventually, I would feed into the sub the query to be run and it will always only return one value from the database, like get the max number or look up the id# for the customer where name = "ABC corp", etc. 

 

Sub DBquery

' Create a query

Set Qry = ADO.CreateADOQuery

' Specify the connection string

Qry.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=Qad;Persist Security Info=True;User ID=DBQA;Data Source=FJO"

' Specify the SQL expression

Qry.SQL = "SELECT MAX(PCMP.TAX_IDENTIFIER.TAX_ID_NO) FROM PCMP.TAX_IDENTIFIER"

' Execute the query

Qry.Open

' Process results and insert data into the test log

Qry.First

While Not Qry.EOF

Log.Message Qry

Qry.Next

Wend

' Closes the query

Qry.Close 

 

 

End Sub
  • Quick question: Is there a code language that you are familiar with? TestComplete offers several code languages in addition to VBScript so you might be able to find one that you can work with. Just a suggestion.


    As for your specific problem, you have EVERYTHING correct... except for your logging.

    The reason being is that you are sending the Qry object to your log rather than a data field from the query. This is why it is returning blank because there is no string equivalent for what you are sending.

    Instead, do the following: 

    Log.Message Qry.Field(0).Value

    Your SQL query you are sending is returning a data record set with only one column/field so you only need to return the value of the first field (index 0). That code should return to your log the value of the first field of every row in your result set.

    Let me know if this helps.

    FYI, if you type "Qry." (note the period), you should be able to get an intellisence/code completion window showing your options off of the Qry object. If not, after typing the period, press "CTRL-SPACE" and that should get you the same thing. BIG help if you're not familiar with the objects.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor

    Quick question: Is there a code language that you are familiar with? TestComplete offers several code languages in addition to VBScript so you might be able to find one that you can work with. Just a suggestion.


    As for your specific problem, you have EVERYTHING correct... except for your logging.

    The reason being is that you are sending the Qry object to your log rather than a data field from the query. This is why it is returning blank because there is no string equivalent for what you are sending.

    Instead, do the following: 

    Log.Message Qry.Field(0).Value

    Your SQL query you are sending is returning a data record set with only one column/field so you only need to return the value of the first field (index 0). That code should return to your log the value of the first field of every row in your result set.

    Let me know if this helps.

    FYI, if you type "Qry." (note the period), you should be able to get an intellisence/code completion window showing your options off of the Qry object. If not, after typing the period, press "CTRL-SPACE" and that should get you the same thing. BIG help if you're not familiar with the objects.

    • jgoetz's avatar
      jgoetz
      Occasional Contributor

      Thank You, Thank You, Thank You!!!  That worked!  I figured it was something like that. 

       

      Unfortunately, VBScript is the one that I am most familiar with, sad to say.  :)  But I only have basic skills, so still learning.