Re: JDBC inserts on views using rules - Mailing list pgsql-jdbc

Stuart,

OK, thanks for the explaination.  All you need to do is use
executeQuery() instead of executeUpdate() and ignore the ResultSet if
you don't want the return value.

thanks,
--Barry


Stuart Robinson wrote:

> I didn't give the actual code because it's a bit involved and the details
> probably don't matter too much. I'll just give a simplified example that
> illustrates my point.
>
> Basically, I've got a view that combines two tables.
>
> CREATE VIEW ex_view AS
> SELECT u.url, i.name
> FROM url u, url_info i
> WHERE u.id = i.id_url
>
> I also have a rule like the following to intercept inserts to the view:
>
> CREATE RULE ex_view_insert AS
> ON INSERT TO ex_view
> DO INSTEAD
>   select ex_function(new.url, new.name)
>
> The function inserts into the tables that make up the view by doing
> something like this (the logic is more compicated, hence the necessity
> for a function, but this will give you an idea of what I'm doing):
>
> CREATE FUNCTION ex_function(TEXT, TEXT)
> RETURNS INTEGER
> AS '
>   DECLARE
>
>   BEGIN
>   INSERT INTO url (url) VALUES ($1);
>   INSERT INTO url_info (name) VALUES ($2);
>   RETURN 1;
>   END;
> '
> LANGUAGE 'plpgsql';
>
> This works fine when you run it manually in psql, but when it's run by the
> JDBC, you get an error, because it returns a value (1) (since I
> called the function with a select). However, the application doesn't
> expect a return value, since it
> thought it was doing an insert and  used the executeUpdate method.
>
> So, is there some way of supressing the return value so that the
> rule-redirected insert will succeed? Could I call the function w/
> something other than select? I hope the problem is clear and that there's
> a straightforward solution, but if functions necessarily return values
> unless they're called as triggers, I might be out of luck.
>
> Thanks.
>
> -Stuart
>
> On Fri, 9 Nov 2001, Barry Lind wrote:
>
>
>>Stuart,
>>
>>What is the sql statement you are issuing that is causing this error.
>>Without seeing the sql statement I am having a difficult time
>>understanding exactly what you are trying to do.
>>
>>thanks,
>>--Barry
>>
>>
>>Stuart Robinson wrote:
>>
>>
>>>I sent this to the general mailing list, but I thought it might be
>>>appropriate for this forum, since it does relate to the JDBC.
>>>
>>>Regards,
>>>Stuart Robinson
>>>
>>>---------- Forwarded message ----------
>>>Date: Thu, 8 Nov 2001 23:51:33 -0800 (PST)
>>>From: Stuart Robinson <stuart@zapata.org>
>>>To: pgsql-general@postgresql.org
>>>Subject: [GENERAL] inserts on views using rules
>>>
>>>I've got a view that combines a couple of tables. The view is meant to
>>>simplify interactions with a Java application, so that selects, inserts,
>>>and updates can be made on the view as if it were a real table. So, I
>>>figured that rules would be the way to go. For inserts, I wrote a
>>>do-instead rule for the view which in turn calls a function. The function
>>>is meant to take the values from the insert statement and stick them into
>>>the appropriate tables. However, functions appear to always return a value
>>>(except as triggers), which confuses the JDBC. So, when I do an insert on
>>>the view, the function is called and returns a value, causing the
>>>following error:
>>>
>>>A result was returned by the statement, when none was expected.
>>>        at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:80)
>>>        at
>>>org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122)
>>>
>>>I take it this is the expected JDBC behavior and not a bug. If so, what
>>>are my options given that functions always return values and triggers
>>>operate before or after inserts, but not instead of them? Is there some
>>>way of calling functions so that they don't return a value? (If this
>>>posting is more appropriate for another mailing list, please let me know.)
>>>Thanks in advance.
>>>
>>>-Stuart Robinson
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>>subscribe-nomail command to majordomo@postgresql.org so that your
>>>message can get through to the mailing list cleanly
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>
>>>
>>>
>>
>
>