ConnectionPoolManager cannot be resolved ?

[SirPrize]

I'm working through the DatabaseWithJDBC example. I want to add some debugging statements, so I copied the source files (ConnectionPool.java, DatabaseJDBCPlugin.java, and TestDatabasePlugin.java) into a new project, called DatabaseWithJDBC. When I hit "refresh", I got the following errors:

"ConnectionPoolManager cannot be resolved"
"ConnectionPoolManager cannot be resolved to a type"
"The import snaq cannot be resolved"

What does this mean, and how can I fix it?

What's a "snaq"?

(I don't have very much experience with Java. It's times like this that I miss Actionscript.)

Thanks in advance.

[tcarr]

Check the original example's lib folder. You need to add the jars found in that lib folder to your project's classpath, so that it can compile.

Please note that you can create an AS1 plugin. It won't perform as well and it's very awkward for databases.

Please also note that you might be better off using one of the three JDBI database examples. See Database Examples for a tutorial discussing these three examples and advice on which one would be the best match for your own project.

[SirPrize]

Thanks, Teres. I copied the DBPool_v4.8.3.jar file into ES5/server/lib, because it was only in the example code folders. (You may want to look into adding a copy to the lib folder in future releases.) After that, I added it to my class path by updating my build path, and that took care of the error.

You mentioned JDBI. From what I understand, I have a few concerns with using JDBI.
1) It's Derby, which I've never used. I'm more familiar with MySQL
2) The docs say JDBI is for development and a small user base. I'm working on a Facebook app, so I *hope* to have a few hundred thousand users one day.
3) Derby is embedded, so I won't be able to use any database client or inspect the database while ES5 is up.

Are these assumptions (2 & 3) correct?

UPDATE:

Good news!

I encountered the following error: Unable to find a server level plugin named 'DatabaseJDBCPlugin' from extension 'DatabaseWithJDBC'

I found the solution here: http://www.electrotank.com/forums/sh...thJDBC-Problem

One thing I was doing wrong was I had made all components server-level. It wasn't really clear in the docs which should be server level. The "Database Examples" overview did not specifically talk about DatabaseWithJDBC. So, I adjusted my sever level components to only use DatabaseJDBCPlugin"

After that, I tried sending a query. I got a message complaining about not being able to find the "TestDatabasePlugin" plugin. After taking a closer look at Extension.xml, I realized that the handle name was different from the plugin name. I changed "TestDatabasePlugin" to "TestDatabaseJDBCPlugin", and that did the trick.

It also took a bit of deconstruction to figure out which parameters to send in my message ("query", "my query goes here").

I found the "Database Examples" page in the docs (http://www.electrotank.com/docs/es5/...e_examples.htm), but that is more of an overview than a tutorial. There were several things that I was able to finally figure out, but took a long time (two weekends) and a lot of frustration. If you get a chance, you may want to include these three things in your docs. Maybe they will save others some time and frustration.

Anyway, here's the code that I got to work. Maybe it will help someone else:

function onLoginResponse(e:LoginResponse):void
{
	trace("Logged in: " + e.successful.toString());
	trace("User: " + e.userName);
	if (e.successful)
	{
		var crr:CreateRoomRequest = new CreateRoomRequest();
		crr.roomName = "Test Room";
		crr.zoneName = "Test Zone";
		
                  //create the plugin
		var ple1:PluginListEntry = new PluginListEntry();
		var ple2:PluginListEntry = new PluginListEntry();

		ple1.extensionName = "MySamples";
		ple1.pluginHandle = "MySamples";
		ple1.pluginName = "MySamples";		

		ple2.extensionName = "DatabaseWithJDBC";
		ple2.pluginHandle = "TestDatabaseJDBCPlugin";
		ple2.pluginName = "TestDatabaseJDBCPlugin";
		
		crr.plugins = [ple1, ple2];

		es.engine.send(crr);
	}
}

btn_sendQuery.addEventListener(MouseEvent.CLICK,onSendQuery);
function onSendQuery(Event:MouseEvent):void {

	var pr:PluginRequest = new PluginRequest();

	pr.roomId = room.id;
	pr.zoneId = room.zoneId;
	pr.pluginName = "TestDatabaseJDBCPlugin";
	pr.parameters = new EsObject();
	pr.parameters.setString("query", "select now()");

	es.engine.send(pr);	
}

[tcarr]

Don't put the jars from an example into your installation's /server/lib. You have to put them into your extension's lib folder when you deploy it, and also into the compile path of your Java project when you are trying to compile it. If you put the jars into the installation's server/lib folder it could prevent ES5 from starting, in some cases.

The DatabaseWithJDBI example uses Derby and is for small numbers of concurrent users. See the DBLoginMySql example for a MySQL one for a few hundred users. See the TwoStepLogin example for a MySQL one that will scale into thousands and thousands of users - although if you get to multiple thousands you will likely want to do some fancy stuff with the database, for caching, such as Hibernate.

You should probably also look at the FacebookConnect example from ES5.3, which uses the current version of the Facebook API (signed_request and authResponse instead of a sig and session).

I fully understand not being able to figure out which parts are java and which are ES5. This tutorial talks about the way Java does databases; JDBI builds on that and I'd be happy to help you figure out how do your database queries using JDBI after you rummage around in the TwoStepLogin example. The SQL for that example is kept in the config folder.

[SirPrize]

Thanks. I read through the first few sections of the Java tutorial several days ago. That helped me understand the Java pieces (a little). It's mostly the setup that's killing me - making sure I have all of my hooks in the right place.

I thought I had this working last night, but I was mistaken. I saw the "Query called with string:" trace, figured I'd made a good connection, and called it a night. Tonight, I realized that, even though I successfully passed the query to my plugin, my code is not actually making a complete connection. The last debug statement from the following code is "Poolname set". I read the ES5 section on Managed Object Factories and looked at the "ConnectionPool" example, but I can't figure out what I'm doing wrong. Plus, the "ConnectionPool" example seems to be handling/creating the connection pool differently than the "DatabaseWithJDBC" example.

From "DatabaseJDBCPlugin.java":

    public EsObject doQuery(String query) throws SQLException {
        getApi().getLogger().debug("Query called with string: " + query);
        EsObject resultSetObj = new EsObject();
        Connection con = null;
        try {
                  getApi().getLogger().debug("trying db stuff");
            EsObject esDB = new EsObject();
            esDB.setString(POOLNAME, poolname);
            	getApi().getLogger().debug("Poolname set");
            con = (Connection) getApi().acquireManagedObject("ManagedConnectionPool", esDB);
            	getApi().getLogger().debug("Connection established");
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            	getApi().getLogger().debug("Statement built");
            ResultSet answer = null;
            answer = stmt.executeQuery(query);
            	getApi().getLogger().debug("Query executed");
            
            if (answer != null) {
                resultSetObj = resultSetToEsObject(answer);            	
            	getApi().getLogger().debug("Results found");
        		while(answer.next()) {
        			System.out.println(answer.getString(1) + " " + answer.getString(2));
        			// Returns the data
        			// result.getString(1) = first field
        			// result.getString(2) = second field
        		}
            }
        } catch (SQLException ex) {
            getApi().getLogger().debug("SQL exception: " + ex.getMessage());
            Logger.getLogger(DatabaseJDBCPlugin.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            return resultSetObj;
        }
    }

Here's my Extension.xml file. ("testdb" is my database name)

<?xml version="1.0" encoding="utf-8" ?>
<Extension>
    <Name>DatabaseWithJDBC</Name>
    <ManagedObjects>
        <ManagedObject>
            <Handle>ManagedConnectionPool</Handle>
            <Type>Java</Type>
            <Path>com.electrotank.electroserver5.examples.database.ConnectionPool</Path>
            <Variables>
            	<!-- drivers is a comma separated list of JDBC drivers -->
                <Variable name="drivers" type="string">com.mysql.jdbc.Driver,org.apache.derby.jdbc.ClientDriver</Variable>
                <Variable name="logfile" type="string">dbpool.log</Variable>
				
				<!-- Create an array of EsObjects.  One object represents a connection pool. -->
                <Variable name="pools" type="EsObject">
                    <Entry>
                        <Variable name="poolname" type="string">mysqlpool</Variable>
                        <Variable name="url" type="string">jdbc:mysql://localhost:3306/testdb</Variable>
                        <Variable name="user" type="string">username</Variable>
                        <Variable name="password" type="string">password</Variable>
                        <Variable name="timeout" type="integer">2000</Variable>
                        <Variable name="maxpool" type="integer">20</Variable>
			<Variable name="maxconn" type="integer">98</Variable>
			<Variable name="expiry" type="integer">5</Variable>
                    </Entry>
                    <Entry>
                        <Variable name="poolname" type="string">derbypool</Variable>
                        <Variable name="url" type="string">jdbc:derby://localhost:1527/testdb</Variable>
                        <Variable name="user" type="string">username</Variable>
                        <Variable name="password" type="string">password</Variable>
                        <Variable name="timeout" type="integer">2000</Variable>
                        <Variable name="maxpool" type="integer">20</Variable>
			<Variable name="maxconn" type="integer">98</Variable>
			<Variable name="expiry" type="integer">5</Variable>
                        <!-- Optional parameters
				    <Variable name="maxpool" type="integer">10</Variable>
				    <Variable name="maxconn" type="integer">0</Variable>
				    <Variable name="expiry" type="integer">60</Variable>
				    <Variable name="init" type="integer">10</Variable>
				    <Variable name="validator" type="string">some java class</Variable>
				    <Variable name="decoder" type="string">some java class</Variable>
				    <Variable name="cache" type="boolean">false</Variable>
				    <Variable name="debug" type="boolean">false</Variable>
				    <Variable name="prop.property" type="string">jdbc specific properties</Variable>
				    <Variable name="async" type="boolean">false</Variable>
				    <Variable name="logfile" type="string">new logfile</Variable>
				    <Variable name="dateformat" type="string">yyyy-MM-dd hh:mm:ss.SSSS</Variable>
				     -->
                    </Entry>
                </Variable>
            </Variables>
        </ManagedObject>
    </ManagedObjects>
    <Plugins>
        <Plugin>
            <Handle>DatabaseJDBCPlugin</Handle>
            <Type>Java</Type>
            <Path>com.electrotank.electroserver5.examples.database.DatabaseJDBCPlugin</Path>
            <Variables>
                 <Variable name="poolname" type="string">mysqlpool</Variable>
            </Variables>
        </Plugin>
        <Plugin>
            <Handle>TestDatabaseJDBCPlugin</Handle>
            <Type>Java</Type>
            <Path>com.electrotank.electroserver5.examples.database.TestDatabasePlugin</Path>
        </Plugin>
    </Plugins>
</Extension>

My server log shows:

DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - using database pool: mysqlpool
. . .
INFO DisplayLogger -
-=-=-=-=-=-=-=-
ElectroServer has started successfully
. . .

When I pass a query from my client, I get this in my server log:

DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - Query called with string: SELECT now()
DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - trying db stuff
DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - Poolname set
DEBUG Extensions.DatabaseWithJDBC.Plugins.TestDatabaseJD BCPlugin - {EsObject:

}
DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - Query called with string: select * from names_table
DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - trying db stuff
DEBUG Extensions.DatabaseWithJDBC.Plugins.DatabaseJDBCPl ugin - Poolname set

So, it's failing on:
con = (Connection) getApi().acquireManagedObject("ManagedConnectionPo ol", esDB);

[tcarr]

I strongly suggest that you take one of the three database examples that are mentioned in the Database Examples tutorials. Just use it without making any changes at first other than customizing config/database.properties.

I'll work with you on this, but I have far more experience using those three database examples than the DatabaseWithJDBC one, and the DatabaseWithJDBC doesn't scale as well as the TwoStepLogin example does.