Archive

Posts Tagged ‘jdbc’

Character data type conversion when using SQL Server JDBC drivers

May 3rd, 2011 No comments

We have a existing old portal in asp with Sql server and now trying to get new features in new site developing in grails,java & same sql server.

Sometime ago, I was working on a login page so that the same old portal credentials could be used.   One issue I had to address was that the web app captures the password in clear text but sends an encoded password to the database (where the password is also stored in our encoded format).

Now I was trying to implement a simple logic for encryption; add 20 to each character’s int value and convert it to char and store the encrypted password.

For example the java code-

StringBuffer t = new StringBuffer(“”);

String test = “sviadha”;for ( int i = 0; i < test.length(); ++i ) {

char c = test.charAt( i );

int k = (int) c ;

int kk = k +20;

char ss = (char)kk;

t = t.append(ss);

}

System.out.println(“original password =”+ test);

System.out.println(“encrypted password ==”+ t);

Now when I tried to match the stored password using new UI it was failed all the time.

No matter what I tried, in the Java-tier I could not get past the fact that by the time the password was received in the SQL-tier, there was an encoded password mismatch.   So I worked around the problem, but passing the clear text password to the database and the stored procedure did the encoding and finally the validation.

After digging it found the  real problem; it turns out that this is a MS SQL Server JDBC driver configuration.  By default MS JDBC driver is sent to pass all strings as NVARCHAR, not VARCHAR.  This forced a Unicode conversion on the way to the database.

That’s why the same encoding logic was working fine for asp-sql server based old site but not on java based new site.

Here’s the magic to change this behavior so VARCHAR are sent and received…

MyDataSource

jdbc:jtds:sqlserver://mac1.temp.test:1434;DatabaseName=MyDatabase;tds=8.0;lastupdatecount=false; sendStringParametersAsUnicode=false

net.sourceforge.jtds.jdbc.Driver

. . .
Read more…

Spring JdbcTemplate batch Update-Insert example

April 12th, 2011 1 comment

This post explain how to use Spring JDBCTemplate to do batch insert and update.

If you are using SimpleJDBCtemplate then the Spirng docs are good place to find a good explanation for it Simple JDBC Template

If you are using JDBCtemplate , here is the example to use prepared statements and do batch updates

public boolean createGroceyList(List<Grocery> groceryList){

final List<Grocery> aGroceryList =  groceryList

int result[] = this.jdbcTemplate.batchUpdate(

“the query goes here “,new BatchPreparedStatementSetter() {

public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setLong(1, “param 1 goes here “);
ps.setString(2, “param 2 goes here “);

ps.setString(3, “param 3 goes here “);
}

public int getBatchSize() {
return aGroceryList.size();
}
});

return (aGroceryList.size() == result.length ? true : false);
}

The query would be something similar to

INSERT INTO GROCERY(ID, NAME, QUANTITY) VALUES(?,?,?)

Categories: Programming / tutorials Tags: ,

JDBC performance tuning with fetch size

July 25th, 2010 2 comments

Problem: For a Oracle (backend) – Java (frontend) system, a query  returning about 500 K rows was running very slow and is taking about 45 mins.  Oracle database is hosted on server (A), has a separate application server (B), and client can access database from client machine (C), using SQLPLUS.  Query is running slow from the clients machine (C) and from the java front end (B).

Basic Checks on dbase:

Make sure to check following with respects to the tables / database in question:

  • Latest Database statistics exists for the tables in question.
  • Indexes
  • Degree of Parallelism at Table Level:
  • Optimizer mode

Database configuration parameters  – sga_max_size,  pga_aggregate_target , memory_target, processes Read more…

Output parameter not allowed as argument list prevents use of RPC.

April 20th, 2010 2 comments

While calling a stored procedure from my java class; I was getting the following  error-

java.sql.SQLException: “Output parameter not allowed as argument list prevents use of RPC.”

After doing a little research; I found that When calling a stored procedure that has output parameters, the driver has to call the procedure using a remote procedure call (RPC). Stored procedures should be invoked using the special JDBC call escape syntax.

For example, {call stored_Proc1(?,?,?,?)}.

In this case the driver will be able to use an RPC successfully as all the parameters are represented by parameter markers (?). If however parameters are supplied as a mixture of parameter markers and literals,

for example {call stored_Proc1(?,’ ‘,?,0)}

Then the driver is unable to use an RPC and therefore cannot return output parameters. In these circumstances the driver raises the above exception and execution fails.

It is possible to use mixed parameter lists to call stored procedures that do not have output parameters. In this case the driver will substitute the parameters locally and use a normal “execute procedure” SQL call; however, this mode of execution is less efficient than an RPC.

Categories: Programming / tutorials Tags: , ,

Spring JDBC Tutorial

October 19th, 2009 No comments

Using Spring with JDBC is not a very common usage I have seen but sometimes you have applications with very few tables and simple CRUD operation and you do not need full fledged ORM framework like hibernate .

JDBC API provides connectivity between the database and java program. It provides support for wide range of databases from complex SQL – Based databases to tabular data sources like spread sheets.  With JDBC – enabled driver one can connect to database and run the queries, fetch data etc in a java program.

JDBC API is widely used to access db in java applications. Often hibernate and JDBC gets compared and Hibernate looks better than JDBC API. But Hibernate it has a big learning curve. People who are aware of JDBC sometime want their projects to be completed fast and might not want to switch to ORM.  In this case one can use JDBC with Spring. Read more…

Categories: Programming / tutorials Tags: ,