James,
Glad to know. 
By the way did you make all that with APPEND FROM or and COPY TO?
When a table is created with adordd it checks automatically if recno field exists and if not creates it.
When you use copy to from dbf to sql since it calls ado_create the recno field its automatically checked you dont need to include it in the structure.
Antonio F,
Yes, I did a COPY TO a dbf, then built the SQL table from the structure of the DBF (and I did know that the HBRECNO field would be added automatically). Then I did an APPEND FROM the dbf. Fairly simple now that I have the code written.
I am wondering about these two lists:
SET ADO TABLES INDEX LIST TO ...
SET ADODBF TABLES INDEX LIST TO ...
What is the difference between the ADO and the ADODBF designations?
Since they both contain the same lists aren't they redundant? Or, are there instances where they don't contain the same lists?
James
Antonio F,
You asked previously about:
SET ADO FORCE LOCK ON
I had a comment in my code that it was required, and you asked why. Well, I found that if you try to do an APPEND FROM with lots of records it will crash without the above statement. I had it crash once at 500 something records and another time at 1300 something records. So there is something going on that is not consistent which leads me to believe it may have to do with memory and/or timing. I am also wondering if it is doing a commit after each record which can account for how slow it appends records. A single commit after every 100 records would increase the speed 100 times. I really can't see any reason why it would need to do a commit after each record.
James
A) SET ADO TABLES INDEX LIST TO ….
----------------------------------------------------
This Set is used by the SQL engine to build select with order by.
Thus the fields must be separated by comma and it can include SQL functions or ASC DESC This Set cannot include Clipper/(x)Harbour functions as they are unknown to SQL.
Example:
SET ADO TABLES INDEX LIST TO { {"TABLE1",{"FIRST","FIRST DESC"} }, {"TABLE2" ,{"CODID","CODID"}} }
B) SET ADODBF TABLES INDEX LIST TO…
-----------------------------------------------------------
This Set is used to evaluate Clipper/(x)Harbour expressions such as:
&( indexkey( 0 ) )
OrdKey( )
Etc.
So it must contain your actual real index expressions.
Example:
SET ADODBF TABLES INDEX LIST TO {
{"TABLE1",{"FIRST","FIRST"} }, {"TABLE2"
,{"CODID","STR(CODID,2,0)"}} }
Summarize
When we use &(indexkey(0)) we can’t evaluate an index expression in ADO TABLES “NAME, DDATA, NVALUE” we will get an error but we can issue SQL SELECT like that.
Thus we need ADODBF TABLES expression “NAME+DTOS(DDATE )+STR(NVALUE) “ to do it.
So both must be defined one for SQL the other real index clipper type expressions.
James Bott wrote:
Antonio F,
You asked previously about:
SET ADO FORCE LOCK ON
I had a comment in my code that it was required, and you asked why. Well, I found that if you try to do an APPEND FROM with lots of records it will crash without the above statement. I had it crash once at 500 something records and another time at 1300 something records. So there is something going on that is not consistent which leads me to believe it may have to do with memory and/or timing. I am also wondering if it is doing a commit after each record which can account for how slow it appends records. A single commit after every 100 records would increase the speed 100 times. I really can't see any reason why it would need to do a commit after each record.
James
Antonio F,
Thanks for the detailed explanation of the indexes. It helps a lot. I thought we were going to have to give up the FW functions, but it seems not. This is great news to me.
James
What do you mean by "memory and/or time"?
COPY TO and APPEND FROM the source area rdd calls ado_append and then ado_putvalue for each record. Both these functions call :update() each time. This might be the reason of taking so long?
Do you mean we should change the :cursorType to adLockBatchOptimistic start a transaction and :updatebatch only at the end?
May be there is a bug in locks as it should in COPY TO open the destination table exclusively only in APPEND FROM ADORDD locks record by record.
Antoino F,
I forgot to mention that an UPDATE forces the disk buffer to be written to disk, so this the reason the update is so slow. If there is only an UPDATE after 100 records (or even 500) there is still probably (depending on the buffer's size) only one disk write, thus the great increase in speed.
James
James,
Unfortunately I haven't find any way to adordd copy to has enough speed to allow copy to from dbf to sql millions of records. It is really to slow.
adordd copy to is good for copying a small set of records ( couple hundreds ) from or to dbfs or from sql to sql.
In order to import big dbfs file to sql you can use adofuncs that might do the job much quicker.
I've tried to work with :updatebatch() but then I have then problems with recno autoinc field.
May be Mr Rao can help on this subject.
I've made in meantime some improvements in speed and corrected some index management to mimic exactly index behavior and copy to now copies all records both with SET ADO FORCE LOCK ON or OFF.
Ill post a new version within the next days.
Antonio F,
Thanks for the update.
I didn't see any mention of changing the commit frequency. Have you looked at that?
I will be looking forward to the updated ADORDD.
I have been working on tests here comparing the results of ADORDD vs DBFCDX drivers and I haven't found any problems yet (other than the APPEND speed). Thanks again for all your work.
James
//get adordd connection to have it in same transaction processing
hb_GetAdoConnection():Execute( "SELECT * INTO newfile FROM currentfile WHERE expression" )
USE newfile /this is adordd
....If there is any way to get from the code block the char expression
Yes, I did a COPY TO a dbf, then built the SQL table from the structure of the DBF (and I did know that the HBRECNO field would be added automatically). Then I did an APPEND FROM the dbf. Fairly simple now that I have the code written.
USE dbf VIA "DBFCDX"
COPY TO sqltable VIA "ADORDD" //if not the default rddNew version of ADORDD at https://github.com/AHFERREIRA/adordd.git
Changes:
Faster opening recordsets
Recordsets are now opened by recno order when no index is active as any dbf
Tables without auto inc fieldto be used as recno throws an error whe opening it
Reccount faster
Copy To (from other rdd to adordd) works ok with both set ado force locks on / off
#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
APPEND BLANK
REPLACE FIELD->FIRST WITH "Pieter2"
// APPEND FROM customer2 via "DBFCDX"
BROWSE()
Return nil