A Beginners Guide to ADO

Rick Lipkin · Post by **Rick Lipkin** » Sun Apr 15, 2007 6:53 pm

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" )

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

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

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" )
oCn:Execute( "DELETE FROM LWMFS WHERE IsNull(FILE_NUM)" )
oCn:Close()

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
#INCLUDE “FIVEWIN.CH”

//---------------------------------------------
Static Func _Cercla( cFILENUM )

LOCAL oErr, oDLG1, nREC, cSQL

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

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

IF oRS1:eof
Msginfo( "not found" )
RETURN(NIL)
ENDIF

oRS1:MoveFirst()

DEFINE DIALOG oDLG1 ;
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",;
"Date",;
"Permit_num",;
"Docket_num",;
"From_to",;
"Description",;
"Added_by"

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

ACTIVATE DIALOG oDlg1;
ON INIT oDlg1:SetControl( oBrow )

oRs1:Close()

RETURN NIL

//-------------------------------
STATIC FUNCTION SKIPPER( oRsx, nSkip )

LOCAL nRec := oRsx:AbsolutePosition

oRsx:Move( nSkip )

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

RETURN( oRsx:AbsolutePosition - nRec )

jose_murugosa · Post by **jose_murugosa** » Tue Apr 17, 2007 3:00 am

Thank you very much

Rick,

I imagine you had to work hard to get this knowledge

, so is very good that you share what you learn with others beginers (like me).

THANKS AGAIN, That is real spirit of service.

jlcapel · Post by **jlcapel** » Tue Apr 17, 2007 11:08 am

Hi Rip,

I just want to comment that

Code: Select all

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

Sometimes will not be enought to determine if this recordset is empty or not. I think should be better do:

Code: Select all

IF oRS:eof and oRs:Bof // query resulted with no records 
Msginfo( "not found" ) 
RETURN(NIL) 
ENDIF

ADO will set Bof and Eof to TRUE when no records results in the query.

Regards,
José Luis Capel

Tue Apr 17, 2007 12:12 pm

Or even better

Code: Select all

IF oRs:RecordCount = 0

But it will not work with all cursortype.

EMG

James Bott · Post by **James Bott** » Tue Apr 17, 2007 3:04 pm

Enrico,

>IF oRs:RecordCount = 0

>But it will not work with all cursortype.

Are you saying that oRS:RecordCount might be greater than zero even when there are no records in the recordset under some circumstances? If so, under what circumstances?

James

Tue Apr 17, 2007 3:10 pm

oRs:RecordCount() is -1 when an adOpenForwardOnly or adOpenDynamic cursor type is used.

EMG

JAGARCIA · Post by **JAGARCIA** » Tue Apr 17, 2007 3:11 pm

Hi,

is RLOCK() possible ?

Thanks,

JAGarcia

Tue Apr 17, 2007 3:16 pm

No, locking is automatic. You can choose pessimistic (from the first change to the Update() call, if I remember correctly) or optimistic (only during Update() call).

EMG

James Bott · Post by **James Bott** » Tue Apr 17, 2007 3:19 pm

Enrico,

>oRs:RecordCount() is -1 when an adOpenForwardOnly or adOpenDynamic cursor type is used.

OK, so oRS:RecordCount() <= 0 when there are no records, right?

Or, oRS:RecordCount() < 1

James

Tue Apr 17, 2007 3:21 pm

No, with those two cursor types oRs:RecordCount is -1 even if there are records in the recordset.

Thanks to MS for this...

EMG

James Bott · Post by **James Bott** » Tue Apr 17, 2007 5:07 pm

>No, with those two cursor types oRs:RecordCount is -1 even if there are records in the recordset.

Hmm. So how can we tell if there is an empty recordset?

James

Tue Apr 17, 2007 5:13 pm

Code: Select all

oRs:Open( ... )

IF oRs:EOF()
    ...
ENDIF

EMG

Rick Lipkin · Post by **Rick Lipkin** » Tue Apr 17, 2007 9:54 pm

To All

oRs:eof is an effective way of determining an EOF as well as a no find as in the first example.

I do have some difficulties running SQL UPDATE statements in a large loop over and over again .. Updating number values as in the first cSQL variable works flawlessly .. however when you start moving 'text' you have to pay attemtion to apostrophes within the charactor string .. will blow your UPDATE statement every time. Even so .. with just normal charactors .. the below UPDATE on the file_name column will just give spuadoc run-time failures .. the only common thread on the failures are special charactors in the txt itself .. such as *&-(),%# .. even if those charactors are legal within your string .. xHarbour reaches a point where it just 'breaks' .. ver 99.70 .org .. If you run the same UPDATE on a single event .. it works just fine on the same UPDATE and the same charactor values .. just re-cursively call the UPDATE statements in a long record loop ( text only ) .. it will 'break'

Otherwise .. I am pleased with ADO .. and am looking forward to applying it to MS SQL server soon.

Rick lipkin

/----------------------------
oRs:Find("file_num = '"+cFIND+"'" )

IF oRs:eof
oRs:MoveFirst()
oRs:Find("file_num = '"+cFIND+"'" )

IF oRs:eof()
Msginfo( "File Number "+cFind+" can not bre found" )
oRs:MoveFirst()
ENDIF
ENDIF

//------------------------------

// update all the relational tables

cID := STR( oRs:Fields( "id" ):Value)
cNAME := oRs:Fields( "FILE_NAME" ):Value
cFILE := oRs:Fields( "FILE_NUM" ):Value

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

cSQL := "UPDATE cercla_indexing SET ASSOCIATED_ID = "+"'"+cID+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()

IF cBOTH = 'Y'

// flakey here ..

cSQL := "UPDATE cercla_indexing SET FILE_NAME = '"+cNAME+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()
ENDIF

cSQL := "UPDATE mining_indexing SET ASSOCIATED_ID = "+"'"+cID+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()

IF cBOTH = 'Y'
cSQL := "UPDATE mining_indexing SET FILE_NAME = '"+cNAME+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()
ENDIF

cSQL := "UPDATE rcra_compliance_indexing SET ASSOCIATED_ID = "+"'"+cID+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()

IF cBOTH = 'Y'
cSQL := "UPDATE rcra_compliance_indexing SET FILE_NAME = '"+cNAME+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()
ENDIF

cSQL := "UPDATE rcra_permitting_indexing SET ASSOCIATED_ID = "+"'"+cID+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()

IF cBOTH = 'Y'
cSQL := "UPDATE rcra_permitting_indexing SET FILE_NAME = '"+cNAME+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()
ENDIF

cSQL := "UPDATE solidwaste_indexing SET ASSOCIATED_ID = "+"'"+cID+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()

IF cBOTH = 'Y'
cSQL := "UPDATE solidwaste_indexing SET FILE_NAME = '"+cNAME+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"
oCn:Execute( cSQL )
SysReFresh()
ENDIF

oCn:Close()
oCN := NIL

SysReFresh()

oRS:Fields( "KEEP" ):value := 'Y'
oRs:Update()

SysReFresh()

James Bott · Post by **James Bott** » Tue Apr 17, 2007 10:30 pm

I'm no SQL expert, but try switching your single and double quotes so that you are not surrounding your text strings with single quotes.

James

Rick Lipkin · Post by **Rick Lipkin** » Wed Apr 18, 2007 12:28 am

James

SQL can only interpret single quotes to pass as values :

cSQL := "UPDATE rcra_compliance_indexing SET ASSOCIATED_ID = "
cSQL += "'"+cID+"', FILE_NAME = '"+cNAME+"'"
cSQL += " WHERE ASSOCIATED_FILE_NUM = '"+cFILE+"'"

This string equates to:

"UPDATE rcra_compliance_indexing SET ASSOCIATED_ID = '12345', FILE_NAME = 'JOES-BAR & GRILL' WHERE ASSOCIATED_FILE_NUM = '45678'"

I have no problem with the numeric values .. where my string breaks is passing the file_name string. Don't know why .. but there is no ryme or reason for the failure .. never happends on the same record .. so I can not find any specific reasons .. just my gut instinct .. VM gets taxed or there is a memory leak in repeating the UPDATE text ( looping thru thousands of records ) .. and then it just breaks...

I just decided to take out the file_name update ... and converted over the associated_id ..

Rick Lipkin