ADO RDD xHarbour

[pieter]

Hello Antonio F and others,

I have a question about adordd, I want to understand better how adordd works.

Code: Select all  Expand view  RUN

#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

Question: where are Commands, like USE, APPEND BLANK, REPLACE FIELD etc translated into ado code? I tried to find it in adordd.prg, adordd.ch, but I could not find it.

Best regards,

Pieter

[AHF]

Pieter,

adordd its coded at usr rdd level so there isn't any translation into ado code.
It is the default rdd that is replaced by adordd like ads rdd, dbfcdx rdd or any other rdd.
All rdd table and index functions will work exactly the same.
Nothing changes in your code.

In adordd.prg for ex the REPLACE or Fieldput calls the ADO_PUTVALUE.
The rdd fuctions are "mapped " to the ado functions in ADORDD_GETFUNCTABLE.

This is why the level of app code compatibility its so high with adordd against other approaches.
Syntax is exactly the same.
But you can also work (getting the work area recordset or connection) with SQL directly using functions like those in adofuncs.prg or executing SQL statements.

Resuming you can wok only ISAM based or SQL based or both. Its your choice.
Besides this the code will be totally compatible with standard (x)Harbour rdd code so it will work exactly the same if you change the adordd to any other.

[pieter]

Antonio,

Thank you:D. I think I understand the concept partially, but I also think there is still some things to learn for me.

So is it true that usrrdd.lib and/or rdds.lib in the xharbour123/lib directory implement the rdd. If yes, what is then the difference between usrrdd.lib and adordd.lib?
Are usrdd.lib and rdds.lib written in C language?

In my Clipper 5.2 book from Rick Spence there is a picture which looks like this:
Database commands -> RDD Interface -> Database driver - Storted data.

I understand Database commands (USE, APPEND BLANK) and storted data (dfb file/sql). I understand the main concept of a rdd (Replaceabel database driver, a layer, which one can change, and the code can stay same, even when you have another database system under it), but the differences between RDD interface and Database driver is not yet clear for me. Do usrdd.lib and rdds.lib and adordd.lib belong to RDD interface or to the database driver?

I am not sure whether I asked questions very well or if they are really importent. But if you have some tips that would be welcome.

Thanks again.

Best regards,

Pieter

[AHF]

Pieter,

Im not a rdd expert at this level.
May be Antonio Linares can explain it better.

So is it true that usrrdd.lib and/or rdds.lib in the xharbour123/lib directory implement the rdd. If yes, what is then the difference between usrrdd.lib and adordd.lib?
Are usrdd.lib and rdds.lib written in C language?

I see usrrdd as an interface to allow us programming new rdds.
Its usrrdd that organizes all work areas data memory spaces etc.
usrrdd for itself it does nothing.
Yes check usrrdd.c

In my Clipper 5.2 book from Rick Spence there is a picture which looks like this:
Database commands -> RDD Interface -> Database driver - Storted data.

I think in adordd it is:
Database commands -> ADORDD -> ADO FRAMEWORK - Storted data.
or
Database commands -> USRRDD -> ADORDD - Storted data.

I understand Database commands (USE, APPEND BLANK) and storted data (dfb file/sql). I understand the main concept of a rdd (Replaceabel database driver, a layer, which one can change, and the code can stay same, even when you have another database system under it), but the differences between RDD interface and Database driver is not yet clear for me. Do usrdd.lib and rdds.lib and adordd.lib belong to RDD interface or to the database driver?

See above.

What is the reason for all these questions ? What are you looking for?

[James Bott]

Antonio F,

Before I spend time testing this, I thought I should ask.

First, I thought we were not supposed to be able to use any functions in SQL indexes, but then I saw you mentioned using (x)Harbour functions such as DTOS(DATE).

I am wondering if user-defined functions can be used? I have tried one and it is sorting the data, but it isn't sorting the same way it does when using the DBFNTX RDD, so I am assuming we can't.

I hope I'm wrong. Maybe I have the syntax wrong.

James

[AHF]

James,

First, I thought we were not supposed to be able to use any functions in SQL indexes, but then I saw you mentioned using (x)Harbour functions such as DTOS(DATE).

Code: Select all  Expand view  RUN

   //SQL  CAN BE USED ANY SQL FUNCTIONS MATCHING CLIPPER FUNCTIONS
  // WE DONT NEED THE CONVERSION FUNCTIONS IN SQL
    SET ADO TABLES INDEX LIST TO {   {"TABLE1",{"COD1","COD+NCOD2+DCOD3"}...

   // CLIPPER EXPRESSIONS
   // WE NEED THIS OTHERWISE IF YOU DO &(INDEXKEY(0)) YOU GET AN ERROR
    SET ADODBF TABLES INDEX LIST TO {   {"TABLE1",{"COD1","COD+STR(NCOD2)+DTOS(DCOD3)"}...
   
 

Concerning UDFs the SQL engine doesnt know it so the only chance is to create these UDFs at the server and then you should be able to call it just like any other SQL function.
Remember that the counter type of that SQL function in our app must be defined in SET ADODBF INDEX LIST to enable our app to evaluate it any time.
Besides that remember that the Vars must be passed in the index expressions already evaluated as in SQL engine they are out of scope.
Never tried it but it should work.

With ADS its exactly like this.

When you said that you tried UDF this is what happens:

If used with INDEX ON the function its simply ignored and if there is a FIELD inside is simply extracted.
If used with SET ADO TABLES INDEX it should generate an error if its not defined at the server.

Please check for ex. MYsql CREATE FUNCTION

[pieter]

Pieter,

Im not a rdd expert at this level.
May be Antonio Linares can explain it better.

So is it true that usrrdd.lib and/or rdds.lib in the xharbour123/lib directory implement the rdd. If yes, what is then the difference between usrrdd.lib and adordd.lib?
Are usrdd.lib and rdds.lib written in C language?

I see usrrdd as an interface to allow us programming new rdds.
Its usrrdd that organizes all work areas data memory spaces etc.
usrrdd for itself it does nothing.
Yes check usrrdd.c

In my Clipper 5.2 book from Rick Spence there is a picture which looks like this:
Database commands -> RDD Interface -> Database driver - Storted data.

I think in adordd it is:
Database commands -> ADORDD -> ADO FRAMEWORK - Storted data.
or
Database commands -> USRRDD -> ADORDD - Storted data.

I understand Database commands (USE, APPEND BLANK) and storted data (dfb file/sql). I understand the main concept of a rdd (Replaceabel database driver, a layer, which one can change, and the code can stay same, even when you have another database system under it), but the differences between RDD interface and Database driver is not yet clear for me. Do usrdd.lib and rdds.lib and adordd.lib belong to RDD interface or to the database driver?

See above.

What is the reason for all these questions ? What are you looking for?

Antonio F,

What is the reason for all these questions ?

I want to know how things conceputally works. How the technologies work together. I think that in the long term I can then solve prolems better.

What are you looking for?

See above,

I have demonstrated a adordd test application(Topic: Test ado rdd app: viewtopic.php?f=3&t=30963) to my colleages, now we are all interested in using it. Originally we wanted to use only standard ado, but I think using adordd also, is very good.

I want to thank you and everybody else who made it possible that that we can use the code of adordd.prg and I wish your project at http://ajusera.com lots of luck:).

Best regards,

Pieter

[James Bott]

Antonio F,

Code: Select all  Expand view  RUN

//SQL  CAN BE USED ANY SQL FUNCTIONS MATCHING CLIPPER FUNCTIONS
// WE DONT NEED THE CONVERSION FUNCTIONS IN SQL
SET ADO TABLES INDEX LIST TO {   {"TABLE1",{"COD1","COD+NCOD2+DCOD3"}...

Are you saying that SQL can concatenate unlike data values automatically? Are unlike data values always converted to string, or could you add a date + 10 and get a date ten days after the date specified? (I'm not sure why you might want to do that, just wondering.)

I just looked up SQL functions for ACCESS and see there are some, but only 8 and maybe 3 or 4 of them might be useful. And none of them deal with dates. Sigh...

Later I will look up SQL functions for MySQL.

Code: Select all  Expand view  RUN

// CLIPPER EXPRESSIONS
// WE NEED THIS OTHERWISE IF YOU DO &(INDEXKEY(0)) YOU GET AN ERROR
SET ADODBF TABLES INDEX LIST TO {   {"TABLE1",{"COD1","COD+STR(NCOD2)+DTOS(DCOD3)"}...

I don't think I have ever used indexkey() before. I just read up on it, and I will have to think of where I might use it in the future. Does this only apply to indexkey() or are there other xHarbour functions too? If so, which ones?

[UDFs] If used with INDEX ON the function is simply ignored and if there is a FIELD inside is simply extracted.

When you said that you tried a UDF this is what happens:

If used with SET ADO TABLES INDEX it should generate an error if its not defined at the server.

This what I would have expected, and I did get an error when I tried two indexes--something like "Too many recursive errors." OK, I don't understand how you can define a function at the server. What language do you use? Does the server understand xHarbour? I will Google this.

The one index used a lot in the app I am trying to convert uses two different fields, both containing strings, and concatenates them into a larger string then adds the two digits of the century (either "19" or "20"). So you end up with what you would normally get with DTOS(dDate). Yes it is strangely overly complex, but I am stuck with it. Yes, I can create a third field and post the result to it every time the record is saved, and (I think) the data is always saved using my database class, so it would be simple to force this automatically within the Save() method. But I will have to view over 100,000 lines of code to insure that all data is saved via the Save() method. (I going to need lots of coffee and some reading glasses for that!).

I know, "If it was easy, anyone could do it!"

Thanks for your patience dealing with a SQL novice.

James

[AHF]

James,

Are you saying that SQL can concatenate unlike data values automatically? Are unlike data values always converted to string, or could you add a date + 10 and get a date ten days after the date specified? (I'm not sure why you might want to do that, just wondering.)

In order by (indexes) you dont need to make any conversion between different data types as opposite to clipper thats why these ADORDD SET ... INDEX....

SELECT DDATA+10 AS MYDATE ORDER BY MYSTRING+MYDATE

I don't think I have ever used indexkey() before. I just read up on it, and I will have to think of where I might use it in the future.

seek key
do while indexkey(0) = key //gives some abstraction of the fields we are seeking

Does this only apply to indexkey() or are there other xHarbour functions too? If so, which ones?

This applies to all functions and I dont know how many but one thing is sure the expected return values are respected acc clipper standards where you cant have different data types together in same expression without convert it all to the same data type.

This what I would have expected, and I did get an error when I tried two indexes--something like "Too many recursive errors." OK, I don't understand how you can define a function at the server. What language do you use? Does the server understand xHarbour? I will Google this.

I think UDFunctions at MySql server must be written in C. Others I dont know.
In ADS you can also do it.

The one index used a lot in the app I am trying to convert uses two different fields, both containing strings, and concatenates them into a larger string then adds the two digits of the century (either "19" or "20")

It probably can be done. Please post index expression.

[James Bott]

Antonio F,

Here is the index and the UDF.

IDATE and DATE are two character fields. IDATE is in the format YYMM where it is the last two digits of the year (e.g. 15 for 2015). DATE is really day in the format DD. Like I said previously, icon3() just returns what you would get from DTOS( dDate ) when SET EPOCH TO 1980 is set.

Code: Select all  Expand view  RUN

INDEX ON ICON3(IDATE+DATE) TO "DATE"

// icondate is in format YYMMDD
// Returned date is in format YYYYMMDD
FUNCTION icon3(icondate)
   //icondate is idate+date
   IF VAL(SUBSTR(icondate,1,2))< 80
      RETURN '20'+icondate
   ELSE
      RETURN '19'+icondate
   ENDIF
RETURN nil

 

James

[James Bott]

Antonio F,

Here is how it can be solved using a database class (and adding a new field SDATE).

Code: Select all  Expand view  RUN

Method Save()
   IF VAL(SUBSTR(::IDATE+::DATE,1,2))< 80
      ::SDATE:='20'+::IDATE+::DATE
   ELSE
      ::SDATE:='19'+::IDATE+::DATE
   ENDIF
Return ::super:save()
 

James

[AHF]

James,

You can use If( in SELECT and in ORDER BY.

SELECT field+IF( field1= ..., true expr, false expr) AS myfield FROM myTable ORDER BY myfield

or

SELECT field ORDER BY field+IF( field1= ..., true, false)

You can also use CASE instead of IF.

You can use also conversion functions such as CONVERT(.... or concatenation CONCAT(...

Please remember that these SQL order by expressions must be at SET ADO TABLE LIST INDEX

[James Bott]

Antonio F,

Ok, thanks that is all very useful info.

SELECT field ORDER BY field+IF( field1= ..., true, false)

So could I then convert this UDF:

Code: Select all  Expand view  RUN

FUNCTION icon3(icondate)
   //icondate is idate+date
   IF VAL(SUBSTR(icondate,1,2))< 80
      RETURN '20'+icondate
   ELSE
      RETURN '19'+icondate
   ENDIF
RETURN nil

To this index (assuming using MySQL):

Code: Select all  Expand view  RUN

index on if(left(idate,2)<"80", "20"+idate+date, "19"+idate+date)

The above would, of course, also work with Fivewin.

James

[AHF]

James,

You have to try it with Mysql Workbench and then apply the correct expression into the app.

I dont know if such literal expression is accepted in INDEX ON at the compile time you have to try it. My be you have to place it in a var and evaluated in INDEX ON
In SET ADO DBF INDEX it would be ok.


Please be aware that you will might need different expressions accordingly to the SQL engine used.

[pieter]

Hello Antonio F, and other members.

It is going well with the adordd implementation of my application. (I am using now a smaller application than the normal application, but it should be similar for the normal application).

I got an error at this moment.

Part of the log of the error:

Application
===========



Admin.: -
ADS: LOCAL
Error ocurred at: 08-09-2015, 14:39:07

Error description: Error BASE/1132 Bound error: array access
Error id 1: -
Error id 2: -
Current opened dbf: BASIS - 1
Last menu action: <onbekend>
Args:
[ 1] = A (L=0)
[ 2] = N 1

Stack Calls
===========
Called from ADOPSEUDOSEEK(3749)
Called from ADOSEEKCLIFIND(3412)
Called from ADO_SEEK(3371)
Called from DBSEEK(0)
Called from DBFSEEK(1479)
Called from DBFSELECT(1465)
Called from ABASIS(2912)
Called from UBTEST(42)

System
======
CPU type: Intel(R) Pentium(R) CPU N3540 @ 2.16GHz 2166 Mhz
Hardware memory: 3500 megs
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20150213)
Windows version: 8

Our application works with ADS, I tried to remove the ads parts out of the application, and replace with adordd code. There were only very few places where I had to do that.
Here are some of the changes which looks like:

Code: Select all  Expand view  RUN

IF(.F.)
// Check if Ads is used local or remote
lAdsLocal := IF( AppRegistry( 2,"ADS","" ) == "LOCAL",.T.,.F. )
nAdsType := IF( lAdsLocal,1,2 )

// Harbour database engine
rddRegister( "ADS",1 )
rddsetdefault( "ADS" )
AdsSetServerType( nAdsType )       // 1=Local, 2=Remote, 4=AIS
AdsSetFileType( 2 )                // 1=Ntx, 2=Cdx, 3=Adt

ELSE

//ADORDD
RddRegister("ADORDD",1)
RddSetDefault("ADORDD")
SET ADO FORCE LOCK OFF   // Required!
SET ADO DEFAULT DATABASE TO "database" SERVER TO "localhost"  ENGINE TO "MYSQL" USER TO "root" PASSWORD TO "password"

ENDIF

 

Code: Select all  Expand view  RUN

//#define ADS_DRIVER        "ADS"
#define ADS_DRIVER        "ADORDD

Code: Select all  Expand view  RUN

REM ADORDD files opnemen
echo %hbdir%\lib\adordd.lib + >> UBtest.bc
echo %hbdir%\lib\rdds.lib + >> UBtest.bc
echo %hbdir%\lib\usrrdd.lib + >> UBtest.bc

REM ADS files opnemen
rem echo %hbdir%\lib\rddads.lib + >> UBtest.bc
rem echo %acedir%\Ace32.lib + >> UBtest.bc
 

I hope you can help me with where I should find the solution. Do you think I should change something in my application or in adorrd.prg?

Kind regards,

Pieter