0

I'm wrote this mysql procedure for inserting customer billing details into an ecommerce database.

If i run this in phpmyadmin, it throws errors for any semicolons. I removed the semicolons but it gives the following error

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 'DECLARE addr_id INT UNSIGNED ... at line 4

I've never worked with procedures before but i double checked the syntax and can't figure out the error.

DELIMITER //

CREATE PROCEDURE addCustomer(email VARCHAR(60), status VARCHAR(15), bill_pin_id SMALLINT UNSIGNED, bill_addr VARCHAR(175), name VARCHAR(60), tel VARCHAR(15))
BEGIN
DECLARE em_id INT UNSIGNED;
DECLARE addr_id INT UNSIGNED;
DECLARE cust_id INT UNSIGNED;
DECLARE sql_error TINYINT DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = true;


--check if customer email already exists
SELECT id INTO em_id FROM email_list WHERE email = email;

-- not sure of the return value, haven't tested it
IF em_id = 1 THEN

  -- if emails exists check if customer exists
  SELECT id INTO cust_id FROM customer WHERE email_list_id = em_id;
  IF cust_id = 1 THEN

    -- customer must have address information
    SELECT address_id INTO addr_id from customer_addr
        INNER JOIN address on address_id = address.id
        WHERE customer_id = cust_id and address = bill_addr;
  END IF
END IF

START TRANSACTION;

IF (em_id = 0) THEN

  -- if email doesn't exist, no customer no address - simple insert
  INSERT INTO email_list (email, status) VALUES (email, status);
  SELECT LAST_INSERT_ID() INTO em_id;

  INSERT INTO customer (email_list_id, full_name, phone)
      VALUES (em_id, name, tel);
  SELECT LAST_INSERT_ID() INTO cust_id;

  INSERT INTO address (pincode_id, address) VALUES (bill_pin_id, bill_addr);
  SELECT LAST_INSERT_ID() INTO addr_id;

  INSERT INTO customer_addr (address_id, customer_id)
      VALUES (addr_id, cust_id);

ELSE
  UPDATE email_list SET status = status where id = em_id;
  UPDATE customer SET full_name = name, phone = tel WHERE id = cust_id;
  UPDATE address SET pincode_id = bill_pin_id, address = bill_addr;
END IF

IF sql_error = FALSE THEN
  COMMIT;
  SELECT 'SUCCESS';
ELSE
  ROLLBACK;
  SELECT 'FAILED';
END IF

END //
DELIMITER ;
1
  • phpmyadmin does not understand the DELIMITER directive, but it should have a way to select the statement delimiter, possibly a small drop-down or text box. The error suggests that your delimiter is not set. Commented Mar 9, 2018 at 22:36

2 Answers 2

2

Try this solution, I added some missing semicolons and spaces in some comments.

CREATE addCustomer(email VARCHAR(60), status VARCHAR(15), bill_pin_id SMALLINT UNSIGNED, bill_addr VARCHAR(175), name VARCHAR(60), tel VARCHAR(15))
BEGIN
DECLARE em_id INT UNSIGNED;
DECLARE addr_id INT UNSIGNED;
DECLARE cust_id INT UNSIGNED;
DECLARE sql_error TINYINT DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = true;


-- check if customer email already exists
SELECT id INTO em_id FROM email_list WHERE email = email;

-- not sure of the return value, haven't tested it
IF em_id = 1 THEN

  -- if emails exists check if customer exists
  SELECT id INTO cust_id FROM customer WHERE email_list_id = em_id;
  IF cust_id = 1 THEN

    -- customer must have address information
    SELECT address_id INTO addr_id from customer_addr
        INNER JOIN address on address_id = address.id
        WHERE customer_id = cust_id and address = bill_addr;
  END IF;
END IF;

START TRANSACTION;

IF (em_id = 0) THEN

  -- if email doesn't exist, no customer no address - simple insert
  INSERT INTO email_list (email, status) VALUES (email, status);
  SELECT LAST_INSERT_ID() INTO em_id;

  INSERT INTO customer (email_list_id, full_name, phone)
      VALUES (em_id, name, tel);
  SELECT LAST_INSERT_ID() INTO cust_id;

  INSERT INTO address (pincode_id, address) VALUES (bill_pin_id, bill_addr);
  SELECT LAST_INSERT_ID() INTO addr_id;

  INSERT INTO customer_addr (address_id, customer_id)
      VALUES (addr_id, cust_id);

ELSE
  UPDATE email_list SET status = status where id = em_id;
  UPDATE customer SET full_name = name, phone = tel WHERE id = cust_id;
  UPDATE address SET pincode_id = bill_pin_id, address = bill_addr;
END IF;

IF sql_error = FALSE THEN
  COMMIT;
  SELECT 'SUCCESS';
ELSE
  ROLLBACK;
  SELECT 'FAILED';
END IF;

END
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for taking the time. I corrected line 1 to create procedure and ran the 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 '' at line 3
its coming back to the same error. something to do with declare or begin statement
0

I figured this out by executing the entire procedure via mysql console

It accepted it without any errors. I then accessed the procedure from phpmyadmin routines tab and exported the statement.

The only difference i could find is this

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomer`(
    IN `email` VARCHAR(60),
    IN `status` VARCHAR(15),
    IN `bill_pin_id` SMALLINT UNSIGNED,
    IN `bill_addr` VARCHAR(175),
    IN `name` VARCHAR(60),
    IN `tel` VARCHAR(15)
)

-- regular procedure statements

END$$
DELIMITER ;

I had made some errors with not adding semicolons after if statements as pointed out by Domingo Sambo.

I deleted the procedure and tested it again adding it via phpmyadmin query box and its working perfectly.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.