| Author |
Message
|
| gmabrito |
 Posted: Wed Jun 18, 2003 12:40 pm Post subject: space to NULL |
 |
|
Apprentice
Joined: 19 Mar 2002
Posts: 35
|
I am having some probems with a string field that contains spaces. When I try to insert this field into a not null field in a database I get errors complaining that the field is null, when in reality it is spaces. How do I prevent this? Thanks.
WMQI 2.1 CSD04 Oracle 8i
Incoming MRM message to database insert via compute node. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Jun 18, 2003 1:07 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is your MRM element definition set up to treat spaces as logical nulls?
If so, the value your Root.MRM.Field will hold will be NULL, and not spaces. |
|
| Back to top |
|
|
| gmabrito |
| Posted: Wed Jun 18, 2003 1:21 pm Post subject: |
|
|
Apprentice
Joined: 19 Mar 2002
Posts: 35
|
The element definition is:
Physical Type: Fixed Length String
Lenght Count : 8
Length Units : Bytes
Byte Alignment : 1 Byte
String Justification: Left Justify
Padding Character: SPACE
Skip Count: 0
Encoding Null: NULLPadFill |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Wed Jun 18, 2003 1:40 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
I think for a database insert the syntax would look like :
Insert into Database.<Schema>.<Table>(col1,col2,col3) values( 'a',,'c');
Now since there are spaces for the second column the database treats it as a null.
If you want to put spaces you need to write :
Insert into Database.<Schema>.<Table>(col1,col2,col3) values( 'a',' ','c');
Now as your input is coming as spaces you need the quotes,
so as that cannot be done on the database statement you need to check for the existence of a space and then change it somehow. |
|
| Back to top |
|
|
| gmabrito |
| Posted: Wed Jun 18, 2003 2:12 pm Post subject: |
|
|
Apprentice
Joined: 19 Mar 2002
Posts: 35
|
| I am going to use COALESCE(source_value1, source_value2...) |
|
| Back to top |
|
|
| gmabrito |
| Posted: Thu Jun 19, 2003 5:31 am Post subject: |
|
|
Apprentice
Joined: 19 Mar 2002
Posts: 35
|
| COALESCE and IS NULL is not working because in the message it is a spaces, but when it gets to the insert removes the spaces. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 19, 2003 8:33 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If a trace output for the field shows that it does contain spaces and not a NULL value, then I think it may be an issue with your database setup. But I don't use Oracle, so I can't say what. Try looking at the ODBC configuration.
One thing to try is switching your insert statement from a standard ESQL insert to using PASSTHRU. I've run into odd conversion issues like this with SQLServer that were fixed by using PASSTHRU instead. |
|
| Back to top |
|
|
| gmabrito |
| Posted: Thu Jun 19, 2003 8:53 am Post subject: |
|
|
Apprentice
Joined: 19 Mar 2002
Posts: 35
|
the trace shows an empty string. I am getting around it by
IF (elementname = '' or elementname IS NULL) THEN
elementname = ' ';
END IF;
This seems to be working fine. |
|
| Back to top |
|
|
| Craig B |
| Posted: Thu Jun 19, 2003 9:25 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
If your MRM-CWF bitstream contains spaces for that particular field then the definitions you have specified will lead to the CWF parser populating the field with the zero length string (''). This is because the Padding character is SPACE and when the CWF parser populates the logical value it will trim the padding character according to justification. In this case, all spaces will be trimmed leaving the value of zero length string which is not NULL as you have found.
As far as I am aware, Oracle should accept a zero length string and be able to distinguish this from the different value of NULL. However, I believe this is dependent on the column type of the column you are inserting to. Although you have worked around your problem, just out of curiosity, what column type were you inserting to, and what was the error that you got from your INSERT call?
_________________
Regards
Craig |
|
| Back to top |
|
|
| drajib |
| Posted: Fri Jun 20, 2003 12:13 am Post subject: |
|
|
Apprentice
Joined: 25 Mar 2003
Posts: 42
Location: India
|
Yes, Oracle definitely distinguishes between '' (a zero length string) & null. In this scenario, I believe that the onus is on the ODBC driver for converting '' to null.
Best regards |
|
| Back to top |
|
|
|
|
