1) I want to create a Database as oDbf and pass oDbf much like we do with Ado .. oRs
Sample code:
- Code: Select all Expand view RUN
function CreateTable()
local aStruct := { { "ID", "+", 4, 0 }, { "CODE", "C", 2, 0 }, { "NAME", "C", 25, 0 } }
local aData := { { "WA", "Washington" }, { "NY", "New York" }, { "NE", "Nebraska" } }
local oDbf
oDbf := TDatabase():Create( "NAMES.DBF", aStruct, "DBFCDX", "*" ) // "*" means index all fields
oDbf:ArrayToDBF( aData, "CODE,NAME" ) // Add initial data
oDbf:GoTop()
? oDbf:ID, oDbf:Code, oDbf:Name
XBROWSER oDbf TITLE "NAMES.DBF : CREATED"
oDbf:Close()
return nil
Opening DBF with TDatabase
The following code works. Kept for backward compatibility. We do not recommend this approach
- Code: Select all Expand view RUN
USE NAMES
// or SELECT nArea
DATABASE oDbf
// works but not recommended
Recommended:
- Code: Select all Expand view RUN
function OpenTable()
local oDbf, lShared := .t.
oDbf := TDatabase():Open( nil, "NAMES", "DBFCDX", lShared )
? oDbf:ID, oDbf:Code, oDbf:Name
XBROWSER oDbf TITLE "NAMES.DBF : OPENED" AUTOSORT
oDbf:Close()
return nil
When we want to create a new table and use:
Recommended:
- Code: Select all Expand view RUN
oDbf := TDatabase():Create( ... ) // as in above sample
oDbf:Close()
oDbf := TDatabase():Open(...) // as in above sample
2) How do I set a filter ? .. are the oDbf:Filters as fast as with ADO ? oDbf:Filter := "[CustomerName] like '"+cLname1+"%'" ?
Please use DBF syntax for filter expressions, not SQL syntax.
Eg:
"NAME LIKE 'X%'" does not work
"NAME = 'X'" works
ADO:
oRs:Filter := "[CustomerName] like '"+cLname1+"%'"
TDatabase:
oDbf:SetFilter( "NAME = '" + cLName1 + "'")
oDbf:GoTop()
OR
oDbf:SetFilter( "NAME = ?", { cLName1 } )
oDbf:GoTop()
TDataBase Filter methods:
- Code: Select all Expand view RUN
oDbf:SetFilter() --> present dbfilter
oDbf:SetFilter( "" ) // Clear filter
oDbf:clear filter // Clear filter
oDbf:SetFilter( cFilter ) // new filter
oDbf:SetFilter( cFilter, aParams ) // parameters for ? place holders
oDbf:setFilter( cFilter, bParams ) // Eval( bParams ) -> aParams
// In case of parametrised filters we can later use
oDbf:ReFilter( aNewParams )
Setting Scopes:
- Code: Select all Expand view RUN
oDbf:OrdScope( 0, uTopValue )
oDbf:OrdScope( 1, uBottomValue )
oDbf:GoTop()
if the value is NIL, clears the scope.
3) How to I extract a oDBf field Value ? oDBf:Fields("Whatever"):Value ??
Accessing:
- Code: Select all Expand view RUN
? oDbf:<fieldName1>, oDbf:<fieldName2>, ...
Assign new values and saving:
- Code: Select all Expand view RUN
oDbf:<fieldName1> := uNewValue1
oDbf:<fieldName2> := uNewValue2
oDbf:Save() // similar to oRs:Update()
Modifying data and saving changes:
1)
- Code: Select all Expand view RUN
oDbf:Code := "WA"
oDbf:Name := "Washington"
oDbf:Save()
2)
- Code: Select all Expand view RUN
oDbf:Update( { "CODE", "NAME" }, { "WA", "Washington" } )
//or
oDbf:Update( "CODE,NAME", { "WA", "Washington" } )
Appending New Data:
1)
- Code: Select all Expand view RUN
oDbf:Append() // same as oRs:AddNew()
oDbf:<fieldName1> := uNewValue1
oDbf:<fieldName2> := uNewValue2
oDbf:Save() // similar to oRs:Update()
2)
- Code: Select all Expand view RUN
oDbf:Append( { "CODE", "NAME" }, { "NW", "Newark" } ) // like ADO AddNew( flds, values )
4) How to I create and use index's .. Like .cdx ( tags )
Approach-1:
oDbf:CreateIndex( [cFile], cTag, cKey, [lUnique], [lDescend], [lMemory] )
Approach-2 (Easiest):
oDbf:CdxCreate() /// creates index on all fields
oDbf:CdxCreaate( cFieldList, [lMemory] )
Eg:
oDbf:CdxCreate( "ID,CODE,NAME" )
Character field expressions are automatically enclosed in UPPER()
Setting order to index tag:
- Code: Select all Expand view RUN
oDbf:SetOrder( cnIndexOrder, [cFile] )
5) Reference oDbf in xBrowse() ?
XBROWSER oDbf [COLUMNS "ID", "CODE", "NAME" ] ....
@ row,col XBROWSE oBrw SIZE w,h PIXEL OF oDlg ;
DATASOURCE oDbf ;
COLUMNS "ID", "CODE", "NAME" ;
..........
For quick editable browse:
- Code: Select all Expand view RUN
oDbf:Browse( [cTitle], [acFields] )
For Quick Edit Dialog
- Code: Select all Expand view RUN
oDbf:Edit( [cFieldList], [lNew] )
