ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wolf <c...@irresponsiblecybernetics.com>
Subject PL/pgSQL function not working in <select>
Date Thu, 05 May 2005 17:30:00 GMT
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