How to Avoid Data Corruption

[Rathinagiri]

Viva Harbour, Viva HMG.

We have to make the variables (like i,j etc) inside the functions to be local. I had forgotten that.

[sudip]

Hi Rathi,

Yes. And you can get the automatically incremented number by "select last_insert_id()" query for further processing.

So, can I store "select last_insert_id()" into temporary code and insert that temporary code into table?

Suppose, for multi-user environment, shall the MySql server calculate last_insert_id() for each user? Then if one user will not save the record (in case of insert), then what will happen to last_insert_id()?

May be it is too early for me to ask those questions, but I am so much excited, that I want to ask those typical problems about multi-user environment, which I have to do programatically with non-RDBMS databases!

With best regards.

Sudip

[swapan]

Great Rathi!
So Rathi has started "MySQL for Dummies"!!!

Sudip thanks for highlighting this issue, and good luck for exploring this new avenue.

BTW, my 2 cents:
Rathi create a fresh thread on MySQL and add these codes there. This can help many of us to give a try with a RDBMS with our applications.

And let this thread restrict itself on discussing how to avoid data corruption of dbfs. I've usually seen it happens in network environment during power failures. Instances like server gets down due to power failure (UPS/generator fails) but the clients at that time are ON and keying in the Entry modules - in such situations there is high chance of data corruption.

With regards,

Swapan

[swapan]

Hi Rathi,
May be it is too early for me to ask those questions, but I am so much excited, that I want to ask those typical problems about multi-user environment, which I have to do programatically with non-RDBMS databases!

With best regards.

Sudip

Watchout Rathi, it seems Sudip is so much excited that he has finished "bignners" level and soon will get into "advanced" questions...... yes I too want to know how you handle the concurrency issue?

[sudip]

Rathinagiri wrote:
We have to make the variables (like i,j etc) inside the functions to be local. I had forgotten that.

No problem

Thanks a lot!!!!!

(My clients already started calling me regarding fiscal year closing. And funny thing is that they always forget one thing - "BACKUP" )

Sudip

[Rathinagiri]

Regarding concurrent issue:

Every connection to the MySQL server is handled in a separate thread. The connection is exclusive and last_insert_id() would give different auto increment numbers to different persons in the same network according to their increment id.

So, in a Point on Sale terminal, if two persons push the save invoice button simultaneously, the server would give invoice number (if it is auto incremented) first come first serve basis and till now FYI, I had not got any problem in this.

If you want to be sure, it is safe if we have Begin Transaction ... End Transaction (if our transaction has multiple queries).

So, can I store "select last_insert_id()" into temporary code and insert that temporary code into table?

No. It is the id which can be received only after the insertion. If you want to know that id for any further processing, you can get. That's all.

MySQL had virtually removed the multi-user and concurrency problems in my programs. And also, indexing.

[sudip]

Hi Rathi,

Thanks a lot! It is (concurrent issue) is now clear to me.

Every connection to the MySQL server is handled in a separate thread. The connection is exclusive and last_insert_id() would give different auto increment numbers to different persons in the same network according to their increment id.

So, in a Point on Sale terminal, if two persons push the save invoice button simultaneously, the server would give invoice number (if it is auto incremented) first come first serve basis and till now FYI, I had not got any problem in this.

If you want to be sure, it is safe if we have Begin Transaction ... End Transaction (if our transaction has multiple queries).
....
....
MySQL had virtually removed the multi-user and concurrency problems in my programs. And also, indexing.

Great!!!

Regarding Begin Transaction ... End Transaction... can we do it with HMG? If yes, then nothing like it

BTW, I am archiving your messages regarding MySql in my computer

With best regards.

Sudip

[Rathinagiri]

Transaction processing is nothing to do with HMG. It is the work of MySQL.

We can send each statement as a SQL query like select all in one line, separated by ;.

This is the syntax for begin... end transaction. From MySQL manual:

Code: Select all

START TRANSACTION, COMMIT, and ROLLBACK Syntax

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

example

Code: Select all

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

In HMG we can send this like this.

Code: Select all

qsuccess := miscsql(dbo,"START TRANSACTION;SELECT @A:=SUM(salary) FROM table1 WHERE type=1;UPDATE table2 SET summary=@A WHERE type=1;COMMIT;")

With this, we can simultaneously save a transaction affecting two or more tables.

[sudip]

Hi Rathi,

I don't know how do you understand exactly what I want?

Thanks a lot!

With best regards.

Sudip

[Rathinagiri]

I don't know how do you understand exactly what I want?

Because, I had also struggled like you.