| Author |
Message
|
| ggriffith |
 Posted: Tue Dec 04, 2007 5:51 am Post subject: Retrieving data fropm Sql Server database |
 |
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
I'm having a problem getting meaningful data returned from a SQL Server database.
Broker running on Linux.
SQL Server running on windows
Using DataDirect 5.0 SQl Server driver
When I execute a simple 'select *' statement I get the following results
data types of int are returned successfully
data types of varchar and text are returned as
ããããããããããããããããããããããããà ´à ¨äãã ã¤ã¤ã¤ãããã â°äæ°ææççæ¼
any suggestions |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Dec 04, 2007 5:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I'd guess the 2 servers have incompatable code pages somehow. What are they using?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 04, 2007 6:06 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Do you have language environment variables set in the Broker profile?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ggriffith |
| Posted: Tue Dec 04, 2007 6:13 am Post subject: |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
The language environment variable is set to en_GB.UTF-8
Code page for windows server is the standard 1252.
Code page fro Linux is UTF-8 ( if this makes sense ) |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 04, 2007 6:19 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
... if I knew of a standalone ODBC test client you could use against the DSN...
What does SQLServer trace show?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Dec 04, 2007 6:22 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ggriffith wrote: |
Code page for windows server is the standard 1252.
Code page fro Linux is UTF-8 ( if this makes sense ) |
Well that should match for the standard letters and numbers at least. 
What are the hex values in the columns you're retrieving? Do they bear any resemblance to the "correct" values in either code page?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ggriffith |
| Posted: Tue Dec 04, 2007 6:40 am Post subject: |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
| SQLServer trace didn't show anything unusual, when the job first connected the log showed that it was using ISO 8859-1 (Latin-1) - Western European 8-bit character set. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 04, 2007 6:42 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
... Are you sure the env variables were in effect in the .mqsiprofile when Broker was started at runtime?
Did you follow the example ODBC.ini setup from the InfoCenter for creating your DNS?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ggriffith |
| Posted: Tue Dec 04, 2007 7:25 am Post subject: |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
Thanks for your quick responses. 
The env variable was definitely in place when the broker was started. We'll check the odbc.ini example ( infocenter appears to have a problem ).
Hex values will also follow shortly |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 04, 2007 7:32 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| ggriffith wrote: |
| ( infocenter appears to have a problem ). |
Thought it was just me...
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Dec 04, 2007 7:39 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jefflowrey wrote: |
| ggriffith wrote: |
| ( infocenter appears to have a problem ). |
Thought it was just me... |
There's certainly something up - it's moving like a snail on Valium. By the look of it even this unhappy state may not persist.
To paraphrase a well known 60s sci-fi show:
| Quote: |
| It's worse than that, it's dead Jeff |
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ggriffith |
| Posted: Tue Dec 04, 2007 9:15 am Post subject: |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
We appear to be in line with the odbc.ini example.
On the HEX front I've tried directing the data to a queue and file but the flows just bomb out. |
|
| Back to top |
|
|
| ggriffith |
| Posted: Wed Dec 05, 2007 3:17 am Post subject: |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
| The data looks ok in the ODBC trace. |
|
| Back to top |
|
|
| ggriffith |
| Posted: Wed Dec 05, 2007 9:04 am Post subject: |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
More clues if anyone still has any interest in this one.
Having executed the 'select *.......' command into the Environment.data array the following code generates the trace below. The flow consists of an MQInput, Compute and MQOutput ( GSQL.aa)
SET OutputRoot.XMLNSC.SQLServer.Data = CAST(Environment.data[1].vchNode AS CHARACTER);
The message broker detected an error whilst processing a message in node 'GSQL.aa'. An exception has been thrown to cut short the processing of the message.
2007-12-05 16:28:54.156917~97194160 ParserException BIP5010E: XML Writing Errors have occurred.
Errors have occurred during writing of XML.
Review further error messages for an indication to the cause of the errors.
2007-12-05 16:28:54.156925~97194160 RecoverableException BIP2136E: Source character ''006e'' in field ''3c00530051004c005300650072007600650072003e003c0044006100740061003e00006e0039003000300030003000313c002f0044006100740061003e003c002f00530051004c005300650072007600650072003e00'' cannot be converted from unicode to codepage '437'.
The source character is an invalid code point within the given codepage.
.
.
.
.
2007-12-05 16:28:55.584014~97194160 UserTrace BIP2537I: Node 'GSQL.Access_SQL': Executing statement ''SET OutputRoot.XML
NSC.SQLServer.Data = CAST(Environment.data[1].vchNode AS CHARACTER);'' at ('.GSQL_Access_SQL.Main', '22.3').
2007-12-05 16:28:55.584072~97194160 UserTrace BIP2539I: Node 'GSQL.Access_SQL': Evaluating expression ''Environment.data[1
].vchNode'' at ('.GSQL_Access_SQL.Main', '22.47'). This resolved to ''Environment.data[1].vchNode''. The result was '''æ¸ã¤ã>
ãããããããããããããã
2007-12-05 16:28:55.584091~97194160 UserTrace BIP2539I: Node 'GSQL.Access_SQL': Evaluating expression ''CAST(Environment.d
ata[1].vchNode AS CHARACTER)'' at ('.GSQL_Access_SQL.Main', '22.42'). This resolved to ''CAST('æ¸ã¤ããããã
. The result was '''æ¸ã¤ãããããããããããããããã
2007-12-05 16:28:55.584140~97194160 UserTrace BIP2566I: Node 'GSQL.Access_SQL': Assigning value '''æ¸ã¤ããããã
to field / variable ''OutputRoot.XMLNSC.SQLServer.Data''.
If I take the exception string, delete all the '00' and then put the resulting string through a HEX/ASCII converter I end up with the required data. The 6e equates to the 'n' in the data element. Someone has suggested that the extra '00' could possibly be due to conversion between UTF-8 and UTF-16.
<SQLServer><Data>n900001</Data></SQLServer> |
|
| Back to top |
|
|
| ggriffith |
| Posted: Thu Jan 31, 2008 2:11 am Post subject: Solution to the problem |
|
|
Acolyte
Joined: 17 Oct 2007
Posts: 67
|
We raised a PMR for this one and received the following solution. I have also received confirmation that this issue is resolved in FP06. We are currently on FP03 so we tried the variable setting and it sorted the problem. 
There were some changes to this between FP03 and FP06 so it could address their problem. It particular the default
setup for linux was changed.
I would suggest they upgrade to FP06. Alternatively, they could try the following setting of
MQSI_ODBC_ASSOCIATE_CONVERTER: 422S1202 |
|
| Back to top |
|
|
|
|
