List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 1 2005 5:32pm
Subject:Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
View as plain text  
What I do is prepare the CREATE PROC script in a text editor template 
which starts with
  DELIMITER |
and ends with
  |
  DELIMITER ;

PB

-----

Anchan, Dinesh wrote:

> Yes it is the delimiter which was giving the errors.  I had tried 
> putting those statements inside the script, which didn't work.  But 
> when i set it from the command line before executing the script to 
> create the procedure it worked.  I am still not clear on how to use 
> delimiter without much pain but i will get there.
>  
> Thanks for your help.
>  
> Dinesh
>
> ------------------------------------------------------------------------
> *From:* Peter Brawley [mailto:peter.brawley@stripped]
> *Sent:* Friday, April 01, 2005 12:03 PM
> *To:* Anchan, Dinesh
> *Cc:* mysql@stripped
> *Subject:* Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
>
> Dinesh,
>
> Do you have something like
>   DELIMITER |
> before the CREATE PROCEDURE call, and
>   |
>   DELIMITER ;
> after it?
>
> Peter Brawley
> http://www.artfulsoftware.com
>
> -----
>
> Anchan, Dinesh wrote:
>
>>Hi,
>> 
>>I am looking to migrate an Informix database to MySQL and trying to
>>write a simple stored procedure in MySQL 5.0.3.   I referred to few
>>examples posted and used the statements from them but i get errors while
>>creating this procedure.  
>> 
>>CREATE PROCEDURE sp_test
>>(
>>   IN user_id    integer,
>>   IN seg_id     integer
>>)
>> 
>>BEGIN
>> 
>>   declare p_rows int;
>>   set p_rows = 0;
>> 
>>   select count(*)
>>     into p_rows
>>     from test
>>    where user_id = user_id
>>      and seg_id  = seg_id;
>> 
>>   if p_rows = 1 then
>>      update test
>>         set visits = visits+1
>>       where user_id = user_id
>>         and seg_id  = seg_id;
>>   else
>>      INSERT INTO test
>>      VALUES (user_id,seg_id, 1);
>>   end if;
>> 
>>END
>> 
>>ERROR:
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>manual that corresponds to your MySQL server version for the right
>>syntax to use near '' at line 7
>>ERROR 1193 (HY000): Unknown system variable 'p_rows'
>>ERROR 1327 (42000): Undeclared variable: p_rows
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>manual that corresponds to your MySQL server version for the right
>>syntax to use near 'if p_rows = 1 then
>>      update test
>>         set visits = visits+1
>>       wher' at line 1
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>manual that corresponds to your MySQL server version for the right
>>syntax to use near 'else
>>      INSERT INTO test
>>      VALUES (user_id,seg_id, 1)' at line 1
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>manual that corresponds to your MySQL server version for the right
>>syntax to use near 'end if' at line 1
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>manual that corresponds to your MySQL server version for the right
>>syntax to use near 'END' at line 1
>> 
>>Any help would be appreciated.
>> 
>>Thanks
>> 
>>Dinesh
>> 
>> 
>> 
>>
>>  
>>
>>------------------------------------------------------------------------
>>
>>No virus found in this incoming message.
>>Checked by AVG Anti-Virus.
>>Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
>>  
>>
> //
>
>/
>/
>------------------------------------------------------------------------
>/
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
>/
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
Thread
Need help with Stored Procedures iin MySQL 5.0.3-betaDinesh Anchan1 Apr
  • Re: Need help with Stored Procedures iin MySQL 5.0.3-betaPeter Brawley1 Apr
RE: Need help with Stored Procedures iin MySQL 5.0.3-betaDinesh Anchan1 Apr
  • Re: Need help with Stored Procedures iin MySQL 5.0.3-betaPeter Brawley1 Apr