Another for MDB recorset

[Rick Lipkin]

Outstanding .. I did not know you can re-sort a recordset .. many times I have a 'find' routine that I need to re-order the same information .. to do this I have used my origional order by query ( lname ) and if I needed to find a group of records matching a like % on fname ...I used the oRs:Filter .. but the filter is a bit clumsy and you have to release the filter

Thanks for the information ..

Rick Lipkin

fyi .. I have not given up on oracle .. I have the oracle 11 disks and plan on trying to load the client in the near future to see if I can connect

[nageswaragunupudi]

Mr Rick Liptin

Still Find is very powerful. It is like our classical locate and locate next in xBase. But we can as well use it in the place of Seek.

> but the filter is a bit clumsy and you have to release the filter

Yet, it is powerful. We can even set filter to an array of book marks. At times very handy and useful. oRs:Filter := { bm1, bm2, ....}

If you find setting and releasing filters needs special attention, consider an alternative, create a clone, set filter and then close it.

oRs2 := oRs:Clone()
oRs2:Filter := ................
do something with oRs2
oRs2:Close

These are some ideas

[James Bott]

NageswaraRao,

Can you sort recordsets on more complex information like concantated multiple fields? How about descending?

James

[nageswaragunupudi]

Mr James

Yes

oRs:Sort := "FIRST , CITY DESC"
ASC is assumed if not specified.

also we can sort on multiple fields of char, number, dates etc.

[Enrico Maria Giordano]

I'm afrad that Sort() method works only with MDB. As an example, it doesn't work with MSDE.

EMG

[nageswaragunupudi]

Sort works with MDB, Oracle, MSSQL Server.

Actually it is an internal feature of ADO and has nothing to do with the data provider, because the ADO engine sorts the recordset within memory totally on the client. The server does not come into picture at all. It even works with synthetic recordsets totally built on the client. It is something analogous to sorting an array after we read data into an array in the memory of our PC.

Kindly recheck.

I use the syntax oRs:Sort = <expression>. I do not know if oRs:Sort( <exp> ) works. Does it work this way also ?

[nageswaragunupudi]

Mr Enrico

I'm afrad that Sort() method works only with MDB. As an example, it doesn't work with MSDE.

EMG

I have not worked with MSDE. May I know how is your experience with the product? Do you think we can do development work with this before we test on real servers ?

Thanks in advance.

[Enrico Maria Giordano]

I have not worked with MSDE. May I know how is your experience with the product? Do you think we can do development work with this before we test on real servers ?

Thanks in advance.

I have MSDE installed and Sort() method doesn't seem to work. So I think it doesn't work with MS SQL Server either. I don't know about MySQL and other engines but yes, I'd test real servers before using Sort() method estensively.

EMG

[Enrico Maria Giordano]

This is the sample I tried:

Code: Select all

#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4


FUNCTION MAIN()

    LOCAL oRs

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    oRs:Open( "SELECT * FROM T_Utenti ORDER BY Utente", "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=EMAG\Emag;Initial Catalog=Quadro", adOpenForwardOnly, adLockReadOnly )

    oRs:Sort = "Passw"

    WHILE !oRs:EOF
        ? oRs:Fields( "Utente" ):Value, oRs:Fields( "Passw" ):Value
        oRs:MoveNext()
    ENDDO

    oRs:Close()

    INKEY( 0 )

    RETURN NIL

And this is the error I get:

Code: Select all

Error ADODB.Recordset/6  DISP_E_UNKNOWNNAME: _SORT Arguments: ( [ 1] = Type: C Val: Passw)

Do you see any problems in my sample that can explain the error?

EMG

[Enrico Maria Giordano]

Found! I have to use

Code: Select all

oRs:CursorLocation = adUseClient

It is perfectly logical. But still I don't know if it is a good idea to use a technique that forces client cursor usage.

EMG

[nageswaragunupudi]

Mr Enrico

You found the reason. Most of the data and methods do not work on serverside cursors and some work only for serverside cursors but not for clientside.

I have a few advices to make in general, based on theory, widely accepted good practices, confirmed by our own experiences.

It is desirable and the normal practice to open recordsets on the client side only. Opening serverside cursors is an exception.

In real life situations the applications are supposed to put least demands on Server and Network resources. Therefore the best practices are ( our discussion is limited to client-server 2-tier architecture only ) :-

1. Dont open more than one connection per application.
2. Ask the server for just enough information required for the purpose. Quickly retrieve it and say Thanks. Leave the Server free to attend to other requests from other users / applications.

I have seen in our forums that every record set is opened with connection information.

Disadvantages:

a) While opening each recordset we are opening a new connection and leaving those connections open on the server till we finish our work at our liesure. Imagine hundreds or users opening tens of connections each and locking the server's limited resources. It is not surprising if the server soon starts denying further requests till previous resources are released. Or server's performance gets bogged down.

b) Opening a connection takes more time. Opening a recordset with a connection already opened is faster.

It seems this usage is adopted from sources available from asp codes and webpage sources. That is 3-tier architecture. There is an application server sitting between us and the server. That situation is much different and good practices are diferent.
c) Classified information like server names, database names, passwords are scattered all around our modules in the application code. Serious security lapse. If the connection is opened in only one function, that alone can be changed whenever passwords and usernames are changed by the administrators for security reasons.

Recommendations:
i) Open one connection at the beginning and store it in a global variable ( better as a return value of a function ). Use the connection as RecordSet's ActiveConnection property while opening a new Recordset. We open the recordset faster ( our users are happy with our program ) and consume minimum resources of the server ( Our System Adminstrator friends are thankful to us.). Amy periodical changes in the connection parameters can be easily changed. Actually it is better not to hardcode such params.

ii) Ask the minimum information and give the server the minimum work. Example, we can sort the recordset at the client instead of bothering the server. After getting the information, disconnect the recordset and leave the server for other works. We can always reconnect if we want to make any updates or resyncs.

iii) More and more advanced our programming needs are more and more complex our sql queries become. Tuning of sql queries is an essential knowledge we need to aquire. Badly drafted sql's can lock server resources for unduly long periods, bringing down the server performance.

iii) Unless required, open client server cursors. Application performance will be very fast. Users will be happy with the blazing speeds of browses. Serverside cursors are not only slow but also keep the server tied up. Also there is a known issue with opening more than one serverside cursor on mssql server at the same time. Easier way is Open the connection at the outset with cursor location set to adUseClient. By default all recordsets will be opened client side.

[Silvio]

EMG or nageswaragunupudi


I must implement an TAB
I want inser this tab to the bottom of a xbrowse
when I clicl on a folder the Xbrowse must order the record to one order

@ oApp():oDlg:nGridBottom, nSplit+2 TABS oApp():oTab ;
OPTION nOrder SIZE oApp():oWndMain:nWidth()-50, 12 PIXEL OF oApp():oDlg ;
ITEMS ' First ', ' Last '
ACTION ( nOrder := oApp():oTab:nOption ,;
oRs:Sort (nOrder)),;
oRs:movetop() ,; oApp():oGrid:Refresh(.t.) ,;


How I can make it ?
)

[nageswaragunupudi]

I think we discussed this earlier. Depending on nOption, set oRs:Sort := <columnname>, oBrw:Refresh().

Make sure you opened the recordset with adUseClient option. The above code will work

[Enrico Maria Giordano]

I have seen in our forums that every record set is opened with connection information.

They are only minimal samples. Nothing more.

Recommendations: [...]

That's why I don't like client/server at all. Too much constraints. An archaic technology passed off as new.

EMG

[James Bott]

nageswaragunupudi,

Thanks for the client-side/server-side cursor explanations.

> Open one connection at the beginning and store it in a global variable ( better as a return value of a function ).

This sounds like good use for a connection object. You could put a connection object inside a SET/GET function. Then you could retrieve the connection object and use it's methods or vars anywhere in the application. You could do things like this:

oConnection:= TCSConnection():new( ... )
connection( oConnection )
connection():activate()
connection():open()
connection():close()

Regards,
James