PL/pgSQL function not working in

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)

---

Re: PL/pgSQL function not working in

(more info)

[Wolf <co...@irresponsiblecybernetics.com>]

[Michael Zurke <mi...@zurke.info>]

put




hi eric,

probably out of date. just in case:
try this in your config

  <transactionManager type="JDBC" commitRequired="true">

mischa


Peng Wang wrote:
> try this
> <select id="getSilly" resultClass="int" parameterClass="string">
>             select silly(#value#) from dual
>  </select>
> 
> I think it can work.
> 
> On 5/8/05, Wolf <co...@irresponsiblecybernetics.com> wrote:
> 


>>Hi Clinton,
>>
>>  Thanks for the response.  I have tried this through raw JDBC :
>>          con = DriverManager.getConnection("jdbc:postgresql:wiki");
>>          stat = con.createStatement();
>>          stat.executeQuery("select silly('foo')");
>>  and that works as expected.
>>
>>  I am using the same jdbc driver on both occasions.
>>
>>  I did try select('foo') from dual but I got  ERROR: relation "dual"
>>does not exist. I think DUAL is an oracle only psuedo-table.
>>
>>-Wolf
>>
>>On May 7, 2005, at 7:39 PM, Clinton Begin wrote:
>>
>>
>>> Try select silly(text) from dual.
>>>
>>> Cheers,
>>> Clinton
>>>
>>
>>
> 

Re: PL/pgSQL function not working in (more info) Posted by Peng Wang <wp...@gmail.com>.

---

try this
<select id="getSilly" resultClass="int" parameterClass="string">
            select silly(#value#) from dual
 </select>

I think it can work.

On 5/8/05, Wolf <co...@irresponsiblecybernetics.com> wrote:
> Hi Clinton,
> 
>   Thanks for the response.  I have tried this through raw JDBC :
>           con = DriverManager.getConnection("jdbc:postgresql:wiki");
>           stat = con.createStatement();
>           stat.executeQuery("select silly('foo')");
>   and that works as expected.
> 
>   I am using the same jdbc driver on both occasions.
> 
>   I did try select('foo') from dual but I got  ERROR: relation "dual"
> does not exist. I think DUAL is an oracle only psuedo-table.
> 
> -Wolf
> 
> On May 7, 2005, at 7:39 PM, Clinton Begin wrote:
> 
> >
> >  Try select silly(text) from dual.
> >
> >  Cheers,
> >  Clinton
> >
> 
>

---

Re: PL/pgSQL function not working in

(more info)

[Wolf <co...@irresponsiblecybernetics.com>]

Hi Clinton,
    I tried it with a Prepared statement :

	Connection con = DriverManager.getConnection("jdbc:postgresql:wiki");
	PreparedStatement stat = con.prepareStatement("select silly('foo')");
	stat.execute();

    and it did work. I'm confused now.  I'm going through the SqlMaps 
code to take a peek at what might be happening, but can you point me at 
a good spot to look?

Thanks,
    Wolf

ps - I know the function 'silly' doesn't look very useful, but it is a 
stripped down version of a more complicated a function.  If I could get 
the simple code to work, I figure the more complicated should work.

On May 7, 2005, at 8:49 PM, Clinton Begin wrote:

>
>  OH!  I missed the "pg" part of your subject line.  :-)   I thought it 
> said PL/SQL.

    Not a problem. I didn't even know about PL/pgsql until a few days 
ago.  I'm definitely not a database guy (thus my interest in SqlMaps).

>
>  The reason your JDBC test worked is that you used a normal 
> statement.  Try that with a PreparedStatement.
>
>  iBATIS uses PreparedStatements exclusively.
>
>  Clinton

Re: PL/pgSQL function not working in (more info) Posted by Clinton Begin <cl...@gmail.com>.

---

OH! I missed the "pg" part of your subject line. :-) I thought it said 
PL/SQL.

The reason your JDBC test worked is that you used a normal statement. Try 
that with a PreparedStatement.

iBATIS uses PreparedStatements exclusively.

Clinton


On 5/7/05, Wolf <co...@irresponsiblecybernetics.com> wrote:
> 
> Hi Clinton,
> 
> Thanks for the response. I have tried this through raw JDBC :
> con = DriverManager.getConnection("jdbc:postgresql:wiki");
> stat = con.createStatement();
> stat.executeQuery("select silly('foo')");
> and that works as expected.
> 
> I am using the same jdbc driver on both occasions.
> 
> I did try select('foo') from dual but I got ERROR: relation "dual"
> does not exist. I think DUAL is an oracle only psuedo-table.
> 
> -Wolf
> 
> On May 7, 2005, at 7:39 PM, Clinton Begin wrote:
> 
> >
> > Try select silly(text) from dual.
> >
> > Cheers,
> > Clinton
> >
> 
>

---

Re: PL/pgSQL function not working in

(more info)

[Wolf <co...@irresponsiblecybernetics.com>]

Hi Clinton,

   Thanks for the response.  I have tried this through raw JDBC :
           con = DriverManager.getConnection("jdbc:postgresql:wiki");
           stat = con.createStatement();
           stat.executeQuery("select silly('foo')");
   and that works as expected.

   I am using the same jdbc driver on both occasions.

   I did try select('foo') from dual but I got  ERROR: relation "dual" 
does not exist. I think DUAL is an oracle only psuedo-table.

-Wolf


On May 7, 2005, at 7:39 PM, Clinton Begin wrote:

>
>  Try select silly(text) from dual.
>
>  Cheers,
>  Clinton
>

Re: PL/pgSQL function not working in Posted by Clinton Begin <cl...@gmail.com>.

---

Try select silly(text) from dual.

Cheers,
Clinton



On 5/5/05, Wolf <co...@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)
> 
>

---