Hi,

I am Sandeep Chibber from vReach Solutions= . We=20 are using postgres functions with java beans.=20 There is no problem with simple postgres functions which have Sim= ple=20 select statements WITHOUT where clause. But when we have sql statements in= =20 the postgres function in which WHERE clause is used ,then we have=20 problem 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. Postgres has some other way ,which we are no= t=20 able to locate.

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

The table structure is as follows

CREATE TABLE ICDN_EVENTREG ( USER_ID VARCHAR(30) NOT NULL= =20 , PASSWORD VARCHAR(16) , EMAIL VARCHAR(80) , = =20 DEPARTMENT VARCHAR(30) , FIRST_NAME VARCHAR(30) , LAST_NA= ME=20 VARCHAR(30) , DESIGNATION VARCHAR(30) , TYPE VARCHA= R(15)=20 , STATUS VARCHAR(15) DEFAULT 'Pending') ;

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

Thanking in anticipation

Sandeep=20 Chibber

------=_NextPart_000_001D_01C20BC2.73619090-- From achill@matrix.gatewaynet.com Tue Jun 4 07:39:11 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id F002C475BB4 for ; Tue, 4 Jun 2002 03:39:11 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 80628475BAB for ; Tue, 4 Jun 2002 03:39:05 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g547dHa12422; Tue, 4 Jun 2002 10:39:17 +0300 Date: Tue, 4 Jun 2002 10:39:17 +0300 (EEST) From: Achilleus Mantzios To: Sandeep Chibber Cc: pgsql-sql@postgresql.org Subject: Re: Postgres Function Problem In-Reply-To: <002001c20b94$5a1a65e0$1302a8c2@multicast.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200206/19 X-Sequence-Number: 7984 On Tue, 4 Jun 2002, Sandeep Chibber wrote: Why dont you just pass a string?? e.g. String wholeStatement = "select yourfunction("+YourArg1+","+YourArg2+")"; DataSource ds = ... get datasource ...; Connection con = ds.getConnection(); PreparedStatement st = con.prepareStatement(wholeStatement); ResultSet rs = st.executeQuery(); > 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 statements in the postgres function in which WHERE clause is used ,then we have problem of passing values to the WHERE clause.Like in the following function,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 locate. > > CREATE FUNCTION tester7 ( varchar, varchar ) returns setof ICDN_EVENTREG as ' SELECT * FROM ICDN_EVENTREG WHERE ICDN_EVENTREG.USER_ID = $1 AND ICDN_EVENTREG.PASSWORD = $2 ;' LANGUAGE SQL > > The table structure is as follows > > CREATE TABLE ICDN_EVENTREG ( USER_ID VARCHAR(30) NOT NULL , PASSWORD VARCHAR(16) , EMAIL VARCHAR(80) , DEPARTMENT VARCHAR(30) , FIRST_NAME VARCHAR(30) , LAST_NAME VARCHAR(30) , DESIGNATION VARCHAR(30) , TYPE VARCHAR(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 > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr