FiveTech Software tech support forums

by **TimStone** » Thu Apr 26, 2012 7:57 pm

OK ... using tDatabase plus tData, the following works with .dbf files to create indexes:

oDB := tdata():new(, "einmst",, .f. )
IF oDB:use()
oDB:createIndex( "einnum", "upper(invnum)",,, .t., 10 )
ENDIF
oDB:close()

However, with ADT files, it doesn't work, so when I try to add a record to the file, it tells me the workarea is not indexed !

Unfortunately Advantage docs seem to be coming up short on this.

Tim

by **fraxzi** » Fri Apr 27, 2012 12:22 am

Otto wrote:Frances,
what access rights on the Server must an ADS user have.
Best regards,
Otto

Dear Mr. Otto,

It depends on your granted rights to users which is define in DD via ARC or you can do it painfully by code.

by **fraxzi** » Fri Apr 27, 2012 12:23 am

TimStone wrote:Otto,

ADS is installed on the server, and of course it needs to be able to create files, and modify them ( read / write ). Since all of the work is done on the server, the clients can have limited access rights. Some of my customers are pretty minimal with the rights ganted on the workstation, but the client program I install on each one interacts with the ADS server with no problem.

Tim

Dear Mr. Tim,

There's no need in defining user rights via OS.. You can do it more securely by ADS server-side aliasing.

by **fraxzi** » Fri Apr 27, 2012 12:27 am

TimStone wrote:1) It would appear that I end up creating a group of separate .adt files just like the .dbf files. I thought ADS created a single database that housed all the tables, just like an SQL database.

2) Can you actually import the empty .dbf file structures ? I can't seem to find an option for doing that.

Tim

Dear Tim,

1) .adt are individual but binded to DD where you import it. You need to freetable those .adt so you can use it outside of DD..
2) no features for that. import only table

by **TimStone** » Fri Apr 27, 2012 12:34 am

Thanks for the info. I'm still concerned about the indexing using tdata / tdatabase classes. Please see my earlier post in this thread.

by **norberto** » Fri Apr 27, 2012 1:05 am

Tim, i create my indexs in "normal" way (use / index), after open using tdata ( database + index).

look this class too (source + help + samples included): http://sourceforge.net/projects/tadscla ... r/download

by **Enrico Maria Giordano** » Fri Apr 27, 2012 8:37 am

TimStone wrote:I thought ADS created a single database that housed all the tables, just like an SQL database.

Please note that an SQL engine can organize its database in the most creative way. You can't bet on the fact that it's a single file or multiple files.

EMG

by **reinaldocrespo** » Wed May 23, 2012 3:39 am

I just saw this thread. I thought there would be a lot more people using .adt files.

Is adt tables still an issue?

You still use the very same rdd_ads.lib with ntx/cdx/vfp or adt tables. All you do is change the RDD. For dbf tables with harbour you have the choice of using DBFNTX, DBFCDX, or ADSNTX, ADSCDX, ADSVFP and for adt tables ADSADT. As in use tablename VIA ADSCDX. That's it.

Now, ADT tables as well as dbf tables can be used with ADS RDD as free tables or DD bound tables. Using DD bound tables is simply a lot better. Creating indexes with ads rdd can be done the same way it is done for dbf tables. Having said that, with ads you can also create indexes using the sp_createIndex stored procedure or with sql syntax.

Since I see there is some doubts on this subject, I wrote a short self contained sample of how to create .adt tables and indexes using regular clipper syntax. I haven't tested the code much, and it is been a long time since I use clipper syntax so there might be some errors, but you'll get the idea:

Code: Select all Expand view: #include "ads.ch" #xcommand DEFAULT <uVar1> := <uVal1> ; [, <uVarN> := <uValN> ] => ; <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] REQUEST ADS request hb_gt_win //needed for console mode app. Static cPath *----------------------------------------------------------------------------------------------------- Function Main() local nerr local afiles SetMode(25,80) //25 lines by 80 columns console rddRegister( "ADS", 1 ) rddSetDefault( "ADS" ) adsSetServerType( ADS_LOCAL_SERVER ) AdsLocking( .t. ) //NON-compatible locking mode cPath := hb_ArgV( 1 ) DEFAULT cpath := "" if !empty( cPath ) .and. right( cPath, 1 ) != "\" ;cPath += "\" ;endif afiles := CreateTables() if !empty( afiles ) ;CreateDictionary( afiles ) ;endif return nil *-------------------------------------------------------------------------------------------------------- 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 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. ) AdsDDCreateUser( , "user1", "password1", "User named userd1 with password password1" ) AdsDDCreateUser( , "user2", "password2", "Description of user2" ) AdsDDCreateUser( , "user3", "password3", "Optional description of user3" ) 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", ; e[ 1 ], ; AdsSetFileType(), ; cExt, ; ADSGetLastError() ) } ) AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "password" ) Wait return Nil *------------------------------------------------------------------------------------------------------------------------------- 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 ) ) *------------------------------------------------------------------------------------------------------------------------------- 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

Hope that helps.

Reinaldo.

by **reinaldocrespo** » Wed May 23, 2012 3:46 am

While I'm at it, here is sample code doing the same thing using sql syntax + stored procedures to create the indexes. Again, I'm not spending much time testing this code. It should work other than some minor errors, but moreover it gives an idea:

Code: Select all Expand view: #include "ads.ch" #xcommand DEFAULT <uVar1> := <uVal1> ; [, <uVarN> := <uValN> ] => ; <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] REQUEST ADS request hb_gt_win //needed for console mode app. Static cPath *----------------------------------------------------------------------------------------------------- Function Main() local nerr SetMode(25,80) //25 lines by 80 columns console rddRegister( "ADS", 1 ) rddSetDefault( "ADS" ) adsSetServerType( ADS_LOCAL_SERVER ) AdsLocking( .t. ) //NON-compatible locking mode cPath := hb_ArgV( 1 ) DEFAULT cpath := "" if !empty( cPath ) .and. right( cPath, 1 ) != "\" ;cPath += "\" ;endif if createDD() ;CreateTables() ;endif return nil *-------------------------------------------------------------------------------------------------------- static function CreateTables() local e AS OBJECT local cSql_1 AS CHARACTER local cSql_2 AS CHARACTER local cSql_3 AS CHARACTER cSql_1 := "CREATE TABLE customers ( " + ; "CUST_ID Char( 10 )," + ; "SEQUENCE AutoInc," + ; "CUSTOMER_NAME Char( 25 )," + ; "START_DATE TimeStamp," +; "NOTES Memo) IN DATABASE;" + ; ; "EXECUTE PROCEDURE sp_CreateIndex90( "+; "'customers',"+; "'customers.adi',"+; "'CUST_ID',"+; "'CUST_ID',"+; "'',"+; "2,"+; "512,"+; "'' ); "+; ; "EXECUTE PROCEDURE sp_CreateIndex90( "+; "'customers',"+; "'customers.adi',"+; "'START_DATE',"+; "'START_DATE',"+; "'',"+; "2,"+; "512,"+; "'' ); "+; ; "EXECUTE PROCEDURE sp_ModifyTableProperty( 'customers', "+; "'Table_Auto_Create', "+; "'False', 'APPEND_FAIL', 'customersfail');"+; ; "EXECUTE PROCEDURE sp_ModifyTableProperty( 'customers', "+; "'Table_Permission_Level', "+; "'2', 'APPEND_FAIL', 'customersfail'); "+; ; "EXECUTE PROCEDURE sp_ModifyTableProperty( 'customers', "+; "'Triggers_Disabled', "+; "'False', 'APPEND_FAIL', 'customersfail');" cSql_2 := "CREATE TABLE sales ( "+; "CUST_ID Char( 10 ),"+; "INVOICE Char( 15 ),"+; "S_DATE TimeStamp,"+; "ITEM_ID Char( 15 ),"+; "UNITS Numeric( 3 ,0 ),"+; "PRICE Numeric( 9 ,0 ),"+; "NOTES Memo) IN DATABASE;"+; ; "EXECUTE PROCEDURE sp_CreateIndex90( "+; "'sales',"+; "'sales.cdx',"+; "'INVOICE',"+; "'invoice',"+; "'',"+; "2,"+; "512,"+; "'' ); "+; ; "EXECUTE PROCEDURE sp_CreateIndex90( "+; "'sales',"+; "'sales.cdx',"+; "'CUST_ID',"+; "'cust_id',"+; "'',"+; "2,"+; "512,"+; "'' ); "+; ; "EXECUTE PROCEDURE sp_ModifyTableProperty( 'sales', "+; "'Table_Auto_Create', "+; "'False', 'APPEND_FAIL', 'salesfail');"+; ; "EXECUTE PROCEDURE sp_ModifyTableProperty( 'sales', "+; "'Table_Permission_Level', "+; "'2', 'APPEND_FAIL', 'salesfail');"+; ; "EXECUTE PROCEDURE sp_ModifyTableProperty( 'sales', "+; "'Triggers_Disabled', "+; "'False', 'APPEND_FAIL', 'salesfail');" TRY ExecuteSQL( cSql_1, ADS_ADT ) ExecuteSQL( cSql_2, ADS_VFP ) CATCH e ShowError( e ) END return nil *------------------------------------------------------------------------------------------------------------------------------- static function ExecuteSQL( cScript, cRDD ) local cArea, e local isGood := .f. if !empty( cScript ) AdsCacheOpenCursors( 0 ) dbSelectArea(0) IF !ADSCreateSQLStatement("SQLarea", cRDD) //.or. !ADSVerifySQL( cScript ) TRY SQLArea->( DBCLOSEAREA() ) CATCH e END Alert ( "AdsCreateSqlStatement() failed with error "+ Str( ADSGetLastError() ) ) Elseif !( isGood := AdsExecuteSQLDirect( cScript ) ) Alert ( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) ) endif endif AdsCacheOpenCursors( 0 ) if Select( "sqlarea" ) > 0 ;SQLArea -> ( DBCLOSEAREA() ) ;endif return nil /*-------------------------------------------------------------------------------------------------------- CreateDictionary creates an Advantage Data Dictionary based on already existing tables --------------------------------------------------------------------------------------------------------*/ static function CreateDD() local cDD := cPath + "test_dd.add" if !ADSDDCREATE( cDD,, "Sample data dictinoary" ) Alert( "AdsCreate() of " + cDD + " failed. Error:" + Str( AdsGetLastError() ) ) return .f. Endif 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. ) AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "password" ) AdsDDCreateUser( , "user1", "password1", "User named userd1 with password password1" ) AdsDDCreateUser( , "user2", "password2", "Description of user2" ) AdsDDCreateUser( , "user3", "password3", "Optional description of user3" ) return .t. *------------------------------------------------------------------------------------------------------------------------------- static function ShowError( oErr ) local cErr := Str( AdsGetLastError() ) Alert( "Error : " + alltrim( cErr ) + " " + ; oErr:Subsystem + " " + str( oErr:subCode ) + " " + ; oErr:operation + " " + oErr:description ) dbcloseall() return nil

I try to keep posting more samples later. For now, here is plenty here to chew on.

I sincerely hope this helps.

Reinaldo.

by **reinaldocrespo** » Wed May 23, 2012 3:51 am

Here is sample code on how to connect to an ADS DD. I'm not totally sure, but I think this has been shown by many people. Just in case there is doubt, here it is:

Code: Select all Expand view: #include "ads.ch" #xcommand DEFAULT <uVar1> := <uVal1> ; [, <uVarN> := <uValN> ] => ; <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] REQUEST ADS request hb_gt_win //needed for console mode app. Static cPath *----------------------------------------------------------------------------------------------------- Function Main() SetMode(25,80) //25 lines by 80 columns console rddRegister( "ADS", 1 ) rddSetDefault( "ADS" ) ADSSetServerType( ADS_REMOTE_SERVER ) AdsLocking( .t. ) //NON-compatible locking mode AdsRightsCheck( 0 ) if !AdsConnect60( "\\192.168.1.56:49000\adsdata\hdd\mp.add", ADS_REMOTE_SERVER, "adssys", "mp8" ) Alert( "Cannot connect to Remote Server " + Str( AdsGetLastError() ) ) Quit endif ReIndex() //or whatever other isam command. //once connected to the DD, you no longer need to //declare the table's physical path to use it. AdsDisconnect() return nil

Reinaldo.

by **reinaldocrespo** » Wed May 23, 2012 3:55 am

Here is more SQL vs ISAM doing some delete from a table. Notice how using SQL there is no need to open or close a table in order to operate on it:

Code: Select all Expand view: #include "ads.ch" #xcommand DEFAULT <uVar1> := <uVal1> ; [, <uVarN> := <uValN> ] => ; <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] REQUEST ADS request hb_gt_win //needed for console mode app. *----------------------------------------------------------------------------------------------------- Function Main() local nerr SetMode(25,80) //25 lines by 80 columns console rddRegister( "ADS", 1 ) rddSetDefault( "ADS" ) adsSetServerType( ADS_LOCAL_SERVER ) AdsLocking( .t. ) //NON-compatible locking mode AdsSetFileType( ADS_CDX ) AdsConnect( ........................ DeleteUsingISAM() DeleteUsingSQL() return nil *-------------------------------------------------------------------------------------------------------- static function DeleteUsingISAM() local cArea := "test" Dbusearea( .t. , "ADS", "test", cArea, .t., .f. ) ( cArea )->( OrdListAdd( "test" ) ) ( cArea )->( dbSeek( "00001" ) ) While !( cArea )->( eof() ) .and. ( cArea )->id == "00001" ( cArea )->( dbDelete() ) ( cArea )->( dbSeek( "00001" ) ) End (alias)->( dbcloseArea() ) retrun nil *------------------------------------------------------------------------------------------------------------------------------- static function DeleteUsingSQL() local cSql := "DELETE FROM test WHERE id = '00001'" DoSql_NoCursor( cSql ) Return nil *------------------------------------------------------------------------------------------------------------------------------- static function DoSQL_NoCursor( cScript ) local cArea, e local isGood := .f. if !empty( cScript ) AdsCacheOpenCursors( 0 ) dbSelectArea(0) if ADSCreateSQLStatement( "SQLarea" ) .and. AdsExecuteSQLDirect( cScript ) TRY SQLArea->( DBCLOSEAREA() ) CATCH e END else Alert ( "SQL Statement failed with error "+ Str( ADSGetLastError() ) ) end end AdsCacheOpenCursors( 0 ) if Select( "sqlarea" ) > 0 ;SQLArea -> ( DBCLOSEAREA() ) ;endif return nil *------------------------------------------------------------------------------------------------------------------------------- static function ShowError( oErr ) local cErr := Str( AdsGetLastError() ) Alert( "Error : " + alltrim( cErr ) + " " + ; oErr:Subsystem + " " + str( oErr:subCode ) + " " + ; oErr:operation + " " + oErr:description ) dbcloseall() return nil

Reinaldo.

by **reinaldocrespo** » Wed May 23, 2012 2:43 pm

Here is a short self contained sample using regular clipper syntax to create and reindex a visual fox pro table type (should work for any table type) and it will work with the local (free) ads RDD:

Code: Select all Expand view: #include "ads.ch" #include "directry.ch" request ads request hb_gt_win //needed for console mode app. /*-----------------------------------------------------------------------------------------------*/ Function Main() SetMode( 25, 80 ) //25 lines by 80 columns console rddRegister( "ADS", 1 ) rddSetDefault( "ADS" ) adsSetServerType( ADS_LOCAL_SERVER ) adsSetFileType( ADS_VFP ) //visual foxpro table and field types AdsLocking( .t. ) //NON-compatible locking mode CreateTable( 'Test') Dbusearea( .t. , "ADS", cTableName, "alias", .t., .f. ) PopulateTable( 'alias' ) ReIndex( 'alias' ) Browse() ( "alias" )->( dbcloseArea() ) return nil /*-----------------------------------------------------------------------------------------------*/ static function CreateTable( cTableName ) local aStructure := { "FileName", "Character", 10, 0 },; { "Size", "Integer", 7, 0 },; { "Date", "Date", 4, 0 },; { "Time", "C", 8, 0 },; { "Attr", "C", 1, 0 } } dbCreate( cTableName, aStructure ) Return nil /*-----------------------------------------------------------------------------------------------*/ static function PopulateTable( cAlias ) local aDir := Directory( "f:\xharbour\include\" ) aEval( aDir, { |e| ( cAlias )->( dbAppend() ),; iif( ( cAlias )->( rLock() ),; ( ( cAlias )->FileName := e[ F_NAME ],; ( cAlias )->Size := e[ F_SIZE ],; ( cAlias )->Date := e[ F_DATE ],; ( cAlias )->Time := e[ F_TIME ],; ( cAlias )->Attr := e[ F_ATTR ] ), ),; ( "alias" )->( dbUnlock() ) } ) Return Nil /*-----------------------------------------------------------------------------------------------*/ static function ReIndex( cAlias ) AdsRegCallBack( { | nPercent | ShowProgress( nPercent ) } ) /* the above codeblock will be called approximately every 2 seconds while indexing. The codeblock may return .t. to abort indexing. */ INDEX ON FileName Tag FileName AdsClrCallBack() Return Nil

Now, while regular Clipper syntax works fine, I would recommend learning sql by introducing some here and there. With time new doors open up.

Again, I'm only showing this samples because I keep seeing question about table and index creation using ADS RDD and I mean to help.

Reinaldo.

by **reinaldocrespo** » Wed May 23, 2012 2:55 pm

Here is a more complex sample. Let us suppose you already have a DD with tables created but you wish to deliver a new update of your application where some structure changes must be made in order for the new update to work properly. Then all we need is check the current table structures on the DD and compare to some other source, either supplied on an external file or coded inside the update. Here I do the second. I do a lot of it by constructing SQL statements to alter a table. You may look up any SQL command syntax on the ADS help files including the ALTER TABLE statement.

Code: Select all Expand view: #include "ads.ch" #include "dbstruct.ch" #define CRLF chr(13)+chr(10) #xcommand DEFAULT <uVar1> := <uVal1> ; [, <uVarN> := <uValN> ] => ; <uVar1> := iif( <uVar1> == nil, <uVal1>, <uVar1> ) ;; [ <uVarN> := iif( <uVarN> == nil, <uValN>, <uVarN> ); ] REQUEST ADS request hb_gt_win //needed for console mode app. Static cPath *----------------------------------------------------------------------------------------------------- Function Main() local nerr SetMode( 50, 80 ) //25 lines by 80 columns console rddRegister( "ADS", 1 ) rddSetDefault( "ADS" ) adsSetServerType( ADS_LOCAL_SERVER ) AdsLocking( .t. ) //NON-compatible locking mode Set Deleted ON cPath := hb_ArgV( 1 ) DEFAULT cpath := "" if !empty( cPath ) .and. right( cPath, 1 ) != "\" ;cPath += "\" ;endif if !ConnectToDD() Return nil endif CheckTables( GetTableStructures() ) showStructure() AdsDisconnect() //disconnect from test_dd wait return nil *----------------------------------------------------------------------------------------------------- //Ideally you would store the dd name and path and server type on an .ini file or //send both strings as parametrs to the application. // //once connected we don't need table's paths anymore as the dd knows where they reside. static Function ConnectToDD() if !AdsConnect60( cpath + "test_dd.add", ADS_LOCAL_SERVER, "adssys", "password" ) Alert( "Cannot connect LOCAL Server to dd: " + cpath + "test_dd.add" + Str( AdsGetLastError() ) ) Return .f. endif Return .t. *----------------------------------------------------------------------------------------------------- static Function GetTableStructures() local all_files_struc := {} aadd( all_files_struc,{ "customers.adt VIA ADSADT ",; { { "cust_id" , "C", 10, 0 },; { "customer_name", "C", 25, 0 },; { "Logical" , "L", 10 , 0 },; { "date" , "D", 0, 0 },; { "Sequence" , "AutoInc", 07, 0 },; //ADT extended field type (EFT) { "TStamp" , "TimeStamp", 01, 0 },; //EFT { "Integer_field", "I", 04, 0 },; //EFT { "Currency_double", "curdouble", 0, 2 } ,; //EFT { "raw" , "raw", 25, 0 },; //EFT { "rowversion" , "rowversion", 0, 0 },; //EFT { "modtime" , "modtime", 0, 0 },; //EFT { "Double" , "Double", 4, 0 },; //EFT { "Image" , "Binary", 10, 0 },; //RDD sees Image,Binary,Blob as Binary { "time" , "time", 0, 0 },; //EFT { "VarChar" , "V", 10, 0 },; //EFT { "short_integer", "I", 02, 0 },; //EFT { "Notes" , "M", 10, 0 } } } ) /* although valid field types, currently rddads does not support field types ciCharacter, Money, VarBinary. But they will work just fine SQL or ACE ADSCreateTable() { "ciCharacter" , "ciCharacter", 10 , 0 },; //case insensitive { "Money_Field" , "Y", 8, 4 },; //extended field type Money { "VarBinary" , "Q", 10, 0 },; //extended field type */ aadd( all_files_struc, { "sales.dbf VIA ADSVFP STRICT", ; { { "cust_id" , "C", 10, 0 },; { "invoice" , "C", 15, 0 },; { "item_id" , "C", 15, 0 },; { "Date" , "D", 8, 0 },; { "Logical" , "L", 10 , 0 },; { "TStamp" , "TimeStamp", 01, 0 },;//VFP extended field type { "Sequence" , "autoincrement", 0, 0 },; //VFP extended field type { "Units" , "I", 04, 0 },; //VFP extended field type { "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 { "Image" , "Binary", 10, 0 },; //VFP extended field type { "Notes" , "M", 10, 0 } } } ) aadd( all_files_struc, { "items.dbf VIA ADSNTX", ; { { "item_id" , "C", 15, 0 },; { "Desc" , "C", 25, 0 },; { "Price" , "N", 07, 2 } } } ) Return all_files_struc *----------------------------------------------------------------------------------------------------- static function CheckTables( aFiles ) local isStrict local cFileType local cTable, cExt, cFileName local aStruc, n local aDDFiles := AdsDirectory() //ACE32 function call returns array of tables in dd for each aStruc IN aFiles isStrict := .f. ////allow only the exact structure sent with no extra fields on table cFileName := lower( aStruc[ 1 ] ) cFileName := SplitTableName( cFileName, @isStrict, @cFileType ) AdsSetFileType( cFileType ) hb_FNameSplit( cFileName,,@cTable, @cExt ) if aScan( aDDfiles, cTable ) == 0 qout( "creating an ntx table ", ctable ) dbcreate( cTable, aStruc[ 2 ] ) AdsDDAddTable( cTable, cTable + iif( cFileType == ADS_ADT, ".adt", ".dbf" ), "" ) else CheckTableStructure( aStruc, cTable, isStrict ) endif next return nil *----------------------------------------------------------------------------------------------------- static function SplitTableName( cFileName, isStrict, cFileType ) local nPos DEFAULT isStrict := .f. DEFAULT cFileType := ADS_ADT //presume .adt when not using VIA as part of tablename if at( " STRICT", upper( cFileName ) ) > 0 isStrict := .t. cFileName := lower( alltrim( strtran( cFileName, " STRICT", "" ) ) ) Endif if ( nPos := at( " VIA ", upper( cFileName ) ) ) > 0 cFileType := iif( "CDX" $ upper( substr( cFileName, nPos ) ), ADS_CDX, ; iif( "VFP" $ upper( substr( cFileName, nPos ) ), ADS_VFP ,; iif( "NTX" $ upper( cFileName ), ADS_NTX, ADS_ADT ) ) ) cFileName := lower( alltrim( Left( cFileName, nPos -1 ) ) ) Endif return cFileName *----------------------------------------------------------------------------------------------------- static Function CheckTableStructure( aTable, cTable, isStrict ) local aStruc := aTable[ 2 ] local cAlias, n qOut( "Checking structure of table: " + cTable ) if TableStrucIsChanged( cTable, @aStruc, @cAlias, isStrict ) //aStruc may change if !isStrict UpdateTableStruc( cTable, aStruc, cAlias ) endif (cAlias)->( dbCloseArea() ) Return Nil *----------------------------------------------------------------------------------------------------- static Function TableStrucIsChanged( cFileName, aStruc, cAlias, isStrict ) local isChanged := .f. local nPos := 0 local i := 0 local afStruc //afStruc is the found file structure local j cAlias := cFileName Dbusearea( .t. , "ADS", cFileName, cAlias, .f. ) //exclusive use is needed to update table struc afStruc := ( cAlias )->( dbstruct() ) while ++i <= len( aStruc ) .and. !isChanged if len( aStruc ) > len( afStruc ) .or. ; ( nPos := aScan( afStruc, { |e| trim( upper( aStruc[ i, DBS_NAME ] ) ) == ; Trim( upper( e[ DBS_NAME ] ) ) } ) ) == 0 .or. ; ; //if field names are equal then continue by comparing field types upper( aStruc[ i, DBS_TYPE] ) != afStruc[ nPos, DBS_TYPE ] .or. ; ;//Avoid comparing field length when autoinc, date,memo,money,raw,rowversion,curdouble ... ( !(TransFieldName( aStruc[ i ] ) $ ; "MONEY,LOGICAL,CURDOUBLE,ROWVERSION,RAW,MODTIME,DOUBLE,BINARY,IMAGE,BLOB,AUTOINC,TIME,TIMESTAMP,DATE,MEMO") .and. ; ( aStruc[ i, 3 ] > afStruc[ nPos, DBS_LEN ] .or. ; aStruc[ i, DBS_DEC ] <> afStruc[ nPos, DBS_DEC ] ) ) qOut( "Table ", cFileName, " needs a structure update on field ", aStruc[ i, DBS_NAME ] ) isChanged := .t. Endif End if isStrict .and. len( afStruc ) > len( aStruc ) isChanged := .t. endif if !isStrict //if not strict then keep old fields for backward compatibility for j := 1 to len( afstruc )//first insert new fields i := 0 ; npos := 0 while ++i <= len( astruc ) .and. nPos == 0 if alltrim( upper( afstruc[ j, DBS_NAME ] ) ) == alltrim( upper( astruc[ i, DBS_NAME ] ) ) nPos := i endif end if nPos == 0 aadd( astruc, afstruc[ j ] ) endif next j Endif Return isChanged *----------------------------------------------------------------------------------------------------- static Function UpdateTableStruc( cTable, aStruc, cAlias ) local cAdd := alltrim( Fields2Add( aStruc, (calias)->( dbStruct() ) ) ) local cDel := alltrim( Fields2Del( astruc, (calias)->( dbStruct() ) ) ) local cMod := alltrim( Fields2Chn( aStruc, (calias)->( dbstruct() ) ) ) local cErr, cSQL, isOk (calias)->( dbclosearea() ) cAdd := SQLAlterStm( "ADD", left( cAdd, len( cAdd ) -1 ) ) cDel := SQLAlterStm( "DROP", left( cDel, len( cDel ) -1 ) ) cMod := SqlAlterStm( "ALTER", left( cMod, len( cMod ) -1 ) ) cSQL := "ALTER TABLE " + cTable + cAdd + cDel + cMod if empty( cadd + cdel + cmod ) Alert( "Update string could not be determined. Table " + alltrim( cTable ) ) else ExecuteSQL( cSQL ) ENDIF return( Nil ) *----------------------------------------------------------------------------------------------------- Static Function SQLAlterStm( cAction, cColInfo ) local cStr := "" local aParsed := hb_aTokens( cColInfo, ";" ) local a local Elem, cFldType //if alter column then: a[1] -old field name, a[2] -new field name, a[3] -field type, a[4] -field length, a[5] -field decimals //if add column then: a[1] new field name, a[2] -field type, a[3] -field length, a[4] -field decimals if empty( cAction ) RETURN cStr Endif if cAction == "DROP" FOR EACH Elem IN aParsed cStr += " DROP " + elem + " " NEXT endif if cAction $ [ADDALTER] FOR EACH Elem IN aParsed a := hb_aTokens( elem, "," ) cFldType := upper( a[ iif( cAction == "ADD", 2, 3 ) ] ) cStr += iif( cAction == "ADD", " ADD COLUMN [" + alltrim( a[ 1 ] ) + "]", ; " ALTER COLUMN [" + alltrim( a[ 1 ] ) + "] " ) do case case cFldType == "CHARACTER" .or. cFldType == "C" cstr += iif( cAction == "ADD", ; " Char(" + alltrim( a[ 3 ] ),; " [" + a[ 2 ] + "] Char( " + alltrim( a[ 4 ] ) ) + ") " case cFldType == "NUMERIC" .or. cFldType == "N" cstr += iif( cAction == "ADD", ; " Numeric(" + alltrim( a[ 3 ] ) +"," + alltrim( a[ 4 ] ),; " [" + a[ 2 ] + "] Numeric (" + alltrim( a[ 4 ] ) + "," + a[ 5 ] ) + ") " case cFldType == "D" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Date " case cFldType == "L" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Logical " case cFldType == "M" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Memo " case cFldType == "Y" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Money " case cFldType == "T" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " TimeStamp" case left( cFldType, 1 ) == "A" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " AutoInc " case cFldType == "I" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + ; iif( a[ iif( cAction == "ADD", 3, 4 ) ] == "2", " Short ", " Integer " ) case cFldType == "DOUBLE" .or. cFldType == "O" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Double( " +; alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " case cFldType == "CURDOUBLE" .or. cFldType == "O" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " CurDouble( " +; alltrim( a[ iif( cAction == "ADD", 4, 5 ) ] ) + ") " case "VARCHAR" $ cFldType .or. cFldType == "V" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " VarChar( "+; alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " case "VARBINARY" $ cFldType .or. cFldType == "Q" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Varbinary( "+; alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " case "RAW" $ cFldType cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Raw (" +; alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " case "CICHARACTER" $ cFldType cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " CIChar (" +; alltrim( a[ iif( cAction == "ADD", 3, 4 ) ] ) + ") " case cFldType $ "BLOB,IMAGE,BINARY" cStr += iif( cAction == "ADD", "", " [" + a[ 2 ] + "] " ) + " Blob" otherwise cStr += iif( cAction == "ADD", " ", " [" + a[ 2 ] + "] " ) + cFldType Endcase NEXT Endif RETURN cStr *------------------------------------------------------------------------------------------------------------------------------- static function Fields2Add( aStruc, adbStruc ) local c := "" local i local cFieldName DEFAULT adbStruc := {} for i := 1 to len( aStruc ) cFieldName := upper( alltrim( aStruc[ i, DBS_NAME ] ) ) if aScan( adbStruc, { |e| cFieldName == upper( alltrim( e[ DBS_NAME ] ) ) } ) == 0 c += cFieldName + "," + ; aStruc[ i, DBS_TYPE ] + "," + ; alltrim( str( aStruc[ i, DBS_LEN ] ) ) + "," + ; alltrim( str( aStruc[ i, DBS_DEC ] ) ) + ";" endif next i return c *------------------------------------------------------------------------------------------------------------------------------- static function Fields2Del( astruc, adbStruc ) local c := "" local i local cFieldName for i := 1 to len( adbStruc ) cFieldName := upper( alltrim( adbStruc[ i, DBS_NAME ] ) ) if aScan( aStruc, { |e| cFieldName == upper( alltrim( e[ DBS_NAME ] ) ) } ) == 0 c += cFieldName + ";" endif next i return c *------------------------------------------------------------------------------------------------------------------------------- static function Fields2Chn( aStruc, adbstruc ) local cRet := "" local i local nPos local cFieldName for i := 1 to len( aStruc ) cFieldName := upper( alltrim( aStruc[ i, DBS_NAME ] ) ) nPos := aScan( adbStruc, { |e| upper( alltrim( e[ DBS_NAME ] ) ) == cFieldName } ) if nPos != 0 .and. ( upper( aStruc[ i, DBS_TYPE] ) <> adbStruc[ nPos, DBS_TYPE ] .or. ; ;//don't compare field length for integer, short, date, time, binary, memo, AutoInc) ( !(TransFieldName( aStruc[ i ] ) $ ; "MONEY,LOGICAL,CURDOUBLE,ROWVERSION,RAW,MODTIME,DOUBLE,BINARY,IMAGE,BLOB,AUTOINC,TIME,TIMESTAMP,DATE,MEMO") .and. ; ( aStruc[ i, DBS_LEN ] > adbStruc[ nPos, DBS_LEN ] .or. ; aStruc[ i, DBS_DEC ] <> adbStruc[ nPos, DBS_DEC ] ) ) ) cRet += adbStruc[ nPos, DBS_NAME ] + "," + ; aStruc[ i, DBS_NAME ] + "," + ; aStruc[ i, DBS_TYPE ] + "," + ; alltrim( str( aStruc[ i, DBS_LEN ] ) ) + "," + ; alltrim( str( aStruc[ i, DBS_DEC ] ) ) +";" endif next i return cRet *------------------------------------------------------------------------------------------------------------------------------- static function showStructure() local aFiles := GetTableStructures() local cFileName, cFileType, aStruc local ctable, cext local i for i := 1 to len( aFiles ) cFileName := SplitTableName( afiles[ i, 1 ],, @cFileType ) hb_FNameSplit( cFileName,,@cTable, @cExt ) AdsSetFileType( cFileType ) Dbusearea( .t. , "ADS", cTable, cTable, .f. ) aStruc := ( cTable )->( dbStruct() ) ( cTable )->( dbclosearea() ) qOut( "Structure of table ", cFileName ) aEval( aStruc, { |e| qout( e[ DBS_NAME ], e[ DBS_TYPE ], e[ DBS_LEN ], e[ DBS_DEC ] ) } ) wait "any key to continue" next return Nil *------------------------------------------------------------------------------------------------------------------------------- static function ExecuteSQL( cSql ) local isOk := .f. AdsCacheOpenCursors( 0 ) if AdsCreateSQLStatement(, ADS_ADT) isOk := AdsExecuteSQLDirect( cSQL ) endif if !isOk Alert( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) ) qOut( cSQL ) endif HB_INLINE() { hb_rddReleaseCurrentArea(); } //only good for non-cursor returning sql return nil *------------------------------------------------------------------------------------------------------------------------------- //translate single characters returned by the RDD for some field types //to full word field types static function TransFieldName( aField ) local cFldType := upper( aField[ DBS_TYPE ] ) do case case cFldType == "Y" return "MONEY" case cFldType == "C" return "CHARACTER" case cFldType == "N" return "NUMERIC" case cFldType == "M" return "MEMO" case cFldType == "V" return "VARCHAR" case cFldType == "Q" return "VARBINARY" case cFldType == "I" return iif( aField[ DBS_DEC ] == 2, "SHORT", "INTEGER" ) case cFldType == "D" return "DATE" case cFldType == "L" return "LOGICAL" endcase return cFldType *------------------------------------------------------------------------------------------------------------------------------- static function TestRIRule() local cParent := "customers" local cChild := "sales" local nerr AdsSetFileType( ADS_ADT ) Dbusearea( .t. , "ADS", "customers", cParent, .t. ) //exclusive use is needed to update table struc qout( "cust_id:", (cParent)->cust_id ) (cParent)->( dbDelete() ) qout( "cust_id:", (cParent)->cust_id, neterr() ) if ( nErr := ADSGetLastError() ) != 0 Alert( "Delete Failed with " + cStr( nErr ) ) endif browse() (cParent)->( dbclosearea() ) return nil *------------------------------------------------------------------------------------------------------------------------------- /* aadd( all_files_struc,{ "customers.adt VIA ADSADT ",; { { "cust_id" , "CHARACTER", 10, 0 },; { "Sequence" , "AutoInc", 07, 0 },; //ADT extended field type autoincrement { "customer_name", "CHARACTER", 25, 0 },; { "Logical" , "Logical", 10 , 0 },; { "date" , "date", 0, 0 },; { "time" , "time", 0, 0 },; //extended field type { "TStamp" , "TimeStamp", 01, 0 },; //extended field type { "Integer_field", "Integer", 03, 0 },; //extended field type { "short_integer", "short", 03, 0 },; //extended field type { "Money_Field" , "Money", 09, 2 },; //extended field type { "Currency_double", "curdouble", 0, 2 } ,; //extended field type { "raw" , "raw", 25, 0 },; //extended field type { "rowversion" , "rowversion", 0, 0 },; //extended field type { "modtime" , "modtime", 0, 0 },; //extended field type { "Double" , "Double", 4, 0 },; //extended field type { "VarBinary" , "VarBinary", 10, 0 },; //extended field type { "VarChar" , "VarChar", 10, 0 },; //extended field type { "Image" , "Image", 10, 0 },; //extended field type { "Binary" , "Binary", 0, 0 },; //extended field type { "Notes" , "MEMO", 10, 0 } } } ) // { "ciCharacter" , "ciCharacter", 10 , 0 }}})//,; //case insensitive aadd( all_files_struc, { "sales.dbf VIA ADSVFP STRICT", ; { { "cust_id" , "CHARACTER", 10, 0 },; { "invoice" , "CHARACTER", 15, 0 },; { "item_id" , "CHARACTER", 15, 0 },; { "Date" , "DATE", 8, 0 },; { "Logical" , "Logical", 10 , 0 },; { "TStamp" , "TimeStamp", 01, 0 },; //VFP extended field type { "Sequence" , "autoincrement", 0, 0 },; //VFP extended field type { "Units" , "Integer", 03, 0 },; //VFP extended field type { "Price" , "Money", 09, 2 },; //VFP extended field type { "Double" , "Double", 4, 0 },; //VFP extended field type { "VarChar" , "VarCharFox", 10 , 0 },; //VFP extended field type { "VarBinary" , "VarBinaryFox", 10, 0 },; //VFP extended field type { "Image" , "Image", 10, 0 },; //VFP extended field type { "Notes" , "Memo", 10, 0 } } } ) *------------------------------------------------------------------------------------------------------------------------------- static function CreateNewTable( cFile, aStruc ) local cSQl := "CREATE TABLE "+ lower( cfile ) + " ( " local aElem, nTmp local isOk local cADD := Fields2Add( aStruc ) local aFieldDesc, aParsed cAdd := SQLAlterStm( "ADD", cAdd ) cAdd := Substr( StrTran( cAdd, "ADD COLUMN", ", " + CRLF ), 3 ) cSql += cAdd cSql := SubStr( cSql, 1, rAt( ",", cSql ) -1 ) cSql += ") IN DATABASE ;" ExecuteSQL( cSql ) Return Nil */

Reinaldo.

FiveTech Software tech support forums

Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Re: Using ADT files - a few questions

Who is online