| Author |
Message
|
| akil |
 Posted: Mon Aug 10, 2015 5:12 am Post subject: IIB9: Mapping Node/Case Sensitive? |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Scenario :
1. mapping node, using the select transform
2. oracle database
What we noticed:
In case the column names / table name given in the data project are anything but capital case, the output does not contain any data ( the elements get created, but the value is not set )
When the column names / table name given in the data project are changed to upper case, all works.
The JDBC specification says that the column names are case in-sensitive, not sure why this is happening in the mapping node.
_________________
Regards |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Aug 10, 2015 5:16 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
IMHO, it makes a great deal of sense to standardise in the case of DB Object and Column naming.
I tend to use UPPERCASE everywhere (like MQ Object names...)
This has a side effect in making the code being used easier to read. when you see an UPPERCASE name you know that it is referring to a DB table element.
But, each to their own.
_________________
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 |
|
|
| fjb_saper |
| Posted: Mon Aug 10, 2015 5:42 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
The mapping node being a java node and java being case sensitive, I would expect the mapping node to be case sensitive.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| akil |
| Posted: Mon Aug 10, 2015 6:14 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
From the trace file it appears that the query executes and even returns a result set. However, the subsequent 'move' statements are never executed, just the element creation is shown.
I suppose an error or at-least a warning in the trace file should help developers know about this ..
_________________
Regards |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Aug 10, 2015 6:55 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Just because JDBC lets you used case-insenitive identifiers, doesn't mean your database does.
Nor does it mean your database specific JDBC driver will correct the case - really how could it know what to change it to? |
|
| Back to top |
|
|
| akil |
| Posted: Mon Aug 10, 2015 7:18 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
The result set came out fine ( it is shown in the trace ) , but the move transforms did not execute . It seems more to do with the move transform / xpath , it just does not execute and is skipped silently , with no errors or warnings..
_________________
Regards |
|
| Back to top |
|
|
| martinb |
| Posted: Tue Aug 11, 2015 7:41 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Couple of things to check/consider
- Are you sure there has not been a change in the table/column name, including case since the Database discovery was run and the database model file ".dbm" used to build the map is now inconsistent with runtime database?
Cross check you map with the actual column names shown in your user trace
- Given you say the trace shows the ResultSets are returned, this is less likely to be relevant, but APAR IBM IC99035 provides Env Var "MQSI_MAP_DB_QUOTE_DBNAMES" which can be used to ensure quoting of names in the generated SQL which might be required to preserve case
HTH |
|
| Back to top |
|
|
| akil |
| Posted: Wed Aug 12, 2015 10:44 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
We can reproduce this
1. Database : oracle
2. Dbm file : column names in small case
3. Result set : success
4. Transform ( move ) : does not create output
----
Change column names in dbm to upper case
Re wire the map
All good
Have done this only on oracle, as of now. Will try SQL server shortly .
_________________
Regards |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Aug 12, 2015 10:27 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| akil wrote: |
We can reproduce this
1. Database : oracle
2. Dbm file : column names in small case
3. Result set : success
4. Transform ( move ) : does not create output
----
Change column names in dbm to upper case
Re wire the map
All good
Have done this only on oracle, as of now. Will try SQL server shortly . |
Hence my post earlier in this thread 
_________________
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 |
|
|
|
|
