A Beginners Guide to ADO

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

http://forums.fivetechsupport.com/viewtopic.php?t=6691

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 )

Latest Harbour MiniGUI 1.6 Extended Edition (Build 69)

CCH : From the HMG Extended Forum

Hi All,

The Harbour MiniGUI 1.6 Extended Edition (Build 69) setup is published at the following URL:

http://hmgextended.com/files/CONTRIB/hmg-1.6-69-setup.zip

and the Lite build 69 at the following URL:

http://hmgextended.com/files/CONTRIB/hmg-1.6-69-setup-lite.zip

There is the following changelog for this build below:

2009/06/25: Build 69 (HMG 1.6 Extended Edition) Published.
* New: The Main menu supports an optional MRU menu item command now.
Syntax:
MRU[ITEM] [ ] ; // caption for empty MRU item
[ ] ; // .INI to manipulate
[ SECTION ] ; // The name of the INI section
[ ] ; // The number of MRU items to manage
[ ACTION MsgInfo( cMruItem ) ] ; // cMruItem is automatically provided
[ NAME ] // The name of MRU items (default is "MRU_#")
Additional functions:
- AddMRUItem(, "OnClickFunc(Item)")
- AddMenuElement(, "OnClickFunc(Item)")
- SaveMRUFileList()
- ClearMRUList()
Based upon a contribution by Janusz Pora <januszpora@onet.eu>
(see demos in folder \samples\Advanced\MRU)
* Fixed: GetBox control allows to erase "-" sign in numeric value when control is re-edited.
Contributed by Jacek Kubica <kubica@wssk.wroc.pl>
* Changed: The function _dummy() is defined as pseudo-function now.
Contributed by Grigory Filatov <gfilatov@freemail.ru>
* Enhanced: The GRID control supports an optional CHECKBOXES clause:
- New: Read/Write property CheckboxItem
You can set/get this property at runtime:
- function syntax:
SetProperty ( Form, Control, 'CheckboxItem', nItem, lState )
GetProperty ( Form, Control, 'CheckboxItem', nItem )
- pseudo-OOP syntax:
Form.Grid.CheckboxItem( nItem ) := lState
Form.Grid.CheckboxItem( nItem ) --> lState
Contributed by Eduardo Fernandes <modalsist@yahoo.com.br>
(see demo in folder \samples\Basic\CheckBox_Grid)
* Enhanced: The function WindowsVersion() detects now a Windows 7 (seven).
Borrowed from Harbour 2.0 distribution.
Contributed by Grigory Filatov <gfilatov@freemail.ru>
* Updated: Adaptation FiveWin Class TSBrowse 7.0 in HMG:
- Fixed: Usage of function ComboWBlock( oBrowse, "Code", nColumn, { aItems, aCode } ).
Bug was reported by Carlos <arcjv@yahoo.com.ar>.
Contributed by Janusz Pora <januszpora@onet.eu>
* Updated: HbSQLite3 library source code (see in folder \Source\HbSQLite3):
- updated for using SQLITE3 version 3.6.15
Contributed by Grigory Filatov <gfilatov@freemail.ru>
(see demos in folders \samples\Advanced\SQLITE and \samples\Advanced\SQLITE_2)
* Updated: MiniPrint library (see source in folder \Source\MiniPrint):
- Fixed: Save EMF images to local path.
Based upon a contribution by Ivanil Marcelino <ivanil@linkbr.com.br>.
* Updated: Synchronized an Extended HMG with an Official HMG 2.9.2:
- New: Property style synonyms for the following DEFINE WINDOW command clauses:
- Row (At Clause)
- Col (At Clause)
- WindowType (Main/Mdi/Modal/Child/SplitChild Clauses)
(see demo in folder \samples\Basic\HELLO_WORLD).
The following synonyms for alternate syntax were added:
- ALIGNMENT (CENTERALIGN,RIGHTALIGN);
- DATATYPE (NUMERIC,CHARACTER,DATE);
- CASECONVERT (UPPERCASE,LOWERCASE);
- TICKMARKS (NOTICKS).
* Updated: Harbour Compiler 2.0.0beta1 (SVN 2008-06-18 14:21):
* Updated: HbSQLite3 library source code (see in folder \Source\HbSQLite3)
Contributed by Grigory Filatov <gfilatov@freemail.ru>
(look at ReadMe.txt in folder \harbour and WhatsNew.txt in folder \harbour\doc)
* Updated: HMGS-IDE v.0.8.8. Project Manager and Two-Way Visual Form Designer.
Contributed by Walter Formigoni <walter.formigoni@uol.com.br>
(look at changelog.txt in folder \Ide)
* Updated: 'Http Get' sample. Contributed by Grigory Filatov <gfilatov@freemail.ru>
(see in folder \samples\Basic\HttpGet)
* Updated: 'Combined Search Box' sample.
Problem was reported by Alen Uzelac <solvox@bbm.hr>.
Contributed by Grigory Filatov <gfilatov@freemail.ru>
(see demo2.prg in folder \samples\Advanced\CombinedSearchBox)
* Updated: 'Grid Print' user component interface (UCI) sample:
- Fixed: Show window command error
- Fixed: When columns are summed up, column width is not calculated according to
the total. Now, total is within the column width.
- Changed: When column sum is used, number mask passed on is used for the whole column
to be printed to make the column look uniform.
- New: "Reset Form" button is added to reset the report configuration saved
in the config file reports.cfg.
Contributed by S.Rathinagiri <srgiri@dataone.in>
(see in folder \samples\Advanced\GridPrint)
* Updated: 'Transparent Form' samples. Contributed by Grigory Filatov <gfilatov@freemail.ru>
(see demos in folder \samples\Advanced\Transparency)
* Updated: 'DBFview v.0.78' sample. Problem was reported by Petr Chornyj <myorg63@mail.ru>
Contributed by Grigory Filatov <gfilatov@freemail.ru>
(see in folder \samples\Applications\DBFview)

This release of MiniGUI is considered stable and ready for production use.

Your comments are welcome!

--
With Best Regards,
Grigory Filatov
[MiniGUI Team]

free.Harbour.com : free fully automated xHarbour.org installers

CCH: From the Internet...

Welcome to free.xHarbour.com, the download site for free fully automated xHarbour.org installers. We are currently focusing on Windows distributions, for Borland, MSVC and PellesC compilers.


Our goal is to provide frequent releases of the free xHarbour project, supplemented by some free goodies, and most importantly, provide fully automated installers that include everything you need to get started developing with xHarbour.


All our installers will check your system for your preferred C compiler. If the installer does not find it, it will automatically download the necessary files and start the installation for the C compiler. All you need to do is follow the standard installation procedure. Plain and simple!


Want know more? Visit the Frequently Asked Questions pages. ;-)

New Harbour Users Forum [Updated 16th June 2009]

From the recent discussions on the Harbour-Project Main Developers List, it is clear that the Harbour Developers does not really welcome requests by Application Developers i.e Harbour users for support.

To their credit, Viktor (Main Harbour Contributor) has set up a new Harbour Users Forum at
http://sourceforge.net/apps/phpbb/harbour-project/

I have just joined as a member and note that there are 3 forums as follows :-

• Harbour Users
• Harbour Tips & Tricks
• Off Topic

My personal view is that they should add an Announcements Forum as well.

HMG : Latest Version 2.92 by Roberto Lopez

CCH: From the HMG Forum

HMG 2.9.2

- Fixed: Print Preview. 'go to page' window not refresh preview window.

- Modified: Documentation completed, fixed, indexed, linked and formatted.

- New: Property style synonyms for the following GUI
objects (look at documentation for details):

- WINDOW
- TOOLBAR
- TREE
- TAB
- SPLITBOX
- CONTEXT MENU
- DROPDOWN MENU
- MAIN MENU
- STATUSBAR

The following synonyms for alternate syntax were added:

- LINES (NOLINES)
- ALIGNMENT (CENTERALIGN,RIGHTALIGN)
- TODAY (NOTODAY)
- TODAYCIRCLE (NOTODAYCIRCLE)
- DATATYPE (NUMERIC,CHARACTER,DATE)
- CASECONVERT (UPPERCASE,LOWERCASE)
- TICKMARKS (NOTICKS)
- AUTOSIZEWINDOW (NOAUTOSIZEWINDOW)
- AUTOSIZEMOVIE (NOAUTOSIZEMOVIE)
- ERRORDLG (NOERRORDLG)
- MENU (NOMENU)
- OPEN (NOOPEN)
- PLAYBAR (NOPLAYBAR)


Download: http://sourceforge.net/projects/harbourminigui/


Regards,

Roberto.

What is the difference between xHarbour SQLRDD and Harbour RDDSQL. ?

CCH : An interesting post by Mindaugas Kavaliauskas on 7th June 2009 "Harbour Project Main Developer List."

RDDSQL implements accessing SQL query result via RDD interface, i.e. you write a SQL query and you are able to reach result of a query using FIELDGET(), DBSKIP(), DBGOTO(), etc. All these function calls does not generates additional queries, it will operate on result
of the query you've asked for.

RDDSQL does not try to be a "transparent" sollution for DBF to SQL move, and does not try to emultate DBF specific operations (ex., DBAPPEND(), DBDELETE(), OrdCreate(), FLOCK(), etc.) via some unnatural SQL operations.

I want to discuss this in more detail. Many current RDDs for accessing SQL servers (ex. SQLRDD from xHarbour.com) tries to make a feeling you are working with DBF file, but not with SQL database.

SQL server does not support many features, ex. RECNO(), deleted flag, file locks, record locks. These RDDs are emulating these features to make feeling of DBF. DELETED() function is emulated by creating additional table columns to store delete flag. Some "hidden system"
tables are used to register locking operations and emulate record and file locks in DBF style. The idea of SQL query is also lost. If you do a simple loop

DBUSEAREA(, "select * from my_table")
DO WHILE ! EOF()
somefunc( FIELD->some_sql_field )
DBSKIP()
ENDDO

"Transparent" DBF to SQL solutions usually will read SQL rows in portions, let's say 100 records per query. So, hidden queries are generated. If another client issues UPDATE query between these hidden queries, it can happen, that the same row will be fetched the second time, or does not fetched at all.

The idea of RDDSQL is different. It does not make hidden queries. All queries should be made explicitly by programmer. RDDSQL gives access to query result via RDD interface, it does not tries to emulate DBF and be "transparent" solution for DBF to SQL migration. If you
call:

DBUSEAREA(, "select * from my_table")

the entire query (it could contain millions of records, so be carefull!) will be cached.


The features of RDDSQL approach are:
- It's possible to access SQL database of other applications. Other
applications usualy does not follow agreement of "transparent" SQL
drivers about additional DELETED column, _RECNO in the end of index
expression, etc. Access of SQL database of another applications is
sometimes not possible.

- It's query oriented. That means a simple DO WHILE ! EOF() loop will iterate each records once and only once. This is not true for "transparent" DBF to SQL solutions.


Regards,
Mindaugas

CachéRDD : The real-time RDBMS RDD for Harbour family of Compilers

CachéRDD is a RDD (Replaceable Database Driver) for open-source Harbour ( http://www.harbour-project.org ) and xHarbour ( http://www.xharbour.org ) compilers to connect to InterSystems ( http://www.intersystems.com ) Caché, the post-relational, massively scalable database.

CachéRDD is a classic blend of true RDBMS and time-tested traditional xbase syntax as,

the same SEEK,
the same SKIP,
the same GOTO, GOTOP, GOBOTTOM,
the same INDEX ON,
the same TBROWSE,
and that too with billions of records,
with same SPEED, even faster in some cases...

the same RLOCK,
the same FLOCK,
even extended to be honored by external processes,
the same USE EXCLUSIVE / SHARED

The ONLY difference is that now your data is more

SECURE,
SCALABLE,
FREE OF INDEX CORRUPTION,
with
TRANSACTION CONTROL,
and subject to
INTEGRATION WITH WIDE RANGE OF EXTERNAL SQL TOOLS.

AND at top of all you need not to change a single line of source except for resolving table name issues and adjusting a few index expressions here and there.

CachéRDD does not issue any SQL commands to the database for any of the RDD functions nor it needs any DSN ( Data Source Name ). CachéRDD does not require ODBC drivers to be installed. CachéRDD talks to the database directly with its lower level interface. This is the main reason that like DBFCDX functionality has been achieved.

Looks interesting ? Learn more...

Breaking News : CacheRDD, a real-time RDBMS RDD for Harbour family of compilers

CCH : As posted in our Guest Book by Pritpal Bedi

Hello Everybody

Today is a special day in my life and I have
decided to share one of my best developments with all of you.

Let me introduce CacheRDD, a real-time RDBMS RDD for
Harbour family of compilers. This is the FIRST RDD in whole history
of xbase dialect that is a TRUE RDBMS RDD. Please visit
www.vouch.info/cacherdd for in depth review of its
accomplishments ( and shortcomings ).

The RDD ( binaries only ) will be available for
Harbour - MINGW, MSVC, BCC, PELLESC, WATCOM
xHarbour.org - BCC
xHarbour.com - XCC

Today I am just announcing. Within the next week or so
I will make available the complete distribution. The delay is
because today I am not prepared to assemble disto but I wished
that it be announced ( due to that special day ).

Regards
Pritpal Bedi

[Harbour] GTWVW => GTWVG Port - A Clarification by Pritpal Bedi

CCH: Quoted verbatim from a thread on the Harbour Developers List


Hi Budiyanto, All

> If you mean revising (or rewriting) GTWVW into Harbour, then I'll be very
> much interested, as well as
> many others. But please keep it backward compatible as much as possible. I
> remember you said here
> in the past that GTWVW is "impossible" to port to the new GT API because
> there is a conflict with
> GTWVW's use of first parameter as window designator (for most functions).
> Do you finally find a way
> to solve that problem? Or do you plan change the convention in GTWVW? (I
> hope it is the former.)
>
> Am I interpreting it right with what you mean by "porting GTWVW in GTWVG"?
>

When GTWVW came into existance I had already ported my applns to GTWVG,
the then GTWVT+WVTGUI, so I never looked into GTWVW deeply. Everything
I needed was available in GTWVG ( current name ). Rather at that time
I thought pseudo emulation of controls was good enough to appeal my clients
and I was saved of the time to learn a new protocol. With only little
efforts I was able to roll my applns with a new look and feel.

Over the time concepts changed. Multi-Window and Multi-Threading forced me
to look into real windows world. I was convinced that a lot can be achieved
on this front as MT and GT modal of Harbour is much superior to any other in the
market today.

I looked around for effective GUI modal to base my next developments
targetting not only my own needs but the Clipper community in general. The available
modals in-use for Harbour around were as follow:

1.  FWH
2.  VXH
3.  HWGUI
4.  MINIGUI
5.  XAILER
6.  GTWVW

Every modal had its pros and cons.  Some had documentation missing,
some were pure IDE based, some dynamic but heavily based on #define(s)
( due a large number of parameters for a single activity ), and some lacked
OOP.
Every modal had its own parameters passing and calling convensions.
And, for sure, all modals lacked adaptation to MT, the biggest mainstay
in the coming future.

What I wanted to base next efforts on development of a GUI :

1) It must be adaptable to MT modal.
2) It should be able to take advantage of MW GT whereever possible.
3) It must adhere to standardization of parameters and calling convensions.
4) It must be having a well written documentation.
5) If possible, it must have a user-base.
6) And above all, it must be able to base Multi-Platform GUI.

After a lot of thinking and spadework I could not accept any of the above.
I just picked Xbase++. This compiler's subsystems were almost fitting
my ideology, is having a large user-base, ships with descent documentation,
complete with MT capabilities and nicely fits into Multi-GT environments.

Moreover I had worked with this compiler for some time and know how
best its parameters passing and calling conventions were. Also I wanted to
have a well define class framework to dream for a multi-platform GUI framework.

I knew to follow an already established framework is always a difficult
task.

To create a new protocol you are the master of your own thoughts. But to
copy feature-by-feature is very difficult. But I choose the difficult way.

Also I wanted to have an integrated library which could absorb different
scattered GUI modals into one as all were having one aspect in common -
Harbour.

My first results are quiet encouraging and now GTWVG can host:

* pure console,
* pseudo GUI console,
* Xbase++ consols and dialogs with xbase parts,
* pure consoles with Xbase parts,
* and a mixture of any of the above.

In this evolution process GTWVW becomes the next natural GEM to
be inducted in this whole picture, and probably FWH too, who knows.

If you invest a little time in GTWVG's Xbase++ implementation, you
will wonder how a Xbase++ .prg ( with only those controls which have
have taken some shape ) can be compiled in Harbour. And this is the
same intention for GTWVW too. No code change whatsoever.

As before, today also i say that it is difficult to port GTWVW as is
in new GT system. As GTWVG and GTWVW share the same code base at the
core levels it is easier to embed GTWVW GUI controls into GTWVG.
End user will not experience any change to his/her code. This way
it is not as difficult as it seems to be.

Also to rewrite it again will be sheer waste of time and resources.
In fact I could never take advantage of GTWVW because it become mutually
exclusive with GTWVG, and which I do not want at this time. Possibly
in the coming few days I will lay the base structure how GTWVW
functionality can be included in GTWVG. There onwards there should be
cummulative effort to achieve this goal. I alone can achieve but it may
take a bit longer.

To conclude, GTWVW is not going to be rewritten but will be included
in GTWVG and for sure end-user will need a minimum to change his code.


Hope I am clear enough.

Regards
Pritpal Bedi