Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Recordset from ADO to StoredProc

    I have been digging around, printing articles and trying out stored proc code I hoped to adapt to an existing results page and I am getting rather confused. I was wondering if someone might be able to help!

    The issue: I have set up a pretty, banded and sortable table using ADO recordset tech to implement paging; it all looks quite nice but, as you may be aware, client-side cursors will fetch all the data from the db on each visit, which is obviously going to hurt when the recordset is large (in this case, 45K). I'd like to move the data handling to a stored proc instead but haven't found a good solution. Part of the problem is, as you can see from the attachment, that the SQL String being passed to the db varies a bit depending on user action. the search results page handles 2 different forms from the referring page and also posts to itself (this is to enable a sort). ADO Recordset technology is great, in that I get all this nice info at the header of the table (such as total pages fetched, number of the current page, etc.) and I' d like to keep all that. It's obviously better to migrate a lot of this to a stored proc or two however in order to fetch only the data required per page load.

    On the SQL Server side, the table being accessed has a primary key, so it is indexed.

    I'd appreciate any input people might have on adapting this code to interact with a stored proc, instead of using ADO recordset technology.

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Recordset from ADO to StoredProc

    This is a pretty common problem. There are quite a few articles out there to address this (using this Google search)

    Paul Wilson's Blog
    WWWCoder
    A lively discussion on SQLTeam.com

    Hope this helps.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Recordset from ADO to StoredProc

    Thanks for the links but I've already looked at all those (and more!) several times. the problem I am having is more like how to implement the stored proc in ASP. I'm sure Mr Learning Curve is the problem here, because I *think* I have a fairly good stored proc in the can (at least it passes the syntax check). If you looked at the code I posted, there are several variables I need to pass to the stored proc:

    Field Names (same for any selection; this is 'hard coded' into the output table design)
    SortBy (can be any field name depending on user selection)
    Direction (ditto)
    TargetName (from request form)
    TargetID (from request form)
    SearchFilter (from request form)
    RecordsPerPage (from request form)
    Status (from request form)

    the stored proc I am attempting to adapt displays the following in Query Analyzer on loading to Execute:

    DECLARE @RC int
    DECLARE @strFields varchar(4000)
    DECLARE @strPK varchar(100)
    DECLARE @strTables varchar(4000)
    DECLARE @intPageNo int
    DECLARE @intPageSize int
    DECLARE @blnGetRecordCount bit
    DECLARE @strFilter varchar(8000)
    DECLARE @strSort varchar(8000)
    DECLARE @strGroup varchar(8000)
    -- Set parameter values
    EXEC @RC = [OIG_Caselist].[dbo].[SELECT_WITH_PAGING] @strFields, @strPK, @strTables, @intPageNo, @intPageSize, @blnGetRecordCount, @strFilter, @strSort, @strGroup

    I take it I need to pass the params to the stored procedure once the ASP code sets up the appropriate SQL string (which is built out of a series of Select Case statements). In the working search results page, the logic is basically to build a SQL String based on this or that choices and execute to a recordset using

    Set myRS= Server.CreateObject("ADODB.Recordset")
    myRS.Open mySQL, DataConnection, adOpenStatic, adLockReadOnly

    It would seem to me best to wrap all the logic (from the Select Case stuff down to the recordset) in stored proc... And actually, the way my code works now there is are two calls to the db: one to fetch the record counts and another to set the SQL string. The ADO recordset vars give me that nifty Page X of Y output and triggers for next / previous record (if applicable). When I look at the params emitted in Query Analyzer when I open the stored proc under Execute it looks darn close...

    I dunno I'll keep banging away at it but it's all a little difficult for me to 'grok' for some reason. But I need to learn this! I need to graduate to using stored procs for recordset handling as I am fortunate enough to have a SQL Server to play with.

    Well, thanks anyway.
    <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Recordset from ADO to StoredProc

    I didn't realize you already had the stored procedure ready to roll.

    To pass parameters back and forth to a stored procedure you should use the ADODB.Command and ADODB.Parameter objects. You will no longer need to use any dynamically-created SQL strings. However, your case logic will still be useful to help determine the values ot the specific parameters.

    The general process is this:
    - Create a connection object
    - Create a command object
    - Add parameter objects to the command (being sure to specify several critical properties)
    - Capture the command object's Execute method into a recordset
    - Optional: Read the value of any output parameters

    Here's a quick code sample:<pre>set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = cnn
    cmd.CommandText = "proc_Data_Lookup"
    cmd.CommandType = adCmdStoredProc
    set prmDataID = Server.CreateObject("ADODB.Parameter")
    With prmDataID
    .Name = "@prmDataID"
    .Type = adVarChar
    .Direction = adParamInput
    .Value = intDataID
    End With
    set rst = cmd.Execute(,prmDataID,adCmdStoredProc)</pre>

    Note: This example only has a single parameter object but you can easily use multiple parameters with the parameters collection.

    You'll find quite a bit of info on this technique available from Microsoft and other ASP coding sites.

    Hope this helps.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Recordset from ADO to StoredProc

    thanks for your help. now i am slogging away in Query Analyzer... this is going to be a lot of work! no royal roads here... ah well, it's well worth the effort.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Recordset from ADO to StoredProc

    Gee, this is weird. I think I may have been working on a solution that isn't necessary. using ADO style recordset pageing DOESN'T seem to be pulling all the records in the db per page. It in fact performs as I would expect if I am pulling and paging on a subset of the data (I should have noticed this before, but I was concerned about the issue in the abstract as well). Anyhow, I use

    <font face="Georgia">Set myRS= Server.CreateObject("ADODB.Recordset")
    myRS.Open mySQL, DataConnection, adOpenStatic, adLockReadOnly]</font face=georgia>

    to grab the records based on the Status code. if I pull all the records it takes about 7 seconds, and sorts on the columns take a little less but still some time. If I pull a dataset of 120 records the speed is proportionally greater (on retrieval and sorting). If I pull one with 4 records all these operations go VERY quickly.

    The only thing I have done that really made much of a difference was eliminate some (now unneeded) ASP string handling stuff.

    ??

    I know adUseClient is supposed to pull everything, which is why I definately did not use that.

    well, well, well.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •