ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: PL/pgSQL function not working in <select>
Date Sun, 08 May 2005 02:39:01 GMT
Try select silly(text) from dual.

Cheers,
Clinton



On 5/5/05, Wolf <coyo@irresponsiblecybernetics.com> wrote:
> 
> Hi folks,
> Thansk for SqlMap, it is making life easier. I am having a problem
> with
> PL/pgSQL functions called from a <select>. I can get around this by not
> using the function, but wanted to
> call this issue to attention (and see if it really is an issue).
> 
> I have a problem with a very simple PL/pgSQL function that causes a
> row
> to be inserted into a table. I am able to call it from within psql and
> have it work
> as expected. In SqlMaps, it returns the expected result, but does not
> perform the
> INSERT. I am using postgres 8.0 in the driver jar and the server.
> 
> Thanks in advance,
> Eric Wolf
> 
> __________________ The tables and function __________________
> CREATE TABLE wordids( id serial not null, word text );
> CREATE INDEX word_i on wordids(text);
> CREATE INDEX wordid_i on wordids(id);
> 
> CREATE OR REPLACE FUNCTION silly(text) RETURNS integer AS '
> DECLARE
> something ALIAS for $1;
> BEGIN
> INSERT INTO wordids(word) values( ''sadf'' );
> return 23;
> END;
> ' LANGUAGE plpgsql;
> 
> _____________ The sqlmapping ______
> I have tried two ways :
> <select id="getId" parameterClass="java.lang.String"
> resultClass="java.lang.Integer">
> select silly(#value#)
> </select>
> AND EVEN
> <procedure id="getId" parameterClass="java.lang.String"
> resultClass="java.lang.Integer">
> { call silly(#value#)}
> </procedure>
> 
> and they both have the exact same result.
> 
> __________________ The function works as expected inside psql
> __________________
> wiki=# delete from wordids;
> DELETE 4
> wiki=# select * from wordids;
> id | word
> ----+------
> (0 rows)
> 
> wiki=# select silly('boo');
> silly
> -------
> 23
> (1 row)
> 
> wiki=# select * from wordids;
> id | word
> ----+------
> 43 | sadf
> (1 row)
> -------------------
> 
> Now when I call the function with sqlMap, it returns 23 but does not do
> the INSERT.
> 
> java code :
> System.out.println("The result is
> '"+smc.queryForObject("getId",word)+"'");
> System.out.println("The result is
> '"+smc.queryForObject("getId",word)+"'");
> System.out.println("The result is
> '"+smc.queryForObject("getId",word)+"'");
> System.out.println("The result is
> '"+smc.queryForObject("getId",word)+"'");
> 
> ______________ output result _________________
> wiki=# delete from wordids;
> DELETE 1
> 
> .. run program ...
> 
> The result is '23'
> The result is '23'
> The result is '23'
> The result is '23'
> 
> wiki=# select * from wordids;
> id | word
> ----+------
> (0 rows)
> 
>

Mime
View raw message