Thread: SQL syntax error handling within SPI functions in C
Hi,
I’ve written a set of functions in C that will ultimately be called from an enterprise java bean. I expected that when calling SPI_exec(sql, 0) it would return an error code if “sql” contained a syntax error. At that point I would be able to return my own (more meaningful) error message based on the error thrown.
Instead an SQLException is thrown, and any error detection and processing code in the SPI function is bypassed. Is this the only error reporting model, or can I trap SQL errors within my functions? I’m using postgreSQL version 7.4.2 on Red Hat Linux version 9 and can’t upgrade at the moment.
Thanks in advance for your help,
Stuart Morse
Optimedia Solutions
1247 Knockan Drive
Victoria, BC, V8Z 7B8
(250) 658-8104 ph
(250) 658-8146 fax
stuart@optimediasolutions.ca
http://www.optimediasolutions.ca/
On Thu, Oct 12, 2006 at 02:29:27PM -0700, Stuart Morse wrote: > Hi, > > I've written a set of functions in C that will ultimately be called from an > enterprise java bean. I expected that when calling SPI_exec(sql, 0) it would > return an error code if "sql" contained a syntax error. At that point I > would be able to return my own (more meaningful) error message based on the > error thrown. Find the section in the docs on exception handling. The rule is basically: if the function gets an error it won't return. For this reason you never have to check if palloc() returns NULL. It really can't happen. There are try/catch blocks you can install to catch errors. pl/pgsql does this for example. Note it is slightly expensive, so you're usually better off avoiding errors you know you're going to ignore anyway. An error will abort the current transaction, no changing that, you use subtransactions to isolate them... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.