MYSQL

[Roberto Lopez]

Hi All,

Had someone found a way to determine which are the altered records in the local recordset when using SQLMIX.

Finding that, we should be able to easily automate the updates to the SQL backend.

TIA.

[apais]

From C:\harbour\contrib\rddsql\readme.txt

3. Modifying database

SSI presents a query result via RDD interface and generates no hidden
SQL queries. So, how database can be changed? Does DBAPPEND() and FIELDPUT()
works, or is it readonly SQL interface?
DBAPPEND(), FIELDPUT() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. DBCREATE() function can also be
used to create an "empty query result" but no table is created on SQL server.
So, SSI can also be used as implementation of "array RDD".
The programmer must call SQL command explicitly to modify SQL tables.
SSI provides a method to detect which cached rows was changed or appended.

HTH
Angel

PD: Wellcome back my friend !!!

[Roberto Lopez]

From C:\harbour\contrib\rddsql\readme.txt

3. Modifying database

SSI presents a query result via RDD interface and generates no hidden
SQL queries. So, how database can be changed? Does DBAPPEND() and FIELDPUT()
works, or is it readonly SQL interface?
DBAPPEND(), FIELDPUT() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. DBCREATE() function can also be
used to create an "empty query result" but no table is created on SQL server.
So, SSI can also be used as implementation of "array RDD".
The programmer must call SQL command explicitly to modify SQL tables.
SSI provides a method to detect which cached rows was changed or appended.

HTH
Angel

PD: Wellcome back my friend !!!

Thanks Angel!

I've already read that some time ago.

My question was oriented to create a function (something like SqlCommit()) that pick the list of modified records in the local recordset and then update those via SQL commands sent to the back-end. Being this process (except for the calling to SqlCommit()) transparent to the programmer.

I've done some asumptions, ie, that the recorset contains a key field that allows to identify the record as unique.

Anyway... talking about that, I've got an idea that could work for small recorsets...

If you backup the recordset immediately after retrieve it, you can later compare it with the modified one and then, identify the modified records to be automatically updated in the backend... THANKS!

[Rathinagiri]

Nice going Roberto.

In SQLite there is an internal unique rowid for each row in every table. I don't know whether that is maintained by MySQL. As you have said, if the record set has an unique id/key, it would be easy to update the database easily.

[Roberto Lopez]

Nice going Roberto.

In SQLite there is an internal unique rowid for each row in every table. I don't know whether that is maintained by MySQL. As you have said, if the record set has an unique id/key, it would be easy to update the database easily.

AFAIK there is not an equivalent in to that in MySql (I'm not an expert).

Regarding my idea, it could work as follows:

SET SQLKEY TO (new HMG command).

Creates a memory variable that holds the unique key field (name or position) used later to build SQL WHERE clause conditions.

BEGIN TRANSACTION (new HMG command).

It creates a copy of the current recordset and lock the remote table.

END TRANSACTION (another new HMG command).

It compares backed-up and current (xBase modified) recordsets to identify altered rows and update them in the back-end. Finally unlock the table.

This is intented to work with SQLMIX but it could be expanded to any other backend/driver.

[Roberto Lopez]

From C:\harbour\contrib\rddsql\readme.txt <...>

Anyway, there is an intriguing line in the SQLMIX readme file:

"SSI provides a method to detect which cached rows was changed or appended."

I've reviewed the code and I've not found such thing.

Maybe the method is there, but, since it is low level code, it could be difficult to find.

[apais]

I think this structure is crucial to find where and how...

Code: Select all

typedef struct _SQLBASEAREA
{
   AREA area;

   /*
    *  SQLBASE additions to the workarea structure
    */

   LPDBRELINFO                lpdbPendingRel;

   HB_ULONG                   ulConnection;
   struct _SQLDDCONNECTION *  pConnection;
   struct _SDDNODE *          pSDD;

   char *                     szQuery;       /* SQL query */

   HB_ULONG                   ulRecNo;       /* Current record number */
   HB_ULONG                   ulRecCount;    /* Total records */
   HB_ULONG                   ulRecMax;      /* Size of pRow, pRowFlags buffer */

   void **                    pRow;          /* array of native pointers or cached PHB_ITEM */
   HB_BYTE *                  pRowFlags;

   void *                     pRecord;       /* current record */
   HB_BYTE                    bRecordFlags;

   HB_BOOL                    fFetched;
   HB_BOOL                    fPositioned;
   HB_BOOL                    fAppend;
   HB_BOOL                    fRecordChanged;

   void *                     pSDDData;      /* SDD specific data */
} SQLBASEAREA, * SQLBASEAREAP;

[ClaudioGalera]

Hola !

Si entendi bien, lo que se necesita es obtener un unico identificador del registro para luego poder llamarlo, asi modificarlo o borrarlo, no es asi ?

En Sqlite, creo que la solucion seria incorporar el clasico campo "ID" donde será un campo numerico entero con autoincremento.
En Firebird,no existe el autoincremento, hay que implementarlo como un Trigger; hoy he visto que existe RDB$DB_KEY para incorporar como campo dentro de un SELECT.

Mi pregunta es la siguiente : Podria utilizar RDB$DB_KEY en vez del "ID" ?

Muchas gracias
Claudio Galera

Google Translate :

Hello !

If I understood, what is needed is to obtain a single record identifier and then to call him, so edit or delete it, is not it?

In Sqlite, I think the solution would be to incorporate classic "ID" field where a field is a numeric INT with autoincrement.
In Firebird, there is no autoincrement, we must implement it as a trigger, but today I have seen that there RDB $ DB_KEY to incorporate as a field within a SELECT.

My question is: Could use RDB $ DB_KEY instead of "ID"?

Thank you very much
Claudio Galera

[ClaudioGalera]

In DBF the row id is RECNO()

ex:
? table->field1,table->field2,recno()

In Firebird the row id is RDB$DB_KEY

ex:
select a.field1,a.field2,a.RDB$DB_KEY

In Sqlite the row id is... rowid

select a.field1,a.field2,a.rowid

In MySql, I was read that we need a field as a numeric integer type with autoincrement

http://www.eggheadcafe.com/community/as ... ql-50.aspx

There is no rowid in MySQL. If you need a 'rowid' that you can reference from the table, you should be using the quite common 'auto_increment' type.


For example:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);


INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');


SELECT * FROM animals;


Which returns:


+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+



If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.
ex.,
SELECT * FROM TABLE1 WHERE _rowid=1


just refer to http://dev.mysql.com/doc/refman/5.0/en/index.html for more info!