RE: Help Error 675 on stored procedure.

This also needs to be in the fact. (Sorry Dave:-) ) You must set MIqry2pass=on to get around the 675 error. The code will work perfectly in dbaccess etc but bombs when called via webexplode Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # forever www.wfsoftware.com # > -----Original Message----- > From: David Williams [mailto:djw@smooth1.demon.co.uk] > Sent: Tuesday, June 13, 2000 11:41 PM > To: informix-list@iiug.org > Subject: Re: Help Error 675 on stored procedure. > > > In article <8i5h1s$122$1@nnrp1.deja.com>, ram_cnc <rsivaguru@my- > deja.com> writes > >Friends, > > > >I need some help here to figure out some problem that I am > stuck with. > > > >Environment: > > > >OS : HP/UX 10.20 with Patch level 48. > >Informix : Universal Server 9.14UC7 > >Datablade: Web Datablade 4.00.UC1 > >Web Server: Netscape Enterprise Server 3.51 > > > >I have a stored procedure that works perfectly well as I test it from > >dbaccess. However when I issue the call to the stored procedure from > >within the web datablade 4.00 I get the error -675. > > > >-675 Illegal SQL statement in stored procedure. > > > >on the trace file of the stored procedure and a Dynamic Page > Generation > >Error:Illegal SQL statement in stored procedure on the web server. > > > >The stored procedure checks for the user, institution to be > present in > >a table email_stats. If present it updates a counter on the > table other > >wise it inserts a new row in the table email_stats. > > > >The stored procedure is (Some code commented for debugging purposes): > > > >drop function "stardba".sendGroupMail; > >create function "stardba".sendGroupMail(FILE varchar(255), TO varchar > >(64), > > UsrLogon varchar(32), IstInstId integer, GROUPNAME varchar(64), > >CREATEDBY varchar(64)) > >returning integer; > > > > > > define reccount int; > > define ONE int; > > define ZERO int; > > define U varchar(32); > > define I int; > > define C int; > > define mailcall varchar(255); > > define makegrp varchar(255); > > set debug file to "/tmp/ram.log"; > > trace on; > > > > let ONE=1; > > let ZERO=0; > > > >-- let makegrp = '/clients/star/mail/bin/cmdutils/addlistshort' > >|| ' ' || GROUPNAME || ' ' || CREATEDBY; > >-- system makegrp; > > > >-- let mailcall > >= '/clients/star/content/private/bin/star_sendmail.sh' || ' > ' || FILE; > >-- system mailcall; > > > > select count(*)::int into reccount from email_stats > > where ems_usr_logon = UsrLogon > > and ems_ist_inst_id = IstInstId;> > > > if reccount == 0 then > > insert into email_stats (ems_usr_logon, ems_ist_inst_id, > > ems_total_sent, ems_total_received) > > values (UsrLogon,IstInstId,ONE,ZERO);> > else > > let One = 2; > update email_stats set (ems_total_sent) =(ems_total_sent+1) > > where ems_usr_logon = UsrLogon > > and ems_ist_inst_id = IstInstId;> let One = 3; > > end if; > > > > trace off; > > > > return 1; > > > >end function; > > > > > What trace does that give? > > When I think I have found the statement which failed I always > put simple one-line debugs BEFORE and AFTER it! > > I think you can also do something like > > TRACE "FIXME - Before it!!"; > update... > TRACE "FIXME - After it !!"; > > > Just remember to remove the statements with 'FIXME' in them > afterwards. > > >The trace Output is: > > > >trace on > > > >let one = 1 > >let zero = 0 > > > >expression: > > (select (count *)::INT > > from email_stats > > where (and (= ems_usr_logon, usrlogon), (= ems_ist_inst_id, > >istinstid))) > >evaluates to 1 ; > >let reccount = 1 > >expression:(= reccount, 0) > >evaluates to f > > > >update email_stats set > > (ems_total_sent) = ((+ ems_total_sent, 1)) > > where (and (= ems_usr_logon, usrlogon), (= ems_ist_inst_id,> >istinstid)); > >exception : looking for handler > >SQL error = -675 ISAM error = 0 error string = = ""> >exception : no appropriate handler > > > >Any help is much appreciated. > > > >Thanks > > > >Ram S. > > > > > >Sent via Deja.com http://www.deja.com/ > >Before you buy. > > -- > David Williams > The contents of this e-mail are confidential to the ordinary user(s) of the mail address(es) to which it was sent and may be legally privileged. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.