Thread: Stored Procedures

Stored Procedures

From

"Minal A. Aryamane"

Date:

16 January 2006, 07:37:11

I am trying to pass values through a jsp page wherein I am accessing this stored procedures by the following select ststemet

SELECT sp_build_course('ETLCRC0001:ETLCRC0010:', 'CRCSUB0001:CRCSUB0001:', 'ABC', 'minal' )

the function is as below:

CREATE OR REPLACE FUNCTION sp_build_course("varchar", "varchar", "varchar", "varchar")
RETURNS text AS
'
Declare
COURSEIDS VARCHAR(1000) ;
TOPICIDS VARCHAR(1000);
COURSENAME VARCHAR(150);
USERNAME VARCHAR(20);

COURSEIDS1 VARCHAR(1000);
TOPICIDS1 VARCHAR(1000);
COURSEID1 VARCHAR(20);
TOPICID1 VARCHAR(20);
NEWCOURSEID VARCHAR(20);
NEWTOPICID VARCHAR(20);
COLUMN1 VARCHAR(50);
COLUMN2 VARCHAR(50);
COLUMN3 VARCHAR(50);
COLUMN4 VARCHAR(50);
QCODE1 VARCHAR(10);
QDATE1 VARCHAR(10);
QSNO INTEGER;
WEIGHTAGE INTEGER;
QSTYPE VARCHAR(50);
QUESTION VARCHAR(1000);
MARKS VARCHAR(50);
QFLAG BOOL;
PFLAG BIT;
PAPER_TYPE VARCHAR(20);
CINDEX INTEGER;
TINDEX INTEGER;
INDEX INTEGER;
QINDEX INTEGER;
QUESTIONID INTEGER;
NEWQUESTIONID INTEGER;
LESSON_FLOW_TITLE VARCHAR(50);
TREE_NODE_TYPE VARCHAR(50);
VIDEO_SIZE INTEGER;
ROOT_NODE_ID INTEGER;
SLIDE_ORDER INTEGER;
row1 record;
row2 record;
row3 record;
COURSENAME1 varchar(150);
USERNAME1 varchar(50);

BEGIN
SET COURSEIDS1:=COURSEIDS;
SET TOPICIDS1:=TOPICIDS;
SET COURSENAME1:=COURSENAME;
SET USERNAME1:=USERNAME;

--PRINT \'INSIDE THE STORED PROCEDURE\'

COLUMN1 := DATE_PART(\'Day\',current_date);
COLUMN2 := DATE_PART(\'Month\',current_date);
COLUMN3 := DATE_PART(\'Year\',current_date);

IF LENGTH(COLUMN1) = 1 THEN
COLUMN1 := \'0\' || COLUMN1;
END IF;
IF LENGTH(COLUMN2) = 1 THEN
COLUMN2 := \'0\' || COLUMN2;
END IF;

QDATE1 := COLUMN1 || COLUMN2 || COLUMN3;

SELECT trim(to_char(max(to_number(QCODE,\'999999\'))+1,\'000009\')) INTO QCODE1 FROM QUESTION WHERE QDATE = QDATE1;

WHILE (LENGTH(QCODE1) < 6) LOOP
QCODE1 := \'0\' || QCODE1;
END LOOP;

SELECT MAX(SUBSTR(COURSEID,7,LENGTH(COURSEID))) INTO NEWCOURSEID FROM COURSEMASTER;

WHILE (LENGTH(NEWCOURSEID) < 4) LOOP
  NEWCOURSEID := \'0\' || NEWCOURSEID;
END LOOP;
  NEWCOURSEID := \'ETLCRC\' || NEWCOURSEID;
  INSERT INTO COURSEMASTER(COURSEID, COURSENAME) VALUES ( NEWCOURSEID,COURSENAME1);
  CINDEX := POSITION(\':\' IN COURSEIDS1);
  TINDEX := POSITION(\':\' IN TOPICIDS1);
  INDEX := 1;
WHILE (CINDEX > 0) LOOP
  COURSEID1 := SUBSTR(COURSEIDS1,1,(CINDEX-1));
  TOPICID := SUBSTR(TOPICIDS1,1,(TINDEX-1));

-- TRANSACTION STARTS HERE
  NEWTOPICID := INDEX;
   WHILE (LENGTH(NEWTOPICID) < 4) LOOP
    NEWTOPICID := \'0\' || NEWTOPICID;
   END LOOP;
   NEWTOPICID := \'CRCSUB\' || NEWTOPICID;
   INSERT INTO MYTABLE6 VALUES(COURSEID1, TOPICID, NEWCOURSEID, NEWTOPICID);
   INSERT INTO TOPICS SELECT NEWCOURSEID, NEWTOPICID, TOPICNAME FROM TOPICS WHERE COURSEID=COURSEID AND TOPICID=TOPICID;
   SELECT NULLIF(0,COUNT(*)) INTO COLUMN3 FROM LESSON_FLOW WHERE COURSEID=COURSEID AND TOPICID=TOPICID;
   COLUMN1 := COURSEID1 || \'_\' || TOPICID || \'_\';
   COLUMN2 := NEWCOURSEID || \'_\' || NEWTOPICID || \'_\';
   IF (COLUMN3 >0) THEN
    SELECT MAX(ATTACHMENT_ID) + 1 INTO COLUMN3 FROM COURSE_LESSON;
    INSERT INTO COURSE_LESSON(COURSEID1, TOPICID, RECORD_STATUS, ATTACHMENT_ID) VALUES (NEWCOURSEID, NEWTOPICID,0, CONVERT(INT, COLUMN3));
    INSERT INTO ATTACHMENT SELECT COLUMN3, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)), SEQUENCE FROM ATTACHMENT WHERE SUBSTR(URL,0,23) = COLUMN1;
    SELECT MAX(LF_ID) + 1 INTO COLUMN4 FROM LESSON_FLOW;

    FOR row1 IN SELECT LESSON_FLOW_TITLE, TREE_NODE_TYPE, VIDEO_SIZE, ROOT_NODE_ID, SLIDE_ORDER FROM LESSON_FLOW WHERE COURSEID=COURSEID AND TOPICID = TOPICID
    LOOP
     LESSON_FLOW_TITLE:=row1.LESSON_FLOW_TITLE;
     TREE_NODE_TYPE:=row1.TREE_NODE_TYPE;
     VIDEO_SIZE:=row1.VIDEO_SIZE;
     ROOT_NODE_ID:=row1.ROOT_NODE_ID;
     SLIDE_ORDER:=row1.SLIDE_ORDER;

     INSERT INTO LESSON_FLOW VALUES (COLUMN4, NEWCOURSEID, NEWTOPICID, LESSON_FLOW_TITLE, TREE_NODE_TYPE, VIDEO_SIZE, COLUMN3, ROOT_NODE_ID, SLIDE_ORDER);
     COLUMN4 := COLUMN4 + 1;

    END LOOP;
    INSERT INTO SELECTEDSESSION SELECT NEWCOURSEID, NEWTOPICID, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)) FROM SELECTEDSESSION WHERE COURSEID= COURSEID1 AND TOPICID= TOPICID;
   END IF;
   QINDEX := 1;

  FOR row2 IN SELECT QUESTION_ID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, FLAG, PAPER_TYPE FROM QUESTION WHERE COURSEID=COURSEID AND TOPICID = TOPICID
  LOOP
   QUESTIONID:=row2.QUESTION_ID;
   QSNO:=row2.QSNO;
   WEIGHTAGE:=row2.WEIGHTAGE;
   QSTYPE:=row2.QSTYPE;
   QUESTION:=row2.QUESTION;
   MARKS:=row2.MARKS;
   QFLAG:=row2.FLAG;
   PAPER_TYPE:=row2.PAPER_TYPE;

   INSERT INTO QUESTION (COURSEID1, TOPICID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, QDATE, QCODE,FLAG, USERNAME, PAPER_TYPE) VALUES (NEWCOURSEID, NEWTOPICID, QINDEX, WEIGHTAGE, QSTYPE, QUESTION, MARKS, QDATE1, QCODE,QFLAG, USERNAME1, PAPER_TYPE);
   NEWQUESTIONID := IDENTITY;
   INSERT INTO OPTIONS SELECT NEWQUESTIONID, OPTIONS, CORRECTOPTION FROM OPTIONS WHERE QUESTION_ID=QUESTIONID;
   QCODE1 :=QCODE1 + 1;
   WHILE (LENGTH(QCODE1) < 6) LOOP
    QCODE1 := \'0\' || QCODE1;
   END LOOP;
   QINDEX := QINDEX + 1;
  END LOOP;
  QINDEX := 1;

  FOR row3 IN SELECT QUESTION_ID, QSNO, QSTYPE, QUESTION, FLAG FROM POLL_QUESTION WHERE COURSEID=COURSEID AND TOPICID = TOPICID
  LOOP
   QUESTIONID:=row3.QUESTIONID;
   QSNO:=row3.QSNO;
   QSTYPE:=row3.QSTYPE;
   QUESTION:=row3.QUESTION;
   PFLAG:=row3.FLAG;
    INSERT INTO POLL_QUESTION (COURSEID1, TOPICID, QSNO, QSTYPE, QUESTION, QDATE, QCODE,FLAG, USERNAME) VALUES (NEWCOURSEID, NEWTOPICID, QINDEX, QSTYPE, QUESTION, QDATE1, QCODE1,PFLAG, USERNAME1);
    NEWQUESTIONID := IDENTITY;
    INSERT INTO POLL_OPTIONS SELECT NEWQUESTIONID, OPTIONS, SUB_OPTIONS, TYPE,DISPLAY FROM POLL_OPTIONS WHERE QUESTION_ID=QUESTIONID;
    QCODE1 := QCODE1 + 1;
    WHILE (LENGTH(QCODE1) < 6) loop
     QCODE1 := \'0\' || QCODE1;
    END LOOP;
    QINDEX := QINDEX + 1;
  END LOOP;

COURSEIDS1 := SUBSTR(COURSEIDS1,(CINDEX+1),LENGTH(COURSEIDS1));
TOPICIDS1 := SUBSTR(TOPICIDS1,(TINDEX+1),LENGTH(TOPICIDS1));
CINDEX := POSITION(\':\' IN COURSEIDS1);
TINDEX := POSITION(\':\' IN TOPICIDS1);
INDEX := INDEX + 1;

END LOOP;

RETURN \'1\';
END;

'
LANGUAGE 'plpgsql' VOLATILE;

when I run it I get the following error:

ERROR: syntax error at or near "$1" at character 6
CONTEXT: PL/pgSQL function "sp_build_course" line 45 at SQL statement.

Can someone please look into the procedure and tell me where I have gone wrong.

Re: Stored Procedures

From

Ludek Finstrle

Date:

16 January 2006, 07:55:27

Mon, Jan 16, 2006 at 02:03:48PM +0530, Minal A. Aryamane napsal(a):
> I am trying to pass values through  a jsp page wherein I am accessing
> this stored procedures by the following select ststemet

...

> when I run it I get the following error:
> ERROR:  syntax error at or near "$1" at character 6
> CONTEXT:  PL/pgSQL function "sp_build_course" line 45 at SQL statement.
>
> Can someone please look into the procedure and tell me where I have
> gone wrong.

Are you sure you choose the right pgsql lists?
Does it work from psql command line client? If not it isn't odbc or jdbc
related.

Regards,

Luf

Re: Stored Procedures

From

"Minal A. Aryamane"

Date:

16 January 2006, 08:51:39

I dont knoiw if I have got the right mailing list, I thought some one will
be able to help me in this regard..

----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: "Minal A. Aryamane" <minalac@yes2etl.com>
Cc: <pgsql-jdbc@postgresql.org>; <pgsql-odbc@postgresql.org>
Sent: Monday, January 16, 2006 2:24 PM
Subject: Re: [ODBC] Stored Procedures


> Mon, Jan 16, 2006 at 02:03:48PM +0530, Minal A. Aryamane napsal(a):
> > I am trying to pass values through  a jsp page wherein I am accessing
> > this stored procedures by the following select ststemet
>
> ...
>
> > when I run it I get the following error:
> > ERROR:  syntax error at or near "$1" at character 6
> > CONTEXT:  PL/pgSQL function "sp_build_course" line 45 at SQL statement.
> >
> > Can someone please look into the procedure and tell me where I have
> > gone wrong.
>
> Are you sure you choose the right pgsql lists?
> Does it work from psql command line client? If not it isn't odbc or jdbc
> related.
>
> Regards,
>
> Luf

Re: Stored Procedures

From

"Campbell, Greg"

Date:

16 January 2006, 14:19:19

So, do you have access to the server command line to run psql and send commands? or pgAdmin? Does
executing your stored procedure fail from there?

Ths directory of mailing lists is available at http://www.postgresql.org/community/lists/

You are probably needed to address pgsql-general.

That said, as a developer, I would tend to make a copy of the original, then comment out or rip out code
until I find the problem.

BTW
your procedure definition looks strange
CREATE OR REPLACE FUNCTION sp_build_course("varchar", "varchar", "varchar", "varchar")
instead of
CREATE OR REPLACE FUNCTION sp_build_course(VARCHAR, VARCHAR, VARCHAR, VARCHAR)
Did pgAdmin build this?

You call a procedure with 4 varchar parameters, yet I do not see the points in the procedure where they
are substituted in $1, $2, $3, $4. Why send parameters is they are not used?

You can read up on CREATE FUNCTION in the Postgresql docs. RTM, always a last resort.
Kindly advice. Start with something ridiculously simple, then build it from there.

Minal A. Aryamane wrote:
> I dont knoiw if I have got the right mailing list, I thought some one will
> be able to help me in this regard..
>
> ----- Original Message -----
> From: "Ludek Finstrle" <luf@pzkagis.cz>
> To: "Minal A. Aryamane" <minalac@yes2etl.com>
> Cc: <pgsql-jdbc@postgresql.org>; <pgsql-odbc@postgresql.org>
> Sent: Monday, January 16, 2006 2:24 PM
> Subject: Re: [ODBC] Stored Procedures
>
>
>
>>Mon, Jan 16, 2006 at 02:03:48PM +0530, Minal A. Aryamane napsal(a):
>>
>>>I am trying to pass values through  a jsp page wherein I am accessing
>>>this stored procedures by the following select ststemet
>>
>>...
>>
>>
>>>when I run it I get the following error:
>>>ERROR:  syntax error at or near "$1" at character 6
>>>CONTEXT:  PL/pgSQL function "sp_build_course" line 45 at SQL statement.
>>>
>>>Can someone please look into the procedure and tell me where I have
>>>gone wrong.
>>
>>Are you sure you choose the right pgsql lists?
>>Does it work from psql command line client? If not it isn't odbc or jdbc
>>related.
>>
>>Regards,
>>
>>Luf
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

Attachment

greg.campbell.vcf

Re: [JDBC] Stored Procedures

From

Markus Schaber

Date:

16 January 2006, 15:57:25

Hi, Minal,

Minal A. Aryamane wrote:
> I dont knoiw if I have got the right mailing list, I thought some one will
> be able to help me in this regard..

This is easy to decide:

If your SQL statements work via psql or other command line tools, but
fail when submittend via ODBC, use the pgsql-odbc@postgresql.org list.

If it works via psql, but fails via JDBC, use the
pgsql-jdbc@postgresql.org list.

If you use neither JDBC nor ODBC, or the query equally fails via psql,
try a list like pgsql-sql@postgresql.org.

When submitting your problem to an inappropriate list, there are less
people _capable_ to help, and less people _willing_ to help.

HTH,
Schabi

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Stored Procedures

From

Tom Lane

Date:

16 January 2006, 16:46:45

"Minal A. Aryamane" <minalac@yes2etl.com> writes:
> the function is as below:
> ...
> SET COURSEIDS1:=COURSEIDS;
> ...

This is completely off-topic for both the lists you have chosen, but
I'd say the problem is that you must not use the SET keyword when
assigning to a plpgsql local variable.

            regards, tom lane