| Author |
Message
|
| er_pankajgupta84 |
 Posted: Mon Apr 06, 2009 10:07 pm Post subject: Inserting control characters using MbSQLStatement in JCN |
 |
|
Master
Joined: 14 Nov 2008
Posts: 203
Location: charlotte,NC, USA
|
we are facing an issue with the MbSQLStatement while inserting a data having control characters (i.e. ASCII values between 0-31) in it, we are getting an Exception (“Invalid character”) while creating MbSQLStatement itself. We have tried both ways of executing the insert query i.e. normal MbSQLStatement and using PASSTRU, but none of them worked. When we removed these special characters from the data then we were able to execute the insert query successfully. We are able to insert control characters by using JDBC-java way of executing database queries using prepared statements.
Here is a sample query that fails at the time of creating MbSQLStatement.
String query = "INSERT into schemaName.tableName(columnName) values ('DataHavingControlCharacter')";
We also tried this query:
String query = "PASSTHRU('INSERT into schemaName.tableName(columnName) values(?)','DataHavingControlCharacter')";
Is there any kind of encoding we need to do. If so can someone post a sample query String to create MbSQLStatement.
We need to do this in Java. |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Tue Apr 07, 2009 2:15 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
let us know which are those control characters that you are trying to insert.
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| er_pankajgupta84 |
| Posted: Tue Apr 07, 2009 3:43 am Post subject: |
|
|
Master
Joined: 14 Nov 2008
Posts: 203
Location: charlotte,NC, USA
|
It could be any character whose ASCII value is between 0-31.
For eg a character similar to a "rectangle" which has an ASCII value of 0 i.e. NULL. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Apr 11, 2009 3:05 pm Post subject: Re: Inserting control characters using MbSQLStatement in JCN |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| er_pankajgupta84 wrote: |
| while inserting a data having control characters (i.e. ASCII values between 0-31) in it, we are getting an Exception (“Invalid character”) while creating MbSQLStatement itself |
What is the exact Exception?
| er_pankajgupta84 wrote: |
Here is a sample query that fails at the time of creating MbSQLStatement.
String query = "INSERT into schemaName.tableName(columnName) values ('DataHavingControlCharacter')";
We also tried this query:
String query = "PASSTHRU('INSERT into schemaName.tableName(columnName) values(?)','DataHavingControlCharacter')"; |
Does the source code contain literal control characters, or Unicode escapes ('\uXXXX')?
| er_pankajgupta84 wrote: |
| For eg a character similar to a "rectangle" which has an ASCII value of 0 i.e. NULL. |
Some applications might display a "rectangle" in place of an un-printable character; that does not imply that a "rectangle" has an ASCII value of 0. |
|
| Back to top |
|
|
| er_pankajgupta84 |
| Posted: Sun Apr 12, 2009 11:06 pm Post subject: |
|
|
Master
Joined: 14 Nov 2008
Posts: 203
Location: charlotte,NC, USA
|
Exception is: "Invalid character" then in the description field it displays that rectangle like character whose ASCII value is 0.
Does the source code contain literal control characters, or Unicode escapes ('\uXXXX')? -> No.
This problem exists with character having ASCII value between 0-31.
This is the string that i want to insert.
String controlCharacters = "<<
/Size 51
/Root 50 0 R
/Info 49 0 R
>>
startxref
438550
%%EOF
ef0 R0000 nRA )Tj
--SAP_1ebbb150-17ff-11de-8fa8-f66625531202_END--"
Please note that all the characters after ")Tj" till "--SAP" are control characters and they are visible as spaces in notepad. But when you look at them in debug mode...you will still rectangle like boxes. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Mon Apr 13, 2009 5:54 pm Post subject: Re: Inserting control characters using MbSQLStatement in JCN |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| er_pankajgupta84 wrote: |
| Exception is: "Invalid character" then in the description field it displays that rectangle like character whose ASCII value is 0. |
That doesn't look like an Exception. It looks like an error message. Description field? What description field? Which software component is generating or displaying the error? Toolkit? Broker? Notepad? Be more specific. Avoid pronouns like "we" or "it" when describing what a particular software component is doing.
| er_pankajgupta84 wrote: |
| Does the source code contain literal control characters, or Unicode escapes ('\uXXXX')? -> No. |
That wasn't a yes-or-no question.
What does the source code contain? If it contains literal control characters, how are they getting there? Copy-pasting control characters from one application to another is problematic, at best. How was the resulting content of the source code verified?
| er_pankajgupta84 wrote: |
This problem exists with character having ASCII value between 0-31.
This is the string that i want to insert. |
Not anymore it isn't. It might have started out that way, but whatever control characters the string had did not survive the trip through the Internet. It's rather pointless to post it that way.
| er_pankajgupta84 wrote: |
| Please note that all the characters after ")Tj" till "--SAP" are control characters and they are visible as spaces in notepad. But when you look at them in debug mode...you will still rectangle like boxes. |
Debug mode of ... what? Notepad?
Applications that display (or worse, substitute) control characters as spaces or "rectangles" are inadequate for debugging text containing control characters. Either use better apps, or don't use control characters. Java supports unicode escapes; use them. |
|
| Back to top |
|
|
| er_pankajgupta84 |
| Posted: Mon Apr 13, 2009 8:45 pm Post subject: |
|
|
Master
Joined: 14 Nov 2008
Posts: 203
Location: charlotte,NC, USA
|
Thanks for your time... we have raised a PMR for it...i will post the result of PMR.
By debug mode i meant the debug mode of my toolkit. In debug mode you can see the values of java variables and exception codes and description etc.
Is there any way i can attach a file to my post..??
I have uploaded the input file on google docs...
http://docs.google.com/ViewDoc?docid=dgncjzb_5d8kqqzx9
I need to insert the file contents in a databse column of type clob using MbSQLStatement. Remember i am able to insert it using JDBC type 4 driver... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Apr 14, 2009 2:10 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Are you even sure that the field definition as in the DB AND posted to WMB will allow such control characters? Note that if the field is defined as String I would expect it to be a char[] and those values may not be acceptable for the type char.... The DB might be a little bit more lax there...
In that case the error is correct and maybe you need to define your field not as string but as byte[] ?? (blob vs clob) 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| er_pankajgupta84 |
| Posted: Tue Apr 14, 2009 9:54 pm Post subject: |
|
|
Master
Joined: 14 Nov 2008
Posts: 203
Location: charlotte,NC, USA
|
I am able to insert same string using JDBC Driver Manager class way of accessing Db. In that case it happily accepts control characters as string...
It is with the MbSQLStatement i am facing this difficulty..
You can try a small POC if you want i have already posted the file and code in this post... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Apr 15, 2009 1:55 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| er_pankajgupta84 wrote: |
I am able to insert same string using JDBC Driver Manager class way of accessing Db. In that case it happily accepts control characters as string...
It is with the MbSQLStatement i am facing this difficulty..
You can try a small POC if you want i have already posted the file and code in this post... |
As I said the JDBC driver might be more lax. Have you tried inserting this string using the broker's odbc? (Use the odbc as defined by the broker from a non broker odbc process). Does that work or give you the same error?
If it works, it means you need to open a PMR.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| er_pankajgupta84 |
| Posted: Wed Apr 15, 2009 10:16 pm Post subject: |
|
|
Master
Joined: 14 Nov 2008
Posts: 203
Location: charlotte,NC, USA
|
| MbSQLStatement uses odbc driver to do database operation. |
|
| Back to top |
|
|
|
|
