Debugging Tip: Wrong number of parameters

[Eyston]

Hello,

I need a tip on finding a problem in my code. I am having queries
fail with IBM error 07002: Wrong number of parameters. When I do
show_sql and look at the logs, the number of parameters is correct.
In fact I have 15 of the same type of entity being updated and the
first one works and the next one fails... so NHibernate is generating
a correct UPDATE for the first entity (or I guess my database is
accepting the first... could be db bug?).

If I comment out one of my properties the update is working fine (I
add update=false):

<property name="ExtendedSalesDollars" column="SXUUA1" update="false"/>

If I remove update="false" the first UPDATE works on a flush and the
second one fails.

Basically... how do I see exactly what query is being sent to the
database or what the parameter array is like?

Thanks.

ps. Here is the log output (second one fails, P17 is the property I
can set update=false to make it work...)

2009-07-02 16:02:58,288 DEBUG UPDATE AMFLIBT.COPSHX01 SET SXTYPE = ?,
SXNUMB = ?, SXHDTY = ?, SXKTSQ = ?, SXORDN = ?, SXLISQ = ?, SXRLNO
= ?, SXCRID = ?, SXUU40 = ?, SXSNO1 = ?, SXUUD2 = ?, SXUUD1 = ?,
SXWKD1 = ?, SXWKSA = ?, SXWKSB = ?, SXUUSA = ?, SXWKQ1 = ?, SXUUA1
= ?, SXCRDT = ?, SXCRTM = ?, SXCRUS = ?, SXCRPG = ?, SXCHDT = ?,
SXCHTM = ?, SXCHUS = ?, SXCHPG = ?, SXTRK1 = ?, SXTRK2 = ?, SXTRK3
= ?, SXTRK4 = ?, SXTRK5 = ? WHERE SXGPID = ? AND SXNOSQ = ?; p0 =
'04', p1 = '86286', p2 = '1', p3 = '0', p4 = '1029270', p5 = '100', p6
= '1', p7 = 'FED X', p8 = '', p9 = '', p10 = '1090702', p11 =
'1090619', p12 = '1090626', p13 = '0', p14 = '0', p15 = '0', p16 =
'1029000', p17 = '121600.0000000', p18 = '1090702', p19 = '144705',
p20 = 'EYSTON', p21 = 'WEBSHIP', p22 = '1090702', p23 = '160256', p24
= 'EYSTON', p25 = 'WEBSHIP', p26 = '123456789', p27 = '', p28 = '',
p29 = '', p30 = '', p31 = '86286', p32 = '1'

2009-07-02 16:02:58,382 DEBUG UPDATE AMFLIBT.COPSHX01 SET SXTYPE = ?,
SXNUMB = ?, SXHDTY = ?, SXKTSQ = ?, SXORDN = ?, SXLISQ = ?, SXRLNO
= ?, SXCRID = ?, SXUU40 = ?, SXSNO1 = ?, SXUUD2 = ?, SXUUD1 = ?,
SXWKD1 = ?, SXWKSA = ?, SXWKSB = ?, SXUUSA = ?, SXWKQ1 = ?, SXUUA1
= ?, SXCRDT = ?, SXCRTM = ?, SXCRUS = ?, SXCRPG = ?, SXCHDT = ?,
SXCHTM = ?, SXCHUS = ?, SXCHPG = ?, SXTRK1 = ?, SXTRK2 = ?, SXTRK3
= ?, SXTRK4 = ?, SXTRK5 = ? WHERE SXGPID = ? AND SXNOSQ = ?; p0 =
'04', p1 = '86286', p2 = '1', p3 = '0', p4 = '1029270', p5 = '200', p6
= '1', p7 = 'FED X', p8 = '', p9 = '', p10 = '1090702', p11 =
'1090619', p12 = '1090626', p13 = '0', p14 = '0', p15 = '0', p16 =
'1029000', p17 = '30895.0000000', p18 = '1090702', p19 = '144705', p20
= 'EYSTON', p21 = 'WEBSHIP', p22 = '1090702', p23 = '160256', p24 =
'EYSTON', p25 = 'WEBSHIP', p26 = '123456789', p27 = '', p28 = '', p29
= '', p30 = '', p31 = '86286', p32 = '2'

[Eyston]

Hmm... if I set the value to 0.0 it updates correctly.

I think the error message is just mistaken and I need to specify
precision on this property?

[Eyston]

So the table is 10,2 precision.

If I do:

<property name="ExtendedSalesDollars">
<column name="SXUUA1" sql-type="decimal(10,2)"/>
</property>

the parameter still shows more precision and it fails:

p17 = '121600.0000000' // first update -- succeed
p17 = '30895.0000000' // second update -- fail

I think if I can get it to try '30895.00' and '121600.00' it should
work?

[Fabio Maulo]

<property name="ExtendedSalesDollars" precision="10" scale="2"/>
or

<property name="ExtendedSalesDollars" type="decimal(10,2)"/>

or

<property name="ExtendedSalesDollars" type="Currency"/>

or for mssql

<property name="ExtendedSalesDollars" type="Currency(10,2)"/>

2009/7/2 Eyston <eys...@gmail.com>

--
Fabio Maulo

[Eyston]

I tried all of those (I moved to 2.1 vs. 2.0.1) and the query still
shows up in the logs (using sql appender) as:

30895.0000000

Instead of 30895.00

The thing is I'm not sure if that is what is being actually sent as a
parameter to the database or not, I just know that is what is showing
up in the logs.

I'm going to try and write a customer UserType to control the
formatting from decimal to a string. It is probably the wrong idea,
but I feel comfortable that I know what to do here.

It is a frustrating bug from my database driver I think. The fact
that INSERTS work and the first UPDATE works before giving back an
ambiguous error on the second UPDATE is annoying...

Thanks for your help.

[Fabio Maulo]

prepare_sql=true

2009/7/6 Eyston <eys...@gmail.com>

--
Fabio Maulo

[Eyston]

No change :(

I'm not sure if what the line does so I just added it to my
configuration and tried again with those property options you
specified. If it was a hint to find more detail I did not look
further.

<property name="ExtendedSalesDollars" column="SXUUA1"

precision="10" scale="2" />

cfg.SetProperty("prepare_sql", "true");

cfg.SetProperty("dialect", "NHibernate.Dialect.DB2400Dialect");

NHibernate: 2.1.0.2002

When I made a IUserType with:

public void NullSafeSet(System.Data.IDbCommand cmd, object
value, int index)
{
decimal d = value == null ? 0.0M : ((decimal)value);

string s = d.ToString("#.00");

NHibernateUtil.String.NullSafeSet(cmd, s, index);
}

It works okay (it is a pretty lazy implementation and not
configurable, but works in these cases to get a fix out quick and lets
me spend time learning the true problem).

Log shows (using log4net NHibernate.SQL logger) :

p17 = 1612.0000000 // prepare_sql = true, precision = "10", scale =
"2"

p17 = '1612.00' // custom IUserType ... db doesn't complain about
quotes when saving to int/decimal

I'm not sure where the bug lays. I am more than willing to do more
tests, but am afraid of using your time. Do you think it might be an
NH bug?

Thanks again.

[Fabio Maulo]

NH version ?

2009/7/6 Eyston <eys...@gmail.com>

--
Fabio Maulo

[Fabio Maulo]

ahhh you are using DB2400Dialect well probably there is something missed in the DB2400Driver where the prepare_sql should be managed.

If you have time take a look to SqlClientDriver and implement a solution for DB2400Driver. When the work is done and if you want share the solution create a new JIRA ticket with the patch for DB2400Driver.cs .

Thanks.

2009/7/6 Fabio Maulo <fabio...@gmail.com>

--
Fabio Maulo

[Eyston]

NH 2.1.0.2002

says runtime: 2.0.50727

download was 2.1.0.Beta2

My code initially was all 2.0.1 but have made a branch for 2.1.0 (only
change was to an IParameterizedType and adding the proxyfactory).

It happened late in code and only happens in the db interaction so
testing is kind of slow. If you think it might be NH I can create a
smaller program just for testing but the bug might be specific to the
AS400 dialect? (I don't know, I haven't tried switching to sql-lite or
something since I can only reproduce through interactive testing as of
right now, but can try that out if it would be helpful).

[Eyston]

I can take a look. I do not know NHibernate code base well, but with
your point in right direction I am happy to check it out.

On Jul 6, 11:52 am, Fabio Maulo <fabioma...@gmail.com> wrote:
> ahhh you are using DB2400Dialect well probably there is something missed in

> the DB2400Driver where the prepare_sql should be managed.If you have time

> take a look to SqlClientDriver and implement a solution for DB2400Driver.
> When the work is done and if you want share the solution create a new JIRA
> ticket with the patch for DB2400Driver.cs .
> Thanks.
>

> 2009/7/6 Fabio Maulo <fabioma...@gmail.com>

[Fabio Maulo]

2009/7/6 Eyston <eys...@gmail.com>

I can take a look.  I do not know NHibernate code base well, but with
your point in right direction I am happy to check it out.

If you need an help with it you can ping me in my private mail.

--
Fabio Maulo