executeQuery and H2 database

[rjgtav]

Hi. after trying and more trying, i couldn't make the executeQuery(sql, params) work with h2 database. It looks like is a problem with h2 database as it says that that method is illegal for a prepared statement. Is anyone facing this problem?

[Lapo]

Hi,
can you provide the details of the exception and the code that causes it?
Are you sure the DB is setup correctly? Can you run a basic query or insert?

[rjgtav]

Yes the db is setup correctly. I am using it since sfs2x rc1.

This error happenned since i upgraded to rc1b.
i was running a simple query (SELECT * FROM blabla WHERE NAME=blabla) for the login system. When I started sfs after the upgrade, it gave me this exception:

SQL Exception: org.h2.jdbc.JdbcSQLException: This method is not allowed for a prepared statement; use a regular statement instead. [90130-148]

Then, i went to the javadocs, and found the new executeQuerty(sql, params) function. So i tried to use it, and it gave again the same exception.

I used this code:

Code: Select all

String userSQL = "SELECT * FROM BLABLA WHERE NAME=? LIMIT 1";
userResult = (ISFSArray) _dbManager.executeQuery(userSQL, new Object[] {username});

where the username was the name sent in the login event.

Finally, after searching the forums and the docs, i found the old way to execute a prepared statement. I used it, and it worked.

Here's the code i'm using now (the code with // is the old code that throws the exception):

Code: Select all

public class LoginEventHandler extends BaseServerEventHandler
{
// Obtain the DBManager and reference the zone name specified in Main.java.
   private IDBManager _dbManager = SmartFoxServer.getInstance().getZoneManager().getZoneByName(Main.ZONE).getDBManager();

   // Bad username/password error
    private SFSErrorData _errData = new SFSErrorData(SFSErrorCode.LOGIN_BAD_USERNAME);

   @Override
   public void handleServerEvent(ISFSEvent event) throws SFSException
   {
        trace("<-LOGIN EVENT->");
        ISession session = (ISession) event.getParameter(SFSEventParam.SESSION);
        String username = (String) event.getParameter(SFSEventParam.LOGIN_NAME);
        String encryptedPass = (String) event.getParameter(SFSEventParam.LOGIN_PASSWORD);
        ISFSApi smartfox = SmartFoxServer.getInstance().getAPIManager().getSFSApi();
        ISFSArray userResult = null;

        //String userSQL = "SELECT * FROM BLABLA WHERE NAME=? LIMIT 1";

        try {
            Connection conn = _dbManager.getConnection();
            PreparedStatement sql = conn.prepareStatement("SELECT * FROM BLABLA WHERE NAME=? LIMIT 1");
            sql.setString(1, username);
            ResultSet res = sql.executeQuery();
            userResult = SFSArray.newFromResultSet(res);
            conn.close();

            //userResult = (ISFSArray) _dbManager.executeQuery(userSQL, new Object[] {username});
        } catch (SQLException sqlErr) {
            trace("SQL Exception: "+sqlErr);
            throw new SFSLoginException("Username or password incorrect.", _errData);
        }

There is more code, but its just to verify the password.

[rjgtav]

Just found now. The test sql statement (SELECT * FROM BLABLA) fails too and gives the same error

[rjgtav]

any clue about why this happens?

[Prof_PP]

Having finally got SFS2X to see my H2 Database, I am also getting this error on startup:

Code: Select all

08:47:31,611 ERROR [main] db.SFSDBManager     - 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Exception: org.h2.jdbc.JdbcSQLException
Message: This method is not allowed for a prepared statement; use a regular statement instead. [90130-148]
Description: The DBManager Test SQL failed
Please double check your SQL code and make sure that Database server is running.
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

My Test statement as entered in the Admin DBS section is :

SELECT COUNT(*) FROM USERS

Not sure why this will not work.

Try added "remote Access" as in another forum posting that had a similar problem with mySQL, but this made no difference.

Any help please?

Cheers.

[tchen]

Just a guess but

http://groups.google.com/group/h2-datab ... 84417351b7

[Lapo]

Unfortunately this is not a quick thing. We'll need to setup a test environment with H2 and see what's going on. I have added a ticket in the DB for further analysis

[hamish]

Hi Lapo, I'm also getting a db connect failure connecting to sql server after upgrading to the RC1b release.

on server startup it fails the test sql with an error something like: Can't pass parameters to prepareStatement or createStatement ( can't remember exactly what it was since i've rolled back to RC1a and it works now )

I wasn't passing any parameters. My test sql was just

select * from blah