Re: strange behavior - Mailing list pgsql-odbc

I am not sure if there is an alternative to Declare/Fetch.

I would suggest taking a more client/server-centric approach. Your loop
uses a dynamic cursor (holding the cursor on the server side, while each
row is examined and possibly changed by the client). The same function
could have been accomplished with an SQL Update statement with the
appropriate where clause. The server would process it, and it would be
faster.

I tend to think SELECT statements with client-side cursors for
retrieving information.

I tend to use executable statements otherwise
INSERTS for  adding. UPDATES for changing. And DELETES for ... the
obvious.

Patrick Hatcher wrote:
>
> Figured out the problem.  I had Declare/Fetch enabled and the cache size
> set to 100.
> Now my next question is.  How do I go about using Declare/Fetch with large
> recordsets?  After the buffer  reaches the end of my first Declare/Fetch, I
> cannot seem to go any further
>
> thanks again
> Patrick Hatcher
>
>
>            Patrick
>            Hatcher/MCOM/FDD
>                                                                         To
>            11/25/2003 01:37 PM        Pg_ODBC Pg_ODBC
>                                                                         cc
>
>                                                                    Subject
>                                       strange behavior
>
>
>
>
>
>
>
> ODBC: 7.03.02
> App: Visual Basics 6
> Pg: 7.3.3 (same problem on 7.4)
>
> I'm having a problem I cannot figure out.  I have code that reads through a
> recordset and makes necessary updates to fields and then updates the
> record.  For some reason, the process always stops at record 99.  No matter
> what happens, it stops while trying to do  rs.MOVENEXT and freezes my app.
> What I do notice is that if I remove the code between the Do and MOVENEXT,
> the entire recordset is read without issue.  Am I missing something?  Do I
> need to clear some variable out?
>
> Here's a snippet of my code (it looks suspiciously like the example
> provided on the website):
>
> Set rsBASADDs = New ADODB.Recordset
>
> rsBASADDs.Open "Select oid,* from PREP order by dept, vend,mstyle", Conn1,
> adOpenDynamic, adLockOptimistic, adCmdText
>
> If rsBASADDs.BOF And rsBASADDs.EOF Then
>     CloseTimer
> End If
>
> rsBASADDs.MoveFirst
>
> Do While Not rsBASADDs.EOF
>
>          If (rsBASADDs.Fields("nrfcolorcode") = Null Or
> Val(rsBASADDs.Fields("nrfcolorcode")) = 0) And _
>          (rsBASADDs.Fields("nrfsizecode") = Null Or Val(rsBASADDs.Fields
> ("nrfsizecode")) = 0) Then
>             NoColorSizeStyle = True
>             rsBASADDs.Fields("mwcolorcode") = "00"
>             rsBASADDs.Fields("mwsizecode") = "000"
>             'rsBASADDs.Fields("mwsizerange") = "000"
>             rsBASADDs.Fields("status") = "NON CS STYLE"
>
>        End If
>
>         rsBASADDs.Update
>         x = x + 1
>         Debug.Print rsBASADDs.Fields("oid") & " - " & x
>         rsBASADDs.MoveNext
> Loop
>
> TIA
>
> Patrick Hatcher
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster