Solved

Testng MySQL Stored Procedure

Posted on 2013-06-09
6
399 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
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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