Re: hello
On 11/01/2015 11:37 PM, בבנייה בבנייה wrote: I trying to leran derby for a project that include it. I want to say that it's very not full , there is much misunderstand for who that not familiar with all issues like class path, start server and more. I think that should at least be a quick and friendly guide for get some order with all the mess. good day,,, Please see http://www.catb.org/esr/faqs/smart-questions.html -- Regards, Dyre
Re: Initializing a new record where field is NULL by default
On 31. jan. 2015, at 20.22, Bob M rgmatth...@orcon.net.nz wrote: Hi Dyre I am still not grasping this. I have psInsert = conn.prepareStatement(INSERT INTO TABLE VALUES(?, ?.., ?) [27 of them] psInsert.setString(1, date); psInsert.setString(25, class); psInsert.setString(26, Trade_ID); but I am unclear what to put for the final line referring to Profit and where I wish to set it to NULL initially which is the default There are two different strategies here: 1) Just insert an SQL NULL manually: psinsert.setNull(number, java.sql.Types.DOUBLE); // See javadoc for PreparedStatement or, psinsert.setNull(“PROFIT_LOSS”, java.sql.Types.DOUBLE) 2) Change the definition of the table: CREATE TABLE T(….., PROFIT_LOSS DOUBLE DEFAULT NULL, …) then when inserting INSERT INTO T(col1, col2, …, col after PROFIT_LOSS, more columns,…) VALUES (?,?,… ?) You have to spell out the columns you are inserting into, unless the columns with default values are the last columns in the table. In that case you can just omit them in the insert Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Initializing-a-new-record-where-field-is-NULL-by-default-tp143732p143735.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Initializing a new record where field is NULL by default
On 02/01/2015 05:42 PM, Bryan Pendleton wrote: You have to spell out the columns you are inserting into, unless the columns with default values are the last columns in the table. In that case you can just omit them in the insert I'm pretty sure it's a bit more flexible than that. I believe that if you don't say NOT NULL, and you don't have a DEFAULT clause, then DEFAULT NULL is automatic. And I believe you can name the columns in any order (so long as they line up with the VALUES values), so they don't have to be the last columns in the table. So you can INSERT INTO T (c, f, a, g, b ) VALUES ( .. ) and columns d and e and h will get NULL values since they weren't mentioned in the insert. That is indeed much more convenient. Thanks for pointing that out :) -- Regards, Dyre
Re: Inserting and deleting records
On 31. jan. 2015, at 13.50, Bob M rgmatth...@orcon.net.nz wrote: Hi Dyre Thank you for your explanation I understand completely My concept of requiring the table to look exactly as I think it should look before any ORDER BY is WRONG!!! Well, its an intuitive way to think about it. That is why some dbs have ROWID. It is easy to think about a table as a vector, but it is really more like a hashtable, where there is no implied order and you need to a key to access the record you’re interested in. I do, in fact, retrieve x records using ORDER BY exactly as you say to always get the x latest records. Now one last question, if I may. After adding a new record, I should like to set the pointer to the penultimate latest record ordered by DATE and Time so that I can update the Profit field Exactly what code do I need to do that, please I will not say that it is impossible, but probably hard. As you probably know, there isn’t really a concept of pointer - the closest thing is a SCROLLABLE UPDATABLE result set. Unfortunately, I don’t think Derby supports ORDER BY with scrollable result sets (I may be wrong, check the docs), so that probably won’t help much… But perhaps there is a simpler way to do what you’re attempting? So you insert a record for a trade, and then after a while (when the trade has gone through?) you calculate the loss/profit and update the record? Assuming this is the situation, sort of, here is what I would consider: 1) Leave a “not calculated yet” value (NULL comes to mind) in the profit column. Then issue an UPDATE … WHERE PROFIT IS NULL. No need to know where the record is, as long as you update the right one… will obviously only work as long as there is only one record with NULL profit at a time... 2) Maybe there is an ID/TRADE NUMBER here? Presumably you could track (in your app) the ID of the trade(s) which has/have not been finalized? Then you could do someting like UPDATE … WHERE ID = last trade id 3) A separate table for the unfinished trades. Update when you know the profit, and copy to the real table… HTH, Dyre
Re: Inserting and deleting records
On 31. jan. 2015, at 14.59, Bob M rgmatth...@orcon.net.nz wrote: Thank you Dyre for a brilliant reply.. I do have a TRADE_ID number Also, I only ever have ONE open trade at any point in time I follow your three points completely Because I already have code which retrieves x records ordered by date and time I am considering the following code to answer my own question * // retrieve and update date and time of penultimate record // I retrieve the latest 2 records rs = s.executeQuery(SELECT * FROM TABLE ORDER BY Date ASC, + Time ASC OFFSET x ROWS FETCH NEXT 2 ROWS ONLY); myConsole.getOut().println(Successfully retrieved latest 2 records from TABLE: ); rs.next(); String Date3 = rs.getString(Date); int Time3 = rs.getInt(Time); s.setCursorName(MYCURSOR); rs = s.executeQuery(SELECT * from TABLE WHERE Date = ' + Date3 + ' AND Time = + Time3 + FOR UPDATE); rs.next(); conn.prepareStatement(UPDATE FROM TABLE SET PROFIT_LOSS = profit WHERE CURRENT OF MYCURSOR).executeUpdate(); myConsole.getOut().println(Updated penultimate record); *** Clever! I have not used scrollable updatable results much, but I believe you don’t need the last update statement - you should be able to issue the rs.setInt() (or what is appropriate) to update the proft direcly. The only sticking point is that I don’t know if you’re guaranteed that the two records are in a particular order (since there cannot be an ORDER BY). You may have to check the which of the two records you’re at and possibly move… Best of luck! Dyre Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Inserting-and-deleting-records-tp143723p143729.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Inserting and deleting records
See setNull http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setNull(int,%20int) in the jdbc javadoc On 31. jan. 2015, at 18.58, Bob M rgmatth...@orcon.net.nz wrote: Thanks Dyre If I choose to follow your No. 2 point to consider I set up the profit field with DEFAULT NULL There will only be one record at any point of time with the default value When I come to inserting a new record, do I need a line of code saying psInsert.setDouble(27, ??); and if so, what do I put in ?? to get the default value there? Bob M p.s. 27 is the field number for profit -- View this message in context: http://apache-database.10148.n7.nabble.com/Inserting-and-deleting-records-tp143723p143731.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Initializing a new record where field is NULL by default
On 31. jan. 2015, at 19.23, Bob M rgmatth...@orcon.net.nz wrote: Hi When creating a new record, what does one need to code to initialize a field which you wish to have the default value of NULL? See columnDefinition http://db.apache.org/derby/docs/10.11/ref/rrefsqlj30540.html in the manual. Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Initializing-a-new-record-where-field-is-NULL-by-default-tp143732.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Inserting and deleting records
On 31. jan. 2015, at 08.27, Bob M rgmatth...@orcon.net.nz wrote: Hi I have a section of code which I hope does the following:- 1) inserts one new record with the latest (date + time) 2) deletes one old record with the oldest (date + time) When I run the code for example, I get 194 new records written where I expect them to be at the end of the table BUT 10 new records are written at the beginning of the table(out of date+time) sequence So, what do you mean by “written where I expect them to be”? In SQL there is no concept of “where” something is written. There is no “natural” order in which records will be returned when you SELECT them. If you want to ensure a particular order you need to add an ORDER BY clause. In your case you could order by your DATE/TIME column, but keep in mind that this sorts according to what is entered. So if you enter the wrong date it may end up at the beginning, even if it was the last thing you inserted… You can sort according to the order in which rows where added to table if you add an GENERATED ALWAYS AS IDENTITY column to your table and ORDER BY this column. Read the docs carefully before using this as the results are not always intuitive (e.g. there can be gaps in sequence). Other database systems (among them ORACLE) adds such a column to all tables automatically, and it is often called ROWID. There you can always do ORDER BY ROWID to get this behavior. But this is not mandated by the SQL standard and Derby does not to have it. What should I do to rectify this situation so that all 204 new records appear at the end of the table ? The code I have is as follows:- *** psInsert = conn.prepareStatement(INSERT INTO TABLE VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)); statements.add(psInsert); 27 - psInsert.xxx lines psInsert.executeUpdate(); // retrieve oldest record from the table rs = s.executeQuery(SELECT * FROM TABLE ORDER BY Date ASC, + Time ASC FETCH FIRST ROW ONLY); rs.next(); String Date2 = rs.getString(Date); int Time2 = rs.getInt(Time); // and now delete this record. s.setCursorName(MYCURSOR); rs = s.executeQuery(SELECT * from TABLE WHERE Date = ' + Date2 + ' AND Time = + Time2 + FOR UPDATE); rs.next(); conn.prepareStatement(DELETE FROM TABLE WHERE CURRENT OF MYCURSOR).executeUpdate(); // commit the above transactions conn.commit(); } // end of adding new record and deleting oldest one Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Inserting-and-deleting-records-tp143723.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Duplicate key feature request
On 29. nov. 2014, at 19.02, John English john.fore...@gmail.com wrote:
On 29/11/2014 14:50, Dyre Tjeldvoll wrote:
I think the SQL-standard way of doing things like this is with the MERGE
statement http://db.apache.org/derby/docs/10.11/ref/rrefsqljmerge.html
(just
added to Derby in the latest release). Though the syntax may be a bit more
cumbersome…
A *lot* more cumbersome, if I understand it correctly!
Arguably yes. But the MERGE statement aims to cover a number of non-std
extensions like this INSERT OR IGNORE UPDATE OR INSERT etc. With the merge
statement you can also do all your updates in a “shadow” table and then merge
it with the real table later.
Anyway here is my attempt at formulating INSERT ON DUPLICATE KEY UPDATE using
MERGE (untested, no warranty):
MERGE INTO T AS DST USING SYSIBM.SYSDUMMY1 ON DST.keycolumn = ?
WHEN MATCHED THEN UPDATE DST SET DST.col = ? …
WHEN NOT MATCHED THEN INSERT INTO DST VALUES(?,?,…,?)
But perhaps you could convince me otherwise by posting a snippet to show me
how to do it?
Basically, I'd just like to be able to do this:
try (Transaction t = new Transaction(connection)) {
//... insert new row
t.commit();
}
catch (SQLDuplicateKeyException e) {
// ... update existing row
t.commit();
}
... and let any other exceptions propagate. As it is, having to use an if
to distinguish between different exceptions is very ugly and non-O-O.
--
John English
Re: Duplicate key feature request
I think the SQL-standard way of doing things like this is with the MERGE
statement http://db.apache.org/derby/docs/10.11/ref/rrefsqljmerge.html (just
added to Derby in the latest release). Though the syntax may be a bit more
cumbersome…
In your case you don’t have a source table, so you may have to use
SYSIBM.SYSDUMMY1 for this. Note that the mergeWhen and mergeWhenNotMatched
clauses do not need use the src table…
On 29. nov. 2014, at 13.25, John English john.fore...@gmail.com wrote:
Something that I find crops up quite often is code to deal with duplicate
keys. I often want to insert into a table, or update if the key already
exists. In MySQL I can just use INSERT ... ON DUPLICATE KEY UPDATE ... for
this, but with Derby I end up with code that looks like this:
try {
//... insert new row
}
catch (SQLException e) {
if (e.getSQLState().equals(DUPLICATE_KEY)) {
// ... update existing row
}
else {
throw e;
}
}
In the absence of something like INSERT ... ON DUPLICATE KEY UPDATE, would it
not perhaps be a good idea for Derby to subclass SQLException so that it
could throw a (say) SQLKeyExistsException to avoid ugly repetitive code like
the above? Or is there already something that I've overlooked that addresses
this problem?
TIA,
--
John English
Re: Derby Error message - explanation required
So, I have never heard about Dukascopy's Forex Platform before… what is that? Based on the callstack you provided it looks like none of Derby’s heuristics for locating a tmp directory works on this platform. Arguably it is a bug that you get an NPE, but the alternative would have been something like “Unable to locate tmp dir”, I think. On 17. sep. 2014, at 19:39, Bob M rgmatth...@orcon.net.nz wrote: Hi Rick derby.log http://apache-database.10148.n7.nabble.com/file/n142209/derby.log attempting to upload derby.log Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/Derby-Error-message-explanation-required-tp142183p142209.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: CLOB data errors after moving cursor around
To me this looks like a bug. Superficially, it looks like your call to
last() has closed the rs on the server side. LOBs make things more
complicated since nested queries with locators are involved.
On 09/12/2014 09:26 PM, bfabec wrote:
On a related note, when a result set contains a CLOB, when doing a
rs.last()/rs.first(), and then calling rs.next(), we get a Container has
been closed. SQL exception.
Sample class:
--
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class testZ {
private static String dbURL =
jdbc:derby://9.42.11.34:1088/TestDB;create=true;user=test;password=derbypass;
public static void main(String[] args) {
try {
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Connection conn = DriverManager.getConnection(dbURL);
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
//GROUPASC is column with CLOBS
ResultSet rs = stmt.executeQuery(select GROUPASC from
RE_EVENTGROUPASC
where EVENTID=5 OR EVENTID=6);
//ResultSet rs = stmt.executeQuery(select EVENTID from
RELATEDEVENTS.RE_EVENTGROUPASC where EVENTID=29419 OR EVENTID=29420);
rs.last();
rs.first();
if (!rs.next()) {
//exception here
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
--
Note there are two queries (one is commented out). The GROUPASC is the
column with the CLOB data type. When I call the second one (without the CLOB
column), it works fine. Only happens with CLOBs!
java.sql.SQLTransactionRollbackException: Container has been closed.
at
org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.ClientResultSet.next(Unknown Source)
at testZ.main(testZ.java:23)
Caused by: ERROR 40XD0: Container has been closed.
at org.apache.derby.client.am.ClientResultSet.completeSqlca(Unknown
Source)
at org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown
Source)
at
org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown
Source)
at
org.apache.derby.client.net.NetResultSetReply.readScrollableFetch(Unknown
Source)
at
org.apache.derby.client.net.ResultSetReply.readScrollableFetch(Unknown
Source)
at org.apache.derby.client.net.NetResultSet.readScrollableFetch_(Unknown
Source)
at org.apache.derby.client.am.ClientResultSet.flowGetRowset(Unknown
Source)
at org.apache.derby.client.am.ClientResultSet.getNextRowset(Unknown
Source)
at org.apache.derby.client.am.ClientResultSet.nextX(Unknown Source)
... 2 more
Are these bugs or something I am misunderstanding about CLOBs and Derby? I
don't hit these in other relational databases like DB2. Thanks again!
--
View this message in context:
http://apache-database.10148.n7.nabble.com/CLOB-data-errors-after-moving-cursor-around-tp142101p142103.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
--
Regards,
Dyre
Re: Problem with Select statement
On 09/09/2014 10:20 AM, Kessler, Joerg wrote: Hi, I want to execute select statement on a table using a Java program and JDBC. The statement is actually not very difficult: SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND (MSGSTATE IS NULL OR MSGSTATE = 'A') What does the Java String value passed to prepareStatement() look like? Sounds like your single-quotes around A have been eaten somehow... When this statement is executed by a test I receive errors like Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A' is not a column in the target table. When I change the statement to SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE WHERE SEQ_ID = ? AND MSGSTATE IS NULL there is no problem. Also when I execute the above statement via Eclipse Database Development/SQL Scrapbook using a fix SEQ_ID the statement is executed without error. What am I doing wrong? Best Regards, Jörg -- Regards, Dyre
Re: Possible bug: duplicate value order with non-unique indexes
On 08/19/2014 08:43 PM, Jim Gray wrote: I have a problem which I think is a bug, please let me know if you agree. I don't, as there are NO guarantees about the order in which rows will be returned, unless you add an ORDER BY clause.* The existence of indices or comment overrides does not change that. If I understand your problem correctly you need to change your select to SELECT * FROM t1 ORDER BY c2,c3 Then the returned rows will be returned in c2 order, with duplicates sorted according to c3 * This is very counter-intuitive as one would assume that the order in which rows were added to the table was implicitly recorded somewhere. But it is not. HTH Also, has anyone else has encountered it and developed a workaround? The problem deals with the ordering of duplicate values within a non-unique index. I have an application that depends upon duplicates occurring in creation order, but, it appears that the order is different for INSERT versus UPDATE. In my test case below, INSERT always puts the latest duplicate value last. However, when an UPDATE creates a new duplicate value, it is placed first in index order. The data is randomized with unique values for the first column, and duplicate values in the second column. The third column has the encounter order of the duplicate value. create table t1(c1 int, c2 int, c3 int); create unique index t1_i1 on t1(c1); create index t1_i2 on t1(c2); insert into t1 values(7, 3, 1); insert into t1 values(96, 2, 1); insert into t1 values(44, 5, 1); insert into t1 values(95, 2, 2); insert into t1 values(68, 1, 1); insert into t1 values(65, 3, 2); insert into t1 values(62, 1, 2); insert into t1 values(84, 3, 3); insert into t1 values(55, 5, 2); insert into t1 values(5, 3, 4); insert into t1 values(25, 5, 3); insert into t1 values(21, 7, 1); insert into t1 values(45, 9, 1); insert into t1 values(43, 0, 1); insert into t1 values(71, 4, 1); insert into t1 values(33, 4, 2); insert into t1 values(24, 1, 3); insert into t1 values(91, 0, 2); insert into t1 values(60, 1, 4); insert into t1 values(39, 3, 5); select * from t1 -- DERBY-PROPERTIES index = t1_i2 ; C1 |C2 |C3 --- 43 |0 |1 91 |0 |2 68 |1 |1 62 |1 |2 24 |1 |3 60 |1 |4 96 |2 |1 95 |2 |2 7 |3 |1 65 |3 |2 84 |3 |3 5 |3 |4 39 |3 |5 71 |4 |1 33 |4 |2 44 |5 |1 55 |5 |2 25 |5 |3 21 |7 |1 45 |9 |1 update t1 set c2 = 5, c3 = 0 where c1 = 7; select * from t1 -- DERBY-PROPERTIES index = t1_i2 ; C1 |C2 |C3 --- 43 |0 |1 91 |0 |2 68 |1 |1 62 |1 |2 24 |1 |3 60 |1 |4 96 |2 |1 95 |2 |2 65 |3 |2 84 |3 |3 5 |3 |4 39 |3 |5 71 |4 |1 33 |4 |2 *7 |5 |0- First in duplicate order after update* 44 |5 |1 55 |5 |2 25 |5 |3 21 |7 |1 45 |9 |1 delete from t1 where c1 = 7; insert into t1 values(7,5,0); select * from t1 -- DERBY-PROPERTIES index = t1_i2 ; C1 |C2 |C3 --- 43 |0 |1 91 |0 |2 68 |1 |1 62 |1 |2 24 |1 |3 60 |1 |4 96 |2 |1 95 |2 |2 65 |3 |2 84 |3 |3 5 |3 |4 39 |3 |5 71 |4 |1 33 |4 |2 44 |5 |1 55 |5 |2 25 |5 |3 *7 |5 |0 - Last in duplicate order after insert* 21 |7 |1 45 |9 |1 -- Regards, Dyre
Re: You cannot invoke other java.sql.Clob/java.sql.Blob methods
It would be much easier to help you if you could provide the actual stack trace
you are seeing.
More comments inline.
On 26. juni 2014, at 08:08, china_wang 2596121...@qq.com wrote:
Dear All,
I have a problem that i can't get Clob when i use
spring3.0.5+ibatis2.3.0+derby10.10.0.1 ,
my code snippets :
select id=PUB-DATASETS resultClass=java.util.HashMap
parameterClass=map remapResults=true
select $columns$ from pub_datasets where 1=1
dynamic prepend=and
isNotEmpty property=dataSoursIds
DATASOURCE_ID in ($dataSoursIds$)
/isNotEmpty
/dynamic
dynamic prepend=and
isNotEmpty property=dataSetIds
DATASET_ID in ($dataSetIds$)
/isNotEmpty
/dynamic
/select
ArrayListMaplt;String, Object list =new
ArrayListMaplt;String,Object();
This looks unnecessary. The ArrayList created here goes out of scope in the
next line.
list =(ArrayList)
datasetDAO.findAll(MS-PUB-DATASETS-SELECT-ALL-BY-ADMIN,params);
If this does not throw, I assume that the database access was ok…
for(MapString, Object map:list)
{
if(map.get(TEMPLATE)!=null)
{
System.out.println(((Clob)map.get(TEMPLATE)).length());
}
}
((Clob)map.get(TEMPLATE)).length() will throw exception….
Right, but there could be many reasons. Which exception did you see?
Regards,
Dyre
Re: Unique constraints and nulls
On 05/17/2014 01:16 PM, John English wrote: On 15/05/2014 10:46, Dyre Tjeldvoll wrote: Could you not do DELETE FROM FOO WHERE A = ? AND ( B = ? OR B IS NULL ) Unfortunately not. If B is not null, it would also delete the corresponding A where B is null. For example: A = X, B = Y A = X, B = NULL Your solution would delete both, but I want to delete ONLY the row with the matching value for B. I think you then can do DELETE FROM WITH_NULLS WHERE A = ? AND CASE WHEN ? IS NULL THEN B IS NULL ELSE B = ? END You then have to set the B value twice on your prepared statement, but I think it will work. It will not work in IJ though, as you cannot supply NULL in the VALUES clause when executing the statement, which looks like a bug to me... -- Regards, Dyre
Re: Unique constraints and nulls
On 05/14/2014 04:35 PM, John English wrote: On 14/05/2014 16:52, John English wrote: Is there an easy way to constrain the values of A to be unique even when B is null? (I could try to change things so that empty strings are used instead of nulls, but that would involve changing existing code and it will take quite a bit of work to ensure that there aren't any unexpected knock-on effects, so I prefer to stick with nulls if I can.) Further investigation reveals that B is nullable for a reason: it's an optional value which is a foreign key if present. What I'm now trying to do is delete before inserting, using a prepared statement inside a transaction: delete from Foo where A=? and B=? However, when the value for B is null, nothing gets deleted (presumably due to the query containing B=NULL rather than B IS NULL). Can anyone suggest a way to fix this? Could you not do DELETE FROM FOO WHERE A = ? AND ( B = ? OR B IS NULL ) ? -- Regards, Dyre
Re: Best way to have a DB browser in your destop app
On 18. apr. 2014, at 09:08, Chux chu...@gmail.com wrote: Hello guys, I have a desktop app using Java FX and so I have Derby as embedded DB. These builds I deploy to my clients. I need however a little tool to somehow access the embedded database for viewing, and maybe some on-the-fly modifications. When the FX app is up and running the DB is locked to it. So my option is to include a built in DB manager tool inside the app. So I was wondering if you know any java-based database viewers that I can import and use inside my app? Nope. But if you don’t need a fancy interface for your maintenance work you could embed a NetworkServer in your app and then use ij to connect with the client driver. This is as simple as setting a property when starting your app… don’t remember the name of the property, but it is in the docs… Regards, Dyre
Re: When to shut down a database
There are a number of issues that for the most part are distinct 1) Commit a transaction when you're sure you don't have to abort (or when you need to release resources). 2) Close the connection when you no longer need it/it is about to go out of scope. 3) Shutdown a database to a) speed up the next boot (as the db does not have to recover from its log) b) to release the database (files) for another jvm 4) Shutdown the Derby engine to release resources to the rest of your application when you don't need access to any databases. On 04/07/2014 03:49 AM, Chux wrote: Hello George, Thanks for your comment. This situation however is, I'm using java DB as an embedded mode. Best, Chux On Mon, Apr 7, 2014 at 3:02 AM, George Toma toma.georg...@yahoo.com mailto:toma.georg...@yahoo.com wrote: Hi Chux, In my opinion the example from app. referred at commit the transaction OR close the connection ( a connection could be transacted too ), and not to shutdown the db. If the business rule specifies that the db. needs to be shutdown when the app. is shutdown, then so be it. Normally the db is not shutdown, not even when the app is down. Cheers, George On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com mailto:chu...@gmail.com wrote: Hello guys, I read in a sample app that you've got to shutdown a database. I was just confused if you need to shut it down on every connection transaction or just shut it down on application close, in my case a desktop applicaiton. Best, Chux -- Regards, Dyre
Re: Derby replication system - Need help
On 03/23/2014 10:22 PM, spykee wrote: Hi, From my application tables ( for each table I have a trigger for INSERT, UPDATE and DELETE operation), I will need two important information: a) the columns names which changed when an update SQL occured. b) the columns values that changed during an UPDATE SQL script. The points a) + b) will be used to create a message to be sent on the QUEUE. But with Derby I encountered few issues . 1. Is not possible to fetch only the updated columns from an UPDATE trigger. True. There is no diff-interface as such. You have to iterate across all columns and compare manually, I think. But you can specify a correlation name for both the new and old row value(*) (e.g. NEW AS NEW OLD AS OLD) so that you don't have query the table for the old value (OLD AS OLD is obviously not available in insert triggers, and similarly NEW AS NEW is not available in delete triggers) (*) Assuming that we are talking about row-level triggers. 2. There is no way of fetching the columns name from a trigger(That's, I want to know from an UPDATE trigger the updated columns names and to use this information on building the message for my replication system) Actually, there is no way to pass the whole row (NEW or OLD) to a stored procedure or function, so you have to create separate procedures for the tables you create triggers on which take all the columns you are interested in as parameters. E.g. your trigger body becomes something like ... FOR EACH ROW CALL MYPROC(OLD.x, NEW.x, OLD.y, NEW.y,...) I thought I can avoid using a lot of Java code for these tasks. Using Java code for searching(filtering) only the updated columns names from a specific table, and their values will cause me a delay on my replication system, a drawback. First a trigger will fire, then a Java code ( stored procedure ) will be called ( the Java code will go back and query the db - I don't like this, why I can't solve this problem using Derby functionality, triggers ? ), a comparison will be made on the last 2 rows from the audit table, pick only the different values + the columns names, create a specific message with this information. There is no other ways of avoiding using Java code for these tasks ? Please advice me. The java code in the sp is runs in the same jvm and is JITed just like any other code, so it is just as fast as the Derby code which executes a normal trigger action. Assuming that the trigger firing has brought the relevant data into the page cache it should be reasonably fast to query the original table from within the sp also, at least if you use an index. Another way is to have the trigger just dump the old and new values in a table. Then the trigger would simply do something like ... FOR EACH ROW INSERT INTO QUEUE_T VALUES (NEW.x, OLD.x, NEW.y, NEW.y,...) you determine the order in which you insert the NEW and OLD values. Then you could do all the logic and analysis in the sender thread which monitors QUEUE_T. You could add an insert trigger to the QUEUE_T which would call a store procedure which would wake up the sender. -- Regards, Dyre
Re: Derby replication system - Need help
On 03/18/2014 09:58 PM, spykee wrote: I agree, starting 20K threads/sec does not sound like I good idea. But since I'm not familiar with these other technologies that you use, I don't understand why you need to fire a thread per message. Why can't you have a dedicated sender thread that reads everything in your queue-table and sends it (through whatever api you have to the MQ system). You could either have the sender thread check the queue table periodically, or have it sleep on a monitor which the trigger action grabs when adding new records to the queue table. First you answered to this question yourself( previous response). For each call of the stored procedure a new thread will be created to execute the stored procedure. I'm sorry but I don't understand this sentence. Why do you say that a call to a stored procedure spawns a new thread?. Derby most certainly does not spawn a new thread when executing a stored procedure. You could, of course add code to your stored procedure which spawns a new thread, but why would you? If the SP takes a long time to run you're only blocking a single connection, not the entire database. Anyway, looks like you have some ideas. Good luck :) At this I referred, and I want to avoid creating one thread per message even if the messages are produced at different times ( e.g. message_1 at t1, and message_2 at t2, t1 t2). Second, I read that pooling from a table ( perform a periodically select c1,c2 from table_T where to see if something changed) is not an optimal choice, but I like the idea with the monitor ( same thing here, for each notification from the db Hey a new row was inserted, wake up and take the message, I will execute a stored procedure - one thread per notification, but since this is just a notifier, less bytes, the network load is lower, but I will have to make a new call from my monitor thread to the database.) Using the monitor approach I will have to make *2 steps:* - throw a notifier message from the db to the monitor thread ( execute a stored procedure) - the monitor thread will then have a logic, and then query the table for the changes and publish them to the queue - this approach will ALLOW me batching the messages ( this should improve the performance)!! With the first approach I have to make *1 step:* - throw a message directly to the queue - this approach will NOT allow me batching the messages( this should decrease the performance) Why can't everything from the Derby trigger to the message queue be inside the same jvm? Everything will run on the same JVM, or different JVM ( this is why I will use a JMS queue). For the moment there will be same JVM. I will implement both solutions : - monitor solution - my first solution - compare how many messages are sent per second/minute for each solution, and use the approach which is the best. Kind regards, George -- View this message in context: http://apache-database.10148.n7.nabble.com/Re-Derby-replication-system-Need-help-tp138003p138081.html Sent from the Apache Derby Users mailing list archive at Nabble.com. -- Regards, Dyre
Re: Derby replication system - Need help
On 03/17/2014 08:48 PM, spykee wrote: Hi Dyre , Thank you for your comments. The JMS Queue ( Topic ) has an API, and it will be called from the stored procedure. The scope of the queue is to be an entry point of the messages publishes by the stored procedures, messages which will go to a cache(s). The architecture is simple: --- db_tables---triggers for INSERT/UPDATE/DELETE put data to the audit tables, populate tReplicationMessage table---trigger the INSERT into tReplicationMessage ( one thread/message )---CALL the stored procedure, send the message in the queue publish the messages to all readers ( cache(s))!. Now, I want to avoid creating one thread/message when sending it from the db -queue. AND beside this, I thought that there may be other solutions than the one I mentioned ( tReplicationMessage ). Firing one thread per message is bad in my opinion, and this should load the db + network. I agree, starting 20K threads/sec does not sound like I good idea. But since I'm not familiar with these other technologies that you use, I don't understand why you need to fire a thread per message. Why can't you have a dedicated sender thread that reads everything in your queue-table and sends it (through whatever api you have to the MQ system). You could either have the sender thread check the queue table periodically, or have it sleep on a monitor which the trigger action grabs when adding new records to the queue table. For queue I use OpenMQ, and by itself will publish the messages received to all subscribers ( no need to do anything here). My problem is not the queue, and it is the way of how the messages arrive from db tables to the queue. From the queue to the cache is purely Java + message format, which is google protocol buffers. Why can't everything from the Derby trigger to the message queue be inside the same jvm? Any other tips other than using a single table which will fire one thread/message (the stored procedure triggered by the INSERT trigger from tReplication table) ? Regards, George -- View this message in context: http://apache-database.10148.n7.nabble.com/Re-Derby-replication-system-Need-help-tp138003p138039.html Sent from the Apache Derby Users mailing list archive at Nabble.com. -- Regards, Dyre
Re: Turning on SQL authorization results in loss of table's ownership and permissions
Hi guys, some comments inline: On 03/14/2014 01:33 PM, Rick Hillegas wrote: Hi Wojciech, Some comments inline... On 3/13/14 12:50 PM, Wojciech Barej wrote: Hello Rick, Thank you for replying to my issue. It drives me crazy. I have done what you asked of me. Results: 1) SCHEMANAMEAUTHORIZATIONID APPAPP NULLIDAPP SAAPP SQLJAPP SYSAPP SYSCATAPP SYSCS_DIAGAPP SYSCS_UTILAPP SYSFUNAPP SYSIBMAPP SYSPROCAPP SYSSTATAPP I can clearly see that the authorizationid is incorrect for my schema 'SA'. Why? This tells us that the database was created in one of two ways. Either i) user was explicitly set to app ii) or no value was supplied for the user attribute and the default (app) was taken by Derby 2) I have successfully run your script from within ij with the results as follows: SCHEMANAMEAUTHORIZATIONID APPAPP NULLIDSA SASA SQLJSA SYSSA SYSCATSA SYSCS_DIAGSA SYSCS_UTILSA SYSFUNSA SYSIBMSA SYSPROCSA SYSSTATSA Here I can see a proper authorizationid values. Also the SQL authorization worked properly here. The owner retained its ownership and I could use SELECT statement. Everything worked as supposed to. What is interesting here is that when I create a new database from within NetBeans 7.3 and run a check: select schemaName, authorizationID from sys.sysschemas order by schemaName; I always have APP as an authorizationid value for every schema even one created by me. I checked your script and the only difference I can spot at the moment is that you didn't supply password when creating the database for user 'sa' whereas I do specify it in NetBeans. I also think I run a check before from ij as well and I executed the statement like this: connect 'jdbc:derby:memory:db;create=true;user=sa;password=sa'; and I also had problems with the SQL authorization. Is it possible that supplying password during the database creation before the authentication (derby.connection.requireAuthentication) is switched on makes Derby assigning incorrect authorization ids? I added a password to the creation url. That does not affect the results. The database is still owned by sa. My suspicion is that the database is not being created when you think it is. Since this only happens under Netbeans, my guess would be that Netbeans is proactively creating the database without supplying a user name. If I were tackling this problem, I would look for a way to tell Netbeans not to do that. Maybe we will get some comments from someone who understands Netbeans better than I do. I tried creating a database in Netbeans. What I see is that 1) It comes with a default database (sample) with authid APP 2) When trying to create a database it defaults to the network driver 3) You can expand the Drivers node and left-click Java DB (Embedded), and select Connect using. Then you get a wizard where you enter username and password, and there is a button for setting properties. You then have to add connect=true to the set of properties to create the database. When pressing Next you are prompted for the schema to use. But the schema corresponding to the new username you chose is not yet available - presumably because it is created on demand, and APP is the default instead. If you then choose a different schema, like SYSIBM, and run a create table command using that connection, you can expand the Other schemas node and see a schema with same name as the user you connected as. If you now create a new connection (by left-clicking on the Java DB (Embedded Driver) node), and repeat the steps above (except that the create property is no longer needed) you now get the schema corresponding to the user name as the default. Moreover, by expanding the node this schema you can see the table you created using the first connection, and there is no new table in SYSIBM. I suspect that the Netbeans wizard was written for a number of different databases and drivers, many of which probably do this differently. As a result there is a certain impedance mismatch between the wizard interface and what actually happens inside Derby. snip -- Regards, Dyre
Re: Adding a new record to a table
On 03/16/2014 06:54 PM, Bob M wrote: On a regular basis I add a new record and delete the oldest record in my table The records have a key and up until recently were listed by date/time order as I wish to have them I understand that this is unnecessary but it is great to always find the latest records at the end of the table Now, after a tweek in the code, my newest records are being written at the beginning of the table next to the oldest records ? What code do I need to add, so that when any new record is added, it will appear at the end of the table and not at the beginning ? At the risk of sounding somewhat pedantic: A database table is NOT an array on disk. You do NOT control where (physically) your records end up. The order in which you SEE records is determined by the ORDER BY clause you use for your query. If you don't specify an ORDER BY clause it may appear that the order is predictable and that it corresponds to the chronological order in which records were inserted, but this is merely a coincidence, and cannot be relied upon. Derby does not, unlike some other databases, automatically add a rowid column to your table that is always increased when adding records to the table. You can create such a column manually - see https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/ref/index.html -- Regards, Dyre
Re: Derby replication system - Need help
Hi George, this is a huge topic, but I'll try to answer some of your questions inline: On 03/16/2014 04:19 PM, George Toma wrote: Hi all, I'm trying to achieve the following functionality and I would like an opinion, advice from more experienced people: - the goal is to provide a replication system which will publish messages to a JMS queue ( OpenMQ) based on the changes that occur in the database tables. *using a JMS queue will allow me loosely coupled system design ( I'm opened for suggestion here too ) E.g. * tables - tBook ( the table which is representing a book entity), tBookAuthor * tables audit - tBook_aud, tBookAuthor_aud * replication table : tReplicationMessage - this table holds the operation type ( insert/update/delete ), the changed columns, the table name ( e.g. tBook), and the primary key. * My current intention is to provide a stored procedure which will publish the messages on the JMS queue ( triggered by the tReplicationMessage table changes). The values that change will be taken from the audit tables. * Google protocol buffers will be used as messages format ( faster, optimal solution) What I don't like at this approach is the following case : * 20 tables ( tBookFormat, tBookCategory etc), and each table with more than 1000 changes per second will trigger too many Java processes ( my stored procedure from tReplicationMessage table) to be executed for each change. Not really sure what you mean by Java process here. Stored procedures execute within the same jvm and the same thread as they are called from. That being said, 2 tps is a high number, so you should probably do some benchmarking before committing to a particular design. What is the right way of implementing a batch system in order to be feasible( batch the messages, e.g. 5 messages, and fire a single Java process to send data to the JMS queue) ? Ok, I think I see what you're getting at - your JMS interface is to spawn a separate process to inject each message? What is wrong here and what should be the best approach with Derby DB ? I have never tried this, but my gut feeling is that it is a non-starter, even for much lighter loads than you have in mind. Java never really followed the UNIX philosophy of combining lots of small simple processes in a pipeline to do the work. Frankly, in this context, the jvm is a behemoth and trying to spawn many is rarely a good idea. Are you sure the JMS interface does not have a java api, e.g. for use in an appserver? Seems to me that you would want to access this api from wihin your Derby stored procedure. In terms of batching, you could have your trigger just insert records into a queue-table, and then have another thread batch records from this queue for sending. That obviously becomes a classic consumer/producer problem with all that entails (load control, etc.). -- Regards, Dyre
Re: Apache Derby Command Line?
On 02/22/2014 07:22 PM, Turtles wrote: I have java working properly (so ignore the quotes and anything else that looks wrong). That isint the issue, its getting derby to load properly. FireDaemon loads the process a little differently than the command line but it works because other java applications ive run as services run fine (tomcat for example). I'm not familiar with FireDaemon, so the following may not be relevant. I was faced with a similar issue when trying to create Windows services for a server that had to be started with a long and complicated command line. I was able to start the server in cmd.exe just fine, but when issuing the net start service command it would fail with strange errors. After some googling I discovered that there are registry entries that contain the string used to start the service. By inspecting that reg. entry after creating my service, I could see that the command line was being mangled in various ways if it contained spaces or other special characters. Some things I managed to work around by adding various quotes and escapes, but not always. One thing to check is whether your daemon/service facility uses a command interpreter (e.g. cmd.exe) when starting the executable. If so, you may have to quote things that should not be expanded twice, once for the command line creating the service, and once for the starting of the service. White space in command line arguments are particularly troublesome as you must prevent spaces from being interpreted as argument separators both when creating and launching the service. HTH, Dyre
Re: Derby in production environment
The URL you are referring to is NOT the official Derby web page, and the statements made there are not correct for the latest version of Apache Derby. The official page can be found here http://db.apache.org/derby/ Derby *is* used in production and it has security controls as described here: Configuring security for Derby I *think* that the page you refer to describe the state of a MessageBroker application running in WebSphere that *can* use Derby internally, but this configuration is not recommended for the reasons you mention. Regards, Dyre On 16. jan. 2014, at 15:41, AirDT cont...@solgt.fr wrote: Hello, I want to use Derby in a production environment and I read the following sentence: The Derby database Has No Associated security controls, and no optimizations-have-been performed. Reasons For thesis, do not use Derby in a Production environment. in doc http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fah35004_.htm Is Derby currently being used successfully in production environments? Any information would be appreciated. Thanks in advance. AirDT -- View this message in context: http://apache-database.10148.n7.nabble.com/Derby-in-production-environment-tp136557.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Derby in production environment
On 16. jan. 2014, at 15:56, Dyre Tjeldvoll dyre.tjeldv...@oracle.com wrote: The URL you are referring to is NOT the official Derby web page, and the statements made there are not correct for the latest version of Apache Derby. The official page can be found here http://db.apache.org/derby/ Derby *is* used in production and it has security controls as described here: Configuring security for Derby I *think* that the page you refer to describe the state of a MessageBroker application running in WebSphere that *can* use Derby internally, but this configuration is not recommended for the reasons you mention. To be clear: The statement is misleading in that it makes it sound as if Derby does not have security controls or an optimizer. In fact Derby has both, but I believe the article is trying to say that the Message Broker application does not configure the controls for Derby and they have not tuned their queries for Derby. Regards, Dyre Regards, Dyre On 16. jan. 2014, at 15:41, AirDT cont...@solgt.fr wrote: Hello, I want to use Derby in a production environment and I read the following sentence: The Derby database Has No Associated security controls, and no optimizations-have-been performed. Reasons For thesis, do not use Derby in a Production environment. in doc http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fah35004_.htm Is Derby currently being used successfully in production environments? Any information would be appreciated. Thanks in advance. AirDT -- View this message in context: http://apache-database.10148.n7.nabble.com/Derby-in-production-environment-tp136557.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Exporting table records
On 01/11/2014 08:20 PM, Bob M wrote: Hi I have a derby database with one table which I access using Netbeans What is an easist way for me to export the contents of the table ? Did you check out https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/adminguide/cadminimport16245.html Regards Dyre
Re: selecting records in a derby database
On 20. okt. 2013, at 04:01, Bob M rgmatth...@orcon.net.nz wrote: hi I have a derby database and a table with over 4,000 records… It is easier to think about a table as a hash table or tree, rather than an array. So there isn't really a concept of first or last other than the ordering imposed by the primary key (or other index). Meaning that there isn't an builtin way to retrieve the first or oldest entry (or the newest) unless you have a column that reflects the age (could be an auto increment column). (Note that Derby is different from some other databases (e.g. Oracle) which have something called row id which would have given you this ordering column automatically) I wish to:- a) get the number of records in the table SELECT COUNT(*) FROM t b) retrieve the last record Assuming you have a column x which imposes the order you're after: SELECT MAX(x) FROM t to get the max key SELECT * FROM t WHERE x=max key c) update this record UPDATE t SET a=.., b=.. … WHERE x = max key d) add a new record and INSERT INTO t VALUES(…) e) delete the first (oldest) record DELETE FROM t WHERE x = (SELECT MIN(x) FROM t) HTH, Dyre
Re: st.executeQuery() got stuck
On 07/ 4/13 10:36 AM, Amit wrote: This is the code I am using to execute query. Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); st.setFetchSize(100); ResultSet queryRs = st.executeQuery(sql); System.out.println(Success); Usually the query takes 1-2 sec to execute. But very rare occasion the code got stuck at line 4. That would be println... I assume you mean that it hangs in executeQuery()? I have waited for 1 hr and then killed the thread. It's a production issue. I am using connection pool and my derby version is - 10.9.1.0 Can you help me narrow down the problem. Really hard without more information. If you can post the SQL involved it would be helpful. Is this client/server or embedded? Is the table large? Do you access it through an index? Are other threads/connections accessing the same table simultaneously? Anything in derby.log at the time of the hang? Regards, Dyre
Re: st.executeQuery() got stuck
On 07/ 4/13 01:30 PM, Amit wrote: Thanks Dyre for your reply. Yes, I mean it hangs in executeQuery(). The derby running as client/server mode. Other threads/connections accessing the same table simultaneously. The tables are not that large. Max records among the tables listed in the query is 5,000 This is the query - (The columns ends with _ID are indexed) SELECTG_EVNT_MASTER_NAME, B.G_CORE_PARTY_NAME, A.G_CORE_PARTY_NAME, G_EVNT_MASTER_START_DT, G_EVNT_MASTER_ID, G_EVNT_MASTER_OFFERING, G_CORE_DOCUMENT_NAME, G_CORE_DOCUMENT_URL FROMINTERNAL.G_CUST_OPPORTUNITY, INTERNAL.G_XREF_EVNT_OPPR, INTERNAL.G_CORE_PARTYA, INTERNAL.G_CORE_PARTYB, INTERNAL.G_XREF_EVNT_PARTY, INTERNAL.G_EVNT_MASTER LEFTOUTERJOININTERNAL.G_CORE_DOCUMENT ONG_EVNT_MASTER_ID=G_CORE_DOCUMENT_MEETING_ID WHEREG_EVNT_MASTER_ID=G_XREF_EVNT_OPPR_EVENT_ID ANDG_XREF_EVNT_OPPR_OPORTUNITY_ID=G_CUST_OPPORTUNITY_ID ANDA.G_CORE_PARTY_ID=G_EVNT_MASTER_ORGANIZER_ID ANDB.G_CORE_PARTY_ID=G_XREF_EVNT_PARTY_PARTY_ID ANDG_XREF_EVNT_PARTY_EVENT_ID=G_EVNT_MASTER_ID ANDG_EVNT_MASTER_TYPE='Contact Event' ANDG_XREF_EVNT_PARTY_TYPE='Primary Contact' ANDG_CUST_OPPORTUNITY_ID='99223977' This is the derby.log - Booting Derby version The Apache Software Foundation - Apache Derby - 10.9.1.0 - (1344872): instance a816c00e-013f-a339-24f0-697cf4d7 on database directory E:\database1 with class loader sun.misc.Launcher$AppClassLoader@f08d0f Loaded from file:/E:/lib/drivers/derby.jar java.vendor=Oracle Corporation java.runtime.version=1.7.0_13-b20 user.dir=E:\ derby.system.home=E:\ Database Class Loader started - derby.database.classpath='' Right. Please have a look at the tuning guide: http://db.apache.org/derby/docs/10.10/tuning/ttundepth33391.html But you should probably start by finding out if your server is buzy or idle. E.g. by using jstack or some other java monitoring tool on the jvm running the Derby server to see what it appears to be doing when the query hangs. These are some possibilities: - Derby thread is starved - other threads take all the resources. Can happen if Derby is sharing the jvm with another application like an appserver. - Derby thread blocked. It is waiting for db lock or java monitor. - Derby thread busy. Optimizer may have chosen a bad plan. Maybe statistics need to be updated. - Derby thread is idle. Possibly network problem between client and server. HTH, Dyre -- Thanks, Amit On Thu, Jul 4, 2013 at 4:06 PM, Dyre Tjeldvoll dyre.tjeldv...@oracle.com mailto:dyre.tjeldv...@oracle.com wrote: On 07/ 4/13 10:36 AM, Amit wrote: This is the code I am using to execute query. Statement st = con.createStatement(ResultSet.__TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); st.setFetchSize(100); ResultSet queryRs = st.executeQuery(sql); System.out.println(Success); Usually the query takes 1-2 sec to execute. But very rare occasion the code got stuck at line 4. That would be println... I assume you mean that it hangs in executeQuery()? I have waited for 1 hr and then killed the thread. It's a production issue. I am using connection pool and my derby version is - 10.9.1.0 Can you help me narrow down the problem. Really hard without more information. If you can post the SQL involved it would be helpful. Is this client/server or embedded? Is the table large? Do you access it through an index? Are other threads/connections accessing the same table simultaneously? Anything in derby.log at the time of the hang? Regards, Dyre
Re: Question on database, schemas and backups
On 06/28/13 09:07 AM, Maison Mo wrote: Hello, For a multi-tenant application, we evaluate the multiple databases pattern and single database / multiple schemas pattern. Regarding the backup process, it seems that the whole database is backed up by the SYSCS_UTIL.SYSCS_BACKUP_DATABASE(), thus all schemas in this DB : is that correct ? Is it then possible to restore a single schema ? I believe so, yes. Given a jdbc client, it is possible to change schema once connection is established. SET SCHEMA, See http://db.apache.org/derby/docs/10.10/ref/rrefsqlj32268.html Is it also possible to change the database ? I think you need to create a new connection to the new database. Regards, Dyre
Re: Problem inserting
On 04/16/13 01:46 PM, Alejandro Rodríguez González wrote:
Basically, I insert using this code:
public void insertInputs(SetInput ri) throws Exception {
PreparedStatement psInsert = null;
IteratorInput it = ri.iterator();
while (it.hasNext()) {
Input in = it.next();
Note that doing it this way defeats the purpose of using prepared
statements, since you create and prepare it each time through the loop.
A better (faster) way would be to move the call to prepareStatement out
of the loop. But it does
psInsert = conn
.prepareStatement(insert into
inputs(input_class,api) values (?, ?));
Where does conn come from here? Are you sure it is in autocommit mode?
It needs to be since you don't call conn.commit() explicitly. Also,
you're using the default schema for your table. Are you sure it is what
you believe it to be?
psInsert.setString(1, in.getObject());
If Constants.APIS[this.api] does not change as you iterate, it does not
need to be a parameter.
psInsert.setString(2, Constants.APIS[this.api]);
this.logger.log(Inserting input in db: +
in.getObject() + - API[ + Constants.APIS[this.api] + ]);
I assume that you see this output when you run your program?
psInsert.executeUpdate();
Place the close after the loop to re-use the prepared statement.
psInsert.close();
}
}
It is supposed that in the following execution of my program, when I
made the select statement to see the inputs in the database, it should
give me results, but.. no results are provided. I attach you the first
two executions of my program:
First (database not created): http://pastebin.com/1k7R8Yup
Second (database created. It is supposed that inputs table should
contain data): http://pastebin.com/yzsK1q9w
So.. as far as I understand, something is wrong in the insert but.. I
don't know what.
I hope someone can help me.
Thanks!
--
Dr. Alejandro Rodríguez González - PhD
Bioinformatics at Centre for Plant Biotechnology and Genomics UPM-INIA
Polytechnic University of Madrid
http://www.alejandrorg.com http://www.alejandrorg.com/
Phone: +34 914524900 . Ext: 25550
//Once the game is over, the king and the pawn go back in the same box.
- Italian proverb//
Re: DatabaseMetaData.getTables() resultset empty?
Ole Ersoy [EMAIL PROTECTED] writes:
Hi,
I'm trying to use DatabaseMetaData.getTables() to get a list of tables
contained in a derby database. I run the statement like this (I made sure
tables are present by browsing using the eclipse Data Source explorer first):
ResultSet resultSet = metaData.getTables(null, null, %, null);
But resultSet is empty. I also tried it like this:
ResultSet resultSet = metaData.getTables(null, null, EXISTING_TABLE_NAME,
null);
Still no love. Anyone have any ideas on what I could be doing better?
How do verify that the result set is empty? Fww. the following works:
import java.sql.*;
public class repro {
public static void main(String[] args) {
try {
Class.forName(org.apache.derby.jdbc.EmbeddedDriver);
Connection con =
DriverManager.getConnection(jdbc:derby:/tmp/reprodb;create=true);
Statement s = con.createStatement();
try {
s.execute(create table foobar (i int, j int));
s.execute(create view fv as select * from
foobar);
} catch (SQLException sqle) {
System.out.println(sqle);
}
System.out.println(--- tables ---);
DatabaseMetaData m = con.getMetaData();
ResultSet tables = m.getTables(null, null, %, args);
while (tables.next()) {
System.out.println(tables.getString(TABLE_NAME)+
(+tables.getString(TABLE_TYPE)+));
}
} catch (Exception sqle) {
sqle.printStackTrace();
}
}
}
[EMAIL PROTECTED]/java$ java -classpath
/home/dt136804/derby-10.4_sane_jars/derby.jar:. repro TABLE VIEW
java.sql.SQLException: Table/View 'FV' already exists in Schema 'APP'.
--- tables ---
FOOBAR (TABLE)
FV (VIEW)
--
dt
Re: Embedded database which only stores data in memory?
Aaron Zeckoski [EMAIL PROTECTED] writes: I am trying to use embedded derby for testing but I am finding it much slower than HSQLDB to start and run and significantly more annoying since I have to remove the actual files between test runs to ensure my database is clean. Is there a way to force derby to not create any files and therefore operate more like something like HSQLDB? I believe there is an existing Jira issue for this, and that someone started working on it, but the work was never completed. It would be interesting to know how you were planning to use Derby if it could run in memory only. Based on what you write I'm assuming that you are running (unit) test frame work (maybe even JUnit) that has a large number of test cases, and that it somehow is inconvenient for you to let all test cases use the same database and clean up afterwards. But would you also use Derby in deployment? Presumably usage of the database would be rather different in deployment? I mean, not may applications put temporary data in a relational database, so I'm guessing that in deployment you would not want to throw away the database files each time you close your application, right? And while I'm sure the problem you have with testing is a pain, I'm not so sure Derby developers will queue up to solve it, as they probably are more interested in making Derby a good database for deployments. -- dt
Re: Is Derby.jar really built using javac?
cowwoc [EMAIL PROTECTED] writes: Is there any way for you to find out the exact JDK (vendor, version) used to compile derby.jar? That is available in the release notes. See http://db.apache.org/derby/releases/release-10.4.1.3.cgi Look for the Java 6/Compiler items in the Build environment section. The vendor is Sun Microsystems. -- dt
Re: Is Derby.jar really built using javac?
cowwoc [EMAIL PROTECTED] writes: Hi, Can someone please confirm whether derby.jar is really built using javac as the website indicates? It seems the latest version might have been built using the Eclipse compiler, Why do you think so? The jars were built on a machine that does not have eclipse installed, so I very much doubt that eclipse could have been used. The machine runs Solaris 10 x86, and as far as I know, eclipse is not even available for that platform. The jars were built using the ant commands described on the wiki: http://wiki.apache.org/db-derby/DerbySnapshotOrRelease?highlight=(Snapshot)|(release)#head-e340693f4c21d10639f13e449b405b8cd128c3f6 -- dt
Re: Anyone successfully used Derby with Perl (ODBC)
CheapLisa [EMAIL PROTECTED] writes: If anyone has successfully used Perl (DBI / DBD) with Derby, please let me know what I am doing wrong. I was able to get the DBI module installed but when I try to install DBD::ODBC it complains that I have no ODBC driver anywhere. Where do I get an ODBC driver for the latest version of derby and how do I tell the Perl install util where it is so it can be found? There is no ODBC driver for Derby. But you CAN use the DB2 universal ODBC driver with a Derby NetworkServer. The DB2 driver is not open source, but you can download a binary for free from IBM, but only for certain platfroms (sorry, I don't have the URL). -- dt
Re: Portability issue for 'Like-clause' on non-string types ?
Albert Kam [EMAIL PROTECTED] writes: Hello again Apache Derby, I'm currently porting my little webapp from using mysql to apache derby. One of the issue i'm having right now is the like clause being used for non-string types. I have not checked the exact chapter and verse of the SQL standard, but I'm pretty sure that LIKE for non-string data is a non-standard extension. As described in the charter, Derby aims to be standard compliant. MySQL on the other hand states fairly openly that they will deviate from the standard when they think that is convenient. So basically, porting from MySQL is going to require some effort, I'm afraid. Anyway, is there a configuration that can be use to be 'friendly' for using like-clause on non-string types ? Or perhaps anything else that i'm missing out from the docs ? :) Not 100% sure, but I don't think so. -- dt
Re: Problem connection to derby with PHP using PDO or ibm_db2 (Linux)
David Rekowski [EMAIL PROTECTED] writes:
Hello list!
My aim is to get PHP to connect and work with Apache Derby. (See end of
message for system and versions)
I tried to follow Dan Scotts slightly outdated tutorial from
http://coffeecode.net/archives/16-Apache-Derby-tutorial-OSCON-2005-materials.html
PDO works for example with mysql, but when I try to connect to derby via
PDO like this
$DSN = DRIVER={IBM DB2 ODBC
DRIVER};PROTOCOL=TCPIP;DATABASE=MYDB;HOSTNAME=localhost;PORT=1527;
$dbh = new PDO(odbc:$DSN);
Have you actually downloaded and installed the DB2 ODBC driver on your
system? It doesn't come with Derby, and last time I checked it was only
distributed as closed source, and only for certain platforms...
It was available on Linux, but you needed root access to install it, if
I remember correctly.
The DB2 ODBC driver should work with a Derby Network Server as they both
use the DBMS-indpendent DRDA protocol...
--
dt
Re: Feedback releases/release-10.4.1.3.html
Andrew McIntyre [EMAIL PROTECTED] writes: On Mon, Apr 28, 2008 at 1:09 PM, ted lifset [EMAIL PROTECTED] wrote: To Whom It May Concern: the page/section: http://db.apache.org/derby/releases/release-10.4.1.3.cgi#Distributions contains a link to: derby_core_plugin_10.4.1.648739.zip this file does not appear to exist on any mirrors. Dyre, I hope you don't mind, I went ahead and copied the core plugin files from the RC directory in your home dir over to the distribution location so that it gets picked up by the mirrors. Not at all! Thanks for doing this :) -- dt (Scratching his head trying to figure out how this happened...)
Re: DB gets corrupts in 10.3.1.2!!
Binoy Thomas [EMAIL PROTECTED] writes: We are a firm having multiple software products we use the Derby DB in all our suite of products for quite many years now. We are facing a blocker situation that is halting all our release process. Any help would be appreciated on the below DB corruption error. Derby versions tried: Derby 10.3.1.2 10.4.1.3 User scenario is: Our base application runs 2 Derby DBs. Another application (which is part of the suite of products) as per deployment strategy would need to be installed on top of it. In this process of installation, the installer first brings down the base application (thereby the DBs are shutdown), installs the 2nd application then brings up the base application as well as the other application. (Internal process while re-starting application: DBs from the base application are brought up then the DB from the 2nd application is brought up.) Have you created a new database with 10.4? If you use 10.4 to access an already corrupt db you will still get an error, I'm afraid. Restoring a non-corrupt image from backup and then upgrade, should also work. -- dt
[ANNOUNCE] Apache Derby 10.4.1.3 released
The Apache Derby project is pleased to announce a new feature release of Derby, 10.4.1.3. Apache Derby is a subproject of the Apache DB project. Derby is a pure Java relational database engine which conforms to the ISO/ANSI SQL and JDBC standards. Derby aims to be easy for developers and end-users to work with. Derby 10.4.1.3 can be obtained from the Derby download site: http://db.apache.org/derby/derby_downloads.html. Derby 10.4.1.3 introduces the following new capabilities: * Asynchronous Replication Allows you to maintain an up to date copy of your (master) database on a different host (the slave). In the case of a crash on the master database you can perform failover to the copy (slave database) and continue serving client requests against your database. * Security o Shutting down the Network Server now supports user authentication, and in fact requires credentials when authentication is enabled. * SQL o Table Functions. Using table functions and standard SQL, you can pose sophisticated queries against in-memory collections, flat files, web resources, non-relational databases, etc.. Table functions also let you efficiently import data from web feeds, sensor logs, and other relational databases. o Unique constraints on nullable columns. o SQL ROW_NUMBER() window function, (for an empty, inlined window specification). o Bracketed comments (/* ... */). * Performance and Memory Usage o New buffer manager with better concurrency. o Statement cache in the client driver. o Caching of isolation level and current schema in the client driver. * Administration o Java Management Extensions (JMX) for Derby, allowing local and remote monitoring and management of running Derby instances (embedded or Network Server). o Continuation prompt. ij adds a short prompt to the next line after a newline has been entered by the user without ending the statement with a semicolon. Derby 10.4.1.3 also has many bugfixes, including the fix for DERBY-3347 which can cause unrecoverable database corruption. All 10.3 users are encouraged to upgrade to avoid potential corruption issues. -- Regards, Dyre
Re: Problems with BLOBS (fixed....)
Mikael [EMAIL PROTECTED] writes: Sorry about that, my head was in Mississippi or something, I managed to use an old Derby jar file so I was not running the correct version, sorry. So which version of Derby are you testing with now? DERBY-3085 has been fixed and should be available in the 10.3.2.1 release (as well as in the upcoming 10.4 release). -- dt
Re: A network protocol error was encountered and the connection has been terminated
Vic Ricker [EMAIL PROTECTED] writes: Sorry about that. I added something to dump follow getNextException() and getCause(). I got the following exception from getCause(), I believe. I don't think getNextException() returned anything. Thanks you did exactly the right thing. I should have mentioned getCause() as well. (Both are currently in use, I believe). I agree with Bryan's conclusions in another email; this sounds like a Derby bug. Based on the call stack I'd say what you're seeing is a meta error (for lack of a better word). I think that what is happening is that the client sends a request to prepare a statement to the server, but something goes wrong and an error is returned. As the client tries to parse the reply it detects that an error has occured (parsePrepareError(NetStatementReply.java:527)), but the error handling appears not to be able to handle the returned error correctly, and so you end up with a generic (and rather unhelpful) error message. If my analysis is correct, you may get around this if you can guess or trace what the root cause on the server is, and avoid it altogether. The best thing, obviously would be if you could come up with a small(er) example which reproduces the problem, which you can share with us (include in a Jira issue). Without being able to reproduce the problem, it will be nearly impossible for someone else to fix it, I'm afraid. org.apache.derby.client.am.DisconnectException: A network protocol error was encountered and the connection has been terminated: the requested command encountered an unarchitected and implementation-specific condition for which there was no architected message org.apache.derby.client.net.NetConnectionReply.parseCMDCHKRM(NetConnectionReply.java:888) org.apache.derby.client.net.NetStatementReply.parsePrepareError(NetStatementReply.java:527) org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(NetStatementReply.java:143) org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(NetStatementReply.java:52) org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(StatementReply.java:40) org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(NetStatement.java:139) org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Statement.java:1341) org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(PreparedStatement.java:1874) org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(PreparedStatement.java:1952) org.apache.derby.client.am.PreparedStatement.prepare(PreparedStatement.java:309) org.apache.derby.client.am.Connection.prepareStatementX(Connection.java:1646) org.apache.derby.client.am.Connection.prepareStatement(Connection.java:413) com.scs.database.sql.MoneyDatabaseSQLType.prepareStatement(MoneyDatabaseSQLType.java:2235) com.scs.database.sql.MoneyDatabaseSQLType.getTransactionByReferenceNumber(MoneyDatabaseSQLType.java:3393) com.scs.money.transaction.TransactionTable.getByReferenceNumber(TransactionTable.java:43) com.scs.money.data.TransactionImportEntry.isDuplicateReference(TransactionImportEntry.java:334) com.scs.money.data.TransactionImportEntry.setReferenceNumber(TransactionImportEntry.java:178) com.scs.money.data.MoneyImportFile.processMoneyImportFile(MoneyImportFile.java:750) com.scs.money.data.DataProcessPanel.doImport(DataProcessPanel.java:201) com.scs.money.data.DataProcessPanel.access$000(DataProcessPanel.java:19) com.scs.money.data.DataProcessPanel$1.process(DataProcessPanel.java:52) com.scs.money.TaskedJob.process(TaskedJob.java:19) com.scs.money.TaskedJob$1.processTask(TaskedJob.java:33) com.scs.taskmanager.TaskManager.cycle(TaskManager.java:71) com.scs.taskmanager.TaskManager.run(TaskManager.java:23) -- dt
Re: limit, offset and random
unludo [EMAIL PROTECTED] writes: Hello, I would like to use the following constraints at the end of some queries: SELECT a FROM B WHERE c = 'toto' ORDER BY RANDOM() LIMIT 10; LIMIT is a non-standard extension. Derby 10.4 (soon to be released) includes the SQL ROW_NUMBER function which will let you do the same thing, although with a different and slightly more verbose syntax. -- dt
Re: A network protocol error was encountered and the connection has been terminated
Vic Ricker [EMAIL PROTECTED] writes: Can anyone give me any suggestions on how to debug this issue? Derby uses exception chaining. The call stack you posted only show the place where an SqlException was caught and transformed to an SQLException which was then thrown. To see the full stack trace you need to do traverse the chain of exceptions using SQLException.getNextException(). With that we should at least be able to tell where in driver the network problem is detected... -- dt
Re: ERROR XSDG2: Invalid checksum on Page Page(0,Container(0, 1313))
David Sitsky [EMAIL PROTECTED] writes: For what its worth, I did another run last night on my 6 quad-core system. This time I had the derby issue happen for a JVM process on machine 1, two processes on machine 4, and one on machine 5. I run four JVM processes per quad-core machine. All the JVM processes have roughly the same data processing rate, but the issue happens at different times into the load. The problem occurred around time 420, 480, 800 and 900 minutes into the load for the four problematic processes. Just to clarify: These jvm processes do not access the SAME database, do they? -- dt
Re: Newbie question
Chris Collins [EMAIL PROTECTED] writes: Select is a good start, procedures would probably work to some extent, though one can imagine that some of the power is lost if for say I wanted to perform an insert into that pseudo table. I am syncing 10.4 source tree as I write this email. There is a beta out at http://people.apache.org/~dyre/derby-10.4.1.0-beta/ if you don't want to build from source... -- dt
Re: Newbie question
[EMAIL PROTECTED] writes: Chris Collins [EMAIL PROTECTED] writes: Select is a good start, procedures would probably work to some extent, though one can imagine that some of the power is lost if for say I wanted to perform an insert into that pseudo table. I am syncing 10.4 source tree as I write this email. There is a beta out at http://people.apache.org/~dyre/derby-10.4.1.0-beta/ if you don't want to build from source... Be adviced, however, that this is NOT formally an Apache Beta Release as it has not been voted on by the Derby community. -- dt
Re: Derby and Bitronix JTA connection failure
Brett Wooldridge [EMAIL PROTECTED] writes:
Hello list,
I am stuck trying to figure out an issue with using the Bitronix JTA
Transaction Manager with Derby (v10.3.2.1). Here’s the details:
I’m running Derby as a network server on the standard port. I have a unit
test which uses the ClientXADatasource to connect to my Derby database, and
this succeeds.
The client code looks like this:
ClientXADataSource ds = new ClientXADataSource();
ds.setDatabaseName(ziptie);ds.setPortNumber(1527);
ds.setServerName(localhost);Connection connection =
ds.getConnection();
As I said, this client connects successfully, and the derby.log records this:
Connection number: 2.
2008-03-16 11:45:05.302 GMT Thread[DRDAConnThread_6,5,derby.daemons] (DATABASE
= ziptie), (DRDAID = {2}), Apache Derby Network Server connected to database
ziptie
Then, I try to start an application which uses the Bitronix TM to perform a
similar connection to Derby. However, this connection fails as recorded by
the derby.log as well:
Connection number: 3.
2008-03-16 11:45:10.817 GMT Thread[DRDAConnThread_6,5,derby.daemons] (DATABASE
= ziptie), (DRDAID = {3}), Database not available
The properties for Bitronix look like this:
resource.ds.className=org.apache.derby.jdbc.ClientXADataSource
resource.ds.uniqueName=ziptie
resource.ds.driverProperties.databaseName=ziptie
resource.ds.driverProperties.serverName=localhost
resource.ds.driverProperties.portNumber=1527
When the connection fails, I get a network client trace that looks like this:
(snip)
derby] BEGIN TRACE_DIAGNOSTICS
[EMAIL PROTECTED] java.sql.SQLException
[EMAIL PROTECTED]@ecf7fa] DERBY SQLCA from server
[EMAIL PROTECTED]@ecf7fa] SqlCode= -1
[EMAIL PROTECTED]@ecf7fa] SqlErrd= { 0, 0, 0, 0, 0, 0
}
[EMAIL PROTECTED]@ecf7fa] SqlErrmc = Database not
available
[EMAIL PROTECTED]@ecf7fa] SqlErrp= CSS10030
[EMAIL PROTECTED]@ecf7fa] SqlState = 08006
[EMAIL PROTECTED]@ecf7fa] SqlWarn=
[EMAIL PROTECTED] SQL state = 08006
[EMAIL PROTECTED] Error code = -1
[EMAIL PROTECTED] Tokens = Database not available
[EMAIL PROTECTED] Stack trace follows
org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1,
SQLSTATE: 08006, SQLERRMC: Database not available
at org.apache.derby.client.am.Connection.completeSqlca(Unknown Source)
(snip)
I have enabled derby.drda.traceAll on the server and here is what I get for
the SUCCESSFUL connection:
I guess you don't see a call-stack in derby.log?
Anyway, I think the error originates from
java/engine/org/apache/derby/jdbc/EmbeddedXADataSource.java:174
and is caused by the resource adapter obtained from the database being
null (or maybe database itself is null). This sounds like a Derby bug to
me. Unless someone tells you otherwise, I suggest logging a jira issue
for this.
Be adviced though, that as long as this can only be reproduced with a
specific Transaction Manager, there is not much chance of it being
fixed, I fear. If you could find a way to replicate the behavior of the
Transaction manger the odds would be better (unless Bitronix is
free/open source product)
(2008.3.16 11:58:53) Request fill DRDAConnThread_6 5
RECEIVE BUFFER: EXCSAT (ASCII) (EBCDIC)
0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF
0065D0410001005F 10410010115E8485 .e.A..._.A...^.. ..}..;de
0010 9982A88495839481 89950009116DC485 .m.. rbydncmain..._De
0020 9982A80020115AC4 D5C3F1F0F0F3F061 .Za rby...!DNC10030/
0030 F1F04BF34BF14BF4 4060404DF5F6F1F7 [EMAIL PROTECTED]@M 10.3.1.4
- (5617
0040 F9F45D0014140414 0300072407000724 ..]$...$ 94).
0050 0F0007144700 0E1147D8C4C5D9C2 [EMAIL PROTECTED] ..QDERB
0060 E861D1E5D40026D0 0100020020106D00 .a. .m. Y/JVM..}.._.
0070 0611A20004001621 10A98997A3898540 ...!...@ ..s..ziptie
0080 4040404040404040 404040@@@
(2008.3.16 11:58:53) Reply flush DRDAConnThread_6 5
SEND BUFFER: EXCSATRD (ASCII) (EBCDIC)
0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF
009BD04200010095 14430035115ED585 ...B.C.5.^.. ..}n.;Ne
0010 A3A6969992E28599 A58599C39695A399 tworkServerContr
0020 969340E2A38199A3 40D385A5859340C5 [EMAIL PROTECTED]@[EMAIL
PROTECTED] ol Start Level E
0030 A58595A340C489A2 9781A38388859900 [EMAIL PROTECTED] vent Dispatcher.
0040 1414041403000724 070007240F000714 ...$...$
0050 4700101147C1 978183888540C485 @[EMAIL PROTECTED] ..Apache
De
0060 9982A80018116DD5 85A3A6969992E285 ..m. rby..._NetworkSe
0070 99A58599C39695A3 9996930020115AC3
Re: Transaction Problems of a Derby Stored Procedure
sin(EaTing), [EMAIL PROTECTED] writes: Thanks, Dan. The document I read seems already the latest, which is Derby Developer's Guide Version 10.3. And I am still confused about the isolation mode, I traced the stored procedure using getTransactionIsolation() and the return value shows the isolation level was changed, I am not sure about the whether that data can reflect the real isolation level. I think that the documentation is incorrect here. It is possible to modify the isolation level in both a function and a procedure, but not while a cursor is open because changing the isolation level will commit the current transaction. You can see a Derby JUnit test which modifies the isolation level in a number of ways here http://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CacheSessionDataTest.java -- dt
Re: using SELECT as an SQL triggered action
musky [EMAIL PROTECTED] writes: somebody please help me here please!! please!! its urgent... Please see http://www.catb.org/~esr/faqs/smart-questions.html#urgent musky wrote: i will make my aim more clearer here: i have created an AFTER insert trigger on a table called chattable which has two fields(ID INT,Message VARCHAR(255)).here is the trigger: CREATE TRIGGER insert_trigger AFTER INSERT ON chattable REFERENCING NEW_TABLE AS chattableupdate FOR EACH STATEMENT SELECT * FROM chattableupdate where chattableupdate is a transition table. i want the transition table to contain the newly inserted rows whenever an insert takes place into chattable. please help me,because with this trigger,when i select * from chattableupdate and assign it to a resultset i get an error that the chattable view/table does not exist I think it would be a lot easier to figure out what the problem is if you could show the failing code. my problem is how to store the result of the SELECT * from table_name statement so that i can display it on the console output whenever ant data is inserted into the table,that data is retrieved from the table. It sounds like you're trying to build a sort of audit trail, so that you can keep track of changes to your data. Triggers are indeed useful for this sort of thing. I think one way to do this is to have your trigger capture the data that is being inserted/updated/deleted, and inside your trigger take that data and store it in an audit table. Then, whenever you want, you can issue normal select statements against your audit table, and fetch the audit records of interest. thanks, bryan -- dt
Re: derby databse connection in Hibernate
cnapagoda [EMAIL PROTECTED] writes: I try to connect derby database using Hibernate. But I got error like this. Exception in thread main org.hibernate.hql.ast.QuerySyntaxException: USERINFO is not mapped [FROM USERINFO] at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:158) at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:87) at org.hibernate.hql.ast.tree.FromClause.addFromElement(FromClause.java:70) at org.hibernate.hql.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:255) at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3056) at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2945) at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688) at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229) at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:77) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:56) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72) at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133) at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112) at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301) at $Proxy0.createQuery(Unknown Source) at com.test.Htest.listEvents(Htest.java:44) at com.test.Htest.main(Htest.java:25) My datadase name is power_moniter, table name is userinfo. why i'm I got error like that? AFAICT no part of your stack trace is from Derby. So my guess would be that you have encountered a Hibernate bug... Dyre
Re: NullPointerException in Derby driver (from at least 10.2.2.0)
Eric Floehr [EMAIL PROTECTED] writes: Daniel, Yes, we see that as well, but not always. Following is the stack trace of that. Did you ever find the solution to the problem? Thanks so much! SQL Error code: 4 SQL State: 08003 java.sql.SQLNonTransientConnectionException: No current connection. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.noCurrentConnection(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.checkIfClosed(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.setupContextStack(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.rollback(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:958) at $Proxy5.rollback(Unknown Source) at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.rollback(JdbcTransaction.java:72) at com.ibatis.sqlmap.engine.transaction.TransactionManager.end(TransactionManager.java:87) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction(SqlMapExecutorDelegate.java:724) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSessionImpl.java:176) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClientImpl.java:153) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(SqlMapExecutorDelegate.java:825) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:400) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:58) at com.our code at com.our code at com.our code at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: No current connection. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 26 more Seems like a bug to me. Unless someone can point to an existing Jira for this I think it would be good to create one. -- dt
Re: distinct and order by
tom_ [EMAIL PROTECTED] writes: I am using disctinct because of some self-joins and also needed to add an order by clause. An error is shown. Is it not possible to use distinct and order by together? I think it is allowed. Executing select distinct * from sys.systables order by tablename; in ij works just fine. Could you show the error message you get, and perhaps what the table looks like? -- dt
Re: Installing and Using Java Procedures.
Briggs [EMAIL PROTECTED] writes: Hey, thanks for your reply. I am still way confused on this. And trying to find examples of the SQL/JRT spec is almost impossible. I am trying to make some sense of the examples of creating store procedures off the http://wiki.apache.org/db-derby/DerbySQLroutines wiki page with no avail. I am just trying to find the actual rules/spec/documentation on doing this. Don't know if it helps, but you could take a look at one of Derby's unit tests for procedures. For example https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java -- dt
Re: How do I share an Apache derby database to other users?
[EMAIL PROTECTED] writes: larsk [EMAIL PROTECTED] writes: I am trying to create an application (in Java 6.0) that will create and use an Apache Derby database. I want to make it so other users will be able to access this database at the same time. How do I make this possible? What do I need to do on the server application? What do I need to on the client applications? Is there any guide out there on how to make this? If anyone have some information on how to make this work I would really appreciate some help. What you need to do really depends on just what you mean with 'users'. Do you mean to let multiple OS users access the same database instance? I.e. different processes with different uids? This is possible but not in embedded mode. You either need to start a NetworkServer, or start your app with an embedded server. (See the documentation) Note that you cannot have two jvms accessing the same database. I must have been tired when I wrote that. It should be Note that you cannot have two jvms accessing the same database IN EMBEDDED MODE -- dt
Re: Generate Always and SQLIntegrityConstraintViolationException
[EMAIL PROTECTED] writes: Knut Anders Hatlen [EMAIL PROTECTED] writes: wbecker [EMAIL PROTECTED] writes: I don't understand how this can be happening, but it is: I've created at a table: CREATE TABLE Foo ( id INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) , PRIMARY KEY (id) ); This works fine and I have added some data to it by using: INSERT INTO Foo () VALUE (DEFAULT) This works fine for a bit, but eventually it stops working and tells me: java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL080122133352260' defined on 'Foo'. How could I possibly have duplicate values if the only way I add to it is by using the above query in a PreparedStatement? Now, I have found a way around it, which seems very dodgy. I can just recreate the PreparedStatement and then run the insert again and it works. Is this a bug, expected behaviour or am I doing something wrong? This looks like a bug to me. It would be great if you could log a bug report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html for instructions) so that we can keep track of it. And you might as well check the regression box right away, since it is a regression caused by DERBY-827. It works in revision = 540920. 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. -- dt
Re: Generate Always and SQLIntegrityConstraintViolationException
Daniel John Debrunner [EMAIL PROTECTED] writes: [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). Bummer... 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? With a default of CURRENT TIMESTAMP does the same problem occur? Nope. Here is the result using an unpatched trunk: ij version 10.4 ij connect 'jdbc:derby:/tmp/db4;create=true'; ij create table time(ts timestamp with default CURRENT_TIMESTAMP); 0 rows inserted/updated/deleted ij prepare p as 'insert into time(ts) values (default)'; ij execute p; 1 row inserted/updated/deleted ij execute p; 1 row inserted/updated/deleted ij execute p; 1 row inserted/updated/deleted ij select * from time; TS -- 2008-01-22 18:00:48.944 2008-01-22 18:00:48.951 2008-01-22 18:00:48.952 3 rows selected But I see in the debugger that canCacheRow is false in this case, whereas it was true in previous case... -- dt
Re: Generate Always and SQLIntegrityConstraintViolationException
Daniel John Debrunner [EMAIL PROTECTED] writes: [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. Good guess :) I've traced it to RowResultSetNode.canWeCacheResults() (no big surprise) which returns false in the CURRENT_TIMESTAMP case, and true in the identity case. canWeCacheResults() use a rather tricky visitor pattern, but I think it ends up returning true because the ResultColumn.expression points to a NumericConstantNode, and NumericConstantNode.getOrderableVariantType() (actually inherited from ConstantNode) returns Qualifier.CONSTANT. CurrentDatetimeOperatorNode.getOrderableVariantType() on the other hand returns Qualifier.QUERY_INVARIANT, which is then transformed to Qualifier.SCAN_INVARIANT in ResultColumn.getOrderableVariantType(). Not sure where the default identity information should be detected in all of this though... -- dt
Re: Help with performance of apache derby server
musky [EMAIL PROTECTED] writes: also note that when the user presses the back and next buttons provided in the testing GUI to navigate through the questions a select operation is made.there will be a maximum of say 1000 questions in the database tables. Don't think the size of your problem/likely transaction rate makes a difference here. So if I were you I'd rather look at other factors that may influence your decision, such as: - Licence, Apache vs Dual GPL - Embedded vs server - All Java vs native code - Support for SQL features need/want etc. musky wrote: hello, i am developing a project in school which is a testing tool which will present to its students mcq questions,figure based questions.note that for the figure based questions,the required image is stored in the database in a BLOB type object. the number of students who will be answering are 50(min) to 100(maximum). the answer tables are updated as and when the student answers his questions. is it a good idea to use apache derby for this kind of project or should i look at mysql server. please tell me which is better suited. i urgently need views on this. good day. -- View this message in context: http://www.nabble.com/Help-with-performance-of-apache-derby-server-tp14996167p14996273.html Sent from the Apache Derby Users mailing list archive at Nabble.com. -- dt
Re: How do I share an Apache derby database to other users?
larsk [EMAIL PROTECTED] writes: I am trying to create an application (in Java 6.0) that will create and use an Apache Derby database. I want to make it so other users will be able to access this database at the same time. How do I make this possible? What do I need to do on the server application? What do I need to on the client applications? Is there any guide out there on how to make this? If anyone have some information on how to make this work I would really appreciate some help. What you need to do really depends on just what you mean with 'users'. Do you mean to let multiple OS users access the same database instance? I.e. different processes with different uids? This is possible but not in embedded mode. You either need to start a NetworkServer, or start your app with an embedded server. (See the documentation) Note that you cannot have two jvms accessing the same database. Or do you mean what you need to do to create users in the database and authorize them, i.e. CREATE USER, GRANT/REVOKE etc. ? This is explained in the the manual, but I don't have the exact chapter and verse. -- dt
Re: sql query input prompts
MR GS REARDON [EMAIL PROTECTED] writes: Hello: I'm looking for documentation and examples on user input prompts in a sql query. Not sure I grok what you're saying here? Do you want to write SQL which reads input from a terminal, or window? The conventional way of doing this (with JDBC) is read your input in Java and then use it to set parameters in a PreparedStatement (or construct a statement if that's more appropriate). E.g. something like PreparedStatememt ps = conn.prepareStatement(SELECT * FROM T where i = ?); int i = readInput(); ps.setInt(1,i); ps.executeQuery(); ... I'd appreciate someone telling me where I can find this information. Google for Java and JDBC tutorials. -- dt
Re: Please Help with apache network server!
musky [EMAIL PROTECTED] writes: i want to build a java application which will run on multiple nodes which access a database to retrieve questions(different questions may be retrieved by diferent hosts) and their answers to these multiple choice questions are entered into the database as and when the user on a host answers. How many such remote hosts can the apache derby server support? Bryan has already answered your question. I certainly can't do better than him. If something in Bryan's answer was not clear, perhaps you could send a followup to his reply? -- dt
Re: newbie question
Brad Berens [EMAIL PROTECTED] writes: I've googled for this but all I can find is create statements so I'm hoping someone can just give me a quick answer. I'm trying to write some db creation scripts. Normally in MSSQL I would use an if exists from the system tables to check whether or not to run a drop before creating the table. I found the sys.systables info about derby, but I can't seem to get the if clause working for that. IF EXISTS (SELECT * FROM sys.systables WHERE tablename='[tablename]') DROP TABLE [tablename]; IF EXISTS is a non-std extension AFAICT. You can do this portably by using DatabaseMetaData.getTables(...). It is a bit more work so you might want to wrap it in a java method. -- dt
Re: FW: Advice on *very* badly performing query
Matt Doran [EMAIL PROTECTED] writes: That did it!!! From 22 minutes down to less than a second. :) I'm kinda embarassed that I didn't figure it myself. Don't be. It is not obvious. I had not idea that derby didn't keep any stats up-to-date without performing that operation explicitly. Ideally it would keep this up-to-date itself. The sys.sysstatistics didn't have any rows in it until I ran the compress table operation. Agreed, but strangely I cannot recall many users actually requesting this. Maybe people just suffer silently? Anyway, thank you for what I would call an exemplary bug-report/question! Even though you use Hibernate you took the time to identfy the actual SQL causing the problem, identified a minimal repro and provided query plans. It really needs to be made more prominent in the documentation. i.e. once your database is loaded with representative data, perform the compress op for optimal performance. Agreed. Any thoughts on where it would be good to mention it? If you want, you can file a Jira issue about this. In the mean-time, we've added a maintenance task to our application to do this operation periodically. -- dt
Re: Secure Delete
Ruben Misrahi [EMAIL PROTECTED] writes: We want to get security-certified and we need to know if deleting a record in Derby actually eliminates or physically overrides that space Yes, AFAIK. and/or if there is a way to do that. And more specifically, if I have a 200 byte field and I override it with a 2 character field. Can some still get the data that was there? Maybe some of it, but not through Derby. We're willing to pay for an answer. Please email to: No need to pay for this answer :) (You can, however, purchase support for Java DB from Sun, see http://developers.sun.com/javadb/support/) In general DBMSs strive to achieve the oposite of what you're seeking. That is; when a transaction doing a delete commits, that record is gone, even if the DBMS crashes before the delete has been reflected on disk. In order to protect your data from accidental deletions you need to perform regular backups. -- dt
Re: Invalid transaction state - held cursor requires same isolation
[EMAIL PROTECTED] (Dag H. Wanvik) writes: Geoff hendrey [EMAIL PROTECTED] writes: Well, it is even harder for us, since we don't see the relevant part of your code... Oh, I think you misunderstood me. You don't need to see my code to answer my question. My question was: ANyone have any ideas what this message might mean? The message I am referring to is: Invalid transaction state - held cursor requires same isolation Your analysis is correct; it means that some result set for which holdability is true is still open when you try to change the isolation level. I think Dyre is trying to say that since we don't have access to your source code is it hard for others to determine if this is a Derby error or an error in your application code. Yes, thanks for clarifying Dag. I should have been a bit more verbose, I guess. -- dt
Re: creating an index on a column that has a UNIQUE constraint just fails silently?
Stacey Brown [EMAIL PROTECTED] writes: http://db.apache.org/derby/docs/10.3/ref/ In the section CREATE INDEX statement it says, If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns. I'm using derby 10.3.1.4 embedded in my application. When the application starts it drops indices and then recreates them. One of those indices was on a column that had a UNIQUE constraint, but the CREATE INDEX statement for that column completes. When I list all the indices though, I only find the index that backs the UNIQUE constraint. Although executing my CREATE INDEX statement didn't throw an exception, it didn't create the index. I'm not trying to argue anything here and I'm not claiming expertise, but when I read you can not create an index on those columns I thought that I would get an exception if I attempted to create an index on a column that has a UNIQUE or PRIMARY KEY constraint. I tend to agree, but I'm not an expert. Unless someone tells you otherwise in the next couple of days, I think you should file a jira about this. See http://db.apache.org/derby/DerbyBugGuidelines.html If you could include a minimal test program that exhibits the problem it will be much appreciated. My question is whether the behavior is intentional or not. Should I have gotten an exception when I executed a CREATE INDEX statement on a column that already had a UNIQUE constraint on it? Seems unreasonable to silently ignore the user's request. At least you should get a warning IMHO. -- dt
Re: Reducing memory usage
Charlie Hubbard [EMAIL PROTECTED] writes: Hi, I have a database that I have been storing binary data in a Blob column. I've decided that it was a bad idea and I'd like to remove it from the DB and store it on the filesystem. I've written a small program to extract the contents from the DB and save it to the file system. It works except it runs out of memory using the default 64 MB. While I could increase the -Xmx setting it seems superflous. I've profiled the application and majority of the memory is being occupied by derby. I see lots of classes like org.apache.derby.impl.services.cache.CachedItem and org.apache.derby.impl.store.raw.data.StoredPage holding 32KB byte buffers. Is there a way to change derby so that it would drop those cached buffers? The size of the page cache is controlled by the derby.storage.PageCacheSize property. Its default value is 1000, so you could try to reduce it. Normally you need to adjust your -Xmx setting so that you have enough heap for the page cache + some overhead for the rest of Derby... Reducing it could affect performance but that depends on the nature of your application (how much it benefits from caching...) Or a way to turn off caching for blobs? Don't think so Or is there a way to optimize the memory a little more so that it would run in a smaller footprint? Well, the page cache is the part of Derby that normally uses the most memory, so it's probably a good place to start. This program will have to run on DB that could be in the GB size. I just need to extract the contents and save it out to disk. If you only read each record once before writing it to disk, you probably won't benfit much from caching anyway, so reducing it is probably a good idea... I think there is a lower limt for the page cache size, but I don't remember what it is. The tuning guide has the details. -- dt
Re: exponential increase in insert time
sudhakar [EMAIL PROTECTED] writes: I'm using embedded derby in a Java SE application with OpenJPA. The applications parses a text file and creates JPA entity objects which it then turns around and persists in the embedded derby database using JPA. A general observation: The more fancy frameworks you put between your code and the jdbc/SQL calls, the harder it will be to identify performance problems. It's the price you pay for ease of development. There is no free lunch I'm afraid. It starts of with inserting about 15 records per second and exponentially drops down to about one record every 25 seconds by the 400th record. I haven't tuned any of the derby parameters. You should definitely swing by the Derby web-site and make sure you have done all the simple stuff. What you're describing sounds unreasonable to me, but there is really no way to tell unless you say how large your records are, and what the database schema looks like. Before I started getting into that I wanted to run this by here to find out if this is the out-of-box expected performance. It doesn't look right. The insert time per record increases exponentially. Can someone confirm that if something is awfully wrong here and how I can go about troubleshooting this. The text file is about 9 records. So this exponential performance decrease would be completely unacceptable to the end-user. Sounds like something is wrong, yes. One thing you could try is to turn on logging of SQL statments (derby.language.logStatementText see the Tuning Guide), to see what actually happens during the insert. -- dt
Re: Invalid transaction state - held cursor requires same isolation level
Geoff hendrey [EMAIL PROTECTED] writes: Here are 3 lines from my code: Connection c = ConnectionManager.getConnection(req); c.setAutoCommit(false); c.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); The *second* time this block executes, I get this exception from the attempt to setTransactionIsolation: java.sql.SQLException: Invalid transaction state - held cursor requires same isolation level at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source) at org.apache.derby.client.am.Connection.setTransactionIsolation(Unknown Source) ANyone have any ideas what this message might mean? My guess is that a result set is open and a cursor in that result set is somehow not getting closed after the first pass. But I'm hard pressed to see where in my code this could be happening since I close all my connections in finally blocks. Well, it is even harder for us, since we don't see the relevant part of your code... -- dt
Re: Embedded Derby
Daniel Noz [EMAIL PROTECTED] writes: Hello derby-users, I develop a richt client application with netbeans using derby-db and toplink as JPA implementation. If i use the embedded driver (see property name=toplink.jdbc.driver value=org.apache.derby.jdbc.EmbeddedDriver/) it works great to persist Entity-Objects. However i cant see anything within the database, i am using. I can connect, hower i dont see any created tables or data within...? Is this a feature with the embedded driver? I know that my Entity Objects are within the database...if i use the entitymanager to find the persited object..it works great... By the way: I dont use the JavaEE lookup. I create the necessary EntityManager instance like this: EntityManagerFactory factory = Persistence.createEntityManagerFactory(BBTrainerAppPU); EntityManager em = factory.createEntityManager(); Can anyone help me and tell me if this is a feature or how to find the created table or data within? Could it be that you have two different databases with the same name in different locations? A similar situation was discussed here a while back. See http://www.nabble.com/Hibernate%3A-table-view-does-not-exist-tf4521125.html HTH -- dt
[comp.lang.java.databases] Derby eclipse install
I just realized that I had snipped the actual question in the reply I sent to comp.lang.java.databases. Here is the full post. Sorry about that. ---BeginMessage--- The install instructions for Derby 10.3.1.4 in Eclipse 3.3 implies that it is as simple as having the Eclipse\plugins directory containing these three directories: org.apache.derby.core_10.3.1, org.apache.derby.ui_1.1.1, and org.apache.derby.plugin.doc_1.1.1, as obtained from extracting the zip files. And afterwards, the Apache Derby menu item should appear when the project folder is right-clicked. In my case, that does not happen. There must be something small which is understood by experienced users, who write the instructions, that is not so obvious for beginners. Can someone point me in the right direction? Thanks. ---End Message--- -- dt However, experience shows that for many people and many applications a dose of paranoia is reasonable - Bjarne Stroustrup
Re: Maximum connections to embedded database
Mark P Ashworth [EMAIL PROTECTED] writes: Good Day, I would like to know the maximum number of connections that a Embedded Derby database can maintain? This is a bit tricky to answer, since the answer can be either unlimited or one, depending on what you actually mean by connection. Embedded Derby can return an unlimited (limited by system resources of course) java.sql.Connection objects that can access the database simultaneously, but all these connections need to exist within the same jvm. A database can only be accessed by a single jvm at a time. So if, by connection you mean another jvm accessing a database that's already opened, the answer is only one such connection is allowed. Note that this means that you cannot use ij to inspect your database while your embedded application is running. There are two ways to get around this: A) Shut down the database from the embedded application before accessing the database in ij, or B) Start your embedded application as an embedded server (see the documentation). Your app will continue to access the database in embedded mode, but other clients (like ij) can connect to your app as if it was a server (it needs to use the ClientDriver and a network URL to do this). HTH -- dt
Re: DevGuide: glitch in generation?
caston first [EMAIL PROTECTED] writes: I am reading the HTML PAGES dev guide, and every time there is a reference to an actual code item in-line with text (in Courier font), it is an obfuscated (meaningless) word, viz.: This base text (as a result of a copy operation): The standard way to obtain a Connection object is to call the method DriverManager.getConnection, which takes a String appears in Safari (Mac OS X, 10.4.10) as in the attached image. In fact, I didn't know the text would paste correctly when I started this note! Hmmm... That does indeed look strange. As far as I know it looks fine in Firefox under both Solaris and Windows, as well as in IE. If the html source looks OK, doesn't that point to a Safari problem? -- dt
Re: DevGuide: glitch in generation?
Thomas Kellerer [EMAIL PROTECTED] writes: [snip] But I have noticed that the reference to the css in the header is incorrect: link href=/export/home/rh161140/derby/docs/10.3/out/devguidetemp/commonltr.css type=text/css rel=stylesheet / this is taken from the 10.3 distribution archive. I'm no DITA or HTML guru, but that doesn't seem right. I'm pretty sure that's a left-over from the environment where Rick (Hillegas) built the documentation for the 10.3 release... Hmm, I thought Myrna Van Lunteren was the release manager for the 10.3 release... strange. I'll let them comment when they see this. As no css files are part of the distribution, the reference does not make sense (a relative path would make sense if the css files were included in the archive). But maybe on your computere the referenced .css file does indeed exist and defines a font which causes the garbled output. Seems a bit unlikely, but you never know I guess... :) -- dt
Re: Hibernate: table/view does not exist
Mathias Conradt [EMAIL PROTECTED] writes: I try to exchange postgres with derby/javadb 10.3.1.4 and created a derby database using ij (I'm new to derby). I use CONNECT 'jdbc:derby:realty;'; for the connection in ij and after the creation I can do selects on the table using ij. The username you used (in ij) will determine which schema the table ends up in. If you didn't specify a username it should end up in APP. I don't know anything about Hibernate, but when you are in ij you could run the following query to figure out where your table actually has ended up: select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s where t.tablename = 'USERDATA' and t.schemaid = s.schemaid; This should also tell you if you have multiple copies of USERDATA in different schemas. You don't have to rely on the default schema for the user you connect as. You can create your own schema (CREATE SCHEMA ...) and explicitly set that as you default schema (SET SCHEMA ...) -- dt
Re: Unraveling FK errors
Williamson, Nick [EMAIL PROTECTED] writes: Hi all, If you get errors like these when you're creating a schema... ERROR 42915: Foreign Key 'PIN_FK1' is invalid because 'the delete rule of foreign key can not be CASCADE. (The relationship would cause another table to be delete-connected to the same table through multiple paths with different delete rules or with delete rule equal to SET NULL.) '. ERROR 42915: Foreign Key 'VC_FK3' is invalid because 'the delete rule of foreign key must be CASCADE. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '. ...which table is it referring to when it says the table and the same table? The table to which the constraint is being added, or the table that is referenced by the constraint? It's really hard - for me at least - to work all of this out because I have c.500 tables in the schema and the relationships are quite complex; a table often has several FK constraints (and each of the referenced tables can have several FK constraints) and so it isn't immediately obvious which tables are causing the problem. Is there a way of getting Derby to provide more detail on exactly what the problem is? Normally the problematic query is echoed before the error message, but I assume that didn't help in this case. I don't think there is a way of making the error messages more verbose. It might be possible to make improvements in the cases you mention. It's not hard to add parameters to the error message that would identify the tables, but I don't know if this info is easily available at the point where the exception is thrown. You could log an enhancement request in Jira for this. -- dt
Re: Wanted: Example using SQLXML
sieg [EMAIL PROTECTED] writes: See below. Incidentally, in a another posting, someone explained to me that downloading and installing derby was redundant with installing Java 1.6. Not quite. Derby aka JavaDB is _bundled_ with the JDK (not the JRE). And you still need to include the Derby jars in your classpath, it will not happen automatically. Older versions of Java 6 have the derby jars in db/lib: $ \ls /usr/local/java/jdk1.6.0_00/db/lib/ derby.jar derbyLocale_fr.jar derbyLocale_zh_CN.jar derby.war derbyLocale_it.jar derbyLocale_zh_TW.jar derbyclient.jarderbyLocale_ja_JP.jar derbynet.jar derbyLocale_de_DE.jar derbyLocale_ko_KR.jar derbyrun.jar derbyLocale_es.jar derbyLocale_pt_BR.jar derbytools.jar Unfortunately, due to a glitch, the derby jars are missing from Java 6 update 2 :( This will be fixed in the next update, but until then you need to download derby from apache... -- dt
Re: Number of Concurrent Connections
Anestis Sismanidis [EMAIL PROTECTED] writes: Hello How many concurrent connections can a derby database handle (in network server mode)? Is there a limitation? I'm currently running the NetworkServer with 250+ threads on a 32 core machine without problems. Not a production system, but... Actually I need 100 concurrent connections. What do you think, is it possible? Absolutely. If not, what would you suggest? -- dt
Re: Number of Concurrent Connections
Emir Causevic [EMAIL PROTECTED] writes: If not, what would you suggest? Buy Oracle. :) Nope. If I had that kind of money I would buy a sports car :) -- dt
Re: Is the derby project still active?
Daniel Fitzgerald [EMAIL PROTECTED] writes: It has been quite a while since any new builds or enhancements have been done on this software. Excuse me. Since when is six months quite a while? Even for open source projects? Is this project dead or in stasis? The 892 unread emails in my derby-dev folder says no... It has it's niche i.e. small footprint and can be packaged with your jars ( albeit cumbersome). Feel free to suggest a better way of doing it :) I read a white-paper a while ago about performance but the problems didn't seem insurmountable and indeed, it alluded to possible solutions. Anyway, I downloaded it and tried it out; it seemed fine as an application data repository (in SQL) so I hope the project doesn't die on the vine. Don't think you need to worry. It doesn't seem like it even got a chance to get out into the public at large for a proper trial. Just my thoughts. Thank you. Eh... maybe you have not heard that JavaDB (aka Derby) is bundled with JDK 1.6? -- dt
Re: Multiple System Directories
Anestis Sismanidis [EMAIL PROTECTED] writes: What I need is: Two totally different derby databases in totally different directories (one directory in the cdrom and the other in the user home directory). I need all that in the same JVM. The problem is that there is only one derby.system.home property per JVM. Is there anything I can do? The database doesn't have to reside in derby.system.home. You can specify the absolute path to the database in the connection url. E.g. jdbc:derby:/cdrom/mydb Note that a running Derby needs a temp directory and somewhere to log errors. This cannot be on read-only media, so you need to specify alternate locations. This is described in Creating and preparing the database for read-only use in the dev guide http://db.apache.org/derby/docs/dev/devguide/ HTH -- dt
Re: How to shutdown with authentication
Calvin (Yu-Hui) Liu [EMAIL PROTECTED] writes: Hi, Leonard, I'm new to derby so maybe this is a stupid question for you: why do you want to use derby, especially use it as a network service? In another word, what's the use case of you? It's reasonable for me to use it as embedded db. But as long as we have more mature db like mysql, I can't understand that. Could you please give me some clue? I can't answer for Leonard, obviously. But it could be beacuse: A) He wants to embed the db in a java app, but also let remote clients connect. B) It may because he wants to be sure that the DB runs anywhere Java runs. C) Maybe he needs to deploy his DB server on many different machines and likes that fact that he only needs to install a couple of jar files D) Maybe he cannot use MySQL's dual GPL/commercial licence for some reason. -- dt
Re: What is the UPDATE syntax for using two tables?
Luan O'Carroll [EMAIL PROTECTED] writes: I have a SQL Server query that I am trying to port to Derby but I can't find the right syntax. Is there any documentation? The SQLServer query is: UPDATE TEMP_RATES SET Level1=RATES.Level1 FROM TEMP_RATES INNER JOIN RATES ON TEMP_RATES .CODENO= RATES .CODENO WHERE (RATES.UserID=306) AND ( TEMP_RATES.BusinessID=1) Any ideas. I tried the SQL for ANSI,Informix,Oracle but none worked What is the error message you are seeing? Which part of the query is Derby choking on? -- dt
Re: question on Working with JavaDB Activities
Joe Annoni [EMAIL PROTECTED] writes: Couple of novice questions here (your help is much appreciated!): 1) While going through the Working with JavaDB pdf in Step 2 of Activity 3 (ie, page 14), I'm getting a command line error of 'javac' is not recognized as an internal or external command I presume there is a problem with my JAVA_HOME path, but I've changed it from the java/j2re1.4.2_03 directory (which has worked for everything else) to java/jdk1.6.0 (where the 'javac.exe' file exists) and still nothing. Is there somewhere else where the 'javac' command should be picked up? I don't know Windows very well, so there could be some Windows-specific issues here, but in general you need to modify PATH (or equivalent) for your command shell in order for it to find an executable. javac.exe is no different from any other program in this regard. 2) Separately, I compiled the WwdEmbedded.java file in an IDE (JCreator) and when I trying executing it I get ClassNotFoundException: org.apache.derby.jdbc.EmbeddedDriver. I understand this may be a CLASSPATH problem, however when I check %classpath% it shows it pointing to the 'derby.jar' and 'derbytools.jar' files. Any other common problems or suggestions? Again, I don't know JCreator, but what little experience I have with IDE's suggest that they frequently require you to add external libs that you want to use (such as derby.jar) to your project. Usually through some add-library or add-to-classpath dialog... -- dt
Re: Derby process taking up more than 20% of cpu for more than 5hrs with only one user connected.
Sachin Chitale [EMAIL PROTECTED] writes: Hi, To give you a background of what I was testing: We have process that uses database as our guranteed message store. To give a stress test to Derby, this program was run to store about 70k+ messages. Then a subscriber was started to fetch the messages. But it seems that the database has stopped responding to the requests. Initially I was able to get message count using: ij select count(*) from M_20070129MESSAGES; 1 --- 54598 After ~15 minutes. 1 row selected ij select count(*) from M_20070129MESSAGES; ERROR 40XL1: A lock could not be obtained within the time requested ij select count(*) from M_20070129MESSAGES; ERROR 40XL1: A lock could not be obtained within the time requested ij select count(*) from M_20070129MESSAGES; ERROR 40XL1: A lock could not be obtained within the time requested ij select * from M_20070129MESSAGES where MESSAGEID='1746' ; MESSAGEID |MES - ERROR 40XL1: A lock could not be obtained within the time requested When I did prstat on the running derby process it gave the following result: --- PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWPID 27823 schitale 76 1.0 - - - - 21 - 73 127 767 0 java/28 27823 schitale 0.5 0.0 - - - - 62 -2 38 40 0 java/2 27823 schitale 0.5 0.0 - - - - 62 -2 36 38 0 java/4 27823 schitale 0.5 0.0 - - - - 62 -2 37 39 0 java/3 27823 schitale 0.5 0.0 - - - - 62 -2 36 38 0 java/5 27823 schitale 0.2 0.0 - - - - 62 -0 0 0 0 java/11 27823 schitale 0.2 0.0 - - - - 62 -0 0 0 0 java/12 27823 schitale 0.1 0.0 - - - - 62 -1 0 5 0 java/6 27823 schitale 0.1 0.0 - - - - 62 -1 0 8 0 java/27 27823 schitale 0.0 0.0 - - - - 63 -4 0 12 0 java/8 27823 schitale 0.0 0.0 - - - - 63 -5 0 12 0 java/7 27823 schitale 0.0 0.0 - - - - 63 -0 0 0 0 java/1 27823 schitale 0.0 0.0 - - - - 63 -0 0 0 0 java/10 27823 schitale 0.0 0.0 - - - - 63 -0 0 0 0 java/15 27823 schitale 0.0 0.0 - - - - 100 -0 0 0 0 java/14 NLWP USERNAME SIZE RSS MEMORY TIME CPU 20 schitale 6610M 5433M34% 1:59.03 23% Total: 1 processes, 20 lwps, load averages: 1.12, 1.12, 1.21 Thread no 28 was taking us most of the CPU time, and 23% was the total CPU cycle share was taken up by derby process. To find out which java thread was taking time I did a jstack of the process which revealed the following: jstack 27823 -- Thread [EMAIL PROTECTED]: (state = IN_JAVA) - java.util.Hashtable.get(java.lang.Object) @bci=45, line=339 (Compiled frame; information may be imprecise) - org.apache.derby.impl.store.raw.data.FileContainer.getUserPage (org.apache.derby.impl.store.raw.data.BaseContainerHandle, long, boolean, boolean) @bci=48 ( Compiled frame) - org.apache.derby.impl.store.access.btree.BTreeScan.reposition (org.apache.derby.impl.store.access.btree.BTreeRowPosition, boolean) @bci=114 (Compiled frame ) - org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows (org.apache.derby.impl.store.access.btree.BTreeRowPosition, org.apache.derby.iapi.types .DataValueDescriptor[][], org.apache.derby.iapi.types.RowLocation[], org.apache.derby.iapi.store.access.BackingStoreHashtable, long, int[]) @bci=31 (Compiled frame) - org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext (org.apache.derby.iapi.types.DataValueDescriptor[]) @bci=29 (Compiled frame) - org.apache.derby.iapi.store.access.DiskHashtable.get(java.lang.Object) @bci=4 (Interpreted frame) - org.apache.derby.iapi.store.access.BackingStoreHashtable.get (java.lang.Object) @bci=27 (Interpreted frame) - org.apache.derby.impl.sql.execute.HashScanResultSet.getNextRowCore() @bci=64 (Interpreted frame) - org.apache.derby.impl.sql.execute.NestedLoopLeftOuterJoinResultSet.getNextRowCo I'm guessing that you are executing a join that is so large that it spills to disk. This could take a long time and so hold locks for a long time. In addition to trying tableLocks (as was already suggested), you could try to increase the heapSize for your jvm (the amount of memory used in joins before spilling to disk is a percentage of the heap size, I think). You could also try to increase the pageCache size. -- dt
Re: Large multi-record insert performance
Sedillo, Derek (Mission Systems) [EMAIL PROTECTED] writes:
Hello Daniel,
Thank you for sharing your finding. It is interesting to see that using
executeBatch was actually slower than executeUpdate.
Of these three approaches I find it hard to picture multiple inserts in
a single transaction using executeUpdate. Would you mind describing how
to place multiple inserts into one transaction 'without' using batch
processing? I am still a bit new to this paradigm after coming from the
Oracle/C++ world.
I haven't followed this thread closely, but is there a reason why you
cannot do:
PreparedStatement ps =
conn.prepareStatement(insert into T values (?, ?, ?));
conn.setAutoCommit(false);
for (int i = 0; i 1000; ++i) {
ps.setX(1, whatever);
ps.setX(2, whatever);
ps.setX(3, whatever);
ps.executeUpdate();
}
conn.commit();
?
--
dt
Re: Large multi-record insert performance
Sedillo, Derek (Mission Systems) [EMAIL PROTECTED] writes: Dyre, The goal is to find the most efficient/optimized way to insert large amounts of data into Derby. For example in working as an Oracle DBA I have discovered that I can bulk load data from ProC using an array of C Structures in one insert statement like this: INSERT INTO SSJ VALUES (:tmp_ssj_data); // Where tmp_ssj_data is an array (100s or 1000s) of structured records This approach greatly enhances performance for large data inserts which we perform regularly. My original question is how can I do something similar with Derby. Not to my knowledge, no. I believe you can only bulk load from a file (which I think others have already described). While I realize there are 'basic' constructs for performing a task, they are not 'normally' optimal. For example performing 1000 separate transactions is less efficient than one. Not sure I understand what you mean here. You control how many inserts you want to do between each commit... All in one transaction using executeUpdate(): 100 tags added in 274ms 100 tags removed in 70ms 1000 tags added in 299ms 1000 tags removed in 250ms 1 tags added in 1605ms 1 tags removed in 1500ms 10 tags added in 14441ms 10 tags removed in 19721ms 100.000 inserts in 15 sec. That's too slow? Then I don't have any answers I'm afraid... -- dt
Re: Does derby support bitwise operators in SQL?
Imran Hussain [EMAIL PROTECTED] writes: Dan, Thanks for the response. I wonder if there is any plan to add support for this in the future? Imran. Dan Karp wrote: I'd like to know if Derby supports bitwise operators in SQL statements. Nope. Wish it did, but nope. select * from MyTable where (status 5) = 5 From my reading of the SQL 2003 standard '(status 5)' isn't a valid 'numeric value expression'. But I'm not well versed in standardese so I could be wrong. -- dt
Re: Derby fully sql-2003 compliant?
Christian Lang [EMAIL PROTECTED] writes:
Hi,
I'm trying to understand which SQL language subset Derby is using. I
installed version 10.2.2.0 and tried to compile and execute the following
statement:
SELECT MIN(A) OVER (PARTITION BY B) FROM C;
(which seems a legal SQL-2003 statement). However, the Derby sql compiler
complains about the '(' after the 'OVER' keyword.
I looked into the sqlgrammar.jj file and could not find a partition or
over keyword at all. Is this not supported (yet)?
Or am I doing something wrong?
I don't find anything about this on
http://wiki.apache.org/db-derby/SQLvsDerbyFeatures
so either it is completely over-looked, or it isn't required.
--
dt
Re: rows locking in derby
Cristian Dudila [EMAIL PROTECTED] writes: Hi, We use derby for an application and we need to lock some rows. The problem is that we would like to know if this rows are already locked. Using TRANSACTION_REPEATABLE_READ we can lock this rows ( using SELECT )but we don't know if this rows was already locked. Using SELECT FOR UPDATE derby returns an exception that tell us that the row is locked, but unfortunately ( for us ) we can lock only one row using SELECT FOR UPDATE. I think it would be better if you could describe what it is you're trying to achieve. Is there a possibility to lock multiple rows, using one connection and to receive an exception or message ( from derby ) if this rows are locked by somebody else before ? Maybe I don't understand what you're trying to do, but in general it is not a good idea to use the database to communicate between threads. The database tries its best to hide the fact that other threads (connections) are accessing the data. -- dt
Re: rows locking in derby
Cristian Dudila [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: Cristian Dudila [EMAIL PROTECTED] writes: Hi, We use derby for an application and we need to lock some rows. The problem is that we would like to know if this rows are already locked. Using TRANSACTION_REPEATABLE_READ we can lock this rows ( using SELECT )but we don't know if this rows was already locked. Using SELECT FOR UPDATE derby returns an exception that tell us that the row is locked, but unfortunately ( for us ) we can lock only one row using SELECT FOR UPDATE. I think it would be better if you could describe what it is you're trying to achieve. I want to warn the user if a row is locked in derby database by another user. You will get an exception if you try to access that row and your transaction cannot get the lock within some timeout (which you can specify, I think). Note that the lock isn't kept by another user, but by another transaction. So if you limit the duration of all transactions accessing this particular row, there should be no need for a warning... Is there a possibility to lock multiple rows, using one connection and to receive an exception or message ( from derby ) if this rows are locked by somebody else before ? Maybe I don't understand what you're trying to do, but in general it is not a good idea to use the database to communicate between threads. The database tries its best to hide the fact that other threads (connections) are accessing the data. With TRANSACTION_REPEATABLE_READ using a SELECT we will have a shared lock in derby, so we can read data but not to change it( in case of update, from another database connection, we receive an exception that the lock cannot be obtained ). Is there a possibility to check in derby if a row is locked( except to try a dummy UPDATE on that row ) ? I don't think that will work. If another transaction has locked the row, your update will just wait for the lock to be released. It will only give an exception if the lock request times out, I think. You can dump all locks from a system table (check the manuals for the details). But even if this shows that the row isn't locked there is no guarantee that your transaction will get the lock, (another transaction may beat you to it). -- dt
Re: Performances: throughput parallelism
Mariachiara [EMAIL PROTECTED] writes: Hi! I'm still researching about the db performaces, in particoular about: - max throughput (max numbero of interactions per second) - parallelism degree ( max number of client that can be served side by side) Such numbers are meaningless unless you specify the environment (hardware, disk configuration, file system, OS, jvm, db configuration), and the type of load on the db. Can you hel me? What exactly are you trying to do? If you want to compare databases to see which is best or fastest you first need to define the type of load that is important to you, AND that the load will actually measure the capacity of the databases, and not the network or the disk or something else entirely. -- dt
Re: Using Derby as a binary store
Kasper Nielsen [EMAIL PROTECTED] writes: David Van Couvering wrote: To be honest, this looks more like a job for BDB than for Derby. I would love to see the Derby store API made public at some point, but currently it's not public and I suspect it may be difficult to work with. Are there reasons BDB/Java won't work for you? Not besides the license:) Its for an BSD licensed project, so BDB is pretty much out of the picture. According to David's recent blog http://weblogs.java.net/blog/davidvc/archive/2006/11/oracle_benchmar_1.html The BDB license is clear: if your product that uses BDB is closed-source, or you want indemnification, then you need to us pony up $$$ for the commercial license. If your product is open source, you can use the BDB open source license, which is a variant of a BSD license. So if your project is BSD also, shouldn't you be able to use BDB in it? -- dt
Re: problem when using several connections against Embedded Derby
legolas wood [EMAIL PROTECTED] writes: Hi I have a problem when i use several connections for an embedded derby database. problem is that simply it say that there is no such table in the database when i use more than one connection. does any one has the same problem ? Use ij to check all relevant schemas (APP and all usernames you use when connecting) to see exactly where your tables end up. Then make sure all your connections look for tables in the same schema. -- dt
Re: LEFT JOIN with a subquery not working in Derby?
Halit M. Maner [EMAIL PROTECTED] writes: The statement below has been working fine in Cloudscape 5.1 but it is not working in Derby (we are using 10.1.2.4). ij SELECT * FROM admapphdr LEFT JOIN admappdtl ON (aah_id=aad_aah_id AND aad_seq_num = (SELECT MAX(a2.aad_seq_num) FROM admappdtl a2)); It is throwing: ERROR 42972: An ON clause associated with a JOIN operator is not valid. It works fine if we remove the SELECT MAX (but we need it!)... admapphdr is a header table with an INTEGER id. admappdtl is admapphdr's detail table with the same id plus a sequence number for the various detail rows... Is this a limitation of Derby or are we overlooking something? Sorry about the late reply. You probably have found a workaround by now. Couldn't you just perform the 'select MAX' first, and insert the value in your join condition? (A pain, I know...) In the general case where the sub-query returns multiple rows, you could create a view of the sub-query, and then join the two tables with this view in a three-way join. -- dt However, experience shows that for many people and many applications a dose of paranoia is reasonable - Bjarne Stroustrup
