f



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;
   end foreach;

end procedure;

I am using dbaccess, what the result is always the same....

Can you help me????

0
Unholy
7/1/2005 10:11:43 AM
comp.databases.informix 16083 articles. 0 followers. Post Follow

4 Replies
641 Views

Similar Articles

[PageSpeed] 35

loose the ; at
>      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;
-------------------------^^---HERE-------

see you

Superboer.


Unholy schreef:
> 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;
>    end foreach;
>
> end procedure;
>
> I am using dbaccess, what the result is always the same....
> 
> Can you help me????

0
Superboer
7/1/2005 12:08:34 PM
It need anopther : afeter the first one???

0
Unholy
7/6/2005 9:39:35 PM
It need another ; after the first one???

0
Unholy
7/6/2005 10:01:30 PM
Unholy try this:


create procedure updateFlags(emp LIKE employee.idEmployee,check like
dailycheck.checkdate)
DEFINE selected integer;
DEFINE dummydate like dailycheck.modificationdate;

     FOREACH cursor FOR select first 1 iddailycheck, modificationdate
                  into selected , dummydate 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;
   end foreach;

end procedure;

have added dummydate since you order by modificationdate
and just compiled and executed it on 930.

have fun 

Superboer.

0
Superboer
7/7/2005 7:02:21 AM
Reply:

Similar Artilces:

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.shivaiah@gmail.com wrote: > 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 > What error do you get ? NB! I would recommend to use dbaccess instead of isql Claus ...

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.
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? "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. ...

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 WHERE ssr_sub_oid=subjectoid); IF ref=0 THEN LET ref=1; END IF; END PROCEDURE; im getting a syntax error. I searched the web but nothing. Please, any ideas are welcomed. I will appreciate any help thx. Martin Try: CREATE PROCEDURE delete_subject_if_no_program(subjectoid INT) DEFINE ref INT; SELECT count(*) into ref FROM dbs_sub_spr_relation WHERE ssr_sub_oid=subjectoid; IF ref=0 THEN LET ref=1; END IF; END PROCEDURE; Superboer. On 22 mei, 10:13, MartinJe...@gmail.com wrote: > 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 ...

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 &...

get syntax error when loading data to a informix database
Hi I have ids 9.4 oon windows and csdk. l successfully connected to the database using Visula Basic 6. When l build a string for example SQL1 = "select * from hse2 where field1 matches " & "'a*' and execute using : DbaseConn.Execute (SQL1) it works fine. When l try to load data to a table : SQL1 = "load from " & " 'd:\x.unl' " & " insert into hse2" or SQL1 = "load from 'd:\x.unl' insert into hse2 I get a syntax error -201. Anyone know why. Thanks in advance Umesh "Umesh" <...

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" ...

Re: get syntax error when loading data to a informix database
Umesh wrote: > Hi > I have ids 9.4 oon windows and csdk. l successfully connected > to the database using Visula Basic 6. > When l build a string for example > SQL1 = "select * from hse2 where field1 matches " & "'a*' > and execute using : DbaseConn.Execute (SQL1) it works fine. > > When l try to load data to a table : > SQL1 = "load from " & " 'd:\x.unl' " & " insert into hse2" > or > SQL1 = "load from 'd:\x.unl' insert into hse2 > > I get a synta...

Re: get syntax error when loading data to a informix database #3
The second form should work. What are you using as a field delimiter? Informix defaults to a pipe "|". If you are using some other character you will need to include the delimiter phrase ... LOAD FROM "pathname" [DELIMITER "char"] INSERT INTO table-name commbtp@lineone.n et (Umes...

Re: get syntax error when loading data to a informix database #2
Umesh said: > Hi > I have ids 9.4 oon windows and csdk. l successfully connected > to the database using Visula Basic 6. > When l build a string for example > SQL1 = "select * from hse2 where field1 matches " & "'a*' > and execute using : DbaseConn.Execute (SQL1) it works fine. > > When l try to load data to a table : > SQL1 = "load from " & " 'd:\x.unl' " & " insert into hse2" > or > SQL1 = "load from 'd:\x.unl' insert into hse2 > > I get a syntax error -201. Anyone know why. Because the LOAD statement is specific to DBACCESS, not SQL. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien � dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed sending to informix-list ...

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 '...

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,...

How to copy a sun-database in one informix database to another informix database?
Hi, Sorry I am not an informix dba, and I was faced to copy a sub-database(sorry I don't know the clear name of such concept, as informix is like sql-server, which used multiple database) from one informix database system to another informix database. I know in oracle, we can use transportable tablespace, copy the datafile and transport the metadata, and do something else. How can I do it in informix then? Is there document which talk about the detail step? Thanks It would really help if you knew the versions of the Informix databases. Assuming Informix IDS 7.x or IDS...

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...

Stored procedure error ....
Trying to use the "DB2 Development Center" on UDB V8 on my Windows XP. I am new to java stored procedures. The stored procedure code I am using is at: https://www6.software.ibm.com/dw/education/dm/db2spdc/sp.java I was able to successfully 'build' the stored procedure that I created using Development Center, however, at run time it is showing the following error: A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4302N Java stored procedure or user-defined function "DB2ADMIN.CHECKRETAILERCREDIT", specific name "SQL040608143836354&quo...

Errors in stored procedure
Hi, I can not get any error from a stored procedure, it seem the execution was succeful even if there was primary key violations, unknow foreign key or RAISERROR. Have you same hints for me? Thanks in advance. This is a code snippet: oCmd :=AdoCommand{} oCmd:ActiveConnection:= self:oConnection oPars := oCmd:PARAMETERS_ oCmd:CommandText := "myProc" oCmd:CommandType := AdCmdStoredProc oPars := oCmd:PARAMETERS_ oPars:Item("@iMyPar1"):VALUE:=iMyPar1 oPars:Item("@iMyPar2"):VALUE:=iMyPar2 oCmd:Execute(nil,nil,nil) ...

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
Hi, Currently coding a program which connects to Informix 7.3x. Java version is 1.4.2. I tend to use stored procedures executing them from Java code and getting recordsets back. Should I use EXECUTE PROCEDURE spl_name (param1,param2) syntax or {call spl_name(?,?)} one with setting parameters ? Shoudl I stick to CallableStatement Thanks in advance and regards, Sergei On 27.09.2006 12:57, tursergei@yandex.ru wrote: > Hi, > > Currently coding a program which connects to Informix 7.3x. Java > version is 1.4.2. > I tend to use stored procedures exec...

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 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...

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...

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...

Web resources about - Syntax error in stored procedure - comp.databases.informix

Federal Rules of Bankruptcy Procedure - Wikipedia, the free encyclopedia
The Federal Rules of Bankruptcy Procedure (abbreviated Fed. R. Bankr. P. or FRBP ) are a set of rules promulgated by the Supreme Court of the ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

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 ...

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

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 ...

Resources last updated: 3/1/2016 12:33:27 AM