Re: Cursors - Server Side - Only Forward?

From: Karl Gram (NOSPAMkarl_at_gramonline.nl)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 23:03:31 +0100

Hi Gerard,

You're using VB's ADO.Recordset object to manipulate the cursor data. The
default cursor type for a ADO.Recordset is adForwardOnly. If you set it to
adOpenDynamic, you should be able to navigate the cursor any way you want.
Add the following code to you app just after the statement where you set the
cursor location:

Dbs.CursorType = adOpenDynamic

Also have a look here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursortype.asp 

-- 
Karl Gram, BSc, MBA
http://www.gramonline.com
<anonymous@discussions.microsoft.com> wrote in message
news:1229201c41120$d70ff770$a301280a@phx.gbl...
> Tom,
>   Use this VB code as an example(below with DDL).  This is
> legacy system code and can't be modified too much or it
> affects about 100,000 lines of code.  I need to search a
> table of mortgages, with about 1.5 million records in it,
> to do a history on a particular home ID.  The home ID's
> appears multiple times.  Using server side cursors would
> keep the data and processing server-side, while returning
> just the results of the history(a 250k file).  When the
> code gets to executing a .movefirst, I get a runtime
> error.  This isn't a big issue, I just loose my progress
> meter in this case.  But in other cases, we use a .find
> which needs to go backwards also to make sure all records
> are searched.  Thanks for the help, sorry about the text
> formatting on the code.
>
> Gerard
> Gulf Management Systems
> Programmer / Processor
>
> Code
> ----
> Dim Dbs As New ADODB.Connection
> Dim rst1 As New ADODB.Recordset
> Dim rst2 As New ADODB.Recordset
> Dim strSQL1 As String
> Dim strSQL2 As String
> Dim strMsgBox As String
> Dim lngButtons As Long
> Dim varReturn As Variant
> Dim lngRecCnt As Long
> Dim dblAmount As Double
> Dim blnHeadingSw As Boolean
> Dim strPriorId As String
>
> Dbs.Mode = adModeShareDenyNone
>
> Dbs.CursorLocation = adUseServer
>
> Dbs.Open "GMSUpsize"
>
> ' Display hourglass
> DoCmd.Hourglass True
>
> ' Prepare SQL Select statement
> 'strSQL1 = "SELECT
> tblPickList_RefundBalance.RefundHomeID, "
> 'strSQL1 = strSQL1
> & "tblPickList_RefundBalance.RefundFromDate "
> 'strSQL1 = strSQL1 & "FROM tblPickList_RefundBalance "
> 'strSQL1 = strSQL1 & "ORDER BY
> tblPickList_RefundBalance.RefundHomeID;"
>
> ''''''''''''''''''''''''''''''''
> ' SP Contains SELECT above
> ''''''''''''''''''''''''''''''''''
> strSQL1 = "spBasRefundBalance_rst1"
>
> 'Open Recordset
> Set rst1 = Dbs.Execute(strSQL1)
>
> ' Initialize progress meter
> '''''''''''''''''''''''''''''''''
> ' Gives an error here when the movefirst is executed
> '''''''''''''''''''''''''''''''''
> rst1.MoveLast
> rst1.MoveFirst
> varReturn = SysCmd(acSysCmdInitMeter, "Processing Refund
> Balances", rst1.RecordCount)
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> ' Open output Error Log file
> Open "C:\TRASHCAN\BALREF" & basJulian(2, "") For Output As
> #1
>
> Print #1, "**** REFUND BALANCE REPORT ****  AS OF: " &
> Format(Date, "mm/dd/yyyy") & "  " & Format(Time, "Short
> Time")
> Print #1, " "
>
> 'Initialize Amount
> dblAmount = 0
>
> Do While rst1.EOF = False
>
>     ' Prepare SQL Select statement
>     'strSQL2 = "SELECT tblMortgageAudit.AudType,
> tblMortgageAudit.AudCompID, "
>     'strSQL2 = strSQL2 & "tblMortgageAudit.AudHomeid,
> tblMortgageAudit.AudDate, "
>     'strSQL2 = strSQL2 & "tblMortgageAudit.AudTime,
> tblMortgageAudit.AudName, "
>     'strSQL2 = strSQL2
> & "tblMortgageAudit.AudMortgageReferenceNumber, "
>     'strSQL2 = strSQL2
> & "tblMortgageAudit.AudGMS_Tracking,
> tblMortgageAudit.AudAmount, "
>     'strSQL2 = strSQL2 & "tblMortgageAudit.AudCheckNumber,
> tblMortgageAudit.AudReturnReasonCode, "
>     'strSQL2 = strSQL2
> & "tblMortgageAudit.AudReturnEffectiveDate,
> tblMortgageAudit.AudRemarks, "
>     'strSQL2 = strSQL2 & "tblMortgageAudit.ClearedDate,
> tblACH_Company.RATE_ACH, "
>     'strSQL2 = strSQL2
> & "tblAcceleratedMortgage.AM_BEG_DTE,
> tblAcceleratedMortgage.INDICATORS,"
>     'strSQL2 = strSQL2 & "IIf([Audtype]=""DEB"",[Audamount]
> +[rate_ach],IIf([Audtype]=""RTN"" and [AudAmount] <> 0.00,
> [Audamount]-[rate_ach],[Audamount])) AS Expr1 "
>     'strSQL2 = strSQL2 & "FROM (tblMortgageAudit LEFT JOIN
> tblACH_Company ON tblMortgageAudit.AudCompID =
> tblACH_Company.KEY) "
>     'strSQL2 = strSQL2 & "LEFT JOIN tblAcceleratedMortgage
> ON tblMortgageAudit.AudHomeid =
> tblAcceleratedMortgage.HOMEID "
>     'strSQL2 = strSQL2 & "WHERE tblMortgageAudit.AudHomeid
> Like " & rst1!RefundHomeID & " "
>     'strSQL2 = strSQL2 & "ORDER BY
> tblMortgageAudit.AudDate;"
>
> ''''''''''''''''''''''''''''''''
> ' SP Contains SELECT above
> ''''''''''''''''''''''''''''''''''
>     strSQL2 = "spBasRefundBalance_rst2(" & rst1!
> RefundHomeID & ")"
>
>     ' Open recordset 2
>      Set rst2 = Dbs.Execute(strSQL2)
>
>     If rst2.BOF And rst2.EOF Then
>     ' No records have been found
>         Print #1, "ID: " & rst1!RefundHomeID
>         Print #1, " "
>         Print #1, "CID   TPE   ---DATE---   AMOUNT......
> NOTES.................."
>         Print #1, "*** No Audit Entries Found ! ***"
>         Print
> #1, "=======================================END-OF-
> REPORT: " & Space(8 - Len(rst1!RefundHomeID)) & rst1!
> RefundHomeID
>         Print #1, " "
>     Else
>
>         blnHeadingSw = True
>
>         strPriorId = rst2!AudHomeid
>
>         ' Process record set
>         Do While rst2.EOF = False
>
>             If blnHeadingSw = True Then
>                 Print #1, "ID: " & rst2!AudHomeid & "
> NAME: " & rst2!AudName & IIf(Mid(rst2!INDICATORS, 1, 1)
> = " ", "   (ACTIVE)", "  (INACTIVE!)")
>                 Print
> #1, "                                       START DATE:  "
> & Format(rst2!AM_BEG_DTE, "mm/dd/yyyy")
>                 Print #1, "CID   TPE   ---DATE--- 
> AMOUNT......  NOTES.................."
>                 blnHeadingSw = False
>             End If
>
>             Print #1, rst2!AudCompID _
>                 & "   " _
>                 & rst2!AudType _
>                 & "   " _
>                 & rst2!AudDate _
>                 & "   " _
>                 & Format(rst2!AudAmount, "currency") _
>                 & Space(12 - Len(Format(rst2!
> AudAmount, "currency"))) _
>                 & IIf(rst2!AudType = "PMT", "  Check # " &
> rst2!AudCheckNumber & " To: " & Mid(rst2!AudGMS_Tracking,
> 5, 4), " ") _
>                 & IIf(rst2!ClearedDate <> "", "  Date
> Cleared: " & Format(rst2!ClearedDate, "mm/dd/yyyy"), " ") _
>                 & IIf(rst2!AudType = "RTN", " " & rst2!
> AudReturnReasonCode & " ON " & rst2!
> AudReturnEffectiveDate, " ") _
>                 & IIf(rst2!AudType = "ADJ", Mid(rst2!
> AudRemarks, 1, 23), " ")
>
>             If rst2!AudType & rst2!AudDate & rst2!
> AudRemarks = "DEB2001/01/04Debit sent in error" Then
>                 dblAmount = dblAmount + rst2!expr1 - rst2!
> RATE_ACH
>             Else
>                 dblAmount = dblAmount + rst2!expr1
>             End If
>
>             ' Move to next record
>             rst2.MoveNext
>
>             ' Exit if end of file has been reached
>             If rst2.EOF = True Then
>                 Exit Do
>             End If
>
>         Loop
>
>         Print #1, " "
>         Print #1, "Running Balance To Date: " _
>             & Format(dblAmount, "currency") _
>             & Space(12 - Len(Format
> (dblAmount, "currency"))) _
>             & "  [" & IIf(dblAmount <
> 0, "Credit", "Debit") & " Homeowner]"
>         Print
> #1, "=======================================END-OF-
> REPORT: " & Space(8 - Len(strPriorId)) & strPriorId
>         Print #1, " "
>
>     End If
>
>     'Initialize Amount
>     dblAmount = 0
>
>     ' Close recordset
>     rst2.Close
>
>     ' Move to next record
>     rst1.MoveNext
>
>     ' Update progress meter
>     lngRecCnt = lngRecCnt + 1
>     varReturn = SysCmd(acSysCmdUpdateMeter, lngRecCnt)
>
>     ' Exit if end of file has been reached
>     If rst1.EOF = True Then
>         ' Turn off hourglass
>         DoCmd.Hourglass False
>         varReturn = SysCmd(acSysCmdRemoveMeter)
>         Exit Do
>     End If
>
> Loop
>
> ' Close output files
> Close #1
>
> ' Close recordset
> rst1.Close
>
> ' Turn off hour glass & progress meter
> DoCmd.Hourglass False
> varReturn = SysCmd(acSysCmdRemoveMeter)
>
> DDL for tblPickList_RefundBalance
> ---------------------------------
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblPickList_RefundBalance]') and
> OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblPickList_RefundBalance]
> GO
>
> CREATE TABLE [dbo].[tblPickList_RefundBalance] (
> [RefundHomeID] [float] NOT NULL ,
> [RefundFromDate] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> DDL for tblMortgageAudit
> ------------------------
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblMortgageAudit]') and OBJECTPROPERTY
> (id, N'IsUserTable') = 1)
> drop table [dbo].[tblMortgageAudit]
> GO
>
> CREATE TABLE [dbo].[tblMortgageAudit] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AudType] [nvarchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudCompID] [nvarchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudDate] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudTime] [nvarchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudHomeid] [float] NULL ,
> [AudName] [nvarchar] (22) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudMortgageReferenceNumber] [nvarchar] (40)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudGMS_Tracking] [nvarchar] (24) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudAmount] [money] NULL ,
> [AudCheckNumber] [nvarchar] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudReturnReasonCode] [nvarchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudReturnEffectiveDate] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AudRemarks] [nvarchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ClearedDate] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ChangeFlag] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> DDL for tblAcceleratedMortgage
> ------------------------------
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAcceleratedMortgage]') and
> OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblAcceleratedMortgage]
> GO
>
> CREATE TABLE [dbo].[tblAcceleratedMortgage] (
> [HOMEID] [float] NOT NULL ,
> [AM_ABA_NUM] [nvarchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_ACCTNUM] [nvarchar] (17) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_AMOUNT] [float] NULL ,
> [AM_TRACK] [nvarchar] (8) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_NAME] [nvarchar] (22) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_C_S_IND] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_BEG_DTE] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_END_DTE] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_REMARKS] [nvarchar] (4000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_SCHED] [nvarchar] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_COMP_ID] [nvarchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_ONCE_SW] [bit] NULL ,
> [MOR_RETRY] [bit] NULL ,
> [MOR_DDEBIT] [bit] NULL ,
> [INDICATORS] [nvarchar] (36) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AM_PERIODS] [float] NULL ,
> [MOR_NSFJAN] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFFEB] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFMAR] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFAPR] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFMAY] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFJUN] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFJUL] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFAUG] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFSEP] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFOCT] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFNOV] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_NSFDEC] [nvarchar] (1) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_COMPID] [nvarchar] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_REFNUM] [nvarchar] (26) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_AMOUNT] [float] NULL ,
> [MOR_SCYCLE] [bit] NULL ,
> [MOR_ADDRL1] [nvarchar] (254) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_ADDRL2] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_CITY] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_STATE] [nvarchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_ZIP] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MOR_PHONE] [nvarchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [WhoWhen] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> >-----Original Message-----
> >For that, you would need to use a SCROLL cursor.  Check
> out DECLARE CURSOR
> >in the BOL.
> >
> >That said, most SQL problems can be solved without
> cursors.  How about
> >posting the requirements, DDL, etc. and let us have a go?
> >
> >-- 
> >Tom
> >
> >----------------------------------------------------------
> -----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >
> >
> >"Gerard" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:d8f001c41111$f9336df0$a001280a@phx.gbl...
> >Hey all,
> >    Quick and simple, I am running SQL 2k on Win 2k
> >Server.  Is there anyway to make a server-side cursor
> >capable of going backwards on records.  They seem to be
> >forward only.  All the info I have says there is no way.
> >Any ideas??
> >
> >Gerard
> >Gulf Management Systems
> >Programmer / Processor
> >
> >P.S. I have a box of recycled bits for the best post :)
> >