f



Stored Procedure Syntax error.

I have this statement in a SQL file.

CREATE PROCEDURE HAPPINESS (IN player BIGINT, IN turn INT)
BEGIN
     UPDATE something SET something=player;
     UPDATE something2 set something2=turn;
END;

When I run this command:

bash$ mysql -p table < storedprocedure.sql

I get this error.
ERROR 1064 (42000) at line 1: 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 'UPDATE something SET something=player;' at
line 3

If i take out the BEGIN and END it works fine, but only includes the
first UPDATE statement. The second one get ommited. Which is expected.
It seems to have something to do with the BEGIN statement and I can't
seem to figure it out. Any Suggestions?

0
ryanrk (3)
12/21/2006 8:51:19 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

2 Replies
1495 Views

Similar Articles

[PageSpeed] 54

"Ryan Knopp"  wrote ...
>I have this statement in a SQL file.
>
> CREATE PROCEDURE HAPPINESS (IN player BIGINT, IN turn INT)
> BEGIN
>     UPDATE something SET something=player;
>     UPDATE something2 set something2=turn;
> END;
>
> When I run this command:
>
> bash$ mysql -p table < storedprocedure.sql
>
> I get this error.
> ERROR 1064 (42000) at line 1: 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 'UPDATE something SET something=player;' at
> line 3
>
> If i take out the BEGIN and END it works fine, but only includes the
> first UPDATE statement. The second one get ommited. Which is expected.
> It seems to have something to do with the BEGIN statement and I can't
> seem to figure it out. Any Suggestions?

delimiter /
CREATE PROCEDURE HAPPINESS (IN player BIGINT, IN turn INT)
BEGIN
     UPDATE something SET something=player;
     UPDATE something2 set something2=turn;
END;
/


Regards
Dimitre 


0
cichomitiko (109)
12/21/2006 9:08:32 PM

On Dec 21, 1:08 pm, "Radoulov, Dimitre" <cichomit...@gmail.com> wrote:
> "Ryan Knopp"  wrote ...
>
>
>
>
>
> >I have this statement in a SQL file.
>
> > CREATE PROCEDURE HAPPINESS (IN player BIGINT, IN turn INT)
> > BEGIN
> >     UPDATE something SET something=player;
> >     UPDATE something2 set something2=turn;
> > END;
>
> > When I run this command:
>
> > bash$ mysql -p table < storedprocedure.sql
>
> > I get this error.
> > ERROR 1064 (42000) at line 1: 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 'UPDATE something SET something=player;' at
> > line 3
>
> > If i take out the BEGIN and END it works fine, but only includes the
> > first UPDATE statement. The second one get ommited. Which is expected.
> > It seems to have something to do with the BEGIN statement and I can't
> > seem to figure it out. Any Suggestions?delimiter /
> CREATE PROCEDURE HAPPINESS (IN player BIGINT, IN turn INT)
> BEGIN
>      UPDATE something SET something=player;
>      UPDATE something2 set something2=turn;
> END;
> /
>
> Regards
> Dimitre- Hide quoted text -- Show quoted text -

Ah, something so simple.  For some reason I thought the delimiter was
only for the command line.  
Thanks!

0
ryanrk (3)
12/21/2006 9:19:00 PM
Reply:

Similar Artilces:

Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way for that reason). I would like to, say, execute a stored procedure in DB1, passing in the data blob and other details, DB1 will create a tree node in DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of course, as will DB2 when it adds the blob. Is this possible? [poste...

Stored procedure syntax error
Hello everybody. I know this is kind of lame problem, but I realy cannot get over it. So, I want create a procedure: CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT) DEFINE ref INT; LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WHERE ssr_sub_oid=subjectoid); IF ref=0 THEN ---SOME DELETE STATEMENTS END IF; END PROCEDURE; The problem is I always get a syntax error. The problem is for sure in the IF statement itself. even i try CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT) DEFINE ref INT; LET ref=(SELECT count(*) FROM dbs_sub_spr_relation WH...

stored procedures
Hi all, I am using mysql 5.0.54, i want to create procedure, i tried an example in mysql link "CREATE PROCEDURE sp1 (x VARCHAR(5)) BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid FROM table1 WHERE xname = xname; SELECT newname; END;" but i see an 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 3", In all links &...

Syntax error in stored procedure
Hi, I have problems with one stored procedure that I am trying to develop in my informix database. The procedure is at folloews create procedure updateFlags(emp LIKE employee.idEmployee,check like dailycheck.checkdate) DEFINE selected integer; FOREACH cursor FOR select first 1 iddailycheck into selected from dailycheck DC where DC.idEmployee=emp and DC.checkdate=check and DC.modificationtype='PLANNED' order by DC.modificationdate desc; update dailycheck set lastPlanned=1 where iddailycheck=selected; ...

Mysql Stored Procedure error 1064
I'm very new to mysql, trying to create a Stored procedure using the following syntax: DROP PROCEDURE IF EXISTS add_new_user; DELIMITER | CREATE PROCEDURE add_new_user (firstname VARCHAR, lastname VARCHAR, sal VARCHAR, address VARCHAR, city VARCHAR, 2dstate VARCHAR, zc INT, em VARCHAR, party INT, phone VARCHAR) BEGIN insert into end_user (first_name,last_name,salutation,street_address,city,idref_state_fip,zip,email,party,phone) values (firstname,lastname,sal,address,city, 2dstate,zc,em,party,phone); ...

Stored procedure giving syntax error in different version of informix
Hi, i am new to informix sql procedures.. the sql below works fine in my development box IBM Informix Dynamic Server Version 9.40.FC8 INFORMIX-SQL Version 7.20.UD8 HP-UX B.11.11 but it throws a syntax error in testing box IBM Informix Dynamic Server Version 9.40.FC5W2 INFORMIX-SQL Version 7.20.UD8 HP-UX B.11.11 create procedure p (c char(20)) returning int, char(20); define en int; define is int; define r1 int; define r2 char(20); .. .. .. .. .. return r1, r2; end procedure; looking forward for your help. Regards Raghu raghu.s...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" ...

MySQL stored procedures (MySQL Connector)
Dear Friends, I've received lately quite common (as I've researched) error, which occurs as following: Commands out of sync; you can't run this command now The problem is, that I'm not able to determine whether I'm doing something wrong, my database server is to old or anything else. I've discovered, that when I'm sending queries to database,where I request for database VIEW mode, I'm able to loop them lots of times but when I request for stored procedure I receive an error. My query function looks like so: bool Database_Object::Query(sql::SQLString qu...

Apache2 + MySQL Authentication = MySQL Syntax Error
Hello All, I am trying to setup Apache2 with MySQL Authentication. I can get it to work fine if I do not use group based authentication, i.e.; only look at the table for the user id and password. If I add the group directives I cannot login (it just keeps asking for the username and password) and I get a MySQL syntax error in my http error log. --------------------------------------- Here is my setup: OS = Mandrake 10.1 Apache = 2.0.50-7.2.101mdk MySQL Auth Mod = 2.0.50_1.11-3mdk MySQL = 4.0.20-3.1.101mdk Note that this was all installed via Mandrake RPMs. ---------------...

SQLS7&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure
Hi, I have a problem with updating a datetime column, When I try to change the Column from VB I get "Incorrect syntax near '942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server] '942' is the unique key column value However if I update any other column the syntax is fine The same blanket update query makes the changes no matter what is updated The problem only happens when I set a unique key on the date field in question Key is a composite of an ID, and 2 date fields If I allow duplicates in the index it all works perfectly I am trying to trap '...

stored procedure throwing MySQL 1329 error : No data
Hello, I'm currently working on a SQL script that creates, then calls two stored procedures. Both of these procedures contains a cursor and works on the data but make no SELECT instruction (only UPDATE or DELETE). The problem with the script is that it throws a MySQL 1329 error : "No data - zero rows fetched, selected, or processed". One error per procedure call (each procedure is called only one time in the script, so there are two error messages). In my development environment, the script doesn't stop and reaches the end of file. But my ISP tells me that the script stops...

calling a stored procedure from a stored procedure
Hi Folks, I'm trying to define a (I thought) trivial stored procedure (SP) as a front-end to the standard SP "dbms_system.set_ev", which I want to use to generate a 10046 tracing event. I'm trying to execute the following PL/SQL: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; and getting the error: PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared If I provide the schema in which "set_ev" is defined, in this case "SYS", as in: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin sys.dbms_system.set_ev(sid, serial, 10046, 4, ''); end; I get: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared Any suggestions? thanks, RU On May 10, 1:25 pm, RU <r...@vakuum.de> wrote: > Hi Folks, > > I'm trying to define a (I thought) trivial stored procedure (SP) as > a front-end to the standard SP "dbms_system.set_ev", > which I want to use to generate a 10046 tracing event. I'm trying > to execute the following PL/SQL: > > create or replace procedure rob_enable_tracing ( > sid in integer, > serial in integer > ) > is > begin > dbms_system.set_ev(sid, serial, 10046,...

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never rea...

Storing all stored procedures in one database to minimize different connection strings
In order to minimize the number of connection strings I have to use to access different databases on the same Sql Server, I was considering storing all stored procedures in just one database. I want to do this because connection pooling in my application - ASP.NET is based on this connection string. So if I need to access 6 different databases on one sql server & set 6 different connection strings, I end up creating 6 different connection pools. Other than it might create more management work for the DBA, are there any performance implications with implementing this scheme? Do stored p...

PHP saying error in mysql syntax, but written my mysql query browser!
Hi, I have a basic db that I access with MySQL query browser. Everything seems fine to me but I am using this db as part of a php shopping basket and when I try to add an item I get: Notice: Query failed: 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 '>function.extract]: First argument should be an array in functions.inc.php on line 31 Notice: Undefined variable: price in functions.inc.php on line 36 Notice: Undefined variable: price in functions.inc.php on line 39 Notice: Undefined variable: total...

stored procedure error
I'm trying to call a ms sqlserver stored procedure in vb that takes in a varchar input parameter. here's the vb code: Dim connection As ADODB.connection Dim command As ADODB.command Dim user As String user = "john_smith" Set connection = New ADODB.connection Set command = New ADODB.command connection.Open "DSN=my_dsn;UID=someuser;PWD=somepassword;DATABASE=my_db" Set command.ActiveConnection = connection command.CommandType = adCmdStoredProc command.CommandText = "user_save" comman...

store procedure error
Hi, i am getting error while executing the store procedure db2 "CALL CRS.UPDATE_DEAD_RECORD2(')" SQL0444N Routine "*_RECORD2" (specific name "SQL080221165103852") is implemented with code in library or path "...tine/sqlproc/NDB_PROD/CRS/P6510378", function "*" which cannot be accessed. Reason code: "4". SQLSTATE=42724 has anyone got this error ?? Regards Ramchandra Ramchandra wrote: > Hi, > > i am getting error while executing the store procedure > > db2 "CALL CRS.UPDATE_DEAD_RECO...

stored procedures
Hi all, I am using mysql 5.0.54, i want to create procedure, i tried an example in mysql link "CREATE PROCEDURE sp1 (x VARCHAR(5)) BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid FROM table1 WHERE xname = xname; SELECT newname; END;" but i see an 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 3", In all links &...

Calling a stored procedure from another stored procedure...
Goodmorning, I'm quite new to PostgreSQL, started off with version 8.0 some time ago. Recently I've begun working with stored procedures and now I've come accross a little problem / issue I can't seem to figure out. A quick overview of the situation: I have one stored procedure with returns a SET OF a new (record) type I've declared. For example: mytype index INTEGER; description VARCHAR; cost FLOAT; function calculate_cost(...): returns set of mytype; This stored procedure works without a problem. I can just do a &quo...

mysql-stored procedures
Hi all, I have mysql 5.0.58 installeed on linux platform, i want to create stored procedures, in mysql prompt if i give create procedure abc() select 'abc'; i see error "ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist", i am stuck with this. Is there any other prerequisties to create stored procedure which is missing.. please help me sending proper links or documentation.... Thanks in advance > I have mysql 5.0.58 installeed on linux platform, i want to create >stored procedures, in mysql prompt if i give create procedure abc() >select 'ab...

Error in store procedure
Hi I've the following SP: ----------- CREATE PROCEDURE spServicios @numero int, @maxdif int, @resultado int OUTPUT AS BEGIN SET ROWCOUNT 1 UPDATE control SET registro = getdate() WHERE (fecha >= getdate() AND fecha < dateadd(mi, @maxdif, getdate()) AND clientes_codigo IN (SELECT clientes_codigo FROM telefonos WHERE numero = @numero) SELECT @resultado = @@rowcount SET ROWCOUNT 0 END SELECT @resultado ------------ But when I try to compile I get the message: Incorrect syntax near the keyword 'S...

Stored Procedure Syntax
Hi All Im trying to use the code at the bottom of this message inside my stored procedure and when i execute the procedure in query analyzer i get the following error: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'H'. This error happens a few times and im pretty sure it is because the select statement needs the ' around the data that is being searched. This is what the select statement looks like for me: SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMS OR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRV And im pretty sure it is ...

Stored Procedures Syntax
Hi All Im still pretty new to using stored procedures and am not sure what syntax i should be using. The variable @LocationID will be something along the lines of 002, 003 and so on. What i want to do in the procedure is see if the table already exists, and if so delete it (The code i have only works if there is a record in the table). My problem with the syntax is that i want to combine the word "Location" with the @LocationID variable when making the new table and when checking if the table already exists but im unsure how to combine the two for use in the procedure Thanks in adv...

MySQL Stored Procedure
Hello Eveyone I have to seprate two stored procedure .I wanted result like below. sele a.Id,a.Name,( Call Test(a.ID)) as Cnt FROM T1 a How Can I do that ? thax in advance Vp wrote: > Hello Eveyone > > I have to seprate two stored procedure .I wanted result like below. > > sele a.Id,a.Name,( Call Test(a.ID)) as Cnt FROM T1 a > > How Can I do that ? > > > thax in advance If you have two stored procedures then by definition they are already separate??? Vp wrote: > Hello Eveyone > > I have to seprate two stored procedure .I wanted r...

Web resources about - Stored Procedure Syntax error. - comp.databases.mysql

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

Resources last updated: 2/19/2016 2:27:23 PM