User-Defined Function Values

[Joe Parsons]

I am using H2 in embedded mode to unit test some DAO classes. I found
one of the DAOs calls a user-defined function in the PostgreSQL
database. I tried to mock the function by writing a java method under
an alias in H2. I used this simple class for my test:

public class UserDefinedDbFunctions {

private static int id = 0;

public final static String getNextIdAndUpdate() {
id++;
return ("" + id);
}

}

Then in my unit test, I created the alias like so:

postgresqlStatement.executeUpdate("CREATE ALIAS IF NOT EXISTS
getNextIdAndUpdate FOR \"UserDefinedDbFunctions.getNextIdAndUpdate
\";");

After running the unit test, it threw a SQLException. It said it
couldn't find the column "getNextIdAndUpdate".

After taking a look inside the DAO, I found that the value was being
retrieved by column name:

String query = "SELECT getNextIdAndUpdate()";
// snip
id = resultSet.getString("getNextIdAndUpdate");

I then added some debug code to look at the ResultSet's metadata and
found that the name of the result column from my function is
"PUBLIC.GETNEXTIDANDUPDATE". The qualified name seems to be breaking
the code.

Without changing any of the original DAO code, is there a way that I
can change the name of the result column to not use its fully-
qualified name? Possibly another workaround?

[Joe Parsons]

Correction to my above column name. The column name includes the
function parenthesis:

PUBLIC.GETNEXTIDANDUPDATE()

[Thomas Mueller]

Hi,

Sorry, but I don't understand the problem. Is there some error message and stack trace? If yes could you post it? Do you have a simple reproducible test case?

Regards,

Thomas

[Joe Parsons]

Thomas,

There is no error message. I thought I worded the question quite
well. I will summarize as best I can.

Java code that I do not wish to change is calling a stored function in
H2 in PostgreSQL mode. Java is pulling the value by name instead of
by index.

My question is, is there any way to change the name of the column
returned by a function in H2?


Referencing the column like this:

id = resultSet.getString("getNextIdAndUpdate");

Is not going to work when the column is named this:

PUBLIC.GETNEXTIDANDUPDATE()

[Thomas Mueller]

Hi,

> Referencing the column like this:
>
>      id = resultSet.getString("getNextIdAndUpdate");
>
> Is not going to work when the column is named this:
>
>     PUBLIC.GETNEXTIDANDUPDATE()

Ah, I guess the main problem is the "()" at the end. I didn't see that
first, sorry.

Could you change the query to:

SELECT getNextIdAndUpdate() AS getNextIdAndUpdate

Regards,
Thomas

[Joe Parsons]

> Ah, I guess the main problem is the "()" at the end. I didn't see that
> first, sorry.
>
> Could you change the query to:
>
> SELECT getNextIdAndUpdate() AS getNextIdAndUpdate

I certainly could, but my goal was to change nothing in the code to
accommodate my unit testing. Referencing the column by name works
fine running against a standard PostgreSQL instance, which requires
neither the parenthesis nor the schema name. Shouldn't H2's
PostgreSQL compatibility mode support this behavior?

- Joe

[Thomas Mueller]

Hi,

I'm sorry but H2 can't be 100% compatible with every other database. If I would change the behavior, it would be no longer compatible with MySQL in this area.

Could you use "SELECT getNextIdAndUpdate() AS getNextIdAndUpdate" or resultSet.getString(1) instead?

Regards,
Thomas

[Joe Parsons]

I was under the impression that the purpose of a "compatibility mode" was to make the product more compatible and to better support the syntax of a specific RDBMS.  Why would adding this change under the PostgreSQL compatibility mode affect MySQL?

-Joe

[Thomas Mueller]

Hi,

Only the most popular features of other database can be implemented in a "compatibility mode" item. In this case, I don't think it is a popular nor important compatibility item, sorry.

Regards,

Thomas

On Wednesday, March 7, 2012, Joe Parsons wrote:

I was under the impression that the purpose of a "compatibility mode" was to make the product more compatible and to better support the syntax of a specific RDBMS.  Why would adding this change under the PostgreSQL compatibility mode affect MySQL?

-Joe

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/IFVB7JPU99cJ.
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.