Change detection in MySQL.

amit_27

Hi,

I am trying to build a application in C++ 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?

bakuryu

Use a timer and query the table at interval of say 60 sec.

__________________
Please don't click here

amit_27

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.

bakuryu

If the table has any timestamp fields set to change itself on insert or update then you need to add them to your sql query with a where clause.

amit_27

It is good if the table has timestamp field.

In such a cases where we don't have filed like this, and need to find out the all different changes (add, delete, modify, rename) done on number of tables.

I searched and tried to find out the same but not yet got the sufficient information.

Do we have any MySQL API which accepts some parameter and internally execute the command to return the changes?

bakuryu

You can use Triggers.
MySQL :: MySQL 5.0 Reference Manual :: 18.3 Using Triggers

amit_27

Thanks.

Currently I am on the same track. I was just looking into triggers for changes in MySQL database.

amit_27

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?

bakuryu

Version 6 is still in Alpha stage and might go a lot of changes before it's finally released. You can use any version of Mysql 5, 5.1.34 or 5.2.3.0

amit_27

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 am trying to use the DECLARE in my trigger definition. I have just created trigger as:
CREATE TRIGGER t1 BEFORE INSERT ON account FOR EACH ROW
BEGIN
DECLARE done INT DEFAULT 0;
END
END;

But it is giving me error. i am using MySQL 5.1.34 version.
I have seen for a DECLARE statement in reference but it is given the same. I wonder why this is not working?

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