TDatabase Class

[Rick Lipkin]

To All

Where can I find the tDatabase Class ?? ..

Thanks
Rick Lipkin

[cnavarro]

Dear Rick
\source\classes\database.prg

[Rick Lipkin]

Cristobal

Any documentation and examples ...

1) I want to create a Database as oDbf and pass oDbf much like we do with Ado .. oRs
2) How do I set a filter ? .. are the oDbf:Filters as fast as with ADO ? oDbf:Filter := "[CustomerName] like '"+cLname1+"%'" ?
3) How to I extract a oDBf field Value ? oDBf:Fields("Whatever"):Value ??
4) How to I create and use index's .. Like .cdx ( tags )
5) Reference oDbf in xBrowse() ?

I know I can probably look for examples in the forum .. The five things I need to get started are the questions above.

Thanks
Rick

[James Bott]

Rick,

Go here and read my Introduction To OOP articles.

http://gointellitech.com/program.html

1) I want to create a Database as oDbf and pass oDbf much like we do with Ado .. oRs

Simple: oCustomers:= TCustomers():new()

Requires that you build a TCustomers class first. You can pass the object. You can also have objects within objects.

Note that above I am using "Customers" (plural). This is a table of customers (database). You can also create a oCustomer object for a single customer, then you can edit and save it, print it, pass it to another object or function. Etc.

2) How do I set a filter ? .. are the oDbf:Filters as fast as with ADO ? oDbf:Filter := "[CustomerName] like '"+cLname1+"%'" ?

Filters are very slow with large databases. I mostly use scopes.

3) How to I extract a oDBf field Value ? oDBf:Fields("Whatever"):Value ??

msgInfo(oCustomers:Company) // from a database object for the current record

msgInfo(oCustomer:Company) // from a single customer object

4) How to I create and use index's .. Like .cdx ( tags )

The same way you always do.

5) Reference oDbf in xBrowse() ?

I am not sure what you mean.

Another point I would like to make is that you move functions (that are typically scattered all over) into the object that they are relevant to. Examples:

? oCustomer:DaysOverDue()
oCustomer:SendLateNotice()

It takes awhile to grasp OOP, but it is a hundred times more powerful and simpler to code once you get the hang of it. I have taken old code and reduced the number of lines by more than 50% using OOP. I could go on and on and on...

Oh, and you never have to deal with workareas again!

Tim Stone is using OOP with ADO so he may jump in with some relevant comments.

[Rick Lipkin]

James

I have been able to use the sample dbf01.prg ... I have created my table traditionally and my index traditionally .. this so far works

Code: Select all  Expand view  RUN

DO WHILE .T.
   CUSTDBF := "TEMP"+(SUBSTR(TIME(),7,2)+SUBSTR(TIME(),4,2))//+".DBF"
   IF .not. FILE( xVol+"\DBTMP\"+CustDbf+".DBF" )
      EXIT
   ENDIF
ENDDO

DBF_STRU := { }
AADD( DBF_STRU, { "CustID",      "C", 15, 0 } )
AADD( DBF_STRU, { "SceisId",     "C", 21, 0 } )
AADD( DBF_STRU, { "CustName",    "C", 50, 0 } )
AADD( DBF_STRU, { "CustAdd1",    "C", 50, 0 } )
AADD( DBF_STRU, { "CustCity",    "C", 35, 0 } )
AADD( DBF_STRU, { "CustState",   "C",  2, 0 } )
AADD( DBF_STRU, { "CustZip",     "C", 30, 0 } )
AADD( DBF_STRU, { "CustPh",      "C", 15, 0 } )
AADD( DBF_STRU, { "CustCont",    "C", 36, 0 } )
AADD( DBF_STRU, { "CustMail",    "C", 50, 0 } )

Try
   DBCREATE( xVol+"\DBTMP\"+CUSTDBF+".DBF", DBF_STRU )
Catch
  Saying := "Sorry .. Error Creating"+chr(10)
  Saying += xVol+"\DBTMP\"+CUSTDBF+".DBF"+chr(10)
  Saying += "Please seek your Administrator"+chr(10)
  MsgInfo( Saying )
  CLOSE DATABASES
  FERASE( xVol+"\DBTMP\"+CustDbf+".DBF")
  FERASE( xVol+"\DBTMP\"+CUSTDBF+".CDX")
  oDlg:END()
  RETURN(NIL)
End Try

SELECT 9
IF NETUSE( xVol+"\DBTMP\"+CustDbf+".DBF", .T.,5)
   Index on CustName TAG CustName
   Set Order to Tag CustName
ELSE
  CLOSE DATABASES
  FERASE( xVol+"\DBTMP\"+CustDbf+".DBF")
  FERASE( xVol+"\DBTMP\"+CUSTDBF+".CDX")
  oDlg:END()
  RETURN(NIL)
ENDIF

Select 9
DATABASE oDbf

xBrowse( oDbf )  // this works and I can pass oDbf to various other functions ..  my next  step is to simulate an incremental search ..


//---------------------------------------- incramental code

 REDEFINE GET oName  var cName  ID 130 of oGrps ;
         When nRad = 1 ;
         ON CHANGE ( _Isearch( oName, cName, oCid, cCid, oSid, cSid, oLbx, oDbf )) UPDATE    // , oRsCust )) UPDATE
   REDEFINE GET oCid   var cCid   ID 131 of oGrps ;
         When nRad = 1 ;
         ON CHANGE ( _Isearch( oName, cName, oCid, cCid, oSid, cSid, oLbx, oDBf )) UPDATE    //, oRsCust )) UPDATE
   REDEFINE GET oSId   var cSId   ID 118 of oGrps ;
         When nRad = 1 ;
         ON CHANGE ( _Isearch( oName, cName, oCid, cCid, oSId, cSId, oLbx, oDBf )) UPDATE    //, oRsCust )) UPDATE

   REDEFINE GET oInv var cInv     ID 140 of oGrps ;
         When nRad = 2 ;
         ON CHANGE ( _Isearch1( oInv,cInv,oLbxB,oRsInv,oRsCust,nRad,oLbx,oDbf )) UPDATE


//-------------------------------------------------------------------------------------------
Static Func  _Isearch( oLname1, cLname1, oCid1, cCid1, oSceis1, cSceis1, oLbx, oDbf ) //, oRsCust )

cLname1  := alltrim( oLName1:GetText() )
cCid1    := alltrim( oCid1:GetText() )
cSceis1  := alltrim( oSceis1:GetText() )

msginfo( cLname1 )


xbrowse(oDbf)

Do Case
Case (Empty( cLName1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1) )
      Set Scope To ( 'bogus' )  // resets browse

     * oRsCust:Filter := "[CustomerName] = 'bogus'"

Case !empty( cLname1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1 )
      Set Scope to ( cLname1 )  // attempting this ???????????????????????????????????????????????    line 709

   * oRsCust:Filter := "[CustomerName] like '"+cLname1+"%'"


// ------  old ado code
Case (!empty( cLname1 ) .and. !Empty( cCid1 ) .and. Empty( cSceis1 ))
     oRsCust:Filter := "[CustomerName] like '"+cLname1+"%' and [CustomerId] like '"+cCid1+"%'"

Case empty( cLname1 ) .and. !Empty( cCid1 ) .and. Empty( cSceis1 )
     oRsCust:Filter := "[CustomerId] like '"+cCid1+"%'"

Case ( Empty( cLName1 ) .and. Empty( cCid1 ) .and. !Empty( cSceis1) )
     oRsCust:Filter := "[SceisId] like '"+cSceis1+"%'"

OtherWise
     oRsCust:Filter := "[CustomerName] = 'bogus'"
End Case

oLbx:ReFresh()

Return(.t.)

James .. see the line above with all the question marks .. as the user types the value gets larger .. like an incremental search .. Appreciate your help!

Thanks
Rick Lipkin

Follow up ... trying the code above .. I get this error

Application
===========
Path and name: C:\Fox\DOI\DoiBillEnc\Billw32.Exe (32 bits)
Size: 6,264,320 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20201015)
FiveWin version: FWH 20.06
C compiler version: Borland/Embarcadero C++ 7.4 (32-bit)
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 31 secs
Error occurred at: 05/19/2021, 11:40:03
Error description: Error DBCMD/2001 Workarea not in use: ORDSCOPE

Stack Calls
===========
Called from: => ORDSCOPE( 0 )
Called from: CUSTVIEW.PRG => _ISEARCH( 709 )
Called from: CUSTVIEW.PRG => (b)_CUSTVIEW( 307 )
Called from: .\source\classes\TGET.PRG => TGET:KEYCHAR( 2215 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1827 )
Called from: .\source\classes\TGET.PRG => TGET:HANDLEEVENT( 1221 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3559 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE( 1097 )
Called from: MAIN.PRG => MAIN( 370 )

[James Bott]

Rick,

Well, I have no experience with ADO, but I thought it was language compatible with FWin. Your code makes me think it is not.

After these lines in your code:

Select 9
DATABASE oDbf

I am not sure what oDBF is in the above case. I don't know if it is being made by Fivewin or ADO? If it is made by ADO is there some documentation?

Can you try:

msgInfo(oDbf:fieldname ) // where fieldname is an actual fieldname.

Does that work?

Also try:

MsgInfo(oDBF:nArea)

Note that TDatabase creates it's own unique workareas which is not going to be 9 in this case. With database objects you never deal with workareas. Your error message:

Error description: Error DBCMD/2001 Workarea not in use: ORDSCOPE

leads me to believe that the workarea is the issue.

[Rick Lipkin]

James

SELECT 9
IF NETUSE( xVol+"\DBTMP\"+CustDbf+".DBF", .T.,5)
Index on CustName TAG CustName
Set Order to Tag CustName
ELSE
CLOSE DATABASES
FERASE( xVol+"\DBTMP\"+CustDbf+".DBF")
FERASE( xVol+"\DBTMP\"+CUSTDBF+".CDX")
oDlg:END()
RETURN(NIL)
ENDIF

Select 9
DATABASE oDbf

James ... I saw this code on opening a database .. I figured the statement DATABASE oDbf created the database object .. which it appears to do .. I pass oDBf to multiple modules and the table and data is there ..

Where I am getting hung up is to create a incremental search of oDbf ..

With ADO and SQl there is a LIKE statement .. so this statement works as you start typing a field value

cLname1 := alltrim( oLName1:GetText() ) // this starts building a value one charactor at a time.
oRsCust:Filter := "[CustomerName] like '"+cLname1+"%'"

This is what I want to achieve with tDatabase .. since there is no LIKE command I was hoping to use OrdScope to build my incremental list .. don't think orderscope works either

Need help ... this is a very big contract and I am ( due to the Colonial Pipeline ransom ) encrypting all the user data .. and moving the application from MS Access to Ms Sql Server .. Access and Sql server are both coded the same .. however .. the CUstomer Table is totally encrypted to I have to create a temp dbf table with the de-encrypted data and the ADO and tDatabase classes are not compatable so far as I can see...

I need a way to use a unencryptd .dbf database and be able to traverse a company's name just like I did using ADO as you see above .. surely there is a way I can start typing in a field and someone come up with an incremental search on oDbf one key at a time ...

I can use anyone's help !!

Thanks
Rick Lipkin

[Antonio Linares]

Rick,

> and moving the application from MS Access to Ms Sql Server

We already did this for a very large US customer and using standard FWH, and it was a great success.

Please wait for Mr. Rao instructions

[James Bott]

Rick,

Code: Select all  Expand view  RUN

SELECT 9
IF NETUSE( xVol+"\DBTMP\"+CustDbf+".DBF", .T.,5)
Index on CustName TAG CustName
Set Order to Tag CustName
ELSE
CLOSE DATABASES
FERASE( xVol+"\DBTMP\"+CustDbf+".DBF")
FERASE( xVol+"\DBTMP\"+CUSTDBF+".CDX")
oDlg:END()
RETURN(NIL)
ENDIF

Select 9
DATABASE oDbf

OK, the last two lines look like standard Fivewn database code. This will give you a database but nothing more. I would like to build a database object that you can instsantiate in one line like this:

oCustomers:=TCustomers():new()

This way you can add methods (which are now functions) to the database object. Possibly you could also decrypt and encrypt the data with the Load() and Save() methods.

So try this:

oCustomers:= TDatabase(, xVol+"\DBTMP\"+CustDbf.DBF")
oCustomers:use()
msgInfo(oCustomers:XXX) // where XXX is a valid fieldname

Let me know what you find.

I am not worried about the incremental search--I'm sure we can figure that out. I may even have an example in my archives.

[Rick Lipkin]

James and Antonio

The problem I am running into is duplicating the same functionality of using an incremental search with ADO

//----------------------------------------------------
Static Func _Isearch( oLname1, cLname1, oCid1, cCid1, oSceis1, cSceis1, oLbx, oDbf ) //, oRsCust )

cLname1 := alltrim( oLName1:GetText() )
cCid1 := alltrim( oCid1:GetText() )
cSceis1 := alltrim( oSceis1:GetText() )

msginfo( cLname1 )

Do Case
Case (Empty( cLName1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1) )
oRsCust:Filter := "[CustomerName] = 'bogus'"

Case !empty( cLname1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1 )

* oDbf->OrdSetFocus( cLname1 )

* Set Scope to ( cLname1 )
* return ( ::cAlias )->( OrdScope( nScopeType, uValue ) )
* oRsCust:Filter := "[CustomerName] like '"+cLname1+"%'" // this works GREAT using MS Access and Sql Server ..

What I have done is de-encrypted the entire Sql Customer table to a Temp database ( oDbf or Select 9 ) .. I need the same incremental functionality to incrementally search oDBf

Thanks
Rick Lipkin

[James Bott]

Rick,

oLname1

Are you using field objects?

And I would really like to know if this works for you:

Code: Select all  Expand view  RUN

oCustomers:= TDatabase():New(, xVol+"\DBTMP\"+CustDbf.DBF")
oCustomers:use()
msgInfo(oCustomers:XXX) // where XXX is a valid fieldname

These answers will really help me answer your question about incremental seaching. I am certain we can solve that but I need to understand your syntax to be able to do so.

Code: Select all  Expand view  RUN

Static Func _Isearch( oLname1, cLname1, oCid1, cCid1, oSceis1, cSceis1, oLbx, oDbf )

Also, are you passing both the field object and the data, then calculating the data also? If so, why?

And why do you put the number "1" at the end of every fieldname?

James

[Rick Lipkin]

James ... here is the current application



oLname 1 is the object on the left side or the Client name entry field .. iSearch

Code: Select all  Expand view  RUN

   

REDEFINE GET oName  var cName  ID 130 of oGrps ;
         When nRad = 1 ;
         ON CHANGE ( _Isearch( oName, cName, oCid, cCid, oSid, cSid, oLbx, oRsCust )) UPDATE
   REDEFINE GET oCid   var cCid   ID 131 of oGrps ;
         When nRad = 1 ;
         ON CHANGE ( _Isearch( oName, cName, oCid, cCid, oSid, cSid, oLbx, oRsCust )) UPDATE
   REDEFINE GET oSId   var cSId   ID 118 of oGrps ;
         When nRad = 1 ;
         ON CHANGE ( _Isearch( oName, cName, oCid, cCid, oSId, cSId, oLbx, oRsCust )) UPDATE

   REDEFINE GET oInv var cInv     ID 140 of oGrps ;
         When nRad = 2 ;
         ON CHANGE ( _Isearch1( oInv,cInv,oLbxB,oRsInv,oRsCust,nRad,oLbx )) UPDATE

...
...
...

//-------------
Static Func  _Isearch( oLname1, cLname1, oCid1, cCid1, oSceis1, cSceis1, oLbx, oRsCust )

cLname1  := alltrim( oLName1:GetText() )
cCid1    := alltrim( oCid1:GetText() )
cSceis1  := alltrim( oSceis1:GetText() )

Do Case
Case (Empty( cLName1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1) )
     oRsCust:Filter := "[CustomerName] = 'bogus'"

Case !empty( cLname1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1 )   // here
     oRsCust:Filter := "[CustomerName] like '"+cLname1+"%'"

Case (!empty( cLname1 ) .and. !Empty( cCid1 ) .and. Empty( cSceis1 ))
     oRsCust:Filter := "[CustomerName] like '"+cLname1+"%' and [CustomerId] like '"+cCid1+"%'"

Case empty( cLname1 ) .and. !Empty( cCid1 ) .and. Empty( cSceis1 )
     oRsCust:Filter := "[CustomerId] like '"+cCid1+"%'"

Case ( Empty( cLName1 ) .and. Empty( cCid1 ) .and. !Empty( cSceis1) )
     oRsCust:Filter := "[SceisId] like '"+cSceis1+"%'"

OtherWise
     oRsCust:Filter := "[CustomerName] = 'bogus'"
End Case

oLbx:ReFresh()

Return(.t.)
 

The trick is evaluating

cLname1 := alltrim( oLName1:GetText() )

As you type in the Name field .. the On Change clause calls iSearch() .. and the GetText() traps the key strokes ... then based on Case 2

oRsCust:Filter := "[CustomerName] like '"+cLname1+"%'"

The above filter condition simulates and incremental search and the key to that search is the Sql like '"+cLname1+"%'" statement.

Hope that makes sense ... I want to do something to simulate incremental search with TDatabase since it is object based ... and there is no 'like' or 'filter' in traditional DbfCdx .. but there is

(::cAlias )->(OrdsetFocus("Fetcha")) .. the use of OrdScope ?? in tDatabase ..

Appreciate your help!!

Thanks
Rick Lipkin

[James Bott]

Rick,

Yes I get that you want to do an incremental search, however you still haven't answered a single question that I have asked. And without any answers there isn't anything more I can do.

[James Bott]

Code: Select all  Expand view  RUN

(::cAlias )->(OrdsetFocus("Fetcha")) .. the use of OrdScope ?? in tDatabase ..

Yes, there is the method OrdScope(uTop, uBottom) in TDatabase.

Use the same value for both uTop and uBottom to limit the filter to a single value. You will need to index on upper(cValue) for the index and use alltrim(upper(cValue)) for both the top and bottom scopes.

You could also subclass TDatabase to create a new method OrdSetFocus() that works like you want but using OrdScope() to accomplish this.

Note that you don't need to use any aliases or workareas with database objects. That is all handled automatically by the object.

[James Bott]

Code: Select all  Expand view  RUN

Case !empty( cLname1 ) .and. Empty( cCid1 ) .and. Empty( cSceis1 )
      Set Scope to ( cLname1 )  // attempting this ???????????????????????????????????????????????    line 709
 

You would do this with:

oCustomers:setScope( alltrim(upper(cLname1)), alltrim(upper(cLname1 )) )