Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id E1205475923; Tue,  4 Jun 2002 02:55:43 -0400 (EDT)
Received: from vreach.net (unknown [203.200.123.44])
	by postgresql.org (Postfix) with ESMTP
	id 2546D47591A; Tue,  4 Jun 2002 02:54:54 -0400 (EDT)
Received: from lokhi [194.168.2.19] by vreach.mzi.net [194.168.2.30]
	with SMTP (MDaemon.PRO.v5.0.5.R); Tue, 04 Jun 2002 12:24:27 +0530
Message-ID: <002001c20b94$5a1a65e0$1302a8c2@multicast.com>
From: "Sandeep Chibber" <sandeep@vreach.net>
To: <pgsql-sql@postgresql.org>, <pgsql-sql-owner@postgresql.org>,
	<pgsql-odbc-owner@postgresql.org>, <pgsql-jdbc-owner@postgresql.org>
Cc: <pgsql-novice-owner@postgresql.org>, <pgsql-general-owner@postgresql.org>,
	<pgsql-bugs-owner@postgresql.org>, <pgsql-admin-owner@postgresql.org>
Subject: Postgres Function Problem
Date: Tue, 4 Jun 2002 12:22:10 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001D_01C20BC2.73619090"
X-Priority: 1
X-MSMail-Priority: High
X-Mailer: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
X-MDRemoteIP: 194.168.2.19
X-Return-Path: sandeep@vreach.net
X-Archive-Number: 200206/18
X-Sequence-Number: 7983

This is a multi-part message in MIME format.

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

Hi,

I am Sandeep Chibber from vReach Solutions. We are using postgres functions=
 with java beans. There is no problem with simple postgres functions which =
have Simple select statements WITHOUT where clause. But when we have sql st=
atements in the  postgres function in which WHERE clause is used ,then we h=
ave problem of passing values to the WHERE clause.Like in the following fun=
ction,how to pass values to $1 an $2 through my java program. In DB2 we use=
 CallableStatement.setString(1,"user_id) to set values to user_id of string=
(varchar) datatype. Postgres has some other way ,which we are not able to l=
ocate.

CREATE FUNCTION tester7 ( varchar, varchar ) returns setof ICDN_EVENTREG as=
 '  SELECT * FROM    ICDN_EVENTREG        WHERE     ICDN_EVENTREG.USER_ID =
=3D $1 AND ICDN_EVENTREG.PASSWORD =3D $2    ;' LANGUAGE SQL=20

The table structure is as follows

CREATE TABLE ICDN_EVENTREG  (  USER_ID VARCHAR(30) NOT NULL ,   PASSWORD VA=
RCHAR(16) ,   EMAIL VARCHAR(80) ,  DEPARTMENT VARCHAR(30) ,   FIRST_NAME VA=
RCHAR(30) ,  LAST_NAME VARCHAR(30) ,    DESIGNATION VARCHAR(30) ,  TYPE VAR=
CHAR(15) ,  STATUS VARCHAR(15) DEFAULT 'Pending') ;


So kindly guide us how to pass values to the $1,$2 in WHERE clause through =
our java beans and also how to execute this function in the pgAdmin II

Thanking in anticipation

Sandeep Chibber

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I am Sandeep Chibber from vReach Solutions=
.&nbsp;We=20
are using postgres functions with java beans.</FONT><FONT face=3DArial size=
=3D2>=20
There is no problem with simple&nbsp;postgres functions which have&nbsp;Sim=
ple=20
select statements WITHOUT where clause. But when we have sql statements in=
=20
the&nbsp; postgres function in which WHERE clause is used ,then we have=20
problem&nbsp;of passing values to the WHERE clause.Like in the following=20
function,how to pass values to $1 an $2 through my java program. In DB2 we =
use=20
CallableStatement.setString(1,"user_id) to set values to user_id of=20
string(varchar) datatype.&nbsp;Postgres has some other way ,which we are no=
t=20
able to locate.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>CREATE FUNCTION tester7 ( varchar, varchar=
 )=20
returns setof ICDN_EVENTREG as '&nbsp; SELECT * FROM&nbsp;&nbsp;&nbsp;=20
ICDN_EVENTREG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
WHERE&nbsp;&nbsp;&nbsp;&nbsp; ICDN_EVENTREG.USER_ID =3D $1 AND=20
ICDN_EVENTREG.PASSWORD =3D $2&nbsp;&nbsp;&nbsp; ;' LANGUAGE SQL </FONT></DI=
V>
<DIV>&nbsp;</DIV>
<DIV>The table structure is as follows</DIV>
<DIV>&nbsp;</DIV>
<DIV>CREATE TABLE ICDN_EVENTREG&nbsp; (&nbsp; USER_ID VARCHAR(30) NOT NULL=
=20
,&nbsp;&nbsp; PASSWORD VARCHAR(16) ,&nbsp;&nbsp; EMAIL VARCHAR(80) ,&nbsp;=
=20
DEPARTMENT VARCHAR(30) ,&nbsp;&nbsp; FIRST_NAME VARCHAR(30) ,&nbsp; LAST_NA=
ME=20
VARCHAR(30) ,&nbsp;&nbsp;&nbsp; DESIGNATION VARCHAR(30) ,&nbsp; TYPE VARCHA=
R(15)=20
,&nbsp; STATUS VARCHAR(15) DEFAULT 'Pending') ;<BR></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>So kindly guide us how to pass values to t=
he $1,$2=20
in WHERE clause through our java beans and also how to execute this functio=
n in=20
the pgAdmin II</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanking in anticipation</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Sandeep=20
Chibber</FONT></DIV></FONT></DIV></BODY></HTML>

------=_NextPart_000_001D_01C20BC2.73619090--