ADORDD FAQs

AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

ADORDD FAQs

Post by AHF »

After several users and client questions I decided to compile them all in adordd faqs.
Hope it will help you deciding to port your app to SQL.

1) Is adordd pure ADO ?

Yes

2) Does it work like another rdd without any limitations ?

Yes and its even more compatible with clipper dbf kind of rdds than ADS rdd.
You don't need anymore to be stuck to dbf kind of rdds.


3) Can I just link it with my app and run?


No.
You need to pre configure adordd with a couple of SETs in the top of your app prg,
but you don't need to alter any of your app code.
Please check 01_README.PDF and tryadordd.prg at github.
All this will take only a couple of minutes.


4) How do I configue adordd ?

At your initial or main procedure place:

Code: Select all

#INCLUDE "adordd.ch"

    RddRegister("ADORDD",1) 
    RddSetDefault("ADORDD") 

    IF RDDSETDEFAULT() == "ADORDD"

       //clipper index expressions as used by your app.
       //if your app uses dynamic build tables names place here a tablename only the static part of it.
       SET ADODBF TABLES INDEX LIST TO check 01_readme.pdf for arrray spec
       
       //temporary index names static part of index name
       SET ADO TEMPORAY NAMES INDEX LIST TO {"TMP","TEMP"}
       //you should place here all index expressions that change the type or en of a field and or your UDFs 
       SET ADO INDEX UDFS TO {"IF","&","SUBSTR","==","DESCEND","MYFUNC"}

       //only needed for engines not supporting these kind of fields ex oracle sqlite access
       SET ADO TABLES LOGICAL FIELDS LIST TO { { "ACESS", { "PODEFAZER" } }  }
   
       //only needed for engines not supporting exact decimal field notation ex access sqlite
       SET ADO TABLES DECIMAL FIELDS LIST TO ;
                      { {"ENCCLIST",{"PRECOVENDA",4,"PRECOVEEST",4 } },;
                        {"ENCFOCLI", {"PRECOUNIT", 4 } } }

       Both these sets are written for you by adordd when uploading the tables.

       //defaut field to be used as recno
       SET ADO DEFAULT RECNO FIELD TO "HBRECNO"
       //only needed if diferent for tables with different recno field from the default
       //SET ADO FIELDRECNO TABLES LIST TO {{"TABLE1",{"ID"}},{"TABLE1",{"ID"}}}
       //field to be used as deleted flag
       SET ADO DEFAULT DELETED FIELD TO "HBDELETE"
       //only needed if diferent for tables with different deleted flag field from the default
       //SET ADO FIELDDELETED TABLES LIST TO {"CLIENTE","HBMYDLETED1" }

       // if you want to have lock control as in any other rdd.
       // you will need to have a share path to it
       // working in WAN you will need for ex a VPN to this share path.
       //SET ADO LOCK CONTROL SHAREPATH TO  "d:\followup-testes" RDD TO "DBFCDX"
       SET ADO FORCE LOCK OFF

       // here connection gets active
       SET ADO DEFAULT DATABASE TO 

       //upload the database path structure as part of the table name.
       //suppose that you have same table name in different folders.
       // without tis set you could not upload both.
       // with thsi set the path becomes part of the table name.
       SET ADO TABLENAME WITH PATH ON
       // uploaded with one path working now with another
       SET ADO ROOT PATH TO .... INSTEAD OF .....
         
       //this set has an impact on performance as app execution continues
       //before recordset itsstill being build.
       SET ADO CACHESIZE TO 50 ASYNC ON ASYNCNOWAIT ON

       //pre open recordset at startup for faster acces during app execution
       SET ADO PRE OPEN THRESHOLD TO 5000

    ENDIF

 
5) Does it works with xHarbour and Harbour ?

Yes.


6) How do I port my dbfs to SQL and how long can it take?

Place in your initial procedure after all adordd SETs:

Code: Select all

hb_AdoUpload( "C:\My root databse path", "DBFCDX", "MY SQL CHOSEN ENGINE", .f. )
 
Choosing the last parameter .f. lOverwrite it allows you if you need to interrupt
porting of dbfs to SQL to run it again from the last uploaded table.
After loading it your app will continue execution now in the SQL chosen engine.
You can after comment that line to avoid run it again when app starts again.

Depending n your tables average size (millions of records) it can take long time.
If this is your case leave it uploading during night.


7) Can I open tables with million of records without a where clause?

Yes.
In this case you might use SET ADO THRESHOLD to wait a little more in app start up but
it will be extremely fast during execution.


8) When I open big tables several times during app execution doesn't this lead to
out of memory or very slow opening of that table?

Adordd builds each table recordset only once.
If your table recordsets have million of records you will need enough memory for it and it can easily
reach some hundred MB or more.
When you open that table again the memory consumption its minimum as adordd clones that table set, which
means a kind of pointer to previous open recordset being extremely fast opening it again and very
low on memory use.


9) Can I use other SQL classes or function like adofuncs with adordd ?

Yes.
You can use the traditional navigational approach like clipper or SQL approach with INSERT, UPDATE, etc or a mix of both.
Adordd will have always the table recordset updated with table last version no matter if for ex updates are made
through functions or SQL statments through adordd or any other classes or functions.


10) But aren't there any restrictions using adordd and don't I need have to change any of my app code?

No restrictions.
No app change code.It works like any other dbf kind of rdd.


11) Is it fast like my dbf rdd application?

Its not so fast as dbf kind of rdd if your app has only 1 to 5 users but almost as fast.
The point is that adordd will not have performance degradation if your app works with 5 or 300 users
and neither on LAN or WAN as opposite to dbf rdd.

Adordd will need minimum working load on your SQL engine server.


12) Does it has transaction control ?

It has everything your SQL engine might offer.
If your chosen SQl engine is Transactional you will have transactions control in adordd.


13) What SQL engines can I use ?

ADS
ACCESS
ANYWHERE
DBASE
POSTGRE
FIREBIRD
FOXPRO
INFORMIX
MSSQL
MYSQL
ORACLE
POSTGRE
SQLITE

But others can be easily added to be supported.


14) What about if MS stops development or support to MS ADO in future?

Adordd its prepared to work with any other user class that provides the few methods and data
adordd uses from MS ADO.
This class could use arrays or dbfs as recordsets and interface through C to each SQL library bypassing
ODBC which might be much faster.
Regards
Antonio H Ferreira
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

How do I solve error Called from UR_SUPER_ADDFIELD(0) ?

Solution:

must link tryadordd.prg with addorrd.prg, and not use hbusrrdd.ch from adordd repository (rename it or delete of your project, HB has your own hbusrrdd.ch in HB32dev\include)
Regards
Antonio H Ferreira
User avatar
James Bott
Posts: 4654
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA
Contact:

Re: ADORDD FAQs

Post by James Bott »

Antonio F.,

Thanks for posting this. It is very informative and helpful.

Regards,
James
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

I try to upload with hb_AdoUpload() however some tables with a couple of million of records take an eternity to be uploaded. Its impossible!
Solution:
In this case you have to upload these tables with some other tool.
Please try this:

Code: Select all

//assuming that default rdd is adordd.

afiles //files to be uploaded from some folder

for n:= 1 to len( afiles )
    use afiles[n] VIA "DBFCDX" // might be another like ADS

    if lastrec() > ?? //your threshold
       //to avoid overwrite existing tables you must check before if table exists
       copy to sqltable while recno() < 1  //creates empty table at SQL 
       copy to cFileNoExt( afiles[n] )+".csv" while !eof() DELIMITED WITH <your delimiter>  //all your data exported

    else
       copy to sqltable while !eof() // other smaller tables are exported directly

    endif

next
 
This should be pretty fast!

After running this you will have all your tables uploaded to SQL engine.
The tables within your threshold will be empty (only structure has been created) and you'll have all exported data in tablename.csv file name.

Now you must find a way through some other available tool to rapidly import data from each .csv file name
into each SQL table.
Every SQL engine has some tool to do this job.
Regards
Antonio H Ferreira
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

adordd its not closing the connection when exiting the app.

Solution:

ado_exit from adordd should be auto called when exiting the app however it seems
that without QUIT command thats not true in some conditions.
If you want to close all tables,sets and connection place before exiting in your app a call
to ADODB_CLOSE()
Regards
Antonio H Ferreira
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

If I upload my tables with or without SET PATH ON and then I run my app from a different folder or set path
where there aren't any files (tables, indexes etc) at all and adordd its not working.

Solution:

If you are running from a different folder where aren't any files function file() tests in your app become invalid
because this function only checks the file system for the file.
If you keep the same folder and files you don't need to alter your app although these files are only there for
file() testing and since the files exist on SQL engine its ok.

If for ex you do this:

if !file("mypath\myfile")
dbcreate( "mypath\myfile"...

If that file gets deleted at the file system it will be created both at File System (fake empty file)
and at the SQL engine the true table file otherwise not.
If does not exist at the file system and exists at SQL then adordd recreates the file at SQL without any warning!

So changing to a folder where these file() tests are not working anymore you need to alter all the occurrences
of File() to hb_AdoRddFile() in order to look first at SQL if file exists and if not then at File System.

hb_AdoRddFile() detects if tables (table extension) or indexes (index extension) and if other extension checks
only file system for that file.
So you only do a replace in all your prgs files File(.. BY hb_AdoRddFile(.. keeping everything else exactly the same.

So the ex:

if !file("mypath\myfile")
dbcreate( "mypath\myfile"...

Its now working with SQL even if that file doesn't exist at file system.

Temporary files (index, tables) you don't need to do anything because adordd creates fake files
at the file system as indicate in your app and at SQL engine so your app continues to work exactly the same.

hb_AdoRddFile() will work with any other rdd so you can use it as a replacement for File() in any conditions.

Besides this if you uploaded all your files with SET ADO PATH ON you need now to SET ADO ROOT PATH TO "NEW DRIVE PATH" INSTEAD OF "OLD DRIVE PATH" otherwise the compound table name "mypath_tablename" will not work anymore because adordd now tries to open "new_drive_path_tablename" that does not exist.

If you uploaded tables with SET ADO PATH OFF you don't need to do nothing.
However please note that if you have the same table name in different folders uploading it like this
it will not be possible to have duplicated table names.

This can be set dynamically at your app accordingly to the path set by your app code so that it can run from
anywhere.
Regards
Antonio H Ferreira
dagiayunus
Posts: 69
Joined: Wed Nov 19, 2014 1:04 pm
Contact:

Re: ADORDD FAQs

Post by dagiayunus »

Dear Sir

How to connect to MSSQL using ADORDD and Create Database/Table ?

Thanks & Regards
Yunus.
Dagia Yunus.
Rajkot, India

FWH 17.04
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

Yunnus,

Please check the first post.

You need previously create the database.
Then:

SET ADO DEFAULT DATABASE TO "your dbname" SERVER TO "your server name or IP" ENGINE TO "MSSQL" USER TO "your user name" PASSWORD TO "your passord"

Then you can upload all tables with:

hb_AdoUpload( "C:\Your root database path", "DBFCDX", "MSSQL", .f. )

Please check all other necessary sets at 01_readme.pdf at GitHub.
Regards
Antonio H Ferreira
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

I cant seek case sensitive strings!
Is it a bug?
So must I SET ADO UDFS when I use UPPER or LOWER in index expression?
Solution:

It seems as far as I know that ADO :find used for dbseek by ADORDD its not case sensitive.
I don't know if it is DB engine limitation or ADO itself. It seems to be ADO.
So seeking "test seek" or "TEST SEEK" or "Test Seek" will find whatever is the first occurrence in the table for ex: "tesT seeK".
Its not a bug it seems to be a limitation of ADO :Find.
Since normally we convert strings to Upper or Lower to be seeked its not serious and it seems not to disrupt any functionality in converted apps.
Using case conversion in index expressions like Upper or Lower you dont have to included it in SET ADO UDFS.
This will avoid special index creation which is must faster.
Please note however that LOCATE its case sensitive.
Regards
Antonio H Ferreira
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

Table structure its not exactly the same as in DBF mainly numeric fields.
I uploaded tables with FW_AdoImportFromDBF
Solution:

If field sizes are not exactly the same as in the DBfs this brings problems in adordd code and does not allow proper working ex. index expressions.
You must upload your tables with hb_AdoUpload() if it takes too long because of the size of the tables check previous FAQS on how to do it.
adordd must create itself the tables to be in accordance to adordd needs.
Regards
Antonio H Ferreira
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

In SET ADO FIELDRECNO TABLES LIST TO what table name must a put here?
The dbf table name or the table name built by adordd with path.
Solution:

In all adordd SETs where you place a table name its always the table name used by former rdd (adsrdd, dbfcdx ,ect) adordd will make all necessary conversions for you.
I have a table that the name is built dynamically cTabe+str(numuser) . How can I place it here?
Solution:

Concerning dynamic table names you only place in any SETs where table names are needed that static part of the name of that table and adordd will do the rest.
Regards
Antonio H Ferreira
User avatar
gautxori
Posts: 69
Joined: Thu Feb 25, 2010 12:44 pm
Location: Plentzia (Bizkaia)

Re: ADORDD FAQs

Post by gautxori »

Ante todo Buenas Tardes Antonio (AHF), estoy haciendo pruebas con ADORDD y he logrado conectarme a la BBDD
pero cuando intento hacer USE de una tabla sencilla para probar da el siguiente error marcado en rojo

Entorno de desarrollo
FWH 16.03
Harbour 3.2
adordd de fecha 08/12/2015
MySQL 5.3

He probado a compilar/linkar con los dos (ADORDD Y TRYADORDD) con el mismo resultado, varias pruebas comentando y descomentando líneas, conectarse parece que se conecta pero da el error UR_SUPER_ADDFIELD( 0 ),
alguna idea de que puedo esta haciendo mal ¿?
PRG = \
Obras32.PRG \
adordd.PRG \
tryadordd.PRG \



Programa
------------------
#include "FiveWin.ch"
#include "adordd.ch"
...
...
...
//
REQUEST ADORDD
RddRegister( "ADORDD",1 )
RddSetDefault( "ADORDD" )

SET ADO DEFAULT RECNO FIELD TO "ID"
// SET ADO TEMPORAY NAMES INDEX LIST TO {"TMP","TEMP"}
// SET ADO FIELDRECNO TABLES LIST TO {{"EMPRESA","ID"}}
// SET AUTOPEN OFF
// SET ADO FORCE LOCK OFF
SET ADO DEFAULT DATABASE TO "EMPRE01" SERVER TO "localhost" ENGINE TO "MYSQL" USER TO "root" PASSWORD TO "1234"

/*
SET ADODBF TABLES INDEX LIST TO { ;
{"EMPRESA",{"EMPRESA","NUMEMP_000"} }, {"APUNTES" ,{"APUIND01","AP_OBRA+DTOS(AP_FECHA)"}} }
*/
Try
SET ADO DEFAULT DATABASE TO "EMPRE01" SERVER TO "localhost" ENGINE TO "MYSQL" USER TO "root" PASSWORD TO "1234"
// Alert ("BBDD MySql ABIERTA")
Catch oErr
Alert ("No puedo abrir MySql")
End

Alert ("Antes de Browse")
USE "EMPRESA" alias Empre SHARED NEW
Alert ("Despues de Browse")


------------------



===========
Path and name: D:\OBRAS32\Obras32.exe (32 bits)
Size: 3,751,936 bytes
Compiler version: Harbour 3.2.0dev (r1601050904)
FiveWin Version: FWH 16.03
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 1 secs
Error occurred at: 04/13/16, 18:29:20
Error description: Error ADORDD/1003 Argument error

Stack Calls
===========
Called from: => UR_SUPER_ADDFIELD( 0 )
Called from: .\SOURCE\adordd.PRG => ADO_OPEN( 0 )
Called from: => DBUSEAREA( 0 )
Called from: .\SOURCE\Obras32.PRG => MAIN( 0 )

System
======
CPU type: AMD Turion(tm) 64 X2 Mobile Technology TL-60 2000 Mhz
Hardware memory: 3839 megs

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

Windows total applications running: 5
1 ,
2 , C:\WINDOWS\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.10240.16384_none_3bccb
3 , D:\OBRAS32\Obras32.exe
4 DDE Server Window, C:\WINDOWS\SYSTEM32\OLE32.DLL
5 GDI+ Window, C:\WINDOWS\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.10240.16603_none_d15a3826af6dc

Variables in use
================
Procedure Type Value
==========================
UR_SUPER_ADDFIELD
Param 1: O Class: ERROR
ADO_OPEN
Param 1: N 1
Param 2: A Len: 7
Local 1: U NIL
Local 2: U ???:U
DBUSEAREA
Param 1: N 1
Param 2: A Len: 8
Local 1: A Len: 42
Local 2: U NIL
Local 3: A Len: 7
Local 4: U NIL
Local 5: U NIL
Local 6: O Class: TOLEAUTO
Local 7: N 16
Local 8: N 1
MAIN
Param 1: L .T.
Param 2: U NIL
Param 3: C "EMPRESA"
Param 4: C "Empre"
Param 5: L .T.
Param 6: L .F.

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

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

Classes in use:
===============
1 ERROR
2 HBCLASS
3 HBOBJECT
4 TINI
5 WIN_OLEAUTO
6 TOLEAUTO
7 TFONT
8 TWINDOW
9 TDIALOG
10 TBRUSH
11 TCONTROL
12 TICON
13 TMULTIGET
14 TBUTTON
15 TRECT
16 TREG32

Memory Analysis
===============
1809 Static variables

Dynamic memory consume:
Actual Value: 0 bytes
Highest Value: 0 bytes
Un saludo
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

AHF wrote:How do I solve error Called from UR_SUPER_ADDFIELD(0) ?

Solution:

must link tryadordd.prg with addorrd.prg, and not use hbusrrdd.ch from adordd repository (rename it or delete of your project, HB has your own hbusrrdd.ch in HB32dev\include)
Regards
Antonio H Ferreira
User avatar
gautxori
Posts: 69
Joined: Thu Feb 25, 2010 12:44 pm
Location: Plentzia (Bizkaia)

Re: ADORDD FAQs

Post by gautxori »

Buenas tardes Antonio, antes de enviar este mensaje ya había probado lo que me dices con el mismo resultado por eso he puesto este mensaje

pero no entiendo bien lo que comentas, yo lo que entiendo por linkar tryadordd.prg con addorrd.prg es meterlos en mi proyecto en el archivo .rmk así
______________________________
PRG = \
Obras32.PRG \
adordd.PRG \
tryadordd.PRG \

etc.......
echo obj\Obras32.obj obj\adordd.obj obj\tryadordd.obj
.......
_____________________

El programa tryadordd.prg no veo que tenga que ver con lo que yo quiero hacer que es conectarme a MySql. veo un ejemplo para ACCESS ¿???,


Ya he repasado mil veces las instrucciones y el programa y no veo el porque del error.
de todas formas he dejado otro mensaje sobre este problema aquí (no se como incluir el TOPIC ¿?)
-------------------------------------------------------
Re: Nuevo AdoRDD (libre)

Notapor gautxori » Sab Abr 16, 2016 6:09 am
-------------------------------------------------------

muchas gracias por las molestias
Un saludo
___________________________________________________
La mente es como un paracaídas, solo funciona si se abre
Harbour 3.2.0dev (r1601050904) , Fivewin 16.04
AHF
Posts: 837
Joined: Fri Feb 10, 2006 12:14 pm

Re: ADORDD FAQs

Post by AHF »

You dont need tryadordd you can remove it.

Please be sure that you are using the correct hbusrrdd.ch. Check FAQS.
The error UR_SUPER_ADDFIELD(0) is due to incorrect version of this ch file.

Show me the obras32.prg where you have all adordd SETs.

Please report adoversion() returned value.
Regards
Antonio H Ferreira
Post Reply