ADO RDD xHarbour

[AHF]

Antonio,

use something = select * from ....
browse() // you will see all records
nreg := recno()
seek whatever = select * from .... where (seek expression)
browse() //you will only see the seek matching record(s)
go to nreg //this doesnt exist anymore

You will see what I mean.

Dont forget this only happens if seek expression corresponds to more than 1 index field!

[AHF]

Lucas,

The app Im converting does not use structural indexes so I didn't foresee it

But lets see:

The name of the index in the array is the name of the tag of the struct index file right?
This approach didn't foresee to keep the struct file name index and it might not be needed.
You simply ask for the orders (tags) right?
The struct file name itself is not needed ?

The only thing we need is to open all indexes with the table if SET AUTO OPEN is true and place the current order accordingly to SET AUTORDER. Right?

Antonio how can I query these values ?

[AHF]

Lucas,

If you don’t use relations what do you use for ex. to display fields of different "related" workareas?

Please clarify this.

[Antonio Linares]

Antonio,

seek whatever = select * from .... where (seek expression)

Why don't you create a new RecordSet for this seek ?

This way, the original RecordSet remains intact.

[lucasdebeltran]

Antonio,

The name of the index in the array is the name of the tag of the struct index file right?

Yes.

This approach didn't foresee to keep the struct file name index and it might not be needed.
You simply ask for the orders (tags) right?

Yes.

The struct file name itself is not needed ?

Yes.

The only thing we need is to open all indexes with the table if SET AUTO OPEN is true and place the current order accordingly to SET AUTORDER. Right?

Yes.


Let´s see it better with a sample:

So, if SET AUTOPEN is ON, after a USE .... command the indexes should be opened, i. e. calling ADORDD SET INDEX TO TAG1, TAG2, TAG3, TAG4, TAG5... going over the items from ListIndex(nOption) for such table.

For instance, if we define at ListIndex those indexes for table CUSTOMER:

Code: Select all  Expand view  RUN

LOCAL a :=  { { "CUSTOMER", {"CUSTOMER1",  "NAME"} ,;
                                           {"CUSTOMER2",  "NAME", "WHERE CITY = 'Barcelona' "} ,;
                                           {"LUCAS",          "STREET", "WHERE NOTES = 'varis' "} ,;
                         }  }
 

So, if I do:

Code: Select all  Expand view  RUN

SET AUTOPEN ON

USE access.mdb VIA "ADORDD" TABLE "CUSTOMER"  ACCESS  NEW  ALIAS "CUSTOMER"
 

ADORDD should call

SET INDEX TO CUSTOMER1, CUSTOMER2, LUCAS

by itself.

Thank you.

[AHF]

Lucas,

Its done also taking in account autoorder setting.

Ill post a new version asap

Thanks for the info.

[lucasdebeltran]

Thank you Antonio, there is no rush.

I would like to ask you how I define such indexes with functions and conditions. For instance:

Code: Select all  Expand view  RUN

INDEX ON FIELD->FSERIE + StrZero (   FIELD->FNUMERO, 20   ) TAG CAB

INDEX ON FIELD->FNUMERO               TAG ALB   FOR FIELD->FP != "S"

INDEX ON FIELD->TIPDOC    TAG AUXILIAR3  FOR  !EMPTY(   FIELD->TIPDOC   )

INDEX ON DTOS( FIELD->FECHA )         TAG CARTERA3    FOR FIELD->COBRO = 0

Thank you.

[AHF]

Lucas,

Thank you Antonio, there is no rush.

I would like to ask you how I define such indexes with functions and conditions. For instance:

Code: Select all  Expand view  RUN

INDEX ON FIELD->FSERIE + StrZero (   FIELD->FNUMERO, 20   ) TAG CAB

INDEX ON FIELD->FNUMERO               TAG ALB   FOR FIELD->FP != "S"

INDEX ON FIELD->TIPDOC    TAG AUXILIAR3  FOR  !EMPTY(   FIELD->TIPDOC   )

INDEX ON DTOS( FIELD->FECHA )         TAG CARTERA3    FOR FIELD->COBRO = 0

Thank you.

Code: Select all  Expand view  RUN

{....
{"CAB",{"FSERIE,NUMERO"}} //I dont know strzero
{"ALB",{"FNUMERO"},{"WHERE FP != 'S' "}}  //!= depends on your DB it might only accepts <>
{"AUXILIAR3",{"TIPODOC"},{"WHERE TIPODOC != NULL"}}
{"CARTEIRA3",{"FECHA"},{"WHERE COBRO = 0 "}}  }
 

You can use on the condition ele of the array any sql statement permitted by your server.
Conversion functions in the index field ele are not needed in SQL.
Please remember that Indexes are truly only selects, Seeks and Locates with more than one field on the expression also.

The problem I have to start solve on Monday is Seeks and child related tables with the expression corresponding to multiple fields for a way to reset the previous recordset as soon as the result from these are not needed anymore.
Please remember that in these conditions you keep that tables with those selects.

Please try it yourself.

Open table
Open index
browse()
nRec := recno()
seek x (expr = 2 or more fields)
browse()
go to nRec

See what I mean?

[lucasdebeltran]

Thank you Antonio.

Have you looked at arrayrdd source, SQLRDD rdd source or Mediator SQL RDD from www.otc.pl?.

It may help.

Also, if you use find for seek?. I think in ADO indexes are fine for showing the data ordered by a field, but they are not so important for searching and speed as they are in DBF.

[lucasdebeltran]

Antonio,

DbCreate() working:

Code: Select all  Expand view  RUN

STATIC FUNCTION ADO_CREATE( nWA, aOpenInfo )


   TRY
      IF Lower( Right( cDataBase, 4 ) ) == ".fdb"
         oConnection:Execute( "CREATE TABLE " + cTableName + " (" + StrTran( StrTran( aWAData[ WA_SQLSTRUCT ], "[", '"' ), "]", '"' ) + ")" )
      ELSE

         //oConnection:Execute( "CREATE TABLE [" + cTableName + "] (" + aWAData[ WA_SQLSTRUCT ] + ")" )

         N := FW_AdoCreateTableSQL( cTableName, aWAData[ WA_SQLSTRUCT ], oConnection, .T. )
         oConnection:Execute( N )

      ENDIF
   CATCH
      oError := ErrorNew()
      oError:GenCode := EG_CREATE
      oError:SubCode := 1004
      oError:Description := hb_langErrMsg( EG_CREATE ) + " (" + ;
         hb_langErrMsg( EG_UNSUPPORTED ) + ")"
      oError:FileName := aOpenInfo[ UR_OI_NAME ]
      oError:CanDefault := .T.

      FOR n := 0 TO oConnection:Errors:Count - 1
         oError:Description += oConnection:Errors( n ):Description
      NEXT

      UR_SUPER_ERROR( nWA, oError )
   END

   oConnection:Close()

   RETURN HB_SUCCESS



STATIC FUNCTION ADO_CREATEFIELDS( nWA, aStruct )

   LOCAL aWAData := USRRDD_AREADATA( nWA )
   LOCAL n

aWAData[ WA_SQLSTRUCT ] := aStruct


/*
   aWAData[ WA_SQLSTRUCT ] := ""

   FOR n := 1 TO Len( aStruct )
      IF n > 1
         aWAData[ WA_SQLSTRUCT ] += ", "
      ENDIF
      aWAData[ WA_SQLSTRUCT ] += "[" + aStruct[ n ][ DBS_NAME ] + "]"
      DO CASE
      CASE aStruct[ n ][ DBS_TYPE ] $ "C,Character"
         aWAData[ WA_SQLSTRUCT ] += " CHAR(" + str( aStruct[ n ][ DBS_LEN ] ) + ") NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "V"
         aWAData[ WA_SQLSTRUCT ] += " VARCHAR(" + str( aStruct[ n ][ DBS_LEN ] ) + ") NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "B"
         aWAData[ WA_SQLSTRUCT ] += " DOUBLE NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "Y"
         aWAData[ WA_SQLSTRUCT ] += " SMALLINT NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "I"
         aWAData[ WA_SQLSTRUCT ] += " MEDIUMINT NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "D"
         aWAData[ WA_SQLSTRUCT ] += " DATE NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "T"
         aWAData[ WA_SQLSTRUCT ] += " DATETIME NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "@"
         aWAData[ WA_SQLSTRUCT ] += " TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP"

      CASE aStruct[ n ][ DBS_TYPE ] == "M"
         aWAData[ WA_SQLSTRUCT ] += " TEXT NULL"

      CASE aStruct[ n ][ DBS_TYPE ] == "N"
         aWAData[ WA_SQLSTRUCT ] += " NUMERIC(" + str( aStruct[ n ][ DBS_LEN ] ) + ")"

      CASE aStruct[ n ][ DBS_TYPE ] == "L"
         aWAData[ WA_SQLSTRUCT ] += " LOGICAL"
      ENDCASE
   NEXT


*/


   RETURN HB_SUCCESS


 

[lucasdebeltran]

Hello Antonio,

I detected that the changes are not saved to the disk.

Have you checked this?.

Thank you.

[lucasdebeltran]

Sample to test it:

Code: Select all  Expand view  RUN

function Main()

   local aArray := {}

/*
   DbCreate( "test2.mdb;table1", { { "FIRST",   "C", 30, 0 },;
                                   { "LAST",    "C", 30, 0 },;
                                   { "AGE",     "N",  8, 0 } }, "ADORDD" )

*/
   USE test2.mdb VIA "ADORDD" TABLE "table1" NEW

browse()


   APPEND BLANK
   test2->First   := "HOMER si no Homer"
   test2->Last    := "Simpson"
   test2->Age     := 45

   APPEND BLANK
   test2->First   := "aaa Lara"
   test2->Last    := "Croft si no"
   test2->Age     := 32


   GO TOP

   xBrowse()
   DbCloseAll()




return nil

 

[AHF]

Lucas, Antonio,

Have you looked at arrayrdd source, SQLRDD rdd source or Mediator SQL RDD from www.otc.pl?.

It may help.

Also, if you use find for seek?. I think in ADO indexes are fine for showing the data ordered by a field, but they are not so important for searching and speed as they are in DBF.

I've looked arrayrdd but not the others I don't have the code.

ADO_CREATE not started yet.

I'm still struggling with seeks with more than one field in seek expr..

We have the following alternatives:

1) Select a new set with where clause with fields used in seek expr.
Pros - Good performance
Cons - Seek must be reset some how if one needs to access records out of the scope
of the select. Previous Set could be saved.
Actual solution in adorddd being tested.

2) ADO Find based on the initial select with a new column = to the fields used in the index expr.
ex : index expr - field1+field2+field3
Initial select is then:
SELECT ctable.*, (field1+field2+field3) AS INDEKEY FROM ctable ORDER BY field1+field2+field3
This allow us to use ADO Find on the INDEXKEY column and we can even create a ADO index on that
column Ex indexkey:Optimize := TRUE
Pros - Don't need any code change in the app.
Cons - Don't know how performance is
I'm implementing this to initiate trials.

3) A mixture of both above solutions:
Instead of use seek in relations create a SELECT with both related areas with the join clause = to the related
fields and the same new indexkey new column
Ex:
SELECT ctable.*, cTable2.*, ctable.(field1+field2+field3) AS INDEKEY FROM ctable,ctable2 LEFT JOIN ....
ORDER BY field1+field2+field3
Pros - Don't need any code change in the app. Only one select for the job. Best performance.
Cons - We need somehow when changing area to ctable2 or address fields in ctable2 to redirected to ctable
area. Dont know if it is possible.

I'm checking now the solution 2 but I would like to know what is your opinion.
Do you have any experience using :Find on a Optimize field on a huge table (couple of 100.000) ? Is it fast?

[AHF]

Antonio,

Ive a problem with bookmarks.

The value returned by bookmark its a Variant.Ex.

nrecno := oSet:bookmark
......
dbgoto(nrecno) = adordd - oSet:Bookmark := nrecno

nRecni it is received in ADO_GOTOID as integer with no decimals and might be any value.

How can we solve this?

By the way in adordd in all recno function (RECNO, GOTO etc) tests if there is a field HBRECNO in the table (autoinc)
If true it uses the value on that field for all recno operations.
This is a optional that assures that will work 100% in all situations under any kind of cursor.

[lucasdebeltran]

Antonio,

DbSeek only supports searching into 1 field.

DbSeek()
Searches a value in the controlling index.
Syntax
DbSeek( <xValue>, [<lSoftSeek>], [<lFindLast>] ) --> lFound

Arguments
<xValue>
The value to search for. Its data type must match the data type of the index expression of the controlling index.
<lSoftSeek>
This optional value defaults to .F. (false) causing the DbSeek() function to position the record pointer at Eof() if <xValue> is not found in the index. When .T. (true) is passed for <lSoftSeek> and <xValue> is not found in the index, the record pointer is positioned on the record with the next higher index value.
<lFindLast>
<lFindLast> is only relevant when the database contains multiple records having identical index values. It defaults to .F. (false) causing the DbSeek() function to position the record pointer on the first record found. .T. (true) instructs DbSeek() to position the record pointer on the last of multiple records having the same index value. Return
DbSeek() returns .T. (true) if <xValue> is found, otherwise .F. (false).
Description
The DbSeek() function is used to perform fast searches in databases. To accomplish this, the database must be indexed, since DbSeek() searches the value <xValue> in the controlling index, rather than in the database. It operates in the current work area, unless it is used in an aliased expression.
When DbSeek() finds <xValue> in the controlling index, it returns .T. (true) and positions the record pointer to the corresponding record. The parameter <lFindLast> optionally specifies which record to find if there are multiple records having the same index value. By default, the first record is found. If <lFindLast> is .T. (true), DbSeek() positions the record pointer on the last of the records having identical index values.
After a successful search, the function Found() returns .T. (true) until the record pointer is moved again. In addition, both functions, BoF() and EoF() return .F. (false).
If the searched value is not found, DbSeek() positions the record pointer on the "ghost record" (Lastrec()+

But the real problem is that data is not saved!!!. When the program is closed, they are lost!.

Thank you very much.