Smartgwt SQL Datasource pooling problem

[gokhanoner]

SmartClient Version: v8.2p_2012-08-22/PowerEdition Deployment (built 2012-08-22)

Hi,

During our DB tests, we see that Smartgwt SQL datasource login/logout eatch time I refresh a listgrid or record a form.
Then I looked at the forum and see that Admin console can give some options for pooling, or at least it's what you say.
I tried and it gives me below config. but still It's login/logout for each and every DB job.

Can you please let us know if your native SQL datasource supports Pooling or should we use JPA datasource or hibernate for pooling???


sql.SQLServer.driver.databaseName: ABCD
sql.SQLServer.driver.url: jdbc:sqlserver://XXXX;DatabaseName=ABCD;SelectMethod=cursor
sql.SQLServer.interface.type: driverManager
sql.SQLServer.driver.context:
sql.SQLServer.driver.portNumber: 1433
sql.SQLServer.driver.driverType: thin
sql.SQLServer.driver.driverName: sqlserver
sql.SQLServer.driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
sql.SQLServer.database.type: sqlserver
sql.SQLServer.driver.networkProtocol: tcp
sql.SQLServer.driver.serverName: gb13
sql.SQLServer.interface.credentialsInURL: false
sql.SQLServer.driver.user: user
sql.SQLServer.driver.password: password
sql.SQLServer.pool.enabled: true
sql.SQLServer.autoJoinTransactions: true

[Isomorphic]

There's a dedicated overview doc for connection pooling that you should read.

We can't tell what's gone wrong from just this. Possibly, you are not using those settings at all (eg, failed to save file, failed to restart after changing settings, not actually using "SQLServer" as dbName, etc).

[gokhanoner]

Hi,

I looked that page and seems that it doesn't have any new info. I've no problem regarding Hibernate pooling, I'm also using C3P0 and it's all OK. Issue here, whatever I try on SQL Data Source, it doesn't use pooling. I really need a workign sqlserver.properties file form you so I can compare mine. I suppose it's that that hard.

[Isomorphic]

We tried this out. SmartGWT pooling is functioning correctly, but your use of SelectMethod=Cursor is causing SQLServer to time out all connections in 15 seconds. So if you rapidly initiate a series of requests you'll see the same Connection used, but not of you wait briefly between tests.

We're not familiar with this setting - you'll need to refer to SQLServer docs on how to avoid this effect.

[gokhanoner]

Thanks for the info.

can you gibe me the parameters that you used so I can test either.

Problem is, without SelectMethod on URL, I couldn't manage connect. Admin Console doesn't accept my 1433 port number.

If you can give me a screenshot of admin console with correct parameters, that'll be really appreciated.

by the way, SelectMethod=Cursor is preferred setting from SQLServer.

[Isomorphic]

We're not sure what parameters you mean - we just set up a test server. The only things that differed were the host for the test server and user/pass, none of which could possibly help you.

If you're having trouble connecting without SelectMethod set, this is something where you should refer to SQLServer docs and/or Microsoft Support.

[gokhanoner]

Hi,

I see that while my app. is loading:

ISC: Configuring log4j from: file:/C:/Works/workspace_jee/KasBox/war/WEB-INF/classes/log4j.isc.config.xml
=== 2012-09-17 19:31:23,177 [main] INFO ISCInit - Isomorphic SmartClient Framework - Initializing
=== 2012-09-17 19:31:23,185 [main] INFO ConfigLoader - Attempting to load framework.properties from CLASSPATH
=== 2012-09-17 19:31:23,261 [main] INFO ConfigLoader - Successfully loaded framework.properties from CLASSPATH at location: jar:file:/C:/Works/workspace_jee/KasBox/war/WEB-INF/lib/isomorphic_core_rpc.jar!/framework.properties
=== 2012-09-17 19:31:23,261 [main] INFO ConfigLoader - Attempting to load project.properties from CLASSPATH
=== 2012-09-17 19:31:23,262 [main] INFO ConfigLoader - Unable to locate project.properties in CLASSPATH
=== 2012-09-17 19:31:23,266 [main] INFO ConfigLoader - Successfully loaded isc_interfaces.properties from CLASSPATH at location: jar:file:/C:/Works/workspace_jee/KasBox/war/WEB-INF/lib/isomorphic_core_rpc.jar!/isc_interfaces.properties
=== 2012-09-17 19:31:23,266 [main] INFO ConfigLoader - Attempting to load server.properties from CLASSPATH
=== 2012-09-17 19:31:23,268 [main] INFO ConfigLoader - Successfully loaded server.properties from CLASSPATH at location: file:/C:/Works/workspace_jee/KasBox/war/WEB-INF/classes/server.properties
=== 2012-09-17 19:31:23,275 [main] INFO Logger - Logging system started.
=== 2012-09-17 19:31:23,276 [main] INFO ISCInit - Isomorphic SmartClient Framework (v8.2p_2012-08-22/PowerEdition Deployment 2012-08-22) - Initialization Complete
=== 2012-09-17 19:31:23,279 [main] INFO ISCInit - Auto-detected webRoot - using: C:\Works\workspace_jee\KasBox\war
=== 2012-09-17 19:31:23,401 [main] INFO PreCache - Isomorphic PreCache servlet loading
=== 2012-09-17 19:31:23,416 [main] INFO PoolManager - SmartClient pooling disabled for 'DataSource' objects

It says that "SmartClient pooling disabled for 'DataSource' objects". Attached is my server.properties file.

can you please advise??

Also

sql.SQLServer.interface.type: driverManager

what is the difference between driverManager and dataSource??

[Isomorphic]

Please see JDBC documentation for the difference between DriverManager and DataSource (the JDBC term).

As far as DataSource (the SmartGWT term), that log does not indicate that SQL connection pooling is disabled. It's active.

[gokhanoner]

Hi Isomorphic,

See below as my connection setting for SQLServer and Oracle, I used Admin Console to create these settings:

sql.defaultDatabase: ORACLE
sql.ORACLE.database.type: oracle
sql.ORACLE.interface.type: driverManager
sql.ORACLE.pool.enabled: true
sql.ORACLE.interface.credentialsInURL: false
sql.ORACLE.driver: oracle.jdbc.OracleDriver
sql.ORACLE.driverName: oracle
sql.ORACLE.driver.url: jdbc:oracle:thin:@0.0.0.0:1521/XXXXX
sql.ORACLE.driver.user: XXXXX
sql.ORACLE.driver.password: XXXXX


SQL server setting is just same, only class names etc is different. After this settings, both SQL and Oracle logs shows that for every fetch, insert, update, Smartgwt connect and disconnect, so It seems that connection pooling is not working of Admin Console don't create correct configuration.

Can you give me 1 working example please???

[Isomorphic]

We are showing that SQL connection pooling is on by default if you start with one of our sample projects. So the problems are not with these settings, but elsewhere (or perhaps you are not interpreting your tools correctly).

The best approach is probably to start over with a sample project, verify pooling behaves as expected, then return to your other project and figure out the differences.

[gokhanoner]

Hi,

I tried your buildInDS example that came with power edition. I changed the DBs to SQLServer and than Oracle, but result is same, it always open a physical connection and close it.

Regarding the interpretation of the tools, same tool shows me that my hibernate connection pooling is working just fine, It opens 3 physical connection and until I stop the webserver, they all there, open. But in your case, it's not that, its opening and closing connections.

Also, please don't misunderstand, but saying "We are showing that SQL connection pooling is on by default" doesn't mean anything, to be honest. I dig the forum to see if someone has successfully implemented and see the pooling is working but no chance. And you always says that we need to use Admin console, use some tools etc. I'm using and what I see, whether you put "Pooled Connection" to your log files, I suppose it's not the same pooling that we're talking here.

As a licenced Power Edition User, I'd like to get a real answer and a test case, example conf. and working example project from you for a DB other than HSQLDB, preferably Oracle/MySQL/SQLServer so I can implement it. Because all this mystery regarding the pooling configuration make me to believe that there is no such config, so it's not possible to get pooled connection from Smartgwt SQL DataSources.

Thanks & Regards

[Isomorphic]

Sorry, but we have already tested the sample project and verified it is pooling correctly (not via our own logs, via seeing what the DB is doing).

So we have already provided a working project and settings.

You need to figure out how your environment is special; we can't do this for you.

[gokhanoner]

Did you test your application with Oracle or any other DB other than HSQL.

If so, can you please give me the connection parameters so I can replicate the setup here for the sample project and see??

[gokhanoner]

Also

in this doc:

http://www.smartclient.com/smartgwtee-latest/javadoc/com/smartgwt/client/docs/SqlConnectionPooling.html

it says that:

"SQLDataSource uses DBCP (Apache Commons) pooling, which also compensates for connection closure automatically. This is enabled by default with appropriate settings, ...."

what is this appropriate settings??? Do you have an example also. If you're saying the settings on example projects, it seems not working, at least for me????

[Isomorphic]

We've tested with each of the DB's we support, in response to your specific query, we've re-tested with Oracle and SQL Server.

DBCP is an Apache project, you can look up the docs at the Apache site.

Sorry, but we've blown a lot of time on this thread with the result being that there's no bug or issue with the framework - we need to move on. You can take advantage of our paid services if you need more help.