Re: NFS and Derby
On 11/11/2010 07:56, Kathey Marsden wrote: I have always told users they have to have their databases on a local disk to ensure data integrity and that a system crash for an NFS mounted database could cause fatal corruption, but had a user this morning take me to task on this and ask me to explain exactly why. I gave my general response about not being able to guarantee a sync to disk over the network, but want to have a more authoritative reference for why you cannot count on an NFS mounted disk although I did find several places where the sync option favors data integrity which certainly doesn't sound like a guarantee. Does anyone know a good general reference I can use on this topic to support my you gotta use a local disk mantra. Part of the issue is that that documentation is really old and file systems have moved on since it was written. There are other shared file systems that maybe do support integrity across the network with Derby, e.g. IBM's GPFS. Thus it's more complicated than local disk versus NFS. Also I think our documentation on this topic should be a bit stronger. Currently we just say it may not work and probably should be clearer that data corruption could occur. The documentation may need to state what Derby requires (sync through Java APIs ensure the data is recoverable) and then have per-file system sections, filled out on a scratch your own itch approach. E.g. even a local disk is not recoverable if the OS is performing disk caching. Dan.
Re: NFS and Derby
On 11/11/2010 16:59, Kathey Marsden wrote: On 11/11/2010 11:27 AM, Mike Matrigali wrote: [snip good summary of support limitations] I did not see anyway that a java program could find out if the required syncing was being enforced. Would it be reasonable to request such an API call in some future java version or would it just simply be impossible to implement? Actually there already is, the FileDescriptor.sync() call has this in its defined contract: SyncFailedException - Thrown when the buffers cannot be flushed, or because the system cannot guarantee that all the buffers have been synchronized with physical media. The problem maybe that the JVMs just do not implement this according to the spec. It also maybe true that a VM has no way of knowing if it could guarantee a sync, thus in reality it would throw SyncFailedException all the time ... Dan.
Re: delete from table
Rami Ojares / PDF-Comics Oy wrote: Hi, It seems that deleting a table takes a VERY long time. I am importing data into derby. And as I work with my import script I have to do the import MANY times. So in the beginning of my import I first delete everything from the tables I have previously imported into. It seems that deleting takes at least as long as doing the import. The import procedures have a replace parameter which performs truncation of the table before the import. That will be much quicker than the delete. Dan.
Re: New to Derby: Modifying Single User App to Server/Client
Bryan Pendleton wrote: Client Code: Properties props = System.getProperties(); I don't think you need to pass all the system properties to DriverManager.getConnection(). Try changing the above line to: Properties props = new java.util.Properties(); Also set derby.system.home on the server side, not the client side. Dan.
Re: Property/config management for embedded use
Benson Margulies wrote: I'm embedding derby in a webapp. The requirement to set derby.system.home to a pathname is giving me small fits. I really wish that derby.properties could be found by classpath, or that there was some API for setting the home directory. Can you use System.setProperty() to set derby.system.home before Derby is started? Dan.
Re: Not able to create encrypted database
Rick Hillegas wrote: You are, of course, right. As I go back through the thread, I don't see any success stories. It might be worth experimenting with a different encryption package, like this old package from the JDK 1.3 days: http://java.sun.com/products/archive/jce/ That might be a hopeless task, standard Java methods and classes have been removed from CDC/Foundation, there is probably a significant chance that the JDK 1.3 security features depend on those classes. From a quick look at the JME web-site, it seems that Foundation 1.1 supports crypto, the best approach would be to make the changes in Derby to support that environment, it may be mainly build changes to allow Derby to load its crypto support in an CDC/Foundation environment. http://java.sun.com/javame/reference/apis.jsp http://java.sun.com/javame/reference/apis/jsr219/ Dan.
Re: Using Derby-style table functions
Tim Dudgeon wrote: Hi thanks for that advice. I've given it a try, but am getting a ClassNotFound exception that I can't explain. I added a simple method that I set up a s a functiona dn that works fine, but when I try to set it up as a table function, even with a null implementation it fails. I have a class that looks like this package org.apache.derbyDemo.vti; Try changing the package to your own space (ie. not org.apache.derby*), for security reasons Java functions that go against derby's internal classes are not allowed, therefore they maybe a check against disallowing any table function with org.apache.derby* as a package. Dan.
Re: IBM J9 Personal Profile 1.1 + OSGi + Derby
Paul French wrote: Thanks Dan, I had a look at the source code last night and noticed the EmbeddedActivator instantiates a EmbeddedDataSource which by default uses java.sql.Driver. I will probably write my own Activator instead. The problem now is the DriverManager provides static methods to return connections where as we need to deal with an instance of a javax.sql.DataSource to get connections. The current Activator instantiates an EmbeddedDataSource and does not need to retain a reference to it since connections are obtained via the static DriverManager.getConnection method. I don't see the current activator (org.apache.derby.osgi.EmbeddedActivator) creating an EmbeddedDataSource?? Ideally current EmbeddedActivator needs to do both. I suppose it can check for the existence of the java.sql.Driver class whether to use a EmbeddedDataSource or EmbeddedSimpleDataSource. But since the java.sql.DriverManager and javax.sql.DataSource provided connections in a different way I'm not sure how to accommodate both? Not sure what you mean here, the activator doesn't create connections. Its only purpose is to start the core engine up, applications would continue to get connections using whichever api they prefer, DataSource (all) or DriverManager (jdbc 3, jdbc 4). Really EmbeddedActivator should be booting the engine using internal methods similar to how EmbeddedSimpleDataSource.findSriver() does, and shutting down the engine using EmbeddedSimpleDataSource to get a connection and not DriverManager. Dan.
Re: IBM J9 Personal Profile 1.1 + OSGi + Derby
Paul French wrote: Hello All, I noticed I derby.jar is an OSGi bundle and so I installed it in my OSGi container (Knopflerfish 2.0.5) running on IBM J9 PP1.1 I included the required jdbc.jar (JSR169) in the bootpath of the J9 jvm. I can install the bundle no problems. When I try and start the bundle I get: java.lang.NoClassDefFoundError: java.sql.Driver From reading this page on the wiki http://wiki.apache.org/db-derby/JavaMESupport?highlight=(j2me) I know java.sql.Driver does not exist in JSR169. Hence how do I tell the derby bundle not to use java.sql.Driver and use instead javax.sql.DataSource This is an outstanding bug, DERBY-873: http://issues.apache.org/jira/browse/DERBY-873 Dan.
Re: Derby CLA location
matt.graves wrote: The NOTICE file for Derby mentions that parts of the code are licensed by IBM under the “Software Grant and Corporate Contribution License Agreement” or “Derby CLA.” That's IBM's license *to* the ASF. If you download Derby from the ASF it's all under the ALv2. Dan.
Re: Derby CLA location
matt.graves wrote: Daniel John Debrunner-2 wrote: That's IBM's license *to* the ASF. If you download Derby from the ASF it's all under the ALv2. Dan. Does that mean the original CLA from IBM doesn't exist anywhere? I'm sure it is within the records of the ASF. My guess is that it was based upon or identical to the standard software-grant: http://www.apache.org/licenses/software-grant.txt I would like to read it. I was just trying to point out that that license does not affect how the licence (ALv2) under which folks download Derby, thus typically it isn't of interest. Dan.
Re: Invalid XJ215 Error?
Peterson, John wrote: Would you agree that closing the ResultSet when the next() method returns false is an undesirable side effect which conflicts with the expected behavior, or have I overlooked something? JDBC 3.0 early mandated that the result set was closed when all of the rows have been retrieved, that's the behaviour you are seeing. JDBC 4 changed the rules for statement completion (in an incompatible way) and Derby has not reconciled the behaviour yet. The same code supports all the JDBC versions (with additional methods/classes added for newer versions) and traditionally the same behaviour was required across JDBC versions (e.g. JDBC 3 was a super-set of JDBC 2). No one has addressed requiring different behaviour for the same method across different JDBC versions. Changing the behaviour for JDBC 4 will also cause backwards compatibility issues for applications that change to Java 6. Dan.
Re: Invalid XJ215 Error?
Daniel John Debrunner wrote: Peterson, John wrote: Would you agree that closing the ResultSet when the next() method returns false is an undesirable side effect which conflicts with the expected behavior, or have I overlooked something? Actually in section 15.2.5. of JDBC 4 specification there is this note: Note – Some JDBC driver implementations may also implicitly close the ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next method of ResultSet returns false. So Derby's current behaviour is allowed by the spec. Note that in the example you show the Blob becomes invalid due to the commit, which must be because you are in auto-commit mode. In non-auto commit mode the ResultSet would still be closed but the Blob object should remain valid until the the transaction is committed or rolled back. Dan.
Re: Invalid XJ215 Error?
Peterson, John wrote: I am unable to locate in any JDBC specification (http://java.sun.com/products/jdbc/download.html#102spec) the requirement that a ResultSet should be closed when all of the rows retrieved. I also do not see this behavior with MSSQL (2000 or 2005), or with Oracle (9 or 10). Also, this functionality must have been recently fixed or added for Derby 10.3 because I did not see this behavior in Derby 10.2 or 10.1 either. JDBC 3.0 section 10.1 The result set is closed as soon as one of the following occurs: - all of the rows have been retrieved ... JDBC 4.0 section 15.2.5 Note – Some JDBC driver implementations may also implicitly close the ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next method of ResultSet returns false. I believe this closing of result sets has always been in Derby. Dan.
Re: how do you make a column a primary key later?
Amir Michail wrote: Hi, I would like to reference a column in another table but that column is not designated as a primary key and unfortunately referring to it explicitly results in this error: ERROR XSCH5: In a base table there was a mismatch between the requested column number 6 and the maximum number of columns 7. That sounds like a bug, can you provide a script to reproduce it? So is there a way to make a column a primary key? alter table table_name alter column column_name primary key doesn't work. I think you need to add it as a table level constraint. here's some examples: ALTER TABLE WAREHOUSE ADD CONSTRAINT WAREHOUSE_PK PRIMARY KEY (W_ID); ALTER TABLE DISTRICT ADD CONSTRAINT DISTRICT_PK PRIMARY KEY (D_W_ID, D_ID); Dan.
Re: Moving a Derby database across timezones
Alan Burlison wrote: I have been looking at the derby documentation for TIMESTAMP, and it's pretty confusing when it comet to try to figure what will happen if I move a database onto a machine in a different timezone. http://db.apache.org/derby/papers/JDBCImplementation.html#Derby+SQL+TIMESTAMP -- Derby’s SQL TIMESTAMP type represents a time of day in the form -mm-dd hh:mm:ss.f (nanosecond granularity) with no associated time zone information. java.sql.Timestamp A JDBC Timestamp (java.sql.Timestamp) by definition represents a point in time, with nanosecond resolution, in a given time zone. -- So what does no associated timezone information mean? Does Derby normalize all timestamps to UTC, or does it just throw away the timezone information altogether? If it is the second, that makes it impossible to move databases across timezones without having to recalculate any timestamps it uses. It is the second, which is defined by the SQL standard which has datetime values with and without timezone. In SQL terms (section 4.6 of the SQL standard foundation document) Derby's TIMESTAMP is TIMESTAMP WITHOUT TIME ZONE Dan.
Re: Signed jar file loaded in DB
bradm6406 wrote:
Hi,
I am having problems running code from within a signed jar file if the jar
file has been loaded into the database using sqlj.install_jar.
I have signed my jar file and if I add the jar file to my Java ClassPath
then the policy file below works just fine and I can run the code in my jar
file. If I remove the jar file from my Java ClassPath and install the jar
into the database using sqlj.install_jar('my.jar', 'APP.myClass') and
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath',
'APP.myClass') then I get the error The exception
'java.security.AccessControlException: access denied
(java.util.PropertyPermission derby.system.home read)' was thrown while
evaluating an expression. when trying to read the derby.system.home
property in code inside my jar file.
I am using the default security manager (java.security.manager=).
Here are the relevant pieces of my policy file.
keystore my.keystore;
grant signedBy myalias
{
permission java.io.FilePermission ${derby.system.home}${/}-,
read,write,delete;
permission java.util.PropertyPermission derby.*, read;
permission java.lang.RuntimePermission loadLibrary.*;
permission java.util.PropertyPermission user.dir, read;
};
I tried setting the keystore value to ${derby.system.home}${/}my.keystore;
in case the problem was that Derby was unable to find my.keystore without
having the full path to it, but that didn't work.
I am using Derby 10.3.2.1 on a Windows XP Pro machine.
Any suggestions?
Can you share the stack trace?
The only thing I can think of is you are not getting the system property
in a privileged block, but then that should also fail if the jar is on
the classpath. (assuming this is being called in both cases from a SQL
procedure or function implemented in Java).
Dan.
Re: Signed jar file loaded in DB
Daniel John Debrunner wrote: bradm6406 wrote: Hi, I am having problems running code from within a signed jar file if the jar file has been loaded into the database using sqlj.install_jar. I am using Derby 10.3.2.1 on a Windows XP Pro machine. Another possibility is DERBY-553, are you using JDK 1.5? http://issues.apache.org/jira/browse/DERBY-553 Dan.
Re: Any ETA on truncate table ?
Kathey Marsden wrote: fancellu wrote: I really miss this from Oracle. I just want to empty out a table without all the logging and delays of delete from table. This is logged as https://issues.apache.org/jira/browse/DERBY-268. Nobody has signed up for it so far. You could vote for it. or get involved (code, test and or document etc.) yourself ... http://wiki.apache.org/db-derby/DerbyDev Dan.
Re: Transaction Problems of a Derby Stored Procedure
sin(EaTing), wrote: *In order to preserve transactional atomicity, database-side JDBC procedures that use nested connections have the following limitations. • Cannot issue a commit or rollback, unless called within a CALL statement. • Cannot change connection attributes such as auto-commit. * If these items are true for my stored procedure, then I cannot modify the isolation level and neither change the autocommit mode. The autocommit mode cannot be changed, it is always false for server-side connections. Calling setAutoCommit(false) is allowed since it doesn't change the mode. Set isolation level I'm not sure about. And I am yet confused about the first statement *Cannot issue a commit or rollback, unless called within a CALL statement.* Could a Derby procedure be called without a CALL statement? Procedures are only supported in CALL statements. However server-side jdbc can also exist in functions which can be called from any expression. So the documentation you found is somewhat incorrect, it could be improved to use routines when describing functions and procedures, and just procedures when needed. E.g. In order to preserve transactional atomicity, database-side JDBC routines that use nested connections have the following limitations. • Can only issue a commit or rollback within a procedure. • Cannot change connection attribute auto-commit. Dan.
Re: java.sql.SQLDataException: The resulting value is outside the range for the data type DOUBLE.
Knut Anders Hatlen wrote: Hong Ji [EMAIL PROTECTED] writes: Thanks for the replies. It will definitely help if the Java Double.MAX_VALUE and Double.MIN_VALUEare supported in Derby. Any reason why the Double.MAX_VALUE/Double.MIN_VALE are different in Derby and Java? According to the comments in the code, this is a limitation that was introduced for compatibility with DB2. Being compatible with DB2 is no longer a requirement for Derby, so if it's not causing any backward compatibility issues and someone signs up for DERBY-3290, there's nothing stopping us from lifting the restriction. DERBY-3290 is a different issue, that's for adding support for non-numeric values to DOUBLE such as Double.Nan, which is believed to be against the SQL standard. Extending the range to match the java range should be a separate issue. Dan.
Re: names of columns in VALUES
Jim Newsham wrote:
It seems there is no way to name the columns produced by the VALUES
keyword.
http://db.apache.org/derby/docs/dev/ref/rreftableexpression.html#rreftableexpression
ij select * from (values ('aa', 'b')) AS
T(MYCOL,MYOTHERCOL);
MYCOL |MYOTHERC
aa|b
1 row selected
On a slightly related note, for orthogonality it would also be useful to
be able to produce a resultset with a finite number of columns but zero
records, using the VALUES keyword. For hand-written sql, this might be
useless, but for some of the auto-generated sql I produce, it would save
some special casing and code contortion. But this one is minor… the
renaming would be much more helpful.
select * from (values ('aa', 'b')) AS
T(MYCOL,MYOTHERCOL) where 1 = 0;
HTH,
Dan.
Re: Installing and Using Java Procedures.
Briggs wrote:
I'm having a bit of a problem installing a java procedure in derby
(10.3). I thought I understood what I could do with them but now...
I have created a simple hello world procedure:
--
package derby.proc;
public class HelloWorldProcedure {
public static String helloWorld(final String name) {
return hello + name;
}
}
--
Then created a jar file called DerbyProcs.jar in my temp directory
(with the above class).
Then I run the following commands:
CALL SQLJ.install_jar('c:\tmp\DerbyProcs.jar', 'APP.DERBY_PROCS', 0);
CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.DERBY_PROCS');
CREATE PROCEDURE APP.HELLO_WORLD(
IN NAME VARCHAR(20),
OUT HELLO_YOU VARCHAR(30)
)
PARAMETER STYLE JAVA
READS SQL DATA
LANGUAGE JAVA
EXTERNAL NAME 'derby.proc.HelloWorldProcedure.helloWorld';
-- CALL THE PROC
CALL APP.HELLO_WORLD('world');
The result I get is:
Error code 3, SQL state 42Y03: 'APP.HELLO_WORLD' is not recognized
as a function or procedure.
Line 13, column 1
What am I missing? Am I wrong on what I am expecting to happen?
You defined an procedure that takes two arguments, but then only passed
one, so no matching procedure will be found. Then as your later e-mail
says this needs to be a function, OUT parameters are returned using one
element arrays in the Java method's argument list.
See:
http://wiki.apache.org/db-derby/DerbySQLroutines
this page has a list of useful topics:
http://wiki.apache.org/db-derby/HintsAndTips
Dan.
Re: Generate Always and SQLIntegrityConstraintViolationException
[EMAIL PROTECTED] wrote: I'm currently running the tests with the following patch which seems to fix the repro: Index: java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java === --- java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java (revision 614214) +++ java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java (working copy) @@ -183,6 +183,7 @@ if (SanityManager.DEBUG) SanityManager.DEBUG(CloseRepeatInfo,Close of RowResultSet repeated); +cachedRow = null; closeTime += getElapsedMillis(beginTime); } If the tests pass, I'm ready to attach it to a Jira issue. That just defeats the purpose of cachedRow, which is the ability to re-use the same result across multiple executions (opens). I think the real bug is that canCacheRow is being passed in as true for the row of (default) when it should be false if the default column definition does not translate to a constant over time. With a default of CURRENT TIMESTAMP does the same problem occur? Dan.
Re: Generate Always and SQLIntegrityConstraintViolationException
[EMAIL PROTECTED] wrote: Daniel John Debrunner [EMAIL PROTECTED] writes: I think the real bug is that canCacheRow is being passed in as true for the row of (default) when it should be false if the default column definition does not translate to a constant over time. So the compiler actually generates incorrect parameters for the RowResultSet constructor in this case? That's my guess. Dan.
Re: getImportedKeys() and getTables() return no rows
blyon wrote: Well, first of all, the ij tool that derby provides for interactive command-line SQL is case-insensitive for table and column names; if you execute all lower-case sql (as I did when I created my tables) the table and column names in derby come out all upper case. Second, when I execute sql commands using Statement.executeQuery(String), I get the correct answer using all lower-case table and column names. So both ij and executeQuery are executing SQL statements and here Derby is following the SQL standard rules for SQL identifiers. Unquoted identifies (e.g. mytable, MyTable) correspond to the upper-case version of the identifier (i.e. MYTABLE), whereas quoted identifies (e.g. myTable) correspond to the value without quotes and no case modification (i.e. myTable). Quoted identifiers also allow characters not allowed in unquoted identifiers, such as space, at-sign, exclamation point etc., e.g. my Table !. I can also get the derby column names from the result set meta-data after a query such as select * from service order by name when the derby table name is apparently really SERVICE and the column name really NAME. Here Derby is returning the actual column name, which is the converted form of the SQL identifier used in create table. So I was programming against derby for quite some time before I found that the case of the table or column names mattered; hence my bafflement when my attempts to get database meta-data (as opposed to result set meta-data) did not work. It is true that column names in ResultSet getter methods are case-insensitive, whereas table, column, etc. names in DatabaseMetaData must match the stored form the identifier. This is defined by the JDBC specification, no idea why it is different, but Derby is just following the spec. Dan.
Re: Workaround needed for nested trigger problem ERROR 54038
Peder Hultin wrote: FANTASTIC!!! This idea works perfectly; thanks so much! This has delayed me quite a number of hours. For the INSERT case are you using a trigger or will a simple DEFAULT clause work for you? Is probably faster than a trigger. create table t (i int, ts timestamp default current timestamp); insert into t(i) values 1; select * from t; I |TS -- 1 |2008-01-04 16:17:53.89 Dan.
Re: Double.NaN in table?
David wrote: For an application I am writing I need to store doubles in a table. I thought I could use the DOUBLE type when creating the columns in my tables. Unfortunately the insert fails when the value of the double is Double.NaN. We use NaN a lot in our analysis. Is there a way to make this work, or should we just convert everything to a string? Any ideas? One possible alternative is to use a BIGINT column. Use the java method Double.doubleToLongBits() when storing the value in the database, e.g. double myDouble = ...; ps.setLong(1, Double.doubleToLongBits(myDouble)); and Double.longBitsToDouble() when reading values, e.g. double myDouble = Double.longBitsToDouble(rs.getLong(1)); I haven't tried this, and you should probably read the descriptions for those methods, they have some detailed info on Nans. There is also the doubleToRawLongBits() method that could be used. No idea on how indexing on the BIGINT column would work with this approach, ie. if the index order would be useful. HTH, Dan.
Re: Double.NaN in table?
Andrew ``Bass'' Shcheglov wrote: On Dec 19, 2007 12:23 PM, Thomas Nielsen [EMAIL PROTECTED] wrote: Looking at the derby code on the main trunk, I see the exception is intentional, as there is an explicit check for NaN values in NumberDataType.normalizeDOUBLE(). Not sure if this is another DB2 compatability issue/limitation or not. Maybe someone with deeper knowledge can give more details on this? This is odd, for Oracle, for instance, fully supports IEEE 754 (since 10g): http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf Note that's Oracle specific and non-standard, requires: - a Oracle specific SQL data type BINARY_DOUBLE or BINARY_FLOAT - requires use of the Oracle specific setBinaryDouble()/setBinaryFloat() after casting the PreparedStatement down to an OraclePreparedStatement. Dan.
Re: Case insensitive indexed search possible?
Bernt M. Johnsen wrote: Rick Hillegas wrote (2007-11-02 05:21:09): Hi Jon, The situation with case-insensitive searching did not change in the latest 10.3 release and no one has volunteered to address this issue in the next feature release, 10.4. If you want to avoid the full table scan, you can store the text in two columns: mixed case in one column, and normalized to lowercase in the second column. Note that in standard SQL, there is no feature that allows for case insensitive searching, so Rick's proposal with a canonical representation (e.g. lowercase) of the data is the only portable way of doing this. SQL does allow implementation specified collations and thus provides the framework to perform case-insensitive searching, though specifying (ie. DDL or create database) how will be database specific. With the language based ordering added to 10.3 Derby has the basic blocks to implement case-insensitive searching. At the database level (applies to all user character strings) would be the easiest, then at the schema level (all data within that schema), then at the data type level. The database level would require adding support for a new collation, and then ensuring that collation leads to the correct java.text.Collator being set up. I think it might be as easy as using the current code and setting the strength of the current collator to be something other than primary(?). Dan.
Re: Case insensitive indexed search possible?
Daniel John Debrunner wrote: Bernt M. Johnsen wrote: Rick Hillegas wrote (2007-11-02 05:21:09): Hi Jon, The situation with case-insensitive searching did not change in the latest 10.3 release and no one has volunteered to address this issue in the next feature release, 10.4. See DERBY-1748 for details on an easy way to implement this. https://issues.apache.org/jira/browse/DERBY-1748 Dan.
Re: Storing Java classes in a Derby DB
Francois Orsini wrote: Loading classes from a database http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#cdevdeploy30736 On 10/24/07, *Daniel Jue* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: But Derby already support class loading from a database, can't you use that Classes loaded by Derby can only be used within the context of a SQL statement, e.g. a procedure or function. Dan.
Re: Multiple database reboots in a single program?
Kim Haase wrote: While working on a fix for DERBY-1823 (a documentation issue concerning user authentication and authorization), I've tried to write a sample Java program that connects to a database using the embedded driver, does some things, shuts it down, starts it up, does some more things, shuts it down again, and so on. I always get an error trying to connect to the database the second time, although I've followed the advice in Shutting down the system You are shutting down the complete Derby system, not just the database. This means in order to reconnect to databases you need to restart Derby. This is done by loading the JDBC driver again, you will need to actually create an instance of the driver, since the class will already be loaded. HTH, Dan.
Re: Returning NULL from a database-side Java function
Alan Burlison wrote:
Rick Hillegas wrote:
It is hard to say where the NullPointerException originates. Could you
post a reproducible test case and the full stack trace from derby.log?
Umm, it may be my bad, I have the following:
public static int iGoBang() {
int pop = 0 == 0 ? 1 : null;
return pop;
}
Which compiles but blows up, whereas
public static int iGoBang() {
return null;
}
won't even compile. Because you can't assign null to a primitive type -
duh. Which leaves 2 questions:
1. Why does int pop = 0 == 0 ? 1 : null; even compile?
Maybe the compiler figures out that it's the same as pop = 1 so it
discards the null? Seems like it shouldn't compile.
2. How *do* you return a database NULL from a function with a primitive
type as the return type? String isn't a primitive type.
You need to use the corresponding object type, in this case
java.lang.Integer as the return type.
Dan.
Re: Derby and character set encodings
Ken Frank wrote: the one remaining question, for the folks at derby-user (and adding derby-dev) is the first one: 1. when one creates a new derby database, is the database created with a certain encoding that will be used ? No. The database doesn't have an encoding. Dan.
Re: DDL in Trigger Procedure
Francois Orsini wrote: Javier, I guess and I may be wrong that the main reason for not allowing DDL operation in a trigger is due to the fact that a DDL operation will get implicitly committed, DDL is not implicitly committed in Derby. Dan.
Re: check if DB exists already?
Teja wrote: How do I check if the database exists already? I know create=true in the connection url creates if db doesn't exist and throws a SQLWarning if exists already, but is there a way to know for sure that a database exists already before connecting? Why not just attempt a connection? What do you gain by knowing if the database exists or not? Dan.
Re: DBMD.getTables() not handling quoted table names correctly?
David Van Couvering wrote: HI, all I am testing quoting identifiers in NB, and I am using DatabaseMetaData to see if a table exists. I create a table called test. In SquirrelSQL I see that it is called TEST because Derby auto-upper-cased it, as expected. Then I run DatabaesMetaData.getTables(null, null, test, null); and it returns no rows. If create a table, *with quotes*, called 'quotedTable'. and then call DatabaseMetadata.getTables(null, null, quotedTable, null); this returns a row, with the table name as 'quotedTable'. Shouldn't DBMD auto-upper-case the identifier just like regular SQL? No. See the javadoc for DMD.getTables(): tableNamePattern - a table name pattern; must match the table name as it is stored in the database Dan.
Re: Returning multiple rows from a procedure call
John C. Turnbull wrote: Can anyone help me fetch multiple rows from a procedure call? You can return JDBC one or more JDBC ResultSet's from a procedure. See this wiki page http://wiki.apache.org/db-derby/DerbySQLroutines HTH, Dan.
Re: before update trigger
Stephen Ince wrote: Brian, Thanx for your response. I will vote for it. I will definitely concider joining the development effort. I was able to get the synatx correct. My next question is can you pass in the new and old row into a procedure call. No, just the individual columns/values of the old and new rows. Dan.
Re: User/password encryption and deployment
Bernt M. Johnsen wrote: Michael Segel wrote (2007-06-16 00:23:56): Which is why I'm a little suspect that the *only* way to do encryption on the wire is to be forced to bring in IBM's JCE. You don't need the IBM JCE. Sun's JDK comes with and JCE which works just fine. The docs tries to tell you that if you use an old IBM environment, you need to install IBMS JCE searately. That section (installing an IBM JCE) should be removed from the documentation for 10.3 onwards since JDK 1.4 is the lowest supported JVM level. There is, however small issue, if you choose ENCRYPTED_USER_AND_PASSWORD_SECURITY, newer Sun JCE's (from 1.4, I think) does not support the shared DHS value defined in the DRDA protocol. It's too weak. As an alternative solution for passsword protection, Francois implemented STRONG_PASSWORD_SUBSTITUTE_SECURITY. This information would be great to add to the docs. Restating the requirements in terms of a JCE that supports the shared DHS value defined in the DRDA protocol (whatever the correct JCE term for that is) and not specifically the IBM JCE. The documentation then should state that this is not supported by some JCEs due to its weakness and an alternative is to use STRONG_PASSWORD_SUBSTITUTE_SECURITY (and/or SSL?). Dan.
Re: Executing command from a file via JDBC
Jim Newsham wrote: -Original Message- From: Bryan Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, May 10, 2007 9:58 AM To: Derby Discussion Subject: Re: Executing command from a file via JDBC I didn't want to distribute derbytools.jar with my application. I guess was looking for something that came with the core jdbc driver. You could put something together yourself pretty easily: BufferedReader rdr = ...; String sql; Statement stmt = conn.createStatement(); while ( (sql = rdr.readLine()) != null) stmt.executeUpdate(sql); Would that serve your purposes? thanks, bryan I had a need to run sql scripts into our database at runtime as well. I didn't find anything in the Derby API, so I whipped the following up. We use it to create the database structure from a .sql resource, the first time our application runs (and also within unit tests, since we want a newly initialized copy of the database for each test). It's not bullet-proof (read the comments), but it was sufficient for our purposes. If something like this doesn't already exist in Derby, it sure would be useful... with the documented deficiencies remedied, of course. How about the runscript() method in ij? runScript public static int runScript(java.sql.Connection conn, java.io.InputStream sqlIn, java.lang.String inputEncoding, java.io.OutputStream sqlOut, java.lang.String outputEncoding) throws java.io.UnsupportedEncodingException Run a SQL script from an InputStream and write the resulting output to the provided PrintStream. SQL commands are separated by a semi-colon ';' character. Parameters: conn - Connection to be used as the script's default connection. sqlIn - InputStream for the script. inputEncoding - Encoding of the script. sqlOut - OutputStream for the script's output outputEncoding - Output encoding to use. Returns: Number of SQLExceptions thrown during the execution, -1 if not known. Throws: java.io.UnsupportedEncodingException Dan.
Re: Syntax error on some derby metadata calls
Bob Durie wrote: Hi, I’ve searched for this issue on jira with no luck, posting here in hopes someone has seen it or knows where it might be coming from. What I’m seeing is that “sometimes” during a schema upgrade of our database, the getImportedKeys or getPrimaryKeys methods of EmbedDatabaseMetaData fail with syntax errors (see the stack trace below). If we rollback the transaction, try again, it is ALWAYS successful, but may fail later on when we do a similar operation with another table. Hence, we have a workaround (try again), but it doesn’t leave me feeling very confident. [snip] One thing to note is that the process in our application calls the “connection.getMetaData” many many times, makes updates to the schema, and calls it again many times on the same transaction. While not necessarily efficient, I’m reluctant to change it as I want to know the source of the problem first. I think it's a bug in Derby. The metadata queries use some internal SQL constructs and are compiled in internal SQL mode to allow these constructs. It looks like something is triggering a re-compile of the metadata statement at runtime and during this re-compile the internal SQL flag is not set. I'm not sure what you could be doing that results in the recompile, one possibility is a rollback in a transaction that performed some DDL. Somthing like: begin tran ddl execute meta data for first time rollback Derby at the moment is fairly liberal in its statement invalidation when a rollback happens in a transaction that performed DDL, assuming that this is a infrequent and non-performance critical event. Dan.
Re: Foreign key -- implicit index?
Dan Karp wrote: Is there any way to explicitly assign a name to this backing index? In my case, I want to force the optimizer to choose the backing index using -- DERBY-PROPERTIES index=. Instead of using 'index=', use 'constraint=' with the name of the constraint. See: http://db.apache.org/derby/docs/10.2/tuning/ctunoptimzoverride.html Dan.
Re: Derby storage using Java Web Start PersistenceService API - is it doable?
David Van Couvering wrote: Hi, Luigi. I looked at this once, and it really didn't seem that the API provided by the JWS persistence service was sufficient for the needs of Derby. But I didn't look too closely at it. For me demo needs, a popup was fine. What you'd have to do is provide a new implementation of the storage interface. Others on this list can give you better guidance than I about where to look and how to do this. I suspect this is not for the faint of heart... As Andrew pointed out this is would be an implementation of org.apache.derby.io.StorageFactory against Java Web Start PersistenceService API. The StorageFactory is is an abstraction around the java filesystem classes so in theory it's not hard to understand. All of the database specific internals are above this layer and would not need to be changed. It's much like the Directory abstraction in Apache Lucene Java. You can find the internals java doc at http://db.apache.org/derby/javadoc/engine/ then look for the org.apache.derby.io package. Here's some information on getting involved in Derby's development: http://wiki.apache.org/db-derby/DerbyDev I would encourage you to subscribe to derby-dev and involve the development community in implementing this (asking questions, incremental development etc.). The in-memory implementation Andrew references may not be the best model to follow, the implementation seems to add new api classes with no indication of what their functionality is required to be, i.e. no comments. Then the original contributor never responded to any questions and non-one else has had the itch to pick up the code. Thus that implementation has stalled and not been committed. It would be a shame for any Java Web Start PersistenceService implementation to go the same way. In the Derby code there are three implementations of StorageFactory that are in use, for file system, jar files and classpath. There is one more that is not in use, fetching data from an http server. Look for subclasses of StorageFactory. HTH, Dan.
Re: Possible to configure Derby for case-insensitive compares by default?
Mamta Satoor wrote: Hi Jon, Currently, you can't configure Derby to do case insensitive searches on CHAR/VARCHAR. Using LOWER() function is a possible solution but as you suspected, this will have a performance hit because Derby does not support functional indexes. So, eventhough you might have an index on the column, the LOWER() function on that column will not use the index on column. I think DERBY-455 is the Jira entry for functional index support in Derby. Mamta ps There is effort going on to support language based ordering as part of DERBY-1478 but that probably won't help you with case insensitive search. I think that the hard work being done on DERBY-1478 (by Mamta) sets the foundation for any default collation order at the database level, such as case-insensitive search. If anyone wants to scratch that itch they should join in or follow the discussion on derby-dev for DERBY-1478. https://issues.apache.org/jira/browse/DERBY-1478 Remember Derby is open source, if you need a feature please join the development community and work on adding it. http://wiki.apache.org/db-derby/#head-1a71e5af4a1c7bc0998cc44cc9f0b5cac9b5fea7 Thanks, Dan. On 3/12/07, *Jon Miller* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi all, I'm wondering if anyone knows if it's possible to configure Derby so that searches on CHAR/VARCHAR fields are case insensitive by default? I'm guessing that if the answer is no, the solution is to just do a LOWER() or similar on the field and compare it that way. I'm guessing if you do that though, it won't use the index if the field is indexed? Jon
Re: How to execute optimizer overrides in a java app
Jean T. Anderson wrote: ResultSet rs = s.executeQuery( SELECT num, addr FROM derbyDB --derby-properties index=IDX1 \r order by num); Is there any feedback on using the \r ? In the back of my mind I'm thinking that won't be portable between Windows and Unix. \n is the unicode newline character in Java. There is no portability concerns here, this code is creating a newline in a Java unicode string which is then passed to Derby's unicode parser. The string constant will be stored in the class file which is platform independent. Dan.
Re: Security in Derby
Andrew McIntyre wrote: On 2/21/07, Kim Haase [EMAIL PROTECTED] wrote: 2) In a JDBC program, calling the org.apache.derby.jdbc.ClientDataSource.setSecurityMechanism method 3) Specifying the connection URL property securityMechanism Note that these two are for specifying the security mechanism for the connection client side. But with the last one it seems you have to use a numeric literal? This seems unfortunate. I wonder if that is why it is not documented? I'm not clear on the reason for the lack of documentation, but I agree it is unfortunate. I've opened DERBY-2361 for adding documentation for the connection URLs. See also DERBY-963 for changing the client to use text names instead of numbers to match (1) and (2). To be precise, 2) and 3) are both setting the same client property/attribute using a numeric value. In the example shown for 2) a Java constant is used that represents a number. In the DataSource case the property is a int property, in the JDBC attribute case the value needs to be an integer, but the api means that the integer has to be passed as a string (use of a Properties object or a JDBC URL). Alternate ways of setting 2) 3) can show use of a numeric literal for 2) and use of the Java constant for 3). 2a) setting the database source property through a application server api would have to use 3 (e.g. xml configuration file, html form). 2b) ds.setSecurityMechanism(3); 3a) Setting the JDBC attribute in a Java program could use the constant, e.g. properties.setProperty(securityMechanism, Integer.toString(ClientDataSource.STRONG_PASSWORD_SUBSTITUTE_SECURITY)); Dan.
Re: Does anyone tested policy file usage in derby 10.2.2 ?
legolas wood wrote: Hi Thank you for reading my post. Does any one tried and used derby under a security manager a a policy file? Yes, over 95% of Derby's functional tests run with a security manager. I tried to use it under a security manager and a policy file but my policy file never take effect. indeed derby ignored any policy that i defined. is there any sample policy file out there? I tries sample policy file which is included in Derby reference? and it does not take affect. How are you setting up the security manager and policy file, and can you provide the policy file you are trying? Also how are you determining that the policy file never takes effect? Thanks, Dan.
Re: ERROR 40XD1: Container was opened in read-only mode when performing SELECT DISTINCT on database in jar
Thomas Kelder wrote: Considering the static properties, do you know a way to set a static property from within the java program before loading the driver (so without using the derby.properties file or a command-line option)? The only option I could find in the documentation to set the properties programatically was to use a Properties object when calling DriverManager.getConnection, but this requires the driver to be loaded first. You cannot set Derby properties (derby.*) using the properties object passed to DriverManager.getConnection. That properties object is only for JDBC attributes, like user, create, password etc. To set a system property, you can use java.lang.System.setProperty(). Dan.
Re: does derby provide SQL access management (grant and revoke permission)
legolas wood wrote: I have some technical questions, - What is differences between a database that have SQL authorization and a database that does not have any SQL authorization? I mean is there any changes in schema structure? - Can we create a database with SQL authorization enabled in a server environment without any property related to SQL authorization in derby.properties? - Is it possible to enable sql authorization for a database that is created without any sql authorization ? We have a database that has no SQL authorization because we were not aware of this future in derby 10.2 and now we want to add SQL authorization to it. I connect to database using a server that its derby.properties has all SQL authorization property enabled, but we can not execute any grant or revoke statement on it. Does this help? http://db.apache.org/derby/docs/10.2/devguide/cdevcsecure866060.html Dan.
Re: Failed to start database
Roozbeh wrote:
Hi,
I'm facing a really annoying problem and that is Failed to start database.
I'm using maven to create the database and the run some test cases on the
database one after each other. I first create a connection and create the
database and then I shutdown the connection in the next step I again try to
connect to the embedded database but basically I face this problem:
java.sql.SQLException: Failed to start database 'c:/database', see the next
exception for details.
[trace snipped]
Does anyone have any Idea?
Look at the next exception. :-)
SQLException supports a chaining model and Derby uses it when multiple
exceptions are involved or more context is needed that just the original
exception. Typically the actual problem is the last exception in the
chain, displaying the entire chain will make debugging issues much
easier. Here's an example of how to walk the chain.
catch (SQLException e) {
do {
System.out.println(e.toString());
e.printStackStrace(System.out);
e = e.getNextException();
} while (e != null);
}
Dan.
Re: Complete Derby/JDBC Stored Proc Example ?
Fisher Associates, Inc. wrote: Does any one have a complete example showing: * How to define and utilize a Java Class as a Stored Proc within Derby * How to test same within ij * How to call from JDBC Any help and insights greatly appreciated ! In addition to the resources Rick provided, Derby's wiki has some information: http://wiki.apache.org/db-derby/DerbySQLroutines Feel free to improve it! Thanks, Dan.
Re: Some design related questions about using derby embedded mode.
Francois Orsini wrote: Hi Legolas, Hope you find some of the answers below: 1) We need highest performance, Should we use stored procedure? Writing and running stored procedures in Java allows you obviously to run Java logic within the same JVM as the Derby DB core engine - If your application runs Derby embedded, you may not gain from a noticeable improvement as everything runs embedded In fact you may notice a slight performance degradation comparing java procedures to an embedded application. The reason is that a java procedure is stateless, thus it cannot keep a reference to any Statement or PreparedStatement objects. So while a embedded client program can prepare a statement once and re-use it many times, the code within a Java procedure needs to be: PreparedStatement ps = conn.prepareStatement(SELECT ...); ResultSet rs = ps.executeQuery(); // process rs ps.close(); Thus the additional cost of object creation for the PreparedStatements can impact performance, typically a compile cost is not involved as Derby has a internal cache for compiled plans. Since the api (JDBC) is the similar for server-side Java procedures the bulk of the logic can be written such that it works in embedded, client or server-side. Doing these allows easy performance comparisons of your own code in various situations, which is the best way of testing. - However, you will bypass some of the JDBC layer(s) as the stored procedure will run inside the database spectrum in theory, with direct access to data, not requiring the use of JDBC for that JDBC is the api used to access data from within a Java procedure. Derby's embedded JDBC code is the same as the server-side JDBC code. - If you feel that you can run some of your logic inside the database, then it certainly a good thing to do it but as usual, there are tradeoffs as far as portability ( i.e. stored procedure in Java), if you ever intend to port your stored procedure logic to other RDBMS that does not support this. Though Java procedures tend to be standard based as the standard appeared before the technology in most databases. Oracle and DB2 support Java procedures according to the standard. Dan.
Re: ERROR 08001:No suitable driver
Craig L Russell wrote: Hi Mike, I've made that same mistake too many times to count. It sure would be a nice feature in ij for it to wait for the semicolon for a decent interval (1 minute?) and then ask the user if they are done typing yet ;-) Craig On Jan 18, 2007, at 8:22 AM, Dunk, Michael (Mike) wrote: Got a connection now. A missing quote and semi colon at the end caused it to hang. IJ seems like it should validate for simple mistakes like this. Rather than the complexity of a timeout, ij could follow the example set by other shells and print a different continuation prompt. E.g. ij connect 'jdbc:derby:cs' Dan.
Re: ERROR 08001:No suitable driver
Anders Morken wrote: Anders Morken: Ask and ye shall receive. I've got a hack in my trunk checkout now, and it seems to work. Even the tests still pass. Amazing. Bah, I need to learn to keep my mouth shut until I've reviewed all the results... Famous last words, etc. I'll need to give it a bit more of a kickin'. =) I'll stick it in a JIRA for review and further work. =) ...when I've decided on a clean way to make sure ij can tell interactive sessions and test harness runs apart, and reliably turn off the line continuation prompts for test harness runs. =) Sorry for the noise. =) Does LocalizedInput.isStandardInput() help? Dan.
Re: Result set as a return value of a Java stored procedure
Mirek Cervenka wrote:
Hello,
I have a following problem. I want to return a result set as a
consequence of some processing by a Java stored procedure on Apache
Derby DB, however, I am still getting a NULL resultset. Could anyone
help me please? I would be very grateful!
Here is the code. Registration of the stored procedure is as follows:
CREATE PROCEDURE myTestProcedure() LANGUAGE JAVA PARAMETER STYLE JAVA
CONTAINS SQL DYNAMIC RESULT SETS 1 EXTERNAL NAME
'StoredProcedures.myTestProcedure'
The actual implementation of the stored procedure is:
public static void myTestProcedure(ResultSet[] rs) {
try {
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
} catch (Exception ex) {
ex.printStackTrace();
}
There's no need to load any JDBC driver in a server side procedure.
You may find this wiki page useful:
http://wiki.apache.org/db-derby/DerbySQLroutines
Dan.
Next release to require CDC/Foundation 1.1 on J2ME - any concerns?
The next release (10.3) of Derby most likely will not support JDK 1.3, instead requiring JDK 1.4.2 or higher (see DERBY-1982). As part of that effort it makes sense for the support for J2ME/CDC/Foundation 1.0 to be dropped as well and instead require J2ME/CDC/Foundation 1.1 (see DERBY-2210). This is because Foundation 1.0 is based upon JDK 1.3 and Foundation 1.1 is based upon JDK 1.4. Do any users have concerns if support for JSR 169 (JDBC subset for J2ME/CDC) requires Foundation 1.1. If so please raise them here or add a comment to DERBY-2210. Thanks, Dan. http://issues.apache.org/jira/browse/DERBY-1982 http://issues.apache.org/jira/browse/DERBY-2210
Re: java.lang.NoClassDefFoundError: java.math.BigDecimal
Jonas wrote: I'm using IBM WSDD version 5.7.1 that ships with IBM J9 JDK, i think, compatible with JDK1.3. J9 has an implementation of this JVM for Microsoft Windows 2003 PDA. The idea is to run java on this PDA and of course JDBC :) Derby is compatible with JSR169, a jdbc optional package for microedition CDC/ Foundation Profile 1.0. Using the EmbeddedSimpleDataSource from Derby i think i get JSR169 compatibility but FP1.0 not comes with java.math.BigDecimal and derby seems it need it. In order to run the app, in the Eclipse Run configuration, i added the following arguments: -jcl:foun10 -Dij.dataSource=org.apache.derby.jdbc.EmbeddedSimpleDataSource -Xbootclasspath/a:...\bootjdbc.jar I order to not use SMF (OSGi) from the ibm WSDD, i create a bootjdbc.jar that includes the classes from jsr169.jar and database_enabler.jar needed to use JDBC. I believe database_enabler.jar is the JDBC 2.0 jar for WSDD and should not be used with foundation as it requires classes not supplied by foundation (e.g. BigDecimal). This page has an example of the command line used with WCTME 5.7 (the new name for WSDD) for Derby's tests. It uses the jdbc.jar (JSR169) provided with WCTME 5.7. http://wiki.apache.org/db-derby/JunitVmIssues#head-0916dd3630b0667e49460439fbd041c720d93eaf Dan.
Re: Best choice for column's data type to represent boolean data
Rick Hillegas wrote: Hi Ian, Two issues block the introduction of a BOOLEAN datatype: 1) We need DRDA support in order to transport this type across our network layer. I am cautiously optimistic that we will see this type appear in the DRDA spec early in 2007. So, soon, this issue will not block us. I don't see this as blocking adding BOOLEAN into the SQL language, we have the XML type which is supported in SQL but not JDBC. Incremental development is good. 2) DERBY-887, as you note, also blocks us. The behavior of certain casts would have to change and that, we believe, would significantly impact existing applications. Our rules about backwards compatibility suggest that this kind of change can only occur in a major release. That, as you note, would be Derby 11.0. I don't believe enough analysis of this cast issues has happened to decide if adding BOOLEAN would require 11.0 or not. I still really can't figure out from all the comments in DERBY-887 what people expected to happen with the strange queries like id = (03) and what does happen. And were those DERBY-887 tests run before the BOOLEAN patch was committed or after? I would encourage those that want BOOLEAN and are willing to work on it to come to the derby developer list and kick off the discussion there. Dan.
Re: R: R: Use SQL parsing
Flavio Palumbo wrote: Well, I made a little tool where a user can submit free SQL statements ... I'd like to parse it before submitting to trap sintax errors. So I assume you are looking to make sure that well-formed SQL statements are created before the statement is sent to the real database. I guess that a statement such as: SELECT A,B FROM T needs to be seen as valid, even if T does not exist, or A, B are not valid columns in T. Derby does not support that yet, but it's possible to add that type of functionality. I could imagine something like a database type of 'syntax' that would only provide syntax checking on a prepare, e.g. a JDBC URL of jdbc:derby:syntax: There would be no store behind this database, just the SQL layer of Derby. In the past I've run Derby like this to show how the layers are independent of each other, i.e. a storeless Derby that could perform any SQL query that did not go against a real database object (e..g VALUES 1). I've been messing with the some code in this area now (and in the past) so I'll look into making progress on this for the next major release of Derby. Dan.
Re: have identity_val_local(), want table.curr_id
Keith Irwin wrote:
Folks---
I want to run something similar in a single transaction (just a loop
that runs each query one after another then commits):
Assuming table (shorthand):
order (order_id autoincrement, name)
items (item_id autoincrement, order_id, name)
And queries:
insert into orders (name) values ('foo');
insert into items (order_id, other) values (identity_val_local(), 'bar');
This works fine. It picks up the order_id inserted automatically (via
autoincrement) on the order table and uses it in the items table foreign
key.
However, when I do the following:
insert into orders (name) values ('foo');
insert into items (order_id, other) values (identity_val_local(), 'bar');
insert into items (order_id, other) values (identity_val_local(),
'quux');
I get a constraint error because the second identity_val_local() call
refers to the recent item table autoincrement, and not the order
autoincremented key (which makes sense reading the docs).
Is there some way to do the above without having to use:
Can you use a single INSERT statement for the items?
insert into items (order_id, other) values
(identity_val_local(), 'bar'),
(identity_val_local(), 'quux');
Dan.
Re: Why booleans as Strings?
Marc Schlegel wrote: Hi. Why do I have to write the create and startup properties as a String? It would be much more convenient with a boolean type. Or are there any architectural issues. If this is for the Derby data source properties then I think it was just an initial mistake, but for backwards compatibility it needs to be kept. One possible solution is for Derby to add new boolean properties such as createDB and shutdownDB and deprecate the old versions. Just would need some clear rules on how the two properties interact. Dan.
Re: Heap and stack size for Derby server
Bryan Pendleton wrote: My problems came from a simple query containing an IN clause with 5000 items in it. I went over this easily by increasing the stack size limit to 1024 KB. Thanks Robert! That definitely sounds like DERBY-47. If you have the time, it'd be great to have some help in working on improving this part of Derby. I don't think this is DERBY-47. DERBY-47 is the issue that the plan generated by an IN query is inefficient. This issue is that a query with a large number of IN parameters fails to compile due to a stack overflow error. Dan.
Re: is it a good practice to have several connections to an embedded DB
Stanley Bradbury wrote:
The main advantage I see to having multiple connections open to an
embedded database is to allow multiple concurrent transactions to
execute. When only one connection is open you can only have one SQL
statement executing at a time.
The last sentence above is true but also not true. :-)
It depends on what is meant by executing.
In Derby only one statement per connection can be actively executing on
any thread, but the connection may have multiple open queries that are
in the middle of execution. Ie. this is valid, assuming the connection
is not in auto-commit mode.
ResultSet rs1 = ps1.executeQuery();
ResultSet rs2 = ps2.executeQuery();
Both of these queries are open and the application can interleave
rs1.next() and rs2.next() calls as needed in any order, e.g.
while (rs1.next())
{
if (rs.getInt(1) == 77)
{
rs2.next();
}
}
The application can also execute DDL or DML statements while these
queries are open, e.g.
while (rs1.next())
{
if (rs.getInt(1) == 77)
{
rs2.next();
int value = rs2.getInt(2);
ps3.setInt(1, value);
ps3.executeUpdate();
}
}
The single actively executing comes in if two threads tried to execute
any action using the same connection, e.g. if T1 executed rs1.next() and
T2 rs2.next() then T2 would block until the rs1.next() had returned for
T1. I would strongly recommend not sharing a JDBC connection across
multiple threads, it basically means you've pushed database
synchronization requirements into the application.
Of course, maybe Stan meant only one SQL transaction, which would be
completely true. :-)
Dan.
Re: NPE in setTimestamp(int,Timestamp,Calendar)
Jose de Castro wrote: A NullPointerException occurs if you pass in a null Timestamp into org.apache.derby.client.am.PreparedStatement.setTimestamp(int,Timestamp,Calendar) Does anyone know if this is being addressed in 10.3? I have included a suggested fix. Is this something I should add to JIRA? Please enter a Jira bug and please attach a patch to that issue. Thanks, Dan.
Re: HELP on creating SQL function
jon INFANTE wrote: Robert, thank you for the prompt reply to my inquiry, Im soo confused when you said that only Java functions are supported by derby, does it mean that I cannot use the derby function on SQL statement?? The CREATE FUNCTION statement creates a function that can be used in SQL but must be implemented by a public static Java method. This information on the wiki might help you. http://wiki.apache.org/db-derby/DerbySQLroutines Derby does not yet support SQL functions that are implemented by the SQL RETURN statement. I've messed around a little with this, one day I will get it closer to being committed to derby's trunk. http://wiki.apache.org/db-derby/SqlPsmSupport Dan.
Re: Newbie question about proper shutdown sequence
Dres Den wrote: I'm using Derbie as an embedded database for my single-user, single-threaded, single-everything app. My question is, if I already do a JDBC close (i.e. Connection.close()), do I still have to issue a: DriverManager.getConnection (jdbc:derby:;shutdown=true); Ideally yes. You will not corrupt any data if you don't, but the next time you open a connection to the same database roll-foward recovery will occur, possibly slowing down your startup. The shutdown just ensures the database is in a consistent state on disk, so that no recovery is needed. (I know that the ensuing SQLException about Derby shutdown is normal). (PS. I know this is a general JDBC question, but Derby seems like a friendlier place to ask! :)) I would say actually you asked in the right place, the shutdown=true is Derby specific. I'm glad you find this a friendly place. :-) Dan.
Re: class does not exist or is inaccesible (function)
Jim McNamara wrote: hi- I can start ij and connect to the db. I am trying to run some code off the mailing list archive. for some reason it will NOT recognize the place I store MyMathFuncs. here is the link for the code sample http://tinyurl.com/zkjj3 . I made an exact copy of the code. the server is started in a separate cmd window. the function is stored in a project directory named budgetcalc in eclipse. the package name is bcalc2. the create statement was done using the squirrelSQL editor. I kept getting the same error last night. can someone please give me a tip? thanks for any help -- jim here is my cmd window in dos: C:\jadeCheckBookcd c:\derby\frameworks\NetworkServer\bin C:\derby\frameworks\NetworkServer\binset CLASSPATH =c:\derby\lib\derbyclient.ja r;c:\derby\lib\derbytools.jar;c:\eclipse\workspace\budgetcalc\bcalc2\MyMathFuncs Couple of possible problems: 1) From your description I would expect the class path for the math functions to be just c:\eclipse\workspace\budgetcalc the version you have seems to include the package name and the class name. 2) The class MyMathFuncs needs to be accessible to the database server, not the client. You seem to be setting up the classpath with the math functions for the client here. The server's class path would be the one that contains derbynet.jar. HTH, Dan.
Re: MySQL TO_DAYS() emulation in Derby
Robert Enyedi wrote:
Michael,
As far as I know, year 0 in MySQL is actually 1 AD. However, it doesn't
necessarily matter when that year is when you have the comparability of
the results.
What matters that this function in combination with its reverse,
FROM_DAYS, allows easily for one to compute things like:
- are both timestamps in the same day? TO_DAYS(tstamp1) = TO_DAYS(tstamp2)
I think this will have the same result:
DATE(tstamp1) = DATE(tstamp2)
- how many days are between the two dates? TO_DAYS(date1) - TO_DAYS(date2)
Use the TIMESTAMPDIFF function (see below)
- what is the date if one adds X days to a specified date?
FROM_DAYS(TO_DAYS(date1) + X)
Use the TIMESTAMPADD function.
I'm not specifically in love with this function either, but what other
alternatives does Derby offer to make these computations?
DATE(number) looks the same as FROM_DAYS.
http://db.apache.org/derby/docs/10.1/ref/rrefdatefunc.html
For the others you can use the TIMESTAMPADD and TIMESTAMPDIFF JDbC
escape functions, see the bottom of this page:
http://db.apache.org/derby/docs/10.1/ref/rrefjdbc88908.html
Note these are JDBC escape functions, thus they have to be called using
{fn }, here's an example from that page.
{fn TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP)}
Returns a timestamp value one month later than the current timestamp.
Dan.
Re: MySQL TO_DAYS() emulation in Derby
Robert Enyedi wrote: This is exactly what I've been trying to do. The difficulty which I'm unable to overcome yet is that the timestamp returned by java.sql.Date.getTime() is not divisible with the number of miliseconds in a day (24 * 60 * 60 * 1000). Yet when I create a java.util.Date() instance with that timestamp, this gives me the 00:00:00 hour of the desired day (so no time information is in the timestamp). This puzzles me, so that's why I decided to ask around. Regards, Robert [EMAIL PROTECTED] wrote: Java calendar and date objects work in miliseconds but I think that fields marked as date rather than timestamp just have their hour/minute/second parts set to 0. Even so, it's fairly easy to roll a timestamp back to 00:00:00. You can then subtract the Date.getTime() values and divide by 86,400,000 to get days. Or am I missing something obvious here? When the timezone of the client is different to the timezone of the server then these type of calculations can get interesting. Derby's JDBC implementation notes contain some (hopefully) clear technical descriptions of how Derby handles the some of the interaction of its SQL date time types with JDBC's date time types. It covers the cases for TIMESTAMP - java.sql.Timestamp DATE - java.sql.Date TIME - java.sql.Time http://db.apache.org/derby/papers/JDBCImplementation.html There are a couple of bugs in this area as well, DERBY-889, DERBY-1811. These are when using ResultSet.getTimestamp to perform this conversion, TIME - java.sql.Timestamp. Following the discussion around those issues I will be updating that document to cover other date time conversions. Dan.
Re: Intermittent PreparedStatement Error
Inns, Jeff wrote: 2006-09-14 12:45:03,127 INFO [STDOUT]at com.extol.util.jdbc.CachePreparedStatement.setString(CachePreparedStatem ent.java:469) Is this your prepared statement cache or JBOSS's? Might be interesting to see if the bug occurs with that removed. We have found a work around. If we set the system property derby.language.statementCacheSize to a value of 1, we never get the exception. We have tried setting the property to 1000, and it helps in that we don't get the error as frequently, but it still happens eventually. This does make it seem like a Derby bug. But I'd still be interested in a run without the com.extol prepared statement cache. A couple of interesting things about our application: 1. We don't use Hibernate, CMP or JDO. 2. We have an in house framework for caching SQL connections - at a maximum we could have 16 open connections to the database per JVM. Practically, we don't usually see more than 10 or 15 (1 remote JVM and 1 embedded JVM). Theoretically we could have 32. Is this the com.extol.util.jdbc.CachePreparedStatement code above? I assume from the naming it is caching statement objects as well. I can provide SQL for the table definitions if that is necessary. Could this problem stem from unclosed statements or result sets? I don't see how, but who knows. :-) Can you try with derby.language.statementCacheSize unset (ie default) but set this property? derby.language.stalePlanCheckInterval=1000 This stops the compiled plan within Derby checking itself to see if it is out of date so frequently. Just another possibility that could come into play with the statement cache. Thanks, Dan.
Re: ClassFormatError while performing a SQL insert
Leslie Software wrote: [snip ClassFormatError with long SQL] Is this a known issue? Is my SQL too long? Some of this is DERBY-766/DERBY-176 that have been fixed in 10.2, now in beta and available for testing: http://wiki.apache.org/db-derby/TenTwoRelease Note that beta jars not suitable for production use and create databases that cannot be upgraded to GA versions. Most likely that will fix your problem. There is another optimisation (that I can't find the Jira number for) that will resolve any issues you may hit with a large number of character constants in a statement (as in your case). I think the number of rows you have mean you won't hit the issue. The other option is use to a single SQL statement per row inserted and use PreparedStatements. You could use a loop with ps.executeUpdate() to insert many rows, or ps.addBatch(). insert into ruling (card_id, ruling_date, ruling) values (?,?,?) Dan.
Re: ClassFormatError while performing a SQL insert
Bryan Pendleton wrote: java.lang.ClassFormatError: Invalid method Code length 69936 in class There is a hard limit in the JDK class-file format of 64K Just to be clear, one of the limits in the Java class file format is 64k bytes of byte code instructions per-method. A large String constant in a method does *not* contribute to this limit. There is a limit on *number* of constants in a java class file (65535) but not the size of those constants. What makes up a entry in what is called the constant pool is more complicated than a 1-1 mapping with literals in the Java language, e.g. class names and descriptions of methods take up entries, a String literal will take up two entries, a integer constant can take up no entries. For bedtime reading see http://java.sun.com/docs/books/vmspec/ (section 4.10 in the *first* edition contains the limitations of the class file format). Dan.
Re: ClassFormatError while performing a SQL insert
Michael Segel wrote: Outch. Missed the first part of Derby's statement. Ok. Doesn't make sense though. His insert is a batch insert? No, a single INSERT with a VALUES clause that has multiple rows (I think he said around 1700). Imagine if you had a table where you were inserting a record that was larger than 64KB. (Not including blobs) Are you saying that Derby would have failed prior to your fix? No. The record size is not a factor here. It's the size of the generated code in a single method. The byte code required to assemble those rows at runtime from the VALUES clause. The size of any individual value has no real effect on the size of the byte code, the same amount of byte code is required to create 'Fred' as 'the complete work of Shakespeare follows '. Dan.
Re: Using Apache DBCP and Tomcat with Derby - database shutdown?
[EMAIL PROTECTED] wrote:
Hi Deepa,
Thanks a lot for your help. That worked like a charm.
So I have now changed my code to this:
((EmbeddedDataSource)ds).setShutdownDatabase(shutdown);
((EmbeddedDataSource)ds).getConnection();
Just a couple of warnings with this code:
1) This affects the datasource for every application or thread that
is using it.
2) Some J2EE app servers will wrap the database's DataSource
implementation in their own, meaning the cases will fail.
Another way is to have another Derby data source setup in the
configuration that has the shutdownDatabase property set to shutdown.
Then you would do:
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup(java:comp/env);
DataSource ds = (DataSource)
envCtx.lookup(jdbc/EmployeeDB_shutdown);
try {
ds.getConnection();
} catch (SQLExcepion sqle) {
}
Dan.
Re: Using Apache DBCP and Tomcat with Derby - database shutdown?
--- Daniel John Debrunner [EMAIL PROTECTED] wrote: 2) Some J2EE app servers will wrap the database's DataSource implementation in their own, meaning the cases will fail. Sorry I mistyped, I mean the casts will fail. Dan.
XMLPARSE/XMLSERIALIZE question
I insert an XML document into an XML column using XMLPARSE (with 10.2) and select it using XMLSERIALIZE. The raw input to the XMLPARSE had as its first line: ?xml version=1.0 encoding=utf-8 ? I don't see that being generated when I select it with XMLSERIALIZE, is that expected? Thanks, Dan.
Re: NullPointerException in Derby client
Craig L Russell wrote: Hi Robert, Please file a bug report in the Derby JIRA. Include the stack trace along with an ij script that creates the tables and a simple jdbc program that issues the query that causes the NPE. Someone on the Derby dev team will follow up. There is no Derby team, Derby is an open-source community where anyone can get involved and work on improving Derby in many ways including fixing bugs. People work on what interests them: fry their own fish or scratch their own itch. Entering a bug may catch the interest of someone already active in the community, but it may not, there is no will about it. One sure way to get a bug fixed is to work in the community on fixing it. Here's an explanation of how development at Apache works: http://db.apache.org/derby/derby_comm.html#Understand+How+Development+Works+at+Apache The community is always ready to welcome new people. http://wiki.apache.org/db-derby/DerbyDev Dan.
Re: Alias referencing from the HAVING clause
Stanley Bradbury wrote: Robert Enyedi wrote: Isn't it possible to reference an alias from inside the HAVING clause? The documentation does not touch this issue. Or is there a more maintainable way to rewrite the erroneous query? Regards, Robert It looks to me like the fix for DERBY-883 might address this problem. You can download the beta version of 10.2 and test it, if you do so please post your results to this thread as feedback on the 10.2 feature. The fix for DERBY-883 is not in the 10.2 beta yet. Hopefully it will be in the next 10.2 snapshot, beta or release candidate. You can try it out by downloading the trunk source and building it. Dan.
Re: 10.2.1.1 beta now available for testing
Stephen Caine wrote: Rick, Is there a process for updating an existing 10.1 database? I get this error: Yes, the process is described here for 10.0 to 10.1, but it's the same for 10.1 to 10.2. http://wiki.apache.org/db-derby/UpgradingTen It's also documented: http://db.apache.org/derby/docs/10.1/devguide/cdevupgrades.html Dan.
Re: DerbyClient has problem with whitespaces in connection urls
Doug Bunting wrote: Dan, I understand though I don't see anything in the JDBC specification requiring spaces be allowed in a jdbc: scheme URL. Did I miss something? The JDBC 4 spec says The contents and syntax of the subname will depend on the subprotocol.. Thus the format is implementation defined. Derby's database names are a reflection of file system names and those allow spaces and to be ease-of-use Derby allows direct use of File.getName()/getPath() without encoding. My question was about a change which seems unnecessary moving the jdbc:derby: scheme /further/ from RFC 3986. The incompatibility with earlier Derby versions makes matters worse. What incompatibility? Dan.
Re: j2me pieces
Daniel John Debrunner wrote: Rick Hillegas wrote: As part of building the beta, I will need the j2me pieces needed to trigger the building of the optional jsr169 support. Could someone email me the relevant classes.zip and jdbc.jar referred to by BUILDING.txt? Getting my hands on these files is proving to be a chore. I have downloaded what I think is the relevant distribution from http://www-306.ibm.com/software/wireless/wctme/bundle.html. However, unpacking this distribution seems to involve installing Websphere first. So now I am pulling on a ball of yarn. Hopefully, someone can help me fast-forward to the end of this process. Of course only send Rick jars if you have the rights to do so under whatever licence you obtaned them. Rick, the JSR169 jar is available from java.sun.com, might be easier for you in many ways. and I'm sure the J2ME foundation jars are somewhere on the java site as well. Dan.
Re: j2me pieces
Rick Hillegas wrote: As part of building the beta, I will need the j2me pieces needed to trigger the building of the optional jsr169 support. The functional spec for DERBY-97 has the link for the J2ME downloads including: http://java.sun.com/products/jdbc/download.html#cdcfp Dan.
Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type.
Peterson, John wrote: Our concern with casting NULL to the appropriate type is that it might have a performance impact on the other databases. At this point it appears as though we would have to do performance testing of our software on all supported databases to determine if the impact of the CAST is worth supporting Derby as well. However, if this is a bug which will be fixed in a future release our efforts will be for naught. Clearly it would be preferable if we could avoid expending our resources on adapting and testing code which currently already works. We are trying to assess whether the Derby community considers this issue a bug before making our decision about whether or not our software will support Derby. I think it's a bug, similar to DERBY-7 (sorry for the earlier confusion). Since this is open source, rather than spending the time modifying your application you could get involved in the development of Derby. The community is always open to new folks, please join. Subscribe to derby-dev, read the wiki, and ask questions on derby-dev. http://wiki.apache.org/db-derby/DerbyDev Dan.
Re: obtaining catalog info on primary and foreign keys
[EMAIL PROTECTED] wrote: Thank you Jean for your answer. In fact, i had found these two tables but the question is how to get column names that take part in PKs and FKs.. By querying Syskeys and Sysforeignkeys i can identify the keys associated to a table but they do not seem to store the information concerning the columns that take part on those keys. Something like a relationship between sysconglomerates with syscolumns would do it, but unfortunately is not provided. maybe i have missed something, but... How about the JDBC metadata calls java.sql.DatabaseMetaData.getImportedKeys java.sql.DatabaseMetaData.getExportedKeys Should be standard, rather than having to write code specific to a database's system tables. Dan.
Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type.
Peterson, John wrote: Hello, It was reported to me that one of the reasons our software currently doesn't work with Apache Derby is because of ERROR 42X89. One of the engineering teams reported: We found that there is a bug in Derby in its support for the Case statement. There is a workaround but that will require us to change the SQL generation code to specifically check if we're connected to Derby and generate a different SQL syntax. After inquiring after the specifics, I was provided with the following example: ijvalues case when 1=2 then 3 else NULL end; ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type. Is this indeed a bug (that will be fixed in the future), or is Derby behaving as it was intended? I think this is DERBY-7 http://issues.apache.org/jira/browse/DERBY-7 which was fixed in the 10.1.3 release. Dan.
Re: Please try your applications with 10.2 optimizer changes
Army wrote: Users who are looking to try applications with the 10.2 changes might want to wait until *both* of those changes are checked in before running their tests... Or try now and when the DERBY-781 changes are committed. The earlier the feedback the better chance of it being addressed in 10.2 release. Dan.
Re: Database Procedures Question
[EMAIL PROTECTED] wrote: I've been trying to troubleshoot this problem with the system database producedures that don't work. I upgraded to 10.1.3.1 (no effect). I looked in the databse system tables and I can clearly see that there are entries in the aliases table for the procedures in question and that those aliases belong to the correct schemas. I also checked that the class listed in the aliases table can be loaded by using a Class.forName() call. An yet the procedures are not recognized. Obviously I'm missing something here. What else can I check? You could look through Derby's tests to see how they are called, these procedures are tested so they do work. You can also provide a reproducible case to this list for others to look at. I think the typical user error here is that the number of parameters to the procedure or function call do not match the number expected by the routine. The error message in this case doesn't make it clear that that's the problem. Dan.
Re: Strange performance issue with BLOB's
Piet Blok wrote: There is something else that I was wondering about. You may have noticed the SIZE and CRC column in my blob table together with the UNIQUE constraint. Obviously, I want to prevent duplicate blob's. What happens if I add a UNIQUE constraint on the blob data itself? Would it be accepted? No, BLOBs are not comparable, this follows the SQL standard. Dan.
Re: BigDecimal incompatibility from 1.5 - 1.4
Ray Kiddy wrote: I have been e-mailing with Andrew McIntyre about getting some things done in the build of Derby on Mac OS X. We (at Apple) are seeing something and I am wondering how you all have dealt with this issue. At one time, we thought we could use Java 1.5 and achieve backwards compatibility with 1.4. We have seen an issue with BigDecimal and I wonder if this has impacted you all, or how you have dealt with it. The problem is this. If one used a method in 1.4, java.math.BigDecimal (int), the javac compiler would map this for you, under the covers, to java.math.BigDecimal(double). Just curious how you all are dealing with this, or if you have had to. The Derby code only (hopefully) uses that method when passing a double to it. The automatic promotion of a long or an int to a double caused problems for the SQL type system, where a precise type such as BIGINT would be incorrectly converted to an imprecise value through this method. This would occur when casting a BIGINT to a DECIMAL or the application fetching a BIGINT as a java.math.BigDecimal. If you've found any instances of this call in Derby code, could you enter a Jira issue to fix them to use the BigDecimal.valueOf() call. Dan.
Re: IRC chat summary: is there a way to find out column names in ij?
Knut Anders Hatlen wrote: Jean T. Anderson [EMAIL PROTECTED] writes: A user asked this question on #derby today (and here's a summary of the chat since there are probably others on this list with the same question): Is there a way that I can find out what columns are in a table in ij? like describe tablename or something? You can't do that in ij, but that feature has been logged as DERBY-1164. Well, actually, you *can* do that in ij, but it's not very user friendly. You could write a query against the system tables. To find out what columns are in SYS.SYSCONSTRAINTS, enter this query: select columnnumber, columnname, columndatatype from sys.systables t, sys.syscolumns, sys.sysschemas s where tableid=referenceid and t.schemaid=s.schemaid and schemaname='SYS' and tablename='SYSCONSTRAINTS' order by columnnumber; Or if you just want the column names: select * from MYTABLE where 1 = 0 Dan.
Re: generated by default question
--- [EMAIL PROTECTED] wrote: There is a very simple solution. [solution details snipped] This is the simplest solution, lowest cost solution, and doesn't violate the definition of an SQL sequence. Tthis solution creates a couple different problems that are probably unexpected by the user. 1) An insert with a non-generated value near or at the end of the range of generated values will exhaust the generator much earlier, leading to failed inserts that would succeed under the current scheme. E.g. if the range of of the generator was 1-10 and the CBV was 3, then inserting a non-generated value of 10 means that no more inserts with generated values would work. In the current scheme six more inserts would succed (4-9). 2) An insert with a a non-generated value that is subsequently deleted or updated before any insert with a generated value affects the generator when under the current scheme it would not. This will reduce the range of available values when there is no need to. E.g. if the range of of the generator was 1-10 and the CBV was 3, then inserting a non-generated value of 10 means that no more inserts with generated values would work, even if the row with 10 is deleted before any other inserts occur. In the current scheme seven more inserts would succed (4-10). So solved one scenario and caused (at least) two new ones. Dan.
Re: generated by default question
[EMAIL PROTECTED] wrote: [mjs] Bzzzt! No. Already solved your first example. Just reset the sequence if the row has a value in the identity column between CBV and MAX. Oops! Guess that blows away your theory. I don't have any theory, I was pointing out that the proposed very simple solution had some issues. To become workable it needs some additional changes, potentially like the ones you described, thus the solution becomes more complex. I just didn't want someone to start implementing it and assume it would automatically be accepted as a solution. This is how open source works, people suggest solutions, others see issues with them, others provide additional input, and hopefully a good solution emerges from the community. Dan.
Re: Proposal for 10.2 release schedule
Thomas Dudziak wrote: On 6/22/06, Rick Hillegas [EMAIL PROTECTED] wrote: Last week, Sun Microsystems announced that it will bundle Derby with the next major release of the reference jdk, Java SE 6, also known as Mustang or jdk1.6. If you download the latest Mustang build, you will see that it contains our Derby 10.2.0.3 snapshot in the db directory parallel to lib and bin. Is this Derby or JavaDB ? Sun Microsystems announced that it will bundle Java DB with Mustang. http://biz.yahoo.com/prnews/060621/sfw063.html?.v=66 I imagine that the Derby update cycle will synchronize somewhat with the JDK update cycle, I doubt Derby's release cycle will synchronize with the Sun's JDK release. Sun's JDK is just one of many that distribute Derby technology. http://wiki.apache.org/db-derby/UsesOfDerby Btw, to which files does the COPYRIGHT containing an IBM copyright notice refer to ? A search in the Derby sources did not bring up any source copyrighted by IBM. The original contribution of Derby was from IBM, hence IBM has the copyright on all those original files. The ASF policy is not to have individiual copyright statements in each source file and the new policy (I think) is to remove the copyright statement in each source file, leaving just the reference to the Apache Licence in the source file. Dan.
Re: Proposal for 10.2 release schedule
[EMAIL PROTECTED] wrote: -Original Message- From: Daniel John Debrunner [mailto:[EMAIL PROTECTED] The original contribution of Derby was from IBM, hence IBM has the copyright on all those original files. The ASF policy is not to have individiual copyright statements in each source file and the new policy (I think) is to remove the copyright statement in each source file, leaving just the reference to the Apache Licence in the source file. Dan. [mjs] That would not be a good idea, and I suggest that you have IP attorneys from IBM make a final recommendation on this. (No need for Apache to spend money if IBM has the resources and its in IBM's best interest to do something.) Here's why: legal stuff snipped Michael, you should probably check out the legal-disucss mailing list archives, I'm sure all of this was covered. Dan.
Re: generated by default question
hilz wrote: Well... A good solution would probably be if someone would look at the HSQLDB code and see how they do it because it works like a charm over there! Anyone doing this must adhere to the licence of HSQLDB. Just because projects are open-source does not mean one can copy code between them, each licene must be respected. Dan.
Re: A question regarding RuntimeStatistics()
[EMAIL PROTECTED] wrote: Hi, I am trying to get runtimestatistics for a PreparedStatement on a jdbc connection. Looks like the RUNTIMESTATISTICS() function completely ignores PreparedStatement(s). Here is the code snippet: conn = DBManager.getConnection(); Statement stmt = conn.createStatement(); stmt.executeUpdate(CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)); stmt.executeUpdate(CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)); PreparedStatement pstmt1 = conn.prepareStatement(SELECT user_name FROM users); rs = pstmt1.executeQuery(); while (rs.next()) System.out.println(rs.getString(1)); ADD -- rs.close(); rs = stmt.executeQuery(VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); if (rs.next()) System.out.println(rs.getString(1)); When I executed the query as a regular Statement, it worked fine. Any ideas ? I think you need to close the result set for the select statement. See above. Dan.
Re: Run Procedure Switch from sqlserver to apache derby !!
Myrna van Lunteren wrote: On 4/12/06, Dheeraj Dhiman [EMAIL PROTECTED] wrote: Hello !! Finally, please stop logging jira issues for questions, and please, please stop the annoying exclamation marks. Seeing you log a JIRA issue, and seeig those exclamation marks certainly made me less interested to answer your question, rather than more, so you're achieving the opposite from what I assume you want to. There are some great guidelines for using the apache mailing lists at: http://www.apache.org/dev/contrib-email-tips.html (also linked from http://db.apache.org/derby/derby_mail.html) I especially like the one linked from contrib-email-tips.html How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-questions.html (note this is a general set of guidelines and not specific to Derby. Please don't send derby questions to the authors, send them to the derby-user@db.apache.org list) Thanks, Dan.
Re: Can A Function or procedure return multiple values !!
Dheeraj Dhiman wrote: I m using apache derby for my Project !! In this project we want to make a function which accecpt three long argument i want to return Object[] ( Object Array ) !! Can this is possible in apache derby if yes then pls give example !! No, Derby does not support array types. A procedure can return one or more JDBC ResultSets from server-side JDBC queries. I've added some examples to this page today. http://wiki.apache.org/db-derby/DerbySQLroutines Dan.
