Introducing Advantage Database Server in a CA Visual Objects DBFCDX environment
Short introduction of the author
Dick van Kooten graduated in economics and computer science on the Erasmus University of Rotterdam and soon founded IC2 Innovative Computer Consultancy. Software and website development are the main activities, all relying heavily on programs written in CA Visual Objects and DBF based databases. Beside the CRM/ERP software package iConnect (www.iconnect.nu) , large tailor made projects have been realized in IC2's home country Holland up until countries like USA, Monaco and Singapore. More information can be found on www.ic2.com.
Why go to ADS?
As a programmer in CA Visual Objects, we still use the relatively simple database system DBFCDX. This database format started with dBASE way back in 1979. The format and especially the way memo fields and indexes were stored and created was improved and the improved format was the standards for development environments like Cavo and Microsoft Foxpro. Although even today dBASE is still on the market (as an integrated development environment) Foxpro, Cavo and Vulcan users are looking for more elaborate database management systems.
When working on a server, the client's PC (workstation) will basically retrieve all data the program needs or even just inspects, process changes on the workstation and send back any changes to the server to write back. This can take a lot of time when many records have to be inspected (e.g. reports, large replace, filter, browser or search actions) and users may experience problems with data (corruption in data or indexes, overwrites) from time to time which may be a result from this kind of data storage. Going to a client server database management system could cope with these kind of problems. Data and data processing processes will remain on the server and only the “end result” will be transported to the workstation (and, with additions or changes, back to be processed on the server again) . This will result in increased stability and speed, especially when large amounts of data have to be inspected with only a few results to be displayed on the workstation.
SQL servers are widely used. SQL is even more ancient than dBASE, it is from 1970. It is basically a (Structured) Query Language (hence the abbreviation) but it’s the key language to access data stored on a server, like Microsoft SQL Server. In other words, is has evolved to a widely used client server system. Coming from DBFCDX, required code changes in data processing are quite big. E.g. a loop like
Do WHILE oContact:NAME=”van Kooten” - ++nCount Skip() ENDDO
will not run efficiently in SQL as the whole loop needs to be replaced by one SELECT FROM statement.
Sybase (a SAP company) has a product on the market called ADS (Advantage Database Server) which would make transition to a client server system easier as most code can be left untouched. They provide a free local server which of course does not give the true client server benefits but provides compatibility and offer a per client one time fee costing from about 175 euro (with 2 users) until less than 100 euro for 50 users and just over 10.000 euro for unlimited users (on one server).
In the below article I will summarize the changes we had to make in our Cavo code to make it work with ADS. I hope that this will provide useful reading material for other xBase based languages as well.
Server installation
After installing ADS make sure that the configuration utility has the following settings if you want to stay compatible with non ADS accesses to your data
Non-Exclusive proprietary locking should be checked
Ansi character set should be set according to your Setcollation setting in your program
A file called ADS.INI should be present in the work directory or the application. Settings in these file can be:
ADS_SERVER_TYPE = 2 // 2 means remote server, 1 local server, 4 server on an internet location. Add the values to let ADS inspect more locations, so 7 means check all.
USE_TCP_IP=1 would mean that TCP/IP is used. This will slow down normal server operation.
You could even force ADS to locate a specific PC (make sure to specify the LAN port equal to the setting in the configuration utility) and a disk:
[IC2MAINPC]
LAN_IP=192.168.0.101
LAN_PORT=6262
[DRIVES]
K:=\\IC2MAINPC\D
For VO, the following files have to be present in your work directory:
Adslocal.cfg
ansi.chr
extend.chr
ace32.dll
adsloc32.dll
axcws32.dll
ads.ini|
AXDBFCDX.RDD
Opening databases
To enable a database to be opened in DBFCDX applications as well, make sure your program starts with:
AX_AXSLocking (.F.)
This requires the VO library DBFAXS, which is part of the delivery, to be included.
Basically the only difference to have the system running on ADS is opening your databases with another RDD: change sDriver from DBFCDX to AXDBFCDX
SUPER:INIT(oDbf_FS, lShare,lReadOnly, sDriver,lDirect )
Or you set:
RddSetDefault("AXDBFCDX")
SetAnsi(FALSE) (setting databases to ANSI is apparently handled internally in ADS)
ADS has a proprietary format databases to be used instead of DBF files but this is not required except if you’re looking for some extra functionality like re-use of deleted records or extra field types as auto-incremental fields.
The following code performs some settings and checks if all works. If so it returns true, otherwise error messages can be set.
DBFAXSAdsSetServerType(DBFAXS_ADS_REMOTE_SERVER)
ulRetVal:=DWORD(_CAST,DBFAXSAdsIsServerLoaded(String2Psz(WorkDir()),@usLoaded))
DO CASE
CASE ulRetVal<>0 // a problem
CASE usLoaded==0 // No server found
OTHERWISE // There is a connection
SELF:_ADSType:=usLoaded
END
RETURN SELF:_ADSType<>0
Release of a copied CDX
In one situation we copied a database structure and created an index to fill the copy database with some changed records, to be sent to an internet site:
RDDINFO(_SET_MEMOBLOCKSIZE,64) // FTP should be 64 bits 1-2-2011
IF !SELF:Server:CopyStructure(cDataPath+"webtxtexp.dbf")
etc
Then we copied the dbf, fpt and cdx but found that cdx gave a run time error. It appears that when the server is installed on Server 2008 or Windows 7 and the client is Vista or higher, there’s a Microsoft confirmed bug causing ADS to free the freshly created index with a delay. There are 2 ways to handle that:
Apply an error handler which forces the wait:
BEGIN SEQUENCE
cbOldErrorBlock:=ErrorBlock()
ErrorBlock({|X|WebTxtExpForceWait()})
FCopy(cDataPath+"webtxtexp.dbf",cTempPath+"webtxtexp.dbf")
FCopy(cDataPath+"webtxtexp.fpt",cTempPath+"webtxtexp.fpt")
lCdx:=TRUE
IF File(cTempPath+"webtxtexp.cdx")
IF !FErase(cTempPath+"webtxtexp.cdx")
lCdx:=FALSE
ENDIF
ENDIF
IF !lCdx
ErrorBox{,Vt(MSG_FILESNOTDELETED,"Bestand(en) kunnen niet worden gewist"+" webtxtexp.cdx")}:Show()
aFilestoSend:={"webtxtexp.dbf","webtxtexp.fpt"}
ELSE
FCopy(cDataPath+"webtxtexp.cdx",cTempPath+"webtxtexp.cdx")
aFilestoSend:={"webtxtexp.dbf","webtxtexp.fpt","webtxtexp.cdx"}
ENDIF
RECOVER
ErrorBlock(cbOldErrorBlock)
FUNCTION WebTxtExpForceWait
//#s Called from errorhandler in METHOD FillChangedWebtxts(cTempPath AS STRING) AS ARRAY CLASS WebTxtVenster 7-2-2011
//#s when copy webtxtexp cdx fails in ADS, wait a while
WaitTime(3)
RETURN TRUE
or apply the following registry change on the client:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanWorkstation\Parameters]
"FileInfoCacheLifetime"=dword:00000000
"FileNotFoundCacheLifetime"=dword:00000000
"DirectoryCacheLifetime"=dword:00000000
(The .reg file is included in the zip file mentioned at the end of this article)
Wildseek
Written by Lutz Eckert, the wildseek program allows random search on any field in a database. This is done by opening the dbf as regular file and inspecting blocks of data. Apart from the Non-Exclusive proprietary setting, which should be checked, the program requires an adaption as apparently the RDD does not always give the correct start value of the data, self:header can both give the correct value or one byte higher. We have added some code to read the start of the header directly from the DBF and only should this fail (not to be expected...) try to rely on self:header:
liFilePos := FSeek3( ptrFile, 8, FS_SET )
// Get header from byte 8+9 as self:Header is one less in ADS but I have seen that it may NOT be 1 less from an exe so it varies pBuffer:=MemAlloc(2) // allocate 2 bytes
nData:=FRead3(ptrFile, pBuffer, 2) // Read the bytes in pBuffer
cData:=Mem2String(pBuffer,nData) // Convert buffer content to a string
IF nData<> 2 // Not read, try the 'old' way
nHeader:=SELF:Header
ELSE
nHeader:=LONG(_CAST,(256*Asc(Right(cData,1))))+LONG(_CAST,Asc(Left(cData,1))) // Otherwise convert hex value to beginning of data value
ENDIF
MemFree(pBuffer) // Free buffer
Also note that Wildseek looks for value 32 as the first byte of the record, treating any different value as deleted. This could better be changed to looking for value equal to 42 meaning deleted as ADS uses this position for some purposes other than deleted so a non 32 value does not necessarily mean that it is deleted.
ADS offers a FTS index (Fast Text Search) meaning that the content of a field is indexed so it is possible to search on any phrase in the field. Surrounded by an *, it need not start with that phrase. This is not a good alternative to search in any field as this would require a FTS index tag for every field. Hence we are happy to have our wildseek working but we will replace our memo wildseek with FTS based searches:
cZoekterm:="*"+cSearch+"*"// Search all occurances of csearch
cZoekfilter:="Contains(INFO,"+"'"+cZoekterm+"')" // Create filter string
otxt:SetFilter(, cZoekfilter)// Set filter. optimized bu FTS index tag
This is the code to create an index tag on a memo field for words up to 30 characters in the current .cdx:
IF Upper(aIndex[dI][DBC_INDEXNAME])="TXTOBJECT"
hTableHandle:=oServer:INFO(DBI_GET_ACE_TABLE_HANDLE)
// Get handle of current db (code for new db: tbhandle:=DWORD(_CAST,AX_GetAceTableHandle()))
nIndexResult :=AdsCreateFTSIndex(hTableHandle, String2Psz("TXTOBJECT.cdx") ,;
String2Psz("INFOFTS"), String2Psz("INFO"), ADS_DEFAULT, 0, 30, 0,;
String2Psz( " " ), 0, String2Psz( " " ), 0, String2Psz( " " ), 0, String2Psz( " " ), NULL_PSZ, NULL_PSZ, ADS_COMPOUND )
ANSI functions
As you have to apply SetANSI(False) to enable ADS to handle ADS conversion you have to realize that multiple non database VO functions use SetAnsi as well. For example:
FReadText() and MemoRead()), FreadStr() (not mentioned in the VO help file, but apparently also influenced). Leaving these unchanged will convert Windows special/ANSI characters to some DOS character. You will have to write alternatives like this one:
dwFileSize:=DWORD(_CAST,FSeek(ptrHandle,0,FS_END))
FSeek(ptrHandle, 0)
cText := Buffer (dwFileSize)
dwError := FError ()
IF dwError == 0
IF FRead (ptrHandle, @cText, dwFileSize) == dwFileSize
IF !FClose(ptrHandle)
Recordfilters and eval
In our program some of the browser based overviews use an Eval filter. This is basically a piece of program code, started with a code block, which evaluates several values. The final step of most of our evals was to inspect an array with records if this was created by a user filter action. Codeblock based filters will not operate on the ADS server. A filter on one or more fields should be constructed as a large text string but that would not include inspecting a array for records. Combining a string filter and a record filter is not possible in ADS. We solved this is follows. For example: our browser shows emails with at least the usual information as Outlook would do. ADS can create indexes much faster than DBFCDX can. We have redesigned the email program to work on a orderscope at all times. Once a (less used) overview is selected, e.g. the user sorts the mails on subject, a user specific cdx is added with the subject and some standard fields as a tag. We found a 60.000 records mail database to index in about a second. Only the record filtering is set as filter (if any, resulting from e.g. a text search in the email source), using the following code for the traditional filtering versus ADS filtering.:
IF lUseADS
IF nAantal>0
tbhandle:=oServer:INFO( DBI_GET_ACE_TABLE_HANDLE )
// Get handle of current db (code for new db: tbhandle:=DWORD(_CAST,AX_GetAceTableHandle()))
AdsSetAOF(DWORD(_CAST,tbhandle),String2Psz(".F."), ADS_RESOLVE_DYNAMIC)
FOR ni:=1 UPTO nAantal // Add all records
nRec:=SELF:aSelection[ni]
AdsCustomizeAOF(tbhandle, 1, @nRec, ADS_AOF_ADD_RECORD // Adds to ADS filter
NEXT
ENDIF
ELSE
oFilterBlock := FilterBlock{oServer, {|oSrv|oSrv:IsSelected()}}
oServer:aRecords := SELF:aSelection
ENDIF
We found that reversing an index did not work very well in ADS, The browser (bBrowser) did not go to the correct record using GoTop() and it was possible to browse beyond the orderscope: after browsing the highest values, the lowest values appeared again.
IF lUseADS
hHandle:=AX_GetAceIndexHandle("emails.cdx","BOXID")
AdsSetIndexDirection(DWORD(_CAST,hHandle),1) // 1 reverses current direction
ELSE
SELF:oDB:OrderDescend (, , SELF:lDescend)
ENDIF
We now just recreate the temporary index after using SELF:oDB:SetOrderCondition (, , , , , , , , , , SELF:lDescend).
FPT files
FPT files need to have a block size of 64 bytes while default in VO they will get 32 bytes. We had to write a program to convert all the dbf files with a FPT with a 64 bit block size. Note that all kind of on first sight unrelated errors can occur when you still work with 32 bits memofields, including failing seeks etc.
Summary
This article aims to help VO and hopefully other developers to transform their applications as smoothly as possible. It doesn’t give you much new option, only what we needed to have our application working. It does not show all a VO developer needs to know regarding the differences; see for that the Application Development Notes in the VO section of the help file. We have uploaded 3 longer methods (Wildseek, a check function to see if ADS is active and 3 methods to convert the memo fields to 64 bytes block size) : www.ic2.com/CAVO/ADSPrgs.zip