Ask a Question

Query database with VBScript help please...

SOLVED
jgoetz
Occasional Contributor

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
1 ACCEPTED SOLUTION

Accepted Solutions
tristaanogre
Community Hero

Re: Query database with VBScript help please...

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.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

2 REPLIES 2
tristaanogre
Community Hero

Re: Query database with VBScript help please...

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.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

jgoetz
Occasional Contributor

Re: Query database with VBScript help please...

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. 

cancel
Showing results for 
Search instead for 
Did you mean: