Advantage Database and SQL

[Jay Hill]

I am currently using Advantage Database 10 via DBF/NTX files for my application. My very basic question is:

What is the most simplistic method to allow another application (Visual C#) to query/modify/update some of the Advantage Data using SQL? Is it to simply create a Data Dictionary in Advantage to house my DBF/NTX files? at that point SQL can be used from an external app? or do the DBF/NTX files also need to be converted to ADT files?

[reinaldocrespo]

Jay;

When the index files are not compounded indexes, as is the case with DBF/NTX, then you should create a data dictionary. Inside the data dictionary metadata is stored with information that relates any and all .ntx files to its .dbf files. Have the Visual basic, PHP, or any other ADS enabled client connect to the data dictionary and that is all. When tables are used from those other clients ADS will know the indexes associated to any DBF and will use them accordingly.

I suggest you stop using DBFNTX RDD on your harbour app and instead use ADS RDD and set your default tables to ADS_NTX. You will probably have to reindex the first time you deploy your app using ADSNTX RDD but no other change is needed. The reason I suggest this is because there seems to be some subtle differences on the way these two RDDs maintain NTX indexes and mixing DBFNTX on some clients with ADS_NTX on other clients will surely cause index corruption.

As a side note; when I first started using ADS back in 2002, I continued using DBFNTX. My main application was a FWH + xharbour + bcc using ADS_NTX RDD. Other clients were PHP, Delphi and C++ Builder all using ADS ACE or TADS descendent classes. It worked so well it took me forever to invest the time to move to purely ADT tables.

Below is some code showing how to do a few tasks using ADS RDD that you might find helpful:

Code: Select all  Expand view

//----------------------------------------------------------------------------
//By Reinaldo Crespo-Bazan --reinaldo.crespo@gmail.com
//
//This (x)harbour console mode app shows how to create a few tables of different types.
//How to create an Advantage Data Dictionary and add the newly created
//tables the data dictionary file.
//I'm using various table types using ADS RDDs for DBF/NTX, DBF/VFP, ADT/ADI
//
//After that it atttempts to connect to the dd, create a new table and then
//open the new table as a free table.
//In short- it creates tables and dd, opens dd bound tables and free tables
//at the same time.
//
//The sample code uses pure rdds + some ACE calls.
//No GUI dependency as only a console mode app is being built, which should
//build just the same under Windows as well as under Linux.
//
#include "ads.ch"

#xcommand DEFAULT <uVar1> := <uVal1> ;
                [, <uVarN> := <uValN> ] => ;
                  <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;;
                [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ]

REQUEST DBFCDX
REQUEST ADS          //Make sure rddads gets linked to our app.
REQUEST hb_gt_win    //needed for console mode app.

//Static variable cPath will contain the path where tables and DD will be created.
//After connecting to an existing DD, there is no need to use the file path as the
//DD saves the relative path of each table as part of its metadata.
STATIC cPath


//----------------------------------------------------------------------------
FUNCTION Main()

   LOCAL afiles

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

   rddRegister( "ADS", 1 )
   
   rddSetDefault( "ADS" )

   adsSetServerType( ADS_LOCAL_SERVER )

   //NON-compatible locking mode.  If other NON-ADS clients are going to
   //to work on the same tables, then make sure to use compatible locking mode.
   AdsLocking( .t. )  
    AdsRightsCheck( 0 )
   
   //Path for existing .dbf tables maybe sent as a parameter
   //on the command line.  This is just one idea.  It could be saved
   //on a .ini file or hardcoded.
   cPath   := hb_ArgV( 1 )
   
   //in case no path was sent on command line, assume current path.
   DEFAULT cpath := ""  
   
   //sanitize cPath contents.
   IF !EMPTY( cPath ) .and. RIGHT( cPath, 1 ) != '\\'    ;cPath += '\\'  ;ENDIF
   
   //if you need to create some new tables other than existing tables look
   //at function CreateTables().  CreateTables shows how to create
   //adt tables, vfp tables, and dbf-ntx tables.
   //
   //CreateTables() creates an array with table names and structure for each.
   //It then creates the tables.  After being created they will be added to a newly
   //created data dictionary using function CreateDictionary().
   //If your .dbf tables are alreay present, then you won't need to execute
   //CreateTables, instead you could simply execute a Directory() harbour
   //function to retreive existing table names.  By storing the names of these tables
   //into afiles variable, you can then execute CreateDictionary() in exactly the same
   //manner.
   afiles := CreateTables()
   
   IF !EMPTY( afiles )     ;CreateDictionary( afiles )     ;ENDIF

   //now that we have a dd created, we will attempt to connect to it and
   //use the tables as dd bound tables.

    IF !AdsConnect60( cPath + "test_dd.add", ADS_LOCAL_SERVER, "user1", "password1" )
   
       Alert( "Cannot connect to test_dd" + Str( AdsGetLastError() ) )
      QUIT

   ENDIF


   //Now we create a dbfcdx table to be used as a free table.
   CreateAndUseFreeTable()
   
   //if table already exists then this function shows how to open and
   //browse an existing free table.
   OpenExistingFreeTable()

       
RETURN NIL

//--------------------------------------------------------------------------------------
//function CreateTables() will create three tables using stnd harbour func dbCreate
//Each table structure is saved on an array following the std returned by dbStruct()
//Each element on the array is an array describing the field.  Thus, the array
//for a table with 3 fields would look like this:
//aStructure := { { cFieldName1, cFieldType1, nFldWidth, nDecimals },;
//                { cFieldName2, cFieldType2, nFldWidth, nDecimals },;
//                { cFieldName3, cFieldType3, nFldWidth, nDecimals } }
//
STATIC FUNCTION CreateTables()
   LOCAL cAlias := "MyAlias"
   LOCAL afiles := {}
   LOCAL cFileName, e

    //---------------------- customers.adt --------------------------------------------
   LOCAL aStruc := { { "cust_id"    , "C", 10, 0 },;
                { "Sequence"        , "AutoInc", 07, 0 },;      //ADT extended field type autoincrement
                { "customer_name"   , "C", 25, 0 },;
                { "Start_date"      , "TimeStamp", 01, 0 },;    //ADT extended field type
                { "Notes"           , "M", 10, 0 } }
                       
    AdsSetFileType( ADS_ADT )  

    TRY    
        dbCreate( ( cFileName := cPath + "customers.adt" ), aStruc,, .t., cAlias )

        ( cAlias )->( OrdCreate( cPath + "customers", "cust_id", "cust_id" ) )
        ( cAlias )->( OrdCreate( cPath + "customers", "Start_date", "Start_date" ) )
        ( cAlias )->( dbclosearea() )

        AADD( afiles, { cFileName, "customers.adi" } )

   
    //------------------------ sales.dbf -------------------------------------------------
        aStruc := { { "cust_id" , "C", 10, 0 },;
                { "invoice"     , "C", 15, 0 },;
                { "s_date"      , "TimeStamp", 01, 0 },;    //VFP extended field type
                { "item_id"     , "C", 15, 0 },;
                { "Units"       , "N", 03, 0 },;
                { "Price"       , "Money", 09, 2 },;        //VFP extended field type
                { "Notes"       , "M", 10, 0 } }
                   
        AdsSetFileType( ADS_VFP )

        dbCreate( ( cFileName := cPath + "sales.dbf" ), aStruc,, .t., cAlias )

        ( cAlias )->( OrdCreate( cPath + "sales", "invoice", "invoice" ) )
        ( cAlias )->( OrdCreate( cPath + "sales", "cust_id", "cust_id" ) )
        ( cAlias )->( dbclosearea() )

        aadd( afiles, { cFileName, "sales.cdx" } )



    //------------------------ items.dbf -------------------------------------------------
        aStruc := { { "item_id"     , "C", 15, 0 },;
                { "Desc"    , "C", 25, 0 },;
                { "Price"   , "N", 07, 2 } }
                   
        AdsSetFileType( ADS_NTX )

        dbCreate( ( cFileName := cPath + "items.dbf" ), aStruc,, .t., cAlias )
        ( cAlias )->( OrdCreate( cPath + "itm_id",, "item_id" ) )
        ( cAlias )->( OrdCreate( cPath + "itm_desc",, "Desc" ) )
        ( cAlias )->( dbclosearea() )

        aadd( afiles, { cFileName, "itm_id.ntx;itm_desc.ntx" } )

    CATCH e
   
        ShowError( cFileName, e )
        afiles := {}
       
    END

RETURN afiles



/*--------------------------------------------------------------------------------------------------------
CreateDictionary creates an Advantage Data Dictionary based on already existing tables
--------------------------------------------------------------------------------------------------------*/
STATIC FUNCTION CreateDictionary( afiles )
   LOCAL cExt AS CHARACTER := ""
   LOCAL cDD := cPath + "test_dd.add"

   IF !ADSDDCREATE( cDD,, "Sample data dictinoary" )

      ALERT( "AdsCreate() of " + cDD + " failed. Error:" + Str( AdsGetLastError() ) )
      RETURN NIL

   ENDIF
   
   //Calling ACE function AdsDDSetDatabaseProperty()
   AdsDDSetDatabaseProperty( ADS_DD_ENABLE_INTERNET, .t. )
   AdsDDSetDatabaseProperty( ADS_DD_INTERNET_SECURITY_LEVEL, ADS_DD_LEVEL_2 )
   AdsDDSetDatabaseProperty( ADS_DD_DEFAULT_TABLE_PATH, cPath )
   AdsDDSetDatabaseProperty( ADS_DD_LOG_IN_REQUIRED, .t. )


   //ACE Function ADsDDCreateUser() creates a dd user.
   //When a new dd is created user ADSSYS is auto-created as the dd administrator
   //it is a good idea to create other users to limit access and permissions.
   //
   AdsDDCreateUser( , "user1", "password1", "User named userd1 with password password1" )
   AdsDDCreateUser( , "user2", "password2", "Description of user2" )
   AdsDDCreateUser( , "user3", "password3", "Optional description of user3" )

   //array aFiles contains the names of the tables to be added to the newly created
   //data dictionary.  
   //element 1 on the array is the table name,
   //element 2 on the array is the index file name.
   //Since .dbf tables could be vfp, cdx, or ntx type tables,
   //we are using the extension on the index file name to "guess" the .dbf
   //file type.  If this is already known beforehand, then the code below
   //could be much simpler.  I'm using AEVAL only because I love it; albeit it is
   //more cryptic.  The same can be done on a FOR EACH (FOR/REPEAT/WHILE) loop
    AEVAL( aFiles, ;
        { |e| hb_FNameSplit( iif( len( e ) > 1, e[ 2 ], e[ 1 ] ), , , @cExt ), ;
            AdsSetFiletype( iif( lower( cExt ) $ ".adt,.adi", ADS_ADT, ;
                        iif( lower( cExt ) == ".ntx", ADS_NTX, ADS_VFP ) ) ),;
            addfiletoDD( e, cPath ), ;
            SetTableProp( e ), ;
            QOut( "Adding table to DD", ;    //show some progress information
                        e[ 1 ], ;
                        AdsSetFileType(), ;
                        cExt, ;
                        ADSGetLastError() ) } )

   //assign the password "password" to the ADSSYS user of the DD.  Just as default.
    AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "password" )

    Wait

return Nil

//-------------------------------------------------------------------------------------------------------
//This is the code that adds the table to the DD.
STATIC FUNCTION AddFiletoDD( aFile, cPath )
   LOCAL cIndexs := ""

   IF LEN( afile ) > 1 .AND. LEFT( afile[ 2 ], 1 ) <> "."
   
        AEVAL( afile, { |e| cIndexs += e +";" }, 2 )
        cIndexs := LEFT( cIndexs, LEN( cIndexs ) -1 )
       
   ENDIF

RETURN( AdsDDAddTable( left( afile[ 1 ], at(".", afile[1] )-1 ), ;
                cPath + aFile[ 1 ], ;
                cIndexs ) )

//--------------------------------------------------------------------------------------------------------
//This function assigns certain properties to all tables.  
//The only table property I'm assigning is the auto_create property.
//Many others could also be applied.  Look at ads.h include file for
//more constants.
STATIC FUNCTION SetTableProp( aFile )
   LOCAL cFileName

   hb_FNameSplit( afile[ 1 ],, @cFileName )

   AdsDDSetTableProperty( cFileName, ADS_DD_TABLE_AUTO_CREATE, .t. )

//  AdsDDSetTableProperty( cFileName, ADS_DD_TABLE_ENCRYPTION, .t. )

RETURN NIL


*-------------------------------------------------------------------------------------------------------------------------------
STATIC FUNCTION ShowError( cMsg, oErr )
   LOCAL cErr

   cErr := STR( AdsGetLastError() )
   
   ALERT( "Error : " + ALLTRIM( cErr ) + " " + ;
            cMsg + " " + oErr:Subsystem + " " + ;
            STR( oErr:subCode ) + " " + oErr:operation + " " + oErr:description )

   dbCloseAll()
   
RETURN NIL

//-----------------------------------------------------------------------------------------------------
//create, open and browse a free table.  This is only to show how free tables can co-exist
//with dd bound tables.  It is useful for temp tables among other uses.
STATIC FUNCTION CreateAndUseFreeTable()
   LOCAL aStruct := { ;
               { "CHARACTER", "C", 25, 0 }, ;
               { "NUMERIC"  , "N",  8, 0 }, ;
               { "DOUBLE"   , "N",  8, 2 }, ;
               { "DATE"     , "D",  8, 0 }, ;
               { "LOGICAL"  , "L",  1, 0 }, ;
               { "MEMO1"    , "M", 10, 0 }, ;
               { "MEMO2"    , "M", 10, 0 }  ;
            }

      DbCreate( "testdbf", aStruct, "DBFCDX", .T., "MYALIAS" )

      Browse()

RETURN NIL


//-----------------------------------------------------------------------------------------------------
STATIC FUNCTION OpenExistingFreeTable()

   DbUseArea( .T., "DBFCDX", "testdbf", "TestAlias", .T., .F. )
   Browse()
   
RETURN NIL


//-----------------------------------------------------------------------------------------------------
 

... and yes, the other clients (C#, C++, PHP...) as well as your own fwh app may use pure SQL against your DBF/NTX tables. Hope that helps.
Reinaldo.

[Jay Hill]

Thank You Very Much Reinaldo for that information! That helps me greatly. Can I ask you a couple of very basic follow-up questions?

1) How would I set up your afiles array to pass 1 dbf and multiple ntx's to the CreateDictionary()? because the names of my dbf's aren't related to the ntx's. Example...
Customer.dbf
Custname.ntx
Custnbr.ntx
Custid.ntx
Payments.dbf
Pmttype.ntx
Pmtdate.ntx

2) How do I link in the ADS.ch? I have never "included" anything in Harbour yet. The following is my current .hbp file:
MATERIAL -Lc:\libfiles -lrddads -lace32 -oMRPADS

These are the only 2 Advantage commands I currently have at the beginning of my main app:
REQUEST ADS
rddsetdefault( "ADSNTX" )

[reinaldocrespo]

Jay;

1) How would I set up your afiles array to pass 1 dbf and multiple ntx's to the CreateDictionary()? because the names of my dbf's aren't related to the ntx's. Example...
Customer.dbf
Custname.ntx
Custnbr.ntx
Custid.ntx
Payments.dbf
Pmttype.ntx
Pmtdate.ntx

Notice on my sample code posted here how the table and indexes get added to the data dictionary using ACE function AdsDDAddTable(). If you take a peek at the documentation -btw ads is extremely well documented- you will find how to use it.

From my code:

Code: Select all  Expand view

AdsDDAddTable( left( afile[ 1 ], at(".", afile[1] )-1 ), ;
                cPath + aFile[ 1 ], ;
                cIndexs )
 

In the case of items.dbf notice this translate to AdsDDAddTable( "items", "items.dbf", "itm_id.ntx;itm_desc.ntx" ) where itm_id.ntx and itm_desc.ntx are ntx index files on table items.dbf. It is all on my sample code. In your case that would be: AdsDDAddTable( "customers", "customers.dbf", "custname.ntx;custnbr.ntx;custid.ntx" ).

2) How do I link in the ADS.ch? I have never "included" anything in Harbour yet. The following is my current .hbp file:
MATERIAL -Lc:\libfiles -lrddads -lace32 -oMRPADS

I don't use .hbp files. Don't really understand them. I don't link ads.ch. You might needed it on your include path if you are using any #defined values.

Again, all you need to create the data dictionary using ACE functions is on my sample code and all these functions are well documented. If you wish to see their wrapper implementation take a peek at adsfunc1.c.

Code: Select all  Expand view

HB_FUNC( ADSDDADDTABLE )
{
   hb_retl( AdsDDAddTable( HB_ADS_PARCONNECTION( 4 ) /* hConnect */,
                           ( UNSIGNED8 * ) hb_parcx( 1 ) /* pTableName */,
                           ( UNSIGNED8 * ) hb_parcx( 2 ) /* pTableFileName */,
                           ( UNSIGNED16 ) hb_ads_iFileType,
                           ( UNSIGNED16 ) hb_ads_iCharType,
                           ( UNSIGNED8 * ) hb_parcx( 3 ) /* pTableIndexFileName */,
                           NULL ) == AE_SUCCESS );
}
 

Reinaldo.

[Jay Hill]

Reinaldo - Thank you very much again. I've got most of it. You've been especially helpful.

One last question:

When using AdsDDAddTable, Is it imperative that I specify the indexes in the correct order? (that is when using the DD, I can now get rid of all my existing SET ORDER TO commands before issuing a SEEK) Or is that only when using SQL that the index(tag) order is irrelevant?

AdsDDAddTable( "items", "items.dbf", "itm_id.ntx;itm_desc.ntx" )

Jay

[reinaldocrespo]

Hey Jay;

Although the SQL engine knows how to best use indexes to optimize speed, ISAM stays ISAM. By this I mean, that your ISAM code stays the same and works the same. With SQL you don't have syntax to "setOrder" or "Seek". The relationships between tables and existing indexes are saved on the Data Dictionary and this info is then used by the SQL engine to do its job. Indexed-Sequential activity stays "old-school". I hope that makes sense.

I think part of the confusion is that ADS is the only SQL that is ISAM as well as SQL and you can mix and match according to your needs.

Reinaldo.

[Jay Hill]

Thank you again, Reinaldo.

Jay