!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()(((

!"#$%&'(#)%"*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*,(

-.)"/*011*(%&2*3$%4*56$7%'$*++++++++++++++++++++++++++++++++++++++++++++++++++++*8(

9'):&)"/*$&&6&.;:)7*3%$*6*.<2()3)(*=0>*?2$.)%"*++++++++++++++++++++++++++++++++++++++++*@(

0).#$)7'#)"/*6"*=&?6"#6/2*A"67:2&*B:)2"#*=<<:)(6#)%"*++++++++++++++++++++++++++++++++*C(

D24%#2E*!"#2$"2#E*1%(6:*=&?6"#6/2*B%""2(#)%".*++++++++++++++++++++++++++++++++++++*FG(

H2.#)"/*%'$*&2?2:%<42"#*2"?)$%"42"#*++++++++++++++++++++++++++++++++++++++++++++*FF(

H2.#)"/*.%42*76.)(*=0>*D00*3'"(#)%"6:)#I*3$%4*56$7%'$*+++++++++++++++++++++++++++++*F8(

0).<:6I)"/*<$%/$2..*)"3%$46#)%"*+++++++++++++++++++++++++++++++++++++++++++++++++*FJ(

HK2*=0>*06#6*0)(#)%"6$I*+++++++++++++++++++++++++++++++++++++++++++++++++++++++*F@(

B$26#)"/*6*06#6*0)(#)%"6$I*3$%4*56$7%'$*B%&2*+++++++++++++++++++++++++++++++++++++*,F(

06#6*=$(K)#2(#* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*,L(

M2N*H67:2*HI<2.*=?6):67:2*N)#K*=0>*+++++++++++++++++++++++++++++++++++++++++++++*,O(

B$26#)"/*6*"2N*00*3$%4*2P).#)"/*3$22*#67:2.*+++++++++++++++++++++++++++++++++++++++*L@(

=&?6"#6/2*Q<#)4)R2&*S):#2$.*++++++++++++++++++++++++++++++++++++++++++++++++++++*LO(

S6.#*H2P#*>26$(K*TSH>U*)"&2P2.*+++++++++++++++++++++++++++++++++++++++++++++++++++*J,(

=&.+2$$;6&#*2$$%$*:%/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*JJ(

=BA8,*56$7%'$*=V!*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*JW((

( !

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+(((

!"#$%&'(#)%"*

Advantage Database Server (ADS) is a high performance client/server RDBMS for stand-alone, networked, Internet, and mobile database applications. It allows developers the flexibility to combine powerful SQL statements and relational data access methods with the performance and control of ISAM navigational commands. ADS was first developed by Extended Systems to help Clipper applications avoid table and index file corruption. Now in its 10th version it is much more than just that. It would seem logical for new incarnations of xbase language programmers to use ADS without much trouble. Sadly, this has not been the case. The fact that, in its most basic form, using ADS only requires a simple change to the default RDD being used, while almost everything else stays the same, doesn’t seem to help alleviate this problem. In part this happens because the information on how to use ADS from Harbour and Harbour is somewhat scattered and many times incomplete. Another reason this happens is because, when the decision is finally made to move to ADS, the developer is looking to learn how to implement new methodology that is simply not available without a true client-server paradigm, which is one of the features offered by ADS. The main intent of this writing aims to provide developers of new generation Clipper languages the information needed to get started taking advantage of ADS. It shows sample code that performs the most basic tasks, progressing from simple to more complicated chores mixing SQL code with traditional ISAM table navigation. The reader is encouraged to download the free 2-user version of ADS server from http://advantagedatabaseserver.com/ to test the sample code. A copy of Arc32 (Sybase Data Architect) should also be downloaded and installed for this tool will ease learning SQL and we will make use of it. In the xbase world there is Harbour and there is xHarbour. They are both fully Object Oriented development languages that find their roots in Clipper, a once extremely popular but bygone compiler. The former initially intended to act as a multi platform replacement to the Clipper compiler. The latter, on the other hand, intended to extend said language. That is why the name e(x)tended Harbour. Lately, this distinction has been blurred. Both now manage data types and language constructs unheard off when Clipper reigned as king. Both projects are open source and reasonably active. The code used throughout this writing is (x)Harbour specific but should work just the same with Harbour. Throughout this document the term Harbour will be used to refer to both. The choices of GUI used with Harbour are well known in the community. The sample code stays away from using any GUI related syntax. Instead, Win32 console apps will be constructed. This may make for rather dull looking apps, but ADS functionality will not be sacrificed. ADS and Harbour is marriage made in heaven. Unlike any other SQL engine in the market, ADS does not do away with the traditional x-base ISAM syntax for database navigation. The Harbour developer may "move" an existing application to ADS and start using the speed and many other benefits offered by the ADS server and RDD extensions while keeping the very same inherited dbf/ntx and-or dbf/cdx tables with none or very little code change. Moving an existing application to use ADS RDD opens a plethora of new capabilities including the use of the Advantage Data Dictionary and SQL transaction set based functionality while keeping indexes to

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(1(((

traverse tables, i.e. ISAM navigation. In short, ADS is the offspring of the union between ISAM and SQL, offering Harbour developers the best of both worlds. +,)"-*.//*(%&0*1$%2*34$5%'$*

Understanding how Harbour interacts, sends and receives parameters with –to and from .dll functions is not crucial to using ADS. Thus, this section may be skipped by the reader. You may want to take a peek only if you ever want a deeper understanding of how ACE32 works from our language. Harbour does not generate an object file (.obj), instead it generates a C source file (.c). Thus, there are commands to tell the compiler what is already in C and not to be “translated”. In other words, these commands signal the Harbour compiler that “inline” C code follows. Here is an example: )(

*------------------------------------------------------------------------------ +(//harbour code 1(#include "hbapi.h" 2(

3(Function Main() 4(

5(? helloworld() 6(

7(Return nil ),(

))(*------------------------------------------------------------------------------ )+(//C code that contains the helloworld() function definition being called above. )1(//please note it is case sensitive. )2(

)3(#pragma BEGINDUMP )4(#include <windows.h> )5(#include “hbapi.h” )6(

)7(HB_FUNC( HELLOWORLD ) +,({ +)(

hb_retc( “hello world” ); ++(} +1(

+2(#pragma ENDDUMP +3(

The #pragma is a directive used inside the source code in many compilers to change the behavior of the compiler at compile time. As you might already suspect, #pragma BEGINDUMP tells the compiler where the c code begins and #pragma ENDDUMP signals where it ends. To pass and receive parameters between Harbour code and c code there are a set of functions. To receive parameters from Harbour, the c code may use these functions: hb_parc( n ) receives a string from Harbour being sent as the nth parameter. hb_parni( n ) receives an integer being sent as the nth parameter. hb_parnl( n ) receives a long type as the nth parameter. hb_parnd( n) receives a double.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(2(((

hb_parl( n) receives a Boolean as the nth parameter being passed. In turn, to send results back to Harbour from c code the vm allows the use of these: hb_ret() -to return nil. hb_retni() to return an integer (numeric once back in Harbour). hb_retnl() to return a long data type. hb_retnd() to return a double data type. hb_retl() to return a Boolean data type. hb_retc() to return a string. The special function hb_pcount() returns the number of parameters received from Harbour code and maybe used inside a c function to know how many parameters were sent to the c function. Below is another example on how to call a common MS-Windows API function directly from a Harbour source code:

/*---------------------------------------------------------------------------------------------------*/ )(//Reinaldo Crespo-Bazán +(#define MB_ICONINFORMATION 0x00000030 1(

2(Function Main() 3(

AlertWindow( "Alert Message", "Window Title", MB_ICONINFORMATION ) 4(Return Nil 5(

6(//Below is the C function AlertWindow() used above: 7(HB_FUNC( ALERTWINDOW ) ),({ ))(

MessageBox( GetActivewindow(), hb_parc( 1 ), hb_parc( 2 ), hb_parni( 3 ) ); )+(} )1(

The above example constitutes a “wrapper” c function to the Windows API MessageBox() function. Because the ADS C wrapper functions are pure C code, there is no need to enclose them between #pragma BEGINDUMP and #pragma ENDDUMP. Instead they are saved inside a .c source file that is to be compiled by the C compiler and then linked to the application. Below is a simple ADS c wrapper function code that calls the Advantage Client Engine (ACE) function AdsDisconnect().

/*---------------------------------------------------------------------------------------------------*/ )(HB_FUNC( ADSDISCONNECT ) +({ 1(/* From ACE HLP: 2(

AdsDisconnect is used to disconnect a connection from the specified server. 3(If tables are currently opened, all data is flushed, locks are released, 4(and open tables are closed before the disconnect occurs. 5(

* 6(If zero is passed as the connection handle, all connections on the server 7(associated with the user will be disconnected. If AdsDisconnect is called ),(on a connection with a transaction active, the transaction will be rolled back. ))(

* )+(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(3(((

*/ )1( )2(UNSIGNED32 ulRetVal = ~AE_SUCCESS; )3(ADSHANDLE hConnect = HB_ADS_PARCONNECTION( 1 ); )4(

)5(// Only allow disconnect of 0 if explicitly passed or adsConnectHandle is 0 )6(// (hConnect might be 0 if caller accidentally disconnects twice; this should not close all connections!) )7(if ( hConnect != 0 || ISNUM( 1 ) ) +,({ +)(

ulRetVal = AdsDisconnect( hConnect ); ++(} +1(

+2(if( ulRetVal == AE_SUCCESS ) +3({ +4(

if ( hConnect == adsConnectHandle ) +5({ +6(

adsConnectHandle = 0; +7(} 1,(

1)(hb_retl( 1 ); 1+(

} 11(else 12({ 13(

hb_retl( 0 ); 14(} 15(

}16(

Below are the C wrapper functions to simpler and very often used ACE calls. Namely AdsSetFileType(), AdsSetServerType(), and AdsSetDateFormat():

/*---------------------------------------------------------------------------------------------------*/ )(HB_FUNC( ADSSETFILETYPE ) +({ 1(

int fileType, oldType = adsFileType; 2(if( hb_pcount() > 0 ) 3({ 4(

fileType = hb_parni( 1 ); 5(if( fileType > 0 && fileType < 4 ) 6({ 7(

adsFileType = fileType; ),(} ))(

} )+(hb_retni( oldType ); )1(

} )2(

/*---------------------------------------------------------------------------------------------------*/ )(HB_FUNC( ADSSETSERVERTYPE ) +({ 1(

int servType; 2(UNSIGNED32 ulRetVal = 999999; 3(if( hb_pcount() > 0 ) 4({ 5(

servType = hb_parni( 1 ); 6(ulRetVal = AdsSetServerType( servType ); 7(

} ),(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(4(((

hb_retnl( ulRetVal ); ))(} )+(

/*---------------------------------------------------------------------------------------------------*/ )(HB_FUNC( ADSSETDATEFORMAT ) +({ 1(

UNSIGNED8 pucFormat[16]; 2(UNSIGNED16 pusLen = 16; 3(

4(hb_retc( ""); 5(AdsGetDateFormat( pucFormat, &pusLen ); 6(if( pusLen > 0 ) 7(

{ ),(hb_retc( (char *) pucFormat ); ))(} )+(

)1(if( ISCHAR( 1 ) ) )2({ )3(

AdsSetDateFormat( (UNSIGNED8*) hb_parcx( 1 ) ); )4(} )5(

} )6( )7( The full source to ACE C wrapper functions is available from the Harbour cvs repository. They are all contained inside a file named adsfunc.c in directory \contrib\rdd_ads and basically they make use of ace32.dll. Further down this reading you will find each wrapper function documented. These wrapper functions are the contribution of Alexander Kresin (alex@belacy.belgrorod.su). Most recently its development, maintenance, and documentation is been steered by Brian Hays of Abacus Data Systems Inc.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(5(((

6')7&)"-*$&&4&,87)5*1%$*4*,90()1)(*:.;*<0$,)%"*

Advantage Client Engine (Ace32.dll) is Sybase’ API to ADS. We will be calling many of these external functions from our Harbour apps. In order for the Linker to resolve references to external functions, like the ones in ace32.dll, we must provide a .lib file format of the .dll file that contains the actual functions as part of our link script. ACE32 is distributed by Sybase and it can be found after installing Data Architect in “c:\program files\Advantage 10.0\ARC\ace32.dll” or after installing the Advantage SDK available from the download link at http://devzone.advantagedatabase.com. Each C compiler has its own utility to create a .lib from a .dll. With Borland C++, the most widely used C compiler in the community, that utility is called "implib.exe" and it can be found on \bin directory of the BCC compiler. To create the actual .lib file implib is executed like this: Implib ace32.lib ace32.dll. The command will generate ace32.lib from ace32.dll. Once the .lib file has been created, it must be linked to the application that makes use of the dynamic linked library functions. With the commercial version of xharbour (xhb builder), you need to link ads.lib which is provided in the /lib directory. With the non-commercial version, link ace32.lib as well as rddads.lib which is located in /contrib/rdd_ads. From time to time Sybase will release a new update of ADS that requires newer clients. While older clients are always compatible with newer servers the opposite is not true. It should be noted that it is not always desirable to stay with old clients (adsLoc32.dll, ace32.dll, axcws32.dll). Newer servers introduce new functionality and services that are only available with the corresponding new client. Thus, it may be necessary to build different versions of the ADS libs (rddads.lib and ace32.lib) to be linked to the Harbour application depending on the server and client being targeted. To build rddads.lib the developer needs to acquire ace.h for the ADS version for which the .lib is to be created i.e. (ADS version 7, 8, 9...). This file can be found on the Advantage SDK which is available for download from the advantage client engine link found in http://devzone.advantagedatabase.com or in the \rdd_ads directory inside \xharbour\contrib directory. Please note that the SDK will also contain ace32.dll which we need to create ace32.lib. Ace.h is the only version specific file to be used when building rddads.lib. It is crucial that the correct ace.h is used and it should be the one that corresponds to the advantage .dll files and ADS version being targeted. More than once the Harbour project developers have changed the method necessary to build an ADS version specific rddads.lib. Below we show three different methods to build this lib. Method #3 is the latest attempt by the developers in trying to eliminate having to edit text files and taking multiple steps in order to specify the version for which the lib is being built. Please try method #3 first. !"#$%&'()'*%+',-./&.01'+&&2&34/.,'*%+'5%-+'36"7.*.7'89:';"+3.%0<' The lines below will compile and link the RDD sources to create rddads.lib to be used with ADS version 8 .dll files. On these sample command lines Borland c++ is used to create the .lib. Replace the "800" with the version number (700, 800, 900...):

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(6(((

C:\Borland\Bcc55\bin\bcc32 -c -O2 -DWIN32;ADS_LIB_VERSION=800 -IC:\xharbour\include -oADS1.OBJ ADS1.C C:\Borland\Bcc55\bin\bcc32 -c -O2 -DWIN32;ADS_LIB_VERSION=800 -IC:\xharbour\include -oADSFUNC.OBJ ADSFUNC.C C:\Borland\Bcc55\bin\bcc32 -c -O2 -DWIN32;ADS_LIB_VERSION=800 -IC:\xharbour\include -oADSMGMNT.OBJ ADSMGMNT.C C:\Borland\bcc55\bin\tlib rddads +ads1.obj +adsfunc.obj +adsmgmnt.obj Finally, remember to create ace32.lib for the ADS version in question. Again, using Borland c++ the command line would be: C:\borland\bcc55\bin\implib ace32.lib ace32.dll. Depending on the x(H)arbour build, you may need to change the ADS_LIB_VERSION=xxx compiler flag to –DADS_REQUIRE_VERSION=8.

!"#$%&'(='*%+',-./&.01'+&&2&34/.,'*%+'5%-+'36"7.*.7'89:';"+3.%0<''Make_vc.bat and make_b32.bat are two MS-Windows batch files supplied to ease the task of building rddads.lib. The first is tailored for MS-Visual C use and the second for the Borland C++ compiler. Executing either of these batch files should create rddads.lib provided that the appropriate C compiler is present. Before executing make_b32.bat make sure that you have set the environmental variable CLIBFLAGS to the appropriate ADS version that you wish to build rddads.lib for: C:\xharbour\contrib\rdd_ads\>set CLIBFLAGS=-DADS_REQUIRE_VERSION=9 If the /bin directory of your C compiler is not part of the current search path, then you may have to change the batch file to add the complete path to the /bin directory of the C compiler version you are using with your Harbour projects. You may also have to edit make_b32.bat to add a definition to the ADS .dll version you wish to build rddads.lib for. For example for version 8, change make_b32.bat from: # Implicit Rules .c.obj: bcc32 $(CLIBFLAGS) $(C_USR) $(CFLAGS) -c -O2 -DWIN32 -I$(INCLUDE_DIR) -o$@ $** To: # Implicit Rules .c.obj: \borland\bcc55\bin\bcc32 $(CLIBFLAGS) $(C_USR) $(CFLAGS) -c -O2 -DWIN32;ADS_LIB_VERSION=800 -I$(INCLUDE_DIR) -o$@ $** The batch file assumes that the directory \xharbour\obj\b32 and the directory \xharbour\lib\b32\ are present. You will probably have to create these directories by hand: C:\xharbour>mkdir .\obj\b32 C:\xharbour>mkdir .\lib\b32

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(7(((

Finally we are ready to execute make_b32.bat to create rddads.lib that will be linked to your Harbour apps using ADS. You will find the newly create lib in \xharbour\lib\rddads.lib. !"#$%&'(>'*%+',-./&.01'+&&2&34/.,'*%+'5%-+'36"7.*.7'89:';"+3.%0<' Starting with version 1.2.1 of the compiler, the Harbour project developers have put a new system in place to automatically get everything set up in order to build \xharbour\lib\rddads.lib as well as ads32.lib for any specific version and it works quite well. It requires acesdk directory for the specific ADS version. Acesdk is installed from aceapi.exe which can be downloaded from the advantage devzone as noted previously. Once acdsdk has been installed, set the environmental variable HB_DIR_ADS to the directory that contains the SDK files: C:\xharbour\contrib\rddasd>set HB_DIR_ADS=c:\ads8\acesdk Now execute the batch file for the C compiler being used with your applications and the newly created rddads.lib and ace32.lib necessary to be linked to your Harbour apps will be found in c:\xharbour\lib. .),#$)5'#)"-*4"*:&<4"#4-0*="4570&*>7)0"#*:997)(4#)%"*

Include the files below with your distribution of a Windows Harbour Advantage client application: 27">=4&//: Advantage Client Engine DLL. This .dll contains the core Advantage Windows client functionality. 2&3/%7>=4&//: Advantage Local Server DLL. This .dll contains the core local server functionality. This file is not necessary if using the Advantage Database Server only. 2&3/%72/47*1: Advantage Local Server configuration file. This file is only necessary if using the Advantage Local Server and you wish to use Local Server settings other than the default. 203.47$+: This file is needed for ANSI language support with the Advantage Local Server. This file may only be necessary if using non-English ANSI character sets with the Advantage Local Server. 2?7@3>=4&//: Advantage remote communication library used when accessing data via the Advantage Database Server -Remote as well as Internet connections. This file is not necessary if using the Advantage Local Server only. "?#"0&47$+: This file is needed for international OEM language support with the Advantage Local Server. This file is only necessary if using non-USA OEM character sets with the Advantage Local Server.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(),(((

?02%#0@*!"#0$"0#@*/%(47*:&<4"#4-0*>%""0(#)%",*

With Advantage there are three different ways to connect to data:

1. Advantage Local Server (ALS). 2. Advantage Database Server in a two tier mode (Client-Server). 3. Advantage Internet Server (AIS).

8A:: Advantage can establish a secure connection between remote locations through basic internet connections. Using an Advantage Internet connection provides both encryption and compression. When the connection is done via the internet then it is referred to as an Advantage Internet Server (AIS) connection (**not a remote connection as logically you might conclude**). This requires configuring an internet port and opening that port within the firewall at the server end as well as the client's end. With the Advantage Internet Server, a developer can have a truly Internet-enabled application without any code changes. This simple, non-HTML solution is incorporated into the Advantage Database Server so that the Internet can be used as its own virtual private network. Be advised that Internet connections tend to be slower than a regular LAN connection, thus certain ways to display data might need to be avoided. B"C%#": Do not confuse the term "Advantage Remote Connection" with an internet connection to the data on the server. With ADS, a Remote connection is really the LAN connection to an actual ADS server, i.e. a two tier -Client-Server system connecting via tcp or udp without the use of the Internet. When using Remote connections, there is no need to share the data directory that contains the actual dbf/adt tables for an Advantage enabled application to access this data. This is one of the security features of ADS using server-side aliasing which we will discuss on the later part of this document. The Advantage Database Server is the key to improved database performance in network environments. The server acts as an intelligent controller that reduces competition for resources and off-loads much of the work normally performed by each client workstation. It is responsible for all database access, including all reading and writing of data, and lock management. Working with the network operating system, the Advantage Database Server processes data requests and returns the information to the network clients. The Advantage Database Server supports the NetWare, Windows, and Linux operating systems. The Advantage Database Server for NetWare is implemented as a NetWare Loadable Module (NLM). The Advantage Database Server for Windows operates as a Windows Service. The Advantage Database Server for Linux runs as Linux daemon service. The Remote and AIS client code used by an Harbour client application compiled to run under Windows is axcws32.dll. The Remote and AIS code used by an Harbour client application compiled to run under Linux is a shared object named libads.so. 8D:: The Advantage Local Server allows Advantage applications access to data files located locally, in shared environments, or in peer-to-peer environments. The Advantage Local Server is

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0())(((

a non-client/server solution and can be used to access data on the local computer or on a shared file server that is not running the Advantage Database Server service. The Advantage Local Server is called directly by the Advantage Client Engine, both of which exist as either Windows DLLs or Linux shared objects. If the data files exist on a local workstation, no network connection is necessary nor is network communication used between the Advantage client and the Advantage Local Server. There is no cost for the Advantage Local Server. The Advantage Local Server is installed with all Advantage Windows and Linux client products (which are also free). With ALS you may develop applications for single and multi-user environments and distribute them royalty-free. The Windows version of the Advantage Local Server (ALS) is a DLL named ADSLOC32.DLL. The Linux version of the Advantage Local Server is a shared object named libadsloc.so. The Advantage Local Server allows both single-user and multiple-user access to data files. Note however that the Advantage Local Server file that is installed with Advantage client products contains a physical limitation such that only five or fewer users can concurrently access any table. One drawback of ALS is the lack of "transaction processing". A second, the fact that the ALS "client" works in the same thread with the ALS "server", waiting one for another as it would if the data was stored locally on the client machine. In this scenario the server only acts as a file sharing disk to multiple workstations pulling data across the network. But then again, this is exactly how any other Harbour RDD works. What makes ALS very attractive is that you may distribute your application to smaller customers without the ADS server and when the customer grows in the number of users, or requires greater security and speed, or cannot tolerate the possibility of data/index corruption due to a workstation unexpected crash, then for a small price you may deploy the ADS remote server while not even having to recompile your application. A0,#)"-*%'$*&0<07%920"#*0"<)$%"20"#*

If the developer already has an Harbour development environment set and working, then this section should be skipped, otherwise and before we go any further, let's make sure we have everything we need to test our samples. If you haven't already done so, download xharbour binaries (http://sourceforge.net/projects/xharbour/files/ ) ready to be used with Borland c++ as well as Borland c++ ( http://cc.codegear.com/Free.aspx?id=24778 ). The Borland c++ Compiler includes the Borland Turbo Incremental Linker (tlink32) and the Borland Resource Compiler (brc32, brcc32). Now set the environment variables used by xharbour and Borland c++: Make sure that the directory containing xharbour binaries (xharbour.exe) is in your windows search path. Make sure that the directory containing bcc32.exe is in your windows search path. bcc32.exe is located in the /bin subdirectory of the Borland C compiler installation directory.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()+(((

Make sure that there is a bcc32.cfg file in the \bin subdirectory of your compiler. It's needed to set the compiler options for the Include and Lib paths. This is what bcc32.cfg could contain: -I"c:\xharbour\bcc55\include" -L"c:\xharbour\bcc55\lib" Make sure that there is a ilink32.cfg file in the \bin subdirectory of your compiler. It is required in order to set the linker options for the Lib file paths. For example, a sample ilink32.cfg could contain (paths may differ): -L"c:\xharbour\bcc55\lib;c:\xharbour\bcc55\lib\psdk;c:\xharbour\lib" Below is a batch file used to compile all our samples; save it to "buildx.bat". To compile simply execute the batch file supplying the .prg file as the only parameter, as in: buildx dbcreate.

echo off )(if A%1 == A GOTO :SINTAX +(if NOT EXIST %1.prg GOTO :NOEXIST 1(

2(ECHO Compiling... 3(

4(rem ------------------------------------------------------------------------------------------ 5(rem you will most likely have to change the paths below 6(rem ------------------------------------------------------------------------------------------ 7(set hdir=f:\xharbour_1.21 ),(set bcdir=f:\borland\bcc55 ))(

)+(rem ------------------------------------------------------------------------------------------ )1(rem open notepad with compile results after compiling )2(rem ------------------------------------------------------------------------------------------ )3(%hdir%\bin\harbour %1 /n /i..\include;%hdir%\include /w /p %2 %3 > clip.log )4(@notepad clip.log )5(

)6(IF ERRORLEVEL 1 PAUSE )7(IF ERRORLEVEL 1 GOTO EXIT +,(

+)(echo -O2 -e%1.exe -I%hdir%\include %1.c > b32.bc ++(%bcdir%\bin\bcc32 -M -c -v @b32.bc +1(:ENDCOMPILE +2(

+3(IF EXIST %1.rc %bcdir%\bin\brc32 -r %1 +4(

+5(echo c0w32.obj + > b32.bc +6(echo %1.obj, + >> b32.bc +7(echo %1.exe, + >> b32.bc 1,(echo %1.map, + >> b32.bc 1)(

1+(rem ------------------------------------------------------------------------------------------ 11(rem link xharbour libraries 12(rem ------------------------------------------------------------------------------------------ 13(echo %hdir%\lib\rtl.lib + >> b32.bc 14(echo %hdir%\lib\pcrepos.lib + >> b32.bc 15(echo %hdir%\lib\vm.lib + >> b32.bc 16(echo %hdir%\lib\gtwin.lib + >> b32.bc 17(echo %hdir%\lib\lang.lib + >> b32.bc 2,(echo %hdir%\lib\macro.lib + >> b32.bc 2)(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()1(((

echo %hdir%\lib\rdd.lib + >> b32.bc 2+(echo %hdir%\lib\dbfntx.lib + >> b32.bc 21(echo %hdir%\lib\dbfcdx.lib + >> b32.bc 22(echo %hdir%\lib\dbffpt.lib + >> b32.bc 23(echo %hdir%\lib\hbsix.lib + >> b32.bc 24(echo %hdir%\lib\debug.lib + >> b32.bc 25(echo %hdir%\lib\common.lib + >> b32.bc 26(echo %hdir%\lib\tip.lib + >> b32.bc 27(echo %hdir%\lib\pp.lib + >> b32.bc 3,(

3)(rem ------------------------------------------------------------------------------------------ 3+(rem link our rddads.lib and ace32.lib that corresponds to the verson of ace32.dll being used 31(rem ------------------------------------------------------------------------------------------ 32(echo %hdir%\lib\rddads.lib + >> b32.bc 33(echo %hdir%\lib\Ace32.lib + >> b32.bc 34(

35(rem ------------------------------------------------------------------------------------------ 36(rem link borland libraries 37(rem ------------------------------------------------------------------------------------------ 4,(echo %bcdir%\lib\cw32.lib + >> b32.bc 4)(echo %bcdir%\lib\import32.lib + >> b32.bc 4+(echo %bcdir%\lib\psdk\odbc32.lib + >> b32.bc 41(echo %bcdir%\lib\psdk\rasapi32.lib + >> b32.bc 42(echo %bcdir%\lib\psdk\nddeapi.lib + >> b32.bc 43(echo %bcdir%\lib\psdk\iphlpapi.lib, >> b32.bc 44(

45(rem ------------------------------------------------------------------------------------------ 46(rem uncomment this line to use the debugger and comment the following one 47(rem %bcdir%\bin\ilink32 -Gn -Tpe -s -v @b32.bc 5,(%bcdir%\bin\ilink32 -Gn -aa -Tpe -s -v @b32.bc 5)(IF ERRORLEVEL 1 GOTO LINKERROR 5+(GOTO EXIT 51(:LINKERROR 52(ECHO * There are errors 53(GOTO EXIT 54(:SINTAX 55(ECHO SYNTAX: Build [Program **No .PRG extension needed**] 56(ECHO 57(GOTO EXIT 6,(:NOEXIST 6)(ECHO The specified PRG %1 does not exist 6+(

61(:EXIT 62(

Notice how on line 55 we are linking the rddads.lib we created on the previous section as well as how on line 56 we are also linking ace32.lib that was the result implib of ace32.dll A0,#)"-*,%20*54,)(*:.;*?..*1'"(#)%"47)#B*1$%2*34$5%'$*

To begin with some code samples, let's also introduce some basic field extensions available with Advantage. In this case we use the RDD function dbCreate() using the ADS RDD to create a Visual Fox Pro table type while gaining access to all the extended field types available to this type of table. Some sections down in this writing, we discuss the extended field types as well as the benefits they each offer.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()2(((

We start with "Requesting ADS". The REQUEST statement declares a symbolic name of a function or procedure for the linker. This is usually required when there is no direct call of a function or procedure in PRG code. Thus, the line "Request ADS" forces the linker to include all the ADS corresponding functions on the final executable. Always include this line on all your Advantage enabled Harbour apps. Keep in mind that the ADS RDD opens access to four different table types. Thus, before creating the actual table using the standard rdd dbcreate() function, the developer needs to specify the file type to create using the ACE function AdsSetFileType(): "AdsSetFileType( ADS_CDX) --or ADS_NTX , or ADS_ADT, or ADS_VFP. Also the statement SET FILETYPE TO ADS_VFP is pre-processed to AdsSetFileType(). The field types should specify the full field type name as in "VARCHAR" or "Autoinc" and not just the first character as traditionally done in xbase unless the field type being specified is one of the traditional five types: “C” for character, “N” for numeric, “L” for logical, “D” for date and “M” for memo. In the section dedicated to discussing field types we also explain how the RDD expects each field to be referred as. File type VFP_DBF, being used in our example, was introduced in ADS version 9. Thus, it is possible that you may need to rebuild rddads.lib to test our example if the one present on your current \lib\rddads.lib is for an older version of ADS. Below is the sample code using dbCreate() to create a Visual FoxPro table type with some extended field types with xharbour code using the ADS RDD:

#include "ads.ch" )(request ads +(request hb_gt_win //needed for console mode app. 1(

2(/*-----------------------------------------------------------------------------------------------*/ 3(Function Main() 4(

5(SetMode( 25, 80 ) //25 lines by 80 columns console 6(

7(rddRegister( "ADS", 1 ) ),(rddSetDefault( "ADS" ) ))(adsSetServerType( ADS_LOCAL_SERVER ) )+(adsSetFileType( ADS_VFP ) //visual foxpro table and field types )1(

)2(AdsLocking( .t. ) //NON-compatible locking mode )3(CreateTable( 'Test') )4(

)5(BrowseTable('Test') )6(

)7(return nil +,(

+)(/*-----------------------------------------------------------------------------------------------*/ ++(static function CreateTable( cTableName ) +1(local aStructure := { { "Field1", "Autonic", 07, 0 },; +2(

{ "field2", "Money", 09, 2 },; +3({ "field3", "TimeStamp", 01, 0 },; +4(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()3(((

{ "field4", "C", 20, 0 },; +5({ "field5", "Binary", 10, 0 } } +6(

+7(dbCreate( cTableName, aStructure ) 1,(

1)(Return nil 1+(

11(/*-----------------------------------------------------------------------------------------------*/ 12(static function BrowseTable(cTableName) 13(

14(Dbusearea( .t. , "ADS", cTableName, "alias", .t., .f. ) 15(Browse() 16((alias)->( dbcloseArea() ) 17(

2,(Return Nil 2)(/*-----------------------------------------------------------------------------------------------*/ 2+(

On this example we created and then opened a Visual Fox Pro dbf table for browsing. If the tables to be used already exist and they reside on one single directory, then we could use ACE function adsConnect() to connect to the directory and then access the tables without having to specify the path. In ADS nomenclature tables that reside on a directory that are not part of an Advantage Data Dictionary, are termed as "free" tables. The opposite of a free table, i.e. "non-free", is a Data Dictionary bound table. There are huge incentives in using Advantage Data Dictionaries (.add) files. We will get to that in the next section. .),974B)"-*9$%-$0,,*)"1%$24#)%"*

*The sample below illustrates how to re-index a table while displaying progress using ACE function AdsRegCallBack(). Keep in mind that when using the ADS server, these massive table operations are performed at the server end which makes for a more secure and faster result. While indexing, records do not need to be sent across the wire anymore. Instead the whole operation happens encapsulated inside the server’s RAM, CPU, and hard drive. In short, an external process does the indexing and this process knows nothing of Harbour’s expressions or codeblocks executing on the workstation issuing the re-index instruction. Even with the Local Server, it is ACE32 doing all the indexing. Therefore ACE32 has special functions in order to keep the workstation informed on the progress of these operations. The special ACE function AdsRegisterCallBackFunction() does this. In Harbour AdsRegCallBack() is wrapper function to ACE32 AdsRegisterCallBackFunction(). Below is a short example of how to use it while indexing:

/*-----------------------------------------------------------------------------------------------*/ )(static function ReIndex( cAlias ) +(

1( 2(

AdsRegCallBack( { | nPercent | ShowProgress( nPercent ) } ) 3(/* the above codeblock will be called approximately every 2 seconds while indexing. 4(The codeblock may return .t. to abort indexing. */ 5(

6(INDEX ON FileName Tag FileName 7(

),(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()4(((

AdsClrCallBack() ))( )+(Return Nil )1(

AdsRegCallBack() works well when indexing tables, but for many other SQL queries Harbour’s wrapper implementation of ACE AdsRegisterCallBackFunction() is not quite adequate or it simply doesn’t return any progress. Below we show a different wrapper implementation of ACE AdsRegisterCallBackFunction() function to be used by the Harbour developer to get more accurate progress information while executing an SQL query. The Harbour function name as implemented below is Register_Callback() and it receives as parameter an Harbour code block that is to be executed by the client app while the SQL query takes place. The code block is executed approximately every 2 seconds. The eval function executing the Harbour code block sends the progress percentage received from the server as the only parameter to the code block. Upon completion of the SQL operation, Unregister_Callback() should be executed to clear the callback codeblock from memory. … )(

Register_CallBack( { | nPercent | Qout( “Percent completed: “ + Str( nPercent ) } ) +( ADSExecuteSQLDirect( cScript ) 1( Unregister_callback() 2(… 3( 4(*------------------------------------------------------------------------------------------------------- 5(#pragma BEGINDUMP 6(#include <windows.h> 7( ),(#include "ace.h" ))(#include "hbapi.h" )+(#include "hbvm.h" )1(#include "hbapiitm.h" )2( )3(unsigned long __stdcall ShowPercentage( UNSIGNED16 usPercentDone, UNSIGNED32 ulCallbackID ); )4( )5(static PHB_ITEM pBlock; )6( )7(HB_FUNC( REGISTER_CALLBACK ) +,({ +)( pBlock = hb_itemParam( 1 ); ++( hb_retni(AdsRegisterCallbackFunction( ShowPercentage, 1 ) ); +1(} +2( +3(HB_FUNC( UNREGISTER_CALLBACK ) +4({ +5( AdsClearCallbackFunction(); +6( +7( if( pBlock ) 1,( { 1)( hb_itemRelease( pBlock ); 1+( } 11(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()5(((

} 12( 13(unsigned long __stdcall ShowPercentage( UNSIGNED16 usPercentDone, UNSIGNED32 ulCallbackID ) 14({ 15( PHB_ITEM pPercent = hb_itemPutNI( NULL, usPercentDone ); 16( 17( if( pPercent && pBlock) 2,( { 2)( hb_vmEvalBlockV( pBlock, 1, pPercent ); 2+(

hb_itemRelease( pPercent ); 21( } 22( 23( return 0; 24(} 25( AC0*:.;*.4#4*.)(#)%"4$B*

Back on the Clipper days, "database" was a term used for a single .dbf file. With the influence of SQL and other market forces, now days we no longer call a single table a database. It is the collection of tables, whether dbfs or adts or both, used by an application that is called a database. An Advantage Data Dictionary (ADD) is a special file that contains a wide range of definitions used by the Advantage Server to control the data and the tables that makes up the database. Although it is possible to extract benefit from using the Advantage Server without using ADDs, once the developer understands the value added by such dictionary, it would be hard to imagine doing without it. This is because only by implementing ADDs becomes possible to access ADS most powerful and advanced features. First, let us discuss the benefits of using Data Dictionaries and after that we will show how to implement the creation and maintenance of Data Dictionaries from Harbour code and/or use of Data Architect -Arc32.exe. 92#2'9.7#.%02+."3'2//%@3'#$"'.C6/"C"0#2#.%0'%*<'

H$)//2$.F: A trigger is a piece of SQL code that is executed on the server before or after or instead when records are appended, deleted, or modified. When a trigger is "fired" it contains some state information, which can be used inside the body of the trigger. Two in-memory tables are available inside a trigger; __new and __old. The __new table contains new field values that were (in case of an after trigger), or are about to be (in case of a before trigger), inserted into the table. The __old table contains old field values for the record in question. Triggers are ideal for tasks such as keeping an audit log of changes to certain critical tables, for creating queues of tasks to be performed by another application, such as send an email or process certain results any time a field changes. The possibilities are endless. Triggers can provide a very powerful means to maintain business rules inside the database. Advantage trigger functionality is available with both ADT and DBF tables. (Please see note #1 at the end of this section).

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()6(((

D232$2"#)6:*!"#2/$)#I): With RI rules, Harbour developers don’t need to be concerned if a parent record on a table is deleted while leaving child records on a transaction file orphan. RI rules take care of enforcing primary to foreign key relationships for all the tables involved on the RI rule. As xbase developers, we are used to write code into our applications to enforce referential integrity. By moving this logic into the database, no longer do we need to take care of this from code for this is now the job of the server. RI rules only work with ADTs and VFP table types and not with dbfs. When using RI with VFP tables, you will need to add the expression “!deleted()” as a condition on the related index definition. Without !deleted() condition, the key will not change when the record is deleted preventing the RI operation from being triggered. Referential Integrity rules offer a set of four actions to take in case of a deletion or update on a parent record or its primary key. These actions are to “restrict”, “cascade”, “set_null”, “set_default”. To learn more details about these four actions or about RI in general, see “Referential Integrity” under Advantage Concepts on the Advantage Database Server help guide. (Please see note #1 at the end of this section).

0236':#*S)2:&*X6:'2.: Default Field Values allows assigning a specific value to a field any time a new record is created. This could be an ordinal value, like zero, or date stamp value like now(), or the result from any of the available expression engine functions such as guid().

S)2:&*12?2:*B%".#$6)"#.: By implementing FLCs you have a better guarantee that the data being stored on the field makes sense by restricting it to a given range or a particular value. For example that the field where a price is stored is not negative or null, or that the date being stored is not before today, or that an ordinal value is within certain range. If the FLC fails when a new record is being appended or modified the operation fails and the associated error text can be retrieved using the ace function AdsGetLastError(). FLCs are available for all table types, including DBFs, starting with version 9. Again, the idea here is to transfer some business logic to the Data Dictionary, liberating the developer from tracking any of this from source.

D2(%$&*12?2:*B%".#$6)"#.: Using RLCs we let the server take care of validating certain relationships within fields in the same record being appended or modified. For example that if the account type begins with T, then the classification field contains only one of the values in the list [ 1, 7, 9 ], or that if sex of the patient is female, then that the field for last menstrual period is not blank. RLCs are available for all table types, including DBFs, starting with version 9.

=((2..*B%"#$%:*#%*#67:2.*6"&*3)2:&.: When connecting to an Advantage Data Dictionary, it is no longer necessary to implicitly declare the path where the tables are located. The path to the table and indexes are also stored on the DD metadata and it may be relative to the DD's path. Therefore there is no need to hard-code (or otherwise) table paths anymore.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0()7(((

When connection is made to a REMOTE server and storing the tables on a directory that is only visible to the user that owns the ADS service process, access to the data is only possible via the application that is connecting to the DD. The client Advantage application, i.e. our Harbour applications connect to an ip and port where the server is listening. Essentially, this eliminates the needs for file sharing on the server. When the directory where our data files is shared, our database is exposed and insecure: it may be deleted, copied, moved... By removing file sharing, our data directory is a lot more secure as only gaining access to the physical server and authenticating as the Administrator becomes possible to browse the contents of the directory. Two DD properties control how users can connect to the DD: (1) “Logins Required” and (2) “Rights Verification”. When #1 is on, connecting to the DD requires a user name and password combination. Once the user has been authenticated, then assigned access to fields and tables are enforced if property #2 is turned on. In short, the DD contains the list of users with its field and table permissions as well as username and password used to connect and if the user is allowed to connect via the Advantage Internet Server (AIS). When #1 and #2 are turned on, the server takes care of granting the appropriate rights to tables, fields in a table, views and stored procedures. User groups can be defined to ease the administration of users with similar access rights and is the advised way to do it.

X)2N.: A view is a virtual table that is not physically stored in the database, but appears exactly like a "real" table. A view can contain data from one or more tables or other views and is used to store often-used queries or query sets in a database. Views can be updateable views or read-only views. By giving users access rights to the views, but not giving them rights to the base tables, views can also provide a limited means of security. For example, a view can be defined to only allow the user to see certain columns in a table while hiding the data in other fields that contain sensitive information. View functionality is available with both ADT and DBF tables.

H67:2Y!"&2P*='#%ZB$26#)%": Auto-creation functionality exists for tables and indexes belonging to an Advantage Data Dictionary. When a table associated with a data dictionary is opened, if the table's file and/or index file(s) are missing, new ones will be created with the file structure as defined on the DD. If a newly created table had memo fields, its memo file will be created as well. This functionality is enabled by setting a dictionary table’s auto-creation property to True with the property ADS_DD_TABLE_AUTO_CREATE. It is illustrated on samples further below. It is also possible to set the memo block size for auto-created tables to something other than the default size by using the ADS_DD_TABLE_MEMO_BLOCK_SIZE property. Table/Index Auto-Creation functionality is available with both ADT and DBF tables. Notice that this allows the developer to deliver the application with a Data Dictionary and no tables. Tables can be created on the fly using structure definitions stored on the Data Dictionary.

D2<:)(6#)%": There are many uses for replication. One very common use is having multiple sets of data being maintained on more than one physical location anywhere in

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+,(((

the world and as long as there is route (locally or via Internet) between the servers data is replicated to a centralized location or n-way between each node. It happens in the background and with very little to no human intervention. Replication is ideal for satellite and branch offices using cheap Internet connectivity to send/receive to and from a main office or between each other. Even when the connection route (Internet) is down, users may continue to work as usual. As soon as the connection is reestablished, replication restarts in the background processing transactions stored on its queue. Replication is an inexpensive option on the server version. Replacement of a VPN or a dedicated line is very probable by implementing replication over a cheap DSL connection.

>[1*6/6)".#*&73Y"#P*D00*#67:2.: By using a DD the server will know what index files are associated to a dbf file even when using ntx indexes. Thus, these indexes do not need to be compound indexes. This will make the SQL execution plan to shortcut having to traverse the whole table whenever the use of an index helps to avoid it. A"($I<#)%": Encryption offers another layer of security. The Advantage Data Dictionary eases deployment of encryption of all or some tables as metadata is stored on the Dictionary that is then applied transparently to the Advantage enabled client application connecting to the Data Dictionary. =((2..*#%*#67:2.*?)6*6"*!"#2$"2#*(%""2(#)%"*'.)"/*=!>\***By connecting to a Dictionary via an AIS connection the Advantage enabled application knows nothing about the path where data is stored. The code on the application does not need to be adjusted. Once ACE function ADSConnect60() is successful connecting to the Data Dictionary a Win32 application will work just the same independent of the route used to reach the server. For an AIS connection to be successful the property Enable Internet access must be checked on the Data Dictionary.

*1When retrieving data using SQL queries instead of xBase ISAM syntax, the ADS server engine simply sends a result set represented as a table (“cursor”) to the ADS client. When implementing triggers and referential integrity rules, the Harbour developer must keep in mind that an xbase syntax statement may cause the execution of an SQL trigger or RI rule altering the state of the table and that there is no way for the server to “speak” back to the client RDD about what has just happened. This may cause the RDD to become temporarily “out of sync” with the server. This is especially true if a trigger or an RI rule prevents a record from being deleted while issuing the xbase function dbDelete(). To alleviate this problem, always check for any errors after a dbcommit() by executing ACE function AdsGetLastError(). Any value returned by this function greater than zero will indicate that the delete was unsuccessful. Executing RDD function dbRecall() will avoid the RDD to be out of sync with the server. The code below shows the general idea: … )( +(( cAlias )->( dbrLock() ) 1(( cAlias )->( dbDelete() ) 2(( cAlias )->( dbCommit() ) 3( 4(If ( nErr := AdsGetLastError() ) != 0 5(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+)(((

? “Error trying to delete record”, nErr 6( ( cAlias )->( dbRecall() ) 7(Endif ),( ))(( cAlias )->( dbUnlock() ) )+( )1(… !>$04#)"-*4*.4#4*.)(#)%"4$B*1$%2*34$5%'$*>%&0*

This sample code creates three tables. The first is named "customers.adt" as an ADT table with extended field types "AutoInc" and "TimeStamp". We will dedicate a section to discussing the benefits of .adt tables later on this text. The second table, named "sales.dbf" as a visual fox pro table with extended field types "integer" and "money". Finally, the third table as a dbf/ntx traditional table. After the tables and its indexes are crated, a data dictionary that will contain the three tables as they exists, will be created as well. This code gives an idea on how to create a data dictionary of existing tables, as well as after creating tables from scratch. The trick is performed by invoking the ACE function ADSDDCreate(). )(

#include "ads.ch" +( 1(#xcommand DEFAULT <uVar1> := <uVal1> ; 2( [, <uVarN> := <uValN> ] => ; 3( <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; 4( [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] 5( 6(REQUEST ADS 7(request hb_gt_win //needed for console mode app. ),( ))(Static cPath )+( )1(*----------------------------------------------------------------------------------------------------- )2(Function Main() )3(local nerr )4(local afiles )5( )6( SetMode(25,80) //25 lines by 80 columns console )7( +,( rddRegister( "ADS", 1 ) +)( ++( rddSetDefault( "ADS" ) +1( +2( adsSetServerType( ADS_LOCAL_SERVER ) +3( +4( AdsLocking( .t. ) //NON-compatible locking mode +5( +6( cPath := hb_ArgV( 1 ) +7( 1,( DEFAULT cpath := "" 1)( 1+( if !empty( cPath ) .and. right( cPath, 1 ) != "\" ;cPath += "\" ;endif 11( 12( afiles := CreateTables() 13( 14(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(++(((

if !empty( afiles ) ;CreateDictionary( afiles ) ;endif 15( 16(return nil 17( 2,(*-------------------------------------------------------------------------------------------------------- 2)(static function CreateTables() 2+(local cAlias := "MyAlias" 21(local afiles := {} 22(local cFileName, e 23( 24( //---------------------- customers.adt -------------------------------------------- 25(local aStruc := { { "cust_id" , "C", 10, 0 },; 26( { "Sequence" , "AutoInc", 07, 0 },; //ADT extended field type autoincrement 27( { "customer_name" , "C", 25, 0 },; 3,( { "Start_date" , "TimeStamp", 01, 0 },; //ADT extended field type 3)( { "Notes" , "M", 10, 0 } } 3+( 31( AdsSetFileType( ADS_ADT ) 32( 33( TRY 34( dbCreate( ( cFileName := cPath + "customers.adt" ), aStruc,, .t., cAlias ) 35( 36( ( cAlias )->( OrdCreate( cPath + "customers", "cust_id", "cust_id" ) ) 37( ( cAlias )->( OrdCreate( cPath + "customers", "Start_date", "Start_date" ) ) 4,( ( cAlias )->( dbclosearea() ) 4)( 4+( aadd( afiles, { cFileName, "customers.adi" } ) 41( 42( //------------------------ sales.dbf ------------------------------------------------- 43( aStruc := { { "cust_id" , "C", 10, 0 },; 44( { "invoice" , "C", 15, 0 },; 45( { "s_date" , "TimeStamp", 01, 0 },; //VFP extended field type 46( { "item_id" , "C", 15, 0 },; 47( { "Units" , "N", 03, 0 },; 5,( { "Price" , "Money", 09, 2 },; //VFP extended field type 5)( { "Notes" , "M", 10, 0 } } 5+( 51( AdsSetFileType( ADS_VFP ) 52( 53( dbCreate( ( cFileName := cPath + "sales.dbf" ), aStruc,, .t., cAlias ) 54( 55( ( cAlias )->( OrdCreate( cPath + "sales", "invoice", "invoice" ) ) 56( ( cAlias )->( OrdCreate( cPath + "sales", "cust_id", "cust_id" ) ) 57( ( cAlias )->( dbclosearea() ) 6,( 6)( aadd( afiles, { cFileName, "sales.cdx" } ) 6+( 61( //------------------------ items.dbf ------------------------------------------------- 62( aStruc := { { "item_id" , "C", 15, 0 },; 63( { "Desc" , "C", 25, 0 },; 64( { "Price" , "N", 07, 2 } } 65( 66( AdsSetFileType( ADS_NTX ) 67( 7,( dbCreate( ( cFileName := cPath + "items.dbf" ), aStruc,, .t., cAlias ) 7)( ( cAlias )->( OrdCreate( cPath + "itm_id",, "item_id" ) ) 7+(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+1(((

( cAlias )->( OrdCreate( cPath + "itm_desc",, "Desc" ) ) 71( ( cAlias )->( dbclosearea() ) 72( 73( aadd( afiles, { cFileName, "itm_id.ntx;itm_desc.ntx" } ) 74( 75( CATCH e 76( 77( ShowError( cFileName, e ) ),,( afiles := {} ),)( ),+( END ),1( ),2(return afiles ),3( ),4(/*-------------------------------------------------------------------------------------------------------- ),5(CreateDictionary creates an Advantage Data Dictionary based on already existing tables ),6(--------------------------------------------------------------------------------------------------------*/ ),7(static function CreateDictionary( afiles ) )),(local cExt AS CHARACTER := "" )))(local cDD := cPath + "test_dd.add" ))+( ))1( if !ADSDDCREATE( cDD,, "Sample data dictinoary" ) ))2( ))3( Alert( "AdsCreate() of " + cDD + " failed. Error:" + Str( AdsGetLastError() ) ) ))4( return nil ))5( ))6( Endif ))7( )+,( AdsDDSetDatabaseProperty( ADS_DD_ENABLE_INTERNET, .t. ) )+)( AdsDDSetDatabaseProperty( ADS_DD_INTERNET_SECURITY_LEVEL, ADS_DD_LEVEL_2 ) )++( AdsDDSetDatabaseProperty( ADS_DD_DEFAULT_TABLE_PATH, cPath ) )+1( AdsDDSetDatabaseProperty( ADS_DD_LOG_IN_REQUIRED, .t. ) )+2( )+3( AdsDDCreateUser( , "user1", "password1", "User named userd1 with password password1" ) )+4( AdsDDCreateUser( , "user2", "password2", "Description of user2" ) )+5( AdsDDCreateUser( , "user3", "password3", "Optional description of user3" ) )+6( )+7( aEval( aFiles, ; )1,( { |e| hb_FNameSplit( iif( len( e ) > 1, e[ 2 ], e[ 1 ] ), , , @cExt ), ; )1)( AdsSetFiletype( iif( lower( cExt ) $ ".adt,.adi", ADS_ADT, ; )1+( iif( lower( cExt ) == ".ntx", ADS_NTX, ADS_VFP ) ) ),; )11( addfiletoDD( e, cPath ), ; )12( SetTableProp( e ), ; )13( QOut( "Adding table to DD", ; )14( e[ 1 ], ; )15( AdsSetFileType(), ; )16( cExt, ; )17( ADSGetLastError() ) } ) )2,( AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "password" ) )2)( Wait )2+( )21(return Nil )22( )23(*------------------------------------------------------------------------------------------------------------------------------- )24(Static Function AddFiletoDD( aFile, cPath ) )25(local cIndexs := "" )26(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+2(((

)27( if len( afile ) > 1 .and. left( afile[ 2 ], 1 ) <> "." )3,( )3)( aEval( afile, { |e| cIndexs += e +";" }, 2 ) )3+( cIndexs := left( cIndexs, len( cIndexs ) -1 ) )31( )32( Endif )33( )34(Return( AdsDDAddTable( left( afile[ 1 ], at(".", afile[1] )-1 ), ; )35( cPath + aFile[ 1 ], ; )36( cIndexs ) ) )37( )4,(*------------------------------------------------------------------------------------------------------------------------------- )4)(static function SetTableProp( aFile ) )4+(local cFileName )41( )42( hb_FNameSplit( afile[ 1 ],, @cFileName ) )43( )44(// AdsDDSetTableProperty( cFileName, ADS_DD_TABLE_AUTO_CREATE, .t. ) )45(// AdsDDSetTableProperty( cFileName, ADS_DD_TABLE_ENCRYPTION, .t. ) )46( )47(return Nil )5,( )5)( )5+(*------------------------------------------------------------------------------------------------------------------------------- )51(static function ShowError( cMsg, oErr ) )52(local cErr )53( )54( cErr := Str( AdsGetLastError() ) )55( )56( Alert( "Error : " + alltrim( cErr ) + " " + ; )57( cMsg + " " + oErr:Subsystem + " " + ; )6,( str( oErr:subCode ) + " " + oErr:operation + " " + oErr:description ) )6)( )6+( dbcloseall() )61( )62(return nil )63(

After creating the DD it is a good idea to connect to it using Arc32 (Data Architect) to inspect that everything worked as expected. The next section shows how. Don't forget to connect as the default Advantage Data Dictionary (ADD) administrator: "AdsSys" using the password assigned from code in the example above: "password" -which happens to be a terrible choice of password. .4#4*:$(C)#0(#*

Before we continue on our journey it is a good idea to learn about Data Architect. This is a freely distributed and very powerful win32 application by Sybase. It is referred to as Arc. It may be downloaded from the product download link on Sybase’ ADS devzone page: 899:;<<=$#>"?$@.=#.?9./$=.9.&.A$@B"%(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+3(((

Ex-Clipper developers may think of Arc as a graphical replacement for DBU. It eases browsing, import and export, creation and configuration of tables, views, queries and even the Advantage Data Dictionary. Surely all these tasks may be performed from code and sometimes it is preferable. But at other times, like when you need to inspect or manipulate, import, export data, or test and debug an SQL query, Arc proves to be the best choice for the chore. Arc also helps developers in the database design by giving insight and even providing the code needed to do it programmatically as we show in the sample code further ahead. Take a moment to introduce yourself to Arc by connecting to our newly created Data

Dictionary. On figure 1 above, we show Arc with 9 different DD connections already defined on the repository. Some are “LOCAL” connections while others are “REMOTE” and “INTERNET” connections. Our newly created connection for testing purposes, “test”, appears expanded on the figure. To create a new connection in the connections repository to an existing DD, such as the one we just created from source on the previous section, click on “Create New Connection” under the “Connection” menu option. The new connection dialog box will show:

&./012%3%

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+4(((

(&./012%4

On ConnectionPath enter the path where the DD resides. This may be a UNC convention name such as \\server:port\path\. On DatabaseName enter “test_dd.add” as shown on line 112 of our sample code on the previous section. If testing with the local server, then leave “local” on Server Type. Otherwise choose the appropriate server, “Remote” or “Internet”, in which case you should have the ADS server service running. For more information on how to install, configure, and run the ADS service process see the help file under Contents in the Help menu option of Arc or visit: 899:;<<=$#>"?$@.=#.?9./$=.9.&.A$@B"%<=><C$&D$E:<F=#.?9./$),<G?=$H@89%EI%.A9$'J.=#.?9./$JB"?KG/L'.9G"?JL9GEG9MJK"'JNG?="NAJ?9J+,,,J+,,1@89%( Once connected to our newly created Data Dictionary let’s explore some options and make some changes to our tables: 9"7/2+"'2'E0.F-"'A0&"?'G"5<'Right click on the table name and choose properties. Open the additional index definitions tab and change the “cust_id” index to unique. On the Sales table change the Cust_id key by adding the condition “!deleted()”. The !deleted() condition is actually used by the server when calculating the number of records in an Advantage Optimized Filter (AOF) such as when browsing a subset of data that may be filtered based on an existing index. It is also a pre-requisite to create a referential integrity rule for the parent primary index key involved in the rule. H+"2#"'2'B"*"+"0#.2/'A0#"1+.#5'+-/"<'Right-click on RI Objects and choose create. A dialog will open where you may declare the rule’s name, the parent table, child table, and what to do when an update happens to the parent primary key field or when a record is deleted on the parent table with children records on the child table.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+5(((

By right clicking on the table and choosing “properties” you may also change some field properties such as valid range and default value. Once we are done creating RI’s, triggers, encryption type and field constraints right click on the data dictionary name, test_dd, and choose “Generate SQL Script”. The SQL editor will open with the full SQL script needed to create the data dictionary and tables with all the properties as setup up to this point. Optionally you could right click on any given table or RI rule and choose “Generate SQL Script” to view the SQL script that would create the given object. The SQL script to create the particular RI rule defined visually on the sample above would be: )(

EXECUTE PROCEDURE +( sp_CreateReferentialIntegrity ( 1( 'No Orphan Records', 2( 'customers', 3( 'sales', 4( 'CUST_ID', 5( 1, 6( 2, 7( NULL/* Enter Fail table path here. */, ),( '', ))( ''); )+(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+6(((

Further down this reading we show how to execute any SQL statement against tables on a Data Dictionary from Harbour code. For xbase developers Arc is very intuitive. Play around with it. Open existing tables make changes and create new indexes with it. Once you learn how to use it, you’ll surely carry with it as part of your tool chest whether you use ADS or not. It is especially helpful to browse and learn to use the help files available under the Help menu. Arc also has a very clever utility that actually helps you construct SQL statements, helps in debugging it and also shows if it optimized. Going into these details would be redundant. Arc is well documented. To learn more about Data Architect visit: 899:;<<=$#>"?$@.=#.?9./$=.9.&.A$@B"%<=><C$&D$E:<F=#.?9./$),<G?=$H@89%EI=$#/LG=$J.=#.?9./$J=.9.J.'B8G9$B9@89%( D0E*A4570*AB90,*:<4)74570*E)#C*:.;*

As Harbour programmers we are accustomed to dbf/ntx as well as dbf/cdx table types. They have worked just fine in the past and find it is hard to imagine the need for another table type. DBFNTX and DBFCDX are perfectly compatible with ADS as shown on the previous sample code. However, with ADS new table types with exciting new extended field types become now available to the Harbour developer. At first it may seem like some of the new field types are superficial, especially when accustomed to dbf table types. But once you discover the versatility and usefulness of these field types, then you will greatly appreciate them. For example: in a multiuser environment and just before updating a record, with ADT’s field type “RowVersion” it is possible to check if someone else hasn’t changed the record since it was last fetch. ADT tables are the Advantage proprietary table format. ADT tables employ the .adt/.adi/.adm file extension for the proper table, compound index, and memo file respectively. This new format greatly expands the existing and well documented limitations of .dbfs such as: S)2:&*M642.: Field names are not restricted to 10 characters. Instead they may be up to 128 characters long H67:2*>)R2.: The maximum table size now depends on the operating system. For Windows and Netware servers this limit is expanded to 16 exabytes. For Linux servers with kernel 2.4 or greater it is 8 exabytes. ]6P)4'4*"'472$*%3*$2(%$&.: The maximum number of records that a single table may contain is 2.2 billion.

]6P)4'4*$2(%$&*:2"/#K\**The maximum number of bytes that a single record can occupy is 64 kilobytes.

]6P)4'4*424%*3):2*.)R2\ The maximum memo file size is 4 terabytes. ]6P)4'4*)"&2P*3):2*.)R2\ The maximum memo file size is 35 terabytes.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(+7(((

M2N*2P)#)"/*3)2:&*#I<2.\ The available field types in the ADT table type goes far beyond the Character, Date, Logical, Numeric and Memo field types. Below is a table that lists all these field types with its description: Field Type RDD Size Description AutoIncrement AutoInc 4 4-byte read-only positive integer value from 0 to

4,294,967,296 that is unique for each record in the table. This value is sequential. It is not recommended to use this value as the unique key of a table. If you need an automatically generated unique key for the record, then using the scalar function guid() on a character type field is the preferred method.

Binary Binary 9 Variable-length memo field containing binary data. The size of each field is limited to 4 GB. The binary data is actually stored in a separate file, called a memo file, to reduce table bloat.

Character C 1 – 65530 Fixed-length character field that is stored entirely in the table.

ciCharacter rddads currently can’t manage this field type.

1 – 65530 Case insensitive fixed-length character field that is stored entirely in the table.

CurDouble Curdouble 8 Date D 4 4-byte integer containing a Julian date Double Double 8 8-byte IEEE floating point value in the range 1.7E

+/-308 (15 digits of precision). The decimal value affects the use of the field in expressions. It does not affect the precision of the stored data. If the length is given, it will be ignored. For example, "salary, double, 10, 2" and "salary, double, 2" produce the same field.

Image Binary 9 Variable-length memo field containing binary image data. The size of each field is limited to 4 GB. The binary image data is actually stored in a separate file, called a memo file, to reduce table bloat.

Integer I when DBS_LEN is 4

4 4-byte long integer values from --2,147,483,647 to 2,147,483,647.

Logical L 1 1-byte logical (boolean) field. Recognized values for

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(1,(((

True are ‘1’, ‘T’, ‘t’, ‘Y’, and ‘y’.

Memo M 9 Variable-length memo field containing character

data. The size of each field is limited to 4 GB. The memo data is actually stored in a separate file, called a memo file, to reduce table bloat

ModTime Modtime 8 8-byte value where the high order 4 bytes are an integer containing a Julian date, and the low order 4 bytes are internally stored as the number of milliseconds since midnight. The value of this field is automatically updated with the current date and time each time a record is updated.

Money Y 8 Currency data stored internally as a 64-bit integer, with 4 implied decimal digits from -922,337,203,685,477.5807 to +922,337,203,685,477.5807. The Money data type will not lose precision.

Numeric N 32 digits with 29 decimal places

Fixed-length (exact ASCII representation) numeric. One byte is reserved for the sign of the numeric value. If the decimal value is not zero, one additional byte is used for the decimal point.

Raw Raw 1 – 65530 Fixed-length, data-typeless raw data field. From (x)harbor ACE APIs must be used to store and retrieve raw data.

RowVersion rowversion 8 An 8-byte unsigned integer unique for each record in the table that is automatically incremented each time a record is updated.

ShortInteger I when DBS_LEN is 2

2 2-byte short integer value from -32,767 to 32,767.

Time Time 4 4-byte integer internally stored as the number of milliseconds since midnight.

TimeStamp Timestamp 8 8-byte value where the high order 4 bytes are an

integer containing a Julian date, and the low order 4 bytes are internally stored as the number of milliseconds since midnight. If using the Advantage CA-Visual Objects RDDs, this is a string type.

VarBinary Q 1 to 65000 Variable length binary data. The maximum length of

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(1)(((

data that can be stored in the field is specified when the table is created. This is similar to the Raw field type except that the true length of the data is stored internally in the record.

VarChar V 1 to 65530 This field type allows variable length character data to be stored up to the maximum field length, which is specified when the table is created. It is similar to a character field except that the exact same data will be returned when it is read without extra blank padding on the end. If you are creating this field using the ACE API function AdsCreateTable, you must specify the type as "VarCharFox" to avoid legacy compatibility issues with an older obsolete varchar field type.

>$04#)"-*#C0*.)(#)%"4$B*',)"-*?..*1'"(#)%",*4"&*;F/G*

One of the unique properties of Advantage and the Advantage RDD is that you can perform many of the typical data chores either by using regular xbase ISAM navigational style syntax, RDD aliased functions, and ACE API calls, as we have demonstrated on the examples on the previous sections, or by issuing SQL statements. On the example code below we show how to accomplish the exact same task of creating the data dictionary and tables as done on our last example using Harbour code but this time we do so with SQL statements. Once you see how SQL statements are executed, then taking the output from Arc’s “Generate SQL Script” menu option as template for creating any Data Dictionary object from code becomes a trivial matter.

#include "ads.ch" )( +(REQUEST ADS 1(request hb_gt_win //needed for console mode app. 2( 3(Static cPath 4( 5(*----------------------------------------------------------------------------------------------------- 6(Function Main() 7(local nerr ),( ))( SetMode(25,80) //25 lines by 80 columns console )+( )1( rddRegister( "ADS", 1 ) )2( )3( rddSetDefault( "ADS" ) )4( )5( adsSetServerType( ADS_LOCAL_SERVER ) )6( )7( AdsLocking( .t. ) //NON-compatible locking mode +,( +)(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(1+(((

cPath := hb_ArgV( 1 ) ++( +1( DEFAULT cpath := "" +2( +3( if !empty( cPath ) .and. right( cPath, 1 ) != "\" ;cPath += "\" ;endif +4( +5( if createDD() ;CreateTables() ;endif +6( +7(return nil 1,( 1)( 1+(*-------------------------------------------------------------------------------------------------------- 11( 12(static function CreateTables() 13(local e AS OBJECT 14(local cSql_1 AS CHARACTER 15(local cSql_2 AS CHARACTER 16(local cSql_3 AS CHARACTER 17( 2,( cSql_1 := "CREATE TABLE customers ( " + ; 2)( "CUST_ID Char( 10 )," + ; 2+( "SEQUENCE AutoInc," + ; 21( "CUSTOMER_NAME Char( 25 )," + ; 22( "START_DATE TimeStamp," +; 23( "NOTES Memo) IN DATABASE;" + ; 24( ; 25( "EXECUTE PROCEDURE sp_CreateIndex90( "+; 26( "'customers',"+; 27( "'customers.adi',"+; 3,( "'CUST_ID',"+; 3)( "'CUST_ID',"+; 3+( "'',"+; 31( "2,"+; 32( "512,"+; 33( "'' ); "+; 34( ; 35( "EXECUTE PROCEDURE sp_CreateIndex90( "+; 36( "'customers',"+; 37( "'customers.adi',"+; 4,( "'START_DATE',"+; 4)( "'START_DATE',"+; 4+( "'',"+; 41( "2,"+; 42( "512,"+; 43( "'' ); "+; 44( ; 45( "EXECUTE PROCEDURE sp_ModifyTableProperty( 'customers', "+; 46( "'Table_Auto_Create', "+; 47( "'False', 'APPEND_FAIL', 'customersfail');"+; 5,( ; 5)( 5+( cSql_2 := "CREATE TABLE sales ( "+; 51( "CUST_ID Char( 10 ),"+; 52( "INVOICE Char( 15 ),"+; 53( "S_DATE TimeStamp,"+; 54( "ITEM_ID Char( 15 ),"+; 55(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(11(((

"UNITS Numeric( 3 ,0 ),"+; 56( "PRICE Numeric( 9 ,0 ),"+; 57( "NOTES Memo) IN DATABASE;"+; 6,( ; 6)( "EXECUTE PROCEDURE sp_CreateIndex90( "+; 6+( "'sales',"+; 61( "'sales.cdx',"+; 62( "'INVOICE',"+; 63( "'invoice',"+; 64( "'',"+; 65( "2,"+; 66( "512,"+; 67( "'' ); "+; 7,( ; 7)( "EXECUTE PROCEDURE sp_CreateIndex90( "+; 7+( "'sales',"+; 71( "'sales.cdx',"+; 72( "'CUST_ID',"+; 73( "'cust_id',"+; 74( "'',"+; 75( "2,"+; 76( &nbsp;"512,"+; 77( "'' ); "+; ),,( ; ),)( "EXECUTE PROCEDURE sp_ModifyTableProperty( 'sales', "+; ),+( "'Table_Auto_Create', "+; ),1( "'False', 'APPEND_FAIL', 'salesfail');"+; ),2( ; ),3( ),4( TRY ),5( ),6( ExecuteSQL( cSql_1, ADS_ADT ) ),7( ExecuteSQL( cSql_2, ADS_VFP ) )),( )))( CATCH e ))+( ))1( ShowError( e ) ))2( ))3( END ))4( ))5(return nil ))6( ))7(*------------------------------------------------------------------------------------------------------------------------------- )+,(//Reinaldo Crespo-Bazán )+)(//used to execute a non cursor returning SQL script. )++(// )+1(static function ExecuteSQL( cScript, cRDD ) )+2(local cArea, e )+3(local isGood := .f. )+4( )+5( if !empty( cScript ) )+6( )+7( AdsCacheOpenCursors( 0 ) //ace function )1,( dbSelectArea(0) )1)( )1+( //ADSCreateSqlStatement is an ace function )11(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(12(((

IF !ADSCreateSQLStatement("SQLarea", cRDD) //.or. !ADSVerifySQL( cScript ) )12( )13( TRY )14( SQLArea->( DBCLOSEAREA() ) //just wana make sure. )15( CATCH e )16( END )17( )2,( Alert ( "AdsCreateSqlStatement() failed with error "+ Str( ADSGetLastError() ) ) )2)( )2+( Elseif !( isGood := AdsExecuteSQLDirect( cScript ) ) //AdsExecuteSqlDirect Ace func. )21( )22( Alert ( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) ) )23( )24( endif )25( )26( endif )27( )3,( AdsCacheOpenCursors( 0 ) )3)( )3+( if Select( "sqlarea" ) > 0 ;SQLArea -> ( DBCLOSEAREA() ) ;endif )31( )32(return nil )33( )34(/*-------------------------------------------------------------------------------------------------------- )35(CreateDD creates an empty Advantage Data Dictionary )36(--------------------------------------------------------------------------------------------------------*/ )37(static function CreateDD() )4,(local cDD := cPath + "test_dd.add" )4)( )4+( if !ADSDDCREATE( cDD,, "Sample data dictinoary" ) )41( )42( Alert( "AdsCreate() of " + cDD + " failed. Error:" + Str( AdsGetLastError() ) ) )43( return .f. )44( )45( Endif )46( )47( AdsDDSetDatabaseProperty( ADS_DD_ENABLE_INTERNET, .t. ) )5,( AdsDDSetDatabaseProperty( ADS_DD_INTERNET_SECURITY_LEVEL, ADS_DD_LEVEL_2 ) )5)( AdsDDSetDatabaseProperty( ADS_DD_DEFAULT_TABLE_PATH, cPath ) )5+( AdsDDSetDatabaseProperty( ADS_DD_LOG_IN_REQUIRED, .t. ) )51( AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "password" ) )52( )53( AdsDDCreateUser( , "user1", "password1", "User named userd1 with password password1" ) )54( AdsDDCreateUser( , "user2", "password2", "Description of user2" ) )55( AdsDDCreateUser( , "user3", "password3", "Optional description of user3" ) )56( )57(return .t. )6,( )6)(*------------------------------------------------------------------------------------------------------------------------------- )6+(static function ShowError( oErr ) )61(local cErr := Str( AdsGetLastError() ) //AdsGetLastError() ace func. )62( )63( Alert( "Error : " + alltrim( cErr ) + " " + ; )64( oErr:Subsystem + " " + str( oErr:subCode ) + " " + ; )65( oErr:operation + " " + oErr:description ) )66( )67(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(13(((

dbcloseall() )7,( )7)(return nil )7+(

)71( Traditionally xbase developers construct the tables and indexes used by an application from code and store the table’s structures on a memory array whose declaration is also part of the application’s code or on an external text file. This is very useful to create new tables and to update existing table’s structures as needed by the application. The table structure definition array follows the pattern defined in “dbstruct.ch” include file and that is also returned by the RDD function DbStruct(). With Advantage Data Dictionaries there is another way to keep table’s structures and index definitions updated according to how it is expected by new updates of the application. This is done by comparing two different versions of the DD. Next we explore both methods.

First, on the following sample code we show how to maintain the structure of tables as traditionally done by xbase programmers by keeping the table’s structure housed in an array. We use dbcreate() to create a new table and ACE function AdsDDAddTable() to add the newly created table to the DD. If the table is already present in the DD, then we compare its structure against the new structure to make the appropriate changes if needed. To carry out any change on the structure of a table using ISAM, we would traditionally make a copy of the table, create a new table with the new structure, then copy data from the old file to the new and at the end we would then rename tables accordingly. In our sample code we chose to perform structure changes using the SQL ALTER TABLE statement since it is much simpler and secure than using ISAM. To find more information about the SQL ALTER TABLE statement, see the help file.

To stay coherent with traditional xbase table structure definition, on this sample code we continue to keep the table’s structures stored on a array consistent with dbStruct() return value. The ALTER TABLE statement is constructed from the table’s structure array.

On this sample we also introduce code that shows how to connect to an existing DD. We won’t need to specify the table’s physical path as long as we have established a connection to the DD. All we need is the path to the DD. The sample is constructed as a progression of the previous samples:

#include "ads.ch" )(#include "dbstruct.ch" +(#define CRLF chr(13)+chr(10) 1( 2(#xcommand DEFAULT <uVar1> := <uVal1> ; 3( [, <uVarN> := <uValN> ] => ; 4( <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; 5( [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] 6( 7(REQUEST ADS ),(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(14(((

request hb_gt_win //needed for console mode app. ))( )+(Static cPath )1( )2(*----------------------------------------------------------------------------------------------------- )3(Function Main() )4(local nerr )5( )6( SetMode( 50, 80 ) //25 lines by 80 columns console )7( rddRegister( "ADS", 1 ) +,( rddSetDefault( "ADS" ) +)( adsSetServerType( ADS_LOCAL_SERVER ) ++( AdsLocking( .t. ) //NON-compatible locking mode +1( +2( cPath := hb_ArgV( 1 ) +3( DEFAULT cpath := "" +4( +5( if !empty( cPath ) .and. right( cPath, 1 ) != "\" ;cPath += "\" ;endif +6( +7( if !ConnectToDD() 1,( Return nil 1)( endif 1+( 11( CheckTables( GetTableStructures() ) 12( showStructure() 13( 14( AdsDisconnect() //disconnect from test_dd 15( wait 16( 17(return nil 2,( 2)(*----------------------------------------------------------------------------------------------------- 2+(//Ideally you would store the dd name and path and server type on an .ini file or 21(//send both strings as parametrs to the application. 22(// 23(//once connected we don't need table's paths anymore as the dd knows where they reside. 24( 25(static Function ConnectToDD() 26( 27( if !AdsConnect60( cpath + "test_dd.add", ADS_LOCAL_SERVER, "adssys", "password" ) 3,( 3)( Alert( "Cannot connect LOCAL Server to dd: " + cpath + "test_dd.add" + Str( AdsGetLastError() ) 3+() 31( Return .f. 32( 33( endif 34( 35(Return .t. 36( 37(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(15(((

*----------------------------------------------------------------------------------------------------- 4,(static Function GetTableStructures() 4)(local all_files_struc := {} 4+( 41( aadd( all_files_struc,{ "customers.adt VIA ADSADT ",; 42( { { "cust_id" , "C", 10, 0 },; 43( { "customer_name", "C", 25, 0 },; 44(

{ "Logical" , "L", 10 , 0 },; 45({ "date" , "D", 0, 0 },; 46(

{ "Sequence" , "AutoInc", 07, 0 },; //ADT extended field type (EFT) 47({ "TStamp" , "TimeStamp", 01, 0 },; //EFT 5,({ "Integer_field" , "I", 04, 0 },; //EFT 5)({ "Currency_double", "curdouble", 0, 2 } ,; //EFT 5+({ "raw" , "raw", 25, 0 },; //EFT 51({ "rowversion" , "rowversion", 0, 0 },; //EFT 52({ "modtime" , "modtime", 0, 0 },; //EFT 53({ "Double" , "Double", 4, 0 },; //EFT 54({ "Image" , "Binary", 10, 0 },; //RDD sees Image,Binary,Blob as Binary 55({ "time" , "time", 0, 0 },; //EFT 56({ "VarChar" , "V", 10, 0 },; //EFT 57({ "short_integer", "I", 02, 0 },; //EFT 6,(

{ "Notes" , "M", 10, 0 } } } ) 6)( 6+(/* although valid field types, currently rddads does not support 61(field types ciCharacter, Money, VarBinary. But they will work just fine with SQL CREATE TABLE 62(or ACE ADSCreateTable() 63( { "ciCharacter" , "ciCharacter", 10 , 0 },; //case insensitive 64(

{ "Money_Field" , "Y", 8, 4 },; //extended field type Money 65({ "VarBinary" , "Q", 10, 0 },; //extended field type 66(

67(*/ 7,( aadd( all_files_struc, { "sales.dbf VIA ADSVFP STRICT", ; 7)( { { "cust_id" , "C", 10, 0 },; 7+( { "invoice" , "C", 15, 0 },; 71( { "item_id" , "C", 15, 0 },; 72(

{ "Date" , "D", 8, 0 },; 73({ "Logical" , "L", 10 , 0 },; 74({ "TStamp" , "TimeStamp", 01, 0 },;//VFP extended field type 75({ "Sequence" , "autoincrement", 0, 0 },; //VFP extended field type 76({ "Units" , "I", 04, 0 },; //VFP extended field type 77({ "Double" , "Double", 4, 0 },; //VFP extended field type ),,({ "VarChar" , "VarCharFox", 10 , 0 },; //VFP extended field type ),)({ "VarBinary" , "Q", 10, 0 },; //VFP extended field type ),+({ "Price" , "Y", 0, 0 },; //VFP extended field type Money ),1({ "Image" , "Binary", 10, 0 },; //VFP extended field type ),2(

{ "Notes" , "M", 10, 0 } } } ) ),3( ),4( aadd( all_files_struc, { "items.dbf VIA ADSNTX", ; ),5( { { "item_id" , "C", 15, 0 },; ),6(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(16(((

{ "Desc" , "C", 25, 0 },; ),7( { "Price" , "N", 07, 2 } } } ) )),( )))(Return all_files_struc ))+( ))1(*----------------------------------------------------------------------------------------------------- ))2(static function CheckTables( aFiles ) ))3(local isStrict ))4(local cFileType ))5(local cTable, cExt, cFileName ))6(local aStruc, n ))7(local aDDFiles := AdsDirectory() //ACE32 function call returns array of tables in dd )+,( )+)( for each aStruc IN aFiles )++( )+1( isStrict := .f. ////allow only the exact structure sent with no extra fields on table )+2( cFileName := lower( aStruc[ 1 ] ) )+3( )+4( cFileName := SplitTableName( cFileName, @isStrict, @cFileType ) )+5( AdsSetFileType( cFileType ) )+6( )+7( hb_FNameSplit( cFileName,,@cTable, @cExt ) )1,( )1)( if aScan( aDDfiles, cTable ) == 0 //table not in DD )1+( )11( qout( "creating an ntx table ", ctable ) )12( dbcreate( cTable, aStruc[ 2 ] ) )13( AdsDDAddTable( cTable, cTable + iif( cFileType == ADS_ADT, ".adt", ".dbf" ), "" ) )14( )15( else )16( )17( CheckTableStructure( aStruc, cTable, isStrict ) )2,( )2)( endif )2+( )21( next )22( )23(return nil )24( )25(*----------------------------------------------------------------------------------------------------- )26(static function SplitTableName( cFileName, isStrict, cFileType ) )27(local nPos )3,( )3)( DEFAULT isStrict := .f. )3+( DEFAULT cFileType := ADS_ADT //presume .adt when not using VIA as part of tablename )31( )32( if at( " STRICT", upper( cFileName ) ) > 0 )33( isStrict := .t. )34( cFileName := lower( alltrim( strtran( cFileName, " STRICT", "" ) ) ) )35(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(17(((

Endif )36( )37( if ( nPos := at( " VIA ", upper( cFileName ) ) ) > 0 )4,( cFileType := iif( "CDX" $ upper( substr( cFileName, nPos ) ), ADS_CDX, ; )4)( iif( "VFP" $ upper( substr( cFileName, nPos ) ), ADS_VFP ,; )4+( iif( "NTX" $ upper( cFileName ), ADS_NTX, ADS_ADT ) ) ) )41( )42( cFileName := lower( alltrim( Left( cFileName, nPos -1 ) ) ) )43( )44( Endif )45( )46(return cFileName )47( )5,(*----------------------------------------------------------------------------------------------------- )5)(static Function CheckTableStructure( aTable, cTable, isStrict ) )5+(local aStruc := aTable[ 2 ] )51(local cAlias, n )52( )53( qOut( "Checking structure of table: " + cTable ) )54( if TableStrucIsChanged( cTable, @aStruc, @cAlias, isStrict ) //aStruc may change if !isStrict )55( )56( UpdateTableStruc( cTable, aStruc, cAlias ) )57( )6,( endif )6)( )6+( (cAlias)->( dbCloseArea() ) )61( )62(Return Nil )63( )64(*----------------------------------------------------------------------------------------------------- )65(static Function TableStrucIsChanged( cFileName, aStruc, cAlias, isStrict ) )66(local isChanged := .f. )67(local nPos := 0 )7,(local i := 0 )7)(local afStruc //afStruc is the found file structure )7+(local j )71( )72( cAlias := cFileName )73( Dbusearea( .t. , "ADS", cFileName, cAlias, .f. ) //exclusive use is needed to update table struc )74( afStruc := ( cAlias )->( dbstruct() ) )75( )76( while ++i <= len( aStruc ) .and. !isChanged )77( +,,( if len( aStruc ) > len( afStruc ) .or. ; +,)( ( nPos := aScan( afStruc, { |e| trim( upper( aStruc[ i, DBS_NAME ] ) ) == ; +,+( Trim( upper( e[ DBS_NAME ] ) ) } ) ) == 0 .or. ; +,1( ; //if field names are equal then continue by comparing field types +,2( upper( aStruc[ i, DBS_TYPE] ) != afStruc[ nPos, DBS_TYPE ] .or. ; +,3( ;//Avoid comparing field len when autoinc, date, memo, money, raw, rowversion… +,4(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(2,(((

( !(TransFieldName( aStruc[ i ] ) $ ; +,5( "MONEY, LOGICAL, CURDOUBLE, ROWVERSION, RAW, MODTIME, +,6(DOUBLE, BINARY, IMAGE, BLOB, AUTOINC, TIME, TIMESTAMP, DATE, MEMO" ) .and. ; +,7( ( aStruc[ i, 3 ] > afStruc[ nPos, DBS_LEN ] .or. ; +),( aStruc[ i, DBS_DEC ] <> afStruc[ nPos, DBS_DEC ] ) ) +))( +)+( qOut( "Table ", cFileName, "needs struc update on field ", aStruc[ i, DBS_NAME ] ) +)1( isChanged := .t. +)2( +)3( Endif +)4( +)5( End +)6( +)7( if isStrict .and. len( afStruc ) > len( aStruc ) ++,( isChanged := .t. ++)( endif +++( ++1( if !isStrict //if not strict then keep old fields for backward compatibility ++2( ++3( for j := 1 to len( afstruc )//first insert new fields ++4( ++5( i := 0 ; npos := 0 ++6( ++7( while ++i <= len( astruc ) .and. nPos == 0 +1,( +1)( if alltrim( upper( afstruc[ j, DBS_NAME ] ) ) == alltrim( upper( astruc[ i, +1+(DBS_NAME ] ) ) +11( nPos := i +12( endif +13( +14( end +15( +16( if nPos == 0 +17( aadd( astruc, afstruc[ j ] ) +2,( endif +2)( +2+( next j +21( +22( Endif +23( +24(Return isChanged +25( +26(*----------------------------------------------------------------------------------------------------- +27(static Function UpdateTableStruc( cTable, aStruc, cAlias ) +3,(local cAdd := alltrim( Fields2Add( aStruc, (calias)->( dbStruct() ) ) ) +3)(local cDel := alltrim( Fields2Del( astruc, (calias)->( dbStruct() ) ) ) +3+(local cMod := alltrim( Fields2Chn( aStruc, (calias)->( dbstruct() ) ) ) +31(local cErr, cSQL, isOk +32( +33(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(2)(((

(calias)->( dbclosearea() ) +34( cAdd := SQLAlterStm( "ADD", left( cAdd, len( cAdd ) -1 ) ) +35( cDel := SQLAlterStm( "DROP", left( cDel, len( cDel ) -1 ) ) +36( cMod := SqlAlterStm( "ALTER", left( cMod, len( cMod ) -1 ) ) +37( cSQL := "ALTER TABLE " + cTable + cAdd + cDel + cMod +4,( +4)(

if empty( cadd + cdel + cmod ) +4+( +41(

Alert( "Update string could not be determined. Table " + alltrim( cTable ) ) +42( +43( else +44( +45( ExecuteSQL( cSQL ) +46( +47( ENDIF +5,( +5)(return( Nil ) +5+( +51(*----------------------------------------------------------------------------------------------------- +52(Static Function SQLAlterStm( cAction, cColInfo ) +53(local cStr := "" +54(local aParsed := hb_aTokens( cColInfo, ";" ) +55(local a +56(local Elem, cFldType +57( +6,(//if alter colmn then: a[1] -old field name, a[2] -new field name, a[3] -field type, a[4] -field len, a[5] -field dec +6)(//if add colmn then: a[1] new field name, a[2] -field type, a[3] -field length, a[4] -field dec +6+( +61( if empty( cAction ) +62( RETURN cStr +63( Endif +64( +65( if cAction == "DROP" +66( FOR EACH Elem IN aParsed +67( cStr += " DROP " + elem + " " +7,( NEXT +7)( endif +7+( +71( if cAction $ [ADDALTER] +72( FOR EACH Elem IN aParsed +73( +74( a := hb_aTokens( elem, "," ) +75( +76( cFldType := upper( a[ iif( cAction == "ADD", 2, 3 ) ] ) +77( 1,,( cStr += iif( cAction == "ADD", " ADD COLUMN [" + alltrim( a[ 1 ] ) + "]", ; 1,)( " ALTER COLUMN [" + alltrim( a[ 1 ] ) + "] " ) 1,+( 1,1( do case 1,2(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(2+(((

1,3( case cFldType == "CHARACTER" .or. cFldType == "C" 1,4( cstr += iif( cAction == "ADD", ; 1,5( " Char(" + alltrim( a[ 3 ] ),; 1,6( " [" + a[ 2 ] + "] Char( " + alltrim( a[ 4 ] ) ) + ") " 1,7( 1),( case cFldType == "NUMERIC" .or. cFldType == "N" 1))( cstr += iif( cAction == "ADD", ; 1)+( " Numeric(" + alltrim( a[ 3 ] ) +"," + alltrim( a[ 4 ] ),; 1)1( " [" + a[ 2 ] + "] Numeric (" + alltrim( a[ 4 ] ) + "," + a[ 5 ] ) + ") " 1)2( 1)3( case cFldType == "D" 1)4( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Date " 1)5( 1)6( case cFldType == "L" 1)7( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Logical " 1+,( 1+)( case cFldType == "M" 1++( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Memo " 1+1( 1+2( case cFldType == "Y" 1+3( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Money " 1+4( 1+5( case cFldType == "T" .or. cFldType == “TIMESTAMP” 1+6( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " TimeStamp" 1+7( 11,( case left( cFldType, 1 ) == "A" .or. cFldType == “AUTOINC” 11)( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " AutoInc " 11+( 111( case cFldType == "I" .or. cFldType == “INTEGER” 112( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + ; 113( iif( a[ iif( cAction == "ADD", 3, 4 ) ] == "2", " Short ", " Integer " ) 114( 115( case cFldType == "DOUBLE" .or. cFldType == "O" 116( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Double( " +; 117( alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " 12,( 12)( case cFldType == "CURDOUBLE" .or. cFldType == "O" 12+( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " CurDouble( 121(" + ; 122( alltrim( a[ iif( cAction == "ADD", 4, 5 ) ] ) + ") " 123( 124( case "VARCHAR" $ cFldType .or. cFldType == "V" 125( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " VarChar( 126("+; 127( alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " 13,( 13)( case "VARBINARY" $ cFldType .or. cFldType == "Q" 13+(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(21(((

cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Varbinary( 131("+; 132( alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " 133( 134( case "RAW" $ cFldType 135( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Raw (" +; 136( alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " 137( 14,( case "CICHARACTER" $ cFldType 14)( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " CIChar (" +; 14+( alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " 141( 142( case cFldType $ "BLOB,IMAGE,BINARY" 143( cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Blob" 144( 145( otherwise 146( cStr += iif( cAction == "ADD", " ", " [" + a[ 2 ] + "] " ) + cFldType 147( 15,( Endcase 15)( 15+( NEXT 151( 152( Endif 153( 154(RETURN cStr 155( 156(*------------------------------------------------------------------------------------------------------------------------------- 157(static function Fields2Add( aStruc, adbStruc ) 16,(local c := "" 16)(local i 16+(local cFieldName 161( 162( DEFAULT adbStruc := {} 163( 164( for i := 1 to len( aStruc ) 165( 166( cFieldName := upper( alltrim( aStruc[ i, DBS_NAME ] ) ) 167( 17,( if aScan( adbStruc, { |e| cFieldName == upper( alltrim( e[ DBS_NAME ] ) ) } ) == 0 17)( 17+( c += cFieldName + "," + ; 171( aStruc[ i, DBS_TYPE ] + "," + ; 172( alltrim( str( aStruc[ i, DBS_LEN ] ) ) + "," + ; 173( alltrim( str( aStruc[ i, DBS_DEC ] ) ) + ";" 174( 175( endif 176( 177( next i 2,,( 2,)(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(22(((

return c 2,+( 2,1(*------------------------------------------------------------------------------------------------------------------------------- 2,2(static function Fields2Del( astruc, adbStruc ) 2,3(local c := "" 2,4(local i 2,5(local cFieldName 2,6( 2,7( for i := 1 to len( adbStruc ) 2),( 2))( cFieldName := upper( alltrim( adbStruc[ i, DBS_NAME ] ) ) 2)+( 2)1( if aScan( aStruc, { |e| cFieldName == upper( alltrim( e[ DBS_NAME ] ) ) } ) == 0 2)2( 2)3( c += cFieldName + ";" 2)4( 2)5( endif 2)6( 2)7( next i 2+,( 2+)(return c 2++( 2+1(*------------------------------------------------------------------------------------------------------------------------------- 2+2(static function Fields2Chn( aStruc, adbstruc ) 2+3(local cRet := "" 2+4(local i 2+5(local nPos 2+6(local cFieldName 2+7( 21,( for i := 1 to len( aStruc ) 21)( 21+( cFieldName := upper( alltrim( aStruc[ i, DBS_NAME ] ) ) 211( 212( nPos := aScan( adbStruc, { |e| upper( alltrim( e[ DBS_NAME ] ) ) == cFieldName } ) 213( 214( if nPos != 0 .and. ( upper( aStruc[ i, DBS_TYPE] ) <> adbStruc[ nPos, DBS_TYPE ] .or. ; 215( ;//don't compare field length for integer, short, date, time, binary, memo, AutoInc… 216( ( !(TransFieldName( aStruc[ i ] ) $ ; 217( "MONEY, LOGICAL, CURDOUBLE, ROWVERSION, RAW, MODTIME, 22,(DOUBLE, BINARY, IMAGE, BLOB, AUTOINC, TIME, TIMESTAMP, DATE, MEMO" ) .and. ; 22)( ( aStruc[ i, DBS_LEN ] > adbStruc[ nPos, DBS_LEN ] .or. ; 22+( aStruc[ i, DBS_DEC ] <> adbStruc[ nPos, DBS_DEC ] ) ) ) 221( 222( cRet += adbStruc[ nPos, DBS_NAME ] + "," + ; 223( aStruc[ i, DBS_NAME ] + "," + ; 224( aStruc[ i, DBS_TYPE ] + "," + ; 225( alltrim( str( aStruc[ i, DBS_LEN ] ) ) + "," + ; 226( alltrim( str( aStruc[ i, DBS_DEC ] ) ) +";" 227( 23,(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(23(((

endif 23)( 23+( next i 231( 232(return cRet 233( 234(*------------------------------------------------------------------------------------------------------------------------------- 235(static function showStructure() 236(local aFiles := GetTableStructures() 237(local cFileName, cFileType, aStruc 24,(local ctable, cext 24)(local i 24+( 241( for i := 1 to len( aFiles ) 242( 243( cFileName := SplitTableName( afiles[ i, 1 ],, @cFileType ) 244( hb_FNameSplit( cFileName,,@cTable, @cExt ) 245( 246( AdsSetFileType( cFileType ) 247( 25,( Dbusearea( .t. , "ADS", cTable, cTable, .f. ) 25)( 25+( aStruc := ( cTable )->( dbStruct() ) 251( 252( ( cTable )->( dbclosearea() ) 253( 254( qOut( "Structure of table ", cFileName ) 255( aEval( aStruc, { |e| qout( e[ DBS_NAME ], e[ DBS_TYPE ], e[ DBS_LEN ], e[ DBS_DEC ] ) } ) 256( 257( wait "any key to continue" 26,( 26)( next 26+( 261(return Nil 262( 263(*------------------------------------------------------------------------------------------------------------------------------- 264(static function ExecuteSQL( cSql ) 265(local isOk := .f. 266( 267( if AdsCreateSQLStatement(, ADS_ADT) 27,( isOk := AdsExecuteSQLDirect( cSQL ) 27)( endif 27+( 271( AdsCacheOpenCursors( 0 ) 272( 273( if !isOk 274( Alert( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) ) 275( qOut( cSQL ) 276( endif 277(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(24(((

3,,(return nil 3,)( 3,+(*------------------------------------------------------------------------------------------------------------------------------- 3,1(//translate single characters returned by the RDD for some field types 3,2(//to full word field types 3,3(static function TransFieldName( aField ) 3,4(local cFldType := upper( aField[ DBS_TYPE ] ) 3,5( 3,6( do case 3,7( case cFldType == "Y" 3),( return "MONEY" 3))( 3)+( case cFldType == "C" 3)1( return "CHARACTER" 3)2( 3)3( case cFldType == "N" 3)4( return "NUMERIC" 3)5( 3)6( case cFldType == "M" 3)7( return "MEMO" 3+,( 3+)( case cFldType == "V" 3++( return "VARCHAR" 3+1( 3+2( case cFldType == "Q" 3+3( return "VARBINARY" 3+4( 3+5( case cFldType == "I" 3+6( return iif( aField[ DBS_DEC ] == 2, "SHORT", "INTEGER" ) 3+7( 31,( case cFldType == "D" 31)( return "DATE" 31+( 311( case cFldType == "L" 312( return "LOGICAL" 313( 314( endcase 315( 316(return cFldType 317( *------------------------------------------------------------------------------------------------------------------------------- >%294$)"-*&)110$0"#*.4#4*.)(#)%"4$B*<0$,)%",G*

Updating existing DD using another DD as source may be done using the Data Dictionary Differentiator available in Arc. The Data Dictionary Differentiator works by comparing the metadata stored in two Advantage Data Dictionaries. An SQL script can then be generated that

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(25(((

contains a mixture of SQL Data Definitions statements and system procedure calls to make one data dictionary the same as the other. To start the Data Dictionary Differentiator, select Database |Compare Data Dictionaries in Advantage Data Architect. The differentiator does not compare data in the tables; it only compares the metadata stored in the data dictionary.

After starting the Data Dictionary Differentiator, select the old data dictionary as the first dictionary and the new data dictionary as the second dictionary. After clicking OK you will be prompted to log into both data dictionaries as the Administrative User, AdsSys. Once you have successfully authenticated into both dictionaries the two data dictionaries will be compared and a graphical representation of the differences will be displayed.

On the graphical display objects with a plus sign in front of them are only contained in that data dictionary, while a corresponding object with a negative sign will be shown in the other data dictionary to signify that object does not exist in this data dictionary. The properties of a single object can be displayed by pressing View. All other objects exist in both dictionaries; however, one or more of their properties are different. By clicking Diff the properties of the object in both data dictionaries will be shown with all properties that are different highlighted in red.

SQL Scripts are generated by clicking Write Script. An SQL script may be created for a single object, a subset of objects such as tables, or an entire data dictionary. This script should be treated as a template that will most likely need to be customized. >$04#)"-*4*"0E*..*1$%2*0H),#)"-*1$00*#4570,*

We have now seen how to deliver an Harbour application that creates the necessary tables and index and binds them to a new Advantage Data Dictionary. We have also seen how to update the structure of tables when we need to deliver an update of the application that requires changes to the structure of the existing tables. Occasionally the need is to create a new Data Dictionary to contain all current tables used by the application. To accomplish this task we may proceed in two different ways:

1. Using Harbour Directory() function we can load into an array all tables of type .dbf and adt. Then using AdsDDCreate() ACE function we create an empty DD. After adding the necessary users and changing database properties as needed, we would then proceed to use ACE function AdsDDAddTable() to bind each table to the newly created DD.

2. Store the tables and index names on an array. Each time a new update of the application is delivered, the array would contain an updated list of the tables and indexes.

3. Using ACE function AdsDDDeployDatabase(). AdsDDDeployDatabase eases deployment of applications using Advantage Data Dictionaries. Specifically, a data dictionary can be defined and then deployed to an existing database consisting of a directory containing free tables and/or indexes. Those free tables and indexes will then be incorporated into the deployed data dictionary as defined within the data dictionary.

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(26(((

:&<4"#4-0*I9#)2)J0&*K)7#0$,*

L%$0*%"*',)"-*;F/*E)#C*:.;*

SQL access through ACE is accomplished using connection handles, cursor handles, and statement handles. Most of this work has been encapsulated by the RDD and ACE wrapper functions developers, thus rendering much of the handles information somewhat abstracted. In fact, handle information is kept on static variables inside ACE wrapper functions c source. As you read ACE help file you might be struck to find that all APIs documented handle parameters is omitted when executing any of the Harbour wrapper functions. This in only due to how the Harbour developers chose to implement access to the ACE API.

The first step in executing an SQL stetament is to connect to the Data Dictionary on either the Advantage Database Server (ADS) using a remote or Internet connection or to the Advantage Local Server (ALS). This is done using API AdsConnect60(). AdsConnect60() will store the connection handle internally and will be transparently supplied to any API that requires the handle. AdsConnect60() requires the DD path and file name, the type of connection, the user name and password:

AdsConnect60( cpath + "test_dd.add", ADS_LOCAL_SERVER, "adssys", "password" )

AdsConnect60() will return .t. upon a successful connection. Otherwise it will return a value of .f.

Next you will need to create an SQL statement handle using API AdsCreateSqlStatement(). The wrapper function receives two optional parameters. The first is work area name. If omitted, “adssql” will be used as default. The second optional parameter is used to set the ADS table type to be created.

The choice of which API to use next will depend on the type of SQL statement being executed. If executing a parameterized query, then a combination of AdsPrepareSql() and AdsExecuteSql() must be used. In all our previous examples we have used AdsExecuteSqlDirect() which is a combination of AdsPrepareSql() and AdsExecuteSql() as this API is a more direct approach to executing an SQL statement when there are no parameters involved. Refer to ACE help files for more information on parameterized queries as this discussion is beyond the scope of this writing.

The Harbour functions below can be used as a template to execute any SQL statement:

/*------------------------------------------------------------------------------------------------------------------------------- )(RCB – +(cScript – Text with sql statement to be executed 1(nTbltype –Table type for returning cursor (ADS_ADT, ADS_VFP…) 2(lCursor – True will return a cursor with sql result set as a work area. False will return an array 3(lShowprogress – will display progress of sql statement execution. 4(-------------------------------------------------------------------------------------------------------------------------------*/ 5(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(27(((

6(static function ExecuteSQL( cScript, nTblType, lCursor, lShowProgress ) 7(local cArea := "SqlArea" ),(local nCount := 1 ))(local a := {} )+(local isGood := .f. )1(local nTmp )2( )3(DEFAULT nTblType := ADS_ADT, lCursor := .f., lShowProgress := .f. )4( )5(

if !empty( cScript ) )6( )7( if Select( cArea ) > 0 ; ( cArea )->( dbclosearea() ) ;endif +,(

AdsCacheOpenCursors( 0 ) +)( dbSelectArea(0) ++(

+1( IF !ADSCreateSQLStatement( cArea, nTblType ) +2(

+3(Alert ( "AdsCreateSqlStatement() failed with error "+ Str( ADSGetLastError() ) ) +4(

+5(Else +6(

+7(if lShowProgress 1,(

1)( Register_callBack( { | nPercent | nCount++,; 1+( iif( ncount * nPercent > 100, nCount := 1, ),; 11( qOut( alltrim( Str( nCount * nPercent ) ) + "%" ) } ) 12( 13(

isGood := AdsExecuteSQLDirect( cScript ) 14( 15(

Unregister_CallBack() 16( 17( Else 2,( isGood := AdsExecuteSQLDirect( cScript ) 2)( endif 2+( 21( if !isGood 22( 23(

Alert ( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) ) 24( 25(

Else 26( 27(

if lCursor ;Return( cArea ) ;endif 3,( a := GetCursorContents( cArea ) 3)( 3+(

endif 31(endif 32(

33(endif 34(

35( AdsCacheOpenCursors( 0 ) 36(if Select( cArea ) > 0 ; ( cArea )->( dbclosearea() ) ;endif 37(

4,(return a 4)( 4+(*------------------------------------------------------------------------------------------------------------------------------- 41(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(3,(((

static function GetCursorContents( cArea ) 42(local aStruc 43(local a := {} 44(local nCount := 1 45(local i, xTmp 46( 47( if Select( cArea ) > 0 5,( TRY 5)( aStruc := (cArea)->( dbStruct() ) 5+( a := array( (cArea)->( lastrec() ) ) 51( 52( while !(cArea)->( eof() ) 53( 54( a[ nCount ] := array( len( aStruc ) ) //{} 55( afill( a[nCount], " " ) 56( 57( for i := 1 to len( aStruc ) 6,( a[ nCount, i ]:= iif( ( xTmp := (cArea)->( fieldGet( i ) ) ) == Nil, Blank( 6)(aStruc[ i, DBS_TYPE] ), xTmp ) 6+( next i 61( 62( nCount++ 63( (cArea)->( dbSkip() ) 64( 65( End 66( 67( CATCH 7,( a := {} 7)( END 7+( 71( endif 72( 73( aSize( a, nCount-1 ) 74( 75(Return a 76( 77(*------------------------------------------------------------------------------------------------------------------------------- ),,(Function Blank( xValue ) ),)( ),+( do Case ),1( Case xValue == "C" ),2( Retur( "" ) ),3( case xValue == "N" ),4( Retur( 0 ) ),5( case xValue == "D" ),6( Retur( ctod( "" ) ) ),7( EndCase )),( )))(Return 0 ))+( ))1( ))2(*------------------------------------------------------------------------------------------------------- ))3(#pragma BEGINDUMP ))4(#include <windows.h> ))5( ))6(#include "ace.h" ))7(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(3)(((

#include "hbapi.h" )+,(#include "hbvm.h" )+)(#include "hbapiitm.h" )++( )+1(unsigned long __stdcall ShowPercentage( UNSIGNED16 usPercentDone, UNSIGNED32 ulCallbackID ); )+2( )+3(static PHB_ITEM pBlock; )+4( )+5(//---------------------------------------------------------------------------// )+6(HB_FUNC( REGISTER_CALLBACK ) )+7({ )1,( pBlock = hb_itemParam( 1 ); )1)( hb_retni(AdsRegisterCallbackFunction( ShowPercentage, 1 ) ); )1+(} )11( )12(//---------------------------------------------------------------------------// )13(HB_FUNC( UNREGISTER_CALLBACK ) )14({ )15( AdsClearCallbackFunction(); )16( )17( if( pBlock ) )2,( { )2)( hb_itemRelease( pBlock ); )2+( } )21(} )22( )23(//---------------------------------------------------------------------------// )24(unsigned long __stdcall ShowPercentage( UNSIGNED16 usPercentDone, UNSIGNED32 ulCallbackID ) )25(//UNSIGNED32 WINAPI ShowPercentage( UNSIGNED16 usPercentDone, UNSIGNED32 ulCallbackID ) )26({ )27( PHB_ITEM pPercent = hb_itemPutNI( NULL, usPercentDone ); )3,( )3)( if( pPercent && pBlock) )3+( { )31( hb_vmEvalBlockV( pBlock, 1, pPercent ); )32( hb_itemRelease( pPercent ); )33( } )34( )35( return 0; )36(} )37( )4,(#pragma ENDDUMP )4)( )4+(*------------------------------------------------------------------------------------------------------- )41( :.;*64(M'9*'#)7)#B*

( *

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(3+(((

K4,#*A0H#*;04$(C*NKA;O*)"&0H0,*

FTS indexes are special ADS indexes to allow high-speed searches on any character field including memos and works on all table types except for dbf/ntx file pairs. An FTS index is built on a single text field but multiple FTS indexes are permitted for field types “CHARACTER”, “MEMO”, “RAW”, and “IMAGE”. An FTS index may be used as part of an expression on an Advantage Optimized Filter (AOF). FTS indexes may be built either using ARC, or with the SQL expression “CREATE INDEX ON”, or with the ACE function AdsCreateFTSIndex(). An FTS index is always made up of all words in the character field but may be customized by using a number of options at creation time. These options include: ])")4'4*^%$&*12"/#K: Words shorter than the length specified in this option will not be included on the index. The default for this option is 3. ]6P)4'4*^%$&*12"/#K: Words longer than the length specified on this option will not be included on the index. The default for this option is 30. 02:)4)#2$.: Characters that defined word boundaries. As default it is set to ASCII code 32, 8, 9, 10,11, 12, and 13. That is -blank spaces, back space, tab, newline, vertical tab, form feed and carriage return. 0$%<*BK6$6(#2$.: Characters to be ignored by the FTS engine. This option defaults to double quotes, single quotes, and back quotes. B%"&)#)%"6:*0$%<*BK6$6(#2$.: These are special type of drop characters. They are dropped only if they are found at the beginning or end of a word. M%).2*^%$&.\: These are words that should be ignored and not to be included on the index. ADS defaults this option to: about after all also and another any are because been before being between both but came can come could did does each else for from get got had has have her here him himself his how into its just like make many might more most much must never now only other our out over said same see should since some still such take than that the their them then there these they this those through too under use very want was way well were what when where which while who will with would you your

B6.2*>2".)#)?2*Q<#)%": By default the index is created as case insensitive. With this option you may instead create a case sensitive index. *_22<*>(%$2*Q<#%": This option specifies whether or not ADS should keep track of the word counts in the index. Below we show Harbour code that may be used to create an FTS index: //--------------------------------------------------------------------------------------------------------------- )(static Function CreateFTSIndexUsingACE() +(local cFileName //Name of file for new index order. null to use default 1(local cTag := "notes" //Name of the new tag. 2(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(31(((

local cField := "notes" //Field name for fts index to be created. 3(local nPageSize //any non numeric value defaults to ADS_DEFAULT 4(local nMinWordLen := 3 //any non numeric value defaults to 3 5(local nMaxWordLen := 30 //any non numeric value defaults to 30 6(local lUseDefaultDelim := .t. //any non logical value defaults to .t. 7(local cDelimiters //if .t., then this list will be used in addition to the default delimiters ),(local lUseDefaultNoise := .t. //any non logical value defaults to .t. ))(local cNoiseWords //Noise words to be used in addition to default )+(local lUseDefaultDrop //any non logical value defaults to .t. )1(local cDropChars //drop chars to be used in addition to default )2(local lUseDefaultConditionals //any non logical value defaults to .t. )3(local cConditionalChars //list of conditional chars to be used in addtion to default )4(local cReserved1 //reserved parm for future use )5(local cReserved2 //reserved parm for future use )6(local nOptions //a bit field for defining the options for the full text search )7( //index creation. Defaults to ADS_DEFAULT. Options are: +,( //ADS_COMPOUND, ADS_FTS_FIXED, ADS_FTS_CASE_SENSITIVE, +)(

//ADS_FTS_KEEP_SCORE, ADS_FTS_PROTECT_NUMBERS ++(local cArea := "Customers" +1(local nerr +2( +3( dbUseArea( .t.,, cArea, cArea, .f., .f. ) +4( AdsCreateFTSIndex( cFileName, ; +5( cTag, ; +6( cField, ; +7( nPageSize, ; 1,( nMinWordLen, ; 1)( nMaxWordLen,; 1+( lUseDefaultDelim,; 11( cDelimiters,; 12( lUseDefaultNoise,; 13( cNoiseWords,; 14( lUseDefaultDrop,; 15( cDropChars,; 16( lUseDefaultConditionals,; 17( cConditionalChars,; 2,( cReserved1,; 2)( cReserved2,; 2+( nOptions ) 21( 22( if ( nErr := AdsGetLastError() ) != 0 23( 24( Alert( "AdsCreateFTSIndex failed with errror: " + str( nErr ) ) 25( 26( endif 27( 3,( ( cArea )->( dbcloseArea() ) 3)( 3+(Return nil 31( 32(//--------------------------------------------------------------------------------------------------------------- 33(

Below we show (x)harbor code to create an FTS index using SQL statement CREATE INDEX ON:

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(32(((

//--------------------------------------------------------------------------------------------------------------- )(static Function CreateFTSIndexUsingSQL() +(local cSql := "CREATE INDEX NOTES ON " +; 1( "customers ( NOTES ) CONTENT MIN WORD 3 MAX WORD 30 "+ ; 2( "NOISE 'about after all also an and another any are as at be because been before being "+; 3( "between both but by came can come could did do does each else for from get got had has "+; 4( "have he her here him himself his how if in into is it its just like make many me "+; 5( "might more most much must my never now of on only or other our out over re said same see "+; 6( "should since so some still such take than that the their them then there these they this "+; 7( "those through to too under up use very want was way we well were what when where which while "+; ),( "who will with would you your'"+; ))( "DELIMITERS "+ chr(32)+chr(8)+chr(9)+chr(10)+chr(11)+chr(12)+chr(13)+; )+( "DROPCHARS '`" + '"' + ; )1( "CONDITIONALS ',.?!;:@#$%^&()-_' PAGESIZE 512 IN FILE "+ '"customers.adi";' + "'' ); " )2( )3( ExecuteSQL( cSql ) )4( )5(return nil )6( )7(//--------------------------------------------------------------------------------------------------------------- +,(static function ExecuteSQL( cScript, cRDD ) +)(local cArea, e ++(local isGood := .f. +1( +2(DEFAULT cRDD := ADS_ADT +3( +4( if !empty( cScript ) +5( +6(

AdsCacheOpenCursors( 0 ) +7(dbSelectArea(0) 1,( if Select( "sqlarea" ) > 0 ;SQLArea -> ( DBCLOSEAREA() ) ;endif 1)(

1+( IF !ADSCreateSQLStatement("SQLarea", cRDD) //.or. !ADSVerifySQL( cScript ) 11( 12( TRY 13( SQLArea->( DBCLOSEAREA() ) 14( CATCH e 15( END 16( 17( Alert ( "AdsCreateSqlStatement() failed with error "+ Str( ADSGetLastError() ) ) 2,( 2)( Elseif !( isGood := AdsExecuteSQLDirect( cScript ) ) 2+( 21( Alert ( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) ) 22( 23( endif 24( 25( endif 26( 27( AdsCacheOpenCursors( 0 ) 3,( 3)( if Select( "sqlarea" ) > 0 ;SQLArea -> ( DBCLOSEAREA() ) ;endif 3+( 31(return nil 32(

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(33(((

Now that we have an FTS, it is time to perform some basic searches. Searching on the FTS can be performed with an SQL sentence or using an AOF. The SQL sentence used follows this format: “SELECT * FROM cTable WHERE CONTAINS( cFieldName, “’knowledge and complicity’”). The scalar function contains() integrates seamlessly with other Advantage and is used to specify the search condition and the field to be searched. It can be used inside SQL statements and in traditional xbase-style record filters. The sample above [CONTAINS( cfieldName, “’knowledge and complicity’” )], would return records where the given field contains both words “knowledge” and “complicity”.

The SQL CONTAINS() scalar function can be combined in the statement with other SQL conditions. FTS use intuitive search conditions consisting of words and phrases optionally combined with logical operators (and, or, not, near). The search conditions are used in SQL WHERE clauses and record filters to produce a result set.

It is possible to perform searches on non-indexed data, but that would require a physical search of the record data and can be much slower. FTS indexes are not supported with DBF/NTX table types. As a result, it is possible to perform full text searches on traditional Clipper tables, but the searches will not be optimized.

*

:&,P0$$84&#*0$$%$*7%-*

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(34(((

:>=QR*34$5%'$*:S!*

*** In this section I plan document this list of ace functions **** AdsTestRecLocs() AdsSetFileType() AdsSetServerType() AdsSetDateFormat() AdsSetEpoch() AdsApplicationExit() AdsIsServerLoaded() AdsGetConnectionType() AdsUnlockRecord() AdsGetTableConType() AdsGetServerTime() AdsIsTableLocked() AdsIsRecordLocked() AdsLocking() AdsRightsCheck() AdsSetCharType() AdsGetTableCharType() AdsSetDefault() AdsSetDefault() AdsSetSearchPath() AdsSetDeleted() AdsSetExact() AdsBlob2File() AdsFile2Blob() AdsKeyNo() AdsKeyCount() AdsAddCustomKey() AdsDeleteCustomKey() AdsClearAof() AdsEvalAof() AdsGetTableAlias() AdsGetAof() AdsGetAofOptLevel() AdsGetAofNoOpt() AdsIsRecordInAof() AdsIsRecordValid() AdsRefreshAof() AdsSetAof() AdsGetFilter() AdsEnableEncryption()

!"#$%&$'())*(+,),( !"#$%&'()%*"'+(,'-((

-./$(0(35(((

AdsDisableEncryption() AdsEncryptTable() AdsDeCryptTable() AdsEncryptRecord() AdsDecryptRecord() AdsIsEncryptionEnabled() AdsIsRecordEncrypted() AdsIsTableEncrypted() AdsConnect() AdsDisconnect() AdsCreateSqlStatement() AdsExecuteSqlDirect() AdsSetBinary() AdsPrepareSql() … **Talk about how ADS uses a deleted() index and faster browsing: