PostgreSQL and Glassfish tables EJB__TIMER__TBL
I am trying to use the timer service provided by Glassfish. Thus, I need to create a table named EJB__TIMER__TBL
and set up a jdbc resource in Glassfish.
I want to store this table in postgreSQL in a schema named glassfish
. So my ddl is this (I replace the type BLOB
with BYTEA
):
CREATE SCHEMA glassfish;
CREATE TABLE glassfish.EJB__TIMER__TBL (
CREATIONTIMERAW BIGINT NOT NULL,
BLOB BYTEA,
TIMERID VARCHAR(255) NOT NULL,
CONTAINERID BIGINT NOT NULL,
OWNERID VARCHAR(255) NULL,
STATE INTEGER NOT NULL,
PKHASHCODE INTEGER NOT NULL,
INTERVALDURATION BIGINT NOT NULL,
INITIALEXPIRATIONRAW BIGINT NOT NULL,
LASTEXPIRATIONRAW BIGINT NOT NULL,
SCHEDULE VARCHAR(255) NULL,
APPLICATIONID BIGINT NOT NULL,
CONSTRAINT PK_EJB__TIMER__TBL PRIMARY KEY (TIMERID)
);
DROP ROLE IF EXISTS glassfish;
CREATE ROLE glassfish WITH NOINHERIT LOGIN PASSWORD '...';
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA glassfish FROM glassfish;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA glassfish FROM glassfish;
GRANT USAGE ON SCHEMA glassfish TO glassfish;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA glassfish TO glassfish;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA glassfish TO glassfish;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA glassfish TO glassfish;
ALTER USER glassfish SET search_path to 'glassfish';
I configured the jdbc pool and resource for Glassfish:
asadmin create-jdbc-connection-pool
--datasourceclassname org.postgresql.ds.PGConnectionPoolDataSource
--restype javax.sql.ConnectionPoolDataSource
--property User=glassfish:Password=...:PortNumber=5432:DatabaseName=...:ServerName=localhost jdbc/simPool/glassfish
asadmin create-jdbc-resource --connectionpoolid jdbc/simPool/glassfish jdbc/sim/glassfish
And I will correctly inject jdbc/sim/glassfish
into the jdbc resource that will be used for the timer service in the Glassish GUI.
Every time I deploy my application, I get an Exception:
[#|2013-02-18T11:42:42.562+0100|WARNING|glassfish3.1.2|org.eclipse.persistence.session.file
:/E:/softs/serveurs/glassfish3_1122/glassfish/domains/domain1/applications/ejb-timer-service-app/WEB-INF/classes/___EJB__Timer__App|_ThreadID=58;_ThreadName=Thread-2;|Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERREUR: la relation Β« EJB__TIMER__TBL Β» n'existe pas
Position : 193
Error Code: 0
Call: SELECT "TIMERID", "APPLICATIONID", "BLOB", "CONTAINERID", "CREATIONTIMERAW", "INITIALEXPIRATIONRAW", "INTERVALDURATION", "LASTEXPIRATIONRAW", "OWNERID", "PKHASHCODE", "SCHEDULE", "STATE" FROM "EJB__TIMER__TBL" WHERE (("OWNERID" = ?) AND ("STATE" = ?))
bind => [2 parameters bound]
Query: ReadAllQuery(name="findTimersByOwnerAndState" referenceClass=TimerState sql="SELECT "TIMERID", "APPLICATIONID", "BLOB", "CONTAINERID", "CREATIONTIMERAW", "INITIALEXPIRATIONRAW", "INTERVALDURATION", "LASTEXPIRATIONRAW", "OWNERID", "PKHASHCODE", "SCHEDULE", "STATE" FROM "EJB__TIMER__TBL" WHERE (("OWNERID" = ?) AND ("STATE" = ?))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
So my table is EJB__TIMER__TBL
not available to Glassfish.
When I create another project, set up the file persistence.xml
with the same credentials as my merged link above and create a simple query SELECT COUNT(*) FROM EJB__TIMER__TBL
I get 0
, so my link is well established and access to the default schema is glassfish
as expected.
There ${glassfish_root}\glassfish\lib\install\databases
are some ddls in there, but none for postgresql ... so where am I doing wrong?
NB: when I test setting up a service timer with MySQL jdbc resource it works ...
thanks for the help
source to share
Ok I found a solution to my problem.
I didn't know what SQL might be case sensitive
. Glassfish calls SELECT ... FROM "EJB__TIMER__TBL"
double quotes, so I need to create a table named "EJB__TIMER__TBL" and not "ejb__timer__tbl" or whatever.
The workaround is just to recreate the double quoted table:
CREATE TABLE glassfish."EJB__TIMER__TBL" (
"CREATIONTIMERAW" BIGINT NOT NULL,
"BLOB" BYTEA,
"TIMERID" VARCHAR(255) NOT NULL,
"CONTAINERID" BIGINT NOT NULL,
"OWNERID" VARCHAR(255) NULL,
"STATE" INTEGER NOT NULL,
"PKHASHCODE" INTEGER NOT NULL,
"INTERVALDURATION" BIGINT NOT NULL,
"INITIALEXPIRATIONRAW" BIGINT NOT NULL,
"LASTEXPIRATIONRAW" BIGINT NOT NULL,
"SCHEDULE" VARCHAR(255) NULL,
"APPLICATIONID" BIGINT NOT NULL,
CONSTRAINT "PK_EJB__TIMER__TBL" PRIMARY KEY ("TIMERID")
);
source to share