cancel
Showing results for 
Search instead for 
Did you mean: 

dynamically run the DB query in keyword driven framework

SOLVED
Frequent Contributor

dynamically run the DB query in keyword driven framework

I am trying to pass the sql stament in the excel. But getting "wrong number of arguments or invalid property assignment"

1 ACCEPTED SOLUTION

Accepted Solutions
Community Hero

Re: dynamically run the DB query in keyword driven framework

Correct... because there is no "Item" sub-property off of the field by name.

 

If you're going to loop via the index, then you should use

 

Qry1.FieldByNumber(i).Value

 

note:

 

In the following help file for the CreateADOQuery method (https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html) there is a link to the wiki for the Delphi ADO component objects which contains information for all fields and methods for the various objects.  This is going to be your resource for any objects that return the Delphi VCL ADO objects.


Robert Martin
[Community Expert Group]
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

9 REPLIES 9
Frequent Contributor

Re: dynamically run the DB query in keyword driven framework

after chaning the code to 

Qry1.SQL=value it worked. But i need help to display the results that query is returning in log results. How can i achieve that.

 

Function Sq_Qry(value )

 

'Create a query
'
Set Qry1 = ADO.CreateADOQuery

'Specify the connection string

Qry1.ConnectionString = "Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=xxx;Password=exxx;Data Source=xxx"

'Specify the SQL expression

'Qry1.SQL="Select * FROM schema.tablename"


Qry1.SQL=value


Set Qryresult = qry1
'Log.Message(Qryresult)
'Execute the query

Qry1.Open()
'Process results and insert data into the test log

Qry1.First()

'while Not Qry1.EOF

'return Qryresult
'Log.Message(Qry1.SQL.Value)
'Qry1.Next()

'Wend

'Closes the query
Qry1.Close()

End Function

Community Hero

Re: dynamically run the DB query in keyword driven framework

Try

 

Qry1.Fields.Item('<column name>').Value

 

This should return the contents of the column name indicated.


Robert Martin
[Community Expert Group]
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
Frequent Contributor

Re: dynamically run the DB query in keyword driven framework

Thanks Robert,

I tried adding that and its giving me Smiley Surprisedbject does not support this method or poerpoerty "Qry1.Fields"

 

Updated my code as below

 

Function Sq_Qry(value )
'Create a query
'
Set Qry1 = ADO.CreateADOQuery

'Specify the connection string

Qry1.ConnectionString = "Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=XXX;Password=XXX;Data Source=XXX"
'Specify the SQL expression
'Qry1.SQL="Select * FROM schema.table"
Qry1.SQL=value

'Execute the query
Qry1.Open()
'Process results and insert data into the test log
Qry1.First()

'Obtains field names

while Not Qry1.EOF
for i=0 to Qry1.Fields.Count
Set test=Qry1.Fields.Item(i).Value
Next


'Log.Message(Qry1.SQL.Value)
Qry1.Next()
Wend
Log.Message(test)
'Closes the query
Qry1.Close()
errCode =0
End Function

Community Hero

Re: dynamically run the DB query in keyword driven framework

My bad... try  instead

 

Qry1.FieldByName('<column name>').Value


Robert Martin
[Community Expert Group]
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
Frequent Contributor

Re: dynamically run the DB query in keyword driven framework

Robert,

I tried with that also same error. should i use ADOConnection instead of query?

 

tried with field,fields,fieldbuname,fieldcount

it is not recongnising qry1.Fields

Community Hero

Re: dynamically run the DB query in keyword driven framework

Right... notice that I changed the code to not use Qry1.Fields at all.  

 

It's Qry1.FieldByName instead... that's the proper code for that object.

https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html


Robert Martin
[Community Expert Group]
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
Frequent Contributor

Re: dynamically run the DB query in keyword driven framework

Thanks Robert,

It works fine if i give the specfic column name and it is displaying that column name values. 

but in my query i am giving select * from table name which will pul all column values right?

so in the log results too i want all the column values

if i given Log.message Qry1.FieldByName.item(i).Value it is not working.

Community Hero

Re: dynamically run the DB query in keyword driven framework

Correct... because there is no "Item" sub-property off of the field by name.

 

If you're going to loop via the index, then you should use

 

Qry1.FieldByNumber(i).Value

 

note:

 

In the following help file for the CreateADOQuery method (https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html) there is a link to the wiki for the Delphi ADO component objects which contains information for all fields and methods for the various objects.  This is going to be your resource for any objects that return the Delphi VCL ADO objects.


Robert Martin
[Community Expert Group]
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

Highlighted
Frequent Contributor

Re: dynamically run the DB query in keyword driven framework

Thanks Robert,

below code worked

Do while Not Qry1.EOF
for i=0 to Qry1.FieldCount -1
'Log.Message("Column Name:" &Qry1.Field(i).FieldName & " |"&" " &"Column Value:"&Qry1.Field(i).Value &"|"& vbNewLine

Next

Qry.Next

Loop