Re: Problem in trying to get a Restricted Table Function loaded
Scratch that. Looks like you have already logged DERBY-5352 to track
this issue and you have found the problem. I should have just stayed
calm and continued wading through the email which piled up while I was
on vacation. Thanks, Brett.
On 8/1/11 1:31 PM, Rick Hillegas wrote:
Hi Brett,
This looks like a bug to me. I have logged DERBY-5366 to track this.
Thanks for finding this problem,
-Rick
On 7/25/11 6:23 AM, Bergquist, Brett wrote:
In case this got missed as I could really use the help. Just need to
know what I need to do to get the VYI classes to be accessible to my
table function. As a straight table function it registers and works
fine but as soon as I reference something like VTITemplate it fails
saying it could not find the class.
Brett Bergquist
Principal Software Engineer
Phone: 203 888 7914 Ext 204
Email: bbergqu...@canoga.com
- Reply message -
From: Bergquist, Brett bbergqu...@canoga.com
Date: Sun, Jul 24, 2011 11:19 am
Subject: Problem in trying to get a Restricted Table Function loaded
To: derby-dev@db.apache.org derby-dev@db.apache.org
My class looks like:
public class NpaResultsTableFunction extends VTITemplate implements
RestrictedVTI {
public static NpaResultsTableFunction read() throws SQLException {
return new NpaResultsTableFunction();
}
public NpaResultsTableFunction() throws SQLException {
conn = DriverManager.getConnection(jdbc:default:connection);
}
...
}
I have defined the function using the following where
csem-pcs-derby-jar is the location of the JAR file containing this
table function:
CALL SQLJ.INSTALL_JAR('${csem-pcs-derby-jar}',
'PCS_V1.pcsderby', 0);
SET SCHEMA PCS_V1;
CREATE FUNCTION NPARESULTS_TABLE () RETURNS TABLE
(
ID BIGINT,
REPORTKEY CHAR(56),
MASTERIP CHAR(4) FOR BIT DATA,
BOOTCOUNT INTEGER,
TESTRESULTID BIGINT,
PROFILEREFID INTEGER,
ADDRESSREFID INTEGER,
STARTDATETIME TIMESTAMP,
ACCURACYLEVEL SMALLINT,
RESULTFLAG SMALLINT,
PACKETSSENT INTEGER,
ROUNDTRIPPACKETS INTEGER,
DROPPEDPACKETS INTEGER,
OUTOFORDERPACKETS INTEGER,
MINROUNDTRIPLATENCY INTEGER,
MAXROUNDTRIPLATENCY INTEGER,
TOTALROUNDTRIPLATENCY BIGINT,
AVGROUNDTRIPLATENCY INTEGER,
LATENCYBUCKETVALUE1 INTEGER,
LATENCYBUCKETVALUE2 INTEGER,
LATENCYBUCKETVALUE3 INTEGER,
LATENCYBUCKETVALUE4 INTEGER,
LATENCYBUCKETVALUE5 INTEGER,
LATENCYBUCKETVALUE6 INTEGER,
LATENCYBUCKETVALUE7 INTEGER,
LATENCYBUCKETVALUE8 INTEGER,
LATENCYBUCKETVALUE9 INTEGER,
LATENCYBUCKETVALUE10 INTEGER,
JITTERMEASUREMENT INTEGER,
MINLOCALREMOTEJITTER INTEGER,
MAXLOCALREMOTEJITTER INTEGER,
TOTALLOCALREMOTEJITTER BIGINT,
AVGLOCALREMOTEJITTER INTEGER,
LOCALREMOTEJITTERBUCKETVALUE1 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE2 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE3 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE4 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE5 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE6 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE7 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE8 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE9 INTEGER,
MINREMOTELOCALJITTER INTEGER,
MAXREMOTELOCALJITTER INTEGER,
TOTALREMOTELOCALJITTER BIGINT,
AVGREMOTELOCALJITTER INTEGER,
REMOTELOCALJITTERBUCKETVALUE1 INTEGER,
REMOTELOCALJITTERBUCKETVALUE2 INTEGER,
REMOTELOCALJITTERBUCKETVALUE3 INTEGER,
REMOTELOCALJITTERBUCKETVALUE4 INTEGER,
REMOTELOCALJITTERBUCKETVALUE5 INTEGER,
REMOTELOCALJITTERBUCKETVALUE6 INTEGER,
REMOTELOCALJITTERBUCKETVALUE7 INTEGER,
REMOTELOCALJITTERBUCKETVALUE8 INTEGER,
REMOTELOCALJITTERBUCKETVALUE9 INTEGER,
CIRCUIT1REFID INTEGER,
CIRCUIT2REFID INTEGER
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME
'com.canoga.derby.fcn.NpaResultsTableFunction.read';
CALL
Re: Problem in trying to get a Restricted Table Function loaded
Hi Brett,
This looks like a bug to me. I have logged DERBY-5366 to track this.
Thanks for finding this problem,
-Rick
On 7/25/11 6:23 AM, Bergquist, Brett wrote:
In case this got missed as I could really use the help. Just need to
know what I need to do to get the VYI classes to be accessible to my
table function. As a straight table function it registers and works
fine but as soon as I reference something like VTITemplate it fails
saying it could not find the class.
Brett Bergquist
Principal Software Engineer
Phone: 203 888 7914 Ext 204
Email: bbergqu...@canoga.com
- Reply message -
From: Bergquist, Brett bbergqu...@canoga.com
Date: Sun, Jul 24, 2011 11:19 am
Subject: Problem in trying to get a Restricted Table Function loaded
To: derby-dev@db.apache.org derby-dev@db.apache.org
My class looks like:
public class NpaResultsTableFunction extends VTITemplate implements
RestrictedVTI {
public static NpaResultsTableFunction read() throws SQLException {
return new NpaResultsTableFunction();
}
public NpaResultsTableFunction() throws SQLException {
conn = DriverManager.getConnection(jdbc:default:connection);
}
...
}
I have defined the function using the following where
csem-pcs-derby-jar is the location of the JAR file containing this
table function:
CALL SQLJ.INSTALL_JAR('${csem-pcs-derby-jar}',
'PCS_V1.pcsderby', 0);
SET SCHEMA PCS_V1;
CREATE FUNCTION NPARESULTS_TABLE () RETURNS TABLE
(
ID BIGINT,
REPORTKEY CHAR(56),
MASTERIP CHAR(4) FOR BIT DATA,
BOOTCOUNT INTEGER,
TESTRESULTID BIGINT,
PROFILEREFID INTEGER,
ADDRESSREFID INTEGER,
STARTDATETIME TIMESTAMP,
ACCURACYLEVEL SMALLINT,
RESULTFLAG SMALLINT,
PACKETSSENT INTEGER,
ROUNDTRIPPACKETS INTEGER,
DROPPEDPACKETS INTEGER,
OUTOFORDERPACKETS INTEGER,
MINROUNDTRIPLATENCY INTEGER,
MAXROUNDTRIPLATENCY INTEGER,
TOTALROUNDTRIPLATENCY BIGINT,
AVGROUNDTRIPLATENCY INTEGER,
LATENCYBUCKETVALUE1 INTEGER,
LATENCYBUCKETVALUE2 INTEGER,
LATENCYBUCKETVALUE3 INTEGER,
LATENCYBUCKETVALUE4 INTEGER,
LATENCYBUCKETVALUE5 INTEGER,
LATENCYBUCKETVALUE6 INTEGER,
LATENCYBUCKETVALUE7 INTEGER,
LATENCYBUCKETVALUE8 INTEGER,
LATENCYBUCKETVALUE9 INTEGER,
LATENCYBUCKETVALUE10 INTEGER,
JITTERMEASUREMENT INTEGER,
MINLOCALREMOTEJITTER INTEGER,
MAXLOCALREMOTEJITTER INTEGER,
TOTALLOCALREMOTEJITTER BIGINT,
AVGLOCALREMOTEJITTER INTEGER,
LOCALREMOTEJITTERBUCKETVALUE1 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE2 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE3 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE4 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE5 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE6 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE7 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE8 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE9 INTEGER,
MINREMOTELOCALJITTER INTEGER,
MAXREMOTELOCALJITTER INTEGER,
TOTALREMOTELOCALJITTER BIGINT,
AVGREMOTELOCALJITTER INTEGER,
REMOTELOCALJITTERBUCKETVALUE1 INTEGER,
REMOTELOCALJITTERBUCKETVALUE2 INTEGER,
REMOTELOCALJITTERBUCKETVALUE3 INTEGER,
REMOTELOCALJITTERBUCKETVALUE4 INTEGER,
REMOTELOCALJITTERBUCKETVALUE5 INTEGER,
REMOTELOCALJITTERBUCKETVALUE6 INTEGER,
REMOTELOCALJITTERBUCKETVALUE7 INTEGER,
REMOTELOCALJITTERBUCKETVALUE8 INTEGER,
REMOTELOCALJITTERBUCKETVALUE9 INTEGER,
CIRCUIT1REFID INTEGER,
CIRCUIT2REFID INTEGER
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME
'com.canoga.derby.fcn.NpaResultsTableFunction.read';
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','CSEM.csemderby:PCS_V1.pcsderby');
All of this is okay and before I tried to make this a restricted table
function, worked correctly. All the above loads into the database
correct right now, but when I execute:
select * from table
Re: Problem in trying to get a Restricted Table Function loaded
In case this got missed as I could really use the help. Just need to know what
I need to do to get the VYI classes to be accessible to my table function. As
a straight table function it registers and works fine but as soon as I
reference something like VTITemplate it fails saying it could not find the
class.
Brett Bergquist
Principal Software Engineer
Phone: 203 888 7914 Ext 204
Email: bbergqu...@canoga.com
- Reply message -
From: Bergquist, Brett bbergqu...@canoga.com
Date: Sun, Jul 24, 2011 11:19 am
Subject: Problem in trying to get a Restricted Table Function loaded
To: derby-dev@db.apache.org derby-dev@db.apache.org
My class looks like:
public class NpaResultsTableFunction extends VTITemplate implements
RestrictedVTI {
public static NpaResultsTableFunction read() throws SQLException {
return new NpaResultsTableFunction();
}
public NpaResultsTableFunction() throws SQLException {
conn = DriverManager.getConnection(jdbc:default:connection);
}
...
}
I have defined the function using the following where csem-pcs-derby-jar is
the location of the JAR file containing this table function:
CALL SQLJ.INSTALL_JAR('${csem-pcs-derby-jar}',
'PCS_V1.pcsderby', 0);
SET SCHEMA PCS_V1;
CREATE FUNCTION NPARESULTS_TABLE () RETURNS TABLE
(
ID BIGINT,
REPORTKEY CHAR(56),
MASTERIP CHAR(4) FOR BIT DATA,
BOOTCOUNT INTEGER,
TESTRESULTID BIGINT,
PROFILEREFID INTEGER,
ADDRESSREFID INTEGER,
STARTDATETIME TIMESTAMP,
ACCURACYLEVEL SMALLINT,
RESULTFLAG SMALLINT,
PACKETSSENT INTEGER,
ROUNDTRIPPACKETS INTEGER,
DROPPEDPACKETS INTEGER,
OUTOFORDERPACKETS INTEGER,
MINROUNDTRIPLATENCY INTEGER,
MAXROUNDTRIPLATENCY INTEGER,
TOTALROUNDTRIPLATENCY BIGINT,
AVGROUNDTRIPLATENCY INTEGER,
LATENCYBUCKETVALUE1 INTEGER,
LATENCYBUCKETVALUE2 INTEGER,
LATENCYBUCKETVALUE3 INTEGER,
LATENCYBUCKETVALUE4 INTEGER,
LATENCYBUCKETVALUE5 INTEGER,
LATENCYBUCKETVALUE6 INTEGER,
LATENCYBUCKETVALUE7 INTEGER,
LATENCYBUCKETVALUE8 INTEGER,
LATENCYBUCKETVALUE9 INTEGER,
LATENCYBUCKETVALUE10 INTEGER,
JITTERMEASUREMENT INTEGER,
MINLOCALREMOTEJITTER INTEGER,
MAXLOCALREMOTEJITTER INTEGER,
TOTALLOCALREMOTEJITTER BIGINT,
AVGLOCALREMOTEJITTER INTEGER,
LOCALREMOTEJITTERBUCKETVALUE1 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE2 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE3 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE4 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE5 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE6 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE7 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE8 INTEGER,
LOCALREMOTEJITTERBUCKETVALUE9 INTEGER,
MINREMOTELOCALJITTER INTEGER,
MAXREMOTELOCALJITTER INTEGER,
TOTALREMOTELOCALJITTER BIGINT,
AVGREMOTELOCALJITTER INTEGER,
REMOTELOCALJITTERBUCKETVALUE1 INTEGER,
REMOTELOCALJITTERBUCKETVALUE2 INTEGER,
REMOTELOCALJITTERBUCKETVALUE3 INTEGER,
REMOTELOCALJITTERBUCKETVALUE4 INTEGER,
REMOTELOCALJITTERBUCKETVALUE5 INTEGER,
REMOTELOCALJITTERBUCKETVALUE6 INTEGER,
REMOTELOCALJITTERBUCKETVALUE7 INTEGER,
REMOTELOCALJITTERBUCKETVALUE8 INTEGER,
REMOTELOCALJITTERBUCKETVALUE9 INTEGER,
CIRCUIT1REFID INTEGER,
CIRCUIT2REFID INTEGER
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME
'com.canoga.derby.fcn.NpaResultsTableFunction.read';
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','CSEM.csemderby:PCS_V1.pcsderby');
All of this is okay and before I tried to make this a restricted table
function, worked correctly. All the above loads into the database correct
right now, but when I execute:
select * from table (PCS_V1.NPARESULTS_TABLE()) s
I get:
Error: The class 'com.canoga.derby.fcn.NpaResultsTableFunction' does not exist
or is inaccessible. This can happen if the class is not public.
SQLState:
