SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 10 of 10
  1. #1
    Apprentice
    Join Date
    Jan 2008
    Location
    San Jose, CA
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Problem in executing sql query

    Hi guys,
    I need to run some sql queries from QTP in my tests. There are two problems that I'm facing. I searched and searched but couldnt find anything useful.I am reading the sql queries from a text file.

    1) The query is too long and the variable in which I am storing the query, is chopping it off from middle.

    2) I need to pass a parameter from the query. This works when I paste the query in my script itself. But when i try doing the same from a text file, it is reading the entire thing as a string.
    eg: strQuery = "select number from table where name = '"&name&"' AND a=b"
    This one works. But when i paste the same in a text file, this is how its being read:
    strQuery = "select number from table where name = '""&name&""' AND a=b"
    And thus its not returning any data.

    Any help/ suggestions?


    Thanks!
    Priyank

  2. #2
    Senior Member
    Join Date
    Feb 2007
    Location
    Pittsburgh, USA
    Posts
    429
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    Try this.

    strQuery = "select number from table where name='"&name&"' AND a='b'"
    -Reddy

  3. #3
    Apprentice
    Join Date
    Jan 2008
    Location
    San Jose, CA
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    Hi there,

    Thanks for your reply. Im sorry i did not state my problem clearly. I am attaching a copy of the text file which contains the sql query that i need to execute. The parameter '&code' is what i need the query to pick from my script, while executing.

    Also, to my other question, how can i read an entire query which is perhaps exceeding the size limit and hence getting chopped? I checked the length of the string that is being read - it gives a max length of 1972 chars. nothing beyond that..

    Thanks once again.
    Priyank
    Attached Files Attached Files

  4. #4
    Member
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    I don't know how to fix your string length problem off the top of my head, however, in regards to your parameter issue, I have in my experience noticed that sometimes QTP has a problem when there are no spaces around the '&'. Also, since the string you are passing has a parameter, remember that in your code you will need to use the eval function to convert it from a string to an actual comand to execute.

    So, for example:
    eval("strQuery = ""select number from table where name='" & name & "' AND a='b'""")

    or in your case
    the string in your file would look like:
    strQuery = ""select number from table where name='" & name & "' AND a='b'""

    the code in your QTP script would like like:
    eval(stringFromFile)

    Note: the number of " is important. To ignore " as a start/end of a string, you need to put another " before it. So for a string to output ", you need to use "".
    Eleonora Faraone
    Melbourne VIC Australia

    eleonorafaraone@yahoo.com.au

  5. #5
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    Try this

    <font class="small">Code:</font><hr /><pre>
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.OPenTextFile("C:\MySQlFile.SQL",1)

    SQLText = oFile.ReadAll

    oFile.Close
    Set oFile = Nothing

    Set FSO = Nothing

    SQLText = Replace(SQLText, """&amp;code&amp;""","MyValue")

    Msgbox SQLText 'This might not show you the whole query as msgbox has a limit
    </pre><hr />

  6. #6
    Apprentice
    Join Date
    Jan 2008
    Location
    San Jose, CA
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    Thanks Eleonora and Tarun!

    Ok this one worked. But the text limit problem still remains. I got another query which is like huge.. It just does not read the entire thing - I even tried to split it into two variables and then pass them in the Execute statement. Still doesnt work. Im attaching the query for reference.

    Thanks!
    Priyank
    Attached Files Attached Files

  7. #7
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    This not a huge query. i have executed queries 10 times huge than this. Involving union of 30 odd queries.

  8. #8
    Apprentice
    Join Date
    Jan 2008
    Location
    San Jose, CA
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    But then why is it failing? When i take only the part of the query before Union, it runs successfully. And the complete query when i run on SQL+ it executes succesfully.
    It keeps throwing an error - Missing Expression..

    Thanks,
    Priyank

  9. #9
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    Try using

    Print SQLText

    to see the final query.

  10. #10
    Apprentice
    Join Date
    Jan 2008
    Location
    San Jose, CA
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Problem in executing sql query

    Yes, thats what Im doing now. I figured the error is due to some problem in the second part of the query , and not any size issue. the first and the second parts run properly in sql+ as independent queries. Its just that there is some typo or some silly mistake in the second part.
    Thanks a lot Tarun for all your help!

    -Priyank

 

 

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.34%
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 07:56 AM.

Copyright BetaSoft Inc.