Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #989 > unrolled thread

ADO

Started byEckhard Sallermann <eckhard.sallermann@bur-kg.de>
First post2011-04-29 15:15 +0200
Last post2011-05-02 21:55 +0000
Articles 11 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  ADO Eckhard Sallermann <eckhard.sallermann@bur-kg.de> - 2011-04-29 15:15 +0200
    Re: ADO "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-04-30 00:07 +0000
      Re: ADO "Willie Moore" <williem@wmconsulting.com> - 2011-04-29 21:43 -0500
        Re: ADO "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-05-01 22:22 +0000
          Re: ADO "Willie Moore" <williem@wmconsulting.com> - 2011-05-01 21:47 -0500
      Re: ADO Eckhard Sallermann <eckhard.sallermann@bur-kg.de> - 2011-05-02 09:29 +0200
        Re: ADO Eckhard Sallermann <eckhard.sallermann@bur-kg.de> - 2011-05-02 14:10 +0200
        Re: ADO "Willie Moore" <williem@wmconsulting.com> - 2011-05-02 07:11 -0500
          Re: ADO Eckhard Sallermann <eckhard.sallermann@bur-kg.de> - 2011-05-02 14:27 +0200
            Re: ADO "Willie Moore" <williem@wmconsulting.com> - 2011-05-03 07:56 -0500
        Re: ADO "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-05-02 21:55 +0000

#989 — ADO

FromEckhard Sallermann <eckhard.sallermann@bur-kg.de>
Date2011-04-29 15:15 +0200
SubjectADO
Message-ID<ipedl3$j3i$1@dont-email.me>
VO 2.8 SP3
VO2ADO 2.8 ( AdoSetAdoVersion() = 20 , AdoVersion() = 6.1 )
Windows 7 64 bit
MS-SQL-Server 2008 R2



code for getting data from SQL-Server:

     lOk := true
     begin sequence
         cbErr := ErrorBlock({|e|_Break(e)})
         oAdoRecordSet := AdoRecordset{}
         oAdoRecordSet:open( csql , _oAdoConnectionMSSQL1 , 
adOpenKeyset , AdLockReadOnly , adAsyncExecute  )
         WHILE true
             IF oAdoRecordSet:state = 1
                 exit
             ENDIF
             Sleep(100)
             ApplicationExec( EXECWHILEEVENT )
             if _lcancel
                 exit
             endif
         ENDDO
         lOk := true
     recover using oError
         MyMessBox( nil , Token( ExecName(FALSE) , "." , 1 )  , 
{     "Fehler bei Selektieren der Buchungen: " + csql , 
oError:description  } , BOXICONEXCLAMATION     + BUTTONOKAY) 

       lOk := FALSE
            _lcancel := true
     end
     ErrorBlock(cbErr)




     if lOk .and. !_lcancel
         oAdoServer := AdoServer{ oAdoRecordSet , ;
                 _oAdoConnectionMSSQL1 ,  ;
                 adOpenKeyset , ;
                 AdLockReadOnly   , ;
                  adCmdTable + adAsyncExecute } //
         oAdoServer:nullasblank := true
         oAdoServer:gotop()
         IF !oAdoServer:eof

                    . . . . .
The loop after oAdoRecordSet:open() runs until getting 1 from 
oAdoRecordSet:state, but this happens to fast and initializing AdoServer 
takes the time, that it takes as running on SQl-Server-Management 
Studio, but the Vo app is frozen until AdoServer{} gets ready.



what im i doing wring here ?

[toc] | [next] | [standalone]


#995

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-04-30 00:07 +0000
Message-ID<4dbb52d2$0$77724$c30e37c6@exi-reader.telstra.net>
In reply to#989
> what im i doing wring here ?

Just about everything <g>.

I don't quite understand why you creating a recordset and then passing 
it to the server. Arse about in my books. And why all that looping and 
waiting????

Robert has perfectly clear examples - why don't you start with them? 
Essentially the process should be:

1.	create a connection object
2.	Set your default connection parameters
3.	Store this connection globally for re-use
4.	Open your ADOServer with a select statement and that connection 
object
5.	Close the connection when you close your app

Use the same connection object for all your RS accesses (when you just 
want data) or for all your servers. Use the connection object directly 
to execute commands or SPs but they often return recordsets anyhow.

Geoff


[toc] | [prev] | [next] | [standalone]


#998

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-29 21:43 -0500
Message-ID<ipft17$7c0$1@speranza.aioe.org>
In reply to#995
Geoff,

I wont speak for the reasons he is creating a recordset and then passing it 
to bBrowser but there are perfectly valid reasons for it. The number one 
reason is bBrowser needs a ADOServer, not the recordset. I always create and 
use a recordset, not an ADOServer. In the case that the users hits the ... 
and wants a braower, then I create the ADOServer from the recordset and pass 
it to bbrowser:use.

Regards,
Willie

"Geoff Schaller"  wrote in message 
news:4dbb52d2$0$77724$c30e37c6@exi-reader.telstra.net...

> what im i doing wring here ?

Just about everything <g>.

I don't quite understand why you creating a recordset and then passing
it to the server. Arse about in my books. And why all that looping and
waiting????

Robert has perfectly clear examples - why don't you start with them?
Essentially the process should be:

1. create a connection object
2. Set your default connection parameters
3. Store this connection globally for re-use
4. Open your ADOServer with a select statement and that connection
object
5. Close the connection when you close your app

Use the same connection object for all your RS accesses (when you just
want data) or for all your servers. Use the connection object directly
to execute commands or SPs but they often return recordsets anyhow.

Geoff

[toc] | [prev] | [next] | [standalone]


#1012

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-05-01 22:22 +0000
Message-ID<4dbddd33$0$99952$c30e37c6@exi-reader.telstra.net>
In reply to#998
Willie,

Two things:

1.	The guy is having trouble so it is a LOT easier to do it the other 
way around. Establish first that you have a valid connection and then 
establish the recordset. Otherwise you have to debug two things for the 
price of one operation. That is why all of Robert's examples work that 
way.

2.	Although I understand that sequence is possible, why do two 
operations when you can have one? Just use the select statement with the 
ADO server and you can ignore the recordset operation first.

The guy is just learning so let's take the easiest course first and then 
add complexity later.

Geoff



"Willie Moore" <williem@wmconsulting.com> wrote in message 
news:ipft17$7c0$1@speranza.aioe.org:

> Geoff,
>
> I wont speak for the reasons he is creating a recordset and then passing it
> to bBrowser but there are perfectly valid reasons for it. The number one
> reason is bBrowser needs a ADOServer, not the recordset. I always create and
> use a recordset, not an ADOServer. In the case that the users hits the ...
> and wants a braower, then I create the ADOServer from the recordset and pass
> it to bbrowser:use.
>
> Regards,
> Willie
>
> "Geoff Schaller"  wrote in message
> news:4dbb52d2$0$77724$c30e37c6@exi-reader.telstra.net...
>
>
> > what im i doing wring here ?
>
>
> Just about everything <g>.
>
> I don't quite understand why you creating a recordset and then passing
> it to the server. Arse about in my books. And why all that looping and
> waiting????
>
> Robert has perfectly clear examples - why don't you start with them?
> Essentially the process should be:
>
> 1. create a connection object
> 2. Set your default connection parameters
> 3. Store this connection globally for re-use
> 4. Open your ADOServer with a select statement and that connection
> object
> 5. Close the connection when you close your app
>
> Use the same connection object for all your RS accesses (when you just
> want data) or for all your servers. Use the connection object directly
> to execute commands or SPs but they often return recordsets anyhow.
>
> Geoff

[toc] | [prev] | [next] | [standalone]


#1015

From"Willie Moore" <williem@wmconsulting.com>
Date2011-05-01 21:47 -0500
Message-ID<ipl5vu$f76$1@speranza.aioe.org>
In reply to#1012
Geoff,

I use ADO recordsets for the same reasons you tell folks not to use data 
windows. I don’t want the extra overhead. For the rair occasions that I need 
to create a browser, I take the recordset (if it is already open) and create 
the ADOServer from it. If I haven't already created the ADORecordset, then I 
skip that step and create the ADOServer directly with the SQL statement.

I do agree though that when learning, it is good to go as simple as possible 
first. You can always add complexity later.

Regards,
Willie

"Geoff Schaller"  wrote in message 
news:4dbddd33$0$99952$c30e37c6@exi-reader.telstra.net...

Willie,

Two things:

1. The guy is having trouble so it is a LOT easier to do it the other
way around. Establish first that you have a valid connection and then
establish the recordset. Otherwise you have to debug two things for the
price of one operation. That is why all of Robert's examples work that
way.

2. Although I understand that sequence is possible, why do two
operations when you can have one? Just use the select statement with the
ADO server and you can ignore the recordset operation first.

The guy is just learning so let's take the easiest course first and then
add complexity later.

Geoff



"Willie Moore" <williem@wmconsulting.com> wrote in message
news:ipft17$7c0$1@speranza.aioe.org:

> Geoff,
>
> I wont speak for the reasons he is creating a recordset and then passing 
> it
> to bBrowser but there are perfectly valid reasons for it. The number one
> reason is bBrowser needs a ADOServer, not the recordset. I always create 
> and
> use a recordset, not an ADOServer. In the case that the users hits the ...
> and wants a braower, then I create the ADOServer from the recordset and 
> pass
> it to bbrowser:use.
>
> Regards,
> Willie
>
> "Geoff Schaller"  wrote in message
> news:4dbb52d2$0$77724$c30e37c6@exi-reader.telstra.net...
>
>
> > what im i doing wring here ?
>
>
> Just about everything <g>.
>
> I don't quite understand why you creating a recordset and then passing
> it to the server. Arse about in my books. And why all that looping and
> waiting????
>
> Robert has perfectly clear examples - why don't you start with them?
> Essentially the process should be:
>
> 1. create a connection object
> 2. Set your default connection parameters
> 3. Store this connection globally for re-use
> 4. Open your ADOServer with a select statement and that connection
> object
> 5. Close the connection when you close your app
>
> Use the same connection object for all your RS accesses (when you just
> want data) or for all your servers. Use the connection object directly
> to execute commands or SPs but they often return recordsets anyhow.
>
> Geoff 

[toc] | [prev] | [next] | [standalone]


#1016

FromEckhard Sallermann <eckhard.sallermann@bur-kg.de>
Date2011-05-02 09:29 +0200
Message-ID<iplmgc$f12$1@dont-email.me>
In reply to#995
ok, then please show me an example how to use adoserver and execute a 
statement asyncron. the user should be able to cancel while waiting for 
the result


Am 30.04.2011 02:07, schrieb Geoff Schaller:
>> what im i doing wring here ?
>
> Just about everything <g>.
>
> I don't quite understand why you creating a recordset and then passing
> it to the server. Arse about in my books. And why all that looping and
> waiting????
>
> Robert has perfectly clear examples - why don't you start with them?
> Essentially the process should be:
>
> 1. create a connection object
> 2. Set your default connection parameters
> 3. Store this connection globally for re-use
> 4. Open your ADOServer with a select statement and that connection object
> 5. Close the connection when you close your app
>
> Use the same connection object for all your RS accesses (when you just
> want data) or for all your servers. Use the connection object directly
> to execute commands or SPs but they often return recordsets anyhow.
>
> Geoff
>
>
>

[toc] | [prev] | [next] | [standalone]


#1018

FromEckhard Sallermann <eckhard.sallermann@bur-kg.de>
Date2011-05-02 14:10 +0200
Message-ID<ipm70j$go5$1@dont-email.me>
In reply to#1016
_oAdoConnectionMSSQL1 := AdoConnection{}
_oAdoConnectionMSSQL1:ConnectionTimeout := 5
_oAdoConnectionMSSQL1:Open("Provider=sqloledb;" + ;
          "Network Library=DBMSSOCN;" + ;
          "Data Source=" + __cSqlServer + ";" + ;	 
"Trusted_Connection=yes",GetFullName(),nil,adAsyncConnect )



oAdoServer := AdoServer{ csql , ;
_oAdoConnectionMSSQL1 , AdOpenStatic, AdLockOptimistic, adCmdText  + 
adAsyncExecute }

this works, but not asynchron, the app freezes during executing

[toc] | [prev] | [next] | [standalone]


#1019

From"Willie Moore" <williem@wmconsulting.com>
Date2011-05-02 07:11 -0500
Message-ID<ipm715$41b$1@speranza.aioe.org>
In reply to#1016
Hey,

Here is some code that sets up for asynch operations. I hope that it helps

Regards,
Willie

    BEGIN SEQUENCE
        oRS := AdoRecordSet{}
        oRS:CursorLocation := adUseClient
        oRs:Properties:[Item,"Initial Fetch Size"]:Value := 500
        oRs:Properties:[Item,"Background Fetch Size"]:Value := 500
        oRS:Open(cSource, SELF:oConn, uCursorType, uLockType, 
adCmdText+adASyncFetch)
        DO WHILE _AND(oRs:State, AdStateFetching) != 0
            // wait in this loop until the process is complete
            Sleep(100)
            IF lShowWait
                // handle code to show a message to the user here with 
progress
            ENDIF
            ApplicationExec(EXECWHILEEVENT)
            IF _lcancel
                 BREAK "User aborted the Select"
             ENDIF
        ENDDO
        IF lShowWait
            // tear down any dialog that is showing the user messages about 
progress
        ENDIF

        IF oRS:State = adStateClosed
            oRS := NULL_OBJECT
        ENDIF
        ErrorBlock(cbErr)

    RECOVER USING uError
        ErrorBlock(cbErr)
        // handle error message here
        oRS := NULL_OBJECT
    END SEQUENCE

[toc] | [prev] | [next] | [standalone]


#1020

FromEckhard Sallermann <eckhard.sallermann@bur-kg.de>
Date2011-05-02 14:27 +0200
Message-ID<ipm7vc$q6t$1@dont-email.me>
In reply to#1019
hi Willie,

that´s okay, what i have forgotten is "oRS:CursorLocation := 
adUseClient", thank you so far.


but how to do without a recordset, only using AdoServer ??



Am 02.05.2011 14:11, schrieb Willie Moore:
> Hey,
>
> Here is some code that sets up for asynch operations. I hope that it helps
>
> Regards,
> Willie
>
> BEGIN SEQUENCE
> oRS := AdoRecordSet{}
> oRS:CursorLocation := adUseClient
> oRs:Properties:[Item,"Initial Fetch Size"]:Value := 500
> oRs:Properties:[Item,"Background Fetch Size"]:Value := 500
> oRS:Open(cSource, SELF:oConn, uCursorType, uLockType,
> adCmdText+adASyncFetch)
> DO WHILE _AND(oRs:State, AdStateFetching) != 0
> // wait in this loop until the process is complete
> Sleep(100)
> IF lShowWait
> // handle code to show a message to the user here with progress
> ENDIF
> ApplicationExec(EXECWHILEEVENT)
> IF _lcancel
> BREAK "User aborted the Select"
> ENDIF
> ENDDO
> IF lShowWait
> // tear down any dialog that is showing the user messages about progress
> ENDIF
>
> IF oRS:State = adStateClosed
> oRS := NULL_OBJECT
> ENDIF
> ErrorBlock(cbErr)
>
> RECOVER USING uError
> ErrorBlock(cbErr)
> // handle error message here
> oRS := NULL_OBJECT
> END SEQUENCE
>
>

[toc] | [prev] | [next] | [standalone]


#1031

From"Willie Moore" <williem@wmconsulting.com>
Date2011-05-03 07:56 -0500
Message-ID<ipou2f$k0a$1@speranza.aioe.org>
In reply to#1020
Eckhard,

What you need todo with the server is take the code  presented in the last 
message and make it method if your SQL class. It should return an 
ADORecordset. so you would execute your asynch method to get the recordset 
asynch and then pas it to your ADOServer.
     oRS := SELF:ExecuteRecordSetAsynch(cSQL,nFlags)
     oServer := ADOServer{oRS}

If you look at the source for ADOServer you will see that it can take a 
string for the source of an ADORecordset. But as soon as it starts to 
process, it needs the completed recordset before it can complete building 
the server. That is why  said that you need to execute your recordset first 
then add it to your server. If you go straight for the server, it will not 
build the recordset asynch.

Geoff does everything asynch in SQLMaster. When I have questions on how to 
do something in ADO, I usually read his source to figure out how he did it 
(if it is a technique that I don’t already know such as asynch).

Regards,
Willie

"Eckhard Sallermann"  wrote in message news:ipm7vc$q6t$1@dont-email.me...

hi Willie,

that´s okay, what i have forgotten is "oRS:CursorLocation :=
adUseClient", thank you so far.


but how to do without a recordset, only using AdoServer ??



Am 02.05.2011 14:11, schrieb Willie Moore:
> Hey,
>
> Here is some code that sets up for asynch operations. I hope that it helps
>
> Regards,
> Willie
>
> BEGIN SEQUENCE
> oRS := AdoRecordSet{}
> oRS:CursorLocation := adUseClient
> oRs:Properties:[Item,"Initial Fetch Size"]:Value := 500
> oRs:Properties:[Item,"Background Fetch Size"]:Value := 500
> oRS:Open(cSource, SELF:oConn, uCursorType, uLockType,
> adCmdText+adASyncFetch)
> DO WHILE _AND(oRs:State, AdStateFetching) != 0
> // wait in this loop until the process is complete
> Sleep(100)
> IF lShowWait
> // handle code to show a message to the user here with progress
> ENDIF
> ApplicationExec(EXECWHILEEVENT)
> IF _lcancel
> BREAK "User aborted the Select"
> ENDIF
> ENDDO
> IF lShowWait
> // tear down any dialog that is showing the user messages about progress
> ENDIF
>
> IF oRS:State = adStateClosed
> oRS := NULL_OBJECT
> ENDIF
> ErrorBlock(cbErr)
>
> RECOVER USING uError
> ErrorBlock(cbErr)
> // handle error message here
> oRS := NULL_OBJECT
> END SEQUENCE
>
> 

[toc] | [prev] | [next] | [standalone]


#1021

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-05-02 21:55 +0000
Message-ID<4dbf2843$0$99958$c30e37c6@exi-reader.telstra.net>
In reply to#1016
Eckie,

PLEASE please please look at Robert's samples.
All such code is right there!

Willie shows you one way to do what you seek but the better approach is 
to NEVER allow a select statement that takes longer than 1 second 
execute. In almost all circumstances... no exceptions, one second is too 
long so the need for user cancelling and asynch operations should be 
negligible. However, if you are insistent on providing it (like 
populating a million rows into a browser) it can be done as shown in 
Robert's samples.

Also, cancelling under such scenarios may not release the CPU as you 
expect. Things like connection timeouts and network timeouts cannot be 
avoided and probably shouldn't be avoided.

Geoff



"Eckhard Sallermann" <eckhard.sallermann@bur-kg.de> wrote in message 
news:iplmgc$f12$1@dont-email.me:

> ok, then please show me an example how to use adoserver and execute a
> statement asyncron. the user should be able to cancel while waiting for
> the result
>
>
> Am 30.04.2011 02:07, schrieb Geoff Schaller:
>
> >> what im i doing wring here ?
> >
>
> > Just about everything <g>.
> >
> > I don't quite understand why you creating a recordset and then passing
> > it to the server. Arse about in my books. And why all that looping and
> > waiting????
> >
> > Robert has perfectly clear examples - why don't you start with them?
> > Essentially the process should be:
> >
> > 1. create a connection object
> > 2. Set your default connection parameters
> > 3. Store this connection globally for re-use
> > 4. Open your ADOServer with a select statement and that connection object
> > 5. Close the connection when you close your app
> >
> > Use the same connection object for all your RS accesses (when you just
> > want data) or for all your servers. Use the connection object directly
> > to execute commands or SPs but they often return recordsets anyhow.
> >
> > Geoff
> >
> >
> >

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web