MySQL Forums
Forum List  »  Stored Procedures

Re: Procedure not working
Posted by: tyuyu yuty
Date: December 26, 2019 05:05PM

You were right. I have worked a little bit more the code. I want to create the tables and fill one of them through dbDelta function.

When the function is called, two tables are created but no record is inserted.
No error is raised.

When I copy and paste this code to phpmysql / sql editor and I execute it , it works perfect.

This is the code of wp_codis_postals.sql
I have problems only with this one file.

Could be the problem that sql string becomes too large in dbDelta function?

I can't post all the insert query because it contains more than 1500 commands. Post is too large and becomes impossible to post it.
I have cut the query to few records.

Thank you very much.

CREATE TABLE IF NOT EXISTS `wp_codis_postals` (
`codi_postal` int(5) NOT NULL DEFAULT 0,
`poblacio` varchar(75) CHARACTER SET utf8 COLLATE utf8_spanish2_ci NOT NULL DEFAULT '',
PRIMARY KEY (`codi_postal`,`poblacio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS usp_test_transaction;

DELIMITER @@

CREATE PROCEDURE usp_test_transaction()

BEGIN

DECLARE total INT DEFAULT 0;

SELECT COUNT(*)
INTO total
FROM wp_codis_postals;

IF (total<=0) THEN

INSERT INTO `wp_codis_postals` (`codi_postal`, `poblacio`) VALUES
(8001, 'Barcelona'),
(8002, 'Barcelona'),
(8003, 'Barcelona'),
(8004, 'Barcelona'),
(8005, 'Barcelona'),
(8006, 'Barcelona'),
(8007, 'Barcelona'),
(8008, 'Barcelona'),
(8009, 'Barcelona'),
(8010, 'Barcelona'),
(8011, 'Barcelona'),
(8012, 'Barcelona'),
(8013, 'Barcelona'),
(8014, 'Barcelona'),
(8015, 'Barcelona'),
(8016, 'Barcelona'),
(8017, 'Barcelona'),
(8018, 'Barcelona'),
(8019, 'Barcelona'),
(8020, 'Barcelona'),
(8021, 'Barcelona'),
(8022, 'Barcelona'),
(8023, 'Barcelona'),
(8024, 'Barcelona'),
(8025, 'Barcelona'),
(8026, 'Barcelona'),
(8027, 'Barcelona'),
(8028, 'Barcelona'),
(8029, 'Barcelona'),
(8030, 'Barcelona'),
(8031, 'Barcelona'),
(8032, 'Barcelona'),
(8033, 'Barcelona'),
(8034, 'Barcelona'),
(8035, 'Barcelona'),
(8036, 'Barcelona'),
(8037, 'Barcelona'),
(8038, 'Barcelona'),
(8039, 'Barcelona'),
(8040, 'Barcelona'),
(8041, 'Barcelona'),
(8042, 'Barcelona'),
(8100, 'Mollet del Vallès'),
(8104, 'Mollet del Vallès'),
(8105, 'Sant Fost de Campsentelles'),
(8106, 'Santa Maria de Martorelles'),
(8107, 'Martorelles'),
(8110, 'Montcada i Reixac'),
(8120, 'Llagosta, la'),
(8130, 'Santa Perpètua de Mogoda'),
(8140, 'Caldes de Montbui'),
(8146, 'Gallifa'),
(8148, 'Estany, l\''),
(8150, 'Parets del Vallès'),
(8160, 'Montmeló'),
(8170, 'Montornès del Vallès'),
(8178, 'Collsuspina'),
(43897, 'Tortosa');

END IF;

END @@

call usp_test_transaction;

Options: ReplyQuote


Subject
Views
Written By
Posted
714
December 25, 2019 06:44PM
251
December 25, 2019 07:02PM
Re: Procedure not working
217
December 26, 2019 05:05PM
216
December 26, 2019 06:53PM
213
December 27, 2019 01:59AM
208
December 27, 2019 10:13AM
199
December 29, 2019 04:20AM
213
December 29, 2019 12:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.