ADO RDD xHarbour

[byte-one]

Antonio, please send to my email office(at)byte-one.com a short description from the way you process indexes with UDFs! I will try to help you to ident the problem.
Good Night!

[AHF]

Gunther,

The array of bookmarks is: 1st ele ado bookmark 2 ele evaluated expression.
The array gets ordered by 2nd ele expression and used by ado :filter := array of bookmarks which is 1st ele.

Once you call seek that goes to ado_seek and if it enters the ELSE //WITH :FILTER OR MOE THAN ONE FIELD
just scans the array looking for the seek expression in the 2nd ele and returns the first ele to have it to move to that bookmark. ors:bookmark := 1st ele. Eof recno doesnt matter.

You confirmed that the number of eles in the array was correct and npos = 0 wich means not found!
You confimed in while !eof() that the indexkey(0) its correct.

I cant see what ghost record has to do with it or go top but he fact is that work in one case not the other.

I guess the data type you are looking its string.

Did you tried that same code with dbfcdx?

Can you post you UDF and I´ll try it tomorrow here.

[byte-one]

Antonio, if i use this on beginn of your test all is OK:

Code: Select all  Expand view  RUN

TESTADISC->(dbsetorder(1))
TESTADISC->(dbgoto(TESTADISC->(lastrec()+1)))
//TESTADISC->(dbsetorder(1))
 

If i use this, then NOT:

Code: Select all  Expand view  RUN

//TESTADISC->(dbsetorder(1))
TESTADISC->(dbgoto(TESTADISC->(lastrec()+1)))
TESTADISC->(dbsetorder(1))
 

The same code in DBFNTX is functioning over years. This is the UDF:

Code: Select all  Expand view  RUN

function rev2to2(cTausch)    // tauscht 3 u. 4 mit 1 u. 2
return subs(cTausch,3,2) + subs(cTausch,1,2)
 

But on ALL UDFs this error occurs.

[AHF]

Gunther,

Ok now I get same problem.

Let me check it and Ill revert asap.

By the way why are you doing TESTADISC->(dbgoto(TESTADISC->(lastrec()+1))) before set order ?

[byte-one]

Antonio, at initial the browse should no show any records. After this, the user will search anything and i switch to the approbate order.

[AHF]

Gunther,

I see.

Replace with this in adordd.prg and let me know.
Please note the ado_create condition only in index creation because if its a subindex (while clause) we dont clean nothing.

Code: Select all  Expand view  RUN

STATIC FUNCTION ADOUDFINDEX( nWa, xExpression, xCondition, lUnique, lDesc, bEval,;
                             nEvery, xWhile, aBookMarks, cExpression )
 LOCAL oRecordSet := USRRDD_AREADATA( nWA )[ WA_RECORDSET ]
 //LOCAL aWAData := USRRDD_AREADATA( nWA )
 LOCAL lRetVal := .F.
 LOCAL aUdfs := ListUdfs(), n
 LOCAL bIndexExprn, bFilterCond, nDecimals, bWhile, nStart := 1
 LOCAL xEvaluated

   bEval  := IF( bEval == NIL, { || .T. }, bEval )
   nEvery := IF( nEvery == NIL, 0, nEvery )
   cEXpression := IF( cExpression = NIL, cExpression := xExpression, cEXpression )

   IF ! EMPTY( aUdfs )
      FOR n := 1 TO LEN( aUdfs )
          IF AT( aUdfs[ n ], UPPER(cExpression) ) > 0
             lRetval := .T.
             EXIT

          ENDIF

      NEXT

   ENDIF

   IF ! EMPTY( xCondition ) .OR. ! EMPTY( xWhile )
      lRetVal = .T.

   ENDIF

   IF lRetVal //.OR. PROCNAME( 1 ) = "ADO_ORDCREATE"
      IF PROCNAME( 1 ) <> "ADO_ORDCREATE"  //CAN BE SUBINDEX CREATION
         oRecordSet:Filter := ""
         oRecordSet:Sort := ADO_GET_FIELD_RECNO(   USRRDD_AREADATA( nWA )[ WA_TABLEINDEX ] )
      ENDIF

      aBookMarks :={}

      IF !EMPTY( xWhile )
         nStart := 0

      ENDIF
      xWhile := IF( EMPTY( xWhile ), ".T.", xWhile )
      xCondition := IF( EMPTY( xCondition ), ".T.", xCondition )
      IF VALTYPE( xExpression ) = "B"
         bIndexExprn := xExpression

      ELSE
         bIndexExprn := &("{ || " + xExpression + "}")

      ENDIF
      IF VALTYPE( xCondition ) = "B"
         bFilterCond := xCondition

      ELSE
         bFilterCond := &("{ || " + xCondition + "}")

      ENDIF

      IF VALTYPE( xWhile ) = "B"
         bWhile := xWhile

      ELSE
         bWhile := &("{ || " + xWhile + "}")

      ENDIF

      nDecimals := SET( _SET_DECIMALS, 0 )
      n := 0

      IF nStart > 0 .AND. !ADOEMPTYSET( oRecordSet )
         ADO_GOTOP( nWA )

      ENDIF

      DO WHILE EVAL (bWhile ) .AND. !oRecordSet:Eof()

         IF nEvery > 0
            n++
            IF n = nEvery
               EVAL( bEval )
               n := 0

            ENDIF

         ENDIF
         xEvaluated := EVAL( bIndexExprn )

         IF lUnique
            IF ASCAN( aBookMarks,;
                     {|x|  SUBSTR( x[ 2 ], 1, LEN( xEvaluated ) ) = xEvaluated  } ) = 0
               IF EVAL( bFilterCond )
                  AADD( aBookMarks, { oRecordSet:BookMark, xEvaluated } )

               ENDIF
            ENDIF
         ELSE
            IF EVAL( bFilterCond )
               AADD( aBookMarks, { oRecordSet:BookMark, xEvaluated } )

             ENDIF

         ENDIF

          oRecordSet:MoveNext()

      ENDDO

      SET( _SET_DECIMALS, nDecimals )

      IF lDesc
         ASORT( aBookMarks, NIL, NIL, { |x,y| x[ 2 ] > y[ 2 ] } )

      ELSE
         ASORT( aBookMarks, NIL, NIL, { |x,y| x[ 2 ] < y[ 2 ] } )

      ENDIF

      // only with set focus these bookmarks become active
      lRetVal := .T.
   ELSE
      lRetVal := .F.
   ENDIF


   RETURN lRetVal
 

[byte-one]

Antonio, it seems, all is OK!
Thanks very much!
I will further report bugs and required changes in code while convert to MSSQL.

[AHF]

Gunther,

Glad to know.

Please report as bug if you need to change any code line other than for better performance.

Thanks,

[AHF]

New build adordd ver 1.0 at https://github.com/AHFERREIRA/adordd.git

Corrected bugs:
Indexes with UDFs.
Lock list array
DBEVAL
Any :requery calls auto call ordsetfocus() to update indexes.

Changes:

1) ADO_CREATE
Corrected sequences and triggers for Oracle and Firebird

2) SET ADO ROOT PATH TO ... INSTEAD OF ...
This will allow you to upload the tables with certain path structure and then run the app with
a different path.
If the tables names are with path on without this set the app wouldn't run.

3) SET ADO TABLES LOGICAL FIELDS LIST TO
This array set is { { Tablename1, {Boolean field1, booleanfield2,...} },;
{ tablename2, .....} }
To identify Boolean fields for engines that don't have Boolean type such as Oracle, Firebird,
Sqlite and others.
In these cases there is no way to find out if a field is Boolean therefore we need this set to
let adordd know the Boolean fields present in each table.

In order to avoid extra work When uploading tables only from dbfcdx family of rdds adordd
builds itself the internal array, because through the dbfcdx rdd it knows if a field is Boolean, in order
that the uploading process doesn't errors.
At the same time adordd writes all this information in a file .ado to let you have the relation of all
tables and Boolean fields that you must place in this set next time app runs otherwise the results
are unpredictable. THE FILE "BOOELANFIELDS.ADO" IS IN APP START PATH.

When creating new tables, not temporary, you must inform adordd through this set of the Boolean fields.
With temporary tables its not needed because at the of the creation adordd keeps a record of Boolean type
fields but only during run time of the app.

If you are working with engines that have Boolean type field you might forget this set.

4) adordd tested ok with sqlite and firebird besides MsSql, MySql, Access, ADS, FoxPro, Dbase.
Should run without problems but not tested with Sybase kind of Sql Servers, Informix, PostGre.
Probably Oracle also but Ill check it soon.

[byte-one]

Antonio, please see on this! (Never changed!)
http://forums.fivetechsupport.com/viewtopic.php?f=3&t=30266&start=930#p183330

Another bug in the new version:
I use MSSQL. The array seems not exist!?

Error description: Error BASE/1123 Argument error: AADD
Args:
[ 1] = U
[ 2] = A { ... }

Stack Calls
===========
Called from: => AADD( 0 )
Called from: C:\adordd\adordd.prg => ADO_ADDLISTFIELDLOGICAL( 4931 )
Called from: C:\adordd\adordd.prg => ADOSTRUCTTOSQL( 4799 )
Called from: C:\adordd\adordd.prg => ADO_CREATE( 4608 )

[AHF]

Gunther,

The other 2 done!

Please alter :

Code: Select all  Expand view  RUN

FUNCTION ListFieldLogical( alist )
 STATIC aListFieldLogical := {}
 

[AHF]

New build adordd ver 1.0 at https://github.com/AHFERREIRA/adordd.git

Corrected bugs:

Logical fields array initialization.
scoped relations Scopes were not cleared when clear relation.
Eof and Bof vars were not being reset correctly by gotop and gobottom.

Changes:

1) New SET ADO TABLES DECIMAL FIELDS LIST TO....
Some engines like Access and SQlLite do not have specifically fields with decimal notation.
In these cases it was defaulting to 2 decimal places where could be more.
If the app would picture the gets with the fielddec function the nr of decimals places that could be entered
was only 2 although might be more.
With this set we let adordd knows the nr of decimals places per table and field.
When uploading tables that is done auto by adordd like for logical fields.
If you work with an engine that supports notation of decimals per field you might forget this set.

[byte-one]

Antonio, there is a incompatibility in the RDD:
If creating a table that already exist in the DB, an error occurs!
In (x)Harbour in this case the present table is automatically deleted and the new one are created!

And some better format for the ADOSHOWERROR()

Code: Select all  Expand view  RUN

FUNCTION ADOSHOWERROR( oCn, cTable, lSilent )
   LOCAL nErr, oErr, cErr

   DEFAULT oCn TO oConnection
   DEFAULT lSilent TO .F.
   DEFAULT cTable TO ""

   IF ( nErr := oCn:Errors:Count ) > 0
      oErr  := oCn:Errors( nErr - 1 )
      IF ! lSilent
         WITH OBJECT oErr
            cErr     := 'Table: ' + cTable
            cErr     += CRLF + CRLF + oErr:Description
            cErr     += CRLF + 'Source : ' + oErr:Source
            cErr     += CRLF + 'NativeError : ' + cValToChar( oErr:NativeError )
            cErr     += CRLF + 'Error Source : ' + oErr:Source
            cErr     += CRLF + 'Sql State : ' + oErr:SQLState
            cErr     += CRLF + REPLICATE( '-', 50 )
            cErr     += CRLF + PROCNAME( 1 ) + "( " + cValToChar( PROCLINE( 1 ) ) + " )"
            cErr     += CRLF + PROCNAME( 2 ) + "( " + cValToChar( PROCLINE( 2 ) ) + " )"
            cErr     += CRLF + PROCNAME( 3 ) + "( " + cValToChar( PROCLINE( 3 ) ) + " )"
            cErr     += CRLF + PROCNAME( 4 ) + "( " + cValToChar( PROCLINE( 4 ) ) + " )"
            cErr     += CRLF + PROCNAME( 5 ) + "( " + cValToChar( PROCLINE( 5 ) ) + " )"
            cErr     += CRLF + PROCNAME( 6 ) + "( " + cValToChar( PROCLINE( 6 ) ) + " )"
            cErr     += CRLF + PROCNAME( 7 ) + "( " + cValToChar( PROCLINE( 7 ) ) + " )"

            MSGALERT( cErr, IF( oCn:Provider = NIL, "ADO ERROR",oCn:Provider ) )

         END
      ENDIF

   ELSE
      MSGALERT( "ADO ERROR UNKNOWN"+;
                CRLF + PROCNAME( 1 )  + "( " + cValToChar( PROCLINE( 1 ) ) + " )" + ;
                CRLF + PROCNAME( 2 )  + "( " + cValToChar( PROCLINE( 2 ) ) + " )" + ;
                CRLF + PROCNAME( 3 )  + "( " + cValToChar( PROCLINE( 3 ) ) + " )" + ;
                CRLF + PROCNAME( 4 )  + "( " + cValToChar( PROCLINE( 4 ) ) + " )" + ;
                CRLF + PROCNAME( 5 )  + "( " + cValToChar( PROCLINE( 5 ) ) + " )" + ;
                CRLF + PROCNAME( 6 )  + "( " + cValToChar( PROCLINE( 6 ) ) + " )" + ;
                CRLF + PROCNAME( 7 )  + "( " + cValToChar( PROCLINE( 7 ) )  )

   ENDIF

   RETURN oErr

[AHF]

Gunther,

Ok Ill change it if table exists it will overwritten!

ADoshowerror changed to yours with following change:

Code: Select all  Expand view  RUN

         WITH OBJECT oErr
            cErr     := IF( !EMPTY( cTable ),'Table: ' + cTable +CRLF + CRLF ,"")
            cErr     += oErr:Description
 

Thanks

[AHF]

Gunther,

In ADOPREOPENTHRESHOLD() not only the size in records should work as a filter.
Also a substring (or a array with substrings) of the tablename are interesting for filtering
the DBs for pre-open! (ex. only "2015" -> DB2015 and Faw2015gt and Klima2015 and ......)

Having ADOPREOPENTHRESHOLD working like that we will not be able to tune it accordingly to our available memory.
Suppose that tables with "2015" in the name when opened all together the memory gets exhausted we dont have any way to tune it up. We must take out "2015" and pre open any.
With nrecords we simply increase the value to straighten the number of tables pre opened accordingly to our available memory.
What in fact takes time in opening during execution its tables lets say above 10.000 records there is not interest in pre open for ex tables with 500 records even if they have in the name "2015".
You dont get any improvement in speed.

May be Im seeing it wrong but it seems to me that we dont need it.
Do you agree?