Well, I've just tried rebuilding my database to the new format and
running my code under 1.2.136. At first it all went smoothly, then one
of my data screens gave me:
Swing Program Exception Report
Sent at 10:54 on 26-May-10
Application: C-Sales Data (1.5)
Activity: Going to new data panel
Stack trace:
org.h2.jdbc.JdbcSQLException: Data conversion error converting
"2010-05-24"; SQL statement:
select code, open_date,
(select min(p.id) from invoice_headers ih, periods p where
ih.member_code = code and p.week_number = ih.week_number
and
p.year_number = ih.year_number and p.week_end_date >=
open_date and
ih.supplier_code in ('NISA','NISAC','NICAF') AND
ih.total_amount <> 0)
as first_order,
( select
sum(v.net_amount_excl_nonsc) from sc_member_summaries_mv v,
periods p
where v.mem_id = m.id
and (f.first_order is null or f.first_order <= p.id)
and v.per_id = p.id and
p.week_start_date >= open_date and
p.week_start_date >= (? - 42) and
p.week_start_date < ? and
p.week_end_date < sysdate
and not exists(select 1 from sc_member_hold_histories where mem_id =
m.id and
on_hold_start < p.week_end_date and
(on_hold_end is null or on_hold_end > p.week_end_date))
) as wk13,
(select count(id) from periods p where
(f.first_order is null or f.first_order <= p.id)
and week_start_date >= open_date and
week_start_date >= (? - 42) and
week_start_date < ?
and week_end_date < sysdate
and not exists(select 1 from sc_member_hold_histories where mem_id =
m.id and
on_hold_start < p.week_end_date and
(on_hold_end is null or on_hold_end > p.week_end_date)) ) as
periods,
scorecard_area, name, shortname, region_code,sh_id
from members m left outer join
sc_first_orders_mv f on f.member_code = m.code
where
open_date between ? and ?
[90021-136]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.value.Value.convertTo(Value.java:785)
at org.h2.expression.Operation.getValue(Operation.java:95)
at org.h2.expression.Comparison.getValue(Comparison.java:191)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:83)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:90)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:90)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:90)
at org.h2.expression.Expression.getBooleanValue(Expression.java:185)
at org.h2.command.dml.Select.queryGroup(Select.java:309)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:574)
at org.h2.command.dml.Query.query(Query.java:233)
at org.h2.expression.Subquery.getValue(Subquery.java:34)
at org.h2.expression.Alias.getValue(Alias.java:35)
at org.h2.command.dml.Select.queryFlat(Select.java:498)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:579)
at org.h2.command.dml.Query.query(Query.java:233)
at org.h2.command.CommandContainer.query(CommandContainer.java:80)
at org.h2.command.Command.executeQuery(Command.java:132)
at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:
96)
at scorecard.data.Recruitment.<init>(Recruitment.java:375)
at scorecard.data.Recruitment.<init>(Recruitment.java:36)
at scorecard.data.Recruitment$1.load(Recruitment.java:610)
at scorecard.data.Recruitment$1.load(Recruitment.java:607)
at scorecard.data.SoftCache.get(SoftCache.java:128)
at scorecard.data.Recruitment.getInstance(Recruitment.java:619)
at
scorecard.windows.tabpanes.RecruitmentPane.switchTo(RecruitmentPane.java:
435)
at scorecard.windows.DataTabPanel
$PaneSwitch2.switchTab(DataTabPanel.java:86)
at scorecard.windows.DataTabPanel.switchTo(DataTabPanel.java:449)
at scorecard.windows.CompositeView$PaneLoader.run(CompositeView.java:
125)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.NumberFormatException: For input string:
"2010-05-24"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:
48)
at java.lang.Integer.parseInt(Integer.java:458)
at java.lang.Integer.valueOf(Integer.java:528)
at java.lang.Integer.decode(Integer.java:958)
at org.h2.value.Value.convertTo(Value.java:742)
... 29 more
>From the trace it looks like
week_start_date >= (? - 42)
is probably the problem (the ? is set with a setDate).
This is a trick I've done in numerous places, and it worked perfectly
in the old versions (as it does on ORACLE). Since I use setDate the
driver should do whatever is needed to
tell the SQL interpreter this is a date value, but it looks like the
parser is just seeing a literal string.
So now I can't go forward, can't go back.
On May 24, 8:31 pm, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> I suggest to upgrade to version 1.2.135
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
