Dual system

[wpacheco]

Dear friends:

I have been worked at the development of a dual system with DBF/CDX and SQL databases (MySQL and SQL SERVER).

First question I did was the connection to SQL with Jose Luis Capel TAdo class with no problem... second question was (and it continues being) the managing of the ADO record set... let me explain

In DBF/CDX environment I work with TDatabase, a great class that simplifies all the job, but in the Ado samples I've checked I have not found anything similar that allows a managing like TDatabase style. I know these class are differents and I know the environment too because I have good experience in php+mysql development

If I do a connection to a table with no records, TDatabase method oDB:Field1 returns a empty value, this is good for me. The same thing in a empty table with TAdo method oQry:oRs:Fields ('field1'):value returns an error because the recordset does not have any values. Maybe I'm not doing the right things but I want to know if it is possible to get the same function in order to save code.

One last question is: Do you think is better to separate the code for DBF and SQL databases or keep one prg with many "IF" structure inside?

Thank

[Badara Thiam]

Dear friends:

One last question is: Do you think is better to separate the code for DBF and SQL databases or keep one prg with many "IF" structure inside?

Thank

You must separate the code only if you have GPF,
else it's better to take the second solution.
It's more easy to upgrade in a "parallele" way,
you can see the code of each rdd in only one .prg

Regards,

[James Bott]

I would suggest that the better solution is to subclass both TDatabase and TADO. You can then make these subclasses both use the same syntax. This way you can just compile with either class to make your application work with either database.

This is one of the great features of OOP.

[wpacheco]

Thanks for your response

Really I had not thought of doing subclasses, because my short experience with it, but I think that it is the best solution. On the other hand as Badara says, I like to have the code in an single prg to be able to do changes in sets, I think that joining both ideas will be great

Thanks again

[James Bott]

>On the other hand as Badara says, I like to have the code in an single prg to be able to do changes in sets, I think that joining both ideas will be great.

I'm not sure I made myself clear. If you make the subclasses with the same syntax, then the rest of your application code will not need to know which database it is using. You won't need any IF/ELSE in you application.

You will need to make each subclass in a separate PRG also.

I have not worked with ADO, but I suspect that you may be able to create just one subclass so it has the same syntax as the other database class. I don't know which one would be the best one.

If you need help creating the classes, I suggest reading my articles on OOP which you can find on my website. If you still have questions, then just ask here.

James

[James Bott]

OK, when I said that you might be able to create just one subclass I was thinking of my own TData class, not the TDatabase class that comes with FW. You will need to create two subclasses, if only for the New() method.

Then you will also need another subclass for each file (DBF or table). So then you can do:

oCustomer:= TCustomer:new()

in your application code. You then compile it with either the DBF database class or the ADO database class. Either way you get a customer table.

James

[wpacheco]

James

I have just stopped reading OOP's articles in your page, both are very interesting and explained.

The database class I use is the one that comes with FW and till now it has served me in everything what I have needed, except a couple of methods that I have created like "Locate" and the refreshment of the buffers in the "Seek" method.

I don't know what feature your class has, but I find a little complicated to do a subclass that allows that the ADO's Recordset should behave as an database object, probably for my short of experience in the matter, nevertheless I am sure that your comment is the best way of working since the code will be the same one without matter the database used.

You say you have not worked with ADO but have you worked with SQL databases? If you have, what class or lib do you use?. Some guys here recommend ADO and this one was I choose.

Thanks again

[Badara Thiam]

Hello

This is a sample code fully fonctionnal.

Regards,

Code: Select all  Expand view

*****************
FUNCTION DBFRLOCK(QV)
*****************
* To lock using the same function with DBFNDX, DBFNTX,
* DBFCDX or ADS
LOCAL QRET
SysRefresh()
IF " " + RDDSETDEFAULT() + " " $ " ADS DBFCDXAX DBFNTXAX "
  QRET := AX_RLOCK(QV)
ELSE
  QRET := DBRLOCK(QV)
ENDIF
SysRefresh()
RETURN QRET



[James Bott]

Williams,

The database class I use is the one that comes with FW and till now it has served me in everything what I have needed, except a couple of methods that I have created like "Locate" and the refreshment of the buffers in the "Seek" method.

TDatabase already refreshes the buffers on Seek, so if you are doing it again you are doing it twice.

I don't know what feature your class has, but I find a little complicated to do a subclass that allows that the ADO's Recordset should behave as an database object, probably for my short of experience in the matter, nevertheless I am sure that your comment is the best way of working since the code will be the same one without matter the database used.

Here is a simple example. In the ADO subclass create a DATA oRS. In the TDATABASE class we have method gotop() to goto the first record. In ADO they syntax is moveFirst(). So in the ADO class we create a gotop() method like this:

Code: Select all  Expand view

method gotop inline ::oRs:moveFirst()

Now you have the same gotop() syntax for each table.

You say you have not worked with ADO but have you worked with SQL databases? If you have, what class or lib do you use?. Some guys here recommend ADO and this one was I choose.

No, I haven't worked with SQL either. I'm sure you can use ADO to do what you want.

James

[wpacheco]

James

Let me explain in my case what buffer refresh means
Usually I open the database object and load values to show it in the dialog. When the user make changes to these values in an edit task, I need to post validate the data so I seek the field value but the seek method "refresh" the buffer initial contents, this action causes that the values get lost.
I did changes to the class and defined a new var "aOriginal" this var like aBuffer permits save my initial values and to recover when I needs them

I'm going to try to create a ADO subclass that could handle as TDatabase, I know this won't be an easy job but I think this is the right way, so soon you'll have news...

[James Bott]

Willams,

Let me explain in my case what buffer refresh means
Usually I open the database object and load values to show it in the dialog. When the user make changes to these values in an edit task, I need to post validate the data so I seek the field value but the seek method "refresh" the buffer initial contents, this action causes that the values get lost.
I did changes to the class and defined a new var "aOriginal" this var like aBuffer permits save my initial values and to recover when I needs them

All you needed to do was set lBuffer to .f. before the seek, then set it to .t. again after the seek. Setting lBuffer to .f. prevents the the buffer array, aBuffer, from being refreshed during any navigation command.

oCustomer:lBuffer:= .f.
oCustomer:seek("whatever")
oCustomer:lBuffer:=.t.

I'm going to try to create a ADO subclass that could handle as TDatabase, I know this won't be an easy job but I think this is the right way, so soon you'll have news...

Keep us posted about your progress.

James

[James Bott]

Williams,

I have reviewed my notes on this topic from some time back when Enrico and I had a discussion about this. There are some differences between Recordsets and DBFs that may be hard to deal with.

When you browse a DBF, the browse reads just enough records to fill the screen. If you browse a Recordset you need to read the entire recordset into memory before you can start the browse. This will be a problem with large files. The alternative is to write more complicated routines that only read enough records into the recordset to fill the browse. However, the SQL langugage must support starting at a given record and the LIMIT clause to limit how many records are read into the set.

Another issue is that only some SQL languages support indexes and you need indexes to do a seek.

Some SQL languages do not support the absolutePosition property of recordsets. Without this, we cannot simulate the recno() of a DBF.

So, it seems although it is possible to write a recordset class that comes very close to simulating the TDatabase class, there are a few issues that will be very difficult to deal with without writing conditional code in the application. Also, there are things that can be done with recordsets that cannot be done (easily) with DBFs so you limit yourself to not programming for recordsets.

Just a few thoughts on the topic.

James

[wpacheco]

James

I'm completely agree with you: the SQL databases programming and DBF databases programming are very different. My experience in this area comes from the business application programming for the internet using PHP, Javascript and MySQL.

First thing it is necessary to take in mind is though the index concept exists in the SQL model, the database engine is the one who tries the consultation and returns the results doing an optimal use of the database and this one depends of the user design.

I think to focus the development of the application with the browse concept is not the right way for the reasons you comment, in fact, on the majority of web sites the programmer meet obliged to use pagination techniques in order to show sets of information in a grid. On the other hand in a SQL table there does not exist the definition of record number like we know in DBF. If the user needs to do a search he must use a criterial sufficiently clear in order that he obtains the awaited results.

For example in DBF model (using TDatabase) we do something like this assuming that exists an active and indexed field 'name'

Code: Select all  Expand view

database oDbf
oDbf:Seek('Williams')
MsgInfo(oDbf:name)

But in SQL we do this

Code: Select all  Expand view

cSQLCommand := "SELECT name FROM users WHERE name='Williams'"
oQry := tAdoRs():New()
oQry:Open( cSQLCommand, ::oConexion:oConnection )
oQry:oRs:MoveFirst()
MsgInfo(oQry:oRs:Fields(0):value)

The difference between both codes is the SQL recordset may returns too many "records" and we meet obliged to limit the result, lucky MySQL and SQL SERVER supports the LIMIT.

As I said originally, I know is complicated and probably it won't have the whole function that TDatabase has but the user must know that both models are different and therefore in his application he will not have to use any concepts that before he was accustoming

Regards
Williams

[Gale FORd]

Have you looked at the RDD drivers for SQL databases? I don't know how they handle the cursor in the dataset or other stuff but there seems to be a few people using them.

If they work as advertised then tdatabase might work without much change.

[wpacheco]

No I haven't.
I will check

Thanks