problem sfs2x and postgresql

[san-sin]

Hello.

Sfs2x send me error on starting like

Code: Select all

Exception: org.postgresql.util.PSQLException
Message: Can't use query methods that take a query string on a PreparedStatement.
Description: The DBManager Test SQL failed
Please double check your SQL code and make sure that Database server is running.

But when i run Sms 1.x with the same connection property all is Ok.
I undestend that it is probably my mistake, but have no idea what is it.

Sfs1.x property

Code: Select all

            
<DatabaseManager active="true">
<Driver>org.postgresql.Driver</Driver>
<ConnectionString>jdbc:postgresql://localhost:5432/game_new</ConnectionString>
<UserName>postgres</UserName>
<Password>admin</Password>
<TestSQL><![CDATA[SELECT COUNT(*) FROM users]]></TestSQL>
<MaxActive>10</MaxActive>
<MaxIdle>10</MaxIdle>
<OnExhaustedPool>fail</OnExhaustedPool>
<BlockTime>5000</BlockTime>
</DatabaseManager>

Sfs2x property

Code: Select all

  <databaseManager active="true">
    <driverName>org.postgresql.Driver</driverName>
    <connectionString>jdbc:postgresql://127.0.0.1:5432/game</connectionString>
    <userName>postgres</userName>
    <password>admin</password>
    <testSql>SELECT COUNT(*) FROM users</testSql>
    <maxActiveConnections>10</maxActiveConnections>
    <maxIdleConnections>10</maxIdleConnections>
    <exhaustedPoolAction>FAIL</exhaustedPoolAction>
    <blockTime>5000</blockTime>
  </databaseManager>

[/code]

I use prostgre 8.4(windows) or 8.3(ubuntu)

[san-sin]

As i understend it's not criminal, becouse this error is only on startup.
I can use SFSDBManager in server extensions.

[laskovdm]

Dear gotoandplay(), please answer on that.

[Lapo]

Have you tried with another test query? Does it always report the same error?

[laskovdm]

Have you tried with another test query? Does it always report the same error?

Yep, i tried more simple requests. And some syntax experiments. And also - different postgre connectors.

SFS2 gives such error (with no useful comments in logs, that is the problem too), but properly working after that.

___
Ubuntu 9.04 (server, x64) + Postgre 8.3 + latest Sun JDK + SFS2

[Lapo]

The problem doesn't pose any issue to your database connection, don't worry. It's just a check. We'll see if we can replicate it and will let you know.

[san-sin]

The same problem if I try

Code: Select all

String sql = "UPDATE stats SET money=" + money + " WHERE id=" + statsId;
dbManager.executeUpdate(sql);

but if

Code: Select all

String sql = "UPDATE stats SET money=" + money + " WHERE id=?";
psmt = connection.prepareStatement(sql);
psmt.setInt(1, statsId);
psmt.executeUpdate();

all is ok.

In first case I've java exception

Code: Select all

UserUtil.loginorg.postgresql.util.PSQLException: Can't use query methods that take a query string on a PreparedStatement.at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:292)
        
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.smartfoxserver.v2.db.SFSDBManager.executeUpdate(SFSDBManager.java:201)
at com.smartfoxserver.v2.db.SFSDBManager.executeUpdate(SFSDBManager.java:174)
at com.test.myprg.UserUtil.login(UserUtil.java:46)
at com.test.myprg.UserInfoExtension.handleClientRequest(UserInfoExtension.java:83)
at com.smartfoxserver.v2.controllers.ExtensionController.processRequest(ExtensionController.java:137)
at com.smartfoxserver.bitswarm.controllers.AbstractController.run(AbstractController.java:96)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

may be it could help))

[Stigma]

same problem

[dingo]

to save you some headaches, here's are some current inconveniences in the dbmanager:

1. executeQuery won't work if any of your field values are null
http://forums.smartfoxserver.com/viewtopic.php?t=9436

2. ISFSArray result = db.executeQuery(sql, new Object[]{name}); won't work: http://forums.smartfoxserver.com/viewtopic.php?t=9705

3. aliasing is not yet supported http://forums.smartfoxserver.com/viewtopic.php?p=40670

[Lapo]

We plan to release a "nightly build" next week with a series of hotfixes including those related to the DBManager.
I will keep you updated.

hope it helps

[rjgtav]

Hi Lapo, have you already solved the problem when using executeQuery and executeUpdate with H2Database?

[dingo]

We plan to release a "nightly build" next week with a series of hotfixes including those related to the DBManager.
I will keep you updated.

hope it helps

great news Lapo, looking forward to it.