Solved

Testng MySQL Stored Procedure

Posted on 2013-06-09
6
395 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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