ADO RDD xHarbour

[James Bott]

Pieter,

Thanks for the response. In my case the table already has the HBRECNO field so that is not the issue. Today I will try a different database and try checking the integrity of the original one.

Perhaps you can post your error log when yours crashes.

James

[maromano]

Hello, I am new to the forum and downloaded the ADORDD code.
I did several tests and several changes.

url off my fork
https://github.com/maromano/adordd

I created
ADO VIRTUAL DELETE SET ON / OFF
ADO SET DEFAULT DELETED FIELD TO cname
SET ADO FIELDDELETED TABLES LIST TO array

take a look at TRYADORDD that even do several tests APPEND FROM VIA DBFCDX

[James Bott]

Antonio,

FWAdoCreateTable( "tableName", aFields, oCon, .T. )

Please, what is the last parameter for this function example above?

Also, is there a place I can find documentation for all the ADO functions?

James

[James Bott]

Pieter or anyone:

Is there way to not import deleted records from a DBF, or do we need to PACK the database before importing?

Maybe we can just do:

APPEND FROM ... FOR ! DELETED()

James

[Antonio Linares]

James,

You can review all FWH ADO functions source code in FWH\source\function\adofuncs.prg

In FWAdoCreateTable() the last parameter is lAddAutoInc. As far as I understand it, it means to automatically create an auto incremental ID field.

I started a page in the wiki for the FWH ADO functions but it is not completed yet:

http://wiki.fivetechsoft.com/doku.php?id=fwh_ado_api

[maromano]

Hi , James
In my tests the append from work via dbfcdx

Download my code and test.
Just did tests with ACCESS .

[maromano]

Pieter or anyone:

Is there way to not import deleted records from a DBF, or do we need to PACK the database before importing?

Maybe we can just do:

APPEND FROM ... FOR ! DELETED()

James

James
With my code if you use
ADO VIRTUAL SET DELETE ON
and let SET DELETED OFF,
qdo import via dbfcdx HBDELETED will see a column in the new table that will TRUE (-1 ) for deleted records and false ( 0) for non deleted .
The name of the column you choose to
ADO SET DEFAULT DELETED FIELD TO cname .

See the TRYADORDD

[James Bott]

Maromano,

Thanks for your responses. I'm sure that your deleted functions will be useful.

I wrote a custom database class that actually reuses deleted records so the DBFs never need compacting. However, right now I am not sure if this can be used with SQL tables since they require an auto-increment field over which we have no control. This is something I will have to investigate so I will put it on my ToDo list.

James

[pieter]

Pieter or anyone:

Is there way to not import deleted records from a DBF, or do we need to PACK the database before importing?

Maybe we can just do:

APPEND FROM ... FOR ! DELETED()

James

From XHarbour reference guide FROM APPEND:

Deleted records: Records in an import database file that are marked for deletion are ignored when
SET DELETED is set to ON. These records are not imported. With SET DELETED OFF, all records
matching the import scope are imported and the deleted flag is set accordingly

I tried to test this, but I cannot get this working because the following code does not work also:

Code: Select all

#include "fivewin.ch"
#include "adordd.ch"

    REQUEST ADORDD, ADOVERSION

    FUNCTION Main()

       RddRegister("ADORDD",1)
       RddSetDefault("ADORDD")
       
       SET ADO FORCE LOCK OFF   // Required!
        
       SET ADO DEFAULT DATABASE TO "testdb1" SERVER TO "localhost"  ENGINE TO "MYSQL" USER TO "root" PASSWORD TO "password"
       
       USE CUSTOMER //connect with customer sqltable (with hbrecno instead of id)
       //APPEND BLANK
       //REPLACE FIELD->FIRST WITH "Pieter2"        
       APPEND FROM customer2 via "DBFCDX" //gives an error. //I also tried   APPEND FROM customer2.dbf via "DBFCDX"
       
       BROWSE()
    
     Return ni

Code: Select all

Application
===========
   Path and name: C:\Pieter\adorddtest\adorddtest.exe (32 bits)
   Size: 2,769,408 bytes
   Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20150213)
   FiveWin  Version: FWHX 15.01
   Windows version: 6.2, Build 9200 

   Time from start: 0 hours 0 mins 1 secs 
   Error occurred at: 07/30/15, 09:42:04
   Error description: Error DBCMD/1005  Argument error: __DBAPP

Stack Calls
===========
   Called from:  => __DBAPP( 0 )
   Called from: adorddtest.prg => MAIN( 18 )

System
======
   CPU type: Intel(R) Pentium(R) CPU  N3540  @ 2.16GHz 2166 Mhz
   Hardware memory: 3500 megs

   Free System resources: 90 %
        GDI    resources: 90 %
        User   resources: 90 %

   Windows total applications running: 4
      1 ,                                                                                                     
      2 , C:\Windows\SYSTEM32\SHLWAPI.dll                                                                     
      3 DDE Server Window, C:\Windows\SYSTEM32\OLE32.DLL                                                                       
      4 Tussen taken schakelen, C:\Pieter\adorddtest\adorddtest.exe                                                                 

Variables in use
================
   Procedure     Type   Value
   ==========================
   __DBAPP
     Param   1:    C    "customer2"
     Param   2:    A    Len:    0
     Param   3:    U    
     Param   4:    U    
     Param   5:    U    
     Param   6:    U    
     Param   7:    L    .F.
     Param   8:    C    "DBFCDX"
     Param   9:    U    
     Param  10:    U    
     Local   1:    U    
     Local   2:    L    .T.
   MAIN

Linked RDDs
===========
   DBF
   DBFFPT
   DBFBLOB
   DBFNTX
   ADORDD

DataBases in use
================

  1: => CUSTOMER                           RddName: ADORDD
     ==============================
     RecNo    RecCount    BOF   EOF
          1          501      .F.   .F.

     Indexes in use                        TagName

     Relations in use

Classes in use:
===============
     1 ERROR
     2 HASHENTRY
     3 HBCLASS
     4 TOLEAUTO
     5 HBOBJECT
     6 TREG32

Memory Analysis
===============
      261 Static variables

   Dynamic memory consume:
      Actual  Value:          0 bytes
      Highest Value:          0 bytes

Pieter

[maromano]

I tried to test this, but I cannot get this working because the following code does not work also:

Code: Select all

#include "fivewin.ch"
#include "adordd.ch"

    REQUEST ADORDD, ADOVERSION

    FUNCTION Main()

       RddRegister("ADORDD",1)
       RddSetDefault("ADORDD")
       
       SET ADO FORCE LOCK OFF   // Required!
        
       SET ADO DEFAULT DATABASE TO "testdb1" SERVER TO "localhost"  ENGINE TO "MYSQL" USER TO "root" PASSWORD TO "password"
       
       USE CUSTOMER //connect with customer sqltable (with hbrecno instead of id)
       //APPEND BLANK
       //REPLACE FIELD->FIRST WITH "Pieter2"        
       APPEND FROM customer2 via "DBFCDX" 
       
       BROWSE()
    
     Return ni

In my tests using ADORDD I put a REQUEST DBFCDX and did not show any error. But I tested with ACCESS .

Code: Select all

#include "fivewin.ch"
#include "adordd.ch"

    *********************
    REQUEST DBFCDX
    *********************
    REQUEST ADORDD, ADOVERSION

    FUNCTION Main()

       RddRegister("ADORDD",1)
       RddSetDefault("ADORDD")
       
       SET ADO FORCE LOCK OFF   // Required!
        
       SET ADO DEFAULT DATABASE TO "testdb1" SERVER TO "localhost"  ENGINE TO "MYSQL" USER TO "root" PASSWORD TO "password"
       
       USE CUSTOMER //connect with customer sqltable (with hbrecno instead of id)
       //APPEND BLANK
       //REPLACE FIELD->FIRST WITH "Pieter2"        
       APPEND FROM customer2 via "DBFCDX" //gives an error. //I also tried   APPEND FROM customer2.dbf via "DBFCDX"
       
       BROWSE()
    
     Return ni

See my TRYADORDD. I do not use FiveWin.
You need to create two folders , DADOS and LOCKS in the current folder of the executable to work. And having a DBF called IMPORTS.DBF.

Code: Select all

//2015 AHF - Antonio H. Ferreira <disal.antonio.ferreira@gmail.com>

#include "adordd.ch" 


 FUNCTION Main()
 
 Request DBFCDX
 
 LOCAL cSql :=""
 SET EXCLUSIVE OFF




 REQUEST ADORDD, ADOVERSION, RECSIZE
  RddRegister("ADORDD",1)
  RddSetDefault("ADORDD")
  


    /*                                             NOTES

    SET ADO TABLES INDEX LIST TO = indexes without of any clipper like expression only to be used by SQL

    SET ADODBF TABLES INDEX LIST TO = indexes with the clipper like expressions needed by the app for its evaluations.

    When we use &(indexkey(0)) we cant evaluate a index expression in ADO TABLES "name+dDate+nValue" we will get an error but we can issues a sql select like that.
    Thus we need the ADODBF expression "name+DTOS(dDate)+STR(nValue) to do it.

    The ADO indexes are for queries the ADODBF are for the normal clipper expressions to allow its evaluation.

        ARRAY SPEC FOR BOTH CASES:
        ATTENTION ALL MUST BE UPPERCASE
        { {"TABLE1",{"FIRST","FIRST"} }, {"TABLE2" ,{"CODID","CODID"}} }

    temporary index names
    temporary indexes are not included here they are create on fly and added to temindex list array
    they are only valid through the duration of the application
    the temp index name is auto given by adordd

    SET ADO TEMPORAY NAMES INDEX LIST TO {"TMP","TEMP"}

    each table autoinc field used as recno

    SET ADO FIELDRECNO TABLES LIST TO {{"TABLE1","HBRECNO"},{"TABLE2","HBRECNO"}}

    default table autoinc field used as recno

    SET ADO DEFAULT RECNO FIELD TO "HBRECNO"

    SET AUTOPEN ON //might be OFF if you wish
    SET AUTORDER TO 1 // first index opened can be other

    set default parameters to adordd if you do not USE COMMAND or dont pretend to include this info
    set it here

    SET ADO DEFAULT DATABASE TO "test2.mdb" SERVER TO "ACCESS" ENGINE TO "ACESS" ;
       USER TO "" PASSWORD TO ""

    SET DBF TABLE TCONTROL ALL LOCKING RECORD AND TABLE IN ADORDD

    DEFAULTS TO PATH WHERE APP IS RUNING
    SET ADO LOCK CONTROL SHAREPATH TO  "C:" RDD TO "DBFCDX"
    DISABLE CONTOL LOCK
    SET ADO FORCE LOCK OFF /ON

/*               THE ONLY CHANGES IN YOUR APP CODE END HERE! (SHOULD)              */


/*                                T R I A L S
   PEASE READ THIS CAREFULLY!

   PLEASE REMEMBER THAT ALTHOUGH ADORDD STILL AND MIGHT WORK WITHOUT ANY AUTOINC FIELD AS RECNO
   RESULT WILL BE UNPREDICTABLE IN SOME CIRCUNSTANCES OR ERROR MIGHT OCCOUR.
   THE FINAL RELEASE WIL NOT WORK WITHOUT SUCH A FIELD

   INDEXES WITH DATES IN SOME BROWSE WITHIN A DATE SCOPE RECORD MOVEMENT HAS STILL SOME PROBLEMS

   WHEN YOU DELETE A RECORD YOU CANT ACCESS IT ANYMORE. THUS CODE LIKE THIS IS ILLEGAL:

   DELETE RECORD
   BLANKREC

   THIS MUST BE CHANGED TO

   IF RDDNAME() = "ADORDD"
      BLANKREC
      DELETE RECORD
   ELSE
      DELETE RECORD
      BLANKREC
   ENDIF

   YOU CAN OPEN A TABLE ALSO WITH ANEW CONNECTION
   USE "CTABLE@ CON SRING" ALIAS "TABLE"

   ANY INDEX FUNCTION OR VARIABLES MUST BE EVALUAED BEFORE SENT TO ADO

   BESIDES THESE CHANGES APP SHOULD RUN WITHOUT ANY CODE LOGIC CHANGE


   PLEASE REPORT ANY BUGS! THANKS!   */
   
   
    SET ADO TABLES INDEX LIST TO { {"TABLE1",{"FIRST","FIRST"} }, {"TABLE2" ,{"CODID","CODID"}} }
    SET ADODBF TABLES INDEX LIST TO {  {"TABLE1",{"FIRST","FIRST"} }, {"TABLE2" ,{"CODID","CODID"}} }
    SET ADO TEMPORARY NAMES INDEX LIST TO {"TMP","TEMP"}
    SET ADO FIELDRECNO TABLES LIST TO {{"TABLE1","HBRECNO"},{"TABLE2","HBRECNO"}}
    SET ADO DEFAULT RECNO FIELD TO "HBRECNO"
    SET AUTOPEN ON //might be OFF if you wish
    SET AUTORDER TO 1 // first index opened can be other
    
    SET ADO FORCE LOCK ON // Changed default to OFF
   
    //CONTROL LOCKING IN ADORDD FOR BOTH TABLE AND RECORD DONT PUT FINAL "\"
    SET ADO LOCK CONTROL SHAREPATH TO  "LOCKS" RDD TO "DBFCDX"
    
    
    // NEW FEATURES -- MAROMANO
    SET ADO VIRTUAL DELETE ON  /* DELETE LIKE DBF ?*/
    SET ADO DEFAULT DELETED FIELD TO "HBDELETED" /* defining the default name for DELETED field*/
    SET ADO FIELDDELETED TABLES LIST TO {{"TABLE1","HBDELETED"},{"TABLE2","HBDELETED"}}
    
    SET DELETED OFF
    
   IF (NewDB := !FILE(   "DADOS\test2.mdb"   ))
      //need to include complete path defaults to SET ADO DEFAULT DATABA
      DbCreate("table1;DADOS\test2.mdb;ACCESS;Marco_Note", ;
                                {{ "CODID",   "C", 10, 0 },;
                                  { "FIRST",   "C", 30, 0 },;
                                  { "LAST",    "C", 30, 0 },;
                                  { "AGE",     "N",  8, 0 },;
                                  { "HBRECNO", "+", 10, 0  } }, "ADORDD" )
                                  
                               
   endif

   //need to include complete path
   SET ADO DEFAULT DATABASE TO "DADOS\test2.mdb" SERVER TO "MARCO_NOTE" ENGINE TO "ACCESS" USER TO "" PASSWORD TO ""
    
    if hb_adoRddExistsTable( "table1") .and. NewDB
          
                                  
       SELE 0
       USE table1 ALIAS "TEST1"
       
       APPEND BLANK
       test1->First   := "HOMER si no Homer"
       test1->Last    := "Simpson"
       test1->Age     := 45
       test1->codid   := "0001"
       
       APPEND BLANK
       test1->First   := "Lara"
       test1->Last    := "Croft si no"
       test1->Age     := 32
       test1->codid   := "0002"
       test1->(dbcommit())  
    endif
    
    if !hb_adoRddExistsTable( "table2")                               
      //need to include complete path defaults to SET ADO DEFAULT DATABA
      DbCreate( "table2;DADOS\test2.mdb;ACCESS;Marco_Note", ;
                             { { "CODID",    "C", 10, 0 },;
                               { "ADDRESS",  "C", 30, 0 },;
                               { "PHONE",    "C", 30, 0 },;
                               { "EMAIL",    "C", 100,0 },;
                               { "HBRECNO",  "+", 10,0  } }, "ADORDD" )
                               
                               
       SELE 0
       USE table2 ALIAS "TEST2"
    
       APPEND BLANK
       test2->address := "742 Evergreen Terrace"
       test2->phone   := "01 2920002"
       test2->email   := "homer@homersimpson.com"
       test2->codid   := "0001"
    
       APPEND BLANK
       test2->address := "Raymond Street"
       test2->phone   := "0039 29933003"
       test2->email   := "lara@laracroft.com"
       test2->codid   := "0002"
       test2->(dbcommit())
   endif                            
                               

    if !hb_adoRddExistsTable( "table3")                               
      //need to include complete path defaults to SET ADO DEFAULT DATABA
      DbCreate( "table3;DADOS\test2.mdb", ;
                             { { "CODID",    "C", 10, 0 },;
                               { "ADDRESS",  "C", 30, 0 },;
                               { "PHONE",    "C", 30, 0 },;
                               { "EMAIL",    "C", 100,0 },;
                               { "HBRECNO",  "+", 10,0  } }, "ADORDD" )
   endif                            


   


   CLOSE ALL
   


   SELE 0
   USE table1 ALIAS "TEST1"
    
       APPEND BLANK
       test1->First   := "A HOMER si no Homer "
       test1->Last    := "A Simpson delete"
       test1->Age     := 45
       test1->codid   := "0001" 
       
   SELE 0
   USE table2 ALIAS "TEST2" 

   //LOCKING TRIAL
   GOTO 1

   IF DBRLOCK()
      MSGINFO("TABLE 2 RECORD 1 LOCKED! START ANOTHER "+;
              "INSTANCE OF APP BEFORE CLOSING THIS MESSAGE"+;
              " CHECK LOCK!")
      UNLOCK

   ELSE
      MSGINFO("TABLE 2 COULD NOT LOCK RECORD 1")

   ENDIF

    skip 1

   IF DBRLOCK()
      MSGINFO("TABLE 2 RECORD 2 LOCKED! START ANOTHER "+;
              "INSTANCE OF APP BEFORE CLOSING THIS MESSAGE"+;
              " CHECK LOCK!")
      UNLOCK

   ELSE
      MSGINFO("TABLE 2 COULD NOT LOCK RECORD 2")

   ENDIF

   GO TOP

   SELE TEST1
   GO TOP
   
   MSGINFO("BROWSE DEFAULT ORDER TABLE1")
   Browse()
   
   GO BOTTOM
      MSGINFO("BLOCKING AND DELETING LAST RECORD")
   
   IF DBRLOCK()
      DELETE
      UNLOCK

   ELSE
      MSGINFO("TABLE 1 COULD NOT LOCK RECORD 1")

   ENDIF

      GO TOP
   
   MSGINFO("BROWSE DEFAULT ORDER TABLE1 AFTER DELETE")
   Browse()
   
      GO TOP
   
   set DELETED ON
   MSGINFO("BROWSE DEFAULT ORDER TABLE1 AFTER DELETE (DELETED ON)")
   Browse()
   
   set DELETED OFF


   SELE TEST2
   GO TOP
   MSGINFO("BROWSE DEFAULT ORDER TABLE2")
   Browse()

   SELE TEST1
   SET RELATION TO CODID INTO TEST2
   MSGINFO("SET RELATION TO CODID FROM TABLE1 TO TABLE2")
   GO TOP
   DO WHILE !EOF()
      MSGINFO("Name "+TEST1->FIRST+" Address "+TEST2->ADDRESS)
      DBSKIP()
   ENDDO

   MSGINFO("BROWSE TABLE1")
   BROWSE()

   MSGINFO("CHANGE ORDER CREATE INDEX ON LAST TABLE1")
   INDEX ON LAST TO TMP
   SET INDEX TO TMP

   BROWSE()

   cSql := "CREATE VIEW CONTACTS AS SELECT TABLE1.FIRST, TABLE1.LAST,"+;
            "TABLE1.AGE, TABLE2.ADDRESS, TABLE2.EMAIL "+;
            "FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.CODID = TABLE2.CODID"
   MSGINFO("RUNING SQL "+cSql)

   TRY
      hb_GetAdoConnection():EXECUTE(cSql)
   CATCH
      ADOSHOWERROR( hb_GetAdoConnection())
   END

   SELE 0
   USE CONTACTS
   MSGINFO("BROWSING VIEW CONTACTS")
   BROWSE()
   INDEX ON ADDRESS TO TMP2
   SET INDEX TO TMP2
   MSGINFO("INDEXED BY ADRESS")
   BROWSE()

   //WORKING DIRECTLY WITH RECORDSET IN ANOTHER AREA
   MSGINFO("GET RECORDSET FOR TABLE TEST1 "+STR(SELECT("TEST1")) )

   oRs := hb_adoRddGetRecordSet(SELECT("TEST1"))

   oRs:close()
   aa := "SELECT * FROM "+hb_adoRddGetTableName( SELECT("TEST1") )+ " WHERE FIRST = 'Lara'"

   MSGINFO("NEW SELECT FOR RECORDSET TEST1 "+AA)
   oRs:open(aa,hb_adoRddGetConnection(SELECT("TEST1")))

   MSGINFO("CURRENT WORKAREA "+ALIAS())

   MSGINFO("BROWSE RECORDSET ALIAS TEST1")
   TEST1->(BROWSE())
   
   MSGINFO("DELETING TABLE IMPORTS IF EXISTS")
   if hb_adoRddExistsTable( "IMPORTS")
      cSql := "DROP TABLE IMPORTS"
      TRY
         hb_GetAdoConnection():EXECUTE(cSql)
      CATCH
         ADOSHOWERROR( hb_GetAdoConnection())
      END
   endif
    
    MSGINFO("GET TABLE IMPORTS STRUCTURE VIA DBFCDX")   
   select 0
   USE IMPORTS ALIAS IMP VIA "DBFCDX"
   aStru := dbstruct()
   use
   MSGINFO("CREATING TABLE IMPORTS VIA ADO WITH STRUCTURE")
    DbCreate( "IMPORTS;DADOS\test2.mdb", ;
                              aStru , "ADORDD" )
   MSGINFO("OPEN TABLE IMPORTS VIA ADO AND APPEND FROM DBF VIA DBFCDX ")                              
   use IMPORTS alias ADOIMP
   APPEND FROM IMPORTS via "DBFCDX"
   
   MSGINFO("BROWSE ADO IMPORTS TABLE")
   browse()

   MSGINFO("DOES TABLE1 EXISTS ON DB ?"+ValToCharacter(hb_adoRddExistsTable( "Table1") ))
   MSGINFO("DOES TABLE3 EXISTS ON DB ?"+ValToCharacter(hb_adoRddExistsTable( "Table3") ))
   MSGINFO("DOES TABLE4 EXISTS ON DB ?"+ValToCharacter(hb_adoRddExistsTable( "Table4") ))
   MSGINFO("DOES IMPORTS EXISTS ON DB ?"+ValToCharacter(hb_adoRddExistsTable( "IMPORTS") ))
   
   
   cSql := "DROP VIEW CONTACTS"
   MSGINFO("RUNING SQL "+cSql)

   TRY
      hb_GetAdoConnection():EXECUTE(cSql)
   CATCH
      ADOSHOWERROR( hb_GetAdoConnection())
   END
   DbCloseAll()

RETURN nil
 

[James Bott]

DROP TABLE

Using ADORDD or the ADO functions, is there any way to drop a table?

I know you can ZAP a table but this doesn't zero out the auto-increment fields. The table is empty but the auto-increment fields start at the last number that was used. I see that this is a good thing, but for testing I need to drop the table then re-create it so I can start auto-increment fields with 1.

OK, I can erase the entire database (mbd) but then I have to recreate all the tables (that is what I am doing now).

I don't see a drop table function in adofuncs.prg or in adordd.prg.

James

[James Bott]

Maromano,

In your tests have you been able to do an APPEND FROM... with several thousand records?

I have tried my code twice and it crashed at 548 records once, and 1354 records once (after rebooting). So, I suspect this has something to do with memory. Both APPEND FROM tests were using the same database for importing.

This is a show-stopper for doing testing on large databases.

James

[Kleyber]

James,

DROP TABLE

Using ADORDD or the ADO functions, is there any way to drop a table?

I know you can ZAP a table but this doesn't zero out the auto-increment fields. The table is empty but the auto-increment fields start at the last number that was used. I see that this is a good thing, but for testing I need to drop the table then re-create it so I can start auto-increment fields with 1.

OK, I can erase the entire database (mbd) but then I have to recreate all the tables (that is what I am doing now).

I don't see a drop table function in adofuncs.prg or in adordd.prg.

James

What about TRUNCATE TABLE? It erases all data from table and reset the auto-increment fields.

[James Bott]

Kleyber,

Thanks, I did not know about TRUNCATE TABLE.

However, I just looked at adofuncs.prg and TRUNCATE does not seem to be supported.

Regards,
James

[Armando]

James:

TRUNCATE TABLE is an ADO command, here is a sample

Code: Select all

    cCmdSql := "TRUNCATE TABLE " + cTabNam

    TRY
        oApp:oCon:Execute(cCmdSql)
    CATCH oError
        MsgInfo("La tabla " + cTabNam + " NO pudo ser limpiada",oApp:cAplicacion)
        ShowError(oError)
    END
 

Regards