Hi,

I am trying to build a application in which I can be able to flow data to MySQL and vice -versa.

I need to understand that how the update, any changes done in MySQL database will be known or immediately flown to other side?

What is the Change detection mechanism in MySQL?

What I am trying to understood is :
If I add some entry in Database it has immediately flown to other side.

Can you please shed some light on this?

In more details:

Is there any API, command for MySQL which will return the changes between given time period
Ex. suppose from 10.11 am to 14.00 pm

Or something like If given any range
From last change number 100 to onwords all changes.

Any takers Plz.

It is very unclear what you want but I'll make a stab in dark:

If each of your tables in MySQL has a field to hold when the record was last updated then you could simply query this field to get the data you want. Of course you'd have to set this field every time you do an insert or an update. Here's a simple example of updating a record and then finding out what the last 100 records to be updated:

Code:

update MyTable
set   dataFields = 'Some new Data',
      lastupdated = now()
where keyField = 123

select * from MyTable 
order by lastUpdated desc 
limit 100

Deletes might be an issue as the record is deleted and you won't be able to see that this record has changed ie it's been deleted. If this is an issue to you then you'll need to add a flag to each table to indicate whether the record has been "deleted" - then rather than deleting records you could just set this flag.

You could use triggers on all the tables to automatically set the update time and delete flag. If you don't want to change any existing tables then you could just create a new log table that records the table and id of each record that is changed (ie inserted, updated or deleted) but this would also probably need to be done via triggers.

Mike

Hi Mike,

Thanks for the valuable information.

I was just going through the triggers use for my work. And I think it is the best option to use trigger while update on tables, So we can keep the all information.

mysql supports the use of a timestamp field, which will automatically be set when the row is first added or on subsequent changes to the row.

however all that monitors is that something changed. you could optionally note the user id of the person and on which computer but that would require some coding in your user interface.

so if a row was changed more than once all you know is the time of the last change, you don't know what was changed. to know what was changed you would need to consider implementing some form of audit log, that would allow you to track changes over time.

you can handle the delete issue by flagging a record for delete, carry the delete flag for a period of time and then finally purge the record after a set period of time. naturally you wouldn't normally then include rows flagged for delete in your normal operations

Correct me if wrong - If we consider timestamp field, User must have to create tables with timestamp field? Isn't it?

Or timestamp filed is default in MySQL?

no timestamp is an inherent MySQL datatype, you do not refernece the column in update or insert queries the time is taken form the server time.

setting a column to datatype timestamp makes that column record the date/time of the last change you do not need to perform any action to record the timestamp (doing so may result in an error, can't remember offhand).

if you want to record the userid and or computerid that requires the user interface to find thise details and add them to the query.

ok, timestamp is inherent MySQLdatatype.
What I mean to ask is while creating any table let say :

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL, id INT NOT NULL);

INSERT INTO t1 VALUES (NOW(), '1');
Or
INSERT INTO t1 VALUES ( '1');

Which is correct insert statement and how we get timestamp of inserted row?

if you are using a timestamp datatype in a table you do not reference that column in any insert or update statements, the time is completed by the MySQL server using the time taken from that server

you can retrieve the values in a select statement

der manual is alwasy a good place to start with such questions

Can you please tell me that If I want to use the latest version of MySQL with trigger facility then which will be better 5.0 or 6.0 or in between?

new question new thread please

Hi,

I have seen the IDENTITY property in MSSQL used like:
CREATE TABLE test(idencol INT IDENTITY(1,1), dupcol INT NOT NULL);


I need to create a same query in MySQL. I saw that there is AUTO_INCREMENT in MySQL. But not sure it is the one which I can use instead if identity.

Can you please confirm me or suggest me if I am wrong.
What can I used instead of IDENTITY?
Amit

I guess you're going to have to be less subtle

Error is :
-> BEGIN
-> DECLARE done INT DEFAULT 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 3