dbf vs sql...

[Francesco Perillo]

The main news of this August seems to be the availability of RDDSQL... I agree it may be a important and perhaps milestone release for Harbour developers.

But it should be clear which are the pros and the cons of using rddsql, and I still have no reference to docs, blogs, articles, and the like.

There are some points I'd like to discus (read: understand how they were implemented)

- "live data" vs "recordset"

Usually when we use DBFs we move on live data, if I do:

USE CLIENTS SHARED

GO 100

inkey(1)

skip

inkey(1)

skip -1

The data of the record 100 may be different from 2 seconds ago, someone else may have chenged it.

In SQL we usually work with recordsets, we ask for a query, we may move back and forth, but in a local copy of the data. If I use the data from record 100 to do something, that thing may be based on stale data.

- record and file locks

it all depends on programming style but since my program has been in use in a small firm with no more than 20 users, when someone enters the "modify item" the record is flock().. Others may browse it, retrieve it, but can't enter the "modify item" form since they will fail the flock()..

In SQL there are, usually server-specific commands like SELECT FOR UPDATE to emulate this behaviour but usually it is done in other ways,for example using a revision counter or a last-modified timestamp; it is possible for multiple people to enter the "modify item" form but the first to save wins, the others must ... (well, it depends on the programmer....)

One of my nightmares is a ransomware crippling the dbfs. From time to time I have to re-enable SMB** due to some Microsoft KB that breaks connections to my samba server. Moving to a SQL server may help but I don't know if a solution like RDDSQL is ok, I mean, for my specific coding style, UX, etc.

Please share your experiences porting codebases from DBF to RDDSQL

Thank you

Francesco

[theos theos]

  1. El contexto      

     App Clipper - pequeña empresas y medianas orientadas

                   a usar datos en forma compartido y con

                   responsabilidad de control y administración.

     App SQL     - grandes empresas y medianas, orientadas

                   a usar grandes base de datos pero como

                   un servicio y sin responsabilidad del

                   control o administración  

  2. App Harbour - Extensión de Clipper que te da la

                   posibilidad de usar los datos tanto

                   en forma compartida, tradicional o como

                   un servicio de base de datos.

 

  3. Una aplicación clipper de los 90 tal como está, fácilmente

     puedes migrarlo a usar Base de datos en una empresa grande

     que use por ejemplo DB2.

 

  4. La clave es realizar unas pequeñas envoltura a la sentencia

     USE tabla y adicionar funciones para la actualización

     de los datos, no es tanto modificar tu código sino adicionar

     código, gracias a las extensiones que tiene Harbour

 

  5. La ventaja de usar Base de Datos depende más del contexto

     entre el desarrollador y la empresa.

     Ejemplo, eres un desarrollador FreeLancer  y deseas vender tú

     sistema de contabilidad que tiene una característica especial

     a una gran empresa con varios servidores con base de datos,

     además tienes un contacto que te puede ayudarte para venderlo pero

     si tu sistema maneja DBF no ingresas ni a la puerta.

     El valor agregado que le das a tu sistema cuando usas una base de

     datos comercial como DB2 u Oracle es diferencial. Y si la empresa

     ya lo tiene es mas fácil todavía. Tanto DB2 como Oracle tienen

     versiones Express gratis que son suficientes para una pequeña y

     mediana empresa.

 

     Yo tengo cariño a Clipper por que hace muchos años me saco de un

     apuro por lo que llegue a conocer a Harbour que es otro nivel.

     Mi campo de acción es con Powerbuilder es una maquina diferente.  

[Ron Pinkas]

On Friday, August 18, 2023 at 10:35:53 AM UTC-5 Francesco Perillo wrote:

...

- record and file locks

it all depends on programming style but since my program has been in use in a small firm with no more than 20 users, when someone enters the "modify item" the record is flock().. Others may browse it, retrieve it, but can't enter the "modify item" form since they will fail the flock()..

...

Curious, why would you use FLock(), which as you said yourself blocks all other users from modifying any other record, when you could instead use RLock() which will block other from modifying only this same record?

Ron 

[matt johnson]

In general, you want to store fields to memory variables, edit the memory variables, use rlock to lock record, replace fields with mem variables. then unlock.  The lock is only applied for in instant. If adding records with APPEND BLANK, you will need a file lock, flock().  Once again, apply lock, append record, replace fields with memory variables, then unlock.  I like to then use commit statement, but I am not sure if that is needed. 

Matt 

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit

https://groups.google.com/d/msgid/harbour-users/1b476581-4873-4595-9fd8-34e45da88aben%40googlegroups.com

.

[Francesco Perillo]

I use rlock(), of course.

I rlock() the record all the time needed to the user to editing the form. It may be hours :-))) if the user forgets to close the windows....

Sorry it was a typo...

--

[Francesco Perillo]

Hi Matt,

Il Ven 18 Ago 2023, 23:46 'matt johnson' via Harbour Users <harbou...@googlegroups.com> ha scritto:

In general, you want to store fields to memory variables, edit the memory variables, use rlock to lock record, replace fields with mem variables. then unlock.  The lock is only applied for in instant.

Unfortunately these steps are in wrong order... :-)))

1. Use rlock()

2. Store fields to mem variables

3. Edit mem variables

4. Replace fields with mem variables if user confirms the changes

5. Unlock

If you do 2 before 1 there is a minimum possibility that someone else may have changed the record...

In my very first clipper version of my program, circa 1987, I did 2, then show the mem variables and asked the user, what you want to do? If the user asked to edit I DID NOT reread the fields into the variables so I lost changes done by other users...

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/687985734.1484210.1692395163705%40mail.yahoo.com.

[Bob Burns]

Hi Matt

One way to minimise the use of flock() when there is a need to add new records to a dbase type database is to create a cache of unused records which can be made invisible to users with special key field values. When a new record is required a request is made to the cache using rlock() and a deleted record is added to the cache. When the cache gets low then it must be expanded using flock() but this process can be run overnight or during times of very low use.

I used this technique in a car fleet management system that ran for over ten years. In this case the cache was defined as a percentage of the number of active records and this percentage number was held in a a configuration table so that it could be changed if required - the value was typically 10%.

Regards

Bob

Bob F Burns G3OOU, G-QRP 6907, @BobFBurns
Retired Software and RF Communications Engineer
Admin/sales site: http://www.g3oou.co.uk/
Technical site: www.qsl.net/g3oou

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/687985734.1484210.1692395163705%40mail.yahoo.com.

[Klas Engwall]

Hi Francesco,

> Il Ven 18 Ago 2023, 23:46 'matt johnson' via Harbour Users

> <harbou...@googlegroups.com <mailto:harbou...@googlegroups.com>>

> ha scritto:
>
>> In general, you want to store fields to memory variables, edit the
>> memory variables, use rlock to lock record, replace fields with mem
>> variables. then unlock.  The lock is only applied for in instant.
>
> Unfortunately these steps are in wrong order... :-)))
>
> 1. Use rlock()
> 2. Store fields to mem variables
> 3. Edit mem variables
> 4. Replace fields with mem variables if user confirms the changes
> 5. Unlock

I don't agree. I have always done it the way Matt suggested. The problem
with your approach is that users don't care about "I am the only user
with access to that record now" and disappear for lunch or into a
meeting for the rest of the day while keeping the record locked.

> If you do 2 before 1 there is a minimum possibility that someone else
> may have changed the record...

That is true, but there are ways around it. Already back in the eighties
I added a numeric field called UPDNO at the end of every dbf. It is
incremented upon every save, and it is checked after the record has been
rlock()ed, before a save is allowed. If it does not match the UPDNO
value from the start of the edit session, then the user gets a message
about it, the record is reread, and the user is allowed to make his
changes once again on top of the refreshed data. It is very solid.

The users understand that life went on while they were absent :-)

Regards,
Klas

[Francesco Perillo]

Hi Klas,

It all depends....

Let's just say that almost twice per month a couple of users travel abroad, worldwide, using a satellite uplink in Europe o a adsl/network connection provided worldwide.

They vpn to hq and use the application locally

It did happen that the connection dropped with records locked.

Your UPDNO is a solution for this exact problem, you recognized there was a possible race condition, and acted accordingly.

And you are in a better position than me now since the UX you provide is in line with a port to sql, where you have to use this or similar tricks.

In my case, late 80s, the boss decided that only one user could enter the modify form. He also wanted that users should be aware if someone was editing a record, infact when I display the record I try to rlock(): if I don'tget the lock I display a notice RECORD BEING EDITED... 

As I said, it all depends.. :-)))

Anyway, we are steering away from the core of the thread: can anybody that did the dbf to rddsql move tell us their experience?

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/b02e0f85-aaa9-54c5-57d1-78881f8d9bd2%40engwall.com.

[Francesco Perillo]

Extrensive docs about SQLRDD are included in the repository...

[David Field]

Hello Francesco,

I moved my systems from dbf to sql first trying to use SQLRDD with many problems that I don't all remember at the moment so I changed to ADO and have been using it for the last 10 years with a minimal change to mi programming since before using SQL I used dbf's as Objects and this resulted in a very easy way to migrate.

On the record lock issue you present, there are ways with SQL to lock the record, but I didn't like them, so I came up with creating a tabled called RecLocks that contains two fields, TableName CHAR(20) and IdRec INT these fields contain the name of the table and the id of the record being locked and the table is indexed by both fields as UNIQUE so there can be no double record.

If I want to implement a lock on a record, I try to insert a record in RecLocks table with the appropriate information, if it succeeds then OK, else the record is locked by someone else.

On the same issue, ADO returns an error if you try to update a record that has been modified from the original information it retrieved, so there is another nice thing about ADO.

If you would like to know more about ADO you can visit my google page https://groups.google.com/g/ado-harbour

Regards,

David Field

[Rafa Pabd]

do you want say that SqlRdd is a broken product? It does not seem that this is the case, when the product was always surrounded by very good comments

[Francesco Perillo]

I read David post as: I tried to use sqlrdd for my program but in the process I found ADO better in line with my code.

I think nobody here is a english native speaker and it shows...

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/d1335bc9-df0e-434b-86ef-99765b26585dn%40googlegroups.com.

[David Field]

Hello Rafa,

I've just realized that we are talking about SQLRDD and not RDDSQL.

My mistake, in my comment I was referring to RDDSQL.

Sorry,

David Field

[Jorge Garate]

Interesante, siempre cargo directamente a mem variables y solo al actualizar uso RLOCK(). Hasta el momento no he tenido problemas, pero pienso en sistemas donde el acceso a un mismo registro por varios usuarios a mismo tiempo podría haber errores y el hacer primero RLOCK() sería la solución.

Saludos.