Link to home
Start Free TrialLog in
Avatar of bobbellows
bobbellows

asked on

Testng MySQL Stored Procedure

Dear Experts,

I am new to MySQL. I have created the following Stored Procedure. It did create successfully. Now I would like to try a simple "call" statement in Workbench to test drive it before inserting it into my code. Every time I execute the call I receive an error there is a syntax error. Nothing specific is stated.

Can you tell me what is wrong?

SPROC

DELIMITER $$

CREATE DEFINER=`profiler`@`%` PROCEDURE `usp_insert_basic_profile_info`(
      -- Add the parameters for the stored procedure here
      MemberId INT,
      FirstName VARCHAR(15),
      LastName VARCHAR(30),
      Address1 VARCHAR(60),
      City VARCHAR(45),
      StateProvince VARCHAR(45),
      PostalCode VARCHAR(45),
      Country VARCHAR(45),
      PhoneNumber VARCHAR(20),
      eMAIL VARCHAR(45),
      Gender VARCHAR(8),
      MonthBorn INT(3),
      DayBorn INT(3),
      YearBorn INT(5),
      Alias VARCHAR(20)

)
BEGIN

    -- Insert statements for procedure here

      INSERT INTO basic_profile_info(MemberId, FirstName, LastName, Address1, City,StateProvince,PostalCode,Country,PhoneNumber,eMAIL,Gender,MonthBorn,DayBorn,YearBorn,Alias)

      VALUES (@MemberId, @FirstName, @LastName, @Address1, @City,@StateProvince,@PostalCode,@Country,@PhoneNumber,@eMAIL,@Gender,@MonthBorn,@DayBorn,@YearBorn,@Alias);
END

CALL STATEMENT:

CALL usp_insert_basic_profile_info (20130609,Batman,Darknight,Batcave,Gotham,NY,12345,USA,1234567890,batman@i-collaborateinc.com,Male,6,9,2013,Darkknight);
ASKER CERTIFIED SOLUTION
Avatar of Rob
Rob
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You have to enclose literal parameters in single quotes: 'batman', 'darknight', and so on.
Avatar of bobbellows
bobbellows

ASKER

tagit and vadimrapp1,
Being new to all of this I don't know if single or double quotes is correct. Both removed the error marker at the beginning of the line in Workbench. Both let the call run. Neither one of them allowed the insert o the record. I think that might be a problem with the sproc. Going to post another question for that. Can you give me some reference to check about the quotes so I know which is correct?
You can use either when quoting though you just have to be careful to escape any quotes coming into your database.

See here for reference: http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
Also look to using the QUOTE() function to get around this

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_quote
Thanks. This took care of it. Thanks also for the reference help since I'm getting started with MySQL.
Bob Bellows