tDatabase

[Colin Haig]

Hi All

I am converting my applications to using the Fivewin database class but are unsure about indexes



lData := TRUE
oCode := tdatabase():Open(,cPath + "code",,TRUE)
if oCode:used()
oCode:SetOrder('CODE')
else
MsgAlert('Can Not Open CLIENT File')
lData := FALSE
endif
oJb := tdatabase():Open(,cPath + "jb",,TRUE)
if oJb:used()
oJb:SetOrder('CODEORD')
oJb:SetRelation(oCode,"code",FALSE)
else
MsgAlert('Can Not Open JOB File')
lData := FALSE
endif
if lData
oCode:use() // if I check the alias and indexkey - there is no index
do while ! oCode:eof()
MsgInfo(oCode:code) // does not skip in index order
oCode:skip()
enddo
endif

Am I supposed to use AddIndex or some other method or do I have to specify the order each time.

Any samples of using tDatabase - openning datafiles and indexes - also setting a relationship would be appreciated.


Cheers

Colin

[nageswaragunupudi]

Please remove "oCode:Use()" after "if lData" statement. Open method already opened the table, using Use() method.

Otherwise your code is correct.

Please take care of the following points:

TDatabase opens the table using the default RDD, which is normally DBFNTX. If you want to use DBFCDX, either you should set the default RDD to DBFCDX by RDDSetDefault( 'DBFCDX' ) or specify the driver as 3rd parameter in the call to open method. Otherwise,

If you are using DBFNTX, you should manually add the index file, by oCode:AddIndex( <indexfilename> ) and then oCode:SetOrder(...)

[Colin Haig]

Mr Rao

I am using dbfcdx.

Why does oCode not skip in the index order - when I set the order when I openned it.

oCode := tdatabase():Open(,cPath + "code",,TRUE)
if oCode:used()
oCode:SetOrder('CODE')
else
MsgAlert('Can Not Open CLIENT File')
lData := FALSE
endif


Cheers

Colin

[nageswaragunupudi]

The above code should work. Similar code works for me and all. May be the tag does not exist or misspelt.

After oCode:SetOrder( 'CODE' ), please check "MsgInfo( oCode:SetOrder() )" and see if the order is set correctly.

[TimStone]

Look at tData ... it adds some significant improvements. I've been using database objects for years and it makes coding a lot easier.

[Colin Haig]

Tim

I have been seeking assistance from James and he has been a great help with the tDatabase class - I am
re writing my application because I cant get a third party database server ( which I have been using for ages)
upgraded - everything was going fine until I tried using SetRelation which I cant get to work and seems to do something
to the index set on the child database. Also changing my twbrowses ( Hernans ) to Fivewin xbrowse and finding xbrowse
very good.

Cheers

Colin

[nageswaragunupudi]

Here is a sample code that works with TDatabase class. This code is tested. You may compile and test the code as it is. Later you may adopt this for your needs.

Code: Select all  Expand view  RUN

#include "FiveWin.Ch"
#include "ord.ch"
#include "xbrowse.ch"

REQUEST DBFCDX

static cPath := "c:\fwh\samples\"   // change this if necessary

function Main()

   local oCust, oStates
   local oDlg, oBrw

   SET DELETED ON
   SET EXCLUSIVE OFF

   RDDSetDefault( 'DBFCDX' )
   SET OPTIMIZE ON

   ReBuildIndex()  // for this test program

   oCust    := TDataBase():Open( , cPath + 'CUSTOMER' )
   if oCust:Used()
      oStates  := TDataBase():Open( , cPath + 'STATES' )
      if oStates:Used()
         oStates:SetOrder( 'CODE' )
         oStates:lBuffer := .f.     // IMPORTANT FOR CHILD

         oCust:SetRelation( oStates, 'STATE' )  // STATE is field name in CUSTOMER.DBF
         oCust:GoTop()

         DEFINE DIALOG oDlg SIZE 640,440 PIXEL
         @ 10,10 XBROWSE oBrw SIZE 300,200 PIXEL OF oDlg ;
            COLUMNS 'City','State' ;
            FIELDS oStates:Code, oStates:Name ;
            HEADERS nil,nil,'Code','StateName' ;
            OBJECT oCust ;
            CELL LINES NOBORDER

         oBrw:CreateFromCode()
         ACTIVATE DIALOG oDlg CENTERED

         oStates:Close()
      else
         MsgInfo( 'Error Opening States.dbf' )
      endif
      oCust:Close()
   else
      MsgInfo( 'Error Opening Customer.dbf' )
   endif

return ( 0 )
\
static function ReBuildIndex()

   FIELD CODE

   if File( cPath + 'STATES.CDX' )
      FErase( cPath + 'STATES.CDX' )
   endif

   USE ( cPath + 'STATES' ) NEW EXCLUSIVE
   INDEX ON CODE TAG CODE
   CLOSE DATA

return nil

Important: oChild:lBuffer := .f. // necessary

[TimStone]

Colin,

Both of those are wise moves.

I've used tData for years. I also was using tsBrowse but moved to xBrowse this year throughout the application. It works well with tData objects, but you also need to use tRecord.

I've spent many months going through my whole application and the results have been quite pleasing.

I'm not sure why you are using child databases but you might also consider loading a subset of data into an array. For example, I use a double browse on a dialog. One shows accounts, and the other shows transactions. The subset of transactions are actually loaded into an array for easier manipulation. I see no performance delay at all. Also, with ADS I was using filters which can perform well, but moved to scopes (at James suggestion ) and its faster and more stable.


Tim

[Colin Haig]

Hi Tim

What does tRecord do ?

I am adding, editing and deleting records in xbrowse with the
standard database class in Fivewin and everthing is working ok.

Cheers

Colin

[James Bott]

Colin,

The problem occurs when you edit a record in a browse with a dialog. Whenever the browse is repainted (as when the user switches to another application and back during a dialog edit) the buffer is reloaded. In order to repaint the window the entire browse is re-read from disk and redisplayed. This reloads the buffer for each visible record as it is repainted including the one being edited. Also, the data that has already been changed on the screen in the dialog remains displayed as changed, but the buffer contains the original data. I have also seen cases where the data was saved into the wrong record after a repaint, but I never figured out what the cause of this was (I did figure out a way to prevent it though--see below).

TRecord acts a second buffer and the data is not changed by a browse repaint. TRecord also has other nice features since it will automatically find its own record in the database to save the data into, or if it is a new record without a recno, then it will append a new record then save itself.

Regards,
James

[Colin Haig]

Hi James

When you say edit in a dialog does that mean you open a separate dialog with the fields of one record
make changes/add data and then close the dialog and refresh the browse.

I do all my editing in each cell of the browse and have not noticed any effects like you have
mentioned.

Cheers
Colin

[James Bott]

Colin,

>When you say edit in a dialog does that mean you open a separate dialog with the fields of one record make changes/add data and then close the dialog and refresh the browse.

Yes.

>I do all my editing in each cell of the browse and have not noticed any effects like you have
mentioned.

I believe it still can happen but only with one field (cell). Each time you leave a cell the data is saved so you would only loose the edit for the current cell.

The downside of this edit approach, is that the user cannot cancel a record edit (only a cell edit). It is a Windows standard design to use dialogs for edits and provide Ok and Cancel terminating buttons. This is what the users are used to and expecting.

Also, unless there are very few fields, you cannot see all the fields at one time as when in a dialog. It is not possible to use Tabs and/or groups to organize the data. With some browses you cannot use checkboxes, comboboxes, spinners, etc.

I very rarely edit directly in a browse and usually only when the user is entering new data with just a few fields.

Regards,
James

[TimStone]

Colin,

I actually buck the windows standard and use something from the DOS days because that is what my clients want. The browse occupies the lower part of a dialog and the fields for the highlighted record are displayed in the upper half of the dialog. As you browse through the records, the edit fields are refreshed with the data. It saves all those popups and it is exactly what my clients want.

That created difficulties because of the various xbrowse buffers. Using tData makes the process very easy, and the ON CHANGE for the browse actually loads the newly highlighted record into tRecord. The edit fields are all trecord objects, and if the save button is pressed after doing editing, the values are now always written to the correct actual database record. It works perfectly.

James is correct about the proper method for handling data ( browse edits done in a pop up window ) based on what people will expect. However, in a vertical market, with some of my clients having in excess of a 25 year history with this software ( DOS and Windows ), I found it necessary to program for their preferences. New users love it also because it makes more sense to them.

Tim

[James Bott]

One should follow Windows standards unless there is a really good reason not to.

However, I do concur with Tim. In certain vertical markets such as his (auto shops) many of his users probably only use his software and many have been using it since the DOS era. So in his case it may make sense to stick with the old interface style.

James

[nageswaragunupudi]

Colin,

The problem occurs when you edit a record in a browse with a dialog. Whenever the browse is repainted (as when the user switches to another application and back during a dialog edit) the buffer is reloaded. In order to repaint the window the entire browse is re-read from disk and redisplayed. This reloads the buffer for each visible record as it is repainted including the one being edited. Also, the data that has already been changed on the screen in the dialog remains displayed as changed, but the buffer contains the original data. I have also seen cases where the data was saved into the wrong record after a repaint, but I never figured out what the cause of this was (I did figure out a way to prevent it though--see below).

TRecord acts a second buffer and the data is not changed by a browse repaint. TRecord also has other nice features since it will automatically find its own record in the database to save the data into, or if it is a new record without a recno, then it will append a new record then save itself.

Regards,
James

This is really a very serious problem. This appears to be least known and least taken care of. It may happen rarely, but when it happens, it spoils the data and the user may not even know. Mr. James Bott recognized this long time back and provided tRecord class. Programs browsing data with TData class should always offer pop-up edits using TRecord class.

But this problem is not only with TData or TDatabase, but with all data sources, be it RDD, ADO or any kind of data source, with XBrowse or any other browse.

While using TRecord is a good solution when browsing TData, we need a generic solution for browsing any kind of data.

Fivewin does not provide TRecord like facility for its TDatabase class, but provides a more generic solution in XBrowse.

oBrw:CurrentRow()

oBrw:CurrentRow() returns a Row object which can be used for pop-up edits. This works whether the data being browsed is TData, TDatabase, RDD, ADO or any other data source. The values of remain static even when the main browse is navigated for any reasons. This object also provides methods to save and undo.

Here is a sample using the oBrw:CurrentRow() object to use in pop-up edits. (\fwh\samples\XbRowEd.Prg in FWH 10.2). The pop-up edit is provided in a non-modal dialog, so that we can test it by navigating the browse while edit is in progress.

Code: Select all  Expand view  RUN

#include 'fivewin.ch'
#include 'adodef.ch'  // in \fwh\include folder
#include 'ord.ch'
#include 'xbrowse.ch'

REQUEST DBFCDX

function Main()

   local oBrw, oDlg, uData
   local oFont

   SET EXCLUSIVE OFF
   SET DELETED ON
   SET OPTIMIZE ON

   SetGetColorFocus()

   uData    := OpenData()

   DEFINE FONT oFont NAME 'TAHOMA' SIZE 0,-12
   DEFINE DIALOG oDlg SIZE 640,460 PIXEL TITLE 'XBrowse Row Edit' ;
      FONT oFont

   @ 10,10 XBROWSE oBrw SIZE -10,-30 PIXEL OF oDlg ;
      COLUMNS 'First', 'City', 'Age', 'Salary' ;
      PICTURES nil, nil, '999', '99,999,999.99' ;
      OBJECT uData ;
      AUTOSORT CELL LINES NOBORDER ;
      ON DBLCLICK RowEdit( oBrw:CurrentRow() )

   WITH OBJECT oBrw
      :nStretchCol  := STRETCHCOL_WIDEST
      :CreateFromCode()
   END

   @ 210, 10 BUTTON 'Edit'  SIZE 40,14 PIXEL OF oDlg ACTION RowEdit( oBrw:CurrentRow() )
   @ 210,270 BUTTON 'Close' SIZE 40,14 PIXEL OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

   CloseData( uData )

return nil

static function RowEdit( oRow )

   LOCAL oDlg

   DEFINE DIALOG oDlg SIZE 300,200 PIXEL TITLE 'Edit Customer' ;
      FONT oRow:oBrw:oFont

   @  10, 10 SAY 'First' SIZE 40,10 PIXEL OF oDlg RIGHT
   @  10, 60 GET oRow:First SIZE 80,12 PIXEL OF oDlg UPDATE ;
            VALID ( oDlg:AEvalWhen(), .t. )

   @  25, 10 SAY 'City' SIZE 40,10 PIXEL OF oDlg RIGHT
   @  25, 60 GET oRow:City SIZE 80,12 PIXEL OF oDlg UPDATE ;
            VALID ( oDlg:AEvalWhen(), .t. )

   @  40, 10 SAY 'Age' SIZE 40,10 PIXEL OF oDlg RIGHT
   @  40, 60 GET oRow:Age ;
      SIZE oRow:oBrw:Age:nWidth / 2,12 PIXEL OF oDlg UPDATE ;
      PICTURE oRow:oBrw:Age:cEditPicture RIGHT ;
      VALID ( oDlg:AEvalWhen(), .t. )

   @  55, 10 SAY 'Salary' SIZE 40,10 PIXEL OF oDlg RIGHT
   @  55, 60 GET oRow:Salary ;
      SIZE oRow:oBrw:Salary:nWidth / 2,12 PIXEL OF oDlg UPDATE ;
      PICTURE oRow:oBrw:Salary:cEditPicture RIGHT ;
      VALID ( oDlg:AEvalWhen(), .t. )

   @  80, 10 BUTTON 'Undo'  SIZE 30,12 PIXEL OF oDlg ;
      WHEN oRow:Modified() ACTION ( oRow:Undo(), oDlg:Update() )

   @  80, 78 BUTTON 'Save'  SIZE 30,12 PIXEL OF oDlg ;
      WHEN oRow:Modified() ACTION ( oRow:Save(), oDlg:aEvalWhen() )

   @  80,110 BUTTON 'Close' SIZE 30,12 PIXEL OF oDlg ACTION oDlg:End()

   ACTIVATE DIALOG oDlg CENTERED NOMODAL

return nil

// ----  DATA OPEN AND CLOSE FUNCTIONS ---- //

static function OpenData

   local uData, nDataType

   nDataType   := Max( 1, Alert( 'DataType', { 'DBFCDX', 'TDataBase', 'ADO' } ) )

   if nDataType > 2
      uData := OpenADO()
   else
      USE CUSTOMER NEW ALIAS CUST SHARED VIA 'DBFCDX'
      DATABASE uData

      if nDataType == 1
         uData := 'CUST'
      endif

   endif

return uData

static function OpenADO

   local oCn, oRs, cPath := CURDRIVE() + ':' + Chr(92) + CURDIR()

   oCn      := TOleAuto():New( 'ADODB.Connection' )
   WITH OBJECT oCn
      :ConnectionString := 'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=' + cPath
      :CursorLocation   := adUseClient
      :Open()
   END

   oRs      := TOleAuto():New( 'ADODB.RecordSet' )
   WITH OBJECT oRs
      :ActiveConnection := oCn
      :Source           := 'CUSTOMER'
      :LockType         := adLockOptimistic
      :Open()
   END

return oRs

static function CloseData( uData )

   local oCn

   if ValType( uData ) == 'O'
      if Upper( uData:ClassName ) = 'TOLEAUTO'
         oCn   := uData:ActiveConnection
         uData:Close()
         oCn:Close()
      else
         uData:Close()
      endif
   else
      CLOSE DATA
   endif

return nil
 

Independent of the edit in the dialog, the browse can be navigated but the data will be saved in the correct record when the saved.

In this example the user can choose either RDD, TDatabase or ADO. In all cases the program works identically.

This example also demonstrates how both xbrowse and CurrentRow() objects can be coded without the knowledge of where the data is read from or written to. XBrowse automatically recognizes the data source and builds codeblocks to handle the data appropriately including locking and unlocking. This way of coding can be useful to write generic interface which can be plugged to different kinds of data sources.

The default save method works well normally in many cases. But we can also customize the save behavior by assigning our own codeblock to oRow:bSave. We can see other data and methods in the static class TXbrRow in the xbrowse.prg.

In quite a few cases, we offer only some selected columns in the browse but we need to edit more columns in the edit dialog. In such cases, we can add all the columns to be edited to xbrowse and hide them with oCol:lHide := .t.. Row object will contain all columns to be edited, though the browse shows only the columns that are not hidden.

Though this method was available in XBrowse for quite long, it had a few minor bugs prior to version 10.2 and also this feature and its benefits were little known. With the following 3 bug fixes, this features works perfectly in all previous versions also.

In METHOD Save() CLASS TXBrRow:
1. Replace the old code

Code: Select all  Expand view  RUN

  Eval( ::oBrw:bBookMark )

with this correct code

Code: Select all  Expand view  RUN

  Eval( ::oBrw:bBookMark, ::nRecNo )

2. Replace the old code

Code: Select all  Expand view  RUN

           oCol     := ::oBrw:oCol( ::Headers[ n ] )

with this correct code

Code: Select all  Expand view  RUN

           oCol     := ::oBrw:oCol( ::aHeaders[ n ] )

3. and Insert one line as indicated

Code: Select all  Expand view  RUN

     lSaved   := .t.
      ACopy( ::aValues, ::aOriginals )  // Insert this line. This line was not in previous versions
   endif