| Author |
Message
|
| Aldrine |
 Posted: Thu Feb 23, 2012 1:02 am Post subject: ESQL Behaviour with Database |
 |
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
Hi Friends,
I have a Database Column with type as "NUMBER(*,0)"; which implies it would accept any data without any decimal value.
While executing the following SQL command "UPDATE tb_oka03100 SET official_employmentpercentage=96.55 WHERE wnm_nr=00106993;"from: many tools(or node) like SQL developer, ISQL, and JCN. The value of "97" is getting updated into the Database.
However while trying to achive the same via ESQL, it causes an exception:
"[IBM][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 12".
When I manually handle the changes done by Database with ESQL to adapt the value; it works perfectly. Hence my question is there is any reason that we should adapt the value while using ESQL alone?
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
 |
| vishnurajnr |
| Posted: Thu Feb 23, 2012 1:50 am Post subject: |
|
|
Centurion
Joined: 08 Aug 2011
Posts: 134
Location: Trivandrum
|
Use PASSTHRU to write ESQL statements that:
Bypass the WebSphere Business Integration Message Broker Parser
Go straight to the configured backend database
Execute a coded statement |
|
| Back to top |
|
|
| Aldrine |
| Posted: Thu Feb 23, 2012 1:54 am Post subject: |
|
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
| vishnurajnr wrote: |
Use PASSTHRU to write ESQL statements that:
Bypass the WebSphere Business Integration Message Broker Parser
Go straight to the configured backend database
Execute a coded statement |
Hi vishnurajnr,
I have already tried both using UPDATE/INSERT statement and PASSTHRU; Both results in the same Exception. 
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Feb 23, 2012 2:13 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| Aldrine wrote: |
| I have already tried both using UPDATE/INSERT statement and PASSTHRU; Both results in the same Exception. |
Develop your SQL statements with a DB-Tool (TOAD, SQLDeveloper) and put the statement into ESQL (when it works) as a string. Also check this post here.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Feb 23, 2012 5:06 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I seem to recall have a problem like this once.
I think I solved it by casting the value as a FLOAT and passing it to a stored proc to do the insert. This was some time ago so I might be wrong.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Aldrine |
| Posted: Thu Feb 23, 2012 5:28 am Post subject: |
|
|
Novice
Joined: 25 Jul 2011
Posts: 22
Location: India
|
@mqsiuser : As you can see in my first post itself I have tested with PASSTHRU too; but resulted in the same Exception.
@smdavies99: Indeed its the developer should take care of type castings in all programming languages and I never complain about it.
But its about the precision and scale, which is automatically taken care by the DB/Driver for eGate, WMB JDBC and other tools but why not for ESQL?
_________________
--
Aldrine Einsteen |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Feb 23, 2012 5:48 am Post subject: Re: ESQL Behaviour with Database |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
I'd say that is not a bug, its a feature (of ESQL) (to be stricter, than your tools).
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| adubya |
| Posted: Thu Feb 23, 2012 5:55 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
Well as you're attempting to put incompatible data into the table column then I'm not surprised an error is returned. The fact that other DB drivers/tools perform differently is academic. Convert your data in ESQL to a compatible data type and everyone's happy  |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 23, 2012 5:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Aldrine wrote: |
| But its about the precision and scale, which is automatically taken care by the DB/Driver for eGate, WMB JDBC and other tools but why not for ESQL? |
Because e*Gate uses it's own method for accessing a database (IIRC it uses JDBC now as well), WMB JDBC is JDBC and ESQL is using ODBC. So different database drivers react in different ways.
My question is this: how do you know from a business standpoint that it's correct for 96.55 to be rounded up and stored as 97? How do you know in any given case it shouldn't be rounded down to 96?
This isn't the sort of thing I'd be happy leaving to a piece of driver software to work out. I'd prefer some visible rules on this in code somewhere.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Feb 23, 2012 5:58 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
War story - financial application I worked on stored integer values but they had to be rounded to the higher even number. So 96.55 became 98.
Don't ask me why, I never got a straight answer just a white board full of actuarial formulas proving conclusively that this had to be done. Apparently.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Feb 23, 2012 6:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| War story - financial application I worked on stored integer values but they had to be rounded to the higher even number. So 96.55 became 98. |
Off by two, instead of off by one?
The British must be coming by sea, instead of land. |
|
| Back to top |
|
|
|
|
