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)
>
>
|