Question about handling of string of length 0 in PreparedStatement.setString()
Hi, I'm wondering if someone has run into this and can
help me understand what's happening.
I'm porting some JDBC code from Another Database to Derby;
I'm using Derby 10.1.1.0 on RedHat Linux.
My program contains a snippet of code something like:
PreparedStatement stmt = conn.prepareStatement(
"insert into my_table (a, b) values (?, ?)");
stmt.setString(1, "1");
stmt.setString(2, "");
stmt.executeUpdate();
Now, it so happens that the second column ('b', above)
is of type INTEGER, and nulls are allowed.
When I run this program in Another Database, what happens
is that the row is inserted, and the value of column 'b'
in the row is set to NULL.
When I run this program in Derby, what happens is that I
get an exception:
org.apache.derby.client.am.SqlException: Invalid character
string format for type INTEGER.
I tried poking around in the JDBC documentation to see what
it said about type conversions, but I got lost, so I thought
I'd ask the Derby list and see what people thought about
this particular behavior.
That is: is it valid for Derby to reject my insert? Or should
it have converted the value to NULL, like Another Database did?
thanks,
bryan
Derby interface from Ruby?
What's the best way to access Derby databases from Ruby scripts? I tried some simple searching on Google, but didn't find anything obvious. thanks, bryan
Network Server startup scripts for RedHat Linux
I'd like to arrange for a NetworkServer instance of Derby to be automatically started up and shut down on my Red Hat Linux system, using the conventional "chkconfig" service management, as described in , for example, http://www.redhat.com/docs/manuals/enterprise/RHEL-3-Manual/sysadmin-guide/ch-services.html I took a look at startNetworkServer.ksh and it does not appear to be set up for direct use by chkconfig. Is there a pre-built Network Server startup script lying around which is ready for installation into a Red Hat services environment? Or should I write my own such script? thanks, bryan
Does Derby support ALTER TABLE DROP COLUMN?
I tried alter table my_table drop column my_column; and I got: ERROR 42X01: Syntax error: Encountered "drop" at line 4, column 27. Do I have a syntax error? Or does Derby simply not support dropping columns from an existing table? thanks, bryan
How to suppress line "Connection number: NNN." in Network Server output
I've been running Derby 10.1.1.0 in Network Server mode using an unmodified version of startNetworkServer.ksh from the Derby distribution. I notice that my network server process produces output of the form: Connection number: 121538. Connection number: 121539. Connection number: 121540. How do I suppress this output? thanks, bryan
How can I tell if Derby is using my derby.properties file or not?
Sorry, rank beginner question here: I've placed a derby.properties file into the directory that (I think) is my derby.system.home directory, and I'm trying to tell whether derby is in fact using my properties or not. Does Derby write any special sort of messages to derby.log upon reading my derby.properties file? Or is there some other way that I can verify that the desired properties have been read? thanks, bryan
What is this exception trying to tell me?
I got this exception several times today, but I don't know what it's trying to tell me. Can anybody take a stab at explaining this error to me in more "layman's" terms? Is there a place where I should have gone to look up 0x2116? thanks, bryan org.apache.derby.client.am.DisconnectException: Execution failed due to a distribution protocol error that caused deallocation of the conversation. A PROTOCOL Data Stream Syntax Error was detected. Reason: 0x2116 at org.apache.derby.client.net.Reply.doSyntaxrmSemantics(Unknown Source) at org.apache.derby.client.net.NetConnectionReply.parseSYNTAXRM(Unknown Source) at org.apache.derby.client.net.NetConnectionReply.parseCommonError(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source) at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source) at ...
Re: SYNTAXRM exception
Responding to Kathy's message from Friday:
> David is on target that we probably need a bug to be filed.
> Normally when there is a protocol exception it means a bug.
> SYNTAXRM means that the server has spent a response to the
> client which the client doesn't understand. Here the
> client has sent a prepare and is expecting statement description
> information back from the server but something went wrong.
I haven't yet figured out how to reproduce this problem, although
it is happening routinely so hopefully I'll be able to pin it down.
Meanwhile, I do get a different, but related, error message in my
derby.log on the server side when I get the SYNTAXRM problem on
the client side. Here is the error that is logged on the server side:
2005-10-07 16:58:25.482 GMT Thread[DRDAConnThread_7,5,main] (DATABASE =
BuildFarm),
(DRDAID = GA0A0026.A122-940688243462706959{274885}), Execution failed because
of a Distributed Protocol Error: DRDA_Proto_SYNTAXRM; CODPNT arg = 2116;
Error Code Value = 1d
Execution failed because of a Distributed Protocol Error: DRDA_Proto_SYNTAXRM;
CODPNT arg = 2116; Error Code Value = 1d
2005-10-07 16:58:25.483 GMT Thread[DRDAConnThread_7,5,main] (DATABASE =
BuildFarm),
(DRDAID = GA0A0026.A122-940688243462706959{274885}), null
null
org.apache.derby.impl.drda.DRDAProtocolException
at org.apache.derby.impl.drda.DRDAConnThread.throwSyntaxrm(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.invalidCodePoint(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.parseCNTQRY(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.splitQRYDTA(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.writeFDODTA(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.writeQRYDTA(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
I have had this happen about a dozen times recently. The exception messages seem
to be very similar in all these cases:
- sometimes the CODPNT arg is 2114, sometimes it is 2116
- when the CODPNT is 2114, the Error Code Value is e, but when the CODPNT
arg is 2116, the Error Code Values is 1d.
I see by looking in CodePoint.java that 0x2114 is QRYBLKSZ, while 0x2116 is
RTNSQLDA.
Please let me know if this additional information helps give you some more
suggestions about what might be going wrong, and I'll continue to try to look
at my code to see if I can figure out what I'm doing to trigger this.
thanks,
bryan
--- Original message is below -
Subject:
Re: What is this exception trying to tell me?
From:
Kathey Marsden <[EMAIL PROTECTED]>
Date:
Fri, 07 Oct 2005 13:44:34 -0700
To:
Derby Discussion
David W. Van Couvering wrote:
>> Bryan, I would love it if you logged this error message as a bug, we
>> need to clean up some of our error messages to be more meaningful.
>>
>> And, no, sorry, I have no idea what this means.
>>
>> David
>>
>> Bryan Pendleton wrote:
>>
>
>>>> I got this exception several times today, but I don't know
>>>> what it's trying to tell me.
>>>>
>>>> Can anybody take a stab at explaining this error to me in more
>>>> "layman's" terms?
>>>>
>>>> Is there a place where I should have gone to look up 0x2116?
>>>>
>>>> thanks,
>>>>
>>>> bryan
>>>>
>>>> org.apache.derby.client.am.DisconnectException: Execution failed due
>>>> to a distribution protocol error that caused deallocation of the
>>>> conversation. A PROTOCOL Data Stream Syntax Error was detected.
>>>> Reason: 0x2116 at
>>>> org.apache.derby.client.net.Reply.doSyntaxrmSemantics(Unknown Source)
>>>> at
>>>> org.apache.derby.client.net.NetConnectionReply.parseSYNTAXRM(Unknown
>>>> Source) at
>>>> org.apache.derby.client.net.NetConnectionReply.parseCommonError(Unknown
>>>> Source) at
>>>> org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown
>>>> Source) at
>>>> org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown
>>>> Source) at
>>>>
org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown
>>>> Source) at
>>>>
org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown
>>>> Source) at
>>>> org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown
>>>> Source) at
>>>> org.apache.derby.client.am.Statement.rea
Re: What is this exception trying to tell me?
> David is on target that we probably need a bug to be filed. > Normally when there is a protocol exception it means a bug. Thanks! I've filed DERBY-614 to track the analysis of this problem. bryan
Question about derby.locks.deadlockTrace
Hi all, I was reading http://www.linux-mag.com/content/view/2134/ (good article, btw!), and it says: The next two properties are needed to diagnose concurrency (locking and deadlock) problems. *derby.locks.monitor=true logs all deadlocks that occur in the system. *derby.locks.deadlockTrace=true log a stack trace of all threads involved in lock-related rollbacks. It seems, that, in my environment, the deadlockTrace property does not log a stack trace of *all* threads involved in the deadlock. Instead, it only logs a stack trace of the *victim* thread involved in the deadlock. Is this a bug in the code? A mistake in the article? A mistake in the way I've configured my environment? If it is a mistake in the article, and deadlockTrace=true is intentionally only logging a stack trace of the victim thread, is there a way to configure Derby to log a stack trace of *all* threads? (That would be very useful to me right now.) thanks, bryan
Re: I need some advice to choose database for an upcomming job
A "Guidelines" section starts on slide 19. Slide 24 lists 100-500 updates per second -- but, of course, your actual performance will depend on the complexity of your transactions. Is there a simple way that I can observe what actual performance I'm getting? That is, does Derby keep its own monitoring statistics about the activity that is occuring, and, if so, how do I retrieve those statistics and view them? I see that there is SYSCS_GET_RUNTIME_STATISTICS, but that seems to be mostly about execution of a single query; how do I see information like: - transactions per second - IOs per second (read and write) - active users - memory usage etc. thanks, bryan
Re: Question about derby.locks.deadlockTrace
A long time ago there use to be room in each lock to point at a stack trace for each lock, but that was removed... Would it be reasonable for me to file an enhancement request on this? It seems like you've outlined several possible alternate implementations that might be viable, and it definitely seems like it would be a useful feature to me. thanks, bryan
Re: Derby with beans and null primitives
Michael McCutcheon wrote: I'm implementing a utility class that populates 'beans' with data from tables in derby. Aren't you sort of re-inventing the wheel? It seems like there are a lot of libraries already out there which do this already. Surely it would be easier to just use an existing library? Try Google-searching for strings like JDO, Hibernate, Castor, etc. thanks, bryan
Lock table messages are truncated in Client/Server mode
Hi all, I think this is a bug, but I wanted to ask the list before reporting it as such. I am running Derby 10.1.1.0 in Network Server mode. My application intermittently encounters lock timeouts. To diagnose them, I have derby.locks.deadlockTrace=true in order to print the lock table when the timeout is encountered. When the lock times out, I get message 40XL2 as I expect. However, the symptom that concerns me is that the message is truncated, to some length around 2,500 characters. Is there some sort of max limit to the size of a message which can be returned to the client in Network/Server mode? It does appear that the entire message is printed to derby.log, so perhaps the truncation occurs when transmitting the exception from server to client? I created a small test program, below, and ran it in two configurations: - when I run derby embedded, the entire lock table is displayed - when I run derby client/server, the lock table is truncated, after about half-a-dozen lines are printed. Is this a bug? If so, I will file the bug and ask the developers list for some help in diagnosing it. I searched JIRA but didn't find anything obvious (though I'm still learning how to use the JIRA search tools). thanks, bryan --- Test program is below --- -- How to use: -- 1) set derby.properties to contain --derby.locks.deadlockTrace=true -- 2) Start the network server -- 3) create a database called 'testLockTimeout' -- 4) Adjust this script to give the proper 'connect' statements -- 5) run this script from ij -- -- the point of the script is that it creates a whole lot of tables -- then arranges for a lock timeout, to see if all the tables are reported -- properly when derby.locks.monitor is set. -- -- Use these to run embedded, where the full lock table is printed correctly: -- connect 'jdbc:derby:testLockTimeout' as me; -- connect 'jdbc:derby:testLockTimeout' as you; -- -- Use these to run in Network Server mode, where the table is truncated: connect 'jdbc:derby://localhost:1527/testLockTimeout' as me; connect 'jdbc:derby://localhost:1527/testLockTimeout' as you; set connection me; drop table a; drop table b; drop table c; drop table d; drop table e; drop table f; drop table g; drop table h; drop table i; drop table j; drop table k; create table a(a integer); create table b(b integer); create table c(c integer); create table d(d integer); create table e(e integer); create table f(f integer); create table g(g integer); create table h(h integer); create table i(i integer); create table j(j integer); create table k(k integer); commit; set connection you; autocommit off; set connection me; autocommit off; lock table a in exclusive mode; lock table b in exclusive mode; lock table c in exclusive mode; lock table d in exclusive mode; lock table e in exclusive mode; lock table f in exclusive mode; lock table g in exclusive mode; lock table h in exclusive mode; lock table i in exclusive mode; lock table j in exclusive mode; lock table k in exclusive mode; -- This, of course, will block, and will (after the configured number of -- seconds have passed) return a lock timeout. set connection you; lock table a in exclusive mode;
Table Intent locks not optimized/collapsed if table-level lock already held?
I ran the following experiment, with somewhat surprising results: create table a (a integer); autocommit off; lock table a in exclusive mode; select * from syscs_diag.lock_table; insert into a values (1); select * from syscs_diag.lock_table; -- Note (1) below commit; select * from syscs_diag.lock_table; lock table a in exclusive mode; select * from syscs_diag.lock_table; update a set a=2 where a = 1; select * from syscs_diag.lock_table; -- Note (2) below commit; quit; At points (1) and (2) in the above script, I was surprised to see that Derby had taken out additional IX-mode locks on table A. It seems that Derby is smart enough to know that it doesn't need to take out ROW locks, since I have the table locked exclusively, but that same optimization doesn't seem to be performed at the table level, and the (apparently) unnecessary IX-mode table lock is redundantly acquired. Am I overlooking something? Is there a reason for the extra IX-mode lock to be taken? Or is this just an opportunity for an additional optimization? thanks, bryan
Re: Table Intent locks not optimized/collapsed if table-level lock already held?
Mike Matrigali wrote: the logic is slightly different dependent on isolation level, what isolation level are you running. All the code gets the table level intent lock first, and if that succeeds then checks if it has covering locks such that it does not need to get row locks. The code is in the lockContainer() routines in opensource/java/engine/org/apache/derby/impl/store/raw/xact/RowLock*.java. I am running in the default (Read Committed) isolation level, so I've been looking at the code in RowLocking2.java. By stepping through the lockContainer() code it appears that: 1) I successfully get the Container IX lock 2) Then the code checks to see if I've already got the Container X lock, and it decides that yes, I do. (isLockHeld returns true). 3) Then the code calls lf.unlockGroup() with the comment //release any container group locks becuase CX container lock will cover everthing. 4) I end up getting down to LockSpace.unlockGroup(), but at the very start of this routine the variable "dl" is null, so I take the "if (dl == null) return;" path. So the lock code seems to be taking the right basic path, but it isn't finding the IX lock to release it. Is it possible that the problem involves this code at the start of RowLocking2.lockContainer: // for cursor stability put read locks on a separate lock chain, which // will be released when the container is unlocked. Object group = forUpdate ? ((Object) t) : ((Object) container.getUniqueId()); Since forUpdate is true in my case, I attach the IX lock to my transaction, but then, a few lines later, when I try to release that IX lock because it's covered by the X lock, we simply call lf.unlockGroup(t.getCompatibilitySpace(), container.getUniqueId()); which means that "group" is set to "t" when I acquire the lock, but it is set to "container.getUniqueId()" when I try to release the lock, and hence the lock is not found. Or maybe I'm totally barking up the wrong tree... thanks, bryan
Re: Table Intent locks not optimized/collapsed if table-level lock already held?
the logic is slightly different dependent on isolation level, what isolation level are you running. All the code gets the table level intent lock first, and if that succeeds then checks if it has covering locks such that it does not need to get row locks. I tried an experiment at lock level 3, too, and it seems to have similar not-quite-optimal behavior. In general, it seems like the "covering" logic is successful at noticing that a table-level covering lock removes the need for row-level locks, but the logic seems to take and hold unnecessary table-level intent locks at both isolation level 2 and isolation level 3. From what I can tell, the lockContainer() method in RowLocking3 doesn't even try to release the unnecessary intent-mode table-level lock, while the lockContainer() method in RowLocking2 does try to release the unnecessary table-level intent lock, but fails to do so. thanks, bryan C:\bryan\src\derby\lockTable>java -classpath c:\bryan\src\derby\trunk\c lasses org.apache.derby.tools.ij lockLevel3.ij ij version 10.2 ij> connect 'jdbc:derby:lockTest'; ij> autocommit off; ij> set isolation rr; 0 rows inserted/updated/deleted ij> lock table a in exclusive mode; 0 rows inserted/updated/deleted ij> select mode,tablename,state from syscs_diag.lock_table; MODE|TABLENAME |STATE --- X |A |GRANT 1 row selected ij> select * from a where a = 3; A --- 3 1 row selected ij> select mode,tablename,state from syscs_diag.lock_table; MODE|TABLENAME |STATE --- X |A |GRANT IS |A |GRANT 2 rows selected ij> update a set a = 3 where a = 3; 1 row inserted/updated/deleted ij> select mode,tablename,state from syscs_diag.lock_table; MODE|TABLENAME |STATE --- X |A |GRANT IS |A |GRANT IX |A |GRANT 3 rows selected ij> commit; ij> quit;
Re: is there any way to monitor commands that are comming to Derby server?
Legolas Woodland wrote: is there any way to see which commands are coming from clients to Derby server ? I mean , i as developer want to see which commands are executed against my database , what should i do ? One good way is to set derby.language.logStatementText. It will cause each statement to be logged to your derby.log file, so that you can read through it and see what actions are occurring. http://db.apache.org/derby/docs/10.1/tuning/rtunproper43517.html thanks, bryan
Re: What is diffrence(es) between derby.system.home and DERBY_INSTALL ?
[EMAIL PROTECTED] wrote: 2 & 3. I have not used derby.properties file but I would assume this file would be in the folder specified by derby.system.home. In my example it would the derbydata folder. The network server does nothing other than delegate network requests to the specified database located on the local file system. As far as I know, the network server cannot be configured with derby.properties or service.properties. derby.properties works fine with the Network Server. As you say, it should be placed in the derby.system.home folder. Here's some more information: http://db.apache.org/derby/docs/10.1/tuning/ctunsetprop13074.html thanks, bryan
Re: Select records that are not locked?
Danny wrote: What I was after was a way to build a list of transactions that are available to edit for a user. What I don’t want in the list is any transactions that are currently being edited by another user. I think you should implement this notion of "transaction" within your application, and be careful not to confuse it with the lower-level DBMS concept of transaction. That is, in your application, you should have a transaction table, and transactions should have a certain state, and a certain lifecycle, which you can define as appropriate for your application. Then, when a user is editing a transaction, you update the transaction state in your transaction table to reflect that this transaction is currently being edited by this user. And, when you want to build a list of transactions which are not currently being edited, you run a select statement which fetches the rows from your transaction table which are in the appropriate state. thanks, bryan P.S. Regarding the low-level DBMS transaction, I agree with what others have already said: keep them short and focused; don't hold them open across UI think periods. Fetch some data from the database, update things as necessary to reflect that a user is currently working with this data, then commit that DBMS transaction. Later, when the user issues some command in the UI, use a separate DBMS transaction to return to those records and process them accordingly.
Re: How i can get current Date in sql statemetn ?
Legolas Woodland wrote: Hi Thank you for reading my post. how i can get current date in derby SQL ? something like Date() ?? http://db.apache.org/derby/docs/10.1/ref/rrefsqlj34177.html thanks, bryan
Re: Load Data from Derby into Jtable
Wondering if there are any good samples of loading data from derby into a Jtable with column names as headers? Here's another nice example to get you started: http://www.javaspecialists.co.za/archive/newsletter.do?issue=118&locale=en_US bryan
Re: UserUtility
Stephen-D Mainstone wrote: I am trying to use the UserUtility class to add permissions for a given user. Possibly you are encountering bug DERBY-87, in which the Derby documentation incorrectly showed a way to call the UserUtility class? See: http://issues.apache.org/jira/browse/DERBY-87 I think that the current documentation for this part of Derby can be found at: http://db.apache.org/derby/docs/dev/devguide/cdevcsecure866060.html and http://db.apache.org/derby/docs/dev/devguide/cdevcsecure865880.html Hope this helps, bryan
Re: Issue creating db
I get this error:
org.apache.jasper.JasperException: Failed to create database
'/home/commrcha/chan/db', see the next exception for details.
Often, when you get an error that says 'see the next exception for details',
you need to call "getNextException()" or "getCause()" or something like
that, because multiple exceptions have been 'wrapped' together.
I see that JasperException extends ServletException, so I believe that
in this case it is "getRootCause()" that you need to call.
So something like:
catch (ServletException se)
{
se.printStackTrace();
if (se.getRootCause() != null)
se.getRootCause().printStackTrace();
}
should give you considerably more information.
thanks,
bryan
Re: how to backup ?
samy wrote: I am using derby database(embedded version) in software. I need to backup the database and also retrieve that backup. So kindly help me to take backup and retrieve. This page should provide the information you desire: http://db.apache.org/derby/docs/10.1/adminguide/cadminhubbkup98797.html thanks, bryan
How do I tell whether a table has unused space or not?
Recently, I had a table which was able to substantially benefit from compression. I determined that I had a problem by setting derby.logQueryPlan to TRUE, running a "select *" query against the table, and then viewing the page and row count statistics that were emitted in the query plan, where I saw that the optimizer was expecting to process many more pages than I thought needed to be in the table. Compressing the table made my problem go away, and the optimizer returned to choosing my desired query plans. But I am left with the desire for an easier way to figure out whether my table needs compression or not. I tried reading the manual, for example I found http://db.apache.org/derby/docs/10.1/adminguide/cadminspace21579.html but that page does not offer any clear way to tell whether a table needs to be compressed or not. What is the preferred way to decide whether a table has unused space? thanks, bryan
Re: DRDA_InvalidReplyTooShort.S:Invalid reply from network server: Insufficient data.
When stress testing a mechanism to simultaneously start the Derby network server from different applications at the same time, I sometimes get the following exception: DRDA_InvalidReplyTooShort.S:Invalid reply from network server: Insufficient data. Is this possibly a bug? I'm not sure I understand the first paragraph above very well, but I can't think of any reason you should be getting the ReplyTooShort response other than a bug, so I would say yes, this sounds like a bug to me. When this happens, can you look on the server side, and find your derby.log, and look there? Are there interesting messages there? Also, can you capture the actual console (System.out and System.err) from the server side, and see if there are any interesting messages there? I suspect that the problem is happening on the server side, and the stack trace and error message on the client side aren't going to be all that helpful. If you are trying to simultaneously start multiple instances of the network server at the same time, aren't you going to get "port number in use" conflicts, and the like? Perhaps, in addition to letting us know what you can learn from crawling around in your server-side logs, you could also explain a bit more about what behavior you are expecting to see from your server-starting mechanism that you are testing. thanks, bryan
Re: "No suitable driver" running dblook
Robert Rivoir wrote: >java org.apache.derby.tools.dblook -d 'jdbc.derby.calDB' Try using a colon instead of a period in the -d database URL argument: java org.apache.derby.tools.dblook -d 'jdbc:derby:calDB' bryan
Re: Query and uppercases
Is there a way to bypass this, so when querying the database it does not look at uppercases or lowercases. è Detroit = detroit = dEtRoIt = … Try using the UPPER function: select * from city_info where UPPER(city_name) = 'DETROIT'; thanks, bryan
Re: turn off row_locking
What I would like to know is how can I disable row level locking
One way is to alter your application to do:
stmt.executeUpdate("lock table CUSTOMER in exclusive mode");
Where "CUSTOMER", of course, is replaced by the actual name of your table.
Once you have done this, Derby will not take any row-level locks for
accesses to this table by this transaction.
thanks,
bryan
Re: [MEMORY]Free memory
Is there something to do closing a database to free all memory use by this database ? In addition to committing and closing your connections, you can also shut down the database: http://db.apache.org/derby/docs/10.1/devguide/tdevdvlp40464.html thanks, bryan
Re: storage requirement
Maryam Moazeni wrote: I would like ti know the storage required for DATE, TIME and TIMESTAMP. I assume you mean the disk space storage requirement for a column of such a type? One simple way to figure this out is to create a table containing a column of such a type, store a few hundred thousand rows into that table, figure out the overall size of the table, and divide by the number of rows. For purposes such as this, it would probably be adequate to figure out the overall size of the table just by using your operating system "ls" or "dir" command on the underlying *.dat file in your seg0 folder. thanks, bryan
Re: Query performance of joining table and a view
The problem is the result from this query returns me after 25 minutes later. Can anybody suggest anything about What the problem is? It sounds like you're getting the results that you expect from your query, but it's taking much longer than you expect. If that's true, than the problem is that the database is doing more work than you want it to, which you may be able to fix by adding indexes or restating your query. To start with, you're going to need to gather a lot more information. Here are some good places to start: http://db.apache.org/derby/docs/dev/tuning/ttundepth33391.html http://wiki.apache.org/db-derby/PerformanceDiagnosisTips thanks, bryan
Article on JDBC 4 enhancements on the O'Reilly site
Here's a nice article on the new JDBC version 4 enhancements at the O'Reilly web site. The article uses Derby for its examples. http://www.onjava.com/pub/a/onjava/2006/08/02/jjdbc-4-enhancements-in-java-se-6.html Enjoy, bryan
Re: DerbyClient has problem with whitespaces in connection urls
Trying to create a dataset using BIRT report's designer i have faced a problem related with white spaces in connection urls. I believe this is DERBY-618: http://issues.apache.org/jira/browse/DERBY-618 which has been fixed, and the fix is in Derby version 10.1.2.4. Can you upgrade to that version and tell us if the fix works for you? thanks, bryan
Re: drop column functionality
But it looks like there is no ALTER TABLE DROP COLUMN ... functionality. ... Is there an expectation for resolution? Hi Tim, Thanks for considering Derby; I think you will find it is very powerful. You are correct that ALTER TABLE DROP COLUMN is not currently present. However, as you've observed, there is an active issue for it and that issue has a patch available. Can you try building Derby with the patch attached to that JIRA issue, and let us know if the feature appears to be working properly for you? Getting some additional testing would definitely accelerate the process of integrating this feature into the codeline. There are two open problems regarding that patch that I am still studying: - DROP COLUMN may not work properly if there are GRANTs on that column - DROP COLUMN may not work properly if there are VIEWs on that column Other than that, I believe that the feature is working properly, so it would be wonderful to get some additional experience about how it works "in real life". If you need assistance building Derby with the patch, please ask on the developers list (derby-dev@db.apache.org) and we'll be glad to help. thanks, bryan
Re: drop column functionality
Tim Dudgeon wrote: 1. Should I apply the patch to the 10.1.3.1 sources (e.g. the db-derby-10.1.3.1-src.zip download) or something else? The patch is intended to be applied to the current trunk, which you can fetch from svn via svn checkout https://svn.apache.org/repos/asf/db/derby/code/trunk/ 2. Syntax: presumably ALTER TABLE a_table DROP COLUMN a_column; Yes, pretty much just like that, with two additional notes: - the keyword "COLUMN" is optional, and - there is an optional [ CASCADE | RESTRICT ] at the end; if you don't say CASCADE or RESTRICT the default is CASCADE. If you have more comments about the patch or the implementation we should probably move this discussion to the developer's list (derby-dev@db.apache.org). thanks, bryan
Re: delete a column feature
Stephen Caine wrote: What we are waiting for is the ability to delete a table column. It's great to hear that you are having success using Derby! Regarding the ability to delete a column from a table, you may want to follow the progress of http://issues.apache.org/jira/browse/DERBY-1489 Note that there is a patch currently available for this issue. If possible, it would be wonderful if you could apply this patch in your environment and let us know your experiences with the proposed implementation. In addition to working directly with the code, community help is always appreciated in areas such as: - testing new features (thanks for testing the beta release!) - reviewing and editing documentation - and more http://wiki.apache.org/db-derby/DerbyDev explains in more detail. thanks, bryan
Re: delete a column feature
Where is the patch and how do I 'apply' it? The patch is an attachment to the JIRA issue. Go to http://issues.apache.org/jira/browse/DERBY-1489 and look at the "File Attachments" section of the web page. You can download the patch just by clicking on it. To apply the patch, you'll need to learn how to build Derby from source code, which is described here: http://db.apache.org/derby/derby_downloads.html#Derby+source+code and here: http://wiki.apache.org/db-derby/ForNewDevelopers#head-278c37b71407ea38bf6f3310a13d7ebce3e2a32c For additional help, please send a message to the derby-developers list at [EMAIL PROTECTED] The developers there will be glad to help you with any problems that you have with building the code and working with patches, etc. thanks, bryan
Re: SQL Parser failing on NULL column contraint
Duncan Groenewald wrote: Any chance someone can explain how I could modify the parser (or whatever) to be able to handle the NULL constraint ? Beware: I haven't tried this, but you could have a look at java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj and experimentally try to modify it for yourself. In terms of the modifications, I think you'll want to look at the subroutine columnConstraint(), and observe how the explicitNotNull and explicitNull flags are handled. Notice that there is an explicit parser block for I think you'll either want to add a second explicit parser block for or modify the existing one to [] and either way you want to make sure that you manage the explicitNull and explicitNotNull flags properly, and that you call setNullability() on the dataTypeDescriptor to record the user's NULL / NOT NULL choice. Hope this helps, bryan
Re: Large IN clause performance
The existing code makes a lot of use of SQL "IN" clauses, where the term can often contains thousands of items. This performs very badly on 10.1.3.1 - because it seems to ignore indexes and just use the min and max terms to do a table scan I think this is DERBY-47, also logged as DERBY-713: http://issues.apache.org/jira/browse/DERBY-47 http://issues.apache.org/jira/browse/DERBY-713 The JIRA issues have some discussion of possible alternate strategies and ways to rewrite queries to get better performance, but I don't think there's any easy solution available right now, sorry. We'd love to have help in this area; perhaps you can consider helping to work on possible implementations? thanks, bryan
Re: ClassFormatError while performing a SQL insert
java.lang.ClassFormatError: Invalid method Code length 69936 in class There is a hard limit in the JDK class-file format of 64K, and there are some known issues in Derby which can cause the generated code to exceed these limits. Tremendous improvements have been made in this area over recent months; are you able to try your test with the latest trunk code or with the 10.2 release beta? We'd love to have more testing of the beta release! http://wiki.apache.org/db-derby/TenTwoRelease thanks, bryan
Re: ALTER TABLE changes in 10.2.1.3
Stephen Caine wrote: Is dropping a column now supported in 10.2.1.3? Hi Stephen, thanks for trying the beta! The ALTER COLUMN changes in this beta are DERBY-119 and DERBY-1491, which allow you to do: ALTER TABLE t ALTER COLUMN c [ NOT ] NULL This lets you add or remove the NOT NULL constraint on a column ALTER TABLE t ALTER COLUMN c [ WITH ] DEFAULT default-value This lets you change the default value for a column Dropping a column is DERBY-1489, which is getting closer to being ready to commit, but is not there yet. You might want to register yourself as a "watcher" on DERBY-1489 to keep better track on its progress: http://issues.apache.org/jira/browse/DERBY-1489 thanks, bryan
Re: How to see SQL as it is executing?
Michael McCutcheon wrote: Can I turn on some property and see the SQL's the database is running? See if this helps: http://db.apache.org/derby/docs/dev/tuning/rtunproper43517.html thanks, bryan
Re: Performance of IN operator
SELECT thingIdx, thingName FROM Things WHERE thingIdx IN (?, ?, ?, ?, ... [100 values scattered throughout index]) performance is pretty slow -- a bit over a minute for 100 records. I believe this is DERBY-47 (http://issues.apache.org/jira/browse/DERBY-47), also logged as DERBY-713 (http://issues.apache.org/jira/browse/DERBY-713). There are a number of suggestions in those two bug reports for ways to work around the problem. I don't believe there is a fix yet, so for the time being it is advisable to avoid the problem. thanks, bryan
Re: Dbase Manager
I have used Squirrel SQL (http://squirrel-sql.sourceforge.net/) on another project and found it reasonably ok. I haven't used it with Derby though. Squirrel works great with Derby! Here's a nice set of notes: http://db.apache.org/derby/integrate/SQuirreL_Derby.html thanks, bryan
Re: Dbase Manager
Marl Atkins wrote: > Thanks this looks good but is there any way I can point it at an existing > embedded database? > >> Squirrel works great with Derby! Here's a nice set of notes: >> http://db.apache.org/derby/integrate/SQuirreL_Derby.html Which database to process is controlled by the information you provide in the "URL" field. Look at Figure 2 in the notes linked above. Do you see the "FirstDB" in that URL field? You want to change that to point to the database you want to work with. Figure 3 shows a different URL value, pointing to a different database. thanks, bryan
Re: calling function : classpath problem SOLVED!
I am having a little better luck now that I have my new Apache Derby Book. Great! Good to hear you are having success. set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar;%DERBY_INSTALL%\lib\derbynet.jar;%CLASSPATH% wasn’t working. it would set some of the environment variables but not all of them. It was leaving the directory containing my function class out. Perhaps you have an issue with spaces and quoting. Getting the quotation marks correct in a batch script is always rather delicate, and there are some common directories on Windows (c:\Program Files is a good example) which have spaces in their name. Anyway, it sounds like you've got a script that is working, so good news. thanks, bryan
Re: no RENAME COLUMN functionality?
I can't find any reference in the documentation for renaming columns. Derby does not yet support this feature. A request to add such functionality has been logged as DERBY-1490: http://issues.apache.org/jira/browse/DERBY-1490 thanks, bryan
Re: Derby 10.1.3.1 Embedded+Network
All this works great, except that when I connect from outside of tomcat, I always get a read-only connection. Do you mean read-only in the sense of http://db.apache.org/derby/docs/dev/devguide/cdevdeploy11201.html#cdevdeploy11201 Or do you mean read-only in the sense of http://db.apache.org/derby/docs/dev/devguide/rdevcsecure190.html That is, can you determine whether the connection is read-only because the database is on read-only media, or because the security settings are read-only? thanks, bryan
Re: Installing db-derby-10.2.1.6-bin
Bill Slack wrote: I have tried to update from db-derby-10.1.3.1-bin, which I had installed and running from C:\Program Files\, to db-derby-10.2.1.6-bin. However, I am only able to get Derby to work from the C:\ directory. What sort of problems do you encounter? thanks, bryan
Re: DatabaseMetaData.getColumns() with specified schema name gives poor performance
I personally consider 3 minutes just to give basic schema information (of a schema that is *not* particularly big) to be totally unacceptable. Yes, this sounds like it is way too long. Can you construct a small standalone test program that demonstrates the problem? For example, a program along the lines of: - create the database - create all the tables, views, indexes, other schema objects - get the system time - call getColumns() - get the system time again and report on the value you got. That would be useful because it would allow other people to experiment and see if they encounter your problem. thanks, bryan
Re: limit in select
Is there a jira issue for adding a limit statement (to limit the number of records returned) to a select statement? I did a jira search but could find no such issue. I’d like to vote for it. Hi Jim, I think DERBY-581 is the issue that tracks this request: http://issues.apache.org/jira/browse/DERBY-581 Thanks for using Derby, and keep those good ideas coming! bryan
Re: Status (of schema alteration features in Derby)
Dropping a column Can't be done in 10.2, but can be done in the trunk. DERBY-1489 tracks this feature. Changing a column's width Changing a column's type Increasing the width of a VARCHAR, CHAR VARYING, or CHARACTER VARYING column has been possible since 10.1, maybe even since 10.0 or earlier. Other modifications to the column's datatype are not yet possible. DERBY-1515 tracks this feature. Note that I'm currently proposing that DERBY-1489 and DERBY-1490 together will provide an adequate solution to this issue. Changing a column's null statusDone Correct. This was DERBY-119, and it is part of 10.2. Changing a column's name This is DERBY-1490. There is a patch available for review, and I'd love to get some review of the proposed changes. Changing a column's primary key status I think this has been possible since 10.1, or even earlier, via ALTER TABLE ADD/DROP CONSTRAINT Adding/Dropping a column's index I think this has been possible since 10.1, or even earlier, via CREATE / DROP INDEX. Changing a column's default value This one is also done in 10.2. This was DERBY-1491. thanks, bryan
Re: is there any tool for help tunning derby ?
is there any too that help tunning derby and also sql statement that are executed against derby database? There is an entire book devoted to this subject: http://db.apache.org/derby/docs/dev/tuning/ thanks, bryan
Re: Derby repair tools
I'm hoping to use Derby as a backend database. Are there any repair tool(s) in the event the database is corrupted and needs to be repaired? I agree with Thomas's observation that a good backup process should remove most of the need for this. Also, there is the CHECK_TABLE function, although that only does a small set of checks: http://db.apache.org/derby/docs/dev/ref/rrefsyscschecktablefunc.html thanks, bryan P.S. In several years of running Derby 24/7 in production, I've never had a corrupted database.
Re: Heap and stack size for Derby server
I guess someone with more knowledge about Derby's internals may be able to tell you why your specific query requires a larger stack size. I think that the query was noted to use an IN clause, so he may be encountering either http://issues.apache.org/jira/browse/DERBY-47 or http://issues.apache.org/jira/browse/DERBY-713. Robert, can you tell us more about the particular query that is giving you trouble? thanks, bryan
Re: Heap and stack size for Derby server
My problems came from a simple query containing an IN clause with 5000 items in it. I went over this easily by increasing the stack size limit to 1024 KB. Thanks Robert! That definitely sounds like DERBY-47. If you have the time, it'd be great to have some help in working on improving this part of Derby. I'm glad you were able to find a workaround to the problem. The question I'm asking is if there are some best practicing in sizing the heap and stack for the Derby process based on the query complexities, number of database objects and estimated amount of data. I think that one reason you haven't had a lot of response on this is that many people aren't experiencing a lot of problems in this area. In my case, for example, my Derby application has been running quite happily, 24/7, for several years, in the default heap and stack. thanks, bryan
Re: Heap and stack size for Derby server
I don't think this is DERBY-47. DERBY-47 is the issue that the plan generated by an IN query is inefficient. This issue is that a query with a large number of IN parameters fails to compile due to a stack overflow error. Good point. Is this issue already known, then? Or would it be helpful for Robert to file a new Jira issue to track it? thanks, bryan
Re: Derby causes appserver hang with "maxthreads"
When I tried to set the timeSlice (tried value 2000, used NetworkServerControl to set it), the third connection succeeded, but it failed when I issued a query on it (see error message below). Hi Kristian, I think this is DERBY-1856? thanks, bryan
Re: maximum file size
I am curious about one thing though: Why would it not be a good idea to use multiple tables? I think that your proposal and Michael's proposal were quite similar. Michael was observing that if you went one step further, and put the multiple tables into multiple databases on multiple machines, then your application could execute the queries against the various table "pieces" in parallel, and get a shorter elapsed time for the overall query. thanks, bryan
Re: slow subqueries
This is why a nested loop is not going to work here... 20,000 squared operations is very expensive, let alone millions squared. For a query with this profile, the inner query should only be executed once. Perhaps you can get the behavior you desire by explicitly creating a temporary table, selecting the data from your inner query into the temporary table, then re-writing your main query to join against the temporary table? thanks, bryan
Re: Unexpected error "When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions."
Is this a bug in derby? Is there a workaround? I'm running Derby 10.1.2.1 on Fedora Core 5. I don't have an explanation for the behavior you are seeing. I spent a little bit of time trying to reproduce your problem with a smaller query, but couldn't. Can you post a complete example script, with full DDL for the tables, that demonstrates the problem? thanks, bryan
drop table cascade?
I think that Derby currently does not support a CASCADE option on the DROP TABLE statement. I searched Jira and found DERBY-1631, which discusses the desire for a CASCADE option on DROP VIEW, but I could not find a Jira issue logged to request a CASCADE option on DROP TABLE. Is there such a Jira entry already logged? If not, should I log one? thanks, bryan
Re: Large IN clause produces server error
If rewriting the query is not an option, what alternatives are there to overcome this limitation? What version of Derby are you using? (Apologies if you already said that and I missed it). If you are using a version prior to 10.2, you should definitely try 10.2, as there was a *lot* of work in the code generation portion of Derby to avoid hitting class file format limits. If you are using the latest version of Derby, then you may be somewhat stuck, as the class file format limits are hard limits and there is no JVM tuning parameter to get around them. The only solution is either to express a simpler query, or modify Derby's code generation algorithms so that we don't bump up against these class file format limitations. There was a great discussion on this topic about 2 months ago on this list. Dan Debrunner gave several pointers to background material with more information in those messages. You can read them starting here: http://www.nabble.com/Inserting-NULL-values-with-the-embedded-driver-tf222.html#a6191125 In my opinion, if you can reproduce your problem with the 10.2 release of Derby, and if you can package up a small standalone test case with your DDL and with the SQL statement that demonstrates the class file format problem, then you should log a new request in Jira to track the problem. thanks, bryan
Re: Error when executing query:com.ibm.websphere.ce.cm.StaleConnectionException: Meta-data for Container [EMAIL PROTECTED] could not be accessed
I have execute the simple select on this particular view as SELECT * FROM vwDerbyBaseView using a small java client for all the derby modes (Embedded and Network) Then the same error Meta-data for Container [EMAIL PROTECTED] could not be accessed was given in the derby.log Great! It sounds like you've managed to isolate a reproducible test case. Can you package up the CREATE TABLE, CREATE INDEX, and CREATE VIEW statements for this particular view, together with the SELECT statement, into a single SQL script file, so that others can confirm the behavior in other configurations? thanks, bryan P.S. It definitely feels like a resource shortage problem. As a workaround, have you tried giving the JVM more resources?
Re: Date - Timestamp format for inserts?
What's wrong with this statement?
Although the doc in http://db.apache.org/derby/docs/dev/ref/rrefsqlj27620.html
appears to say that the minutes and seconds portions of the timestamp value can
be ommitted, the code does not appear to conform to that behavior.
So instead of '2006-09-10-00', use '2006-09-10-00.00.00' or '2006-09-10
00:00:00'.
Hopefully that will be a reasonable solution to your problem for now.
From what I see by reading through SQLTimestamp.parseDateOrTimestamp(), the code
intends for the minutes and seconds portions to be optional, but the
implementation
doesn't handle that, so my initial reaction is that the documentation is correct
and this is a bug in the timestamp parser.
What do others think? Is Derby supposed to accept '2006-09-10-00' as a valid
timestamp value?
The documentation in question appears to have been added as part of DERBY-234:
http://issues.apache.org/jira/browse/DERBY-234
I've attached some simple experiments I tried, below.
thanks,
bryan
ij> create table tmstp (c1 timestamp);
0 rows inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22 10:00:00');
1 row inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22-11');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22 11');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22 11.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22 11.00.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22-11.00.00');
1 row inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22-11.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22-11.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22-11.00.');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
Re: SQL command to view active locks
I tried doing what you said, but the table does not seem to exist. I am running 10.1.1.0 via the NetworkServerControl API. In version 10.1, you have to use the old name for the table. Try: SELECT * FROM NEW org.apache.derby.diag.LockTable() as LOCK_TABLE See DERBY-571 for more info: http://issues.apache.org/jira/browse/DERBY-571 thanks, bryan
Re: Query regarding derby
"A lock could not be obtained within the time requested " this exception. My queries are as follows 1. Why I am getting this exception? 2. What I should do to avoid this error? 3. How the locking mechanism working in Derby Database? This is a completely normal exception, and indicates that you are experiencing contention for database resources by multiple active transactions. You'll want to spend some time reading through the various subsections of http://db.apache.org/derby/docs/dev/devguide/cdevconcepts30291.html There are many suggestions in that section of the manual for ways to minimize data contention problems. http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html also has some very good material on these topics. Hope this helps! thanks, bryan
Re: Replication Support
Anybody know when/if Derby will support replication? :-) Check http://wiki.apache.org/db-derby/UsesOfDerby for some pointers. There are at least 3 data replication systems listed there which work with Derby. thanks, bryan
Re: XJ200 error
"Exceeded maximum number of sections 32K" It seems to me that you may be suffering from a resource leak of some kind. Can you check to make sure that you are closing all of your ResultSets, Statements, and Connections as soon as you are finished with them? Also, tell us a bit more about your application: What version of Derby are you running? Is it running embedded, or client/server? How long can you run before you see this problem? Is the problem always on the same SQL statement or does it vary? thanks, bryan
Re: deploying derby db via tomcat
I'm looking for simple step-by-step instructions for deploying this app via tomcat. Here's a nice article about using Derby via Tomcat: http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html thanks, bryan
Re: getBytes() or getBlob() fails when resultSet is larger than 1
ERROR XCL30: An IOException was thrown when reading a 'BLOB' from an InputStream. Can you gather and post some more information? Specifically: - on the client side, call printStackTrace() on your exception, and call getNextException() to fetch any nested exception and print that exception, as well (you may need to call getNextException repeatedly in a loop) - on the server side, what's in your derby.log when this happens? That might give some more clues about what's going wrong. thanks, bryan
Re: ALTER TABLE __ ALTER COLUMN Data type syntax
"The length, precision, or scale attribute for column, or type mapping 'VARCHAR(100)' is not valid." Varchar is definitely limited to 32 thousand bytes as its max length, see: http://db.apache.org/derby/docs/dev/ref/rrefsqlj41207.html If you really need a enormous length value, I think you need to use a CLOB. thanks, bryan
Re: ALTER TABLE __ ALTER COLUMN Data type syntax
Alex Moots wrote: Thanks for the quick response, but the query: ALTER TABLE bl.USERSPROPERTIES ALTER COLUMN Value SET DATA TYPE CHAR(32000) Still gives me the same error message: The length, precision, or scale attribute for column, or type mapping 'CHAR(32000)' is not valid I think in this case the problem is that you can't change a VARCHAR to a CHAR using ALTER TABLE .. ALTER COLUMN. You can only change a VARCHAR to another (longer) VARCHAR. In the next release, you'll be able to use the techniques described in DERBY-1515 (http://issues.apache.org/jira/browse/DERBY-1515) to make more substantial changes to a column's datatype in a dynamic fashion. thanks, bryan
Re: ERROR 08001:No suitable driver
ij> connect 'jdbc:db2j:c:\LabFiles60\CloudscapeDB\BANKDB;create=true'; ERROR 08001: No suitable driver When I changed “db2j” to “derby” IJ just hung! Are you sure it hung? Maybe it was creating the database. That does take 30 seconds or so. 'jdbc:derby' is the correct prefix for the connection string. Try again with something like connect 'jdbc:derby:mydb;create=true'; and see if you get a 'mydb' folder created in your current directory. thanks, bryan
Re: ERROR 08001:No suitable driver
A missing quote and semi colon at the end caused it to hang. Ah, yes, the missing semicolon. I've made that mistake many a time myself. Sorry about that. Glad you figured it out! thanks, bryan
New article on using Derby as a Web Client Database
I just came across this article on java.net, thought people on this list might be interested in it: http://today.java.net/pub/a/today/2007/01/16/synchronizing-web-client-database.html Nice work, David! thanks, bryan
Re: indexes
Also, how do I find out all the (possibly compound field) indexes that have been created for a table? Finally, is there a way to dump the db to a file of SQL commands that can be used to reconstruct the db? Hi Amir, I don't know a way to get any suggestions about indexes which could have been used. Regarding your other questions: - "show indexes from t;" in ij will show you all the indexes for table t. - "dblook" will show you the DDL for your database. See http://db.apache.org/derby/docs/dev/tools/ctoolsdblook.html Note that this is just the schema, not the data. If you want the data, you could export the data using the bulk export procedure, or use ddlutils: http://db.apache.org/derby/integrate/db_ddlutils.html Hope this helps. thanks, bryan
Re: Is it possible to rename a field?
Dan Scott wrote: Renaming columns will be possible in the as-yet-unreleased 10.3.0 version of Derby according to https://issues.apache.org/jira/browse/DERBY-1490 Hi Dan, thanks for the nice words! I just wanted to suggest a couple other ideas that might help in the meantime: - define a view, and have your program access the view rather than the table. - create a new table, copy your data from the old table to the new table, drop the old table, and then rename the new table to the old table. These are definitely more cumbersome, but until we get a release of the code with the DERBY-1490 changes, they might help. thanks, bryan
Re: Can't run the Eclipse plug-in labs
'jdbc:derby:net://localhost:1527/jayDB;create=true;user=APP;password=APP;'; Try taking the "net:" out. Having "net:" in there means to use the IBM DB2 JDBC driver, also called the "JCC" driver. The Derby JDBC driver just uses the prefix jdbc:derby:, so just say jdbc:derby://... thanks, bryan
Re: determing index name
Is there a way to get a list of indexes on a table including the index name. This sounds like a nice enhancement request for 'show indexes'. You could also try looking in sys.sysconglomerates. SELECT * FROM SYS.SYSCONGLOMERATES seems to show me the index name, but of course you'll have to do a bit of system catalog joining and querying to tie that back to your base table. thanks, bryan
Re: Eclipse Plug-in Lab code is failing to find driver in derby.jar
A. Rick Anderson wrote: I'm getting a "driver not found" exception when running a relatively trivial derby program from within eclipse. It's frustrating because I can crack the derby.jar file and the targeted driver class is in the derby.jar file that is in the build path for the project. ... private static final String derbyClientDriver = "org.apache.derby.jdbc.ClientDriver"; The client driver is usually in derbyclient.jar, not in derby.jar. It's the *embedded* driver that is in derby.jar. Could that be the problem? thanks, bryan
Re: problem with ALTER COLUMN DEFAULT on VARCHAR column
alter table TABLE_NAME alter column COL_NAME DEFAULT 'new value' and with some VARCHAR columns I get an error like this: Invalid character string format for type long. Well, I'm not sure what's causing this, but here's what I think is going on, maybe it makes sense: when you alter the default for a column which is automatically generated, the code appears to want to compute the current maximum value for that column, and internally it generates and executes the statement: SELECT MAX(COL_NAME) FROM TABLE_NAME For some reason, this MAX query did not return a numeric value. Perhaps the table is empty, and so the MAX query returned a NULL? Does any of this make sense? Are you altering the default for an automatically generated VARCHAR column? If so, can you try running the SELECT MAX query by hand yourself prior to running the ALTER TABLE statement and see what the SELECT MAX query returns? thanks, bryan
Re: problem with ALTER COLUMN DEFAULT on VARCHAR column
If however the column contains data then the SELECT MAX... returns a
string value.
Hi Tim,
I think you've pinpointed this one, and I think it's definitely a bug.
Can you file it in Jira so we can get it fixed? Here's a simple script:
-bash-2.05b$ java org.apache.derby.tools.ij
ij version 10.3
ij> connect 'jdbc:derby:brydb';
ij> create table t (a varchar(10));
0 rows inserted/updated/deleted
ij> alter table t alter column a default 'my val';
0 rows inserted/updated/deleted
ij> insert into t (a) values ('hi');
1 row inserted/updated/deleted
ij> alter table t alter column a default 'another val';
ERROR 22018: Invalid character string format for type long.
thanks,
bryan
Re: Parallel loading, truncation, and booleans
Are you running in Embedded or in Network Server mode? If you are running in Network Server mode, you could try loading your data in Embedded mode, then switching to Network Server mode for actual *use* of the data. That would eliminate the Network Server overhead during the load. thanks, bryan
Re: How to execute optimizer overrides in a java app
In my post I should have said "I also showed two queries that are expected to get syntax errors." --I'm not sure that sample invalid syntax needs to be put into the docs. Is there any feedback on using the "\r" ? In the back of my mind I'm thinking that won't be portable between Windows and Unix. I've wondered about this syntax in the past, so I'm happy to see you exploring the details of its behavior. Thanks! Perhaps you could package up your various example queries as a complete test program, and then add it to the test suite, and then we'd run it on lots of different platforms. That would help us figure out if there are any platforms where it doesn't work. Having a regression test for this would also: 1) Capture the sample invalid syntax in the tests, which is maybe a better place for it to live than in the docs 2) Ensure that the documented behavior continued to work, and didn't accidentally get broken at some point in the future. I guess this is a long way to say that I think you've written a valuable new regression test, and to encourage you to contribute it to the test suite. thanks, bryan
Re: Network Server Daemon
I've been looking through the archives, and JIRA, but I can't seem to find an answer. Basically, will Derby network server be "enabled" to run as a daemon? I understand the next version of JAMES has been modified to use commons-daemon, and wondered if there were plans for Derby to follow. Having it behave as a "proper" daemon would enable it to play nice with SMF under Solaris. Hello John, http://issues.apache.org/jira/browse/DERBY-187 has some of the results of previous investigations of this issue. I believe that our conclusion at the time was that no modifications to Derby are needed; Derby works fine with the procrun software from the commons-daemon project. Are there particular changes that you believe to be necessary? thanks, bryan
Re: Heap container closed exception (2 statements on same connection)
java.sql.SQLException: The heap container with container id Container(-1, 1173965368428) is closed. Hi Jeff, I don't have a lot to offer on this problem, but I did try your test program and I get the same exception, so the problem reproduces for me. What was the behavior that you expected to see, instead of the exception? thanks, bryan
Re: Heap container closed exception (2 statements on same connection)
implicit commit. However, with holdability set to HOLD_CURSORS_OVER_COMMIT, I no longer get an exception on calling next() the first time, but rather get good data for 415 calls. On the 416th call, I get an exception not that the result set is closed, but rather than an underlying storage mechanism is closed. That sounds like a bug to me. thanks, bryan
Re: invalid checksum
one of my customers gets on a notebook the following exception: ... ERROR XSDG2: Invalid checksum on Page Page(28,Container(0, 1248)), Does this happen for this customer over and over? Or did it happen only once? Is the customer able to provoke the error on demand? Can you relate the exception to any particular action that the customer is taking in your application? If it has happened multiple times, is it always the same page on the same container? Or is the location different each time? When the exception happens, are there any follow-on symptoms? Or did the application seem to behave properly after the user shut it down and restarted it? Can the customer later access the data properly on their notebook? Or do you have to move the files to another machine in order to access the data? thanks, bryan
Re: Javdoc
If you're building a Derby application you should just use the regular JDBC javadoc from your JDK. For example when you are working with a PreparedStatement you can refer to http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html thanks, bryan
Re: Queries satisfiable from indexes
Can Derby do this, or does it load the rows regardless? Yes, Derby can definitely do this. Here's more info: http://db.apache.org/derby/docs/dev/tuning/ctunperf10679.html http://db.apache.org/derby/docs/dev/tuning/ctundepth23033.html#ctundepth23033 In the case of the query plan you presented, it seems to me that Derby is in fact using the index, and not the base table; that's what I believe this line is saying: > Index Scan ResultSet for MAIL_ITEM using index I_MAIL_ITEM_TAGS_DATE thanks, bryan
Re: Derby and index order
I couldn't find an open JIRA entry for enhancing Derby to make > using of opposite-ordered indexes when doing ORDER BY. I think that DERBY-884 and DERBY-642 are related: http://issues.apache.org/jira/browse/DERBY-642 http://issues.apache.org/jira/browse/DERBY-884 However, they don't specifically mention ORDER BY. thanks, bryan
Re: Problem in release connection
provided by derby. Following are the code I am using for clean the
database connection and also PreparedStatement.
if(!(aoConnection ==null || aoConnection.isClosed()))
aoConnection.close();//Release Connection
if(aoPreparedStatement != null)
aoPreparedStatement.close();
I think it would be better to close the statement before
you close the connection.
I also tend not to bother with calling isClosed methods. Instead
I set the variable to null after I close the connection. I think
that might also help the garbage collector find more garbage to
collect.
So I'd write something more like:
if (aoPreparedStatement != null)
{
aoPreparedStatement.close();
aoPreparedStatement = null;
}
if (aoConnection != null)
{
aoConnection.close();
aoConnection = null;
}
thanks,
bryan
Re: Connection reset
sometimes i get a Connection reset (i'm also investigating why this happens but i think i've found a problem with derby) when calling a remote derby db. the connection is closed but the transaction is still in progress (see last table at the bottom of the mail). I think that a Connection Reset can mean that the client was terminated abruptly without closing all its Statement and Connection objects. That is, it just hard-closed the TCP/IP connection without closing all the JDBC resources first. When that happens, I think that the server will eventually figure out that the connection has been closed, and will abort the in-progress transaction. I am not sure how long it will take for the server to figure this out. Perhaps it depends on how the network is configured. Does the behavior that you are seeing differ from this? thanks, bryan
Re: invalid checksum
we installed the app serveral times and i think the following lines are per installation (so per installation it happend on the same page): org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(28,Container(0, 1248)), expected=3'455'715'557, on-disk version=3'357'396'866, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(461,Container(0, 1248)), expected=3'908'279'257, on-disk version=137'782'528, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(482,Container(0, 1248)), expected=4'113'528'744, on-disk version=44'301'386, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(702,Container(0, 944)), expected=2'598'292'545, on-disk version=3'174'535'138, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(2767,Container(0, 944)), expected=2'067'623'629, on-disk version=2'180'532'620, page dump follows: Hex dump: org.hibernate.util.JDBCExceptionReporter - Invalid checksum on Page Page(99,Container(0, 1248)), expected=242'030'294, on-disk version=3'389'026'885, page dump follows: Hex dump: That looks to me like it is happening in several different containers (1248 and also 944), and on several different pages (28, 461, 482, 702, 2767, 99). Perhaps that particular computer has a hard disk that is failing. Do you use NTFS on that computer? Can you check the computer's event log and see if the operating system is reporting hardware errors? thanks, bryan
Re: INPLACE Table Compression
“Inplace” Compression utility, no disk space is recovered and the size of the “*.dat” files is not reduced. That is correct. In-place compression re-arranges the records on the existing pages of the existing file, gathering the existing records together and shifting all the free space to be together. This makes access to the existing records more efficient, and allows new records to efficiently make use of the available free space, but does nothing to reduce the overall size of the file. Think of it as somewhat similar to the "defragment" process that your operating system offers. Your overall disk volume is still the same size, but the use of the disk is improved. To release disk space back to the operating system, you must use the version of compression which copies the data to a new file, then deletes the old file. thanks, bryan
Re: INPLACE Table Compression
three operations: purge, defrag, and truncate; when truncate is used, it releases disk space to the operating system Oops! My mistake. Sorry about that. Perhaps the original caller was not passing the TRUNCATE_END parameter in their call to INPLACE compression. My answer described the DEFRAGMENT behavior pretty well, but as you point out that is only one mode of operation for INPLACE compression. Thanks for pointing this out. bryan
Re: INPLACE Table Compression
Inns, Jeff wrote: passed "1" for each operation, which should have turned them on. I agree, Jeff; passing 1 should have been the correct thing to do. This is starting to sound like a bug in the TRUNCATE_END feature to me. thanks, bryan
