Re: Unique constraints and nulls
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? -- John English
Derby DB suddenly can't open
I am use 10.8.2.x for more than 2 years, and it works well till today. Suddenly I can't open it even I upgrade it to 10.8.3, the stack is below: java.sql.SQLException: Database 'XingDB' not found. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) 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.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleDBNotFound(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection30.init(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection40.init(Unknown Source) at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source) at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source) at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:207) What's the reason it should be? Thanks for advance! Regards, Liu
Re: Unnecessary increment of sequence
On 04/03/2014 19:10, Rick Hillegas wrote: On 1/3/14 4:49 AM, Tim Dudgeon wrote: I'm trying to use a sequence to generate a value where one is not supplied, but I'm not able to only increment the sequence when its needed. This is actually going on in a trigger, but to simplify matters here is an example that illustrates the problem: VALUES SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE('APP', 'SEQ_CPD_CODE'); VALUES COALESCE(99, NEXT VALUE FOR seq_cpd_code); VALUES SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE('APP', 'SEQ_CPD_CODE'); In the coalesce function the first argument is always non null, so the second argument should never be needed, but you will see that the sequence is incremented anyway. Are there any alternative approaches that can avoid this? I was thinking of trying in a CASE statement instead, but sequences can't be used there :-( Thanks Tim Hi Tim, I have looked more closely at this. I believe that Derby should not allow NEXT VALUE FOR inside a COALESCE expression. My reasoning can be found on this issue: https://issues.apache.org/jira/browse/DERBY-6494. However, I don't feel inclined to fix this divergence from the SQL Standard if you rely on it. Thanks, -Rick Rick Sorry for delay - I only just spotted your response. I'm not actually using NEXT VALUE FOR inside COALESCE as it didn't give me what I wanted (the sequence was always being incremented even when not necessary). So changing behaviour won't impact me. Instead I reverted to using Java stored procedure as this allowed me to do the conditional stuff that I needed. But its seems like an unnecessary workaround. I would have thought it would be useful to conditionally grab a value from a sequence, and if this can't be done with CASE or COALESCE then its probably not possible except by reverting to Java. So I've got no strong feelings either way. Tim