SPONSORS:






User Tag List

Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Junior Member
    Join Date
    Dec 2000
    Location
    Bolingbrook, IL USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database query problems

    Hi,

    I'm hoping someone can help me out with this. I'm using SilkTest 5.5 on NT Server to test a web app. I'm currently trying to access the database on another machine (seperate from the one running Silk), grab a few pieces from a table, and basically just print them out or something. The problem is happening after I run the query & try to use the result.

    For example, I have a script like:
    ------------------
    [-] testcase DBTest() appstate none
    [ ]
    [ ] HDATABASE hdbc
    [ ] HSQL hGetOrder_PartnerID, hGetOrder_PartnerOrderData
    [ ] STRING sPartnerID, sOrderID
    [ ] BOOLEAN status
    [ ]
    [ ] hdbc = DB_Connect("dsn=silktest_odin;UID=sa")
    [ ] hGetOrder_PartnerID = DB_ExecuteSql(hdbc,"use ws_cats SELECT referrer_order_data, referrer_id FROM ecom_orders WHERE order_number = '22530'")
    [ ] status = DB_FetchNext(hGetOrder_PartnerID, sOrderID)
    [ ] DB_FinishSQL(hGetOrder_PartnerID)
    [ ] DB_Disconnect(hdbc)
    -----------------
    (hope that's readable above...)
    But when I run, I get an error that says:
    "*** Error: (24000) [Microsoft][ODBC SQL Server Driver]Invalid cursor state"

    This occurs right after the query (so it happens on the FetchNext function). I don't understand, since print statements show that the query runs ok (a handle like "H2097152" is returned), but when I try to do anything with that handle, like just a true/false verification step like the above script, I get that error.

    Is there something I need to do with that handle/query result first before I use DB_FetchNext?

    I would really appreciate some help!
    Thanks,
    Eric


    ------------------

  2. #2
    Senior Member
    Join Date
    Jul 2001
    Location
    Montreal, QC, Canada
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    Which handle are you saying is OK, the hdbc connection one, or the execution hstmt one?

    This error 'invalid cursor state' means one of the following:

    the handle was in an executed state but that no result set was associated with the handle.

    A cursor was open on the handle and SQLFetch had been called.

    A cursor was open on the handle but SQLFetch had not been called.

    The sql statement contained a positioned update or delete statement and the cursor was positioned before the start of the result set or after the end of the result set.

    ------------------

  3. #3
    Member
    Join Date
    Jun 2000
    Location
    Derby, UK
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    Your SQL statement is invalid, "use" is not valid here.
    If you need to specify your database do so in your dsn or modify the select statement thus:
    "SELECT referrer_order_data, referrer_id FROM ws_cats.dbo.ecom_orders WHERE order_number = '22530'"

    Hope that helps.


    ------------------


    [This message has been edited by James.Woolfenden (edited 07-06-2001).]

  4. #4
    Junior Member
    Join Date
    Dec 2000
    Location
    Bolingbrook, IL USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    MaxMc,
    Both handles (hdbc, and the execution hstmt one I called "hGetOrder_PartnerID") are ok. The script has no problem with them at all. Using print statements I see a value of "H2097152" for hGetOrder_PartnerID, so it seems ok. It's when I try to use the GetNext function that has problems...

    But thanks for the insight on the cursor errors...

    ------
    James Woolfenden,
    Actually, the use statement here works just fine in my script. No errors from SilkTest from it, and I can run it in SQL itself just fine. This is because I log in with master priveliges (as "sa"), and then simply tell the script to use the ws_cats database.

    Or do you mean that Silk won't complain about it, but it's still wrong to use?

    Can you please clarify?

    Thanks,
    Eric

    ------------------

  5. #5
    Senior Member
    Join Date
    Aug 1999
    Location
    Cambridge, UK
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    Correct me if I'm wrong here, but the bit in your sql query which says "SELECT referrer_order_data, referrer_id" means that the DB_FetchNext will return two items of data for each row it finds? But you're not submitting enough variables to hold two pieces of data - you're just submitting one string variable sOrderID, where you should either submit two variables, or one appropriate list or record variable to hold all the data coming back from the DB_FetchNext.

    ------------------

  6. #6
    Junior Member
    Join Date
    Dec 2000
    Location
    Bolingbrook, IL USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>Originally posted by vincebowdren:
    Correct me if I'm wrong here, but the bit in your sql query which says "SELECT referrer_order_data, referrer_id" means that the DB_FetchNext will return two items of data for each row it finds? But you're not submitting enough variables to hold two pieces of data - you're just submitting one string variable sOrderID, where you should either submit two variables, or one appropriate list or record variable to hold all the data coming back from the DB_FetchNext.

    <HR></BLOCKQUOTE>

    Vince,
    My query is indeed returning 1 row of data with 2 columns. I am grabbing the "referrer_id" and "referrer_order_data" columns from the row where the order_number is 22530 (this is a unique number to each row).

    Hmm...so perhaps I am using DB_FetchNext incorrectly! I thought I could just grab however many colums I want to, so if I specify 1 variable, it only grabs 1. That's what I thought I was supposed to do...but if what you say is correct (I need # of variables = to what I'm retreiving with the query), then yep, I got it wrong here. I'll try adding another variable (or use a list or record).

    ------------

    My apologies to James & Max too...it seems that yes, my query wasn't returning the handle correctly or something. I don't get the cursor error when I use the "ws_cats.dbo.ecom_orders" instead of the "use" statement. I still don't understand why I can use it in SQL but not Silk, or why the error pointed to the line after the query was run (leading me to believe the query was ok).

    But eliminating the "use" statement solved the problem.

    However, now I've got problems using the DB_FetchNext function (SQL_TYPE not supported) that I'm guessing are because of what Vince is saying...

    I'll keep trying....thanks for the help so far!

    ------------------


    [This message has been edited by emeiring (edited 07-06-2001).]

  7. #7
    Member
    Join Date
    Jun 2000
    Location
    Derby, UK
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    emeiring,
    The use statemnent is perfectly good SQL for a SQLServer database.
    However it doesnt work from within silk. I checked.
    The method i showed works, however if your ever writing a lot of select statements youll get less confused this way (in my humble opinion).
    Your use of DB_fetch as mentioned is valid.




    ------------------

  8. #8
    Senior Member
    Join Date
    Aug 1999
    Location
    Cambridge, UK
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    No sorry, according to the manual entry for DB_FetchNext if you haven't specified enough parameters it shouldn't fail, it should simply only give you back enough data to fit into the parameters you supplied. My mistake.

    ------------------

  9. #9
    bjc
    bjc is offline
    Member
    Join Date
    Jan 2000
    Location
    CT
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>
    The use statemnent is perfectly good SQL for a SQLServer database.
    However it doesnt work from within silk. I checked.
    <HR></BLOCKQUOTE>

    I have not had any problems with executing "use [db name]" SQL syntax from within Silk. All Silk does with any SQL is treat it as a string. It does not validate the syntax.

    ex:
    ------------------

  10. #10
    Junior Member
    Join Date
    Dec 2000
    Location
    Bolingbrook, IL USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Database query problems

    Well, I thought the use statement would be ok, but I keep getting that cursor error I mentioned above when I use it. When I don't, it runs ok. So I must be leaving something out...? Regardless, I just won't use the "use" statement.

    Which brings me to yet another snag. I actually got my script to work when I only retrieve "referrer_order_data" (which is a string)...woohoo!

    Unfortunately, when I try to retrieve referrer_id (which is in reality a GUID), I get the error:
    "*** Error: SQL_TYPE (-11) in column (referrer_id) not supported"

    So obviously I can't assign a GUID that's returned into a string variable like I'm doing. Any ideas on how to correct this new problem now, to handle the GUID?

    Thanks for all the help so far!
    -Eric

    ------------------

 

 
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
  •  

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin 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 02:34 AM.

Copyright BetaSoft Inc.