How to send a message to the client

[jomarlla]

Hi everyone,
This is my first post in this list and first of all I want to express
my gratitude about this fantastic project. I want to say Hi to all
the people in this list too.

I do not know how to send a message to the client from a java store
procedure, for example: in PostgreSQL (in plpgsql) I can use RAISE
NOTICE 'message'. Does H2 have something similar?. I want to send this
message from a java store procedure that does not have a reference to
the object connection.

Thaxs,
Best,
Jose

[Thomas Mueller]

Hi,

H2 supports only Java procedures and functions. In Java, you could use:

throw new SQLException("no connection");

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[jomarlla]

Thanx for the answer Thomas, but I have already used Exceptions in H2
and as you said It worked.
The problem is: if I use just exceptions with one of my java
functions:

For example:
select st_Geomfromtext ('POINTT (10 20)');

This function throws an exception with this messages: "Unknown
geometry type: POINTT"

The problem is that for the user is really difficult to find the
message "Unknown geometry type: POINTT" among all the exception
messages, as you can see in the code below. Thats because H2 writes
his exception messages before mine. I though about sending a message
to the user without using exceptions (as postgres can do with raise
notice). If there is any other way I can write my exception message at
the top of the messages please let me know.

Thanx a lot,
Jose

select st_Geomfromtext ('POINTT (10 20)');
Exception calling user-defined function; SQL statement:
select st_Geomfromtext ('POINTT (10 20)') [90105-118] 90105/90105
(Help)
org.h2.jdbc.JdbcSQLException: Exception calling user-defined function;
SQL statement:
select st_Geomfromtext ('POINTT (10 20)') [90105-118]
at org.h2.message.Message.getSQLException(Message.java:105)
at org.h2.message.Message.convert(Message.java:270)
at org.h2.engine.FunctionAlias$JavaMethod.getValue
(FunctionAlias.java:306)
at org.h2.expression.JavaFunction.getValue(JavaFunction.java:38)
at org.h2.expression.JavaFunction.optimize(JavaFunction.java:59)
at org.h2.command.dml.Select.prepare(Select.java:716)
at org.h2.command.Parser.prepareCommand(Parser.java:235)
at org.h2.engine.Session.prepareLocal(Session.java:415)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:227)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:138)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.h2.engine.FunctionAlias$JavaMethod.getValue
(FunctionAlias.java:299)
... 8 more
Caused by: org.cartosig.jaspa.io.ParseException: Unknown geometry
type: POINTT
at org.cartosig.jaspa.io.WKTReader.readGeometryTaggedText
(WKTReader.java:648)
at org.cartosig.jaspa.io.WKTReader.read(WKTReader.java:284)
at org.cartosig.jaspa.io.WKTReader.read(WKTReader.java:248)
at org.cartosig.jaspa.Core.getJTSGeometryFromEWKTorWKT(Core.java:
301)
at org.cartosig.jaspa.JP.ST_GeomFromText(JP.java:243)
at org.cartosig.jaspa.SQL.ST_GeomFromText(SQL.java:393)
... 13 more

at org.h2.engine.SessionRemote.done(SessionRemote.java:520)
at org.h2.command.CommandRemote.prepare(CommandRemote.java:70)
at org.h2.command.CommandRemote.<init>(CommandRemote.java:48)
at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:
416)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1049)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:149)
at org.h2.server.web.WebThread.getResult(WebThread.java:1691)
at org.h2.server.web.WebThread.query(WebThread.java:1274)
at org.h2.server.web.WebThread.process(WebThread.java:430)
at org.h2.server.web.WebThread.processRequest(WebThread.java:183)
at org.h2.server.web.WebThread.process(WebThread.java:236)
at org.h2.server.web.WebThread.run(WebThread.java:193)

On Jan 27, 8:43 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> H2 supports only Java procedures and functions. In Java, you could use:
>
> throw new SQLException("no connection");
>
> Regards,
> Thomas
>

[lvr123]

You have to catch the error at GUI level and make it more user-
friendly, by using the sql error codes.
This way you could handle Internalization fore example.

[Dario Fassi]

Hi,
This approach does not help resolve the real need.
Many databases implement a command or function like RAISE to throw a custom (business) exception that is injected in normal database error handling.

In the same way that RULES, TRIGGERS and other constraint types  are used to impose business integrity rules to database tables and operations,
RAISE is the correct vehicle to express that business exceptional condition for GUI, Logging or what ever needed and to handle it properly.

For example in IBM DB2 , this is implemented as a function: 

	RAISE_ERROR( sqlstate, diagnostic-string)

I thinks this is a very clean a useful implementation because it can be user even in querys.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/fraisee.htm#fraisee 

The RAISE_ERROR function causes the statement that invokes the function to return an error with the specified SQLSTATE (along with SQLCODE -438) and error condition. The RAISE_ERROR function always returns NULL with an undefined data type.

sqlstate

An expression that returns a character string (CHAR or VARCHAR) of exactly 5 characters. The sqlstate value must follow these rules for application-defined SQLSTATEs:

• Each character must be from the set of digits ('0' through '9') or non-accented upper case letters ('A' through 'Z').
• The SQLSTATE class (first two characters) cannot be '00', '01', or '02' because these are not error classes.
• If the SQLSTATE class (first two characters) starts with the character '0' through '6' or 'A' through 'H', the subclass (last three characters) must start with a letter in the range 'I' through 'Z'.
• If the SQLSTATE class (first two characters) starts with the character '7', '8', '9', or 'I' though 'Z', the subclass (last three characters) can be any of '0' through '9' or 'A through 'Z'.

diagnostic-string

An expression that returns a character string with a data type of CHAR or VARCHAR and a length of up to 70 bytes. The string contains EBCDIC data that describes the error condition. If the string is longer than 70 bytes, it is truncated.

To use this function in a context where Rules for result data types do not apply, such as alone in a select list, you must use a cast specification to give a data type to the null value that is returned. The RAISE_ERROR function is most useful with CASE expressions.

I hope this help.
regards,
Dario



El 28/01/10 07:29, lvr123 escribió:

You have to catch the error at GUI level and make it more user-
friendly, by using the sql error codes.
This way you could handle Internalization fore example.

  

On Jan 27, 9:56 pm, jomarlla <jclla...@gmail.com> wrote:
  

Thanx for the answer Thomas, but I have already used Exceptions in H2
and as you said It worked.
The problem is: if I use just exceptions with one of my java
functions:

For example:
select st_Geomfromtext ('POINTT (10 20)');

This function throws an exception with this messages: "Unknown
geometry type: POINTT"

The problem is that for the user is really difficult to find the
message "Unknown geometry type: POINTT" among all the exception
messages, as you can see in the code below. Thats because H2 writes
his exception messages before mine. I though about sending a message
to the user without using exceptions (as postgres can do with raise
notice). If there is any other way I can write my exception message at
the top of the messages please let me know.

Thanx a lot,
Jose

    

[Thomas Mueller]

Hi,

If you throw a SQLException in the function, then it will not be
converted. Example:

drop alias test;
create alias test as 'int test() throws SQLException { throw new
SQLException("Error Parsing..."); }';
call test();

This will throw the exception "Error Parsing...". If you throw any
other type of exception in the function, it will be converted to the
generic "Exception calling user-defined function".

I will document that.

> This approach does not help resolve the real need.

I think it does. In my view RAISE is the same as "throw new SQLException".

Regards,
Thomas

[Dario Fassi]

El 29/01/10 16:26, Thomas Mueller escribió:

If you throw a SQLException in the function, then it will not be
converted. Example:

drop alias test;
create alias test as 'int test() throws SQLException { throw new
SQLException("Error Parsing..."); }';
call test();

This will throw the exception "Error Parsing...". If you throw any
other type of exception in the function, it will be converted to the
generic "Exception calling user-defined function".

I will document that.
  

One case I've seen time ago  (don't remember h2 version) ends with a SQLException with different text.
In an SELECT query with UDF and other in an INSERT over a table with a trigger that throw a exception with explanatory description.
Finally my exception get lost or converted and application receive another exception with it's own description related to involved table.

May be in some case when rollback with multiple exception they are not properly chained ?

I will try to reproduce that with actual version.

But  no matter that,  this seems a partial solution compared with RAISE , because with an exception we can't set and SQLSTATE that can be used programmatically at application level.

 

This approach does not help resolve the real need.
    

I think it does. In my view RAISE is the same as "throw new SQLException".
  

Thomas I don't reply to your answer.
When I say that not resolve the real need , I anwser to to Ivr123 suggestion to deal / convert the exception at GUI level.

El 28/01/10 07:29, lvr123 escribió:

You have to catch the error at GUI level and make it more user-friendly, by using the sql error codes.
  

Regard,
Dario

[Thomas Mueller]

Hi,

> But  no matter that,  this seems a partial solution compared with RAISE ,
> because with an exception we can't set and SQLSTATE that can be used
> programmatically at application level.

You can. See http://java.sun.com/javase/6/docs/api/java/sql/SQLException.html#SQLException(java.lang.String,%20java.lang.String,%20int,%20java.lang.Throwable)

Regards,
Thomas

[Dario Fassi]

El 31/01/10 05:46, Thomas Mueller escribió:

Thomas, you're all right. 
I feel very stupid since I read my last post - sorry.