Crappy loop inside mysql request… Play with mysql procedure.

2 octobre 2008 at 18 h 02 min Laisser un commentaire

If you miss something during the conception of your mysql database, or wish to add some new tables for additional features in your web application, it could be a mess to build the new version on your database without droping all data of the existing tables.

I personnaly needed to keep thousand of registered users (in a table) and nevertheless i had to provide some messaging feature in another table.

I decided to:

1. Create the new table in my existing database:

CREATE TABLE `newone` (
`id` INT NOT NULL ,
`field1` TINYINT NOT NULL ,
`field2` BLOB NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

2. Set my default values for each field:

ALTER TABLE `newone` CHANGE `field1` `field1` TINYINT( 4 ) NOT NULL DEFAULT ‘999999’

3. Insert as many lines as registered user, so that every registered user have their own line in the « newone » table:
i used the LOOP mysql statement to achieve this:

delimiter !!

CREATE PROCEDURE massiveInsert(IN maxInserts INT)
BEGIN
DECLARE i INT;
SET i=1;
myloop: LOOP
SET i=i+1;
IF i=maxInserts THEN
LEAVE myloop;
END IF;
insert into newone values();
END LOOP myloop;
END!!

Doesn’t it looks like 80’s GW*-Basic, ehhh ?

To run this procedure, just type:
call massiveInsert();

What do you need to remind concerning MySQL procedures:

– Define you OWN delimiter, unsing the delimiter. Otherwise, you will get the Error Code : 1064

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 blahblahblah

– Use the keyword IN to input parameters, and next the variable name + parameter type.

– Use the call() instruction to execute your procedure.

References:

mysql manual

Entry filed under: english publications, mysql. Tags: , , .

HOWTO: configure Tomcat to send mails, using javamail La nouvelle nintendo DSi: moins chère, sans port GBA, contenu téléchargeables ?

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Articles récents


%d blogueurs aiment cette page :