Re: Connection issues with Windows Server 2016
> Am 17.05.2017 um 09:45 schrieb julian.rehb...@de.gbs.com: > > Thats the problem, I don't any error message/stacktrace when it stops > working. > It looks like the Derby complete freeze after. Sometimes after 10 seconds, > sometimes after 10 minutes... Hi, Sounds like this is related to the Java and/or Windows Version. Do you use OpenJDK or what JVM vendor are you using? Something you can only probably find out using Windows in debug mode with WinDbg. Getting stack traces of the running JVM of all threads may help. Maybe trigger a mini dump of the JVM process under Windows will help. With kind regards Thomas > We are using the Derby in network server mode. > It is configured as datasource in WildFly 9.0.1 > Our application runs on this WildFly and connecting to the Derby via > Hibernate. > > The setup with Windows Server 2012 instead of Windows Server 2016 work fine. > The setup with Windows Server 2016 and MS SQL instead of Derby works fine. > But Windows Server 2016 with Derby don't work. > Julian Rehborn > Junior Software Engineer > > > GROUP Business Software Europa GmbH > Im Dörener Feld 3 > 33100 Paderborn > Germany > Phone:+49 52 51 31 02-0 > Fax: +49 52 51 31 02-99 > www.gbs.com > > > -- > GBS Disclaimer > automatically added by GBS Email Management Solutions > > European Headquarters: > GROUP Business Software Europa GmbH > Ottostrasse 4 > 76227 Karlsruhe > Germany > > Phone: +49 721 4901-0 > Fax: +49 721 4901-199 > E-Mail: i...@gbs.com > Internet: http://www.gbs.com > > Court Registration: Amtsgericht (Local Court) Mannheim HRB 725322 > VAT identification number under § 27a of the German Value Added Tax Act > (UStG): DE301913584 > Management of the Company: Marion Betz, Constanze Zarth > > > > > > > From:Bryan Pendleton > To:Derby Discussion > Date:09.05.2017 15:44 > Subject:Re: Connection issues with Windows Server 2016 > > > > What sort of error do you get? Provide as much detail as you can. > > thanks, > > bryan > smime.p7s Description: S/MIME cryptographic signature
Re: SQL help needed
Am 27. November 2016 11:13:33 MEZ, schrieb John English : >I'm trying to find all rows in a table where a pair of values is not in > >anther table: that is, I want to do something like this: > > SELECT * FROM x WHERE (a,b) NOT IN (SELECT DISTINCT a,b FROM y); > >which of course doesn't work. > >At the moment I've bodged around it by doing this: > > SELECT * FROM x WHERE a||'-'||b NOT IN (SELECT DISTINCT a||'-'||b >FROM y); > >but this strikes me as really ugly. Can anyone a more elegant way to >get >what I want? > >TIA, Hi, Can this problem be solved by an correlating sub select?! Maybe something like this: Select * from x where not exists ( select 1 from y where x.a = y.a and x.b = y.b ) Or like your first SQL but convert to string and concatenate that would work but probably very slow as no index could be used. With kind regards Thomas
EmbeddedDriver and db.lck file
Hi, I have a Servlet running under Jetty 9.2.11 which uses EclipseLink 2.6as JPA tool. In the JPA tool I did configure the usage of the Derby Embedded 10.11.1 driver. For a fresh start of the jetty server everything works as expected. But when I now redeploy the context config XML file after I did update the referenced war file, derby begins to tell me that another instance did already boot the database. Somehow the db.lck file is not released when I close the EntityManagerFactory. Any idea what's going on here? How can I force the release of the db.lck file in a ServletListener.contextDestroyed() method? With kind regards Thomas
Re: XSDB6 Another instance of Derby may have already booted
Many Thanks Rick - deleting the ".lck" files in the root directory solved the issue.
XSDB6 Another instance of Derby may have already booted
Hi, I am getting below error even after the derby server and the server machine had been shutdown and brought up again. Might this indicate the data base is corrupt? Is ther any way to further diagnose what is going on? Thanks java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'PMT' with class loader sun.misc.Launcher$AppClassLoader@77cde100, see the next exception for details.::SQLSTATE: XSDB6Another instance of Derby may have already booted the database /webs/web1540/pmtdata/PMT. at java.util.concurrent.FutureTask.report(Unknown Source) at java.util.concurrent.FutureTask.get(Unknown Source) at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132) at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand.access$100(OpenConnectionCommand.java:45) at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand$2.run(OpenConnectionCommand.java:115) at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.RuntimeException: java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'PMT' with class loader sun.misc.Launcher$AppClassLoader@77cde100, see the next exception for details.::SQLSTATE: XSDB6Another instance of Derby may have already booted the database /webs/web1540/pmtdata/PMT. at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171) at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand.access$000(OpenConnectionCommand.java:45) at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand$1.run(OpenConnectionCommand.java:104) ... 5 more Caused by: java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'PMT' with class loader sun.misc.Launcher$AppClassLoader@77cde100, see the next exception for details.::SQLSTATE: XSDB6Another instance of Derby may have already booted the database /webs/web1540/pmtdata/PMT. at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.jdbc.ClientDriver.connect(Unknown Source) at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager. getConnection(SQLDriverManager.java:133) at net.sourceforge.squirrel_sql.client.mainframe.action. OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167) ... 7 more Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'PMT' with class loader sun.misc.Launcher$AppClassLoader@77cde100, see the next exception for details.::SQLSTATE: XSDB6Another instance of Derby may have already booted the database /webs/web1540/pmtdata/PMT. at org.apache.derby.client.am.Connection.completeSqlca(Unknown Source) at org.apache.derby.client.net.NetConnectionReply. parseRdbAccessFailed(Unknown Source) at org.apache.derby.client.net.NetConnectionReply. parseAccessRdbError(Unknown Source) at org.apache.derby.client.net.NetConnectionReply. parseACCRDBreply(Unknown Source) at org.apache.derby.client.net.NetConnectionReply. readAccessDatabase(Unknown Source) at org.apache.derby.client.net.NetConnection. readSecurityCheckAndAccessRdb(Unknown Source) at org.apache.derby.client.net.NetConnection. flowSecurityCheckAndAccessRdb(Unknown Source) at org.apache.derby.client.net.NetConnection.flowUSRIDPWDconnect(Unknown Source) at org.apache.derby.client.net.NetConnection.flowConnect(Unknown Source) at org.apache.derby.client.net.NetConnection.(Unknown Source) at org.apache.derby.client.net.NetConnection40.(Unknown Source) at org.apache.derby.client.net.ClientJDBCObjectFactoryImpl40. newNetConnection(Unknown Source) ... 10 more
Re: placement of derby.log
Thomas writes: > > > Hi, > > is there any way to tell Derby where to store the derby.log file? > (note: I am not referring to the database log file here, but to the file > into which protocoll messages are logged). > > If not, what is the logic used to determine where this is stored? > > Thanks > > to add: I am talking about an embedded driver use case here and this is what is stored in the log file: Mon Jun 10 13:24:02 GMT+01:00 2013: Booting Derby version The Apache Software Foundation - Apache Derby - 10.9.1.0 - (1344872): instance a816c00e-013f-2e0b-058a-02303778 on database directory H:\PMT20130608 \configuration\net.thmb.pmt\data\PMTedb with class loader org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader@aae86e Loaded from file:/H:/PMT20130608/configuration/org.eclipse.osgi/bundles/11/1/.cp/derby. jar java.vendor=Sun Microsystems Inc. java.runtime.version=1.6.0_18-b07 user.dir=H:\PMT20130608 derby.system.home=null Database Class Loader started - derby.database.classpath=''
placement of derby.log
Hi, is there any way to tell Derby where to store the derby.log file? (note: I am not referring to the database log file here, but to the file into which protocoll messages are logged). If not, what is the logic used to determine where this is stored? Thanks
Re: Native authentication and password expiry date
Rick Hillegas writes:
I am running the 10.9.1.0 server using the basic security manager with the
default security policy settings.
Trying to inspect the system property settings from a stored procedure which
includes using java.lang.System.getProperty() I am getting:
Error: The exception 'java.security.AccessControlException: access denied
("java.util.PropertyPermission"
"derby.authentication.native.passwordLifetimeMillis" "read")' was thrown
while evaluating an expression.
SQLState: 38000
ErrorCode: -1
Error: Java exception: 'access denied ("java.util.PropertyPermission"
"derby.authentication.native.passwordLifetimeMillis" "read"):
java.security.AccessControlException'.
SQLState: XJ001
ErrorCode: 9
I tried adding the line
permission java.util.PropertyPermission "java.lang.System.getProperty",
"read";
to the security.policy file and restarted the server, but I am still getting
the same error.
Can someone please advise which addition/change to security.policy is
exactely needed to prevent the error message? Would also be interested in
getting a hint on security risk behind that I should consider prior to
making that change? (if there would be none, I would suspect reading the
system properties would be possible using the dafult configuration).
Many thanks in advance.
error when selecting from sys.sysusers
Hi, when issuing a SELECT username, lastmodified FROM sys.sysusers; the statement returns a resultset as expected when issuing a SELECT username, lastmodified FROM sys.sysusers WHERE USERNAME = CURRENT_USER; an error is returned: Error: No one can view the 'SYSUSERS'.'PASSWORD' column. SQLState: 4251E ErrorCode: -1 But I am not trying to select the password column!? Thanks Thomas
Re: Native authentication and password expiry date
Hi Rick,
thanks for pointing me to the lastmodified column on the sysusers table.
I think your query tells me how many days ago the password has been changed.
So would I need to compare this to the property
derby.authentication.native.passwordLifetimeMillis
to get to know the remaining days?
In my installation I have not changed this property which by default I
understand is a system-wide property set to 31 days.
Is there a system function which allows me to retrieve the value of a
system-wide property?
as I think the approach needed might be to first check if the default of the
property has been changed at database level by executing
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY
('derby.authentication.native.passwordLifetimeMillis');
If this returns null (what it does in my installation) I know the property
is set at system level. But how do I then retrieve the value of a system
property? I have not found a system procedure for this.
Thanks for looking into this again.
Kind regards
Thomas
Native authentication and password expiry date
Hi, is there a way to find out when the password of the user logged in will expire so an application can display a message like "Your password will expire in x days. Do you want to change it now?" Thanks
Re: SSL peerAuthentication
Dag, I am not using CA certs, but self-signed certificates. Would you mind having a look at my server truststore file if I email it to you? Kind regards Thomas
Re: SSL peerAuthentication
Ups - had attached this answer to the wrong mail tread first. Thanks. Let me know if you want me to share with you the keystores used or the scripts run for starting the server/ij.
Re: Create embedded, run via network server: what's databaseName?
Thanks. Let me know if you want me to share with you the keystores used or the scripts run for starting the server/ij.
Re: SSL peerAuthentication
yes, I did check the docs. My serverTrustStore was populated as described in the manual, i.e. "Install a client certificate in the server's trust store: keytool -import -alias aDerbyClient -file aClient.cert -keystore serverTrustStore.key" As said my expectation was installing just the certificate of the client(s) would suffice (as per my use case 3a). And this way the set-up of the serverTrustStore would be achieved the same way as and be consistent with how this is done for the clientTrustStore (and as said my use case 2 below where only the client requests peer Authentication works). But in case of the serverTrustStore and the server requesting peerAuthentication this only works after importing the CA certificate into the serverTrustStore as well - BUT!! then *any* client certificate signed by this CA seems to work - even if the client certificate is not part of the truststore. In fact it already works if the CA certificate is the *only* certificate in the servertruststore. It is unclear to me why the CA certificate needs to be imported into the truststore - imho this should not be necessary.
SSL peerAuthentication
Hi, currently trying to switch from basic authentication to peer Authentication, but having trouble with understanding serverTrustStore content. Have gone through the following scenarios: 1) in my current set-up I am starting the network server and the client(s) with basic authentication - this works fine 2) left the server starting with requesting basic authentication, but changed my client to request peerAuthentication - this works fine (so the additional clientTrustStore file is set-up correctly) 3) changed set-up so both server and clients request peer Authentication a) when importing just the trusted client certificate into the serverTrustStore I am getting a communication error - my assumption was this import is all needed for this file b) when importing the key pair of the client certificate into the serverTrustStore I am getting a communications error as well c) when importing two trusted certificates (not key pairs) into the serverTrustStore, i.e. the trusted client certificate and the certificate of the signing CA no error is thrown and I can access data, BUT this is true not only when using the trusted certificate imported into the truststore, but surprisingly also for other certificates signed by this CA. -> So how do I need to do the set-up so that peerAuthentication is activated and restricts data access only to those client certificates that I have imported into the truststore? Thanks
best practice - SQL Routines - return value handling
have written a couple of SQL Routines for Derby and am recently looking into possibly re-using some of the code on another DBMS system also supporting Java (PostgreSQL pl/Java). pl/Java for me is more a playground and subject of curiosity then a real intention to use it productively at this stage. Need to say the promise of Java in the data base is - from a portability point of view - limited as my observations so far show implementations are back end specific in many cases. Maybe Apache Derby and Oracle would be more compatible, but Derby and PostgreSQL are not too often. However when there are multiple implementation approaches possible, one might be favoured over the other when portability is considered a requirement. Specific question: If there is a need for returning multiple out parameters from a routine (just one row, not a set of rows), one might choose to implement a procedure in Derby like this: CREATE PROCEDURE xy(IN CLIENTID integer, OUT LASTNAME varchar(30), OUT FIRSTNAME varchar(30) or CREATE PROCEDURE xy(IN CLIENTID integer) DYNAMIC RESULT SET 1 Is one of these approaches to be favoured over the other? What is the best pratice here? Any one to be favoured over the other when having Oracle portability in mind? Thanks a lot for sharing your experience and advise. Kind regards Thomas
export from systables
Hi, I am trying to export a comma separated list from the syscatalog tables to a file which holds the following information: schemaname, tablename, columnname, columdatatype and javadatatype. e.g. appl, mytable, column2, char(1), java.lang.String appl, mytable, column2, integer, java.lang.Integer Is this possible using IJ and export query system function? Thanks
Re: trigger calling a procedure
> Hi Thomas,
>
> I think you'd need to add a REFERENCING clause to the trigger definition
> and pass in the new value as an argument to the procedure. Something
> like:
>
> CREATE TRIGGER "TR_XY"
> AFTER INSERT
> ON "TBL_XY"
> REFERENCING NEW AS NEW
> FOR EACH ROW
> CALL "SP_xy"('xyz', 0, NEW."RowID")
>
> Hope this helps,
>
Hi,
referencing NEW as NEW I could add although not sure why this is needed
(e.g. NEW as UPDATEROW or something, but NEW as NEW?).
Is passing the value in the only option?
For compatibility with how other data
base backends (PostgreSQL) do this I would prefer
if there would be an option to access the value
from within the procedure.
Can someone confirm whether this is possible or not in Derby please?
Many thanks.
Thomas
Re: trigger calling a procedure
Rick Hillegas writes:
>
> On 10/21/12 11:45 PM, Thomas Hill wrote:
> > CREATE TRIGGER "TR_XY"
> >AFTER INSERT
> >ON "TBL_XY"
> >FOR EACH ROW
> >CALL PROCEDURE "SP_xy"('xyz', 0);
> Hi Thomas,
>
> That's almost correct. If you remove the keyword PROCEDURE from the
> triggered statement, then it will be a valid SQL statement. The
> following would work:
>
> CREATE TRIGGER "TR_XY"
>AFTER INSERT
>ON "TBL_XY"
>FOR EACH ROW
>CALL "SP_xy"('xyz', 0);
>
> Hope this helps,
> -Rick
>
>
thanks for that! working now.
Next challenge for me is to figure out if and how the NEW values can be read and
stored in a variable in the java code?
Tried
int i = NEW."RowID";
to store the new value of column "RowID" in a variable, but the java compiler
already complains about that (NEW cannot be resolved to a type / class
cannot be resolved to a type / syntax error on token "RowID" : class expected)
Many thanks
Thomas
trigger calling a procedure
Hello,
the Wiki mentions a trigger can also call a procedure rather than scripting the
SQL statement on the create trigger statement. The documentation as far as I can
see does not give an example of that.
Would this be the right syntax?
CREATE TRIGGER "TR_XY"
AFTER INSERT
ON "TBL_XY"
FOR EACH ROW
CALL PROCEDURE "SP_xy"();
How about passing in parameters? would this change to:
CREATE TRIGGER "TR_XY"
AFTER INSERT
ON "TBL_XY"
FOR EACH ROW
CALL PROCEDURE "SP_xy"('xyz', 0);
Thanks
NATIVE authentication
Hi, I am trying create a database from scratch using the new native authentication provider introduced with version 10.9.1.0. What I tried after reading the docs was: 1st attempt) started the network server supplying -Dderby.authentication.provider=NATIVE:myCredDB as property passed on JVM command line. The server started up, but when trying to connect I am getting SQLState 4251I "Authentication cannot be performed because the credentials database '' does not exist." so my assumption the credentials database would be automatically created doesn't seem to be correct. 2nd attemtpt: started the network server without specifying an authentication provider and used ij to connect to server and created a database myCredDB manually. Shutdown the server and started it up again pointing at this manually created credentials database and tried to create an application database myAppDB. Trying to do this I am getting authentication not possible / invalid user. In both connection strings have I used the same user (dbo) and password (derby). Can someone please support and point me into the right direction or potentially share a sample script that shows how to create an application database which makes use of native authentication? Thanks a lot in advance Thomas
Returning java.sql.ResultSets from Java procedures
Hi,
can someone please share an example how the Client side application code to call
procedure looks like when I want my stored procedure to return TWO resultsets?
The derby Wiki (extract see below) says 'WORK IN PROGRESS' in the section where
documentation on this had been started.
CallableStatement cs = conn.prepareCall("{ call DRS2(?, ?)}");
cs.setInt(1, p1);
cs.setInt(2, p2);
cs.execute();
WORK IN PROGESS
Re: New developer, need a little help with a subquery
if I understand correctly you need a 'group by' in your query;
so like
SELECT day("dtmDateCompleted") as "Day",
"A"."strName" AS "Category",
COUNT(*)
FROM "tblAssetCategory" "AC"
INNER JOIN "tblAsset" "A" ON "AC"."id" = "A"."intCategoryID"
INNER JOIN "tblWorkOrder" "W" ON "W"."intAssetID" = "A"."id"
WHERE "dtmDateCompleted" IS NOT NULL
GROUP BY day("dtmDateCompleted"), "A"."strName"
Re: metadata / getClientInfo()
conn.getMetaData().getUserName() seems to be exactly what I have been looking for Thanks for this and also all the feedback I have received on my initial question Regards Thomas
Re: metadata / getClientInfo()
okay - true. I was however wondering and wanted to test whether I could replace a potentially in terms of performace more costly JDBC statement definition and result set processing of a 'select session_user from sysibm.sysdummy1' by an assumed less costly request of a property from a connection object I have at hand anway? Even if this would turn out to not be better in terms of performance, I think I would still like to understand how this getClientInfo() works. When using SQuirreL I can see a lot of connection properties / metadata displayed under their 'metadata' tab that I would suspect they are getting via such mechanisms. In the derby docs I am afraid one hardly finds details on the subject. Thanks Thomas
metadata / getClientInfo()
Hi,
I am on version 10.7.1.1 and have read manuals and searched the net trying to
find out how to obtain the *user* property used on the connectionURL from within
a stored procedure.
The attempt to use the connection object and getClientInfoProperty("user") just
returns null.
Thanks for your support
Thomas
Re: JDBC escape syntax
Thanks Knut for pointing me in the right direction.
JDBC escape syntax
Hi,
I would need help in understanding how to escape a quoted identifier in JDBC
as this seems to be different from how to do it in interactive SQL using ij.
What I am trying to do is to compare CURRENT_ROLE to constant string.
Scenario 1)
===
Please see the following output in which I am using ij and where I achieve what
I want, i.e. there is one row returned as the comparison in the
where clause is satisfied:
ij> connect 'jdbc:derby://localhost:1527/dummydb;bootPassword=xy;
user=dbo;password=derby;create=true';
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
('derby.database.sqlAuthorization', 'true');
Statement executed.
ij> connect 'jdbc:derby://localhost:1527/dummydb;shutdown=true;';
ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006,
SQLERRMC: Database 'dummydb' shutdown.
ij> disconnect;
ij> connect 'jdbc:derby://localhost:1527/dummydb;bootPassword=xy;
user=dbo;password=derby;create=true';
ij> create role db_reader;
0 rows inserted/updated/deleted
ij> set role db_reader;
0 rows inserted/updated/deleted
ij> select IBMREQD FROM SYSIBM.SYSDUMMY1 WHERE CURRENT_ROLE='"DB_READER"';
IBM&
Y
1 row selected ===> so success here!
ij> disconnect;
ij>
Scenario 2)
===
Now I would like the query to be embedded into a stored procedure.
However I am struggling to find out what the correct syntax might be here.
Here is my java code for the stored procedure:
public static void SP_getRole(String dummy[]) throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
Statement stmt = conn.createStatement();
String cSQL = "SELECT ibmreqd FROM sysibm.sysdummy1"+
" WHERE CURRENT_ROLE='\"DB_READER\"'";
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
System.out.println(rs.getString(1));
};
rs.close();
stmt.close();
return;
}
and the java stub to call the procedure:
try {
Connection conn = DriverManager.getConnection(connectionURL);
System.out.println("Successfully connected to Database");
Statement stmt = conn.createStatement();
String cSQL = "SET ROLE db_reader";
stmt.executeUpdate(cSQL);
stmt.close();
cSQL = "SELECT CURRENT_ROLE FROM SYSIBM.SYSDUMMY1 \n";
PreparedStatement ps = conn.prepareStatement(cSQL);
ResultSet rs = ps.executeQuery();
String rsString = "";
while (rs.next()) {
rsString = rs.getString(1);
System.out.println(rsString);
};
rs.close();
ps.close();
CallableStatement cstmt =
conn.prepareCall("{ CALL rte.\"SP_getRole\"(?) }");
cstmt.setString(1, "dummy");
cstmt.executeUpdate();
and the create procedure statement
CREATE PROCEDURE rte."SP_getRole"(OUT "vcRole" varchar(128))
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL SECURITY DEFINER
MODIFIES SQL DATA EXTERNAL NAME '...SP_getRole'
;
HOWEVER when running this now row is returned!
I also tried
" WHERE CURRENT_ROLE='\\\"DB_READER\\\"'";
but this also did not lead to success.
Any hints appreciated.
Thanks
Thomas
Derby Issue
Hi Team, We are using derby-10.4.1.3 in our application. Recently, we faced an issue in using this derby database with an exception java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested I would like to know under what scenario this exception could occur and if this issue is already addressed in any one of the derby buglist. Please refer the bug id or if you want to take into the programming level, please suggest the statement to avoid this issue. Thanks in advance, Remya
Re: Use variables in SQL script?
David, I am afraid Derby doesn't implement variables - I had been looking for this myself also some time back when writing Derby scripts for test data creation/manipulation. I finally ended up writing small Java programs where needed instead of or on top of just scripts. Since a couple of days I am using Squirel as front-end rather than ij and this tool has a script plug-in which sounds interesting, but I have not really looked into this plug-in yet whether it would provide any help when it comes to variables. When I asked basically the same question you just did to the derby community there was a mention that it should not be too hard to enhance ij to offer this support - however I have not raised a feature request and to the best of my knowledge this didn't go any further than the communication on the user mailing list itself. I would be willing to help document/test such feature, but can't program myself as I am not a programmer.
Re: Inaccuracies in H2's claims: Autocounter/Sequqnce-Numbers
Hi, > Derby performance is comparable to other open source databases > No claim such as "database x is slow". But you claim Derby performance is similar to other databases. The benchmark results were published at multiple large conferences, but the benchmark source code is not available, making it impossible to reproduce the results. This is not really scientific: http://en.wikipedia.org/wiki/Reproducibility "Reproducibility is one of the main principles of the scientific method, and refers to the ability of a test or experiment to be accurately reproduced, or replicated, by someone else working independently." I would really like to reproduce the results, to understand what you have tested exactly, and to find out how fast H2 is in those tests. > I would > still encourage people to do their own benchmarking when making > decisions on performance. Me too :-) Regards, Thomas
Re: Inaccuracies in H2's claims: Autocounter/Sequqnce-Numbers
Hi, The feature comparison on the H2 web site should be fixed now: http://h2database.com/html/features.html#comparison (you may need to refresh the page). When I wrote it, the features were not available in Derby yet. However, in some cases it was also clearly my fault because I wasn't clear what I meant (for example 'user defined data types' was the wrong expression; what I meant was 'domains' as in the 'create domain' statement). Also, I should have added the database versions, I have done that now. I hope the table is now more accurate, please tell me if not. It's not in my interest to mislead anybody, but it's also clear that the list can never be 'complete' (that would be very hard, and it would be a very very long list). But I'm open to discuss what should be on the list and what should not. I guess the best way to discuss this is in the H2 Google Group. Benchmarks: I know there is quite a controversy about benchmarks in general and the benchmark results published on the H2 web site in particular. I understand Derby better supports multiple connections accessing the same set of tables concurrently. However I don't agree that this is a very common use case (it's getting more common now that computers tend to have more and more cores). H2 is mainly an embedded database, and for this you generally tend to use very few connections. SQLite for example only very recently added support for multiple concurrent connections. I do understand using multiple connections is a use case for H2, and the benchmark does include a test with multiple connections accessing the database concurrently. But the performance comparison page also states that this is mostly a single connection benchmark run on one computer. By the way, I also have a problem with benchmark results published by others: - HSQLDB: the benchmark result on the HSQLDB web site. Why does it include McKoi (which is no longer maintained) but not H2? I asked Fred Toussi but didn't a meaningful answer. He basically said H2 is not listed so I can't complain :-) - Derby: at various conferences, for example JavaOne 2007 and Jazoon 2007, Derby included benchmark results for Derby and MySQL where Derby won, but strangely the benchmark source code is not available. I asked but I was told it's not possible to make it open source... hard to believe if you ask me. The presentations are available at http://home.online.no/~olmsan/publications/pres/ - db4o: I was told the PolePosition benchmark at http://www.polepos.org/ was sponsored by db4o (which is relatively easy to prove), but the page doesn't list that. There are some obvious problems with the PolePosition benchmark (for example memory usage isn't taken into account). > the blanket statements made on the H2 site have > lead to a misperception on the part of the user community Well, one solution is to not publish any number. But any meaningful benchmark needs to do that. You have to pick what you believe is a good set of use cases, and you have to present the data in some way. > It made > it quite difficult for me to justify running our own independent benchmark > to management when they were looking at the benchmark on the H2 site. Well, it should be clear to your management that _your_ use case may not match the benchmark _I_ wrote. > For example, you could Derby in-memory if you didn't care about durability... > turn off synchronous logging to disk if database consistency after a crash > isn't an issue. One can lower the default isolation mode to avoid lock > contention etc... Well... I did that and Derby was still slow. What database URL should I use to get the highest possible performance? > I thought blanket statements and whitewashing were the exclusive domain of > commercial software! Of course there is competition in the open source world as well (it would be bad if there isn't). The difference between open source and commercial is that commercial software tends to "disallow" publishing any benchmark results. For example it's not allowed to publish numbers for Oracle, or any other bigger commercial database I know. When I wrote Hypersonic SQL I also published benchmark results, and then I got a mail from somebody from Cloudscape (now called Apache Derby) which basically read "Did you not read our license? You are not allowed to publish any results. Remove the benchmark results from your web site or we will sue you." Unfortunately I don't have this email any more :-) With open source, you have open mailing lists and you can discuss it. Also the licenses tend to be more liberal. Regards, Thomas
Re: problems after "could not listen on port xxx on host 0.0.0.0" / corrupting data base?
okay, the address already in use being due to too quick request attempts might be an explanation. I have in the meantime found out that, although the address already in use error is given and the log does not contain the normal 'server started and ready to accept connections' statements ==> the server is actually available anyway and can be pinged/used, so the server was indeed started Leaving the 'another instance' issue which I am afraid I can't even say has been created and how it can be reproduced. I came as a result of using a custom build Eclipse RAP web application talking to the server. So I need to continue to monitor to find out when this occurs and what might have caused it. Unfortunatly there seems to be no way of making the data base usuable again once this error is given. So...some questions I can think of: 1) What version of derby are you using? -> 10.7.1.1 2) What has happened between now and when you last could use the system - did the system crash? ->trouble is I haven't found out yet how to reproduce this 3) Did someone ctrl-c or kill the network server process? -> no 4) Did any other piece of software get installed on this system that could be using the same port? -> don't think so, but it is a hosted tomcat server environment in which also Derby is hosted 5) Can you start networkserver with a different port (not to connect to the database; if ij can't, another network server cannot either). -> would need to ask my hoster for this which I did not do Have you tried booting your system since? -> my hoster advised that my tomcat+derby server has been brought down and up again, but as said the 'another instance...' error kept coming even after that
Re: problems after "could not listen on port xxx on host 0.0.0.0" / corrupting data base?
Hi Morten, thanks for your reply "on the address already in user issue". The Network Server is hosted on the internet and my provider tells me that nothing else is using the port and their tests would indicate that - the normal (Linux) start/stop scripts coming with Derby are being used - when the Server is stopped the port is indeed properly released. They are assuming that if the port in a (fresh) start is not properly released the Derby Server might not have properly been shutdown / might have crashed - for which I don't have indications. Any toughts on whether the server is started after the address already in use has been encountered? There a no further entries in the log that would say 'server is ready to accept connections' - so I guess the start failed. And any thougths on the 'another instance ...' issue - which prevents me to boot the database? might the database have been corrupted?? Tx
problems after "could not listen on port xxx on host 0.0.0.0" / corrupting data base?
Hi, when trying to start-up the network server I am getting Could not listen on port 31540 on host 0.0.0.0: java.net.BindException: Address already in use (which is also the message store in derby.log). This is the last message in the log and I am not sure what state the server is in after that (started or not?). When trying to connect from remote using IJ I am getting error 'Another instance of Derby may have already booted the database'. From looking at the log (an extract attached below) it seems as if a recovery has been attempted but finally failed?! I have found no way to get the database up and running again. Even after a complete shutdown and restart of the server I continue to get the 'another instance...' error. So I am not sure if my database might have been corrupted?? Any way to find out what is going on? Thanks Thomas Wed Feb 23 20:25:54 CET 2011 Thread[DRDAConnThread_3,5,main] Cleanup action starting java.sql.SQLException: Failed to start database 'PMTedb' with class loader sun.misc.Launcher$AppClassLoader@77cde100, see the next exception for details. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQL Exception(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection30.(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection40.(Unknown Source) java.sql.SQLException: Another instance of Derby may have already booted the database
Is the Network Server now started or not?
>From time to time I am running into the following situation with my hosted Derby Network Server (in the hosted environment the Tomcat start (/stop) script is used to also start (/stop) Derby) hwne attempting to start the server: Sun Feb 20 18:25:11 CET 2011 : Security manager installed using the Basic server security policy. Sun Feb 20 18:25:11 CET 2011 : Could not listen on port 31540 on host 0.0.0.0: java.net.BindException: Address already in use When then using IJ (from remote) and issuing a 'connect 'jdbc:derby://myserver:31540/myDB' the connect succeeds and the derby log on the server has the following entry: Booting Derby version The Apache Software Foundation - Apache Derby - 10.7.1.1 - (1040133): instance a816c00e-012e-4419-9c5f-727a788d on database directory /xxx/derby/data/myDB with class loader sun.misc.Launcher$AppClassLoader@77cde100 Loaded from file:/xxx/derby/lib/derby.jar java.vendor=Sun Microsystems Inc. java.runtime.version=1.6.0_21-b06 Database Class Loader started - derby.database.classpath=. Does this now mean the Network Server has been properly started yes or no? Thanks
Re: Trying to migrate to LDAP (but getting Error 08004)
> This means that a) you are running with the Java security manager
> enabled, and b) you need to add a missing SocketPermission to the
> derby.jar codebare in a policy file, cf.
>
ad a) yes, the security manager enabled is the default java security manager
which is what is confirmed in derby.log and matches what is stated in the
documentation ("If you boot the Network Server without specifying a security
manager, the Network Server will install a default Java security manager
enforcing a Basic policy")
ad b) I assume the concrete property referred to that would need to set/checked
is the java.net.SocketPermission property which can be set as documented in the
last line of the examples in the documentation, i.e.
permission java.net.SocketPermission "*", "accept";
which is the deault. What I do not quite understand is, if the default "*" -
which I have not changed - leads to connections being accepted from any host,
why am I then getting the
> java.sql.SQLException: Connection refused :
> javax.naming.CommunicationException
> : miniserver:10389 [Root exception is java.security.AccessControlException:
> access denied (java.net.SocketPermission miniserver resolve)]
> at org.apache.derby.impl.jdbc.authentication.
> JNDIAuthenticationSchemeBase.getLoginSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.authentication.LDAPAuthentication
> SchemeImpl.authenticateUser(Unknown Source)
to start with? Is this an AccessControl/Security issue or an issue that it does
not know how to resolve the servername to an IP-Address? What exactly would I
need to put as 'permission java.netSocketPermission' if not '*' and 'accept'?
Thanks
Re: Trying to migrate to LDAP (but getting Error 08004)
I have now tested the following two scenarios in conjunction with the network
driver:
1) using system-wide properties rather than data-base level properties
2) as you suggested, supply the properties as command line parameters
ad 1) when trying to connect using IJ I continue to receive error 08004,
however now the following messages are written to derby.log (which I have to
admit do not tell me much at this stage - but at least it looks like the
network driver has recognized the "LDAP" related properties. Note: I had
started IJ on the same machine where Derby and directory server are running)
me much :
Tue Jan 18 20:44:36 CET 2011:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.7.1.1
- (1040133): instance a816c00e-012d-9aa7-e0cc-5302821d
on database directory /var/lib/derby/db-derby-10.7.1.1-data/ldaptest with
class loader sun.misc.Launcher$AppClassLoader@7d772e
Loaded from file:/var/lib/derby/db-derby-10.7.1.1-bin/lib/derby.jar
java.vendor=Sun Microsystems Inc.
java.runtime.version=1.6.0_22-b04
Database Class Loader started - derby.database.classpath=''
Tue Jan 18 20:44:37 CET 2011 Thread[DRDAConnThread_3,5,main] (XID = 13),
(SESSIONID = 0), (DATABASE = ldaptest), (DRDAID = {1}), Cleanup action starting
java.sql.SQLException: Connection refused : javax.naming.CommunicationException
: miniserver:10389 [Root exception is java.security.AccessControlException:
access denied (java.net.SocketPermission miniserver resolve)]
at org.apache.derby.impl.jdbc.authentication.
JNDIAuthenticationSchemeBase.getLoginSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.authentication.LDAPAuthentication
SchemeImpl.authenticateUser(Unknown Source)
at org.apache.derby.impl.jdbc.authentication.AuthenticationServiceBase.
authenticate(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials
(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection30.(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection40.(Unknown Source)
at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
at org.apache.derby.impl.drda.Database.makeConnection(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.getConnFromDatabaseName
(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.verifyUserIdPassword
(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.parseSECCHK(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.parseDRDAConnection
(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.processCommands
(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
Cleanup action completed
Tue Jan 18 20:44:37 CET 2011 Thread[DRDAConnThread_3,5,main]
(DATABASE = ldaptest), (DRDAID = {1}), Connection refused : javax.naming.
CommunicationException: miniserver:10389 [Root exception is java.security.
AccessControlException: access denied (java.net.SocketPermission
miniserver resolve)]
Here is the derby.properties file used:
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# derby.properties
#
# we are using the default properties values for this demo
#
derby.language.logQueryPlan=false
# derby.drda.logConnections=true
# derby.drda.traceAll=true
derby.connection.requireAuthentication=true
derby.authentication.provider=LDAP
derby.authentication.server=ldap://miniserver:10389/
derby.authentication.ldap.searchBase=o=THMB
ad 2) I have passed the properties on the command line as suggested (after
having removed the derby.properties file). In this scenario the network
driver lead to the same results as the embedded driver. Athorisation worked
as expected; no entries in derby.log.
In summary my testing seems to evidence that the network driver is only
working in conjunction with LDAP authorization if the required properties
are passed on the command line when starting up the server. (So there
Re: Trying to migrate to LDAP (but getting Error 08004)
Bryan, thanks for your answers and suggestions. My updates are as follow: 1) derby.log does not contain any information other than the server having been started 2) I added 'ldap:://' at the start and '/' at the end of the value for property derby.authentication.server and (after having dropped and recreated the whole data base) retested still using the network client driver - no difference, i.e. still receiving the same error 3) I then used the embedded driver and recreated the database from scratch once again - and found everything working as expected with this driver! So using correct credentials I could connect to the data base and select data, providing incorrect user name and/or password resulted in the error 08004 with the message 'invalid athentication..'. Hard to believe that the network client driver is not working, whereas the embedded driver does!? -> where the normal use case I would consider using LDAP only in multi-user/ networked environments (and with all the warnings about the BUILTIN security system and the advise to use LDAP I would have expected quite some people having switched and then run into this problem??). Can you please advise on next steps?, i.e. should I create a JIRA issue immediately or will/should someone from the development try to reproduce first? Regards Thomas
Trying to migrate to LDAP (but getting Error 08004)
Thanks for reading my post. Any help to get Derby work with LDAP would be
greatly appreciated.
What I am trying to achieve and what I have done so far:
I would like to prepare my system for production use and migrate off the
BUILTIN authentication system to use LDAP as external directory service.
My testing environment is Apache Derby 10.7.1 on a stable Debian 5.x Lenny
server running on my own local area network and accessed from a Windows XP
client on the same network. As LDAP server I have chosen ApacheDS 1.5.7
which is running on the same server machine as the Derby Network Server.
While trying to get Derby to speak to my LDAP server I have for now
turned off SSL.
The following preparations have been taken on the Derby side:
(the server machine is called 'miniserver')
export DERBY_HOME=/var/lib/derby/db-derby-10.7.1.1-bin
java -jar -Dderby.system.home=/var/lib/derby/db-derby-10.7.1.1-data
$DERBY_HOME/lib/derbyrun.jar server start -h 0.0.0.0
java -jar -Dderby.system.home=/var/lib/derby/db-derby-10.7.1.1-data
$DERBY_HOME/lib/derbyrun.jar ij
connect 'jdbc:derby://miniserver:1527/ldaptest;create=true';
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.connection.requireAuthentication', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.authentication.provider','LDAP');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.authentication.server','miniserver:10389');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.authentication.ldap.searchBase','o=THMB');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.user.thill','uid=thill,o=THMB');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.sqlAuthorization', 'true');
connect 'jdbc:derby://miniserver:1527/ldaptest;shutdown=true';
(all these statement execute without any problem)
jndi.jar, ldap.jar and providerutil.jar are part of my CLASSPATH,
the jar files have also been copied into the $DERBY_HOME$/lib folder
and also the complete folder structure for jndi112 and LDAP103 as
downloaded from the Oracle/SUN homepage were copied into derby.system.home.
ApacheDS is running fine and two test users (one with uid=thill and
userPassword=xx) have been defined in my directory.
> IMPORTANT: Accessing the directory from Apache Directory Studio
and/or by using the java program AdvancedBindDemo which can be found
on the ApacheDS tutorial is working as expected!!, i.e. I can
successfully bind with uid=thill, o=thmb and userPassword xx.
So I am assuming the server side is in good shape.
However when trying to speak to Directory Server from Derby/IJ I am
getting error messages.
Here is my connect statement:
connect 'jdbc:derby://miniserver:1527/ldaptest;user=thill;password=xx';
and the trace output (I have added line breaks as needed to allow
posting here):
BEGIN TRACE_DRIVER_CONFIGURATION
Driver: Apache Derby Network Client JDBC Driver 10.7.1.1 - (1040133)
Compatible JRE versions: { 1.3, 1.4 }
Range checking enabled: true
Bug check level: 0xff
Default fetch size: 64
Default isolation: 2
No security manager detected.
Detected local client host: miniserver/127.0.1.1
JDBC 1 system property jdbc.drivers = null
Java Runtime Environment version 1.6.0_22
Java Runtime Environment vendor = Sun Microsystems Inc.
Java vendor URL = http://java.sun.com/
Java installation directory = /usr/lib/jvm/java-6-sun-1.6.0.22/jre
Java Virtual Machine specification version = 1.0
Java Virtual Machine specification vendor = Sun Microsystems Inc.
Java Virtual Machine specification name = Java Virtual Machine
Specification
Java Virtual Machine implementation version = 17.1-b03
Java Virtual Machine implementation vendor = Sun Microsystems Inc.
Java Virtual Machine implementation name = Java HotSpot(TM) Client VM
Java Runtime Environment specification version = 1.6
Java Runtime Environment specification vendor = Sun Microsystems Inc.
Java Runtime Environment specification name = Java Platform API Specification
Java class format version number = 50.0
Java class path = /var/lib/derby/db-derby-10.7.1.1-bin/lib/derbyrun.jar
Java native library path = /usr/lib/jvm/java-6-sun-1.6.0.22/jre/lib
/i386/client:
/usr/lib/jvm/java-6-sun-1.6.0.22/jre/lib/i386:/usr/lib/jvm
/java-6-sun-1.6.0.22/jre/../lib/i386:/usr/java/packages/lib/i386:/lib:/usr/lib
Path of extension directory or directories = /usr/lib/jvm
/java-6-sun-1.6.0.22/jre/lib/ext:/usr/java/packages/lib/ext
Operating system name = Linux
Operating system architecture = i386
Operating system version = 2.6.26-2-686
File separator ("/" on UNIX) = /
Path separator (":" on UNIX) = :
User's account name = root
User's home directory = /root
User's current working directory = /root
END TRACE_DRIVER_CONFIGURATION
BEGIN TRACE_CONNECTS
Attempting connection to miniserver:1527/ldaptest;traceFile=/root/trace.out
Using properties: { user=thill, traceFile=/root/trace.out, password=** }
END TRACE_CONNECTS
[net][time:1295204362513][thread:main][tracepoint:1][Request.flush]
SEND BUFFER: EXCSAT (ASCII
Is it possible to run multiple network servers in parallel?
Hi, is it possible to run for example a network server in version 10.6 in parallel to running the current version 10.7 on one and the same (server) machine? If so, how would a client program like IJ know to which server to connect to? Thanks
Re: Problem solved
I will try to summarise my experiences in the wiki once I have completed my full round trip of what I am/was trying to achieve: 1) have a JAVA provider (in Germany) host a Derby Network Server for me - done 2) have them run the Derby Server using SSL encryption and peer authentication - done 3) become my own CA to allow me to create and sign SSL *client* certificates myself - done (and buy the server certificate from an official CA) 3) have my applications securely communicate with the database server either direct (my java application - done) or via Tomcat (my java web application - mostly done) 4) use SQL authorisation to protect my data base objects - done (also many thanks to Dag and the team that with release 10.7.1 the possibility to execute procedures with definer rights was introduced which was a concept I was missing in the previous version) 5) migrate off from using the built-in user system to utilizing LDAP - work in progress (and hoping this journey will be al lot shorter than my SSL endevours) Regards
Problem solved
After many hours of further investigation I have been able to overcome all road blocks and now successfully use SSL certificates (created and signed using openSSL and converted in jks keystores using keytool) and peer Authentication between server and client. I wish though the certificate expiry date would not be ignored, but from what I read on other forums that seems to be intended behaviour in the SUN implementation of JSSE.
No one able to help?
Hi, I am stuck with this problem and would appreciate any help. Thanks Thomas
No available certificate or key corresponds to the SSL cipher suites which are enabled
Hi, I am trying to use the same keystore file that I am successfully using in conjunction with my Tomcat server also with the Apache Derby Network Server. However while the keystore works fine with Tomcat, Derby doesn't like it and throws the error 'No available certificate or key corresponds to the SSL cipher suites which are enabled' when trying to start-up the server. The keystore contains one keypair only. Below is the output of a keytool -v -list. As long as I am using keytool to generate my keystore with a self-signed certificate the server starts up using SSL as expected. However, when trying to use a certificate signed by a CA - and as I am only doing this in a test environment on my LAN I am acting as the CA - then I can only get Tomcat to accept my keystore. Here the keystore content: Keystore type: JKS Keystore provider: SUN Your keystore contains 1 entry Alias name: thmb Creation date: Dec 11, 2010 Entry type: PrivateKeyEntry Certificate chain length: 2 Certificate[1]: Owner: emailaddres...@t-online.de, CN=THMB, OU=IT, O=x, L=x, ST=x, C=DE Issuer: emailaddres...@t-online.de, CN=THMB CA, OU=IT, O=x, L=x, ST=x, C=DE Serial number: 1 Valid from: Sat Dec 11 12:50:08 CET 2010 until: Sun Dec 11 12:50:08 CET 2011 Certificate fingerprints: MD5: A8:27:6E:B4:81:E0:6B:23:B4:A7:4C:13:4B:16:80:EC SHA1: B9:9F:2B:CA:03:40:00:A0:4B:03:A0:CD:E7:E7:8F:61:9D:B9:26:42 Signature algorithm name: SHA1withRSA Version: 3 Certificate[2]: Owner: emailaddres...@t-online.de, CN=THMB CA, OU=IT, O=x, L=x, ST=x, C=DE Issuer: emailaddres...@t-online.de, CN=THMB CA, OU=IT, O=x, L=x, ST=x, C=DE Serial number: 95e743a14724966f Valid from: Sat Dec 11 12:44:17 CET 2010 until: Tue Dec 08 12:44:17 CET 2020 Certificate fingerprints: MD5: 8D:D4:44:B6:37:EC:51:CD:25:85:E8:F1:0A:A9:30:2D SHA1: E7:04:DB:FC:DA:16:FE:46:88:56:C5:0B:65:D5:0F:DF:AC:0E:A1:D7 Signature algorithm name: SHA1withRSA Version: 3 Any help would be greatly appreciated. Thanks Thomas
Re: ERROR 08004: Connection refused : Invalid authentication.
Sonny Laskar writes:
>
>
> Dear All,I am new to Derby.I have forgotten the username/password set for
connecting to my derby database.Now when I tried to connect , i get the invalid
authentication error (ERROR 08004: Connection refused : Invalid
authentication.)Please let me know if I can reset this password or if I can
create a new user.Also I want to know that if I want to create the clone of a
derby database , can I just copy the folder and use it?Regards,Sonny LaskarIndia
> =-=-=Notice: The information contained in this
e-mailmessage and/or attachments to it may contain confidential or privileged
information. If you are not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the information contained in this
e-mail message and/or attachments to it are strictly prohibited. If you have
received this communication in error, please notify us by reply e-mail or
telephone and immediately and permanently delete the message and any
attachments. Thank you
>
login in with your data base owner account you can reset the password of a user
by calling SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.' with
an empty password as the second parameter passed. Assuming you are using the
builtin authentication provider. With the same stored procedure you can also
create new users. (see reference guie on page 327 for details on this system
built-in stored procedure)
To your second question: Yes, to clone a database you can just copy the folder.
how do I make the pieces in this puzzle fit?
Hi, the puzzle I am refering to consists of the following pieces: a hosted Derby Network Server, SSL and mixed client apps I need help in understanding how the different components need to be configured so that they work together properly. 1) my Derby Server is not running in my LAN, but is hosted by a java hoster on the internet 2) the Security Manager installed is using the Basic Security Server policy, i.e. default options 3) the Network Server is expected to require user authentication 4) the Network Server is expected to support network security with SSL 5) the SSL certificates I would like to create and issue myself (I don't want to use a Certification Authority and buy certificates at this stage aa my application is not used in real production yet) 6) the Derby Server will receive connections from "fat" clients and from web client applications (with Apache http server and Tomcat 5.5 servlet container) So far, I have a workable solution for java *fat* client applications: ad 1) the Derby Server is started from the command line (my hoster has written the necessary scripts to tie derby start/shutdown to the Tomcat start/stop processes. I can issue Tomcat start/stop/restart command via the default web interface supplied by the hoster to allow their customers to start/stop their Tomcats themselves) ad 2) no special configuration has be done here ad 3) SQL authentication has been configured and users are managed using the build-in user system (users as data base properties) ad 4) the server starts up fine reporting "server started and ready to accept SSL connections" - I am not using peer authentication, but basic ssl security only at this stage ad 5) my certificates have been self-generated/signed using the java keytool utility. Keystores files (server truststore and client keystore) are available as needed ad 6) connections from my java application or from IJ are possible ==> as said, for a fat client environemnt I think I have a workable solution Now I would like to connect to my data base server also from a web application - and it is not clear to me how this can be achieved: ad 1) would the Derby Server still be started from the command line? -> I would expect so, as SSL encryption needs to be kept in place for my fat client applications which I want to use in parallel to the web version and I have also not found SSL related options that could be used when running the Derby Server as a servlet under Tomcat. Or is SSL support now to be implemented at a different level? between the web server and the browsers of my users? ad 2) do I have to change default options of the Security Manager or can they stay as they are in this scenario? ad 3) I expect user authentication works the same way in both secanrios ad 4) how does SSL support work in this context?? how do I need to configure Tomcat and/or Apache hhtp server? ad 5) do my users needs to load certificates into their browser certificate store? can I still use self-signed certificates? do I need a specific algorithm / type of certificate? (RSA instead of DES)? Thanks for your support Thomas
Language of error message
Hi, can someone please advise what is determining the language which will be seen in error messages? Is this depend on the locale of the machine? I am currently seeing messages in german, but would like to switch to english, but don't know how to do that. Thanks in advance Thomas
IJ scipts - stop execution and rollback in case of error
Hi, is there a way to make scripts stop (and the transaction rollbacked) when an error is thrown rather than IJ just continuing the execution with the next statement? Thanks
Reusable components
When implementing the data model for my application using Derby and coding some stored procedures in JAVA over the past months I have asked myself a couple of times along the way what the best approach to fulfil some 'standard' requirements would be - general requirements which I would expect most people designing data base applications would have and will need a solution for. One example: How do I best store hierarchical data like a (file system) folder structure in my data base? In case of my application the decision was to go with the nested set model / using the modified pre-order tree traversal algorithm (the article 'nested set model' in wikipedia might be a good starting point for details on this). PEAR - the PHP Extension and Application Repository which is "a framework and distribution system for reusable PHP components" even has a reference implementation of nested sets in PHP - how exiting and unfortunate that this omly seems to exist for PHP. So I was wondering whether such reusable components actually might also exist in form of stored procedure code written in Java which could be used in data base management systems supporting Java as procedural language - like Derby - somewhere? (without me having come accross this) and if not, whether there might be an appetite of some users on this forum to dedicate some of their time sharing their solutions and to jointly build/test/document some reusable procedures? (which might be an opportunity for some people like myself who will never be able to contribute to advancing Derby itself (as I am not a professional Java programmer), but who have experience in data modelling and SQL and might therefore contribute in such an endeavor.
SSL - certificate expiry date ignored
Hi, on 10th July I implemented SSL encryption between my network server and its clients. The certificates were self-generated using the keytool utility. I am starting the server requesting peer authentication. I am starting IJ with proper references to my client keystore. When trying to connect to the server without specifying the ssl=basic parameter, I am getting an error explaining that I tried to connect to a server using ssl encryption via a clear text connectionand the connection is refused - so far so good. When specifying ssl=basic the connection gets established - fine as well. However when connecting with a certificate with an expiry date 20th July the connection also gets established and I can read data. So it looks to me the expiry date is being ignored. Any hint? Thanks
Re: using set role in a stored procedure / Error 25001
when calling commit I am no longer getting error 25001.
Now I have a different problem: the lifetime of the set role seems to be limited
to the stored procedure itself - after returning to the main program my role is
NULL again. I have double-checked using the debugger and confirmed that the set
role within the procedure actually was carried out successfully.
Using an already established connection (see Connection conn =
DriverManager.getConnection("jdbc:default:connection") at the beginning of the
procedure) I would have expected that the role is still set after returning.
Thanks
Re: using set role in a stored procedure / Error 25001
The procedure is the first call to the data base after opening the connection,
so there is no transaction open/pending from outside this procedure.
Within the procedure itself however I need to query the catalog to see if a
role
is already set and only if this is not the case set the role as needed/
determined by a second select. So the set role is not and can not be the first
SQL statement within the procedure. Attached is the java code of the procedure.
I am receiving the error when using IJ to connect via a first
'connect jdbc:derby:' statement, followed by a
'Call rte."SP_setRole"();' statement.
Thanks for your help
public static void SP_setRole() throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection");
boolean lRoleNeedsToBeSet = false;
String vcRole = "";
Statement stmt = conn.createStatement();
// every user can read this system table which always holds only one row
String cSQL = "SELECT CURRENT_ROLE FROM sysibm.sysdummy1";
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
vcRole = rs.getString(1);
// result will be NULL if no role is set
if (rs.wasNull()) {
lRoleNeedsToBeSet = true;
// DB-Admin procedures will ensure each data base user will only
// be granted ONE role, i.e. the most priviledged role in the
hierarchie
// hierrachie of roles defined for the app, directly to his login
cSQL = "SELECT roleid FROM sys.sysroles WHERE grantee = current_user";
ResultSet rs1 = stmt.executeQuery(cSQL);
while (rs1.next()) {
vcRole = rs1.getString(1);
break; // to be on the safe side, although rs1 should always
//hold only one row
}
rs1.close();
}
break; // again, just to be on the safe side
}
rs.close();
// if no role is set, then set role to the role assigned to the user by the
// application owner/dbo; (else role already set will remain unchanged)
if (lRoleNeedsToBeSet) {
cSQL = "SET ROLE " + vcRole;
stmt.executeUpdate(cSQL);
}
stmt.close();
return;
}
using set role in a stored procedure / Error 25001
Hi, can someone please advise if it is possible to execute a 'set role' command within a stored procedure? My code runs fine when being part of the java program, but throws "ERROR 25001: Invalid transaction state: active SQL transaction." when put inside a java stored procedure. Some documentation found on the net suggested that setting a role is not transactional and not allowed when a transaction has been opened - so I assume as all code within a procedure is considered a transaction this is why I am getting the error? Would be thankful if someone can confirm this is the case. Thanks
RE: how to include derby.jar in my .class file?
I've used launch4j (http://sourceforge.net/projects/launch4j/) to package multiple JARs into a single Windows executable. Or, you can un-JAR derby.jar (jar -xf derby.jar), add your class files to the same directories, and re-JAR everything into one. Regards, Thomas Taylor -Original Message- From: oldmhe Sent: Sunday, May 09, 2010 6:17 PM To: derby-user@db.apache.org Subject: how to include derby.jar in my .class file? I'm new to Java, but am making progress with accessing Derby via the "embedded" framework. My program works as long as CLASSPATH points to derby.jar. But my preference to deploy the program as a single file, so that the user can run it simply with: java myprogram [parameters] without having to set CLASSPATH. Is there a way to include derby.jar inside my class file (or some other way) so as to eliminate the need to set CLASSPATH -- and so the entire deployment involves a single file? Thanks in advance. -- View this message in context: http://old.nabble.com/how-to-include-derby.jar-in-my-.class-file--tp28505459p28505459.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
closed - improvement request raised
I have now entered an improvement request in JIRA - or to be more precise I have added to and voted for existing request derby-4551.
changing a user password
Hi,
can someone please advise how the password of a user can be changed? My database
requires authentication, is using the BUILTIN authentication provider and I have
defined users as database properties using the system procedure
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.xxx,...
I have searched the documentation (refguide and devguide) but haven't found this
being documented.
Thanks
RE: Problems to use Derby Network Server
Hello Fabricio, Ive normally seen a Connection Refused: connect message when a firewall is blocking a network port (Windows Firewall most likely). You can add an exception to the firewall on the server (203.203.1.1) for port 1527. Alternately, you can try disabling the firewall briefly to confirm that with the firewall turned off you can connect to the derby network server. FYI. you will normally see a Connection Timeout message when either the destination (server) IP address is wrong, or the network server has not started. Best regards, Thomas Taylor From: Fabricio Pedroso Jorge [mailto:fpjb...@gmail.com] Sent: Tuesday, May 04, 2010 10:55 PM To: derby-user@db.apache.org Subject: Problems to use Derby Network Server Hello... This is my first e-mail, and i hope i can help and be helped... At this moment, i need help. My problem is tah a can't connect my application to a database, using Derby Network Server. Let me explain better: I have 2 machines in my LAN, using the following IPs: (203.203.1.1) - Server and (203.203.1.2) Client. I'm trying to conect the client to the server, but i keep getting the following message: "Connection refuse: connect" On the server, i've installed Derby Network Server, configured it properly and started successfully. Then copied my Derby database to DERBY_HOME\bin. OBS: Using the ij, i can connect to the database using the localhost address, but when i try to use de server IP, i get "Connection refuse: connect". On the client, when i try to create a connection, via NETBEANS 6.8, using the following URL: "jdbc:derby://203.203.1.1:1527/BDHistoricoPeso", i get, again the error "Connection refuse: connect" Thats the problem: i simply can't connect mai client to the server... i can't create a connection via NETBEANS to my server... I don't know what to do anymore, and i hope you guys "show me the light" Thanks!!! -- Atenciosamente, Fabrício Pedroso Jorge. Programador de Sistemas Júnior - SEFA-PA Bacharel em Ciência da Computação - CESUPA Resumo Profissional: http://br.linkedin.com/pub/fabricio-jorge/19/554/58b No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.814 / Virus Database: 271.1.1/2854 - Release Date: 05/04/10 14:27:00
Re: SQL authorisation and routine permissions
Do I understand correctly that, if my procedures had been defined by the database owner (which they were in my case), then additional grants would not be required => would Derby support running routines with definer's privileges? If so, I would file an improvement request. For the time being, is there any possibility to achieve the following: 1) I do NOT want any user to have direct access to data in my base tables. 2) the ability to READ data off tables is handled by the definition of VIEWS and granting rights on these views to roles/users. This works fine. 3) However I also want to prevent people from connecting to the database directly and issuing data manipulation statements (insert, update, delete) as I would like to have full control over database content. Therefore I have written stored procedures that, for each object/table, for example handle the insertion of data (e.g. a proc SP_addClient to insert into TBL_Clients). Within this procedure I do include business logic that ensures that certain fields are only populated in adherence to certain business rules. For example when inserting a german client I want a VAT field filled with 19%. If I now need to grant insert rights on the underlying base table to the role/user as well, I do open up the possibility for a user using - let's say IJ - to (intentionally or not) fill the VAT field with a different value than 19% although setting-up a german client. This is just a simple example of business logic made up here to illustrate and I understand I could probably prevent this set-up mistake from happening with a check constraint. However I do think this brings across why I want only procedures to change my data. 4) I have looked into whether it is possible to grant the stored procedure insert rights on the table to prevent the error message that the user is not authorised to insert into the table from being thrown. Although I could issue the command 'GRANT INSERT ON table "TBL_Clients" to "SP_addClient" without receiving an error, the situation remained unchanged (the user still gets the error that he is not allowed to insert data [obviously I had granted the user the rights to execute the procedure upfront and set his role accordingly before calling the procedure]. Any tips? Thanks
SQL authorisation and routine permissions
Hi, having set-up SQL authorisation I would like to grant data modification rights (insert, update, delete) to stored procedures only. I was assuming that granting executing rights on a routine using GRANT EXECUTE ON PROCEDURE to appl_user (with appl_user being a role) would automatically grant the right to insert data to any user who can take on this role. So there is no need to also GRANT INSERT ON TABLE xy TO appl_user. However testing this I am getting a ' does not have INSERT permission on table' error. Does this mean I have to grant rights on the tables accessed in a procedure on top of granting execution rights on the procedure for this to work? I was hoping to grant execution rights on the procedure would be all needed. I have checked the docs, but haven't found a statement on this (or must habe overlooked it). Thanks
Re: SQLAuthorisation and role permissions
Thanks a lot for the help. Now everything is working fine. Regards Thomas
Re: SQLAuthorisation and role permissions
here is the script which can be executed to reproduce the problem
-- create an embedded database that is encrypted and
-- specify user 'derby' when initially creating the database so this user
-- will be or can become the database owner
connect 'jdbc:derby:SecuredDB;create=true;
dataEncryption=true;bootPassword=encryption;user=derby';
-- create a table without granting permissions on it to anyone so that
-- when security set-up is in place only the
-- database owner should be able to query this table
CREATE SCHEMA RTE;
-- no grant seems to be required in derby to allow schema access
CREATE TABLE RTE."SecuredTable"(
"ColumnA" integer NOT NULL,
"ColumnB" varchar(10) NOT NULL,
"ColumnC" varchar(60) NOT NULL,
"ColumnD" date,
CONSTRAINT "PK_SecuredTable" PRIMARY KEY ("ColumnA"));
-- insert some sample data into the table while being logged in
-- as database owner
INSERT INTO RTE."SecuredTable" ("ColumnA", "ColumnB", "ColumnC")
VALUES (1, 'aaa', 'bbb');
INSERT INTO RTE."SecuredTable" ("ColumnA", "ColumnB", "ColumnC")
VALUES (2, '111', '222');
-- create a view on which authorisations will be granted (or not)
CREATE VIEW RTE."SecureView" AS
SELECT "ColumnA", "ColumnB" FROM RTE."SecuredTable";
-- set-up security mechanisms (authentication, SQL authorisation)
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.connection.requireAuthentication', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.authentication.provider', 'BUILTIN');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.derby', 'derby');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.fullAccessUsers', 'derby');
-- this property is derby specific and should not be used when standard SQL
-- authorisation is used
--CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
--'derby.database.defaultConnectionMode', 'noAccess');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.sqlAuthorization', 'true');
-- prevent ability to overwrite security settings made
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.propertiesOnly', 'true');
-- shutdown data base for the static settings to be activated on next start-up
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=derby;password=derby;shutdown=true;';
-- set-up (application specific) roles and SQL authorisations
-- login again using data base owner login and continue with granting
-- permissions
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=derby;password=derby';
CREATE ROLE reader;
-- this should also grant select permissions on the underlying base table
-- for the columns included in the view
GRANT SELECT ON RTE."SecureView" TO reader;
-- maintain user information
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.user."tho...@xy.de"', 'th');
GRANT reader TO "tho...@xy.de";
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.user."b...@xy.com"', 'b');
disconnect;
-- trying to connect to the database with an undefined user should not be
-- possible and return an error
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user="a...@xy.com";password=x';
-- connect to database with a user that is allowed to connect
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user="b...@xy.com";password=b';
-- try selecting data from a view on which user has no permission
-- (no data to be returned)
select * from RTE."SecureView";
disconnect;
-- connect to the database with user holding read permission on the view
-- via his role assignment
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user="tho...@xy.de";password=th';
-- data should be returned
select * from RTE."SecureView";
-- no data returned which is strange and possibly a bug
disconnect;
-- explicitely grant select permissions to user (rather than role)
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user=derby;password=derby';
GRANT SELECT ON RTE."SecureView" TO "tho...@xy.de";
disconnect;
connect 'jdbc:derby:SecuredDB;bootPassword=encryption;
user="tho...@xy.de";password=th';
-- only after the explicite grant to the user data is returned
select * from RTE."SecureView";
disconnect;
exit;
SQLAuthorisation and role permissions
Hi, I have trouble understanding why the following doesn't work as expected: I have secured an embedded data base by requiring authenification and using SQL Authorisation. Authentification is working as I expected; SQL Authorisation not or only partially. When loggin in as database owner and granting select permission on a view to a user X, then loggin off and loggin back in as user X => data can be selected from the view as expected. However when creating a role, granting select permission to that role (for all columns on the view, so no colum list specified) and then granting the role to user X - then, when logging in a user X, I am getting an error that user X doesn't have select permission on the first column in the view. My expectation is that user X via having been granted the role should be able to select data from the view. Can anyone please advice what I am doing wrong or what I am misunderstanding? If needed, I could post all SQL statements to create sample objects, users, roles, permissions, Note: I have also checked system table SYS.SYSTABLEPERMS and found an entry for the role defined to hold the permission granted on that view. When explicitely granting select permission to the user, then I can select data as expected - but obviously I want to avoid the overhead of needing to grant permissions on objects to individual users, but only to roles and then maintain user rights via role assignments. Thanks Thomas
Re: debugging java stored procedures
Thomas writes: can someone please confirm that stepping through the Java code of a stored procedure is indeed possible? no sure why there is no feedback - is it trivial and must be me / my specific set-up why it doesn't work for me? or am I trying to do something that is not possible?
debugging java stored procedures
Hi, I need to come back to this subject after many hours of trying to get this to run over the weekend I have followed Bryan's advice to my previous post (some weeks ago) and moved to an embedded database scenario as suggested as this was supposed to remove the complexity involved when trying to debug in a network server environment. However, I was still not able to actually step into the stored procedure code for debugging as Eclipse always tells me it can't find the source path to the code. Using an embedded data base have tried to just step through the code of a procedure that does work perfectly fine. The debugging works well up to the point where the callable statement is executed and where I would need to step into this call for further debugging. I have tried two approaches: 1) stepping through when the corresponding code resides on the filesystem 2) stepping through after loading the jar file into the data base. In both cases the debugger doesn't show the source as it tells me it can not find the path to the source code (even if I include the jar as external jar or if I include the folder on the filesystem which holds the class into the eclipse classpath) Help would be really be appreciated as being able to use the debugger would be a dramatic help and improvement for me. Thanks Thanks
data base (server) timezone
Hi, as Derby currently does not support timezone aware date/time data types, my application that can run in local/embedded data base mode but also in networked/network server mode needs to convert input dates into UTC before storing (and back to local timezone if wanted when displaying stored information). As a start I have written a stored function accepting a timestamp and a timzone id that currently does the conversion. When running in network server mode the timezone to pass to the function is clear, but in embedded mode the 'database server' timezone depends on the user timezone. embedded data base mode: How would I determine the timezone e.g. 'current_timestamp' function is refering to? Is there a function available for this? Would I need to write a java stored procedure using java.util.TimeZone.getDefault() for this? network server mode: Although I know the timezone when running in network server mode (and could sort of "hard code" it by putting the ID on a static data table), would there be a more elegant way of retrieving the timezone in this scenario as well? (maybe asystem property?) Thanks
Re: common use case for data base programming not possible in derby?
sorry for me using the 'post' action instead of the 'follow-up' action - I wanted to put this as a last remark on my previous / related query
common use case for data base programming not possible in derby?
Okay thanks. The approach should work for me! Note: although I would have preferred to be able to accept data supplied for the "normal" columns (e.g. payload) and silently have the system correct the "tech" columns (e.g. createdBy/On, updatedBy/On) in case needed by overwriting user input rather than reject all input in case inproper values (incorrect username or timestamps too far off) in the techcols are detected.
common use case for data base programming not possible in derby?
use case - use of database side programming to log data maintenance activities - tech columns CreatedBy/CreatedOn, LastUpdatedBy/LastUpdatedOn to store username, timestamp information on when row was inserted / last updated defined on every application table - no possibility for users/applications to tamper data stored in these columns - avoid implementation of extensive protection mechanisms (e.g. by allowing table data to be maintained only via stored procedures) approaches investigated 1) using default column values to populate CreatedBy/CreatedOn on insert, i.e. "CreatedBy" varchar(64) NOT NULL DEFAULT CURRENT_USER -> column values not secured -> works for insert statements only (can not be used for populating columns LastUpdatedBy/LastUpdatedOn in case of updates) 2) using a generated column spec with a value expression i.e. "CreatedBy" varchar(64) GENERATED ALWAYS AS CURRENT_USER; -> not possible as CURRENT_USER, CURRENT_TIMESTAMP as non-determinstic functions can not be used as value expressions 3) using INSERT triggers (with SQL statements or calling java procedures) i.e. CREATE TRIGGER xy AFTER INSERT FOR EACH ROW UPDATE "CreatedBy"... -> might work for insert statements, but triggering an update statement with the insert would prevent me from defining an after update trigger on the table which I would need to log update activity (and which would create an infinte loop) Can't think of a way that would work which is unfortunate as for example PostgreSQL would allow such an implementation easily as transition values can be referenced and updated with the system funtions associate with a trigger(i.e. all needed is a one line statement NEW.CreatedBy = CURRENT_USER within the trigger function). Any suggestions? Is there really no way this can be achieved in Derby? (other than maybe putting the tech cols on a separate table and joining the two base tables in a view so that at least in read operations it would look like the tech cols are held on the same table?) Thanks Thomas
SOLVED: FOR UPDATE is not permitted in this type of statement
mistake was accessing rte."IDs" which is a view instead of "TBL_IDs" which is the base table. Also rewrote procedure to use updatable resultset rather separate select for update and update statement.
FOR UPDATE is not permitted in this type of statement
Hi,
I am trying to execute a select for update statement and receive the error
message shown on the subject line although I think I had respected all
requirements for such a statement.
Here is the Java code of the stored procedure:
==
public static void SP_GetNextID(int iNextVal[], String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
int column = 1;
PreparedStatement ps = Utils.prepare(conn,
+ "SELECT \"LastValue\" \n"
+ "FROM rte.\"IDs\" \n"
+ "WHERE \"IDName\" = ? \n"
+ "FOR UPDATE OF \"LastValue\" \n");
ps.setString(1, vcIDName);
ResultSet rs = ps.executeQuery();
rs.next();
iNextVal[0] = rs.getInt(column++) + 1;
ps = Utils.prepare(conn,
+ "UPDATE rte.\"IDs\" \n"
+ "SET \"LastValue\" = ? \n"
+ "WHERE \"IDName\" = ? \n");
ps.setInt(1, iNextVal[0]);
ps.setString(2, vcIDName);
ps.executeUpdate();
Utils.close(ps);
return;
This is the SQL code used to define the procedure:
==
CREATE PROCEDURE
rte."SP_GetNextID"(OUT "iNextID" integer, IN "vcIDName" varchar(64))
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
EXTERNAL NAME 'allDatabasesPk.Functions.SP_GetNextID';
and here is the code snippet from the main program calling the procedure:
=
Connection conn = DriverManager.getConnection(connectionURL);
String vcIDName = "Transaction";
CallableStatement cstmt =
conn.prepareCall("{ call rte.\"SP_GetNextID\"(?, ?) }");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, vcIDName);
cstmt.executeUpdate();
String cTrxID = Integer.toString(cstmt.getInt(1));
cstmt.close();
System.out.println(cTrxID);
The error is thrown on line 'cstmt.executeUpdate();
Any help would be very much appreciated.
IJ scripting (variables in IJ?)
Hi,
I would like to execute a series of statements in IJ where the value returned by
the function call executed in one statement is passed as a parameter to the next
statement calling another procedure.
Example:
CALL "SF_addClient"('lastname', 'firstname')
==> this function creates a new client record returning the client ID
automatically generated which was given to the client)
CALL "SF_addClientAddresses"('clientid', )
==> this would add some client info into a different table where clientid is a
field in this table.
With
values CALL "SF_addClient"()
I can see the return value, but how can I store this in a variable /use the
return value in further processing?
Is this possible to achieve in IJ?
Thanks
Re: debugging java stored procedures
Hi, any tipps from anyone which would help to debug java stored procedures? Not being able/knowing how to debug the code executed by the database using the eclipse debugger, I have included System.out.println statements into the procedures, but have no idea where these end up when running against a network server on a remote host mode (have checked tomcat logs, but haven't found anything). Thanks
debugging java stored procedures
Hi, I was wondering whether it would be possible to debug java stored procedures loaded into the database in a jar file using the eclipse debugger. Debugging of my java stub program works fine, but when trying to step into the callable statement by which the java procedure call is envoked, eclipse says 'source not found' and offers a button to edit the source lookup path. In the dialog becoming available when using this button there is an option to add a references to 'external archives' RESIDING ON A FILE SYSTEM, but when I reference the jar file (which btw is on the file system of the Linux server to which I am connected via a samba share and which) I loaded into the database the message 'source not found' keeps reappearing. So I was wondering if there is a way to debug jar files loaded into the data base or how what I am trying to achieve can be accomplished. Thanks
Re: *Unrecognized* procedures
The problem was the numbering of parameters in the registerOutParameter and the
setxxx Statement:
instead of
CallableStatement cs = conn.prepareCall("{ call APPL.\"myFunction\"(?, ?)");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(1, parm1);
cs.setString(2, parm2);
CallableStatement cs = conn.prepareCall("{ ? = call APPL.\"myFunction\"(?, ?)");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(2, parm1);
cs.setString(3, parm2);
was correct/needed.
Unfortunately there was no specific hint in any documentation I read mentioning
aspects of how the numbering needs to be done.
Regards
*Unrecognized* procedures
Hello,
can't find the reason for the following issue:
(Derby 10.5.3.0 network server running on a Linux server, the java function has
been loaded into the database as part of a jar file, server running without
security policy).
When trying to call a procedure from a java program I am getting error '.. is
not recognised as a function or procedure'.
I used the exact same connection URL as used in the java program in IJ to
connect to the database and executed a 'values APPL."myFunction"("parm1",
"parm2")' and the call executed fine returning the expected result.
So I would conclude the procedure exists in that schema on the server.
In the program I used
CallableStatement cs = conn.prepareCall("{ call APPL.\"myFunction\"(?, ?) \n}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(1, parm1);
cs.setString(2, parm2);
which lead to 'APPL.myFunction' is not recognised as a function or procedure.
Commenting out the CallableStatement and issuing **from within the same program
(leaving driver, connectionURL untouched) ** a preparedStatement doing a "select
ALIAS FROM SYS.ALIASES \n" I can see that my Function exists.
I am passing the correct number of parameters. It can't be a classpath problem
as the java code is stored in the database. I am specifying the correct schema
name. When using eclipse data tools platform data explorer to connect to
database I can browse the system catalog and see the procedure...
So what else should I check??
Thanks
Eclipse plugins - Derby Nature not available
Hi, I am having problems wanting to migrate from eclipse 3.4 ganymede (under which I had successfully used the plugins) to eclipse 3.5 galileo. A fresh install of the current eclipse version has created the folder structure: 'C:\eclipse-reporting-galileo-SR1-win32 v3.5_2009' (root folder I defined/used) '..\eclipse' (first level subfolder) and further subfolders '..\eclpise\configuration\...' '..\eclipse\plugins\..' I have downloaded the 2 zip files derby_core_plugin_10.5.3.zip and derby_ui_doc_plugin.zip from apache.prg and unzipped the binaries into the eclipse plugin directory - C:\eclipse-reporting-galileo-SR1-win32 v3.5_2009\eclipse\plugins\ in my case. This has created folders ..\eclipse\plugins\org.apache.derby.core_10.5.3 ..\eclipse\plugins\org.apache.derby.plugin.doc_1.1.2 ..\eclipse\plugins\org.apache.derby.ui_1.1.2 However when launching eclipse and right clicking on my project I am NOT getting a menu item 'Apache Derby' (and of course then also no submenu item 'Add Apache Derby nature'). However I can open the derby plugins user guide from the menu by selecting help->help contents, so I assume this piece of the install has worked. Any hint on why the menu entries when right clicking on the project are not available? Thanks
ORDER BY in query when defining a VIEW
Hi, can anyone please confirm whether an order by clause is supported in derby 10.5.3 or not? The manuals contain no reference to this and reading through Jira I was under the impression that this should (now) be possible. However when trying to create a view using a select query which has an order by an error message Syntax Error: encountered "ORDER" is thrown. Omitting the Create View as and executing just the SQL query itself is working fine (so the SQL statement has no syntax problems). Thanks
Re: Managing the Derby Network Server remotely by using the servlet interface
Bryan you are right - I typed the wrong port in my write up; it should have been what you stated and documented in the wiki: ==> http://localhost:8080/derby/derbynet ? Regards Thomas
Re: Managing the Derby Network Server remotely by using the servlet interface
Bryan, thanks for your advise. With your guidance and some further reading how to deploy apps under Tomcat I finally managed to get this running. As a short summary: 1) Deploying the war file into tomcat using the tomcat manager application placed the war file 'derby.war' into tomcat's webapps folder (/var/lib/tomcat5.5/webapps/ on my debian system) and also created a folder within webapps called 'derby'. Initially the derby folder created only contained one subfolder 'WEB-INF' in which only one file 'web.xml' was found. 2) Within the WEB-INF folder I then manually created an additional folder 'lib' into which I placed 'derby.jar', 'derbynet.jar' and 'derbytools.jar'. (Note: I made user 'tomcat55' the owner of the folders created and of all derby jar files which I copied over, and specified 'nogroup' as group. (so I actually did not need to place anything in ../common/lib). 3) Restarting Tomcat and then opening URL http://localhost:1527/derby/derbynet returned a screen confirming that the 'Derby Netwrok Server has been started' and the screen also offers some buttons, e.g. one 'Stop' button which can be used to stop the server again. The last thing I did was to add line derby.system.home=/var/lib/derby/derbydata as the last entry in file 'catalina.properties' (found under /usr/share/tomcat5.5/conf) so that database files are created and stored in a separate location and not within the tomcat hierarchy. Hope this write up helps people looking for some guidance (beyond what is documented in the admin guide) on topic. Regards Thomas
Managing the Derby Network Server remotely by using the servlet interface
Hi, I am trying to deploy derby network server as a web app under tomcat5.5 on a debian server. The server admin guide talks about this on page 40, but not in enough detail for me to understand what I might be missing or doing wrong. What I have done/tried/achieved so far: 1) the derby packages have been unpacked to /var/lib/tomcat5.5/webapps/derby 2) the file owner has been set to tomcat55 on all files in this folder and it's sub folders 3) the environment variables PATH, CLASSPATH, DERBY_HOME, JAVA_HOME have been set and the following tests have been run successfully on the command line, so the server could be started and stopped as intended: -> java -jar -D/../derbydata $DERBY_HOME/lib/derbyrun.jar server start (and java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown) and also -> java org.apache.derby.drda.NetworkServerControl start (and java org.apache.derby.drda.NetworkServerControl shutdown). 4) I could connect to the server using ij either from localhost or (after adding -h 0.0.0.0 to the start command) from a client machine on my network 5) I have also deployed the war file in to tomcat (not sure if this was needed) 6) in tomcat manager derby is listed as an application in the 'List Applications' section 7) the start command hyperlink is not underlined in tomcat initially, so I assume this means the application is considered started and starts when tomcat starts 8a) when using the 'Stop' command I am receiving a message 'OK -Stopped application at conext path /derby' 8b) when then using the 'Start' command, I am receiving a message 'OK - Started application at context path /derby' 9) when trying to follow the hyperlink under path /derby on the same screen which issues http://localhost:8180/derby/, I am getting http 404 the requested resource is not available. 10) when typing http://localhost:8180/derby/derbynet into the browser instead I am getting HTTP 500 'Wrapper can not find servlet org.apache.drda.NetServlet or a class it depends on' Can someone please advise what is still needed or what I do wrong? Thanks Thomas
ONE solution found: ERROR 42X51 class not found
I have now found a solution. When packaging everything into a JAR file and installing the jar in the database my DDL executed successfully and the trigger using java code do what they are supposed to do. Still a miracle to me why it didn't work when storing the java code on the filesystem. Regards Thomas
Re: ERROR 42X51 class not found
Sylvain, when I executed 'java derbyPk.Functions' from the command line I received an error 'No such method: main' (not class not found). Then I inserted a method main just to see what happens and all main did was to print a 'Hello World' to the console. After recompiling and executing 'java derbyPk.Functions' now with a method main again I received Hello World as answer. I checked my file permissions and they are all the same with root as owner and others having read-permission. I do not think this a file permission problem. I then invoked ij and connected to the database as user=root (no longer as user=derby) [how do I know which user derby is running under, I thought root as I did not specify anything when starting the server?] Here are the results I received in ij: a) when I still had a methode main: 'CALL "TF_Clients_AI2"(1,'000','xxx');' ==> "TF_CLients_AI2" is not a recognised function or procedure 'CALL derby."TF_Clients_AI2"(1,'000', 'xxx');' ==> no answer, system seemed to hang and I exited out using Ctrl+C b) so I removed the method main again and recompiled: 'CALL derby."TF_Clients_AI2"(1,'000','xxx');' ==> good old ERROR 42X51, derbkyPk.Functions does not exist or is inaccessible followed by ERROR XJ001 java.lang.ClassNotFoundException 'CALL derby."TF_Clients_AI2"(1,1,1);' ==> when passing wrong data types I received 'VARCHAR' can not hold types 'INTEGER' 'CALL derby."TF_Clients_AI2"();' ==> derby."TF_Clients_AI2" is not a recognised function or procedure. Still confusing and I wonder what the problem will have been once it was solved (if it ever will). Thomas
Re: ERROR 42X51 class not found
derby.Functions is a class and TF_Clients_AI2 a method.
the java code reads:
package derbyPk;
public class Functions {
public static void TF_Clients_AI2(int iRowID, String vcClientID, String
vcClientName) throws SQLException
{
...
}
}
As said all of this worked fine when running the derby Networkserver on
localhost and with the java code stored in the filesystem on localhost.
Thanks
Re: ERROR 42X51 class not found
I am getting this error: miniserver:/var/lib/derby/db-derby-10.5.3.0-bin/lib# java derbyPk.Functions.TF_Clients_AI2 Exception in thread "main" java.lang.NoClassDefFoundError: derbyPk/Functions/TF_Clients_AI2 Caused by: java.lang.ClassNotFoundException: derbyPk.Functions.TF_Clients_AI2 at java.net.URLClassLoader$1.run(URLClassLoader.java:200) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:188) at java.lang.ClassLoader.loadClass(ClassLoader.java:307) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301) at java.lang.ClassLoader.loadClass(ClassLoader.java:252) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320) Could not find the main class: derbyPk.Functions.TF_Clients_AI2. Program will exit. here the proof that classpath is set: miniserver:/var/lib/derby/db-derby-10.5.3.0-bin/lib# echo $CLASSPATH /var/lib/derby/db-derby-10.5.3.0-bin/lib/ and yes folder /var/lib/derby/db-derby-10.5.3.0-bin/lib/ does contain a folder derbyPk and yes there is a file Functions.class and yes in Functions.java there is a function TF_Clients_AI2 which is defined as public. Thanks for your help Thomas
Re: ERROR 42X51 class not found
Hi, what I have done now is 1) opened up a root terminal session on the server 2) checked CLASSPATH with echo $CLASSPATH command -> emtpy 3) defined the CLASSPATH=/var/lib/derby/db-derby-10.5.3.0-bin/lib/ 4) exported the CLASSPATH 5) checked CLASSPATH setting again and now the path was returned 6) ran sysinfo again and saw the path was added at the end, as you can see here in the new sysinfo output (see last entry) Java classpath: /var/lib/derby/db-derby-10.5.3.0-bin/lib/derby.jar :/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbynet.jar :/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbytools.jar :/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbyclient.jar :/var/lib/derby/db-derby-10.5.3.0-bin/lib/ 7) opened a second root terminal session and copied my folder derbyPk and the all files included from my windows machine to the server as :/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbyPk 8) switched back to my first root terminal session (in which classpath variable was defined and then 8) started the network server in the same terminal session 9) tried to execute the DDL again against the server => unfortunately with the same result - still getting error 42X51
ERROR 42X51 class not found
i, I am trying to recreate data base objects (/ triggers) using java functions on a Linux server after having them tested successfully by executing the DDL against the derby network server running on localhost. The development is done using the Apache Nature in the Eclipse IDE and with a network server started on the same machine refered to as 'localhost' on the connect statement. The java code is stored on the filesystem of that same machine. I could create my data base objects as intended and the triggers are working fine. Now I wanted to deploy the solution onto my Linux server and am running into - most probably classpath - problems. The java code I have copied to the java home and also to the derby home folder on the server. the functions are defined as public. Here is what the sysinfo utility reports on my development machine (network server run on localhost which is a windows machine): -- Java Information -- Java Version:1.6.0_15 Java Vendor: Sun Microsystems Inc. Java home: C:\Programme\Java\jre6 Java classpath: C:\Daten\eclipse ganymede\derby; C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derby.jar; C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derbyclient.jar; C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derbynet.jar; C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derbytools.jar OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: Thomas Java user home: C:\Dokumente und Einstellungen\Thomas Java user dir: C:\Daten\eclipse ganymede\derby java.specification.name: Java Platform API Specification java.specification.version: 1.6 - Derby Information JRE - JDBC: Java SE 6 - JDBC 4.0 [C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derby.jar] 10.5.1.1 - (764942) [C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derbytools.jar] 10.5.1.1 - (764942) [C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derbynet.jar] 10.5.1.1 - (764942) [C:\Programme\eclipse-ganymede-SR1\eclipse\plugins\ org.apache.derby.core_10.5.1\derbyclient.jar] 10.5.1.1 - (764942) -- - Locale Information - -- Here is what sysinfo reports on the Linux server: -- Java Information -- Java Version:1.6.0_12 Java Vendor: Sun Microsystems Inc. Java home: /usr/lib/jvm/java-6-sun-1.6.0.12/jre Java classpath: /var/lib/derby/db-derby-10.5.3.0-bin/lib/derby.jar: /var/lib/derby/db-derby-10.5.3.0-bin/lib/derbynet.jar: /var/lib/derby/db-derby-10.5.3.0-bin/lib/derbytools.jar: /var/lib/derby/db-derby-10.5.3.0-bin/lib/derbyclient.jar OS name: Linux OS architecture: i386 OS version: 2.6.26-2-686 Java user name: root Java user home: /root Java user dir: /root java.specification.name: Java Platform API Specification java.specification.version: 1.6 - Derby Information JRE - JDBC: Java SE 6 - JDBC 4.0 [/var/lib/derby/db-derby-10.5.3.0-bin/lib/derby.jar] 10.5.3.0 - (802917) [/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbytools.jar] 10.5.3.0 - (802917) [/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbynet.jar] 10.5.3.0 - (802917) [/var/lib/derby/db-derby-10.5.3.0-bin/lib/derbyclient.jar] 10.5.3.0 - (802917) -- - Locale Information - Current Locale : [English/United States [en_US]] Found support for locale: [cs] version: 10.5.3.0 - (802917) Found support for locale: [de_DE] version: 10.5.3.0 - (802917) Found support for locale: [es] version: 10.5.3.0 - (802917) Found support for locale: [fr] version: 10.5.3.0 - (802917) Found support for locale: [hu] version: 10.5.3.0 - (802917) Found support for locale: [it] version: 10.5.3.0 - (802917) Found support for locale: [ja_JP] version: 10.5.3.0 - (802917) Found support for locale: [ko_KR] version: 10.5.3.0 - (802917) Found support for locale: [pl] version: 10.5.3.0 - (802917) Found support for locale: [pt_BR] version: 10.5.3.0 - (802917) Found support for locale: [ru] version: 10.5.3.0 - (802917) Found support for locale: [zh_CN] version: 10.5.3.0 - (802917) Found support for locale: [zh_TW] version: 10.5.3.0 - (802917) -- miniserver:~ Here is the output IJ reports showing the DDL executed properly on localhost: ij version 10.5 ij> CONNECT 'jdbc:derby://localhost:1527/test; create=true; user=d
Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
All, thanks for your support and further insight into how derby works which you have been providing in your answers. So if I understand correctly: (1) With the lack of a possibility to change the content of a transition variable in an After Insert trigger, (2) the lack of insert, update or delete in Before Insert triggers and (3) with the restriction of check constraints needing to be deterministic, it looks like (1) defining a default value on the colum (to ensure something is put there even if the user does not give a value on the insert itself) and (2) a SECOND after insert trigger which includes an Update statement seems to be the only option to implement an audit function if one wants to ensure that a column 'CreatedBy' on a table shows the login of the person who has inserted the row (regardless of what that person might or might not have included on the insert statement itself). (Note: a second after insert trigger, because the 'other' after insert trigger which executed a derby procedure is already needed to implement the audit function as such). I still need to implement this second after insert trigger to check details, but I would assume the update statement included will then itself fire the update trigger I defined for auditing changes on my table and I will then have audit records (1) showing the initially inserted value on the columns and (2) audit records showing the 'old' value fom the initial inserrt and records from after the update coming fom the second trigger - whereas I would have only had one entry in case I could have overwritten transition variable content in the first place with whatever would have been stored there prior to this being written back to the data base. Regards Thomas
Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
All, thanks for your support and further insight into how derby works which you have been providing in your answers. So if I understand correctly: (1) With the lack of a possibility to change the content of a transition variable in an After Insert trigger, (2) the lack of insert, update or delete in Before Insert triggers and (3) with the restriction of check constraints needing to be deterministic, it looks like (1) defining a default value on the colum (to ensure something is put there even if the user does not give a value on the insert itself) and (2) a SECOND after insert trigger which includes an Update statement seems to be the only option to implement an audit function if one wants to ensure that a column 'CreatedBy' on a table shows the login of the person who has inserted the row (regardless of what that person might or might not have included on the insert statement itself). (Note: a second after insert trigger, because the 'other' after insert trigger which executed a derby procedure is already needed to implement the audit function as such). I still need to implement this second after insert trigger to check details, but I would assume the update statement included will then itself fire the update trigger I defined for auditing changes on my table and I will then have audit records (1) showing the initially inserted value on the columns and (2) audit records showing the 'old' value fom the initial inserrt and records from after the update coming fom the second trigger - whereas I would have only had one entry in case I could have overwritten transition variable content in the first place with whatever would have been stored there prior to this being written back to the data base. Regards Thomas
Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
Hi, passing the values worked fine, so I can now log the new values into my audit log table. Do you know if it would be possible to CHANGE the new values as well? Background: Let's say my Client table has a column 'CreatedBy' in which I want to store the role of the user that inserted the row. For a proper auditing I need to ensure that ONLY the rolename of the user is stored - and the user has no possibility to insert something different (in case he would ty on the insert statement, whatever was specified here would be overwritten by the after insert trigger). In PL/pgSQL I would just add: NEW.RoleName = CURENT_USER into my after insert trigger SQL code. How would I do that in derby? Thanks
Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
Hi, thanks for your reply. the trigger needs to execute multiple SQL statement, which is why I am using an external procedure. I will try passing in the values as you suggest. Regards Thomas
Re: trigger calling a procedure
Hi, after having closed down Eclipse going in again and rebuilding from scratch the trigger could be created and actually worked for the first time without throwing the syntax error. Strange, as I am not aware I have changed anything. So although not fully understanding what is / was going on here, this I would consider solved. Thanks
after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
Hi,
have created the following procedure and trigger:
CREATE PROCEDURE "TF_ClientsAI"()
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
EXTERNAL NAME 'derbyPk.Functions.TF_ClientsAI';
-- DROP TRIGGER "TR_ClientsAI";
CREATE TRIGGER "TR_ClientsAI"
AFTER INSERT
ON rte."Clients"
REFERENCING NEW AS NEW
FOR EACH ROW
CALL DERBY."TF_ClientsAI"();
What I cannot find out is how I can refer to and use the new values inserted
into the Clients table when wanting to log them on another table.
I have tried:
public static void TF_ClientsAI()
throws SQLException
{
Statement stmnt = conn.createStatement();
StringcSQL = "INSERT INTO rte.\"EntityAuditLog\" \n" +
"(\"ClientID\") \n" +
"VALUES (NEW."ClientID")";
}
but this didn't work.
I have tried:
public static void TF_ClientsAI()
throws SQLException
{
int iClientID = NEW."ClientID"
// and then the insert statement into which I wanted to pass the variable
}
before using any other SQL inside the procedure, but this doesn't work either.
Can someone please advise. Have searched the derby documentation and the web but
have not found an example where this is done.
Thanks
trigger calling a procedure
Hi,
I am trying to use an after insert trigger defined on a table to log values of
each row that gets inserted in a audit trail table.
The error I am getting which I do not understand / know how to resolve is a
syntax error:
ERROR 38000: The exception 'java.sql.SQLException: Syntax error:
Encountered ";" at line 1, column 171.' was thrown while evaluating
an expression.
ERROR 42X01: Syntax error: Encountered ";" at line 1, column 171.
Here are my trigger and procedure definitions:
CONNECT 'jdbc:derby://localhost:1527/pmsdev; create=true; user=derby';
DROP TABLE rte."Clients";
CREATE TABLE rte."Clients"
(
"ClientID" character varying(15) NOT NULL,
"ClientName" character varying(50),
"RowID" integer generated always as identity,
"CreatedBy" character varying(128), -- NOT NULL,
"CreatedOn" timestamp, -- NOT NULL,
"LastUpdatedBy" character varying(128), -- NOT NULL,
"LastUpdatedOn" timestamp, -- NOT NULL,
"DeletedBy" character varying(128),
"DeletedOn" timestamp,
CONSTRAINT "PK_Clients" PRIMARY KEY ("ClientID")
);
CREATE UNIQUE INDEX "UI_Clients_RowID" ON rte."Clients"("RowID");
DROP PROCEDURE "TF_ClientsAI";
CREATE PROCEDURE "TF_ClientsAI"()
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
EXTERNAL NAME 'derbyPk.Functions.TF_ClientsAI';
-- DROP TRIGGER "TR_ClientsAI";
CREATE TRIGGER "TR_ClientsAI"
AFTER INSERT
ON rte."Clients"
REFERENCING NEW AS NEW
FOR EACH ROW
CALL DERBY."TF_ClientsAI"();
DISCONNECT;
EXIT;
public static void TF_ClientsAI()
throws SQLException
{
Connection conn = getDefaultConnection();
String cSQL = "INSERT INTO rte.\"EntityAuditLog\" \n" +
"(\"TransactionID\", \"Statement\", \"TableName\",
\"RowID\", \"LogSequence\", \"ColumnName\") \n" +
"VALUES (1, 'INSERT', 'rte.\"Clients\"', 1, 0, '')";
Statement stmnt = conn.createStatement();
stmnt.executeUpdate(cSQL);
stmnt.close();
conn.close();
}
Surprisingly I am getting the same error no matter which SQL statement I am
trying to submit!?
Thanks
Re: creating a SQL function in derby
Finally spotted the problem. The method definition should have read: public static void spGetNextID( int iNextVal[], String vcIDName ) (not public static int spGetNextID...) and the line trying to return the integer at the end return iNextVal[0]; needed to be removed. Now it works. Regards
Re: creating a SQL function in derby
Hi Rick,
thanks for your help once again.
I have now created my first procedure and have paid attention to the link you
provided to guide me regarding argument matching. Even after having read the
documentation multiple times and checking my code again and again, I am stuck
again and can't figure out why I am getting an 'No method was found that matched
the method call void derbyPk.Functions.spGetNextID(int[], java.lang.String)
error.
This is my procedure definition:
CREATE PROCEDURE spGetNextID(OUT iOut integer, IN vcIn character varying(128))
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
EXTERNAL NAME 'derbyPk.Functions.spGetNextID';
This is my Java method:
package derbyPk;
import java.sql.*;
public class Functions {
public static int spGetNextID( int iNextVal[], String vcIDName )
throws SQLException
{
Connection conn = getDefaultConnection();
int column = 1;
PreparedStatement ps = Utils.prepare
(
conn,
"select \"NextVal\"\n" +
"from rte.\"IDs\"\n" +
"where \"IDName\" = ?\n"
);
ps.setString( 1, vcIDName );
ResultSet rs = ps.executeQuery();
rs.next();
iNextVal[0] = rs.getInt( column++ ) + 1;
return iNextVal[0];
}
/*
Get the default connection, called from inside the database engine.
*/
static Connection getDefaultConnection()
throws SQLException
{
return DriverManager.getConnection("jdbc:default:connection");
}
}
and this is the application code to call the procedure:
public static void main(String[] args)
throws SQLException {
// TODO Auto-generated method stub
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
try {
Class.forName(driver);
} catch(java.lang.ClassNotFoundException e) {
}
String dbName = "pmsdev";
String connectionURL = "jdbc:derby:" + dbName + ";create=true";
try {
Connection conn = DriverManager.getConnection(connectionURL);
System.out.println("... Connection successful");
String vcIDName = "Transaction";
CallableStatement cstmt = conn.prepareCall("{
call DERBY.spGetNextID(?, ?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, vcIDName);
cstmt.executeUpdate();
int retVal = cstmt.getInt(1);
System.out.println(retVal);
} catch (Throwable e) {
System.out.println(e);
}
}
}
Seems I need to call the procedure fully qualified as 'DERBY.spGetNextID' as
otherwise I am getting an error that no procedure or funtion was found.
Hope you can easily spot what I am unable to find.
Regards
Thomas
Re: creating a SQL function in derby
Hi Rick, thanks for that. It already helped a lot and I am starting to understand. I successfully wrote my first function reading the value stored on my ID table and returning it. What I can NOT figure out is: you advised that I need to create a function (rather than a procedure) and you stated to use the 'CONTAINS SQL' function element. Actually when I use 'CONTAINS SQL' when defining the function, I am getting an error that I am trying to read data, but have not specified 'READS SQL DATA'. When I change 'CONTAINS SQL' to 'READS SQL DATA' my function works and returns the result. However I need to not only select, but also update the data by incrementing the ID value on the ID table (see the select for update & update in my pgsql sample). But it seems that using a function I can not update data (and the explanation in the derby manual which is given for 'contains data' honestly doesn't make sense to me) and when using a procedure I could update data, but with a procedure not return any value. However I need to update data and return the new value so that I know which ID has been assigned. Can you help with the solution of this please? Thanks again Thomas
RE: How would you implement a record-level versioning system with Derby and Java?
Hi Alessandro, We've implemented an approach similar to this for storing and tracking changes to XML documents - because the values we are storing could be large (base 64-encoded binary objects), we decided to have a 'attribute-value' table that would only allow inserts, plus an 'audit-record' table that tracks when/where/who changed the value. This approach works reasonably well for moderate sized documents (1MB / 100,000 values/changes), but it starts to slow down as the number of records/changes exceed 1,000,000. In Oracle and SQL Server, we implemented a VIEW on the attribute-value/audit-record table to only show the most recent value -- makes it easier/faster to get the latest value. Value history is easily retrieved by selecting all values from the attribute-value table with the same unique attribute-id. We haven't optimized our code for Derby, so haven't tried a similar approach here. Best Regards, Thomas Taylor -Original Message- From: Alessandro Bottoni [mailto:alexbott...@yahoo.it] Sent: Thursday, July 09, 2009 10:48 AM To: Derby Discussion Subject: Re: How would you implement a record-level versioning system with Derby and Java? Peter Ondru�ka ha scritto: > Actually I would store the history values outside the main table for > performance and storage overhead reasons (history table may have > additional columns, e.g. When-who-etc for auditing information). Peter Yep... This has to be kept into account, actually, and it makes the whole thing a little bit more complicated... Thanks for this suggestion, Peter. -- Alessandro Bottoni Website: http://www.alessandrobottoni.it/ Who wants to remember that escape-x-alt-control-left shift-b puts you into super-edit-debug-compile mode? -- (Discussion in comp.os.linux.misc on the intuitiveness of commands, especially Emacs.)
RE: Derby 10.1 -> 10.2 upgrade issue
Kal,
According to the Java 5 docs, the getIndexInfo ResultSet should contain all
of the required information (INDEX_NAME, COLUMN_NAME, NON_UNIQUE) - if the
index consists of multiple columns, then multiple ResultSet entries will be
returned for a given INDEX_NAME, and you'll have to use ORDINAL_POSITION to
recreate the sequencing of columns within the index.
If not, you might try looking at the thread/link that Rick Hillegas sent
(http://www.nabble.com/How-can-I-fetch-constraint-attribute-on-Column-Level-
from-SYS-Tables---td19554573.html#a19554573)
Thomas
INFOTECH Soft, Inc.
See DatabaseMetaData#getIndexInfo():
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIn
dexInfo(java.lang.String,
<http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getI
ndexInfo(java.lang.String, java.lang.String, java.lang.String, boolean,
boolean)> java.lang.String, java.lang.String, boolean, boolean)
From: Kalyan Inuganti [mailto:kinuga...@gmail.com]
Sent: Thursday, March 05, 2009 1:44 PM
To: tho...@infotechsoft.com; derby-user@db.apache.org
Subject: Re: Derby 10.1 -> 10.2 upgrade issue
Hi Thomas,
I have been doing some research on how i can get the column name(s) that the
index corresponds to (see the 2nd bold section of the SQL) and haven't found
anything yet. Any ideas? I would also like to know if a given index is a
unique index or not? The reason I say this is because I looked at "Create
Index..." statements and there are 2 flavors - ones with the Unique
qualifier and the others without.
statement.executeUpdate("CREATE UNIQUE INDEX "+indexNameString+" ON
DeviceInfo (DeviceID)");
Thanks a lot for your help!
Kal
On Thu, Mar 5, 2009 at 11:15 AM, Thomas J. Taylor
wrote:
Hi Kal,
I'll check to see if I can find the code/process that I used back then to
solve the issue. Since I only had one (remote) Derby installation causing
problems, once I figured out the way to resolve the problem (drop & recreate
index), I probably (1) used DBLook to identify the corrupt (missing)
indexes, then used SQurilleL to (2) identify the names of the keys through
the GUI, (3) write the DDL to drop and re-create the indexes.
You should be able to use JDBC to get the same index information and
drop/create the index that way; however, the challenge is identifying the
corrupt indices. Perhaps this might work?
Connection connection; // existing db connection
Statement statement = connection.createStatement();
try {
// test table to confirm corrupt index: SQLException is thrown if
corrupt
statement.executeQuery("SELECT DeviceID, DeviceName, DeviceType FROM
DeviceInfo WHERE DeviceID=1");
} catch (SQLException ex) {
// retrieve index information for the corrupt table
//
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIn
dexInfo(java.lang.String,
<http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html%23ge
tIndexInfo%28java.lang.String,%20java.lang.String,%20java.lang.String,%20boo
lean,%20boolean%29> java.lang.String, java.lang.String, boolean, boolean)
DatabaseMetaData databaseMetaData = conn.getMetaData();
ResultSet resultSet = databaseMetaData.getIndexInfo(null, null,
"DeviceInfo", false, false);
// for each index, drop & recreate the index
while (resultSet.hasNext()) {
// get the name of the
String indexNameString = resultSet.getString("INDEX_NAME");
statement.executeUpdate("DROP INDEX "+indexNameString+" ON
DeviceInfo);
// recreate index:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj20937.html
statement.executeUpdate("CREATE UNIQUE INDEX "+indexNameString+"
ON DeviceInfo (DeviceID)");
}
}
Thomas Taylor
INFOTECH Soft, Inc.
From: Kalyan Inuganti [mailto:kinuga...@gmail.com]
Sent: Thursday, March 05, 2009 11:10 AM
To: derby-user@db.apache.org
Subject: Derby 10.1 -> 10.2 upgrade issue
Hi,
I am reaching out to you guys for some help with a Derby indexing issue that
we have run into at Monsanto, St. Louis. The issue is pretty much the same
issue that was reported by Thomas J. Taylor in 2007 (The link is provided
below).
Brief Description:
I have a database that was originally created with Derby 10.1.1.0 and was
recently upgraded to Derby 10.2.2.0. I've performed this upgrade on several
copies of the same database schema (each created on different computers,
but with the same version of Java (1.5.0_07) and Derby (10.1)).
For all but one of the database upgrades, it worked correctly. However, in
one case, it appears that the PRIMARY KEY and FOREIGN KEY constraints have
been lost/corrupted. When I use DBLook to check a 'working' database, I see
the appropriate constraints for keys. However, on the '
