Zorro | 28 Nov 2012 20:25
Picon

NullPointerException in Derby 10.9.1.0

Dear All,

When doing in ij a bulk Insert into a table of my Derby database I do 
get a NullPointerException.

I am using the Derby Network Server on Fedora 14 using the OpenJDK 
Runtime Environment (IcedTea6 1.9.10) version "1.6.0_20".

I did the following:
ij> Connect 'jdbc:derby://localhost:1527/derby.db;user=...;password=...' ;
ij> AutoCommit Off ;
ij> Set Schema ExchRates ;

ij> Describe SecurityDaySummary ;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
SECURITYID |CHAR |NULL|NULL|15 |NULL |30 |NO
TRADINGDATE |DATE |0 |10 |10 |NULL |NULL |NO
VOLUME |INTEGER |0 |10 |10 |NULL |NULL |NO
CLOSINGPRICE |DECIMAL |4 |10 |12 |NULL |NULL |NO
LOWPRICE |DECIMAL |4 |10 |12 |0.0 |NULL |NO
HIGHPRICE |DECIMAL |4 |10 |12 |0.0 |NULL |NO

ij> Select 'NL0010273215', TradingDate,
 > INT(Volume * 0.97048368 + 0.5),
 > DOUBLE(ClosingPrice * 1.03139545 + 0.00005),
 > DOUBLE(LowPrice * 1.03139545 + 0.00005),
 > DOUBLE(HighPrice * 1.03139545 + 0.00005)
 > From SecurityDaySummary where SecurityID = 'NL0006034001'
 > Order By SecurityID, TradingDate ;
(Continue reading)

carlspring | 30 Nov 2012 12:02
Picon

Introducing the derby-maven-plugin


Hi,

For all you guys working with Maven, I would like to let you know that a
while ago I knocked up a Maven plugin which can start Derby for you during
the build (in the same VM) and be used by integration tests (which are not
forked. of course).

The plugin is under an Apache 2.0 license and is available in:
-  Maven
Central
<http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22org.carlspring.maven%22%20AND%20a%3A%22derby-maven-plugin%22>

-  GitHub <https://github.com/carlspring/derby-maven-plugin>  

For details on how to use it, you can check the  USAGE
<https://github.com/carlspring/derby-maven-plugin/blob/master/USAGE>   file.

Cheers,

Martin Todorov

--
View this message in context: http://apache-database.10148.n7.nabble.com/Introducing-the-derby-maven-plugin-tp125564.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Thomas Hill | 27 Nov 2012 15:29
Picon
Favicon

best practice - SQL Routines - return value handling

have written a couple of SQL Routines for Derby and am recently looking into 
possibly re-using some of the code on another DBMS system also supporting Java 
(PostgreSQL pl/Java). pl/Java for me is more a playground and subject of 
curiosity then a real intention to use it productively at this stage. Need to 
say the promise of Java in the data base is - from a portability point of 
view - limited as my observations so far show implementations are back end 
specific in many cases. Maybe Apache Derby and Oracle would be more 
compatible, but Derby and PostgreSQL are not too often. However when there are 
multiple implementation approaches possible, one might be favoured over the 
other when portability is considered a requirement.
Specific question: 
If there is a need for returning multiple out parameters from a routine (just 
one row, not a set of rows), one might choose to implement a procedure in 
Derby like this:
CREATE PROCEDURE xy(IN CLIENTID integer, OUT LASTNAME varchar(30), OUT 
FIRSTNAME varchar(30)
or
CREATE PROCEDURE xy(IN CLIENTID integer) DYNAMIC RESULT SET 1

Is one of these approaches to be favoured over the other? What is the best 
pratice here? Any one to be favoured over the other when having Oracle 
portability in mind?

Thanks a lot for sharing your experience and advise.

Kind regards
Thomas

Stefan R. | 26 Nov 2012 20:31
Picon

Backup using SYSCS_UTIL.SYSCS_BACKUP_DATABASE with required authentication

Hi,

in one of our web apps we activated derbys builtin user authentication for the databases. We are using Derby 10.8.2.2

The app is triggering a backup task, which executes SYSCS_UTIL.SYSCS_BACKUP_DATABASE over a JDBC connection. This throws an exception saying: the connection could not be authorized.

The following statements have been used to activate authentication:

--------------------
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider', 'BUILTIN');

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.DBUSER1', 'password);
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode', 'readOnlyAccess');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers', 'DBUSER1);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','false');
--------------------

So, DBUSER1 should have full access. 

Are there any restrictions to SYSCS_UTIL functions for fullAccessUsers? How to authorize these?

Thanks for your input,
Stefan

John English | 21 Nov 2012 15:14
Picon

Pivoting tables?

I have a situation where I have a table listing users and products and 
associated values:

   USER   PRODUCT   VALUE
   abc    xyz       3
   def    ghi       5
   def    xyz       7

and I want to pivot this to display it with a column for each product 
like so:

   USER   ghi  xyz
   abc         3
   def    5    7

This means that the columns I have depend on the product list, which 
changes pretty regularly (at least at certain times) and they also 
depend on which department you're visiting (each has a different product 
list). At the moment I use a temporary table:

     Object lock = null;
     synchronized (state.tempTables) { // "state" is from the HttpSession
       if (state.tempTables.get("products") == null) {
         state.tempTables.put("products",new Object());
       }
       lock = state.tempTables.get("products");
     }
     synchronized (lock) {
       // start transaction
       // drop the temporary table if it exists
       // create the temporary table
       // select rows from the real table
       while (res.next()) {
         // insert into temporary table
       }
       // commit transaction
       // display the temporary table
     }

This is ugly and slow, but I've been unable to come up with a better 
way. The table is dropped at the start rather than at the end because 
the user might choose to download it as CSV, so it's left in existence 
after it's displayed in case it's needed for this purpose.

I thought about using a table function, but again the column list is 
fixed when the function is defined.

Does anyone have any ideas what else I could try? Or is there anything 
in the pipeline for a future version that might be relevant?

TIA,
--

-- 
John English

John English | 18 Nov 2012 18:14
Picon

SQL: JOIN syntax

Can anyone tell me what the correct syntax is to make this join work?

   SELECT * from a,b LEFT JOIN c ON a.foo=c.foo AND b.bar=c.bar;

That is, I want the crossproduct of a and b, with columns from c when 
they correspond to rows in BOTH a and b, and nulls if not. Everything 
I've tried gives me something like

   "Column 'A.FOO' is not in any table in the FROM list"

I know I'm probably being stupid, but please help anyway!

TIA,
--

-- 
John English

Ecker Sandor | 9 Nov 2012 12:54
Picon
Favicon

derby (dead)lock exception

Hi,

I'm getting the following exception.

    <-: 'deleteByIds' throws '[DatabaseException <at> 74c9e3[
      Internal Exception: java.sql.SQLTransactionRollbackException: DERBY SQL error: SQLCODE: -1,
SQLSTATE: 40001, SQLERRMC: Lock : ROW, TRIP_TIMETABLE, (1,20)
        Waiting XID : {5144, U} , TTM, DELETE FROM TRIP_TIMETABLE WHERE EXISTS(SELECT ID FROM TRIP WHERE (ID IN (?))
AND ID = TRIP_TIMETABLE.trips_ID)
        Granted XID : {5150, X} 
      Lock : ROW, TRIP_TIMETABLE, (1,19)
        Waiting XID : {5150, U} , TTM, DELETE FROM TRIP_TIMETABLE WHERE EXISTS(SELECT ID FROM TRIP WHERE (ID IN (?))
AND ID = TRIP_TIMETABLE.trips_ID)
        Granted XID : {5144, X} 
      ^T5144^T40001
      Error Code: -1

Does this mean I had a deadlock? I guess there are two transactions 5144, 5150, they execute the same query.
What does U and X mean after the trans. Ids?

The query is:

DELETE FROM TRIP_TIMETABLE 
WHERE EXISTS(
          SELECT ID FROM TRIP WHERE (ID IN (?)) AND ID = TRIP_TIMETABLE.trips_ID
)

What can be wrong with this query?

Thanks,
Regards,
Sandor

Bhavesh Thanki | 6 Nov 2012 19:47
Picon

question

Hi

I have a question to ask i want to configure Derby db with my Squirrel sql client. I have added the driver jar but it is asking me for a password in the sql connection window.#
 what do i enter as i cannot leave it blank.

--
Kind Regards,
Bhavesh.N.Thanki
Tel:07572879181
Mailto: bhavesh.thanki <at> gmail.com

Sperry, Maurice | 6 Nov 2012 16:32

Feedback derby_comm.html

To whom it may concern:

 

I recently installed Apache Derby, and have gone through the tutorial exercise of connecting, creating a database, creating tables with the DDLs provided, inserting data into the tables, etc.; as well as reviewing the documentation.  The documentation seems very well written; however, I do find explicit documentation on DDL syntax.  Where would I find that?  Thanks.

 

Maurie Sperry

Applications Architecture, Design and Development

EMC Consulting

maurice.sperry-mb1K0bWo544@public.gmane.org

Office:  260 444-2700  Ext 208

 

Tom Deering | 5 Nov 2012 16:19
Picon

Custom Bulk Import from CSV

I have a table that has the following columns:

INTEGER A, INTEGER B, DATE C, FLOAT D, FLOAT E, FLOAT F, FLOAT G, INTEGER H, FLOAT I
A is my primary, auto-generated key
B is a foreign key referencing the primary key of another table.

I have a csv file containing many rows of data for columns C - I that I would like to import with "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA". All rows of the csv file correspond to the same value for B. If be is left unspecified, then I get:

Column 'B'  cannot accept a NULL value. 

QUESTION: How can I bulk-import the data for columns C - I while specifying the use of the same value for column B?

Thanks in advance.

-Tom-

Hong Ji | 4 Nov 2012 14:16
Picon

Locking Error When Dropping a Table

When dropping a table, I keep getting the exception 40XL1 (A lock could not be obtained within the time requested). The Derby build is 10.9.1.0. I traced into the source code and it timed out when waiting for a lock,  even when the DROP TABLE transaction is the only running transaction.

The problem doesn't always happen. But once it happens, the table just cannot be dropped with the 40XL1 exception.

Following are the stack traces from locally built and the released derby.jar files:

With locally built derby.jar file:

java.sql.SQLTransactionRollbackException: 40XL1
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:89)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2360)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1334)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:630)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:559)
    ...
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2028)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2351)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
    at javax.swing.AbstractButton.doClick(AbstractButton.java:389)
    at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:809)
    at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:850)
    at java.awt.Component.processMouseEvent(Component.java:6382)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
    at java.awt.Component.processEvent(Component.java:6147)
    at java.awt.Container.processEvent(Container.java:2083)
    at java.awt.Component.dispatchEventImpl(Component.java:4744)
    at java.awt.Container.dispatchEventImpl(Container.java:2141)
    at java.awt.Component.dispatchEvent(Component.java:4572)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4619)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4280)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4210)
    at java.awt.Container.dispatchEventImpl(Container.java:2127)
    at java.awt.Window.dispatchEventImpl(Window.java:2489)
    at java.awt.Component.dispatchEvent(Component.java:4572)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:704)
    at java.awt.EventQueue.access$400(EventQueue.java:82)
    at java.awt.EventQueue$2.run(EventQueue.java:663)
    at java.awt.EventQueue$2.run(EventQueue.java:661)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
    at java.awt.EventQueue$3.run(EventQueue.java:677)
    at java.awt.EventQueue$3.run(EventQueue.java:675)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:674)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:296)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:196)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:188)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.sql.SQLException: 40XL1
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71)
    ... 50 more
Caused by: ERROR 40XL1: 40XL1
    at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:268)
    at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(ConcurrentLockSet.java:618)
    at org.apache.derby.impl.services.locks.AbstractPool.lockObject(AbstractPool.java:119)
    at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(ConcurrentPool.java:28)
    at org.apache.derby.impl.store.raw.xact.ContainerLocking3.lockContainer(ContainerLocking3.java:64)
    at org.apache.derby.impl.store.raw.data.BaseContainerHandle.useContainer(BaseContainerHandle.java:809)
    at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(BaseDataFileFactory.java:645)
    at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(BaseDataFileFactory.java:585)
    at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Xact.java:1327)
    at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.init(OpenConglomerate.java:910)
    at org.apache.derby.impl.store.access.heap.Heap.open(Heap.java:689)
    at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:476)
    at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1308)
    at org.apache.derby.impl.sql.execute.DDLConstantAction.lockTableForDDL(DDLConstantAction.java:258)
    at org.apache.derby.impl.sql.execute.DropTableConstantAction.executeConstantAction(DropTableConstantAction.java:164)
    at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:61)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:443)
    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:324)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1242)
    ... 44 more



With the officially released derby.jar file.


java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested
java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at com.a.db.derby.f.delete(ADerbyDAO.java:157)
    at com.a.db.controller.e.deleteZLTSimAccountDB(DBManager.java:144)
    at com.a.b.controller.d.deleteZLTSimAccount(AManager.java:173)
    at com.a.b.accounts.table.c$2.actionPerformed(ATablePopupMenu.java:100)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2028)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2351)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
    at javax.swing.AbstractButton.doClick(AbstractButton.java:389)
    at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:809)
    at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:850)
    at java.awt.Component.processMouseEvent(Component.java:6382)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
    at java.awt.Component.processEvent(Component.java:6147)
    at java.awt.Container.processEvent(Container.java:2083)
    at java.awt.Component.dispatchEventImpl(Component.java:4744)
    at java.awt.Container.dispatchEventImpl(Container.java:2141)
    at java.awt.Component.dispatchEvent(Component.java:4572)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4619)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4280)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4210)
    at java.awt.Container.dispatchEventImpl(Container.java:2127)
    at java.awt.Window.dispatchEventImpl(Window.java:2489)
    at java.awt.Component.dispatchEvent(Component.java:4572)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:704)
    at java.awt.EventQueue.access$400(EventQueue.java:82)
    at java.awt.EventQueue$2.run(EventQueue.java:663)
    at java.awt.EventQueue$2.run(EventQueue.java:661)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
    at java.awt.EventQueue$3.run(EventQueue.java:677)
    at java.awt.EventQueue$3.run(EventQueue.java:675)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:674)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:296)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:196)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:188)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.sql.SQLException: A lock could not be obtained within the time requested
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 51 more
Caused by: ERROR 40XL1: A lock could not be obtained within the time requested
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown Source)
    at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown Source)
    at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown Source)
    at org.apache.derby.impl.store.raw.xact.ContainerLocking3.lockContainer(Unknown Source)
    at org.apache.derby.impl.store.raw.data.BaseContainerHandle.useContainer(Unknown Source)
    at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(Unknown Source)
    at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(Unknown Source)
    at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Unknown Source)
    at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.init(Unknown Source)
    at org.apache.derby.impl.store.access.heap.Heap.open(Unknown Source)
    at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(Unknown Source)
    at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(Unknown Source)
    at org.apache.derby.impl.sql.execute.DDLConstantAction.lockTableForDDL(Unknown Source)
    at org.apache.derby.impl.sql.execute.DropTableConstantAction.executeConstantAction(Unknown Source)
    at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
    ... 45 more


Gmane