Special character handling

[Jagdeep Sharma]

Hi guys,

I'm facing a problem while saving a special symbol to database. What i am doing is that i have a jsp page which has encoding UTF-8. From that jsp page i send ♠ symbol to servlet. On servlet side i have already set encoding to UTF-8 using request.setCharacterEncoding("UTF-8") So far everything works fine. But let say width of the field in which i store this symbol is 100 (varchar). When i insert 70 ♠ symbols. It throws exception saying "host variable value is too large to store". I am unable to understand why this is happening when insert few spade symbols. Everything works fine. I am using prepared statement.

Any suggestions. Thanks in advance.

Jax

[Charles 'King]

Please post the first few lines of the exception from the error log. Which DBMS are you using? State the java object type your storing in your db field. Is it a String, etc...?

[Jagdeep Sharma]

Charles 'King wrote: Please post the first few lines of the exception from the error log. Which DBMS are you using? State the java object type your storing in your db field. Is it a String, etc...?

I am using DB2 and I am storing string in varchar. Below please find some snippets from log.

[color=red]

[7/20/10 15:11:44:109 IST]  99d2bb5 SystemErr     R COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL0302N  The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.  SQLSTATE=22001

[7/20/10 15:11:44:109 IST]  99d2bb5 SystemErr     R 	at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[7/20/10 15:11:44:109 IST]  99d2bb5 SystemErr     R 	at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
[7/20/10 15:11:44:109 IST]  99d2bb5 SystemErr     R 	at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
[7/20/10 15:11:44:109 IST]  99d2bb5 SystemErr     R 	at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)

[/color]

[Charles 'King]

Jagdeep Sharma wrote:[ The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.
........

It appears your trying to store bit data into a string object. The string representation of your spade symbol is too large for your DB field data type. One option is to store your data as bytes in your DB, which means you'll have to change the DB data type for that specific field.

[Jagdeep Sharma]

Charles 'King wrote:

Jagdeep Sharma wrote:[ The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.
........

It appears your trying to store bit data into a string object. The string representation of your spade symbol is too large for your DB field data type. One option is to store your data as bytes in your DB, which means you'll have to change the DB data type for that specific field.

Thanks charles. Here i am copying some part of code. Can you please help me out. I don't understand bit or byte thing. I am just doing simple thing. Please have a look. This is servlet code. After running this code, On console value of description is to be shown as ???. And when i insert it into database it gives exception.

  
request.setCharacterEncoding("UTF-8");
sqlBuffer.append("insert into invoice values(?)");
ps = con.prepareStatement(sqlBuffer.toString());
ps.setString(1,request.getParameter("description"));  	
	
System.out.println("Before executing query.......addInvoice" );
	  
ps.executeUpdate();

[Gaurav Sainii]

Hi,

As Charles said, please change the data type in your database to bytes and in the provided java code change the following:-

  request.setCharacterEncoding("UTF-8");  
 sqlBuffer.append("insert into invoice values(?)");  
 ps = con.prepareStatement(sqlBuffer.toString());  
 ps.setString(1,request.getParameter("description"));      
       
 System.out.println("Before executing query.......addInvoice" );  
         
 ps.executeUpdate();    

following changes are suggested:-

  

 request.setCharacterEncoding("UTF-8");  
 String description = request.getParameter("description");
 sqlBuffer.append("insert into invoice values(?)");  
 ps = con.prepareStatement(sqlBuffer.toString());  
 ps.setBytes(1, description.getBytes("UTF-8"));      
       
 System.out.println("Before executing query.......addInvoice" );  
         
 ps.executeUpdate();    

[Jagdeep Sharma]

Gaurav Sainii wrote:Hi,

As Charles said, please change the data type in your database to bytes and in the provided java code change the following:-

  request.setCharacterEncoding("UTF-8");  
 sqlBuffer.append("insert into invoice values(?)");  
 ps = con.prepareStatement(sqlBuffer.toString());  
 ps.setString(1,request.getParameter("description"));      
       
 System.out.println("Before executing query.......addInvoice" );  
         
 ps.executeUpdate();    

following changes are suggested:-

  

 request.setCharacterEncoding("UTF-8");  
 String description = request.getParameter("description");
 sqlBuffer.append("insert into invoice values(?)");  
 ps = con.prepareStatement(sqlBuffer.toString());  
 ps.setBytes(1, description.getBytes("UTF-8"));      
       
 System.out.println("Before executing query.......addInvoice" );  
         
 ps.executeUpdate();    

Hey gaurav!

What's the problem with my code. Why it is throwing exceptions. Can you please explain a little bit.
When i convert datatype to bytes. How would i set its length. I mean let say for varchar(50) what could be equivalent for bytes.

[Gaurav Sainii]

What's the problem with my code. Why it is throwing exceptions. Can you please explain a little bit.

This kind of error that you are getting, occurs when you have a char or varchar field in your table in database of a given length,
and user tries to to put a longer string into it.
In particular, when using the UTF-8 Unicode character set, you must keep in mind that not all characters use the same number of bytes and can require up to four bytes per character.

When i convert datatype to bytes. How would i set its length. I mean let say for varchar(50) what could be equivalent for bytes.

I do not have much knowledge over DB/2 database types but possibly you can increase the size of your varchar type. For varchar(50) its equivalent in bytes could be 50x4=200. So you can declare a varchar(200) in this case.

[Jagdeep Sharma]

Gaurav Sainii wrote:

What's the problem with my code. Why it is throwing exceptions. Can you please explain a little bit.

This kind of error that you are getting, occurs when you have a char or varchar field in your table in database of a given length,
and user tries to to put a longer string into it.
In particular, when using the UTF-8 Unicode character set, you must keep in mind that not all characters use the same number of bytes and can require up to four bytes per character.

When i convert datatype to bytes. How would i set its length. I mean let say for varchar(50) what could be equivalent for bytes.

I do not have much knowledge over DB/2 database types but possibly you can increase the size of your varchar type. For varchar(50) its equivalent in bytes could be 50x4=200. So you can declare a varchar(200) in this case.

Thanks for your reply!
But is there any way to accommodate special character in database without increasing field size. I mean there should be some centralized control to change character encoding in database.

[Gaurav Sainii]

But is there any way to accommodate special character in database without increasing field size. I mean there should be some centralized control to change character encoding in database.

Yes there are ways to set the encoding in Oracle database but for DB/2 you need to google a little bit. Or anyone working on DB/2 could help.

[Jagdeep Sharma]

Gaurav Sainii wrote:

But is there any way to accommodate special character in database without increasing field size. I mean there should be some centralized control to change character encoding in database.

Yes there are ways to set the encoding in Oracle database but for DB/2 you need to google a little bit. Or anyone working on DB/2 could help.

Here i understood the problem. For special symbol symbol database takes more bytes which increases total capacity. For ♠ database varchar takes three character. I want fix this. For spade symbol. It should take 1 character instead of three so my database design doesnt break.