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.
