Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B36B7475C7C for ; Wed, 14 Aug 2002 08:24:01 -0400 (EDT) Received: from herald.cybage.com (unknown [203.124.129.210]) by postgresql.org (Postfix) with ESMTP id 379A6475C65 for ; Wed, 14 Aug 2002 08:23:58 -0400 (EDT) Received: from Sugandha ([192.168.5.32]) by herald.cybage.com with Microsoft SMTPSVC(5.0.2195.4453); Wed, 14 Aug 2002 17:56:16 +0530 Message-ID: <026801c2438d$c9466a00$2005a8c0@cybage.com> From: "Sugandha Shah" To: Subject: parser: parse error at or near "$1" Date: Wed, 14 Aug 2002 17:56:16 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0265_01C243BB.E2B4A4B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-OriginalArrivalTime: 14 Aug 2002 12:26:16.0535 (UTC) FILETIME=[C93B4670:01C2438D] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/221 X-Sequence-Number: 9067 This is a multi-part message in MIME format. ------=_NextPart_000_0265_01C243BB.E2B4A4B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi , I'm getting this error : Error occurred while executing PL/pgSQL function ins_schedule_status line 42 at SQL statement parser: parse error at or near "$1" Unable to locate the cause. Please any hint or clue will be of great help. Below is the function=20 CREATE FUNCTION ins_schedule_status(int4,time without time zone,varchar(256= ),int2,int2,int2) RETURNS int4 AS ' DECLARE=20 schedule_id ALIAS FOR $1; var_current_time ALIAS FOR $2; status ALIAS FOR $3; status_code ALIAS FOR $4; module ALIAS FOR $5; var_level ALIAS FOR $6 ; last_status varchar(256) ; last_code int2; level_value int2 ; BEGIN=20 IF ( var_level !=3D 0 )THEN --select level_value =3Dvar_level; level_value :=3Dvar_level; ELSE IF ( status_code =3D 0 or status_code < 0 )THEN --select level_value =3D 1 ; level_value :=3D 1 ; ELSE --select level_value =3D 3 ; level_value :=3D 3 ; =20=20 END IF; END IF; -- dont report server control values, just positive values IF ( status_code < 0 ) THEN --select status_code =3D null; --status_code :=3D null; =20=20=20=20=20=20=20=20=20=20 -- check for the exact same status string reported -- previously and skip it if found select into last_status top 1 status, last_code status_code f= rom status_log where schedule_id =3Dschedule_id order by complete_time desc= ; if ( last_status =3D status and ( ( last_code is null and status_= code is null ) or last_code =3D status_code ) ) THEN return ; END IF; END IF; =20=20=20=20=20=20=20 insert into status_log ( log_id, computer_id,schedule_id,status, status_code, module, level, user_id, schedule_time, complete_time )=20 select 1, computer_id, schedule_id, status, status_code, module, level_value, user_id, start_time, var_current_time from event_schedule where schedule_id =3Dschedule_id; =20=20=20=20=20=20=20=20=20 return status_code; END;' LANGUAGE 'plpgsql' ; Original MS _SQL stored procedure=20 create procedure ins_schedule_status @schedule_id int, @current_time datetime,=20 @status varchar(254),=20 @status_code smallint,=20 @module smallint, @level smallint =3D 0 as declare @level_value smallint if ( @level !=3D 0 ) select @level_value =3D @level else begin if ( @status_code =3D 0 or @status_code < 0 ) select @level_value =3D 1 else select @level_value =3D 3 end -- don't report server control values, just positive values if ( @status_code < 0 ) select @status_code =3D null -- check for the exact same status string reported=20 -- previously and skip it if found declare @last_status varchar(256) declare @last_code smallint select top 1 @last_status =3D status, @last_code =3D status_code from st= atus_log=20 where schedule_id =3D @schedule_id order by complete_time desc if ( @last_status =3D @status and ( ( @last_code is null and @status_cod= e is null ) or @last_code =3D @status_code ) ) return declare @tran int set @tran =3D @@trancount if (@tran =3D 0) begin transaction insert into status_log ( computer_id, schedule_id, status, status_code, [module], [level], [user_id], schedule_time, complete_time ) select computer_id, schedule_id, @status, @status_code, @module, @level_value, user_id, start_time, @current_time=20 from=20 event_schedule where schedule_id =3D @schedule_id if (@@error !=3D 0) begin if (@tran =3D 0) rollback transaction end if (@tran =3D 0) commit transaction=20 GO Regards, -Sugandha ------=_NextPart_000_0265_01C243BB.E2B4A4B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi ,
I'm getting this  error :
 
Error occurred while executing PL/pgSQL = function=20 ins_schedule_status
 line 42 at SQL statement
parser: parse erro= r at=20 or near "$1"
 
Unable to locate the cause. Please any h= int or=20 clue will be of great help.
 
Below is the function
 
 
CREATE FUNCTION ins_schedule_status(int4= ,time=20 without time zone,varchar(256),int2,int2,int2) RETURNS int4 AS '
DECLARE= =20
     schedule_id ALIAS FOR=20 $1;
     var_current_time ALIAS FOR=20 $2;
     status ALIAS FOR $3;
   &= nbsp;=20 status_code ALIAS FOR $4;
     module ALIAS FOR=20 $5;
     var_level  ALIAS FOR $6=20 ;
     last_status    varchar(256)=20 ;
     last_code     =20 int2;
     level_value int2 ;
BEGIN=20
      IF ( var_level !=3D 0=20 )THEN
           =  =20 --select level_value=20 =3Dvar_level;
         &nbs= p;  =20 level_value :=3Dvar_level;
      ELSE
 
          IF ( status= _code =3D=20 0 or status_code < 0=20 )THEN
           = =20 --select level_value =3D 1=20 ;
           &nbs= p;=20 level_value :=3D 1 ;
        &nb= sp;=20 ELSE
           = =20 --select level_value =3D 3=20 ;
           =20 level_value :=3D 3 ;
 =20
         END=20 IF;
      END IF;
 
           -- do= nt=20 report server control values, just positive=20 values
       IF ( status_code < 0 )&nb= sp;=20 THEN
         --select =20 status_code =3D null;
        = =20  --status_code :=3D=20 null;
         =20
          -- check for the= =20 exact same status string=20 reported
          -- = =20 previously and skip it if found
 
          select = ; into=20 last_status  top 1  status, last_code  status_code from=20 status_log where schedule_id =3Dschedule_id order by complete_time desc=20 ;
          if ( last_statu= s =3D=20 status and ( ( last_code is null and status_code is null ) or last_code =3D= =20 status_code ) )=20 THEN
           &= nbsp;          =20 return  ;
          EN= D=20 IF;
 
       END= =20 IF;
      =20
        insert into status_log ( log= _id,=20 computer_id,schedule_id,status,
      &nbs= p; =20 status_code,
        =20 module,
        =20 level,
        =20 user_id,
        =20 schedule_time,
         complete= _time=20 )
   select 1,
     =20 computer_id,
     =20 schedule_id,
     =20 status,
     =20 status_code,
     =20 module,
     =20 level_value,
     =20 user_id,
     =20 start_time,
     =20 var_current_time
     =20 from
        =20 event_schedule
     =20 where
         schedule_id=20 =3Dschedule_id;
         =
 

    return status_code;
 
END;'
LANGUAGE 'plpgsql' ;
 
 
Original MS _SQL stored procedure
 
create procedure ins_schedule_status
  =20 @schedule_id     int,
  =20 @current_time    datetime,
  =20 @status          varchar(254),= =20
   @status_code     smallint,
 &= nbsp;=20 @module         =20 smallint,
  =20 @level           smallint= =3D=20 0
as
   declare @level_value smallint
   if (= =20 @level !=3D 0 )
      select @level_value =3D= =20 @level
   else
  =20 begin
      if ( @status_code =3D 0 or @status_= code=20 < 0 )
         select @level_= value=20 =3D 1
     =20 else
         select @level_valu= e =3D=20 3
   end
 
   -- don't report server control values, just positive=20 values
   if ( @status_code < 0=20 )
      select @status_code =3D null
 
   -- check for the exact same status string reported=20
   --    previously and skip it if=20 found
   declare @last_status   =20 varchar(256)
   declare @last_code    &nbs= p;=20 smallint
   select top 1 @last_status =3D status, @last_code = =3D=20 status_code from status_log
      where schedu= le_id=20 =3D @schedule_id order by complete_time desc
   if ( @last_sta= tus =3D=20 @status and ( ( @last_code is null and @status_code is null ) or @last_code= =3D=20 @status_code ) )
      return
 
   declare @tran int
   set @tran =3D=20 @@trancount
 
   if (@tran =3D 0)
      begin= =20 transaction
 
   insert into status_log
     = =20 (  computer_id,
        =20 schedule_id,
        =20 status,
        =20 status_code,
        =20 [module],
        =20 [level],
        =20 [user_id],
        =20 schedule_time,
         complete= _time=20 )
   select
     =20 computer_id,
     =20 schedule_id,
     =20 @status,
     =20 @status_code,
     =20 @module,
     =20 @level_value,
     =20 user_id,
     =20 start_time,
      @current_time=20
      from=20
        =20 event_schedule
     =20 where
         schedule_id =3D= =20 @schedule_id
 
   if (@@error !=3D 0)
  =20 begin
      if (@tran =3D=20 0)
         rollback=20 transaction
   end
 
   if (@tran =3D 0)
      commit= =20 transaction
 
GO
 
 
Regards,
-Sugandha
 ------=_NextPart_000_0265_01C243BB.E2B4A4B0--