SPONSORS:






User Tag List

Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Junior Member
    Join Date
    Nov 2001
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question re: stored procedure call

    In my script right now, I am/was writing a generic query function which would call the passed-in query or stored procedure and store the results in a multi-dimensional list, (ie. a List of List of Any Type). To my surprise, this worked just fine when I was using a simple query (select * from x where y =z).

    However, when I pass in a stored procedure which I know works, I am not getting any results back; ie. DB_FetchNext(<handle>, ListValue) is never equating to true. Are there specific issues with Silk calling stored procs, or is this just a snafu on my part? Any help would be much appreciated.

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

    Here is the relevant code, along with the output of my print statements:

    [ ] hdbc = DB_Connect("dsn={dsnName};SRVR={serverName} UID={userName};PWD={passWord}")
    [ ]
    [ ] print(query)
    [ ] hstmnt = DB_ExecuteSql(hdbc, "{query}")
    [ ]
    [ ]
    [-] while (DB_FetchNext (hstmnt, ParamsList) == TRUE)
    **[ ] print("DB_FetchNext is not False")
    **[ ] ListAppend(Rows,ParamsList)


    [ ] int i
    [ ] i = ListCount(Rows)
    [ ] print("i="+i)

    Output:
    usp_get_event_fields @event_id=1606, @form_id=1, @field_status = 'R', @field_type = 'R'

    i=0

    If I cut and paste the stored procedure from the output and run it through SQL, it works correctly. As you can see from the output above. DB_FetchNext is never considered True. The ListCount check I put in (which evaluates to 0) was just to make sure that I am indeed not being returned anything.

    The only two distinctions I can think of which separate the working select statement and this failing stored procedure are (1) one is a select, the other is a stored proc, and (2) the select statement was returning multiple rows while the stored proc should only return one row of data.
    ------------------


    [This message has been edited by ctullbane (edited 02-04-2002).]

  2. #2
    Senior Member
    Join Date
    Aug 2000
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    stored procedures and sql are two distinct entities. sql is ansi compliant where stored procedures are specific to a vendors database. you will be able to run ansi sql from silk or winrunner using there db commands but. you will not be able to run stored procedures with the db commands

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

  3. #3
    Junior Member
    Join Date
    Nov 2001
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    Thanks for your reply.

    If the DB functions are out, is there an easyish way to handle stored procedures for MS SQL in Silk or should I just abandon the effort?

    Thanks again.

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

  4. #4
    Junior Member
    Join Date
    Nov 2001
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    Hmm. I went into the stored procedure, and extracted the necessary select statement, (shown below), but continue to receive 0 results. Do you know if this is still the same issue as previously discussed, despite the fact that I am no longer using a stored procedure?

    ---------------------------------------
    select DISTINCT F.name as field_name, EF.field_status
    From Field F (NOLOCK)
    Inner Join Event_Field EF
    ON F.field_id = EF.field_id AND EF.event_id = 1606
    WHERE
    F.account_id = 7
    AND
    F.field_status <> 'D'
    AND
    (EF.field_status = 'R')
    AND
    (F.custom_flag = 'N')

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

  5. #5
    Junior Member
    Join Date
    Nov 2001
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    I went through the SQL statement with my DBA and we excised anything that could potentially have been non-ANSI compliant, until we came up with this:

    select DISTINCT F.name, EF.field_status
    From Field F, Event_Field EF
    WHERE F.account_id = 7
    AND F.field_id = EF.field_id AND EF.event_id = 1606
    AND F.field_status <> 'D'
    AND (EF.field_status = 'R')
    AND (F.custom_flag = 'N')

    Again, running it produces no errors, and yet returns no records. We ran the script while profiling the DB and it appears Silk was not even trying to execute the statement. Can anyone explain why this is? My code works fine with a simpler query (Select * from Supporter where first_name = 'Ashlynn', for example)

    Additionally, I read in these forums about different means to call stored procedures with Silk and using DB_ExecuteSQL, but I get a Cursor State error when I try the one which supposedly works with MS SQL 7.

    Obviously, I'd prefer being able to call stored proc's directly, so if anyone has some advice here, I'd appreciate it. If there is still no way to do so, and the other posts on the subject are erroneous, then any advice on what is wrong with my SQL statement would be greatly appreciated.

    [This message has been edited by ctullbane (edited 02-04-2002).]

  6. #6
    Junior Member
    Join Date
    Nov 2001
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>
    select DISTINCT F.name, EF.field_status
    From Field F, Event_Field EF
    WHERE F.account_id = 7
    AND F.field_id = EF.field_id AND EF.event_id = 1606
    AND F.field_status <> 'D'
    AND (EF.field_status = 'R')
    AND (F.custom_flag = 'N')
    <HR></BLOCKQUOTE>

    More interesting news. I have found that if I remove one line ("AND EF.event_id = 1606"), the query works. That line is necessary, of course, but it's interesting that it works without it.

    If I print() out the query (with this line included), and then copy and paste it into my SQL window and run it, the query works fine, so the problem does -not- seem to be with my query itself.

    Just for kicks, I set up a simple query, as follows:
    select * from EVENT_FIELD where event_id = 1606
    Again, I had no results returned, (DB_FetchNext never evaluated to TRUE), whereas the same statement in SQL returned numerous rows.

    Any ideas?

    Thanks.

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

  7. #7
    Senior Member
    Join Date
    Jan 2002
    Location
    Des Moines, Iowa
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    I am using SQL Sever in my application. I have made great use of the AUT system's stored procedures to affect CRUD (Create Read Update, Delete) actions against the data in the database. The benefit is that I can do what needs to be done to the database in one sql call through silk and I am using the systems stored procedures to do it. This means that when the database changes the D.B.A. has to change the Procedures and I don't have change my code. But if the Stored Procedure returns any value I can not get it with in silk.

    You will have to go back to the tried and true select statement in silk. Sorry.

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

  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: Question re: stored procedure call

    Maybe there is a problem with sql queries that are longer than some number of characters - try chopping different sections from your query and see which work, maybe there's a definite cut-off point at which it will stop working.
    Though from looking at your query, that cut-off point would have to be between 201 and 224 characters, which doesn't correspond to any of the usual data amounts we're famililar with (128, 256 etc.).

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

  9. #9
    Senior Member
    Join Date
    Jan 2002
    Location
    Des Moines, Iowa
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    What is the field type for "event_id = 1606"
    Often times Sql Sever will show you one data format but store another and any sql call using the displayed value will not work because it does not find the value as it is stored in database. Date fields are notorious for this in Sql Server.


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

  10. #10
    Junior Member
    Join Date
    Nov 2001
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Question re: stored procedure call

    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>
    What is the field type for "event_id = 1606"
    Often times Sql Sever will show you one data format but store another and any sql call using the displayed value will not work because it does not find the value as it is stored in database. Date fields are notorious for this in Sql Server.
    <HR></BLOCKQUOTE>

    sp_help for event_id in the table event_field shows it as an int. For a while I was concerned that the value (1606) I was passing in for event_id was being converted by Silk into something other than an int, but other int fields in my query (account_id, for example) are working correctly. I've actually even changed my line with event_id to explicitly covert it into an int (just in case it was being sent by Silk as something else) with the following code:
    "AND Convert(int, EF.event_id) = 1606 "+

    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>
    Maybe there is a problem with sql queries that are longer than some number of characters - try chopping different sections from your query and see which work, maybe there's a definite cut-off point at which it will stop working.
    Though from looking at your query, that cut-off point would have to be between 201 and 224 characters, which doesn't correspond to any of the usual data amounts we're famililar with (128, 256 etc.).
    <HR></BLOCKQUOTE>

    Vince, that was my initial thought as well, which is why I started simplifying the SQL statement. I found that even as simple (and short) a statement as:
    select * from EVENT_FIELD where event_id = 1606
    will return 0 results.

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

    The obvious answer here is that somehow I'm not using event_id right in Silk, that it's of a different type, or that there actually IS no event_id 1606 in that table. Unfortunately, all but the first issue are easily resolved simply by outputting the query string and copying and pasting it into a SQL Query Analyzer and running it -- there are indeed results that get returned by these queries, as long as they are not called by Silk.

    My other concern would be that there was an issue with how I was outputting the results:

    List of Any Type ParamsList
    while (DB_FetchNext(hstmnt, ParamsList)==TRUE)
    **print("DB_FetchNext is true")
    **ListAppend(Rows,ParamsList)

    However, using this same schema (exact same, as I wrote a generic query method which I am calling with different queries), I can get results using other SQL queries.

    Anyway, thank you both for your help and suggestions... hopefully, I will be able to get this working.

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


    [This message has been edited by ctullbane (edited 02-07-2002).]

 

 
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 07:33 AM.

Copyright BetaSoft Inc.