Problem!!! Prepare statement in ejb.

[gordonlch]

Hi All,

I had a problem, that is i use prepare stement in ejb, and execute the statment, it send a not my statment. crazy!!! part of my code as following :

-------->8---------
String _sqlString = "SELECT ";
_sqlString = _sqlString + "b.subsysid, a.modid, a.rightsid ";
_sqlString = _sqlString + "INTO " + _tmpTbl + " ";
_sqlString = _sqlString + "FROM usermodrights as a ";
_sqlString = _sqlString + "LEFT JOIN module AS b USING(modid) " ;
_sqlString = _sqlString + "WHERE userid = ? " ;
_pstat = _conn.prepareStatement(_sqlString);
System.out.println("spUserSubsystemList() : SQL : " + _sqlString);
_pstat.setString(1, UserID);
System.out.println("spUserSubsystemList() : Prepared : " + _pstat.toString());
---->8-------

----- Result --------
spUserSubsystemList() : SQL : SELECT b.subsysid, a.modid, a.rightsid INTO _tmp_gordon_usl FROM usermodrights as a LEFT JOIN module AS b USING(modid) WHERE userid = ?

spUserSubsystemList() : Prepared : org.jboss.pool.jdbc.PreparedStatementInPool@25491d
------------------------
Please help!!! what's wrong with me, i don't thing cann't use prepare statement in ejb. :~(

Gordon

[davidjencks]

What's the problem? your example works exactly as expected, but you haven't shown executing the ps.

[gordonlch]

Hi david,

Problem is, the result not my expected. The Preparedstatement.toString() should be return "select...where userid='someone'", but it return "org.jboss.pool.jdbc.PreparedStatementInPool@25491d", this is not my expecte statement.

Gordon

[armint]

This is comment isn't related to your problem, but you should create your string like this:
String _sqlString = SELECT " +
" b.subsysid, a.modid, a.rightsid " +
"INTO " + _tmpTbl +
" FROM usermodrights as a "
"LEFT JOIN module AS b USING(modid) " +
"WHERE userid = ? " ;
Your method will create a new string object(overhead) every time you do _sqlString + "someString". Whereas, the above statement should compile into StringBuffer.append(). Probably won't make a huge difference in performance, but there's no reason not to do it this way.

[gordonlch]

Hello armint,

You are right, my code (production) also use StringBuffer and some myown query generation method, my piece of code, just for my debug to trace error... ;-)

About my problem, any idea?

Gordon

[davidjencks]

I'll try again: What's the problem? Eveything you show is working fine. There are no specs for what PreparedStatement.toString() should return.

[gordonlch]

Hi davidjencks,

I had change my program, it work fine, thanks.

As early of my program , i use the preapredstatemet.tostring to return the compiled sting, for example,

1.) my_sql_statement="select * from table1 where pri_key = ?"
2.) set the parameter to "myid"
3.) preapredstatemet.tostring return = "select * from table1 where pri_key = 'myid'"

the return is what i want.

In jboss, it wrapped preparestatment and add to hash table (a helper say that), so when i call tostring, it return the "org.jboss.XXXXXX@1234". :-)

now i change my code directly use the preparestatment, it everything work fine.

Gordon