Re: Connection Pooling with mySQL
Ken Hygh
> Hi,
>
> I am trying to establish connection pooling within WSAD 5.0, Test Environment WAS 5.0 and mySQL 4.0.18 and mySQLConnector/J 3.0.11. stable.
>
[snip]
>
> ALEX
Alex,
When I got this working, I set the following properties on the datasource:
user, java.lang.String, <userid to connect with>
password, java.lang.String, <password for above user>
databaseName, java.lang.String, <name for your db. AW_Test in your case)>
port, java.lang.String, 3306 (or whatever port # you're using)
Ken
jli...@uk.ibm.com
I'm using WSAD 5.1, MySQL 4.0.18, mySQLConnector/J 3.0.12 (and tried 3.1.1 alpha). I'm also using JAAS.
I've set the resource properties for ConnectionPoolDataSource
databaseName = MyDB
serverName = localhost
port = 3306
and just to make sure:
url = jdbc:mysql://localhost:3306/MyDB?autoReconnect=true
In the code, I get and execute a statement:
Connection con = null;
javax.naming.Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/SOME_DB");
con = ds.getConnection();
Statement smt = con.createStatement();
smt.execute("select * from user");
--- I get the following error
[21/05/04 13:48:53:827 BST] 6eb56d12 SystemErr R java.sql.SQLException: General error, message from server: "No Database Selected"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1962)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1257)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2239)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2187)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1158)
at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeQuery(StatementWrapper.java:617)
----- so I tried this
Statement smt = con.createStatement();
smt.execute("use MyDB");
smt.execute("select * from user);
---- then everything works
This kind of defeats the purpose of JNDI, because the databaseName is not being set.
How do fix it?
Ken Hygh
jli...@uk.ibm.com
> > at
> com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
> > at
> com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:125
> > at
> com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
> > at
> com.mysql.jdbc.Connection.execSQL(Connection.java:2239
>
> com.mysql.jdbc.Connection.execSQL(Connection.java:2187
>
> > at
> com.mysql.jdbc.Statement.executeQuery(Statement.java:1
> 58)
> > at
> com.mysql.jdbc.jdbc2.optional.StatementWrapper.execute
> uery(StatementWrapper.java:617)
> >
> >----- so I tried this
> >
> >Statement smt = con.createStatement();
> >smt.execute("use MyDB");
> >smt.execute("select * from user);
> >
> >---- then everything works
> >
> >This kind of defeats the purpose of JNDI, because
> the databaseName is not being set.
> >
> >How do fix it?
> >
> >
> >
> >
> Just a WAG - did you try without setting the URL?
> Ken
Every permutation I could think of...
Jamie
poo
supported because according to the IBM site
http://www-306.ibm.com/software/webservers/appserv/doc/v50/prereqs/was_v51.htm
for database section, mySQL is not in the list.
I am using WAS 4.0 AE. Currently what I do is, Iconnect to MySQL through
coding not using JDBC resource in WebSphere at all.
--
Thank you and regards,
----------------------------------------------------------------------------
---------------
Poo
Senior Analyst Programmer
Formis Software Dynamics Sdn Bhd
6th Floor, Menara Aik Hua,
Cangkat Raja Chulan,
50200 Kuala Lumpur,
Malaysia.
Tel: (603) 2026 4988
ext: 107
"awagner2" <a...@itag.de> wrote in message
news:664032255.1084973156944.JavaMail.wasadmin@swg3ws006...
Hi,
I am trying to establish connection pooling within WSAD 5.0, Test
Environment WAS 5.0 and mySQL 4.0.18 and mySQLConnector/J 3.0.11. stable.
I got the following error message:
[19.05.04 14:49:34:371 CEST] 4c99a25 WebGroup I SRVE0180I: [AW_Test]
[/AW_Test] [Servlet.LOG]: TestDatabase: init
[19.05.04 14:49:34:842 CEST] 4c99a25 ConnectionFac I J2CA0107I: Es wurde
kein Aliasname für die über Komponenten realisierte Authentifizierung für
die Verbindungs-Factory oder die Datenquelle AW_Test angegeben.
{no aliasname set for using components realised authentication for the
connection factory or datasource AW_Test}
[19.05.04 14:49:35:143 CEST] 4c99a25 WSRdbDataSour u Database version is
40.18-nt
[19.05.04 14:49:35:143 CEST] 4c99a25 WSRdbDataSour u JDBC Driver version is
mysql-connector-java-3.0.11-stable ( $Date: 2004/02/04 02:47:36 $,
$Revision: 1.27.2.34 $ )
[19.05.04 14:49:35:413 CEST] 4c99a25 SystemErr R
readTable::SQLException: General error, message from server: "No Database
Selected"
[19.05.04 14:49:35:413 CEST] 4c99a25 SystemErr R
readAllPerson::SQLState: S1000
[19.05.04 14:49:35:443 CEST] 4c99a25 SystemErr R
readTable::VendorError: 1046
** readTable is my method to read all table entries. My database name is
AW_Test.
** ?? Where do I specifiy the database name to connect to ??
without pooling I used conn =
DriverManager.getConnection("jdbc:mysql://localhost/AW_Test"); and
everything worked fine !
** My configuration:
I added the datasource with the following entries:
JDBC Provider:
Implementing
class=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
DataSource:
Name: AW_Test
JNDI-Name: jdbc/mysql
Datasource Helper: com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
No Resource definitions (because I do not know which to add!)
** In my webproject web.xml I added the following:
Resource:
Typ javax.sql.DataSource
shareable
JNDI-Name: jdbc/mysql
And I also added the same entries for Resourve-Environment.
** My java-testcode is the following:
class properties:
private Connection conn;
private DataSource ds;
private void getConnection() throws SQLException {
try {
Properties parms = new Properties();
parms.setProperty(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.websphere.naming.WsnInitialContextFactory");
Context ctx = new InitialContext(parms);
ds = (DataSource) ctx.lookup("jdbc/mysql");
} catch...
try {
conn = ds.getConnection();
} catch...
}
** I really appreciate your help, because I nearly searched the whole www
and did not get any usable hints !
Thanx !!!
ALEX
Joacim J
news:440718295.1085146227083.JavaMail.wasadmin@swg3ws006:
> Hi, I've had problems with the MySQL ConnectionPool.
> I'm using WSAD 5.1, MySQL 4.0.18, mySQLConnector/J 3.0.12 (and tried
> 3.1.1 alpha). I'm also using JAAS.
>
---snip---
>
> [21/05/04 13:48:53:827 BST] 6eb56d12 SystemErr R
> java.sql.SQLException: General error, message from server: "No
> Database Selected"
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1962)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1257)
> at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2239)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:2187)
> at com.mysql.jdbc.Statement.executeQuery(Statement.java:1158)
> at
> com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeQuery(Statem
> entWrapper.java:617)
>
---snip---
>
> How do fix it?
Hi,
I just had the same problem and used the same mySQLConnector/J 3.0.12 but
my collegue had a working server with version 3.0.9. Replaced the driver
and it works like a dream.
Test this driver!!
--
Regards
Joacim J