Spring JDBC Prepared Statement & Char Datatype

I spent couple of hours figuring out this, hence thought to have a quick post.

I was trying to pass a string value as a parameter of prepared statement. I was using Spring JDBCTemplate for the same. This code was supposed to query a legacy database. When I was using the value in SQL directly it was working. But when I was using the prepared statement it was not. E.g.

// Following was working

String id = getJdbcTemplate().queryForObject("SELECT ID FROM SOME_TABLE WHERE TABLE_FIELD = 'abc'", String.class);

// Following was not working

String value = "abc";

String id = getJdbcTemplate().queryForObject("SELECT ID FROM SOME_TABLE WHERE TABLE_FIELD = ?", String.class, value);

The reason was that the TABLE_FIELD was of type CHAR(50) and hence the issue.

Since I did not have any control on changing the datatype itself. In order to fix the issue I changed the SQL to trim the char field as shown below:

String value = "abc";

String id = getJdbcTemplate().queryForObject("SELECT ID FROM SOME_TABLE WHERE RTRIM(TABLE_FIELD) = ?", String.class, value);

So, that’s one of the reasons why we should prefer using VARCHAR2 or equivalent datatype. Lesson learnt 🙂

Happy Learning,

Kamlesh

Advertisements

Coding Best Practices

For a software developer it is advisable to follow coding conventions. Here are some good links for coding conventions on different languages for quick reference.


Java Coding Conventions
.Net Coding Conventions
Database Naming Conventions (Oracle)
C Coding Conventions

In addition there are tools available using which you can check whether you are following best coding practices or not. For example in Java tools like PMD, FindBugs and Checkstyle are available as eclipse plugin which helps you to ensure that your code follows the recommended coding practices.


Cheers,
Kamlesh

Drop tables for a user (A Workaround)

I am not an expert in database and hence i got stuck when I had to drop around hundreds of tables for a user in a database (Oracle 10g in my case). The tables were having complicated relationships and hence they were having dependencies on other tables maintained by constraints. But then I got a very nice trick to do the same. Below mentioned are the steps for the same:

Log on to the database as the user whose tables has to be dropped and run the following command,

select ‘drop table ‘ || tname || ‘cascade constraints;’ from tab ;

The output of this script will be the list of drop table commands which will also drop the constraints. For example if there are two tables say customer and account, the output will be like:

drop table customer cascade constraints;

drop table account cascade constraints;

Now the script for dropping all the tables is ready. This script will drop all the tables. However this script could be hand-coded but if you have hundreds of tables this trick will help you. You can also modify the command to write the output in a .sql file so that you can directly execute it.

However this is of course a workaround but this had solved my problem.

Cheers,

Kamlesh