Error Code: 1366. Incorrect string value - MySql stored procedure

Hello friends,

In continuation to the character encoding issues that I have been facing during development of my website AssameseBooks.com, here comes another blog.

The situation goes as follows,
I had the requirement to pull data that were saved in different tables to a single table. The data in the source tables were saved properly in UTF-8 character encoding.
I went through the most simplest approach to do it i.e. write a stored procedure and have the job one.

Well, the moment I executed the stored procedure, I encountered following error i.e.

Error Code: 1366. Incorrect string value: '\xE0\xA6\x85\xE0\xA6\xB8...' for column 'CUSTOM1' at row 1

where custom1 is a column in my database table having the text in assamese font.

Well, the solution was to alter my table and set the character set to UTF8 and collate to utf8_unicode_ci

so the query to be executed to achieve is,

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Hope this comes handy to you guys too.

Cheers!! and Happy coding :-)

How to insert Indian font characters in MySql database using JAVA

Hello friends,

This blog is regarding my latest finding while working on my website Assamesebooks.com
As the name suggests, the website is all about Assamese literature and hence the books names had to be displayed in assamese as well.

I have created a dataload utility to load the product data from excel file into the mySql database.
The data in the excel file is in assamese font. Now the problem was that after the dataload, the assamese font  were not getting saved properly in the DB and were visible as ???? (question marks)

First I thought it might be due to the CHARSET or CHARACTER ENCODING that I had selected for my database schema and the table. But there were set to utf8_general_ci

This kept me puzzled for quite some time. While googling I came across the real solution which was related to how I was creating my JDBC connection URL.
In order to have the UNICODE characters saved properly to the mysql database we need to specify to use unicode and character encoding as UTF-8 while making the JDBC connection URL for databse.
This can be done by simply adding following parameters to the connection URL i.e. useUnicode=true&characterEncoding=UTF-8

So, your JDBC connection URL my look like,

String url = "jdbc:mysql://localhost/TestDB?useUnicode=true&characterEncoding=UTF-8";

Hope this information comes handy to you people as well.

Cheers! and happy coding :-)

PS: The google page where I found this helpful tip is :
http://uwudamith.wordpress.com/2011/09/02/how-to-insert-unicode-values-to-mysql-using-java/

Changing web project deploy name in Tomcat server

Before I start with details, I would like to give a brief of the problem so that you exactly know what I mean to say here.

Have you ever tried copying the same project in you workspace to make a duplicate so that you can fiddle with the code and ensure that the original code is not lost. If yes, then while running the project you might have come across the problem where the Tomcat server complained that there was already a project deployed with the same name. Well, this blog is about getting across this problem.

The first thing that comes to mind to get around this problem is changing the following things
1. Project Name
2. The 'Context Root' in 'Web Project Settings'

Below is an image of  the problem.



But to surprise it still doesn't work. Tomcat server picks the deploy name of the project from some where else.

After a bit of finding, I found following file in my project's folder i.e. org.eclipse.wst.common.component
It is located in the ".settings" folder in your project's folder.

The content of the file is as below,



You need to change the "deploy-name" under "wb-module" tag to a different name as I have highlighted above.

That's it. Start eclipse, refresh your workspace and then redeploy your project. You can see the new name there. Well, You are now ready to continue creating wonders.. :-) Cheers!!!

JSP / Servlet based web Site hosting on the web server : Tips / Tricks / Roadblocks


Well, as part of job I have created many web projects and made them live. But it is a different story when you need to do it on your own.
In the company you have different teams to handle various activities i.e. the Infra team for handling the Deployment and server setup stuff, Database team to handle the database setup / table creations, the sitedev team which deals with HTML pages, CSS, and JS and the developers keep themselves busy with the coding part.

I had my share of adventure of all these activities on my work for www.assamesebooks.com
This is a project that I am still working on. I will adding here the challenges that I have faced so far and their solution so that starter we developers like me who want to create something  all on their own can benefit.

1) Web Server folder structure - Where to place what files
Ok, so this is very important for us to know that which file need to be place at what location on the webserver. Many of the Linux based hosting servers give us a very nice interface to manage our application through "cPanel". I will discuss the same.
Below is an image of what a generic cPanel file structure looks like,


I will explain the details as colour marked in the image,
      a)      Red: These are custom folders. Some of them were present from before while others I created to manage my web resources like CSS, Javascript files, images, HTML files and the JSPs in a much better way. You can create your own folders.
      b)      Orange: This is the Runtime CLASSPATH of our web project. So anything that needs to be placed in the classpath of the web project like the classes, library JARs, properties files, web.xml, struts-config.xml  etc. goes here. 
      c)     Green: This is a configuration file for the web servers based on Tomcat Apache servers. The only configurational change that I did in it was adding the entry for my custom error page. Details of which will follow later.
      d)      Pink: These are my custom error pages. These are defined in the .htaccess as discussed above.
error400.jsp is displayed when an error with error code 404 occurs
error500.jsp is displayed when an error with error code 500 occurs
       e)      Blue: This file is the first file that is executed when someone types the address of our application in the browser. It is defined in the web.xml file under tag.

I hope this gives a fair idea of how the file system in cPanel is detailed. Let’s get in more details now.

2) Generic error page definition
Importance: This is important because we do not want our visitors to see a pre-defined ugly error page of our hosting provider when some error occurs. It is very important for a nice user experience to show our own error page with information about what to do next. So, let’s get on with it.
As I explained above, we can use the .htaccess file to define generic error page for our application based on the error codes. .htaccess is a normal text file and can be opened with any txt editor. Just note that this file has no extension so when you edit and save it, no extension is added or else it will stop working.
You can make an entry as follows for you error pages at the end of the file,
ErrorDocument 404 /error404.jsp
ErrorDocument 500 /error500.jsp

3) Deploying you web application on the Web Server
It is really easy. Though it appeared like a mystery when I did not know anything before. J
You can use the in built file upload tool of cPanel to upload your files. But I would suggest you use a FTP application like FileZilla to do it via FTP. It will make live much simpler as you can upload multiple file in a go with lot of ease.
So, place you file as detailed below,
      a)      The index.jsp and the .htaccess files go in the root. You also place all your custom folders that may be present under /WebContent  folder in your workspace here.
      b)      Copy all the build .class and properties files from you workspace location i.e. /build/classes to /WEB-INF /classes


       c)      Copy all the required JAR files to following location i.e. /WEB-INF/lib 




      d)      Copy all the configuration files and the TAG LIB files such as, web.xml, struts-config.xml, validation.xml, c.tld, fn.tld etc to /WEB-INF



      That’s all. This completes the deployment of your web application on the server.

4)  Next, challenge that I faced was while trying to access a file that I had placed in one of the custom folders through my servlet code. Giving a relation file location will not at all work here. It may work on our local machine and in eclipse. But when deployed on the web server, the code would fail.
In order to overcome this issue we need to get the full path of the file using the Servletcontext. Below is a sample code explaining  how to do it,

ServletContext context = getServletContext();
String relFileLoc = "uploadedData/Data.csv";
String fullFileLoc = context.getRealPath(relFileLoc);

5) Accessing you servlets from the browser
So, you have deployed all you servlet class files and code on the web server and are ready to access it. But , Hey! why am I not able to access it.
You can access your servlet correctly though the URL of form as given below in you workspace, i.e.
http://localhost:8080/servlet-name

But simply replacing localhost:8080 with your domain name (i.e.  http://domain-name/servlet-name  ) does not allow you to access your servlet.
You need to use URL of following form to access it, i.e.

http://domain-name/servlet/servlet-name 


Please note the word servlet in the URL. This is some kind of restriction from the host servers . I came to know about it through our host server’s documentation.

6) Want to upload a file to the web Server. Things you need to know.
Well, the first and last thing that you need to know is that you need to give write permission to the folder where you want your files to be saved. If you forget this your files will never get uploaded.
I have also attached here my fileUtil.java file with a sample code for performing single file upload on the server. For me, the uploaded data folder was /UploadedData . So, all you have to do is, right click on the folder name in the cPanel and choose “change permission” option. And select the write permission. Refer image below,



Download FileUtil.java: 


     7) Loggers – Key to finding errors and debugging them in you web application
      I do not need to explain why using loggers in your application is important. But I will definitely help you with how to deploy it on the web server J
      I have used Log4J for my purpose and will explain the same. Hope, it would be similar for any other loggers that you may use.
      Log4J comprises of 2 important things,
          a)      Log4J.properties file – this file holds the various properties needed for Log4J
          Like all the other property file it needs to be placed at following location on the web server i.e. /WEB-   INF /classes

       b)      The location where the log files would be created
       Now, this is really important and took me hell lot of time to figure it out. People taking you shared JVM based JSP hosting for their purpose will know it. J
      Well, I read lots tips/hints on the web as to how to set the path of the log file in the log4j.properties file so that it can be properly deployed on the web server. Some suggested using a servlet that was initialized on server startup or to use a ServletListner class. But somehow, both did not work for me. So this is what I did,
i)                    I printed the full path of my log folder in one of the JSPs by using the code to get the full path of a folder  in servlet as explained above i.e. context.getRealPath()

ii)                  I wrote this path as the location of my log file in the log4j.properties file

iii)                Gave write access to my Log folder
      
       I think this is the quickest and most simplest way to setup log4J for you application on the web server J

      8) Want to use Struts. No problem at all
      Setting up struts needs to special treatment. Simply place the required JARs and the configuration XML files in the locations as already explained and it will start working.
      The only little difficulty that I had was with using the struts-html and struts-bean Tag libraries in the JSP.
      With the setting I have explained,  including the tag-libs as below worked for me.
   
   <%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>
   <%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean" %>

     9) MySql + Stored Procedure = A hard time till I figured out the goof up
     Setting up the Database is very simple using the PHPMySQL interface that cPanel provides. I was able to run all kind of select, update and delete queries but when it came to running stored procedures I got following error, i.e.
      
     User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types 
     
     Using “noAccessToProcedureBodies=true” in the connection URL also did not work for me. It was then I discovered that there was some bug in a specific version of the MySQL JDBC driver that did not allow “noAccessToProcedureBodies”  to work. I had to use “useInformationSchema=true” attribute in the connection URL to have my stored procedure execute. In case you face similar issue, please try this out.

      10) SVN repository – A must use feature
      Most of the Host servers provide a SVN repository as well. You can check if one exists for you or not by looking for this logo in cPanel,


     This is a very nice feature and must use it for sure to keep a repository of your project so that you can maintain version of it and share across team members. It was of great help to me lot of times when I accidently deleted some of my code files and I had to get it back.

     So that's all guys that I have got for now. I will keep adding more to this list as I come acroos more challenges.

     Hope this makes some one's life easy and my purpose of writing this post will be solved. CHEERS!!





"User does not have access" error with stored procedure on MySQL

Well, I was working on this new project where I needed to execute a stored procedure from my JAVA code over MySQL database. But, the problem was that the statement,
CallableStatement cs = con.prepareCall("CALL myproc(?)");
gave following error i.e.

User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types

Even on using the parameter "noAccessToProcedureBodies=true" in the connection string didnot work. It was like a nightmare. Googled like hell but to no resolution. The code seemed all correct and used to work fine on local development machine but when deployed on the web server it just failed. To my surprise, I was even able to execute the procedure through PHP code but was only the JAVA code that had issues.

I read somewhere that my database user did not have proper rights but even that could not be the problem as I had given all the privileges to my user.

Then I came upon this article (http://bugs.mysql.com/bug.php?id=61203) which points to a bug in MySQL JDBC connector which cause "noAccessToProcedureBodies" not to work.

I was now interested. A bug !! well, that was the least I could expect that gave me such horrible time. Anyways, the discussion at the article suggested using the parameter "useInformationSchema=true" in the URL string to over come the problem or to use a more latest version of the MySQL JDBC conenctor as this issue was fixed in version 5.1.19

So, a connection string like this works like charm till your web server host provider upgrades his MySQL JDBC driver i.e.

DBURL=jdbc:mysql://localhost:3306/DatabaseName?user=DBUser&password=DBPassword&useInformationSchema=true

Hope, this information comes handy to some one in need. Cheers!! :-)