Solved

Testng MySQL Stored Procedure

Posted on 2013-06-09
6
387 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
  • 3
  • 2
6 Comments
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now