<x-tad-bigger>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)
</x-tad-bigger>
