Solved

Testng MySQL Stored Procedure

Posted on 2013-06-09
6
397 Views
Last Modified: 2013-06-11
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);
0
Comment
Question by:bobbellows
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39233727
Your call statement needs to escape the strings ie:
CALL usp_insert_basic_profile_info (20130609,"Batman","Darknight","Batcave","Gotham","NY",12345,"USA",1234567890,"batman@i-collaborateinc.com","Male",6,9,2013,"Darkknight");
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39233751
You have to enclose literal parameters in single quotes: 'batman', 'darknight', and so on.
0
 

Author Comment

by:bobbellows
ID: 39233790
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Rob
ID: 39233805
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
0
 
LVL 43

Expert Comment

by:Rob
ID: 39233806
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
0
 

Author Closing Comment

by:bobbellows
ID: 39238786
Thanks. This took care of it. Thanks also for the reference help since I'm getting started with MySQL.
Bob Bellows
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question