ADO RDD xHarbour

[AHF]

Lucas,

Try it like this:

//sql expressions only
SET ADO TABLES INDEX LIST TO {{ "TABLE1", {"TEST1", "FIRST"} ,;
{"TEST2", "LAST"} ,;
{"TEST3", "AGE"} },;
{"LUCAS", {"LUCAS1", "NOMBRE"} ,;
{"LUCAS2", "NOMBRE", "WHERE NOMBRE = 'AGAMENON'"},;
{"LUCAS3", "NOMBRE","WHERE ACTIVO = TRUE" } } } //Maybe its not TRUE but 1 I dont know

//clipper expression only
SET ADODBF TABLES INDEX LIST TO { { "TABLE1", {"TEST1", "FIRST"} ,;
{"TEST2", "LAST"} ,;
{"TEST3", "AGE"} },;
{"LUCAS", {"LUCAS1", "NOMBRE"} ,;
{"LUCAS2", "NOMBRE", "NOMBRE = 'AGAMENON'"},;
{"LUCAS3", "NOMBRE", "ACTIVO = .T." } } }

Note that when doing this with INDEX ON you only have to use standard clipper expression INDEX ON NOMBRE TO LUCAS3 FOR ACTIVO = .T.

[lucasdebeltran]

Antonio,

I guessed that conditions and functions went only to SET ADODBF, as to keep Clipper compatibility.

Now I realze I have to indicate the condition or functions in both cases -SET ADO TABLES and SET ADODBF TABLES-, so in order to be clear, why use only one way?.

I also talked with other users and they agree on considering such point very confusing.

Dates are working fine.

That´s the problem of using such an old version of xHarbour.

Thank you.

[AHF]

Lucas,

Now I realze I have to indicate the condition or functions in both cases -SET ADO TABLES and SET ADODBF TABLES-, so in order to be clear, why use only one way?.

I also talked with other users and they agree on considering such point very confusing.

Dont understand what you mean.

Dates are working fine.

That´s the problem of using such an old version of xHarbour.

Great!
Do you know any workaround? I cant update now.

[lucasdebeltran]

Antonio,

For and while and all Index parameters should be indicated either one way, Clipper or sql format.

[AHF]

Lucas,

I guessed that conditions and functions went only to SET ADODBF, as to keep Clipper compatibility.

Please note that functions need to be different.

Ex

SQL UCASE = CLIPPER UPPER
SQL LCASE = CLIPPER LOWER

So we must enter each corresponding function into SET ADO or SET ADODBF.

Also please note that as in other rdds such as ADS all functions and vars in indexes must be evaluated before sending it to the server as the funcs or vars do not exist there.

For and while and all Index parameters should be indicated either one way, Clipper or sql format.

It can be done but its a lot of program work because then adordd would have to make all parsing of all expressions. Its a lot of program work, trial and subject to bugs.

We always need 2 expressions one for clipper another for sql and they can be much different.
All clipper functions as ordfor() indexkey() etc need the clipper expression.
You cannot run indexkey() over a sql expression "NAME+AGE+DATE"
You need "NAME+STR(AGE)+DTOC(DATE)" but you can not run sql with this.

This is much simpler and if there is a problem we know immediately where it is.
This is only defined one place in the app and thats it.

If someone wants to do it like that great but I dont have the time.

[AHF]

Lucas,

Dates did you try it with mysql?

[lucasdebeltran]

Antonio,

Not yet, but I remember that there were some issues with dates. I have to check.

Also, about updating xHarbour, unless there is a pcode change, which I doubt, you will be able to keep your Fivewin version, which I guess is what you want.

[AHF]

Lucas,

With MySql the date field problem is:

If the new value its a empty date and we do:

Code: Select all  Expand view  RUN

           
IF ADO_FIELDSTRUCT( oRecordSet, nField-1 )[2] $ "DT" .AND. EMPTY(xValue)
   xValue :=0
ENDIF
           
oRecordSet:Fields( nField - 1 ):Value := xValue
           
oRecordSet:Update()
 

Its ok but the field date keeps a value of 1899-12-31 can not be!

If we use instead of xValue :=0 xValue :=ADONULL() date field is empty but :Update() errors!

Any ideas?

[lucasdebeltran]

Antonio,

I have found those big errors so far:

DbCommitAll() produces an error:



This filter produces a GPF and a hb_out.log:

SET FILTER TO alltrim(AUXILIAR->RELACION) == alltrim(cValToChar( cCodigo ))

I also have minor things for other post.

Thank you.

[AHF]

Lucas,

This seems not serious.
Dbcomittall() call ado_flush() that calls :updated() I dont know why it errors like that.
Here with Mysql sometimes gives also error NOT Supported.
Probably because there aren't any pending updates?

My idea its to enclose it in TRY CATCH. with catch doing nothing.
In all my trials with MySQL and oledb ADS doesn't seem to have any bad effect.
What do you think?

About filter:
Filter2sql this has been taken from the actual app used with ADS.
All harbour used functions must be defined there.
I believe that cvaltochar its not. Please try STR.

Filter2Sql is used by the app we are using with adordd and it wasn't written by us.
Its indexkey and ordfor aware.
Also please be aware that using filters we have to ensure that besides the filter block there is a filter literal expression.
In the app they use it like this :

dbsetfilter(&("{ || " + cfilter + "}"), ;
cfilter )

Please check .ppo file if translates to that.

Please check filter2sql and if you need Ill try to help you adding new functions to the arrays.

This fully satisfy our client needs thats why we didn't changed it.

[lucasdebeltran]

Antonio,

With set filter to, I changed cvaltochar to str, and I still have the gpf.

Maybe the conversión into SQL is not good:



Also, in dbseek -STATIC FUNCTION ADOSEEKSQLFIND()-, there is an error with # and numeric fields. You call to:

SELECT CUSTOMER.* FROM CUSTOMER WHERE CODIGO=#10# ORDER BY CODIGO

But in ADO should be:

SELECT CUSTOMER.* FROM CUSTOMER WHERE CODIGO=10 ORDER BY CODIGO

Also, when appending a dbf with 30 fields, I get:

Error description: (DOS Error -2147352567) WINOLE/1006 Record is too large. (0x80040E21): Microsoft JET Database Engine
Args:
[ 1] = C

Stack Calls
===========
Called from: => TOLEAUTO:_VALUE( 0 )
Called from: source\adordd.prg => ADO_PUTVALUE( 1414 )


ADO_PUTVALUE( 1414 )
oRecordSet:Fields( nField - 1 ):Value := xValue

Thank you.

[AHF]

Lucas,

Sorry I forgot that Filter2sql definitely does not work with ACCESS.

I place filter2sql in adordd but still only works with oledb for ADS it misses other DBs syntax.
If you want please try :FILTER with SQLTRANSLATE instead
Filters will be done within coming weeks.

About seek this was left overs from trials please test instead of
cSql := IndexBuildExp(nWA,aWAData[WA_INDEXACTIVE],aWAData,.F.,IF(aSeek[3],aseek[1],aSeek[2] ))
this:
cSql := IndexBuildExp(nWA,aWAData[WA_INDEXACTIVE],aWAData,.F.,aSeek[2] )

aseek[1] it was expression to use with :find.
Ill change it here for next post.

Append

Code: Select all  Expand view  RUN

Error description: (DOS Error -2147352567) WINOLE/1006 Record is too large. (0x80040E21): Microsoft JET Database Engine

I have not any idea what this error is maybe Rao or Rick can help us.
Here with mysql there isnt any problem
We append with more than 30 fields.
The error doesnt seem to be append but edit as ADO_PUTVALUE I think its not called from ADO_APPEND.

Please check also field types (any dates?)

There was a bug in your version please replace the function might solve it although I dont think.

Code: Select all  Expand view  RUN

FUNCTION ADOSTRUCT( oRs )

   LOCAL aStruct  := {}
   LOCAL n

   FOR n := 0 TO oRs:Fields:Count()-1
      AADD( aStruct, ADO_FIELDSTRUCT( oRs, n ) )
   NEXT

RETURN aStruct  

[lucasdebeltran]

Antonio,

Append blank and error with dates are related.

Tdatarow does it in a different way, it updates at once all fields.

Try to comment out oRecordSet:Update() at ADO_PUTVALUE( nWA, nField, xValue ).

For filtering, maybe in tRecSet class there is a method.

Remember, once you work with Acess you will be able to work with the rest of rbdms, including MySQL. Opposite is not true, as mysql has own functions not supported by ADO.

Also, for future you may take into acount:

Issuing dbcreate when the table is already created does not arrise an error. Or previousy check if the table exists or not; if yes, don´t create.

An equivalent function for IF FILE "MYDBF.DBF", as now should reflect as a table into a database.

Adofuncs.prg provide such functions.

Thanks.

[AHF]

Lucas,

Can you send me by mail Tdatarow and trecset?
Do you mean then update with dbcommit or skip (ado already does this) as in clipper?

OK Ill tested it with access.

Dbcreate its being done checking file exist.

As for FILE and to keep app code compatibility the ideal was to alter the file function itself to work with adordd or any other file.
Where can I find it?

Browses are ok?
Locks are ok?
Navigation ok?

[lucasdebeltran]

Antonio,

This could be the fix for FUNCTION ADO_PUTVALUE( nWA, nField, xValue ):

Code: Select all  Expand view  RUN

            //oRecordSet:Fields( nField - 1 ):Value := xValue
            //oRecordSet:Update()

            oRecordSet:Update( nField - 1, IfNil( xValue, AdoNull() ) )
 

But ADO_APPEND( nWA, lUnLockAll ) is still failing with Record is too large. (Error 3047). Here we need Mr. Enrico, Mr. Rick or Mr. Nages advice.

Also SET SCOPE TO nVal, where nVal is a numeric fails too.

About filtering, why don´t you use ADO command Filter()?.

As those erros are still present, I can´´t work with my App.