Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQLRDD locking scheme is broken and XHarbour.com don't want to fix it

1,218 views
Skip to first unread message

Roberto Romeo

unread,
Apr 11, 2014, 9:37:27 AM4/11/14
to
I wrote this post to inform the community about SQLRDD errors and the lack of support from xHarbour.com.

I bought xHarbour Builder Enterprise in November 2013 because I needed SQLRDD.

As soon as I started using SQLRDD I found that DbrLock() / DbrUnLock() scheme is broken because DBrunlock(xRecno) releases all the locks instead of the one passed.

I posted this issue with an example in December 2013 when my subscription was still covered but my post was ignored.

I wrote to Patrick Mast in February 2014 (in the meanwhile my support was expired but my post still pending unresolved).

Patrick forwarded my question to Luiz an Marcelo Lombardo but still there was no answer.

I sent a new email to Patrick, Marcelo and Luiz in March and finally I got the following answer from Marcelo:

"Is not a bug but a SQL limitation. There is no such thing as individual lock / release in any SQL database. All locks resides in a transaction. Once transactions is committed, all locks are released. Sorry, no workaround possible."

I can't accept with Marcelo reply because locking is a FUNDAMENTAL PART of the language and a MUST HAVE for any RDD.

No information about this limitation is present neither in the website nor in the documentation.

These are the SQLRDD specifications published in xHarbour.com website:

SQLRDD allows transparent access to the most important relationary data bases of the market, ACCURATELY IN THE SAME WAY AS IF IT MAKES WITH ARCHIVES DBF.

Very little changes in the xBase source code. Only to declare the RDD, a function call to connect to the database and a substitution for the function file() for an equivalent that returns the presence of the internal archive of the database.

Then I wrote to Marcelo:

"I could be wrong, but it seems that if you are opening a transaction for each lock on a table, as soon as the first (most external) transaction is committed all the internal transaction are committed. This could be a big error.
No information on this topic is present in the documentation of SQLRDD and you should agree with me that the locking mechanism of xHarbour is a "must have" for every RDD. I'm not an expert of Relational Database but it seems to me that the only way to realize such mechanism with a DBRMS is to use a "semaphore" table (I can see that there is a table named SR_MGMNTLOCKS, maybe it was created for this scope).

And Marcelo wrote back to me :

"SR_MGMNTLOCKS was created on an attempt to simulate TABLE LOCKS, and this attempt failed."

Thus Marcelo is confirming that it is a well known problem but they neither solved it nor documented.

Marcelo also gave me wrong informations when I asked him:

"Does the following code work in the same way with DBFCDX and with SQLRDD?

dbrlock(1)
dbrlock(2)
dbrunlock(1) <--- Does SQLRDD release also record 2 here?

do something with record 2

dbrunlock(2)"

He replied to me:

"No. It will release all in second unlock, so record 1 remains locked until second unlock is executed."

(Marcelo, let me say - with the same words you write to me - that "It seems to me that you should take a little time to learn and understand SQL, than maybe, just maybe, you will understand what I mean...")

After this reply I wrote to Patrick, Marcelo and Luiz:

"Patrick, it seems to me that XHarbour.com don't want to solve problems.
I can't trust XHarbour.com if you are not able to solve this problem.
I need support, I want to pay for it, but I can't pay you more money if you can't or simply don't want provide support.
Moreover now I'm also angry, because this issue was opened 4 month ago.
Solve this problem is not enought to convince me to trust in you again.
I also need a solution for the SR_setfilter() with conditional index problem (you already have the sample), and for the
tbrowse:forcestable() problem (I can produce a sample for this last error only if we found an agreement and when the first 2 are solved).
I will wait a positive answer about this agreement within tomorrow or I will post on the newsgroup these errors with samples, my emails and your answers, and my opinion on SQLRDD and related service.
I'm sorry that we get to this point.
This is not a good way to solve problems but it's the only one you leave me."

At this Point Marcelo replied to me:

"About your sample, I will try it as soon as I can.
Please fell free to publish whatever you want on wherever you want. This is a free world.
Please understand that me, as a xHarbour.com member, will not work under any kind of terrorism threats in newsgroups, so simply go on and try to explode it as much as you can. I don't care. SQLRDD have thousands of happy users and your opinion is nothing more than that: your opinion (mainly with fragile technical arguments)."

I am very sorry to write this post because I think the xHarbour team has done a great job. I hope that this post will push them to solve these issues or, if not , it will be useful to anyone who intends to buy SQLRDD.

I think that xHarbour is a good product but SQLRDD is not compliant with RDD functionality.
My experience with xHarbour support is terrible. I waited 4 month and their answers was sometimes quite offensive:

"It seems to me that you should take a little time to learn and understand SQL, than maybe, just maybe, you will understand what I mean..."

Or

"As I said, just go ahead and do whatever you think you should do. That will never change the way SQL works, and will end up as you exposed as someone with zero SQL knowledge."

Marcelo, I know how SQL works and that's why I expect that SQLRDD addresses this difference between DBF and DBRMS lock behavior.

We have one more bug documented by an example: SR_Setfilter() is not working with CDX conditional Indexes (indexes with a FOR Clause). Also this bug was ignored by xHarbour.com.

Moreover we have a bug on tBrowse:forcestable().
We didn't have a sample yet, because there is no use in spending time to produce examples that are ignored.

Finally, there are some other minor bugs on how indexes are maintained but it does not worth the time to detail more.

Regards,

Roberto

donov...@gmail.com

unread,
Apr 12, 2014, 12:02:45 AM4/12/14
to

donov...@gmail.com

unread,
Apr 12, 2014, 12:11:41 AM4/12/14
to
Hola, Roberto.

Actualmente utilizo SQLRDD y SQl Server 2008 con muy buenos resultados.
Por favor checa este link:

http://forums.fivetechsupport.com/viewtopic.php?f=6&t=25937&p=141954

A mi me ayudo a comprender y utilizar las transacciones en sql, no necesitas
utilizar DbRlock() y DbUnlock()

Saludos !!



El viernes, 11 de abril de 2014 08:37:27 UTC-5, Roberto Romeo escribiĂ³:

Roberto Romeo

unread,
Apr 14, 2014, 5:31:35 AM4/14/14
to
Hello Donov,

thank you for your post.

Unfortunatelly, the problem is not to learn how to use transactions or isolation levels. We are using them since 1995.

The problem is that - with SQLRDD - dbunlcok(xRecno)release all the locks instead of one.

And this is not a minor difference!

At xHarbour.com they must clearly explain this difference in their website and in SQLRDD documentation.

It would be enought to write something like this in the product specs:

"If you are using dbrlock() and dbrunlock() to locks several records and release them one by one, your code must be changed because SQLRDD does not support this."

We spent 995 dollars to buy the product, and 2 month to develop a DotNet website based on the same data used also by the xHarbour application.
At in the end we found this limitation.

For this reason:
1) Our customer lost a lot of data.
2) We worked two weeks to restore the losted data.
3) We are working since the end of february to rewrite tons of xHarbour code
4) up to now we are loosing about 5.000 Euro.

I can assure you that if they were in Italy I would not hesitate to file a lawsuit to recover lost time and money.

Best regards,

Roberto

arielso...@gmail.com

unread,
Apr 14, 2014, 12:46:45 PM4/14/14
to
Hola, Roberto.

PodrĂ­as por favor publicar tĂº cĂ³digo para analizar.

Saludos

Roberto Romeo

unread,
Apr 14, 2014, 1:09:10 PM4/14/14
to
On Monday, April 14, 2014 6:46:45 PM UTC+2, arielso...@gmail.com wrote:
> Hola, Roberto.
>
> PodrĂ­as por favor publicar tĂº cĂ³digo para analizar.
>
> Saludos
>
Hello Arielsol,

This is the code that shows the dbrunlock() bug.


#include "sqlrdd.ch"
#define RECORDS_IN_TEST 2

Function Main( cRDD, cDSN )

local aStruct := {{"CODE_ID","C",8,0 }}
local i

Connect( @cRDD, cDSN ) // see connect.prg

dbCreate( "TEST_TABLE", aStruct, cRDD )

USE "TEST_TABLE" EXCLUSIVE VIA cRDD

For i = 1 to RECORDS_IN_TEST
Append Blank
Replace CODE_ID with strZero( i, 5 )
Next

dbCloseArea()

USE "TEST_TABLE" SHARED VIA cRDD

dbrlock(1)
? len(dbrlocklist()) // 1

dbrlock(2)
? len(dbrlocklist()) // 2

dbUnLock(1) <- this should release one lock but it release all the locks
? len(dbrlocklist()) // 0 <- lock on record 2 is lost

Return NIL

/*------------------------------------------------------------------------*/

#include "connect.prg"

/*------------------------------------------------------------------------*/


Roberto Romeo

unread,
Apr 14, 2014, 1:19:35 PM4/14/14
to
On Monday, April 14, 2014 6:46:45 PM UTC+2, arielso...@gmail.com wrote:
> Hola, Roberto.
>
> PodrĂ­as por favor publicar tĂº cĂ³digo para analizar.
>
> Saludos
>
This is the code showing the bug of SR_SetFilter()


#include "sqlrdd.ch"
#define cTable 'TEST'

REQUEST SQLRDD
REQUEST SR_ODBC


Function Main( cDSN )
local aStruct := {{"DATA1","C", 1,0},;
{"DATA2","N", 1,0},;
{"DATA3","L", 1,0}}, i, cRDD := "SQLRDD"
dbconnect(cDNS)
if ! SR_file(cTable)
dbCreate( cTable, aStruct, cRDD )
endif
dbUseArea( .T., cRDD, cTable,, .F. )
if ! SR_file(cTable)
For i := 1 to 9
Append Blank
Replace DATA1 with str( i, 1 )
Replace DATA2 with i
Replace DATA3 with mod(i,2)<>0
Next
ordCondSet("DATA3==.t.",{||"DATA3==.t."})//,.t.,,,, nil, nil, nil, nil,, nil, .F., .F., .F., .F.)
dbGoTop()
ordCreate(,'TEST_FILTER', 'DATA1', {||DATA1} )
endif
set order to
Browse() <---- browse all rows
ordsetfocus('TEST_FILTER')
dbgotop()
Browse() <---- browse only rows included in conditional index TEST_FILTER
SR_SetFilter("data2<6") <---- Conditional index stop working
dbgotop()
Browse()
DbCloseAll()
Return NIL

function dbConnect(cConnString)
local nDetected := DetectDBFromDSN( cConnString )
if ndetected==0
alert('Connection string Error')
endif
return SR_AddConnection( nDetected, cConnString )

arielso...@gmail.com

unread,
Apr 14, 2014, 5:21:50 PM4/14/14
to
On Friday, April 11, 2014 8:37:27 AM UTC-5, Roberto Romeo wrote:
> I wrote this post to inform the community about SQLRDD errors and the lack of support from xHarbour.com.
>
>
>

Hola, Roberto.

Puedes por favor probar este cĂ³digo y comentar los resultados.

#include "sqlrdd.ch"
#define RECORDS_IN_TEST 2

Function Main( cRDD, cDSN )

local aStruct := {{"CODE_ID","C",8,0 }}
local i

Connect( @cRDD, cDSN ) // see connect.prg

dbCreate( "TEST_TABLE", aStruct, cRDD )

USE "TEST_TABLE" EXCLUSIVE VIA cRDD

For i = 1 to RECORDS_IN_TEST
DBAppend()
CODE_ID := strZero( i, 5 )
DBCommit()
Next

dbCloseArea()

USE "TEST_TABLE" SHARED VIA cRDD

Do While !Eof()
Blog_Reg = .F.
Do While !Blo_Reg
Blo_Reg = DBRLock()
Enddo

? len(dbrlocklist())
Inkey(2)
DBUnlock()
Enddo

/*
dbrlock(1)
? len(dbrlocklist()) // 1

dbrlock(2)
? len(dbrlocklist()) // 2

dbUnLock(1) <- this should release one lock but it release all the locks
? len(dbrlocklist()) // 0 <- lock on record 2 is lost

*/

Return NIL

/*------------------------------------------------------------------------*/

#include "connect.prg"

/*------------------------------------------------------------------------*/

Saludos :-)

Claude R

unread,
Apr 15, 2014, 2:07:59 AM4/15/14
to
see comment
This for sure is wrong. Should be in any way dbRunlock(1). Dbunlock ALWAYS releases all the locks.
Claude

Roberto Romeo

unread,
Apr 15, 2014, 4:18:30 AM4/15/14
to
Claude,

you are right (we have done a lot of samples to test in different ways and this one was wrong).

Anyway, also dbrUnlock(1) release all locks.

Roberto Romeo

unread,
Apr 15, 2014, 4:31:38 AM4/15/14
to
Hello Arielso.

If you lock and unlock only one record at a time the system works.
If you lock more records, as soon as you unlock the first one, you unlock all the records.

From the manual:

DbRUnlock( [<xRecno>] )

The DbRUnlock() function releases a record lock for an individual record specified with <xRecno>. If no record identifier is passed to the function, DbRUnlock() bahaves like DbUnlock() since all active record locks are released.



Regards,
Rob

cul...@gmail.com

unread,
Apr 15, 2014, 11:51:42 AM4/15/14
to
Roberto

use inside transactions
sr_begintransaction()
try
all your code with dbunlock
sr_committransaction()
catch e
sr_rollbacktransaction()
end

RDBMS dont have sql commands to unlock just one item.
and an command block ends when commit command is issued.

if you use inside transaction.
the commit command from dbunlock is not issued, and only goes when the transaction is all commited. give an try
Regards

Roberto Romeo

unread,
Apr 16, 2014, 8:16:44 AM4/16/14
to
Luiz,

I've tried this and does it not work.

sr_begintransaction()
dbrlock(1)
? len(dbrlocklist()) // 1
dbrlock(2)
? len(dbrlocklist()) // 2
dbrUnLock(1)
? len(dbrlocklist()) // 0 <- lock on record 2 is lost
inkey(0)
sr_committransaction()

Regards,

Roberto

Marcelo Lombardo

unread,
Apr 16, 2014, 9:27:18 AM4/16/14
to
Did you guys ever see an edited video where the initial recording takes a very different meaning?

This is the case in this message. Roberto took few words from different messages exchanged with me, edited it all and created his own version of facts.

This is pure manipulation and it's very sad to see at what point can a person go, instead of taking a little time to LEARN how SQL works and UNDERSTAND that there is no way to lock / unlock individual records in ANSI SQL.

Marcelo

dlzc

unread,
Apr 16, 2014, 10:23:53 AM4/16/14
to
Dear Marcelo Lombardo:

On Wednesday, April 16, 2014 6:27:18 AM UTC-7, Marcelo Lombardo wrote:
> UNDERSTAND that there is no way to lock / unlock
> individual records in ANSI SQL.

Well there is, just not in one step (which means without possibility of error, or losing lock on a record you did not intend to).

Record current position in table,
Make a copy of the lock list,
Release all locks,
Visit each member of the recorded lock list, except the one you want deleted,
Apply the lock if possible, to each record in turn,
return the current locklist.

David A. Smith

Roberto Romeo

unread,
Apr 16, 2014, 12:43:41 PM4/16/14
to
Marcelo,

nobody is manipulating any of your messages.

I can post the entire thread between you and me, but I think that people could only be interested in the problems they can face if they are going to buy SQLRDD.

Everybody can see the specifics of SQLRDD here.

http://www.xharbour.com/products/sqlrdd.php

SQLRDD allows transparent access to the most important relationary data bases of the market, accurately in the same way as if it makes with archives DBF.

ACCURATELY IN THE SAME WAY

Very little changes in the xBase source code. Only to declare the RDD, a function call to connect to the database and a substitution for the function file() for an equivalent that returns the presence of the internal archive of the database;

It supports all the traditional commands of the Clipper/xHarbour, such as USE, APPEND FROM, SEEK, dbRLock(), dbUnlock(), dbCommit(), ZAP, REPLACE, etc.

All these informations are not completely true.

Nowhere in the documentation there is informations about the fact that dbrunlock() is not working IN THE SAME WAY than any other RDD.

Nowhere in the documentation there is information about the fact that recno() after a dbappend() fails in a multiuser environment.

The SR_MGMNTLOCKS table that is automatically created in any db managed with SQLRDD it's a prove that the developpers of this RDD tried to solve the problem.
Thus they knew the problem exists.
But no words about this in the documentation.

The problem is that xHarbour (and you) are not understanding that we are in big troubles for these undocumented problems. And this is an xHarbour fault.

If I would have known about these limitations before, I would never buy this product because I would have been facing with a lot of problems.

But now thae I discover these limitation after having:
- payed the product;
- migrated my application (absolutely stable with DBFCDX);
- created an intranet based on the same data migrated (developped with DOTNET);

I can thay that xHarbour is responsable for this limitation!

And this limitation became big because is undocumented.

Due to this undocumented limitation our customer lost of a lot of data (orders, rows, inovice rows, accounting data) and we spent (and are still spending) a lot of time trying to recover those data.

You say that "there is no way to lock / unlock individual records in ANSI SQL.", but you are misunderstanding the problem again.

SQLRDD (and not the database) must mantain a list of locked records.
Since SQLRDD is the interface between xHarbour/Clipper functions and the RDBMS, SQLRDD can mantain a virtual list of locked records.
I used the term "Virtual list" because I know that those records are not actually locked by the RDBMS, but for any application using xHarbour data access logic (ISAM & DML) this is enought.
Only in this way you will have an RDD compliant with any other and you can say that

"SQLRDD allows transparent access to the most important relationary data bases of the market, accurately in the same way as if it makes with archives DBF".

Addressing problems of coexistance of ISAM with SQL is not required to an RDD. It would be a task of the programmer.


These are not manipulations. These are facts!

Roberto

Marcelo Lombardo

unread,
Apr 17, 2014, 8:30:39 AM4/17/14
to
Hello, David

Thanks for your suggestion. I enumerated your steps to make it easier to explain:

1 - Record current position in table,
2 - Make a copy of the lock list,
3 - Release all locks,
4 - Visit each member of the recorded lock list, except the one you want deleted, Apply the lock if possible, to each record in turn,
5 - return the current locklist.

Something like this came across my mind but there are some issues:

- between 3 and 4 the records can be locked or deleted by another connection
- to release all locks may conflict with and transaction you explicit opened
- serious performance issues. We're talking about many SQL statements goint to and back the server for a single unlock.

So I prefer to refactory any bad code that depends on this DBF-only feature (hopefully no more than 0,01% of code) and have a RDD that runs at maximum speed. That's the kind of trade off I can live with :-)

Best,

Marcelo

Roberto Romeo

unread,
Apr 18, 2014, 2:43:46 PM4/18/14
to
Hello David,

thank you for your post.

I agree with Marcelo the solution you suggest has the limits that Marcelo pointed out.

I think that the consequences of this problem can be limited by the partial solution that I suggested in my previous post.

Now, an application locking several records with dbrLock(xRecno)/dbrUnlock(xRecno) and counting on dbrLockList(), believes that everything is working but, as soon as this application releases the first locked record, all the other records are released, and this instance of the application and any other instance running on any client is potentially failing because can manipulate records that are presumed to be locked.
Moreover any other application using SQL syntax can manipulate those records.
In this situation one must change the logic of the application in every part where this approach is used.

But, if SQLRDD mantains a list of locked records (using the table that was created for this scope), at least those kind of applications are safe again.

Moreover any application using SQL that must coexist with those applications could check for locked records in this table.

I know this is a partial solution, but it is a possible solution to mantain backwards compatibility.

In this way one's can choose to migrate old code step by step.
Now you can't choose: you have to rewrite every piece of code that was correct and was working with ISAM.

Best Regards,

Roberto

arielso...@gmail.com

unread,
Apr 18, 2014, 5:35:44 PM4/18/14
to
Hola, Roberto.

Tengo un punto de venta en modo consola con SQL server 2008 con 10 terminales locales y 8 terminales remoto.

Esta es la forma en que bloqueo y desbloqueo los folios de las notas de venta.
Hasta el momento no tengo ningĂºn problema.

Anexo una imagen.
https://dl.dropboxusercontent.com/u/94521536/Pantalla.jpg

Try
SR_BeginTransaction()
DBSelectar("FOLIOS")
DBGoTop()
Blo_Reg = .F.
DO WHILE !Blo_Reg
Blo_Reg = DBRLock()
ENDDO
CX = StrZero(CORTE_&cCJA,3)
CZ = StrZero(CORTE_Z,6)
cFOLIO = FNOTAS
Replace FNOTAS With FNOTAS + 1
DBCommit()
DBUnlock()

SR_CommitTransaction()
SR_EndTransaction()

cFOLIO = StrZero(cFOLIO,8)
cTIME = Time()
@ 19, 50 SAY " "
@ 19, 63 SAY "NOTA : "+ cFOLIO Color Color4
Catch
SR_RollBackTransaction()
PlsWait(.T.,"Error al grabar folio..",22,48,24,79)
Inkey(3)
PlsWait(.F.)
End Try

Saludos !!


On Friday, April 11, 2014 8:37:27 AM UTC-5, Roberto Romeo wrote:

Patrick Mast

unread,
Apr 19, 2014, 5:54:06 AM4/19/14
to
Hey guys,

We use SQLRDD in our WinFakt! app for years now, and never had the need to use the locking method that Roberto uses. We don't have any locking problems at all.

Maybe its in the different way of coding?

Patrick

jun...@gmail.com

unread,
Apr 19, 2014, 9:01:09 AM4/19/14
to
I converted a large application to MySQL using SQLRDD without any record locking issues: the application does not lock multiple records in a table.

There were two reasons for not moving ahead with SQLRDD: Browses were slow, and that ordKeyNo() didn't work. I was unable to find suitable workarounds for both.

Instead, I am evaluating other tools for managing data in a server setting that are closer to the spirit of Clipper - Simplicity.

Regards.
Ash

johnson russi tello

unread,
Apr 19, 2014, 9:09:07 AM4/19/14
to

arielso... favor probar con los cambios hechos : Ejp :
se deben realizar n cantidad de replace sobre la misma tabla ( operaciones, replace, operaciones con valor resultante, replace , validaciones, replace, cĂ¡lculos , etc )el tiempo usado en el ciclo completo puede ser mas de 2 segundos. La situaciĂ³n que presenta problemas es : al ingresar una segunda estaciĂ³n a realizar el mismo proceso y la App esta en el ciclo de MULTIPLES replace, el mĂ©todo SR_BeginTransaction() libera los bloqueos y estĂ¡ segunda llamada inicia el proceso sin que la primera instancia haya terminado.
Se asume por lĂ³gica que la segunda estaciĂ³n requiere los datos actualizados por el primer llamado :

Resultado actual : el replace efectivo es el segundo(estaciĂ³n 2), pero si tener en cuenta el valor resulante del replace o procesos de la estaciĂ³n 1

Nota : hacer el mismo proceso sin el SR_BeginTransaction() : resultado el esperado bloqueo efectivo datos actualizados y retomados , tal cual como se ejecuta con "DBFCDX".



Try
SR_BeginTransaction()
DBSelectar("FOLIOS")
DBGoTop()
Blo_Reg = .F.
DO WHILE !Blo_Reg
Blo_Reg = DBRLock()
ENDDO
CX = StrZero(CORTE_&cCJA,3)
CZ = StrZero(CORTE_Z,6)
cFOLIO = FNOTAS
Replace FNOTAS With FNOTAS + 1

ALERT("SIMULACION DE EJECUCION DE OTROS REPLACE") // NUEVO
Replace FNOTAS With FNOTAS + 2 // NUEVO
Message has been deleted

arielso...@gmail.com

unread,
Apr 19, 2014, 11:23:04 AM4/19/14
to
Hola, Johnson.

De esta forma actualizo el detalle de cada nota de venta en (n) mĂ¡quinas funcionando, sin problema.

PROCEDURE GRA_DET()
Try
SR_BeginTransaction()
DBSELECTAR("DETNOTAS")
DETNOTAS->(DBAPPEND())
REPLACE DETNOTAS->FOLIO WITH cFOLIO
REPLACE DETNOTAS->KEY WITH cKEY
REPLACE DETNOTAS->CVE WITH cCVE
REPLACE DETNOTAS->FECHA WITH DATE()
REPLACE DETNOTAS->CANT WITH nCANT
REPLACE DETNOTAS->P_PUB WITH nP_PUB
REPLACE DETNOTAS->P_NETO WITH nPRECIO
REPLACE DETNOTAS->COMO WITH cCOMO
REPLACE DETNOTAS->OFERTA WITH nP_OFER
REPLACE DETNOTAS->COSTO_P WITH ARTICULO->COSTO
REPLACE DETNOTAS->COSTO_U WITH nCOSTO_U
REPLACE DETNOTAS->UNIDAD WITH cUNI
REPLACE DETNOTAS->IVA WITH xIVA
REPLACE DETNOTAS->SIVA WITH cSIVA
REPLACE DETNOTAS->SINV WITH cSINV
REPLACE DETNOTAS->MARCA WITH cMARCA
REPLACE DETNOTAS->LINEA WITH cLINEA
REPLACE DETNOTAS->PROV1 WITH cPROV1
REPLACE DETNOTAS->PROV2 WITH cPROV2
REPLACE DETNOTAS->CANCELA WITH cCMEN
REPLACE DETNOTAS->autorizo WITH cautori
if cdepto = "00004" .or. cdepto = "00025" .or. cdepto = "00026"
replace DETNOTAS->salchi with csalchi
endif
if cdepto = "00005" .or. cdepto = "00016"
replace DETNOTAS->carne with ccarne
endif
if cmanu = "*"
replace DETNOTAS->manual with "*"
cmanu := ""
endif
DETNOTAS->(DBCOMMIT())

CCMEN := SPACE(30)
CAUTORI:= ""

IF cSINV = "S"
DBSELECTAR("ARTICULO")
DBSETORDER(1)
ARTICULO->(DBSEEK(cKEY))
IF ARTICULO->LIGASN = "N"
BLO_REG = .F.
DO WHILE !BLO_REG
BLO_REG = ARTICULO->(RLOCK())
ENDDO
REPLACE ARTICULO->EXIS WITH ARTICULO->EXIS - nCANT
REPLACE ARTICULO->ACUM_PXV WITH ARTICULO->ACUM_PXV + nCANT
REPLACE ARTICULO->ACUM_MXV WITH ARTICULO->ACUM_MXV + nIMP
ARTICULO->(DBCOMMIT())
ARTICULO->(DBUNLOCK())
ELSE
IF ARTICULO->(DBSEEK(LIGA))
BLO_REG = .F.
DO WHILE !BLO_REG
BLO_REG = ARTICULO->(RLOCK())
ENDDO
REPLACE ARTICULO->EXIS WITH ARTICULO->EXIS - nCANT
REPLACE ARTICULO->ACUM_PXV WITH ARTICULO->ACUM_PXV + nCANT
REPLACE ARTICULO->ACUM_MXV WITH ARTICULO->ACUM_MXV + nIMP
ARTICULO->(DBCOMMIT())
ARTICULO->(DBUNLOCK())
ENDIF
ENDIF

claveant:=mensaje:=space(10)

DBSELECTAR("ARTICULO")
DBSETORDER(1)
ARTICULO->(DBSEEK(CKEY))
if articulo->ligasn = "S"
ckey := articulo->liga
mensaje := articulo->cve
else
mensaje :="VENTA"
endif
ARTICULO->(DBSEEK(CKEY))

DBSELECTAR("MOVIMIEN")
MOVIMIEN->(DBAPPEND())
REPLACE MOVIMIEN->LLAVE WITH ckey
REPLACE MOVIMIEN->FECHA WITH DATE()
REPLACE MOVIMIEN->HORA WITH TIME()
REPLACE MOVIMIEN->DOCUMENTO WITH cFOLIO
REPLACE MOVIMIEN->REFER WITH mensaje
IF NCANT >=0
REPLACE MOVIMIEN->SALIDA WITH NCANT
ELSE
REPLACE MOVIMIEN->ENTRADA WITH abs(NCANT)
ENDIF
REPLACE MOVIMIEN->EXISTENCIA WITH articulo->exis
REPLACE MOVIMIEN->USUARIO WITH ANOMUSU
MOVIMIEN->(DBCOMMIT())
ENDIF
SR_CommitTransaction()
SR_EndTransaction()
Catch
SR_RollBackTransaction()
PLSWAIT(.T.,"Error al grabar detalle de la nota...",22,38,24,79)
INKEY(4)
PLSWAIT(.F.)
lERROR = .T.
End Try
DBSELECTAR("ARTICULO")
DBSETORDER(2)
Return



On Friday, April 11, 2014 8:37:27 AM UTC-5, Roberto Romeo wrote:

johnson russi tello

unread,
Apr 19, 2014, 11:35:41 AM4/19/14
to
Arielso ... buenos dĂ­as

Si, asĂ­ es que la mayorĂ­a(todos) lo hacemos, pero el problema se presenta cuando se libera el bloqueo, ante la ejecuciĂ³n de n replaces( largo periodo de tiempo). EvalĂºelo con este cambio, agregue un wait en medio de los replace, y verifique si el bloqueo de las segundas estaciones se produce. ejp:

BLO_REG = .F.
DO WHILE !BLO_REG
BLO_REG = ARTICULO->(RLOCK())
ENDDO
REPLACE ARTICULO->EXIS WITH ARTICULO->EXIS - nCANT
REPLACE ARTICULO->ACUM_PXV WITH ARTICULO->ACUM_PXV + nCANT

alert("simulando un largo proceso") // asĂ­ sean milisegundos
// supuestamente las segundas llamadas(otras estaciones - pc) deberĂ­an
// no ingresar hasta que se libere este wait.

REPLACE ARTICULO->ACUM_MXV WITH ARTICULO->ACUM_MXV + nIMP
ARTICULO->(DBCOMMIT())
ARTICULO->(DBUNLOCK())

// evaluelo también sin el begintransaction

Saludos..
Espero haberme hecho entender...

arielso...@gmail.com

unread,
Apr 19, 2014, 2:09:01 PM4/19/14
to
Buenas tardes, Johnson.

El resultado de la prueba es:
1. Abro una sesiĂ³n como lo sugeriste
2. Abro otras 2 sesiones
3. la primera sesiĂ³n tiene el bloqueo y en las otras 2 sesiones no puedo seguir vendiendo hasta que pulso <Ok> desbloqueo en la primera sesiĂ³n.

Anexo imagen :

https://dl.dropboxusercontent.com/u/94521536/Pantalla01.jpg

Saludos !!





On Friday, April 11, 2014 8:37:27 AM UTC-5, Roberto Romeo wrote:

johnson russi tello

unread,
Apr 19, 2014, 5:22:57 PM4/19/14
to
Arielso... buenas tardes

Efectivamente su ejemplo funciona como debe ser, mi problema estaba en que las pruebas ubique el rlock() antes del begintrasaction. De hecho no uso sqlrdd en mis App(usuario final), he venido probĂ¡ndolo y haciendo cambios en procesos crĂ­ticos (actualizaciĂ³n en bloque(sql nativo), control de la transacciĂ³n, transferencia entres compañías (bases de datos), actualizaciĂ³n de versiĂ³n, copias de soporte, etc...), y estoy en plan de solo lanzarlo comercialmente cuando lo haya probado con las funcionalidades de sqlrdd y la misma compatibilidad con fivewin()

Gracias

Saludos...



Roberto Romeo

unread,
Apr 21, 2014, 2:45:47 PM4/21/14
to
Patrick and Arielso,
we have no problems with simple locks, but we need to lock more than one record at a time. Changing this means change a lot of code. That's what we are doing.

We are the developpers (the product is sold e supported by our distributors) of an ERP that manage accounting, finance, sales, manufacturing and product data management, warehouse management, manufacturing execution system, and reporting, supply chain management, customer relationship management and e-business.

The first version of the product was developed between 1979-1982.
At that time we used CP/M, MS Basic and flat files.
We migrated to Novell Btrieve in 1988/89.
Than we migrated to Clipper between 1992 and 1993.

In the golden age the product was used by more than 800 companies, but nowdays most of them are using another ERP developped in Delphi.
But still there are about 100 companies using it.

With SQLRDD we were planning of migrate those customers to a more robust solution. We compared OTC Mediator, ADS and SQLRDD. Unfortunatelly I made the wrong choice.

Too many problems. After 4 month of work we are still facing with bugs, and we have about 30-40% of the code to be rewritten in order to have the same performance that we had with DBFCDX.
Batch processes that use to be performed in 20/30 secs with DBFDX now with SQLRDD takes 4-6 minutes. Reports are 3 to 5 times slower.
We are still searching a solution for the bug of SR_SETFILTER() not working with conditional index (the bug is documented but nobody is working at it).
We still have tbrowse:forcestable() that sometimes moves to the first record of the controlling index, and tbedit tha duplicate the row at the cursor, but it is not easy to produce a self contaned demo of these 2 problems.

Regards,

Roberto

arielso...@gmail.com

unread,
Apr 21, 2014, 3:40:11 PM4/21/14
to
Hola, Roberto.

Nunca utilizo SR_SetFilter en SqlRDD.
Prefiero utilizar cĂ³digo sql (Select campo1, campo2 from mybase where campo1 = 'xxxx') y para mis reportes utilizĂ³ Fastreport Ă³ excel segĂºn decida el usuario.

La ventaja de SQLRDD es que puedes escribir cĂ³digo para los dos mundos (xBase y sql ).

En el caso de los reportes, tengo reportes que solamente tardan entre 5 Ă³ 12 segundos con un internet inhalĂ¡mbrico de 1.5 MB

Si pudieras publicar el cĂ³digo fuente que estas modificando, para ver que parte estĂ¡ causando problema.

Saludos !!



Aquí un pequeño ejemplo:

PROCEDURE REPDETA()
local np , nCol, nError, oSql, nPos , oExcel, oLibro, OHoja, X, xFILE

BXX(11,10,16,35,,3,8,cCAJA1)
@ 11,14 SAY "Detalle de Notas por facturar" COLOR COLOR4

DO WHILE .T.

dFECFIN = DATE()

@ 13,12 SAY "HASTA # "

@ 13,22 GET dFECFIN PICT "@D" VALID DTOC(dFECFIN) != " / / "
READ
IF LASTKEY() = 27
EXIT
ENDIF

cSQL = "SELECT N.FOLIO, N.FECHA, D.IDHRAE, A.ARTICU01, A.FORMULA, D.CANT, "
cSQL += "D.P_NETO, (D.CANT * D.P_NETO), A.ARTICU21, N.NOMAREA, D.T_IVA, N.STA FROM "
cSQL += "NODETALL D INNER JOIN NOENCABE N ON N.FOLIO = D.FOLIO "
cSQL += "INNER JOIN ARTICULO A ON A.ARTICU22 = D.LLAVE "
cSQL += "WHERE N.FECHA >= '01-01-2012' AND N.FECHA <= " + "'" + DTOC(dFECFIN) + "'" + " AND (N.STA <> '2') AND (N.STA <> '5') ORDER BY D.IDHRAE, N.FECHA"

aRet :={}
oSql := SR_GetConnection()

oSql:exec( cSQL,,.t.,@aRet)

IF len(aRet) > 0
REPDETEXCEL()
ELSE
PLSWAIT(.T.,"No existen notas pendientes a esta fecha ...")
inkey(2)
PLSWAIT(.F.)
Endif
EXIT
ENDDO
RETURN

Andrzej Morgiewicz

unread,
Apr 22, 2014, 6:30:10 AM4/22/14
to
Hi all
I've read this thread with great interest because i also bought sqlrdd and i'm almost to start porting my apps to MSSQL 2012. And I don't understand what are you arguing about with Roberto, because he is absolutely right. The true problem is not that something works differently, the problem is that these differencies are undocumented. I wonder docs is up to date looking at docs's files timestamps. Also all problems/uncompatibilities like those mentioned in Roberto's posts should be immediatly put to docs or solved.
Andrzej Morgiewicz

dlzc

unread,
Apr 22, 2014, 11:13:08 AM4/22/14
to
Dear Andrzej Morgiewicz:

On Tuesday, April 22, 2014 3:30:10 AM UTC-7, Andrzej Morgiewicz wrote:
...
>
> I've read this thread with great interest because
> i also bought sqlrdd and i'm almost to start
> porting my apps to MSSQL 2012. And I don't
> understand what are you arguing about with Roberto,
> because he is absolutely right.

What he is arguing, is that a DBF-centric application, suffers under SQL. Just like you have to make code changes to use GUI, you *have* to make code changes to use SQL.

Roberto may be right in the sense that documentation does not describe the changes to code that are necessary to effectively use SQL.

http://sqlmag.com/database-administration/15-steps-convert-access-data-sql-server
... and this is obviously not DBF, but had similar support to the record-centric view of data processing.

> The true problem is not that something works
> differently, the problem is that these
> differencies are undocumented.

SQL is well documented, and DBFSQL implements this. In this particular "reported bug", multiple records in the same table can be updated more efficiently by instructing the server to do this... it can negotiate locks, and make the replacements, if it just knows what the key values need to be.

> I wonder docs is up to date looking at docs's
> files timestamps. Also all problems /
> uncompatibilities like those mentioned in Roberto's
> posts should be immediatly put to docs or solved.

The variances are documented here. Now.

The "uncompatibility" is SQL access et al, in a Clipper variant language. As implemented, DBFSQL complies with ANSI SQL.

There are also a few threads where people discuss what they learned in making this conversion, both here and comp.lang.clipper.

Roberto has every right to feel however he feels. You have every right to feel that he is misunderstood, or that his message is lost. What else is going on in this thread, are attempts to teach him improvements. Additions for posterity. Please try and not misunderstand intents here.

I hate the docs in general, since they stopped at the language reference, and omitted any sort of guides. My offers of help to improve the docs were similarly refused. But we have 5 or 6 people here that have weighed in (so far) that have done such conversions, and could easily add to a thread what they found, how they worked around it, and we can *build our own* docs.

There are 1000000 ways to get from application start to updated data files. No sort of documentation can cover every approach. But each person that provides their "local path map" of how they got from 1 to n... builds a more complete map of the terrain.

Geees... I should have been a preacher! ;-P

David A. Smith

Roberto Romeo

unread,
Apr 24, 2014, 11:31:23 AM4/24/14
to
Dear Andrzej,

I'm happy to read your comment.
Stay tuned because I'm going to publish samples that will show other bugs.

Best regards,

Roberto

Roberto Romeo

unread,
Apr 24, 2014, 12:01:56 PM4/24/14
to
> What he is arguing, is that a DBF-centric application, suffers under SQL. Just like > you have to make code changes to use GUI, you *have* to make code changes to > use SQL.

David maybe you are misunderstanding my post.
Here I'm showing everybody that xHarbour.com is misrepresenting the features of SQLRDD in their website and in the product documentation.

>
> Roberto may be right in the sense that documentation does not describe the changes to code that are necessary to effectively use SQL.
>

David, I don't need that the documentation tells me how to change my code BUT I need that the documentation tells me which features of the language are not supported anymore.

>
> The variances are documented here. Now.
>
David, I have bought a product that doesn't do what the producer say it should do. I'm documenting the product. Who pays for my time?

>
> Roberto has every right to feel however he feels. You have every right to feel that he is misunderstood, or that his message is lost. What else is going on in this thread, are attempts to teach him improvements. Additions for posterity. Please try and not misunderstand intents here.
>

David, in this and in the other threads I'm documenting bugs and xHarbour.com is trying "to wash their hands" about these bugs.
Why nobody is correcting the bug of SR_setfilter()?

Regards,

Roberto

dlzc

unread,
Apr 24, 2014, 3:54:18 PM4/24/14
to
Dear Roberto Romero:

On Thursday, April 24, 2014 9:01:56 AM UTC-7, Roberto Romeo wrote:
> > What he is arguing, is that a DBF-centric
> > application, suffers under SQL. Just like
> > you have to make code changes to use GUI,
> > you *have* to make code changes to use SQL.
>
> David maybe you are misunderstanding my post.

No, I am trying to give you the benefit of the doubt, which you gruesomely thrown back in my face.

> Here I'm showing everybody that xHarbour.com
> is misrepresenting the features of SQLRDD in
> their website and in the product documentation.

False. xHarbour (and Harbour) claim to be nearly 100% Clipper 5.3 compatible. No claims on compatibility can possibly apply to SQL, Clipper did not offer this. So they (xHarbour) settled for 100% ANSI compatibility on the SQL part, and fell back to "some features are not implemented in all RDDs"... just like occurs in Clipper.

I understand you are incensed. I understand you have spent lots of time finding out how and where ANSI SQL does not behave like you could program DBFCDX before. And no need for me to weigh in on documentation yet again.

Clipper and its variants tend to ignore some faults, and are loosely typed. Which means you have to know what you are doing.

> David, I have bought a product that doesn't
> do what the producer say it should do.

False, I think.

> I'm documenting the product. Who pays for my time?

Who pays for *my* time? I have put months into responding and helping people here. I don't get paid to to this, I do this for the good of the community. The community helped me when I needed help.

They have sold more than 1 of these SQLRDD packages. I cannot believe you are the only one that did not have to discover that you can make SQL fail in the eyes of your customer (due to speed), because you treat it like DBFCDX. You will be "justified" in claiming it is not your fault, but is that Truth?

Ultimately, you have stated a problem. Others have supplied the workarounds or answers. Any other communications may have been nice, may have been to voice anger, but none affect the posterity *I* serve. How much time did you spend documenting the "problem"? That is the only "payable" time that counts in this context. The many answers you receive are also "payable". Surely you have helped others with problems here? What is your payment then?

David A. Smith

fatfat

unread,
Apr 27, 2014, 11:03:43 AM4/27/14
to
Just a Info and 2 cents :p

We tried all most all possible solution such as Foxpro, Clip4win, Flagship,
xBase++, VO, DBMAX, ...... OTC Mediator, ADS since 2000.
We have ported our system to xharbour + SQLRDD from 2004-2006 for internal
testing and planning to migrate to it's environment, it work 85% fine but
have 10%-15% not working like DBFCDX.
Due to different nature of data handling between DBFCDX and SQLRDD, the
converted program is put UNDER the desk b'cos we can't affort to invest more
manpower on convert/test/debug this changes

Therefore now,
for console application, we stay with xharbour + dbfcdx + telnet server for
multi-site access ( this give me 100% no changes and get 100% same speed
when using from different site) :)
for gui application, we stay with wvw/minigui + xharbour + terminal service
(Windows 2003 Server) or remote apps (Windows 2008 Server) - this allow us
to focus on GUI changes only still retain 100% speeds access / coding of
data.





"Roberto Romeo" 在郵件張貼內容主旨
2ca3a00e-d273-46f0...@googlegroups.com 中撰寫...

Roberto Romeo

unread,
Apr 28, 2014, 6:30:30 AM4/28/14
to
Dear David,

> David maybe you are misunderstanding my post.
>>No, I am trying to give you the benefit of the doubt, which you gruesomely thrown back in my face.

I have nothing against you but it's seems to me that somebody is trying to turns fatcs into opinions. I'm speaking about proved facts.

SR_MGMNTLOCKS was created to simulate locks but was abandoned.
Thus Dbrlock() problem for multiple records locks was well known but it is not documented.
Moreover I posted my problem in December and it tooks 4 (four!) month to xHarbour staff to give me an answer! These area facts.

It is fundamental to know these facts BEFORE you buy the product!
These facts are costing us a lot of time and money.

If we had got these information before, we would be able to:

- choose if buy or not the product;
- prepare a different offer for our customer;
- set a different time frame for the porting of our software;
- choose another product;

>I understand you are incensed. I understand you have spent lots of time finding out how and >where ANSI SQL does not behave like you could program DBFCDX before. And no need for me >to weigh in on documentation yet again.

We are using SQL since 1995 and we know how it works.
We are incensed because we are spending time in:

- Discover what is not working due to bugs in SQLRDD
- find a workaround
- restore data lost

> Clipper and its variants tend to ignore some faults, and are loosely typed. Which means you have > to know what you are doing.

The problem we are facing are not Clipper faults. Are SQLRDD faults.


> David, I have bought a product that doesn't do what the producer say it should do.
>> False, I think.

Let's take a look at OTC.pl web site. They explains how lock mechanism works so one can choose. I haven't looked at SAP (formerly SYBASE formerly EXTENDED SYSTEMS) ADS but maybe somebody here knows these products and can tell us if they are hiding lost functionalities in their pre-sale informations.

> I'm documenting the product. Who pays for my time?
>> Who pays for *my* time? I have put months into responding and helping people here. I don't >> get paid to to this, I do this for the good of the community. The community helped me when I >> needed help.

There is a difference between an open source project and a product sold. When we choosed to pay for SQLRDD we choosed to save our time and to buy an out-of-the-box solution.

>> They have sold more than 1 of these SQLRDD packages. I cannot believe you are the only one >> that did not have to discover that you can make SQL fail in the eyes of your customer (due to
>> speed), because you treat it like DBFCDX. You will be "justified" in claiming it is not your
>> fault, but is that Truth?

The problem is that we knew that we have to modify some part of our code for performance reasons, but we are spending the time that we forecasted for this scope to solve problems cause by bugs in SQLRDD.

>>Ultimately, you have stated a problem. Others have supplied the workarounds or answers. Any >>other communications may have been nice, may have been to voice anger, but none affect the >>posterity *I* serve. How much time did you spend documenting the "problem"? That is the >>only "payable" time that counts in this context. The many answers you receive are also >>"payable". Surely you have helped others with problems here? What is your payment then?


We spent more than a week to:
- log the system in order to understand what was going wrong;
- recover data loss
- create a first workaround
- document with a sample that dbrunlock was not working

Than we spent another week to:
- log the system again (and discover that the xHarbour log system is not concurrency safe)
- recover data loss
- create a new workaround
- document with a sample that recno() after an append can change before the dbrunlock() (And I'm still wondering why, since identity in T_SQL does not change even if a rollback is executed)
Than we spent about 1 day to discover that dbsetfilter() does not work with characters fields and to discover that SR_Setfilter() does not work with conditional indexes (Indexes with FOR clause) and to document it with a sample.

Than we spent another day with the last bug posted in the group.
https://groups.google.com/forum/#!topic/comp.lang.xharbour/PGAFxO5PZVM

Then we spent another couple of days with two other bugs but we are still unable to reproduce the problem in a simple code sample. (and by the way why should I go on with sample code if they don't fix the bugs?)

Obviously I don't want to be paid for this work, but it would be nice that somebody at xHarbour tell me "thank you". Instead, they wrote to me:

"If up to 50% of your code looks like the samples you shared in your last posts, you have a bigger problem and you should consider to rewrite 100%."

or

"It seems to me that you should take a little time to learn and understand SQL, than maybe, just maybe, you will understand what I mean..."

And I paid for this service 995 dollars! And I was willing to subscribe the support service!

Roberto Romeo

unread,
Apr 28, 2014, 6:32:53 AM4/28/14
to
Dear Fatfat,

On Sunday, April 27, 2014 5:03:43 PM UTC+2, fatfat wrote:
> Just a Info and 2 cents :p

Thank you for your post!

> We have ported our system to xharbour + SQLRDD from 2004-2006 for internal
>
> testing and planning to migrate to it's environment, it work 85% fine but
>
> have 10%-15% not working like DBFCDX.
>
> Due to different nature of data handling between DBFCDX and SQLRDD, the
>
> converted program is put UNDER the desk b'cos we can't affort to invest more
>
> manpower on convert/test/debug this changes
>

You used the right words! Test and debug!

We are testing and debugging SQLRDD!

dlzc

unread,
Apr 28, 2014, 10:21:15 AM4/28/14
to
Dear Roberto Romeo:

On Monday, April 28, 2014 3:30:30 AM UTC-7, Roberto Romeo wrote:
...
> And I paid for this service 995 dollars!
> And I was willing to subscribe the support
> service!

And *this* site is not for commercial support. If you have grief, you should take it up with xHarbour.com, and do it through their channels. You can even post the bug in the bug reporter, so it is public.

Let me add that if you pay with AMEX (if possible), you can even force a refund, if you do not get satisfaction.

Your opinions and dissatisfaction are clear.

David A. Smith

poopall

unread,
Apr 28, 2014, 10:03:30 PM4/28/14
to
Look I for one am grateful that his grievance has been aired in an open forum, as I may have wished to purchase the product, and now have some doubts as to is suitability for the purpose at hand. I think trying to deal with these issues behind closed doors doesn't benefit me or the wider user or potential user of your product. Only people that have already purchased the product have access to that forum, and therefore useless for those of us that have now.

As a new convert to Xharbour from Clipper and looking for a migration to SQLRDD, I now have some doubts if this transition will be as easy as it appears. I look forward to Roberto receiving a resolution to his issues and prefer that these results get displayed in this forum.

Thanks Roberto for alerting us to a potential issue with this RDD, you may have saved us endless amounts of time that you have already spent.

Gerard de Vries

unread,
Apr 29, 2014, 3:35:19 AM4/29/14
to
Dear Roberto,

We have been using SQLRDD since 2008 for a very large application, and we're
very pleased with it. Our quick switch from DBF to SQL would not have been
possible without SQLRDD. I can assure you I've seen and used my fair share
of crappy products that claim to be 100% Clipper compatible but come nowhere
close to the level of compatibility that xHarbour and SQLRDD offer. Are
there things I would do different or that I would like to see improved,
sure:
1. I would definitely make sure I'd have a basic understanding of SQL before
using SQLRDD, the way SQLRDD works makes a lot more sense once you know SQL
a bit better.
2. It would be helpful if there was a list of known incompatibilities or,
more likely, differences in implementation. Like Recno() for example, a
Recno() of a record can change in a DBF, if you pack a table. But, in
SQL(RDD) it will always stay the same cause it's stored in the SR_RECNO
column.

But, I will keep recommending SQLRDD to clients that wish to make a gradual
transition from DBF to SQL.

Kind regards,

Gerard

"Roberto Romeo" schreef in bericht
news:3f09b857-4bbc-43cf...@googlegroups.com...

Roberto Romeo

unread,
Apr 29, 2014, 12:51:19 PM4/29/14
to
Dear Poopall,

>
> Thanks Roberto for alerting us to a potential issue with this RDD, you may have saved us endless amounts of time that you have already spent.
>

Thank you for your post!

Roberto

Roberto Romeo

unread,
Apr 29, 2014, 2:18:16 PM4/29/14
to
Dear Gerard,

thank you for your post!


> We have been using SQLRDD since 2008 for a very large application, and we're
> very pleased with it. Our quick switch from DBF to SQL would not have been
> possible without SQLRDD. >

The problem is how much not-documented-incompatibility clipper features are you using in you code.

If you are lucky you can be completely satisfied with SQLRDD.
But if your project is heavy using those features you start living in a nightmare of test and debug, like mine.

> 2. It would be helpful if there was a list of known incompatibilities or,
> more likely, differences in implementation. Like Recno() for example, a
> Recno() of a record can change in a DBF, if you pack a table. But, in
> SQL(RDD) it will always stay the same cause it's stored in the SR_RECNO
> column.

And this is why I'm angry!
Apart of the dbrunlock() problem (that must be documented), while is logical that recno() has been implemented with an identity (thus it keep on growing) I can't explain why recno() may change between a dbappend() and an unlock() on a shared table. This is not logical and should be documented.

>
> But, I will keep recommending SQLRDD to clients that wish to make a gradual
> transition from DBF to SQL.

I think that transition could be gradual or not depending on project.

Best regards,

Roberto

Ron Pinkas

unread,
May 14, 2014, 10:52:26 AM5/14/14
to
Dear Roberto,

I am here to officially apologize to you, and to all others who have been
exposed to this or similar issues.

I am specifically apologizing for the fact that we allowed ourselves do
engage in personal friction rather than benefit and improve our docs and
product, based on the information you provided.

Sometime we (human beings) get frustrated and we may easily lose focus, and
get carried into personal confrontations. This is especially true about
programmers, and even more so when we are forced to protect some aspects of
behavior of our code, or choices and compromises we made while developing
our code.

We truly do wish to make SQLRDD 100% compatible, and it is an uphill battle,
which we are facing every day. In the process, I am sure you are aware there
are times when we must face very difficult and frustrating choices.

Either way, no one can ever argue, that every minute known compatibility
issue should be documented, and thus we should have thanked you for your
input, and not allow ourselves to be defensive.

If you are dissatisfied with your purchase, I am here to offer you a
complete refund.

Again, my mot sincere apologies,

Ron

"Roberto Romeo" <rfuma...@gmail.com> wrote in message
news:2ca3a00e-d273-46f0...@googlegroups.com...
> (Marcelo, let me say - with the same words you write to me - that "It
> seems to me that you should take a little time to learn and understand
> "It seems to me that you should take a little time to learn and understand
> SQL, than maybe, just maybe, you will understand what I mean..."
>

Roberto Romeo

unread,
May 19, 2014, 2:35:06 PM5/19/14
to
Dear Ron,

I'm glad that finally xHarbour.com acknowledged the problem and I really appreciate your apologies.

You offer a refund, but this can't be a solution.
We have invested in migrating to SQLRDD and heavily invested in debug and patch bugs. Migrate to another product is not an option.

Maybe it would be better to work together to fix problems.

We are working to isolate other bugs and I hope we will have some new code samples within a couple of days.

You can contact me in private and we could find an agreement on how to proceed: please, let me know if you agree in trying to pursue a solution together.

Sincerely,

Roberto

Roberto Romeo

unread,
Dec 10, 2014, 3:56:56 PM12/10/14
to
Dear Ron,

unfortunatelly I'm still waiting for a contact from you since May 19.

Apart of the issues of this thread, we are also waiting for SR_SETFILTER bug fix.

It was posted on this thread

https://groups.google.com/d/msg/comp.lang.xharbour/czD9kWNWKkI/SKBsTodE2uoJ

and also posted to the support but neither here nor there we had answers.

Regards,

Roberto

Ron Pinkas

unread,
Dec 16, 2014, 4:52:22 PM12/16/14
to
Hey Roberto,

Sorry, I never meant to mislead you, and I am not sure what kind of contact
you where expecting. I do remember apologizing on behalf of xHarbour.com,
for failing to meet your expectations. To the best of my memory, you did
encounter some issues, which are absolutely valid, but fall under the
category of "extremely difficult to address in a an efficient manner". Sadly
there appear to also have been some issues on personal basis, which made it
even more possible to resolve.

I have just talked to our SQLRDD product owner, Marcelo Lombardo, and we
would attempt to review your issues one more time, in the hope we can find
some acceptable resolutions.

Your cooperation will be greatly appreciated.

Please forward a detailed summary of your issues to me personal email
Ron.Pinkas at out domain name.

Ron



"Roberto Romeo" <rfuma...@gmail.com> wrote in message
news:8046b74c-8149-4bfa...@googlegroups.com...

mar...@omie.com.br

unread,
Dec 17, 2014, 10:15:24 AM12/17/14
to
Roberto,

The problem you report on dbSetFilter() and SR_Filter() is actually a side effect of a great optimization. We parse the filter expression and if if works in SQL we pass it to be solved in server side. This technique allows filters to run infinite times faster than client-side filtering.

The side effect lies on NULL contents. It cannot be compared via <> or =. The only and easy solution is to change the table column structure to NOT NULL. It can be done either via dbCreate() (5th array parameter, if I'm not wrong) or using simple DML commands in database console.

Once field is not nullable, SQLRDD will detect and store ' ' instead of NULL automatically, and filter will work correctly at its best speed.

About the locking scheme, as I said many times over, it will not be changed because there's nothing to be done. We depend on back end server resources and there's no sense to push Microsoft to change SQL Server lock features. So you are correct, we are not 100% Clipper compatible in this item, and I admit that.

Marcelo

Andrzej Morgiewicz

unread,
Dec 29, 2014, 9:09:49 AM12/29/14
to
Hi,
After transferring all my dbfs to SQL Server and linking applications with SQLRDD i've started to test my applications with SQLRDD in multiuser environment.
1. You can open exclusive tables by different instaneces of application
2. If opened exlusive: you can flock(), rlock() or update in both.
3. If opened shared: dbrlock() works as expected : can't dbrlock() locked record OK
4 If opened shared: you can't flock() if there is another flock() in other app. OK
5. If opened shared: you can flock() even if dbrlock() is done in other application, you can dbrlock() even if flock() is done also.
6. When rlock() is established and you try to update field (with no locking ) in locked record in other app then no error is thrown ( as in DBFCDX ) but infinite loop starts until dbunlock is issued in first app. Maybe this has something common with transactions levels i'm not aware of. ( still SQL newbie )

For me points 3 and especially 6 are the main problem because:

1. I use sometimes flock() which can't be successfull over rlock() (as in DBFCDX)
2. I planned to rewrite common parts of code to move to "try / catch" model with transactions and i've suppoused then inside this block the error will be thrown if a record/file is locked. Now I know this seems wrong assumption and rewriting of all code is needed because YOU CAN'T MIX RLOCK() WITH UPDATE ERROR CATCHING INSIDE TRANSACTION like is adviced. I'have a lot of personalized *.hrb procedures with CDX style coding i hoped to preserve some time after moving to SQL.

If i'm wrong or you can help me with understanding SQLRDD I will be gratefull.

Andrzej Morgiewicz

PS. For test and development:
I'm using SQL Server 2012,
I'm using SQLRDD libs from 2013.08.25 and 2014.08.11 ( i've got from Rafael, but this shows wrong numeric field len ),
I'm using current SVN xHarbour,
I'm also testing with xbscript linked with above libs.

PS. I can't install my current libs from xHarbour.com because after downloading and istalling i'm getting empty lib folder :(

cul...@gmail.com

unread,
Dec 29, 2014, 11:49:32 AM12/29/14
to
Hi Andrzej Morgiewicz


Can you post the sql script to create the table that you are telling that field size if wrong?

Regards
Luiz

Andrzej Morgiewicz

unread,
Dec 30, 2014, 2:17:57 AM12/30/14
to
Hi,
Here is code sniplet based od sqlrdd sample code i use to tranfer data from existing dbfs to SQL: cDriver is "DBFCDX", cSQLRDD is "SQLRDD"
....
IF !SR_ExistTable( cSQLFile ) .AND. DBUseArea( .T. , cDriver, cFile, "ORIG" )

aStruct := ORIG->( DBStruct() )

DBCreate( cSqlFile, aStruct, cSQLRdd )

DBUseArea( .T., cSQLRdd, cSqlFile, "DEST", .F. )

APPEND FROM ( cFile ) VIA cDriver

ORIG->( DBCloseArea() )
DEST->( DBCloseArea() )
ENDIF
.....
F.E. in "MS SQL MANAGER" in table "WOBR" field "KW" is (numeric, 14, 2 ) as oryginal was, same is shown in old 2013 lib : len(str(kw)) = 14 .or. browse shows field kw 14 chars wide. In 2014.08 len(str(kw)) = 17 and same in browse ( my program or xbscript ). If you remember in 2014.06 version no decimal places was shown ! I don't test this version anymore.

Also after:

select column_name, data_type, character_maximum_length,NUMERIC_PRECISION,DATA_TYPE
from information_schema.columns
where table_name = 'PRXA_WKLADY_WOBR'

numeric_precision for column "KW" is 14.

Regards, Andrzej

cul...@gmail.com

unread,
Dec 30, 2014, 5:25:42 PM12/30/14
to
Hi Andrzej

Please post the sqlserver create table script to create this table you mentioned

Regards
Luiz

Andrzej Morgiewicz

unread,
Jan 5, 2015, 10:38:37 AM1/5/15
to
Hi,

> Please post the sqlserver create table script to create this table you mentioned

Here is sql commands log from SQLLOG.DBF:

........
21645 01/05/15 16:12:53 SELECT * FROM [PRXA_WKLADY_WOBR] WHERE 0 = 2
21646 01/05/15 16:12:53 COMMIT
21647 01/05/15 16:12:53 COMMIT
21648 01/05/15 16:12:53 DROP TABLE [PRXA_WKLADY_WOBR] /* create table */
21649 01/05/15 16:12:53 COMMIT
21650 01/05/15 16:12:53 DELETE FROM SR_MGMNTINDEXES WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe index info */
21651 01/05/15 16:12:53 COMMIT
21652 01/05/15 16:12:53 DELETE FROM SR_MGMNTTABLES WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21653 01/05/15 16:12:53 COMMIT
21654 01/05/15 16:12:53 DELETE FROM SR_MGMNTLANG WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21655 01/05/15 16:12:53 COMMIT
21656 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRAINTS WHERE TABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21657 01/05/15 16:12:53 COMMIT
21658 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRAINTS WHERE SOURCETABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21659 01/05/15 16:12:53 COMMIT
21660 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRTGTCOLS WHERE SOURCETABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21661 01/05/15 16:12:53 COMMIT
21662 01/05/15 16:12:53 DELETE FROM SR_MGMNTCONSTRSRCCOLS WHERE SOURCETABLE_ = 'PRXA_WKLADY_WOBR' /* Wipe table info */
21663 01/05/15 16:12:53 COMMIT
21664 01/05/15 16:12:53 SELECT * FROM [PRXA_WKLADY_WOBR] /* check dropped table */
21665 01/05/15 16:12:53 COMMIT
21666 01/05/15 16:12:53 COMMIT
21667 01/05/15 16:12:53 CREATE TABLE [PRXA_WKLADY_WOBR] ( [CZLO_KEY] VARCHAR (36) ,
[MSK_KEY] VARCHAR (46) ,
[NP] CHAR (8) ,
[TO] CHAR (10) ,
[MR] CHAR (2) ,
[RR] CHAR (4) ,
[KO] VARCHAR (30) ,
[KP] VARCHAR (30) ,
[ST] NUMERIC (1,0) ,
[SR] CHAR (3) ,
[SD] CHAR (2) ,
[ND] NUMERIC (6,0) ,
[NK] NUMERIC (4,0) ,
[KW] NUMERIC (14,2) ,
[DD] DATE NULL ,
[TP] DATE NULL ,
[DR] DATE NULL ,
[DK] DATE NULL ,
[UW] VARCHAR (30) ,
[PF] VARCHAR (25) ,
[DKR] BIT,
[USR] CHAR (1) ,
[SR_RECNO] NUMERIC (15,0) IDENTITY,
[SR_DELETED] CHAR (1) NOT NULL
)
21668 01/05/15 16:12:53 COMMIT
21669 01/05/15 16:12:53 CREATE CLUSTERED INDEX PRXA_WKLADY_WOBR_SR ON [PRXA_WKLADY_WOBR]([SR_RECNO]) /* Unique Index */
21670 01/05/15 16:12:53 COMMIT
21671 01/05/15 16:12:53 DELETE FROM SR_MGMNTTABLES WHERE TABLE_ = 'PRXA_WKLADY_WOBR'
21672 01/05/15 16:12:53 COMMIT
21673 01/05/15 16:12:53 INSERT INTO SR_MGMNTTABLES ( TABLE_ , SIGNATURE_, CREATED_, TYPE_, REGINFO_ ) VALUES ( 'PRXA_WKLADY_WOBR','MGMNT 1.72', '2015010516:12:53','TABLE',' ' )
21674 01/05/15 16:12:53 COMMIT
21675 01/05/15 16:12:53 SELECT MAX( [SR_RECNO] ) FROM [PRXA_WKLADY_WOBR] /* Counting Records */
21676 01/05/15 16:12:53 SELECT A.* FROM [PRXA_WKLADY_WOBR] A WHERE 1 = 0 /* Open Workarea */
21677 01/05/15 16:12:53 COMMIT
21678 01/05/15 16:12:53 SELECT TABLE_,SIGNATURE_,IDXNAME_,IDXKEY_,IDXFOR_,IDXCOL_,TAG_,TAGNUM_ FROM SR_MGMNTINDEXES WHERE TABLE_ = 'PRXA_WKLADY_WOBR' ORDER BY IDXNAME_, TAGNUM_
21679 01/05/15 16:12:53 SELECT TOP 12 A.* FROM [PRXA_WKLADY_WOBR] A ORDER BY A.[SR_RECNO] /* GoTop */
21680 01/05/15 16:12:53 COMMIT
..............

OLD LIB
USE PRXA_WKLADY_WOBR
? LEN(STR(KW))
14
NEW LIB (2014.08)
USE PRXA_WKLADY_WOBR
? LEN(STR(KW))
17

BUT...

OLD LIB
USE PRXA_WKLADY_WOBR
? LEN(STR(ND))
6
NEW LIB (2014.08)
USE PRXA_WKLADY_WOBR
? LEN(STR(ND))
6

SO I SUPPOUSE PROBLEM HAS SOMETHING COMMON WITH DECIMALS :)

Regards
Andrzej Morgiewicz

0 new messages