This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes PreparedStatement is not renderting char(13)/char(10) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement is not renderting char(13)/char(10)" Watch "PreparedStatement is not renderting char(13)/char(10)" New topic
Author

PreparedStatement is not renderting char(13)/char(10)

Gajendra Tomer
Ranch Hand

Joined: Sep 22, 2008
Posts: 32
Hello Everyone!

I trying to insert following text into Oracle through "PreparedStatement":

String str1="This is dummy text'||char(13)||char10' End Of Text";

I am expected that char(13) should be replace with "Carriage Return" & char(10) should be replaced with "Line Feed" in the final string which is going into DB TABLE.

but rather above text as it is going into table....whereas same thing is happening with simple "Statement".

Could anyone suggest something?

Regards,
Gajendra
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

Yes, true. That is the functionality of PreparedStatement.
It places the value of the variable in the record field.

A Statement is different. It sends the complete query as a text to your database.




OCUP UML fundamental and ITIL foundation
youtube channel
Alan Hampson
Ranch Hand

Joined: Apr 10, 2009
Posts: 31
I think you have a problem with your string delimiters. The string should look like this:


You're missing some single quotes, parens and pipes. Putting that whole thing into double quotes to make it a string literal would look like this:




Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

Still, with PreparedStatement, the content of str1 is literaly put into the column. If you bind a variable, the content is put into the column without any interpretation. No matter how many quotes, ampersands, @ts and pipes are involved.

Alan Hampson
Ranch Hand

Joined: Apr 10, 2009
Posts: 31
Jan,

I wasn't arguing with you, it appears we were both typing at the same moment. Thanks for your better answer.

Gajendra Tomer
Ranch Hand

Joined: Sep 22, 2008
Posts: 32
Thanks Jan & Alan for this assistance.

Alan! as suggested by you I had reformatted str1 and inserted it into DB through PreparedStatement but I got same outcome.

I believe Jan is right and that we have proved through your suggestion.

Cheers,
Gajendra
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

If you are using binds (which is good), simply put the CR/LF characters directly into your String, eg.Bind variables send the text into the database as it is, without any parsing or interpreting. Binds will make your life immensely easier.

(Of course, this technique works only when using binds. It would not work with simple Statement, don't even try it.)

Edit: messed up the String constant. Java String literal corresponding to CHR(13)||CHR(10) is "\r\n", not "\n\r" I had there previously. Sorry.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

now that we all agree : a warning

The CHR(13) CHR(10) or \r\n combination are platform dependent line break combinations.

The text might not appear as you expect on all platforms.
Gajendra Tomer
Ranch Hand

Joined: Sep 22, 2008
Posts: 32
Hi There!

Here is another flavour of the CR/LF issue.

CODE:
---------

String str1=new String("Dear First Name ,Last Name, \n\nThe process has started.");
ByteArrayOutputStream bos1 = new ByteArrayOutputStream();
String result=null;


//CONTENTS OF "NewFile.txt" FILE IS GIVEN IN THE FOLLOWING LINE :
//"Dear First Name ,Last Name, \n\nThe process has started."

File myFile = new File("NewFile.txt");

try{
FileInputStream fis= new FileInputStream(myFile);
byte[] buffer = new byte[50 * 1024];
int i=0;

while ( (i = fis.read(buffer)) != -1 ) {
bos.write(buffer,0,i);
}

result=new String(bos.toByteArray());

System.out.println(result);//----------------------------------- (LINE-1)

}catch(Exception e)
{
System.out.println("Exception:"+e);
}

EXPECTED RESULT:
--------------------------

I am expecting that "println" method at (LINE-1) should display following:

Dear First Name ,Last Name,

The process has started


REALITY:
-----------
Dear First Name ,Last Name, \n\nThe process has started.

MY CONCERN:
-----------------

WHY SO?

Regards,
Gajendra
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Your file probably contains the characters \n\n. Remember that \n is only a way how to encode the new line character in Java (and several other languages). The compiler replaces these characters with newline. When you read your file, it is (of course) not interpreted by the compiler and therefore the \n characters are left intact in it.

Edit your input file, delete the characters and just press Enter twice where you want the linebreak to be. That should do it.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement is not renderting char(13)/char(10)