Stored Procedures

This is a multi-part message in MIME format.

------=_NextPart_000_00E7_01C61AA5.ABF05C20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=20
COURSEIDS VARCHAR(1000) ;
TOPICIDS VARCHAR(1000);
COURSENAME VARCHAR(150);
USERNAME VARCHAR(20);

COURSEIDS1 VARCHAR(1000);=20
TOPICIDS1 VARCHAR(1000);=20
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:=3DCOURSEIDS;
SET TOPICIDS1:=3DTOPICIDS;
SET COURSENAME1:=3DCOURSENAME;
SET USERNAME1:=3DUSERNAME;

--PRINT \'INSIDE THE STORED PROCEDURE\'

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

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

QDATE1 :=3D COLUMN1 || COLUMN2 || COLUMN3;

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

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

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

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

-- TRANSACTION STARTS HERE
NEWTOPICID :=3D INDEX;
WHILE (LENGTH(NEWTOPICID) < 4) LOOP
NEWTOPICID :=3D \'0\' || NEWTOPICID;
END LOOP;
NEWTOPICID :=3D \'CRCSUB\' || NEWTOPICID;
INSERT INTO MYTABLE6 VALUES(COURSEID1, TOPICID, NEWCOURSEID, =
NEWTOPICID);
INSERT INTO TOPICS SELECT NEWCOURSEID, NEWTOPICID, TOPICNAME FROM =
TOPICS WHERE COURSEID=3DCOURSEID AND TOPICID=3DTOPICID;
SELECT NULLIF(0,COUNT(*)) INTO COLUMN3 FROM LESSON_FLOW WHERE =
COURSEID=3DCOURSEID AND TOPICID=3DTOPICID;
COLUMN1 :=3D COURSEID1 || \'_\' || TOPICID || \'_\';
COLUMN2 :=3D 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) =3D 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=3DCOURSEID AND =
TOPICID =3D TOPICID
LOOP
LESSON_FLOW_TITLE:=3Drow1.LESSON_FLOW_TITLE;
TREE_NODE_TYPE:=3Drow1.TREE_NODE_TYPE;
VIDEO_SIZE:=3Drow1.VIDEO_SIZE;
ROOT_NODE_ID:=3Drow1.ROOT_NODE_ID;
SLIDE_ORDER:=3Drow1.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 :=3D COLUMN4 + 1;
=20
END LOOP;
INSERT INTO SELECTEDSESSION SELECT NEWCOURSEID, NEWTOPICID, COLUMN2 =
+ SUBSTR(URL,23,LENGTH(URL)) FROM SELECTEDSESSION WHERE COURSEID=3D =
COURSEID1 AND TOPICID=3D TOPICID;
END IF;
QINDEX :=3D 1;
=20
FOR row2 IN SELECT QUESTION_ID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, =
MARKS, FLAG, PAPER_TYPE FROM QUESTION WHERE COURSEID=3DCOURSEID AND =
TOPICID =3D TOPICID
LOOP
QUESTIONID:=3Drow2.QUESTION_ID;
QSNO:=3Drow2.QSNO;
WEIGHTAGE:=3Drow2.WEIGHTAGE;
QSTYPE:=3Drow2.QSTYPE;
QUESTION:=3Drow2.QUESTION;
MARKS:=3Drow2.MARKS;
QFLAG:=3Drow2.FLAG;
PAPER_TYPE:=3Drow2.PAPER_TYPE;
=20
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 :=3D IDENTITY;
INSERT INTO OPTIONS SELECT NEWQUESTIONID, OPTIONS, CORRECTOPTION FROM =
OPTIONS WHERE QUESTION_ID=3DQUESTIONID;
QCODE1 :=3DQCODE1 + 1;
WHILE (LENGTH(QCODE1) < 6) LOOP
QCODE1 :=3D \'0\' || QCODE1;
END LOOP;
QINDEX :=3D QINDEX + 1; =20
END LOOP; =20
QINDEX :=3D 1;

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

COURSEIDS1 :=3D SUBSTR(COURSEIDS1,(CINDEX+1),LENGTH(COURSEIDS1));
TOPICIDS1 :=3D SUBSTR(TOPICIDS1,(TINDEX+1),LENGTH(TOPICIDS1));
CINDEX :=3D POSITION(\':\' IN COURSEIDS1);
TINDEX :=3D POSITION(\':\' IN TOPICIDS1);
INDEX :=3D 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.


------=_NextPart_000_00E7_01C61AA5.ABF05C20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: 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 5: don't forget to increase your free space map settings

Re: [ODBC] Stored Procedures

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"
To: "Minal A. Aryamane"
Cc: ;
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 2: Don't 'kill -9' the postmaster

Re: Stored Procedures

This is a multi-part message in MIME format.
--------------000104020608040301030001
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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"
> To: "Minal A. Aryamane"
> Cc: ;
> 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

--------------000104020608040301030001
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------000104020608040301030001
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--------------000104020608040301030001--

Re: [JDBC] Stored Procedures

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Stored Procedures

"Minal A. Aryamane" 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq