Howto implement isNull(value, defaultValue) Sybase function in H2?

[joseaio]

public static Object isNull(Object value, Object defaultValue) {
if (value == null) {
return defaultValue;
} else {
return value;
}
}

throws org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-
hex character: "0.00";

note: I use this function with with DECIMAL (BigDecimal) types

[joseaio]

If I use BigDecimals works fine, but is not valid for other types
(String, Date, Boolean, Integer, ...)

public static BigDecimal isNull(BigDecimal value, BigDecimal

defaultValue) {
if (value == null) {
return defaultValue;
} else {
return value;
}
}

Any solution?

[Thomas Mueller]

Hi,

User defined functions that support *all* (or multiple) data types are
not supported. Either ISNULL needs to be implemented as a "core"
function within H2, or you use IFNULL(..), NVL(..), COALESCE(...), or
CASE / CASEWHEN.

Does Sybase support IFNULL or CASE?

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.
>
>

[joseaio]

Thank you Thomas,

Sybase support CASE, but if use calculated column (sum, avg, or any
other system or user function...) needed multiple evaluations of
function

case
when (sum(a1) == null) then 0
else sum(a1)
end

note: multiple evaluations of 'sum' when sum(..) != null

and isNull() is short expresion...

Any alternative as use Comparable types?

public static Comparable isNull(Comparable value, Comparable

defaultValue) {
if (value == null) {
return defaultValue;
} else {
return value;
}
}

On Dec 28, 6:46 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> User defined functions that support *all* (or multiple) data types are
> not supported. Either ISNULL needs to be implemented as a "core"
> function within H2, or you use IFNULL(..), NVL(..), COALESCE(...), or
> CASE / CASEWHEN.
>
> Does Sybase support IFNULL or CASE?
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

I understand. Is it possible for you to use IFNULL? According to this
document, Sybase Adaptive Server IQ supports it:

http://manuals.sybase.com/onlinebooks/group-iq/iqg1250e/iqref/@Generic__BookTextView/34146;pt=34859

> note: multiple evaluations of 'sum' when sum(..) != null

In my experience, calculating the sum twice isn't a big overhead. The
bottleneck is usually reading from disk. In theory, the duplicate
'sum' could be optimized in the database engine (H2 doesn't do that
however).

Regards,
Thomas

> Any alternative as use Comparable types?
>
>    public static Comparable isNull(Comparable value, Comparable defaultValue) {

No. Currently you would need to use a function with the correct data
type. You could overload it, that means one function for each required
data type, but that's a lot of work.

Regards,
Thomas

[joseaio]

On Jan 3, 10:41 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> I understand. Is it possible for you to use IFNULL? According to this
> document, Sybase Adaptive Server IQ supports it:
>

> http://manuals.sybase.com/onlinebooks/group-iq/iqg1250e/iqref/@Generi...
>

If you see at the end of your link,

Sybase: Not supported by Adaptive Server Enterprise.

I tested with Sybase ASE (Adaptive Server Enterprise) 15.0 and 15.5
versions and this function not exists

> >  note: multiple evaluations of 'sum' when sum(..) != null
>
> In my experience, calculating the sum twice isn't a big overhead. The
> bottleneck is usually reading from disk. In theory, the duplicate
> 'sum' could be optimized in the database engine (H2 doesn't do that
> however).
>
> Regards,
> Thomas
>
> > Any alternative as use Comparable types?
>
> >    public static Comparable isNull(Comparable value, Comparable defaultValue) {
>
> No. Currently you would need to use a function with the correct data
> type. You could overload it, that means one function for each required
> data type, but that's a lot of work.
>

I overloaded my isNull() function with BigDecimal and Integer but H2
throw next Exception: only use this function with BigDecimal and
Integers.

Caused by: org.h2.jdbc.JdbcSQLException: Matching Java methods must
have different parameter counts: "public static java.math.BigDecimal
H2Functions.isNull(java.math.BigDecimal,java.math.BigDecimal)" and
"public static java.lang.Integer
H2Functions.isNull(java.lang.Integer,java.lang.Integer)"; SQL
statement:
create alias isNull for "H2Functions.isNull" [90073-148]

why?

> Regards,
> Thomas


I found another "incompatible" function: convert()

In h2 the function signature is convert(value, type)
But sybase the function signature is convert(type, value)

Any solution? I try to overload the convert function but h2 throws and
exception when trying to overload system function


Regards

[Thomas Mueller]

Hi,

> Caused by: org.h2.jdbc.JdbcSQLException: Matching Java methods must
> have different parameter counts:

> why?

H2 currently doesn't support overloading methods with the same name
and same parameter count. Supporting this would be possible, but quite
complicated. Do you know if other databases support it?

> I found another "incompatible" function: convert()

> Any solution?

No, I'm sorry. Probably the best solution is to add a Sybase
compatibility mode where parameters are swapped. I'm currently not
working on this, but patches are welcome of course!

Regards,
Thomas

[joseaio]

On Jan 16, 1:05 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > Caused by: org.h2.jdbc.JdbcSQLException: Matching Java methods must
> > have different parameter counts:
> > why?
>
> H2 currently doesn't support overloading methods with the same name
> and same parameter count. Supporting this would be possible, but quite
> complicated. Do you know if other databases support it?

I think, that only Sybase and SQL Server allows isNull function

>
> > I found another "incompatible" function: convert()
> > Any solution?
>
> No, I'm sorry. Probably the best solution is to add a Sybase
> compatibility mode where parameters are swapped. I'm currently not
> working on this, but patches are welcome of course!
>

Any instruction or example code to start is welcome...

> Regards,
> Thomas

Thank you, Thomas

[Thomas Mueller]

Hi,

> Any instruction or example code to start is welcome...

I think it's not hard to support those features. If you want to add
Sybase support, you first need to get familiar with the source code.
See http://www.h2database.com/html/build.html

Compatibility modes are here: Mode.java - just add one for Sybase.

"CONVERT" is here: Parser.java (search for "Function.CONVERT") and
Function.java (search for "case CONVERT:").

Regards,
Thomas