May 12, 2011

Set a DataBase connection and run any queries from QTP

How to set a DB connection and to get a value from an "adodb" Data Base
Public Function DBSelect
set conn = createobject("adodb.connection")
conn.open "DSN=DSN_SERVERhere; UserID=userID; Password=password;"
set rs = createobject("adodb.recordset")
'get the date value from DB for an Event
rs.open "SELECT searchColumnHere FROM tableName WHERE columnName='value'", conn
eventsInDB = rs("searchColumnHere")
'eventsInDB = FormatNumber(eventsInDB, 2) - use this if you need to format the output nr
rs.close
DBSelect = eventsInDB 'here we are defining the output value
End Function


Or, if the query is defined somewhere outside, for e.g.:

getCol1data = UCASE("SELECT 'searchColumnHere' FROM tableName WHERE columnName='value'"'"searchColumnHere" is the code name of  the column which's value should be outputed. for e.g: CLBVAS (use from your select)
then the function can be wrote like this (I've added it within a class):

Public Function DBQuery
 DBQuery = New Query
End Function

Class Query
 Public Function DBSelect(sqlSelect, searchColumnHere)
  set conn = createobject("adodb.connection")
  conn.open "DSN=DSN_SERVERhere; UserID=userID; Password=password;"
  set rs = createobject("adodb.recordset")
  'get the date value from DB for an Event
  rs.open sqlSelect, conn
  eventsInDB = rs(searchColumnHere)
  'eventsInDB = FormatNumber(eventsInDB, 2) - use this if you need to format the output number
  rs.close
  DBSelect = eventsInDB 'here we are defining the output value
 End Function
End Class

now the function is called like this:
DBQuery.DBSelect getCol1data "searchColumnHere"  'the "getCol1data" has the value assigned to it upper
or:
DBSelect getCol1data "searchColumnHere"  'when there is no class defined

16 comments:

  1. try to change your template, its giving fell like annoying

    ReplyDelete
  2. what do you mean, it's about making the article more detailed or what?!

    ReplyDelete
  3. How would you change the script to be able to write to DB? e.g. Update table Set column=val where column=val

    Regards,

    ReplyDelete
    Replies
    1. Hi Ovidiu, so in my sample I have the "getCol1data" variable which calls a Select in DB:
      -->
      getCol1data = UCASE("SELECT 'searchColumnHere' FROM tableName WHERE columnName='value'") '"searchColumnHere" is the code name of the column which's value should be outputed. for e.g: CLBVAS (use from your select)
      <--
      you can use the "Update ..." sql function assigned to your variable instead of my "Select" and it should work well, give it a try and let me know.
      Regards!

      Delete
  4. Your information about qtp is really interesting. Also I want to know the latest new techniques which are implemented in qtp. Can you update it in your website?

    ReplyDelete
    Replies
    1. Hello my friend, I would really like to fulfill your request by doing that, but want to sadly tell you that I am not maintaining this web site almost at all anymore...
      This is mainly because I don't work with QTP (now FTP) for more that 2 years already..
      I'm going to try finding some guys on the iNet wishing to become part of editors team for this web site, and I hope that will work.
      You may want to be the first to try?!

      Delete
  5. Its best source for Setting a DB connection to run any queries from QTP

    By

    Sri Priya ( Web Designing Training in Chennai )

    ReplyDelete
    Replies
    1. Thanks Priya! I really appreciate it and glad that at the material here (even if there is not much of it) is useful! Enjoy!

      Delete
  6. Thanks for sharing this informative blog. Recently I did Digital Marketing Training in Chennai at a leading digital marketing company. It's really useful for me to make a bright career.

    ReplyDelete
    Replies
    1. Thank you John! I am glad that it still helps.

      Delete
  7. Hello there,

    I am attempting to computerize and run SQL questions through QTP.

    . Database Utility instrument introduced is TOAD

    . Database is Oracle

    Question:

    a) Do i have to compose 2 association stings, one to join with amphibian and one to database

    On the other hand

    I can straightforwardly compose an association string that interfaces with the database and run the questions.

    b) For whatever be the situation in the above inquiry,

    Would anyone be able to give me the settings i have to design in the Administrative Tools - > DNS Settings

    furthermore, the association string too ?

    Thanks,

    QTP Training in Chennai

    ReplyDelete
  8. Jharkhand Labour Department Recruitment 2016

    Thanks for sharing . Will come to visit again.Click here to find more information......

    ReplyDelete
  9. Thanks for sharing your ideas. Its really very useful for us.
    Regards
    QTP Training in Chennai

    ReplyDelete
  10. Thanks for Sharing the nice information.. It was very helpful for us.

    Hadoop Training Chennai
    Hadoop Training Bangalore

    ReplyDelete