ADO RDD xHarbour

[James Bott]

Armando,

Thanks, I will give it a try.

James

[James Bott]

I am having trouble with AdoCreateTableSQL(). It errors out.

Here is the section of my code:

Code: Select all

   // Read structure of DBF   
   aFields := dbstruct( "ptime")
   aFields := aadd( aFields, { "HBRECNO", "+", 10, 0 } ) // New field (required)

   // Create SQL table
   lAddAutoInc:=.f. // already added autoinc field above
   FW_AdoCreateTableSQL( "ptime", aFields, oCon, lAddAutoInc )

And here is the error:

Code: Select all

Application
===========
   Path and name: C:\Users\James\Documents\Projects\ADORDD\Test8.exe (32 bits)
   Size: 2,987,008 bytes
   Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20141106)
   FiveWin  Version: FWHX 15.05
   Windows version: 6.2, Build 9200 

   Time from start: 0 hours 0 mins 6 secs 
   Error occurred at: 08/02/15, 13:14:04
   Error description: Error BASE/1604  Argument error: SWITCH
   Args:
     [   1] = C   

Stack Calls
===========
   Called from: .\source\function\ADOFUNCS.PRG => ADOCREATECOLSQL( 741 )
   Called from: .\source\function\ADOFUNCS.PRG => FW_ADOCREATETABLESQL( 709 )
   Called from: Test8.prg => MAIN( 51 )

System
======
   CPU type: AMD A8-4555M APU with Radeon(tm) HD Graphics    1600 Mhz
   Hardware memory: 3271 megs

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

   Windows total applications running: 3
      1 GDI+ Window,                                                                                                     
      2 GDI+ Window, C:\Windows\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.9600.17415_none_dad8722c5bcc2d 
      3 Task Switching, C:\Users\James\Documents\Projects\ADORDD\Test8.exe                                                  

Variables in use
================
   Procedure     Type   Value
   ==========================
   ADOCREATECOLSQL
     Param   1:    C    "+"
     Param   2:    N    0
     Local   1:    C    " [+]"
     Local   2:    U    
     Local   3:    C    ""
     Local   4:    U    
     Local   5:    U    
   FW_ADOCREATETABLESQL
     Param   1:    C    "ptime"
     Param   2:    A    Len:    4
     Param   3:    O    Class: TOLEAUTO
     Param   4:    L    .F.
     Local   1:    A    Len:    4
     Local   2:    U    
     Local   3:    U    
     Local   4:    N    2
     Local   5:    U    
     Local   6:    N    0
   MAIN
     Local   1:    L    .F.
     Local   2:    C    "Northwind.mdb"

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

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

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

Memory Analysis
===============
      368 Static variables

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

I am using the Northwind.mdb of Access.

Ideas, anyone?

James

[James Bott]

Ignore my previous message--I found a couple of errors, and now it is working.

James

[James Bott]

Armando,

I tried your "truncate table.." command but it errors out, and I don't have your showErrror( ) function so I don't know what the error is. Any ideas? Does the table need to be locked or something?

James

[James Bott]

Armando,

OK, I just changed the command to DROP TABLE...and that worked. Problem solved.

Thanks,
James

[Armando]

James:

Cool !

Regards

[James Bott]

Speed Test Results
Using the new ADORDD and a local drive.

Success at last. I have been able to import a large number of records from a DBF into a SQL table in an ACCESS database using the APPEND FROM command.

Appending 26,191 records with 31 fields from a DBF into an ACCESS table: 18 minutes.

Appending the same records from a DBF into a DBF: 1 second

Hmm, are all SQL databases that slow?

James

[Antonio Linares]

James,

Maybe append is slow but other operations are very fast

[AHF]

James, Antonio,

Im back from holidays and Im glad to see that more people is using ADORDD.
New functionalities have been added by maromano and Ill give it a look asap.

Im using adordd with MySql and I dont have any speed issues.
Normally to import dbf files I use :

Code: Select all

SELECT 0
USE XTABLE ALIAS XTABLEX VIA DBFCDX
DBGOTOP()
COPY TO XTABLE WHILE !EOF() VIA "ADORDD"
 

This is quite fast not the times James is getting with ACCESS with APPEND FROM.

Lucas report me some issues with copy to. Ill will give it a look asap.

Please note that when source area its not a adordd the copy to or append from its not controlled by adordd but by the source area rdd only the replaces and appends are done by adordd.
Please check ado_trans() in adordd.

[James Bott]

Welcome back AHF. I am excited to have your expertise available again.

I like the COPY TO example. One concern I have is the HBRECNO field. I presume you need to first create the SQL table with the added HBRECNO field before running your code?

James

[AHF]

James,

No thats done by adordd.
In ado_create the field defined as recno is taken care automatically so you dont need to care about it.
The field will be always created with the name defined as default or to that table in adordd sets.
If the field is defined already in the dbf structure and not as autoinc field it will be changed accordingly in sql.

I notice that maromano added a new field to be used a deleted record control and I dont know if he added support for it in ado_create. Ill get into it.

Resuming every time you create a new sql structure adordd enforces that the recno field its always present you dont need to defined it yourself in the new structure.
This is done in:

Copy to
Append from
Dbcreate
etc.


ado_create code

Code: Select all

/*
   fix to add HBRECNO if it´s not present  // Lucas De Beltran 23.05.2015
   cannot be first otherwise copy to changes all fields order and values ahf 23.5.2015
   */
   n := ASCAN( aWAData[ WA_SQLSTRUCT ],{ |x| x[1] = ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ] ) }  )
   IF n == 0
      AADD( aWAData[ WA_SQLSTRUCT ], {  ADO_GET_FIELD_RECNO(  aWAData[ WA_TABLENAME ] ), '+', 10, 0 } )

   ELSE  //FIX AHF CAN ALREADY EXIST AND NOT TRUE INC FIELD
      aWAData[ WA_SQLSTRUCT ][n,2] := "+"
      aWAData[ WA_SQLSTRUCT ][n,3] :=  11
      aWAData[ WA_SQLSTRUCT ][n,4] := 0

   ENDIF
 

[James Bott]

AHF,

Thanks for that explanation.

I tried your COPY TO method but it still took 16 minutes and 42 seconds. So, it is not significantly faster that APPEND FROM.

I think the speed issue has to do with locking. One reason is that I found that you have to force locking on or the APPEND FROM will crash at some point (unless maybe there are only a very few records).

SET ADO FORCE LOCK ON // must be ON to do APPEND FROM...

Maybe you have some ideas about this?

James

[James Bott]

To put this APPEND FROM speed issue in perspective, if you need to import 1 million records it will take:

1000000/26000 = 38 X 18 minutes = 692 minutes/million or 692/60 minutes = 11.5 hours.

If you rarely need to import records, then this is probably not a big issue. I do know some people on this forum have mentioned having 2 million records in a single DBF, so just to convert the data to SQL could take days.

James

[Kleyber]

James,

Speed Test Results
Using the new ADORDD and a local drive.

Success at last. I have been able to import a large number of records from a DBF into a SQL table in an ACCESS database using the APPEND FROM command.

Appending 26,191 records with 31 fields from a DBF into an ACCESS table: 18 minutes.

Appending the same records from a DBF into a DBF: 1 second

Hmm, are all SQL databases that slow?

James

To make a comparison, try using mu DBF2SQL and export one or many DBF tables into your SQL tables and see the speed. Of course, DBF2SQL is not using ADORDD, but SQLLIB, but the SQL database can be the same.

[James Bott]

Kleyber,

That may be an option. I downloaded it and will take a look.

I did see this in another message thread, "It imports DBFs into MySQL or PostgreSQL databases" and since for testing I am using ACCESS right now it won't be helpful.

I do think the ADORDD issue can be solved. I expect maybe it is doing a COMMIT after adding each record. From my previous experience, this will REALLY slow things down.

Regards,
James