SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15
  1. #1
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Returning a results object from a function

    Can I return an SQL results object from a function in a vbsfile?

    I'm sure that I've done this is the past but can't seem to do it now.

    e.g

    In QTP Script

    results = runSomeDataBaseQuery()

    In vbs file

    Function runSomeDataBaseQuery()

    craete objects & connect to database, etc.
    Set objResults = objDB.Execute(select * from table)

    runSomeDataBaseQuery = objResults
    End Function


    I suppose I could return it as some kind of array but I'd rather use the results object within the script

  2. #2
    Advanced Member cgrzelka's Avatar
    Join Date
    Feb 2005
    Location
    Bordeaux, France
    Posts
    750
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    VBScript functions can return objects. The correct syntax in VBScript to assign an objet value to a variable is the Set statement. Thus :

    Function runSomeDataBaseQuery()
    ....
    Set runSomeDataBaseQuery = objDB.Execute ("select * from table")
    End Function
    ...
    Set result = runSomeDataBaseQuery()

  3. #3
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    Cool, I'll try this

  4. #4
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    Not having any success I'm afraid.

    Now I know that this is because I'm closing the objects and setting them to nothing at the end of function. But surely I have to do this else I'll have issues with regards to my objects not being closed.

    here is my code;

    <font class="small">Code:</font><hr /><pre> 'Get RecordSet of emails for a given proposal
    Function DBreturnEmailRecordSet(strDBProposalID)

    Set objDB = CreateObject("ADODB.Connection")

    connectedToDataBase = DBconnectToDatabase(objDB)

    strDBQuery = "select * from tester_proposal_email_view where proposal_id = " &amp; strDBProposalID &amp; " order by created_date desc"

    Set objResults = objDB.Execute(strDBQuery)
    Set DBreturnEmailRecordSet = objResults
    objResults.Close
    objDB.Close
    Set objResults = Nothing
    Set objDB = Nothing

    End Function </pre><hr />

  5. #5
    Senior Member
    Join Date
    Oct 2005
    Location
    London, U.K
    Posts
    1,804
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    I wouldn't worry about objects not being closed unless they are still being displayed in the task manager.

    Does it work otherwise? I would just close the connection.

  6. #6
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    So I still set the objects to Nothing but just forget the close?

    How will this impact if I do any subsequent SQL?

  7. #7
    SQA Knight
    Join Date
    Jul 1999
    Location
    Grand Rapids, MI
    Posts
    3,727
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    Robbie wrote:

    Function DBreturnEmailRecordSet(strDBProposalID)

    snip...

    Set objResults = blah
    Set DBreturnEmailRecordSet = objResults

    snip...

    Set objResults = Nothing

    snip

    End Function

    I believe that...

    At the 2nd statement above the objResults and DBreturnEmailRecordSet variables, which are pointers (<u>not</u> variables containing an object] both reference the same, valid object. But when you set objResults to Nothing, you are destroying the object, not the pointer to the object. So you can't do this if you need to use the object returned from the function. If it needs to be done at all (and I rarely destroy objects, let built-in garbage collection do this...), let the function caller do that after they use the object.

    -Hope this helps, Terry

  8. #8
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    I've done a bit of experimenting, and it appears that "objResults.Close" does destroy DBreturnEmailRecordSet but "Set objResults = Nothing" doesn't appear to have any impact.

  9. #9
    SQA Knight
    Join Date
    Jul 1999
    Location
    Grand Rapids, MI
    Posts
    3,727
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    I probably should have looked more closely at what objects you were referencing. I see now that you are doing some SQL stuff that I don't work with.

    My comments about destroying a vbScript object when there are multiple references to it, is one level of generic object persistance, while the object in your example presents an SQL-unique persistance issue.

    While I believe my comments about generic objects and pointers are correct, I do not now think they are useful in this thread. Sorry for tossing in some static on this matter, as the issues you are noodling over are more complex than the simple suggestions my previous posting provided.

    -Terry

  10. #10
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Returning a results object from a function

    That's cool Terry. Objects are a minefield to me at the best of times!

 

 
Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.71%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:20 AM.

Copyright BetaSoft Inc.