Обсуждение: Postgres Function Problem
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
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
simplepostgres 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
thefollowing 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) ,
DEPARTMENTVARCHAR(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
thisfunction 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
Hi i wrote a litte function that converts an int4 to the equivalent _int4 1x1 array. It is supposed to be used with Oleg's and Teodor's contrib/intarray package. Now i want to ask if someone (especially Oleg and Teodor) could lead to a way that this function is used for automatic casts. The code is pretty simple, but if any one wants i can send it. -- 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
On Tue, 4 Jun 2002, Achilleus Mantzios wrote: > > Hi i wrote a litte function that converts an int4 to the equivalent > _int4 1x1 array. > > It is supposed to be used with Oleg's and Teodor's contrib/intarray > package. > > Now i want to ask if someone (especially Oleg and Teodor) could lead > to a way that this function is used for automatic casts. > > The code is pretty simple, but if any one wants i can send it. Why not send the code to us :-) > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83