Tuesday, June 30, 2009

A Beginners Guide to ADO

CCH : ADO users should find this thread interesting...


To All

Over the past few days I have had to learn how to connect and manipulate an Access database .mdb. with xHarbour and FWH.

I would like to dedicate my “Beginners Guide for ADO” to Enrico Maria Giordano who has been an inspiration and a tireless help in answering every one of my questions.

For anyone who has had to deal with MS Access .. here is a general guideline as to how to work with ADO ..

Here is the MSDN link for more info:

http://msdn.microsoft.com/library/defau ... erence.asp

Again .. many thanks Enrico !!

Rick Lipkin
SC Dept of Health, USA

ADO general connections and methods:

There are 4 types of cursors supported by ADO: ( first parameter )

(0)adOpenForwardOnly: This is the lightest (cheapest) cursor, and the default when opening a recordset. It allows only forwards movement. Only the most minimal information about the recordset is calculated
by Jet (eg you can't even get a .recordCount of the total number of records in the recordset). If you try to move backwards using this cursor, the recordset is closed, and the query re-executed. Avoid doing this!

(1)adOpenKeyset: A static snap-shot of the primary key values of the records that match your search criteria are put into the recordset. As you scroll backwards and forwards, the primary key value in the recordset is used to fetch the current data for that record from the database. This cursor thus allows you to see updates to the data made by other users, but it doesn't let you see new records that have been added by other users (because the primary key values for those records are not in your recordset).

(2)adOpenDynamic: A dynamic snapshot of the database is maintained by OLEDB/ADO. All changes by other users to the underlying database are visible. Obviously this is the most sophisticated cursor, and thus is usually the most expensive. Because the data in the recordset is dynamic,
attributes like AbsolutePosition and AbsolutePage can not be set. The adOpenDynamic cursor is not supported by the Jet OLEDB Provider.

(3)adOpenStatic: A static snap-shot of the records that match your search criteria are put into the recordset. You can scroll forwards and backwards, and set bookmarks. Changes made to the database by other users however are not visible - all you can see are the records that matched your search at the point in time when the query was executed

( second parameter )

(1) adlockReadOnly
(2) adlockPessimistic
(3) adlockOptomistic
(4) adlockBatchOptomistic

How to Open a recordset .. this example is for MS Access .mdb

oRs := CREATEOBJECT( "ADODB.Recordset" )

oRS:Open( "SELECT * FROM LWMFS ORDER BY FILE_NUM" , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
MsgInfo( "Error in Opening LWMFS table" )

IF oRS:eof // query resulted with no records
Msginfo( "not found" )

How to Open a connection .. like for running global inserts, deletes

oCn := CREATEOBJECT( "ADODB.Connection" )
oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=lwmfs.mdb" )

Various Methods for ADO recordsets

oRs:Delete() - delete
oRs:MovePrevious() - skip -1
oRs:MoveNext() - skip +1
oRs:MoveLast() - go bott
oRs:MoveFirst() - go top
oRs:AddNew() - append blank
oRs:Update() - commit
oRs:Find() - seek, locate

some examples

oRs:Find( "file_num = '"+cFIND+"'" )
oRs:Fields("fieldname"):Value := 'MyValue' - replace
oRS:Filter := "file_num = '"+cFIND+"'" - scope condition

sample twbrowse for FWH

STATIC oRs1, oBrow

Static Func _Cercla( cFILENUM )


oRs1 := CREATEOBJECT( "ADODB.Recordset" )
cSQL += "WHERE cercla.ASSOCIATED_FILE_NUM ='"+cFILENUM+"'"+" order by date_"

oRS1:Open( cSQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LWMFS.mdb", 1, 3 )
MsginFo( "failed to open table" )

IF oRS1:eof
Msginfo( "not found" )


FROM 7,7 to 35,104 ;
TITLE "Matching CERCLA Docket Browse for File Number "+cFILENUM

@ 0, 0 LISTBOX oBrow FIELDS ;
oRs1:Fields("associated_file_num" ):Value,;
DTOC( oRs1:Fields( "DATE_" ):Value ),;
oRs1:Fields( "associated_permit_num" ):Value,;
oRs1:Fields( "docket_num" ):Value,;
oRs1:Fields( "from_to"):Value,;
oRs1:Fields( "description"):Value,;
oRs1:Fields( "added_by"):Value;
SIZES 80,80,100,100,200,500,80;
HEADERS "File_num",;

oBrow:bLogicLen = { || oRs1:RecordCount }
oBrow:bGoTop = { || oRs1:MoveFirst() }
oBrow:bGoBottom = { || oRs1:MoveLast() }
oBrow:bSkip = { | nSkip | Skipper( oRs1, nSkip ) }
oBrow:cAlias = "ARRAY1"

ON INIT oDlg1:SetControl( oBrow )




LOCAL nRec := oRsx:AbsolutePosition

oRsx:Move( nSkip )

IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF

RETURN( oRsx:AbsolutePosition - nRec )

1 comment:

  1. Hi CCH,
    Thanks for this topic. Can anyone explain - how to check whether a perticular table exists in a database?

    With best regards.



Welcome to Clipper... Clipper... Clipper

In 1997, then using Delphi 3, I had already created 32-bits Windows applications for HRIS, ERP and CRM. In 2007, using Ruby on Rails, an AJAX powered CRM site running on Apache & MySQL was created and I am now using Visual Studio .Net 2008 to create web-based projects and Delphi 7 for Win32 applications using SQL2005 & DBFCDX.

So, why then am I reviving the Original Clipper... Clipper... Clipper via a Blog as CA-Clipper is a programming language for the DOS world ? Believe it or not, there are still some clients using my mission-critical CA-Clipper applications for DOS installed in the late 80's and up to the mid 90's. This is testimony to CA-Clipper's robustness as a language :-)

With the widespread introduction of Windows 7 64-bits as the standard O/S for new Windows based PCs & Notebooks, CA-Clipper EXE simply will not work and it has become imperative for Clipper programmers to migrate immediately to Harbour to build 32/64 bits EXEs

Since 28th January 2009, this blog has been read by 134,389 (10/3/11 - 39,277) unique visitors (of which 45,151 (10/3/11 - 13,929) are returning visitors) from 103 countries and 1,574 cities & towns in Europe (37; 764 cities), North America (3; 373 cities) , Central America & Caribeans (6; 13 cities), South America(10; 226 cities), Africa & Middle-East (12; 44 cities) , Asia-Pacific (21; 175 cities). So, obviously Clipper is Alive & Well : -)

TIA & Enjoy ! (10th October 2012, 11:05; 13th November 2015)

Original Welcome Page for Clipper... Clipper... Clipper

This is the original Welcome Page for Clipper... Clipper... Clipper, which I am republishing for historical and sentimental reasons. The only changes that I have made was to fix all the broken links. BTW, the counter from counter.digits.com is still working :-)

Welcome to Chee Chong Hwa's Malaysian WWW web site which is dedicated to Clipperheads throughout the world.

This site started out as a teeny-weeny section of Who the heck is Chee Chong Hwa ? and has graduated into a full blown web site of more than 140 pages (actually hundreds of A4 size pages) ! This is due to its growing popularity and tremendous encouragements from visiting Clipperheads from 100 countries worldwide, from North America, Central America, Caribbean, South America, Europe, Middle-East, Africa and Asia-Pacific. Thanx Clipperheads, you all made this happen !

What is Clipper ?

You may ask, what is this Clipper stuff ? Could Clipper be something to do with sailing as it is the name of a very fast sailing American ship in the 19th century ?

Well, Clipper or to be precise, CA-Clipper is the premier PC-Software development tool for DOS. It was first developed by Nantucket Corporation initially as a compiler for dBase3+ programs. Since then, CA-Clipper has evolved away from its x-base roots with the introduction of lexical scoping & pre-defined objects like TBrowse. As at today, the most stable version ofClipper is 5.2e while the latest version, 5.3a was introduced on 21 May 1996.

As at 11th November, 1996, an unofficial 5.3a fixes file was made available by Jo French. See the About CA-Clipper 5.3a section for more details. BTW, Jo French uploaded the revised 5.3a fixes file on 20th November, 1996.

Latest News

The latest news is that CA has finally released the long-awaited 5.3b patch on 21 May, 1997.

For 5.3b users, you must a take a look at Jo French's comments on unfixed bugs in 5.3b.

BTW, have you used Click ? If you're a serious Clipperprogrammer and need an excellent code formatter, Click is a natural choice. How to get it ? Simple, access Phil Barnett's site via my Cool Clipper Sites.

32-bits Clipper for Windows ?

Have you tried Xbase ++ ? Well, I have and compared to Delphi (my current Windows programming tool of choice), I'm still sticking to Delphi.

Anyway, you should visit the Alaska Home Page. Give it a chance and then draw your own conclusions !.

The Harbour Project

Is this the future of Xbase ? Take a look at at the Harbour Project

You are Visitor # ...

According to counter.digits.com, you are visitor since 3 June 1996.

If you like or dislike what you see on this website, please drop me a line by clicking the email button at the bottom of this page or better still, by filling out the form in my guest book. If you are not sure what to write,click here to take a look at what other Clipperheads have to say.