My question: does anyone know if it is possible to disable the need to write
to a temp directory, or some other way to get around this problem?
You might read through the discussion here, as it seems related:
https://issues.apache.org/jira/browse/DERBY-2469
thanks,
bryan
I'm looking for a way of displaying available databases,
ideally something similar to show databases from MySQL.
Derby doesn't maintain a master catalog of databases, so you
have to keep track of the databases yourself. Here's
a few suggestions from the list archive from the last time
this
Run my sample program i receive the error: Invalid cursor state - no
current row.
If comment the line;
ResultSet rsOpzioni =
con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY).executeQuery(sql);
while (rsOpzioni.next()) {
Forwarding from the general@ list to derby-user@ to reach more Derby users.
---BeginMessage---
Hi Brayan,
Could you do in another way? My aim is to delete all the rows where the
OPTION_TEXT is empty..
Regards,
Soumen
On 10/26/07, Bryan Pendleton [EMAIL PROTECTED] wrote:
Soumen Saha wrote
Could you do in another way? My aim is to delete all the rows where the
OPTION_TEXT is empty..
DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',null);
Perhaps:
delete from quiz_options_info where option_text = '' or option_text is null;
thanks,
bryan
The following error message is obtained:
ALTER TABLE T_RES_FILESYSTEM ALTER COLUMN GROUP_ID DEFAULT -1
org.apache.derby.client.am.SqlException: Syntax error: Encountered
DEFAULT at line 1, column 53.
ALTER COLUMN DEFAULT was added in Derby 10.2.2.0. Are you using
Derby 10.2.2.0 or higher?
I've attached a simplified version of the program, one that just does
the repeated startups and shutdowns. The error is shown below. What am
I doing wrong?
Your code seems reasonable to me. What version of Derby are you trying
this with, and what version of Java are you using? Can you try
ALTER TABLE T_RES_FILESYSTEM ALTER COLUMN GROUP_ID DEFAULT -1 ;
It appears that this is not working.
What is the problem that you encounter? Do you get an error message?
thanks,
bryan
Everything I've read so far says that Derby does not implement LIMIT
which lets you write queries that return only so many rows in the resultset.
Is this still the case or has this changed? Or are there initiatives to
implement this?
DERBY-581 and DERBY-2998 are relevant, I believe.
If you
1. Why does int pop = 0 == 0 ? 1 : null; even compile?
For what it's worth, it doesn't compile for me:
C:\bryan\src\java\popjava -version
java version 1.4.2_05
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_05-b04)
Java HotSpot(TM) Client VM (build 1.4.2_05-b04, mixed mode)
The problem was solved by running Derby embedded in the program that
inserted data into the database and only commit every 50th row.
When you were running in the Network Server configuration, were you
also committing your transaction after every 50th row?
thanks,
bryan
database. As the data are published scientific data I can share my source code
and the data. Everything is available on
www.fysik.dtu.dk/~munter/derby/
Thanks for sharing your sample program. It looks like you are creating
statements, but never closing them.
Each call to
while((result.next()) (tableExists = false)) {
Is this possibly a typo? Make sure it says:
while((result.next()) (tableExists == false)) {
You want to do a logical and, not a bitwise and; also you want
to do a comparison, not an assignment.
thanks,
bryan
The difference between boolean1 boolean2 is not bitwise versus
logical; it's between unconditional versus conditional operation.
Thanks Craig! There's always more to learn ...
bryan
I am using eclipse plugin for derby, I apply derby nature in multiple
projects, however when I try to start the derby from the menu I got:
Security manager installed using the Basic server security policy.
2007-10-01 15:16:41.125 GMT Thread[main,5,main]
java.security.AccessControlException:
see that there are four JIRA issues which relate to this particular
error message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804) and which
could possibly be related. I filed a new issue at
https://issues.apache.org/jira/browse/DERBY-3093. Is there any progress
on any of these issues?
Hi
However, in practice, the 'TagValue' may be of different data types (float,
varchar, int, etc). The right data type is known only at runtime.
Here's a couple of ideas:
- Declare the column as a string, and convert the various data types
to and from string format when storing/retrieving
Hi, all. IBM used to make an ODBC driver available for use with Derby.
Is that still happening, or is that no longer supported?
I found this on Google, does it help?
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0409cline2/index.html
thanks,
bryan
UPDATE connecteurs,boitier_connecteurs,boitiers SET
connecteurs.nom='TREO650' WHERE
connecteurs.id=boitier_connecteurs.id_connecteur AND
boitier_connecteurs.id_boitier=boitiers.id AND boitiers.emplacement=1
AND boitier_connecteurs.emplacement = 'C';
Try something like:
update connecteurs set
I do not understand why it only works with small BLOB contents, as soon
as I pass 32750 bytes or so the above happens,
when data is 32750 or smaller it works just fine.
This sounds like a bug to me. The DRDA client/server protocol
tends to divide data at 32K boundaries, so it's possible that
I see that feature T431 etc (grouping, rollup, cube, and grouping sets) are
included in the todo list for derby; Are linear regression and other
statistical functions also on this list?
I'd like to see Derby implement all of the SQL Standard OLAP features, but
that's a large set and will take a
Quick question: Is it true that Apache Derby cannot handle Rollups and
linear regression functions?
If you're referring to Features T611, T612, T431, and T433 of the SQL 2003
standard, some efforts are underway to add that functionality to Derby,
see these pages for more:
Is there a way to delete rows while automatically deleting other rows
dependent on those rows?
There are a number of ways. Here's three:
1) ON DELETE CASCADE
2) Write a trigger for the table which implements the delete logic.
3) Always update the table via a stored procedure, and have that
locks or deadlocks around the runtime. is it any easy way to avoid those
locks.
This is a fairly complex subject. Here is a good place to start:
http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts30291.html
Some of the tools under your control are:
- use smaller transactions
-
Is there a way to restrict external connection to a Derby Database?. I
only want the JVM to be able to connect to the Derby database and no
other external processes.
Use the embedded configuration, then, not the client/server configuration.
In the embedded environment, only the JVM which runs
I am wanting to lock a table.
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj40506.html
thanks,
bryan
I was looking for two functions
One possibility is to write these functions yourself, using
Derby's functionality for writing extension functions in Java:
http://wiki.apache.org/db-derby/DerbySQLroutines#head-a33ab58dd138ca35ee2ada0551ad77d0feee1b79
thanks,
bryan
Error code -1, SQL state XCL47: Use of 'AUTHORIZATION' requires database
to be upgraded from version 10.1 to version 10.2 or later.
The GRANT/REVOKE family of features were introduced in Derby 10.2.
To upgrade your 10.1-version database to 10.2, pass upgrade=true
on your connection URL when
Can somebody help me to get it work?
Perhaps you are using a different transaction isolation level with
Derby than you are with the other databases you're testing, causing
you to experience lock contention in Derby but not in other databases.
As you increase the size of the footprint, you make it harder to embed.
And as you add features, you are increasing the size of the footprint.
True.
Although, I was extremely happy to see that after a year of development,
Derby had hardly changed in size at all:
- 10.2: derby.jar was
always the same. Now I want to identify the files which are still used
In the database folder, each file corresponds to a conglomerate. Each user
table,
system table, and each index on a table is a conglomerate. You can
figure out how conglomerates map to underlying operating system files
by
That's fine if there are no triggers or other objects associated with the
original table. Is there a system procedure or the like for finding such
objects?
Absolutely!
http://db.apache.org/derby/docs/dev/ref/rrefsistabs38369.html
thanks,
bryan
SQL state 42601: ALTER TABLE statement cannot add an IDENTITY column to a table.
Is there any way to work around this?
Perhaps:
1) create a new table with all the columns of the existing table,
plus the new identity column.
2) INSERT INTO new-table SELECT * FROM old-table to copy all the
Failed Statement is: ALTER TABLE logicproperty
ADD CONSTRAINT r_186
FOREIGN KEY
( zone_id )
REFERENCES zone
ON DELETE CASCADE
You can include the foreign key constraints in the
initial table definition itself, instead of creating the
table and then adding the constraints to it later.
Does setting the default value for the other columns kick in that bug?
No, I didn't think so. DERBY-2371 specifically involved a problem when
there were a non-zero number of non-null values in the table at the time
of the ALTER TABLE command.
Also, I tried your complete set of commands against
I tried the 10.3.1.4 release, and my test case if failing before I get
to the other error. It's failing on this call:
tableRS = conn.getMetaData().getTables(getCatalogName(),
getSchemaName(), null, new String[]{TABLE, GLOBAL TEMPORARY, LOCAL
TEMPORARY, ALIAS, SYNONYM});
with this stack trace:
I am trying to create a reusable function that would allow me to send in a
derby query, and get a resultset back.
http://wiki.apache.org/db-derby/DerbySQLroutines
Look for the section titled Returning java.sql.ResultSets from Java procedures
thanks,
bryan
ALTER TABLE defaultValueTest ALTER COLUMN dateA WITH DEFAULT
DATE('2007-08-09')
and getting the error: “An attempt was made to get a data value of type
'long' from a data value of type 'DATE'.”
Hi Nathan,
What version of Derby are you working with?
I just tried your statements on the
Can I conclude that since I got an error message that says it (SQLXML) is
not implemented, that this is truly the case?
I believe that is correct. Army posted on this earlier in this thread:
http://www.nabble.com/forum/ViewPost.jtp?post=11652905framed=y, pointing
to
Can we use static SQL in Derby? Does it support it?
What is static SQL? Can you provide a pointer to a
definition, or examples of static SQL?
thanks,
bryan
With dynamic SQL, database access and authorization are determined at
run time. The user of the application must have all required database
privileges, and the database must determine the best way to access the
required data at run time. However, with static SQL, access and
authorization are
Oh, and one more observation. The IX table lock for the insert thread
mentions LOCKCOUNT=476. I can only infer the meaning of the column (so
this might perfectly normal), but the number of row locks of that
connection is about 150 (it could not exceed 200, the number of
inserts I do per
Can someone point me to some sample code that works that demonstrates these
features?
In the Derby source tree itself, check out XMLTypeAndOpsTest.java,
in the directory java/testing/org/apache/derbyTesting/functionTests/tests/lang
thanks,
bryan
I'm looking in
db-derby-10.2.2.0-src\java\testing\org\apache\derbyTesting\functionTests\tests\lang
and the only file that begins with XML and has a java extension is
xmlBinding.java.
Are you looking at a differnt version?
Yes, I was looking at the trunk. Here's how to have a look at the trunk
Now, I have a pretty simple unit test that results in a deadlock:
- one thread is inserting records in a table (in transactions of N=200
records)
- another thread comes from behind and reads the same records (select
* where id $lastReadID)
After examining the deadlock trace the
I was wondering if it doesn't exist a way to create a backup only of the
existent data, and not the whole database, like a dump.
Would the Import/Export tools be useful, perhaps?
http://db.apache.org/derby/docs/10.2/tools/ctoolsimport16245.html
thanks,
bryan
name (Test) is correct. Is there a way to setup the persistence provider
so that it can insert double quotes around both the connection string
and all query strings?
You'll have better luck asking on the persistence provider's mailing lists.
Some readers of this list are expert in some
I found the following example below and saw the example work at our JUG
meeting. However, I cannot make it work. I get an exception feature not
supported when createSQLXML.
Ensure that you have a recent copy of Apache Xalan in your classpath,
as the XPath code uses Xalan internally.
If you
If I enclose the lowercase table name in quotes then this works.
select * from abc
However, I don't want to enclose the tablename in quotes.
Table and column names which are enclosed in double quotes are
case sensitive.
Table and column names which are not enclosed in double quotes are
case
footprint, and is Java-based. I have been testing Derby to see how it
performs storing large amounts of data in this environment, and I've
seen a slight degradation of performance over time. I'm using Derby
Hi Steve, I think this is very interesting data.
Can you run your experiment out
Hi, I am trying to debug the statement over and over again, and would
like it to be recompiled every time, on the same derby connection. Is
there a property I could set, or some other setting? I tried looking at
the docs but could not find anything quickly.
Assuming you're using IJ, or some
all of these options - except the append - are already set, but there
was no more information about this in the logs:
DERBY-666 describes at least one case in which these properties
don't provide all the information that you might wish.
https://issues.apache.org/jira/browse/DERBY-666
For
Well, as I was installing Eclipse 3.3 I decided to only import the projects
I'm actually working on and leave the other ones in the old workspace.
None of the imported projects has anything to do with Derby, especially none
has the Derby nature.
I just created a new empty workspace, then
Is there currently any plans for multi-version concurrency control for
a future release of Derby? If so what is the rough timeframe?
I haven't heard of anyone working on such a feature, but we're always
interested in getting more people to join the community and participate
in adding more
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.
I am geting syntax errors.
Hi Stephen, I'm afraid I don't know what might be wrong, hopefully
somebody else on the list can help you.
Can you post the exact text of the
The ability to use a WHEN clause in a trigger is logged
as DERBY-534. If you're interested in this issue, you could
vote for it, or (better!) join the development community and
contribute an implementation.
https://issues.apache.org/jira/browse/DERBY-534
I think the other issues you raised
My tests pass when run in Eclipse. However, when I run them in Ant I see
errors like this:
Another instance of Derby may have already booted the database
Only a single JVM at a time can access a Derby embedded database.
However, you can shutdown the Derby embedded system from the
first JVM,
How do I create a schema?
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj31580.html
describes the CREATE SCHEMA statement.
It seems like I should create a schema before I create any databases,
No, it's the other way around. Each database can have one or more
schema. So create the database
ERROR 42ZA0: Statement too complex.
What version of Derby are you using? If it is prior to 10.2,
upgrading to 10.2 may help. As Dan says in
http://mail-archives.apache.org/mod_mbox/db-derby-dev/200612.mbox/[EMAIL
PROTECTED]
Derby's SQL statements are compiled to Java byte code.
The
my project use the derby 10.1 version .some table have 10 records
data ,when add ,delete ,query from the table frequently,the derby will
out of memory ,why the memory can't release auto?
Two suggestions:
1) Upgrade to 10.2.2.0 as soon as you can. It's got
many fixes, including a
I've tried:
'//myns:Name' and that would seem to be correct, but I can find no way of
setting myns to resolve to http://www.myproject.ox.ac.uk/MyProject; in
Did you try including
xmlns:myns=http://www.myproject.ox.ac.uk/MyProject;
in your XQuery expression?
I don't know if it will work,
Hi,
Is there a configuration setting which I can use to
set a limit on the total number of file handles which
Derby will use?
I'm trying to run Derby inside of various application server
environments, many of which have limited resources available
for each application, and I'd like to configure
Thanks for the repro! This is an interesting little situation.
I think that the basic issue here involves result set caching,
which is a feature that went into Derby in 10.2, I believe.
You can read more about that feature here:
http://issues.apache.org/jira/browse/DERBY-822
In your program,
I think that the basic issue here involves result set caching,
Actually now I'm doubting myself, because DERBY-822 was a
*client* change, and this test program is for the *embedded* driver.
So the DERBY-822 comment is irrelevant, sorry about that.
Still, I think that the basic idea is the
Amir Michail wrote:
The column in question has type timestamp yet this doesn't work:
ij alter table tablename alter column columnname default
current_timestamp;
ERROR 22005: An attempt was made to get a data value of type 'long'
from a data value of type 'TIMESTAMP'.
The table does not have
situation. The central question is, why are there any locks held for nothing
selected?
Sometimes, a lock is held for the case where nothing was selected,
to prevent the insertion of a new row that would have satisfied the
criteria, had it been present at the time of the original select.
That
thanks for your answer. But why is there already a lock on the row.
You can find extensive information about Derby's locking behavior here:
http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts30291.html
thanks,
bryan
I am unable to connect successfully from a remote client running 'ij'.
By default, the Network Server will listen to requests only on
the loopback address, which means that it will only accept
connections from the local host. See:
So my question is: is storing only 'T' and leaving nulls any
less expensive than storing both 'T' and 'F' in a lookup table?
I believe the CHAR(1) NOT NULL columns will be simpler and slightly
more efficient for the database to handle. NULL processing is
complex and adds both space and time
which operation and isolation level may impose a lock on tables or rows?
http://db.apache.org/derby/docs/dev/tuning/ctunoptimz19357.html#ctunoptimz19357
http://db.apache.org/derby/docs/dev/devguide/cdevconcepts30291.html
thanks,
bryan
When using JDBC escape syntax, as described in
http://db.apache.org/derby/docs/10.2/ref/rrefjdbc1020262.html
how do I encode ordinary braces so that they are ignored?
That is, if I want to do something like:
{call myProc('arg1', 'arg2')}
but my arg1 value is {myval}, I can't seem to send
but my arg1 value is {myval}, I can't seem to send
{call myProc('{myval}', 'otherval')}
because I get an error about the '{' character in myval.
Duh. I'm sorry, my apologies for the noise. Placing
ordinary brace characters into literal strings works
fine. I had a stray quotation mark in my
Robert, I was a little confused by your original mail. Did
you use the live option when you ran jmap? In my experimentation,
I found that it was quite helpful to use this option, otherwise
jmap would produce lots of data about objects that were in fact
garbage, but hadn't yet been collected.
Basically, the program runs those commands over and over and over
again. At some point the derby thread starts to spin.
Perhaps you are just giving your system more work than it can handle.
It sounds like you have some sort of a benchmark in place, and you
are running it, looking for problems
and still what is DSS chain?
It's an internal behavior of the DRDA protocol. You can
find some high-level information here:
http://wiki.apache.org/db-derby/DssProtocolErrors
To really understand it, you'll need to study the DRDA
spec and read the network server and network client code.
All seems to be going fine but when I shutdown my webapp, the
NetworkServerControl.shutdown() is correctly called but I have db.lck and
dbex.lck left :o(.
I think this may be DERBY-51:
http://issues.apache.org/jira/browse/DERBY-51
Also if I try to restart my webapp, the
shutdown command fails if I try it through a network url (database not
found) or with an embeded url (java.sql.SQLTransientConnectionException:
Database 'ed' shutdown.)
I don't know about the database not found part, but the shutdown command
always throws an exception, so getting Database 'ed'
events, and it appears that the problem is that the bulk load CALL
SYSCS_UTIL.SYSCS_IMPORT_DATA (?,?,?,?,?,?,?,?,?) is not entirely
finishing before I try to query the table it is populating. I would
expect to get a locking exception, or something of that nature, but the
I think I would expect
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
host and the port. I could successfully ping the Apache Webserver
getting port 80, but not the Derby server.
You don't mention a lot about how you started the network server,
but are you using the -h parameter? Note that, as it says on:
sir how i add new table in derby database
and please send me derby gui software or link
You add a new table using the CREATE TABLE statement:
http://db.apache.org/derby/docs/dev/ref/rrefsqlj24513.html
And you can find a number of GUI packages for Derby here:
I was wondering whether there was a good way to calculate the amount of
space that my derby database is currently using.
There is org.apache.derby.diag.SpaceTable.
This should give you some pointers about how to use it:
Initialisation consists in writing objects through jpa. After commit is
done, the program starts and reads back those data.
I haven't heard of any problems involving a delay after commit in Derby
itself. Perhaps jpa is not committing the data when you think it is, or
perhaps your program is
problems when *reading* from the CLOB.. When I call the
ResultSet.getString on the CLOB I get a IOException thrown, when the
String is large... Is this a known problem?
Might be a resource exhaustion problem. Can you print out the
entire exception stack trace, including all chained exceptions,
Caused by: java.sql.SQLException: An IOException was thrown when reading a
'java.sql.String' from an InputStream.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at
New trace :)
Thanks Ruben!
But we still don't seem to be getting down to the actual IOException.
I'm expecting to see a line that says:
java.io.IOException: ...
with the actual information and the actual stack trace of the
IOException itself, but so far all we've seen is the wrapping
Do you have indexes on the important columns of the ASSOCIATION
table? From a quick peek at your query plan, this seems to be
killing you:
Right result set:
Table Scan ResultSet for ASSOCIATION
...
scan information:
Bit set of columns fetched={1, 2}
The store's metadata thinks that there are 20 columns in the table, and
it only currently supports adding a column at the end of the table. The
request is to add the column at position 16, in the middle. From
I notice that the overall algorithm for adding a column to a table
(in
I was running my test harness code to create a new user. I had a
number of poorly coded sql statements causing exceptions, and eventually
i came to a point where the derby database server gave up.
Make sure that when you catch SQLException and continue running,
that you close the various
Inns, Jeff wrote:
passed 1 for each operation, which should have turned them on.
I agree, Jeff; passing 1 should have been the correct thing to do.
This is starting to sound like a bug in the TRUNCATE_END feature to me.
thanks,
bryan
“Inplace” Compression utility, no disk space is recovered and the size
of the “*.dat” files is not reduced.
That is correct. In-place compression re-arranges the records on
the existing pages of the existing file, gathering the existing
records together and shifting all the free space to be
three operations: purge, defrag, and truncate; when truncate is used, it
releases disk space to the operating system
Oops! My mistake. Sorry about that.
Perhaps the original caller was not passing the TRUNCATE_END parameter
in their call to INPLACE compression. My answer described the
provided by derby. Following are the code I am using for clean the
database connection and also PreparedStatement.
if(!(aoConnection ==null || aoConnection.isClosed()))
aoConnection.close();//Release Connection
if(aoPreparedStatement != null)
sometimes i get a Connection reset (i'm also investigating why this
happens but i think i've found a problem with derby) when calling a
remote derby db. the connection is closed but the transaction is still
in progress (see last table at the bottom of the mail).
I think that a Connection
we installed the app serveral times and i think the following lines are per
installation (so per installation it happend on the same page):
org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page
Page(28,Container(0, 1248)), expected=3'455'715'557, on-disk
version=3'357'396'866,
If you're building a Derby application you should just use
the regular JDBC javadoc from your JDK. For example when
you are working with a PreparedStatement you can refer to
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html
thanks,
bryan
Can Derby do this, or does it load the rows regardless?
Yes, Derby can definitely do this. Here's more info:
http://db.apache.org/derby/docs/dev/tuning/ctunperf10679.html
http://db.apache.org/derby/docs/dev/tuning/ctundepth23033.html#ctundepth23033
In the case of the query plan you presented,
I couldn't find an open JIRA entry for enhancing Derby to make
using of opposite-ordered indexes when doing ORDER BY.
I think that DERBY-884 and DERBY-642 are related:
http://issues.apache.org/jira/browse/DERBY-642
http://issues.apache.org/jira/browse/DERBY-884
However, they don't
implicit commit. However, with holdability set to
HOLD_CURSORS_OVER_COMMIT, I no longer get an exception on calling next()
the first time, but rather get good data for 415 calls. On the 416th
call, I get an exception not that the result set is closed, but rather
than an underlying storage
java.sql.SQLException: The heap container with container id
Container(-1, 1173965368428) is closed.
Hi Jeff,
I don't have a lot to offer on this problem, but I did try
your test program and I get the same exception, so the problem
reproduces for me.
What was the behavior that you expected to
401 - 500 of 575 matches
Mail list logo