Re: can't delete my DB directory
AFAIK, when auto-commit is off, transaction demarcation is left (almost) entirely to the user. You should commit() the connection, or close() any Statements/ResultSets. What a database does to handle "unfinished" transactions (even read-only ones) at shutdown is likely highly DB-specific and not part of the spec. While I think Derby should not hang or leave open resources after shutdown, I wouldn't count on it. In the same way that an OS will usually close all open file handles owned by a process when that process exits, it is better if your program cleans up after itself and closes resources explicitly rather than relying on some implicit behavior. In that vein, given that you have turned auto-commit off, the last thing your program should do before shutdown is commit() or rollback(). In auto-commit mode, the end of one transaction implies the beginning of the next. So if you've run *any* SQL on a connection, that connection should be rolled back or committed at some point. -Brett On Thu, Jun 10, 2010 at 3:48 PM, oldmhe wrote: > > Thanks for the info. > > Though this is my first Java DB program. I've written many C programs > using > ESQL, and have never needed to issue a commit after a read or query > operation. > > Is this "commit after query" requirement an SQL concept, or is it just > related to the Java JDBC API? > > > Brett Wooldridge-2 wrote: > > > > Even read operations create transactions (and locks). Because you set > > autocommit to false, you must manually commit. > > > > Having said that, I would expect shutdown to automatically rollback > > any open transactions at the time of shutdown. > > > > Brett > > > > Sent from my iPhone > > > > On Jun 9, 2010, at 10:32, oldmhe wrote: > > > >> > >> The OS is Windows XP. > >> > >> Since I'm using the embedded driver, I don't think it's possible to > >> check > >> what process is hanging on to the file. I.e., I believe it's the same > >> process as my program. When my program exits, I'm able to delete the > >> directory manually. > >> > >> Regarding your last question, my program does shutdown explicitly > >> (as shown > >> in my original post). > >> > >> Since posting, I found a solution, but I don't understand why it > >> works. > >> > >> Below is a simplification of what the program does: > >> > >> 1. load the embedded driver > >>set AutoCommit to false > >>connect to the DB engine, and create a DB > >>create tables > >>load the tables with data, and commit all work > >> > >> 2. using SELECT, read some data records, and create an output > >> file. > >> > >> 3. shutdown the DB engine > >> > >> 4. try to delete the DB directory (and all files and > >> subdirectories) > >>exit > >> > >> With regard to my initial post, Step 4 fails to delete all the files > >> and > >> directories (it's able to delete most of them). > >> > >> However: > >> > >> 1. If I omit Step 2 (the reading of the DB), Step 4 succeeds. > >> > >> 2. Or, if I add "xxx.commit()" between Step 2 and Step 3, then Step 4 > >> succeeds. > >> > >> It seems that a commit() is needed even though Step 2 makes no > >> change to the > >> DB. This is contrary to my expectations for two reasons: > >> > >> a) Since Step 2 is a read-only operation, I don't see why commit() is > >> needed. > >> > >> b) Even if a commit() is needed, the shutdown should release all DB > >> resources (and not hang on to any files). > >> > >> Any thoughts? > >> > >> > >> Kristian Waagan-4 wrote: > >>> > >>> Hello, > >>> > >>> What operating system are you using? > >>> Are you able to use the operation system's proper tool to check which > >>> process (if any) is hanging on to the file? > >>> (i.e. pfiles or lsof) > >>> > >>> Also, do you see the same behavior if you in addition shut down the > >>> database explicitly? > >>> (i.e. 'DriverManager.getConnection > >>> ("jdbc:derby:myDB;shutdown=true");') > >>> > >>> > >>> Regards, > >>> -- > >>> Kristian > >>> > > > >>> > >>> > >>> > >> > >> -- > >> View this message in context: > >> > http://old.nabble.com/can%27t-delete-my-DB-directory-tp28782490p28825037.html > >> Sent from the Apache Derby Users mailing list archive at Nabble.com. > >> > > > > > > -- > View this message in context: > http://old.nabble.com/can%27t-delete-my-DB-directory-tp28782490p28839396.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. > >
Re: Resetting ResultSet with first() or beforeFirst()
On 09.06.10 19:32, Pavel Bortnovskiy wrote: Hi: I am trying to reset a given ResultSet back to the first record, so that I could iterate through its records one more time, after it's already been done once. So, my code does something like this (it's pseudo-code, and in the real application these two passes are done in different parts of the application): // get ResultSet final ResultSet resultSet = ((PreparedStatement) m_statement).executeQuery(); final int columnCount = resultSet.getMetaData().getColumnCount(); // First Pass through the data while (resultSet.next()) { for (int i = 1; i <= columnCount; i++) { if (resultSet.wasNull()) { Hi, I don't know of this issue is present in your actual code, but note that ResultSet.wasNull must be called *after* a getter method has been called. From the JavaDocs: "Reports whether the last column read had a value of SQL NULL. Note that you must first call one of the getter methods on a column to try to read its value and then call the method wasNull to see if the value read was SQL NULL." doSomethingWithNULL(); } else { doSomethingWithObject(resultSet.getObject(i)); Assuming wasNull was called after the getter method, the second getObject above may raise an exception in Derby. The restriction that you can call a getter only once for a given column is limited to some data types (i.e. BLOB and CLOB) and some getters (getXXXStream, getObject for the data types mentioned). Just thought I'd mention it :) Regards, -- Kristian } } } // Reset ResultSet - but it generates SQLException: The 'beforeFirst()' method is only allowed on scroll cursors resultSet.beforeFirst(); // Second Pass through the data while (resultSet.next()) { for (int i = 1; i <= columnCount; i++) { if (resultSet.wasNull()) { doSomethingElseWithNULL(); } else { doSomethingElseWithObject(resultSet.getObject(i)); } } } However, resultSet.beforeFirst() generates an exception: java.sql.SQLException: The 'beforeFirst()' method is only allowed on scroll cursors. 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.EmbedResultSet.checkScrollCursor(Unknown Source) at org.apache.derby.impl.jdbc.EmbedResultSet.beforeFirst(Unknown Source) Caused by: java.sql.SQLException: The 'beforeFirst()' method is only allowed on scroll cursors. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 8 more Could someone please help me out to understand what may be done incorrectly and what would be the right approach to enable two-pass logic through the RecordSet? Thanks, Pavel. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Java exception: '-1: java.lang.ArrayIndexOutOfBoundsException' error
Hi Kristian, Is it possible to give me a time frame for when this issue is likely to be fixed. Thanks & Regards, Devi. -- View this message in context: http://old.nabble.com/Java-exception%3A-%27-1%3A-java.lang.ArrayIndexOutOfBoundsException%27-error-tp28678738p28840567.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: can't delete my DB directory
On 09.06.10 03:32, oldmhe wrote: The OS is Windows XP. Since I'm using the embedded driver, I don't think it's possible to check what process is hanging on to the file. I.e., I believe it's the same process as my program. When my program exits, I'm able to delete the directory manually. Hi, It would still be useful to see which files the process has open handles to. If there is no tool readily available for doing this, a crude way would be to run the partly successful delete and then just post a listing of the files that are left in the database directories. Regarding your last question, my program does shutdown explicitly (as shown in my original post). Yes, you are shutting down the Derby engine, but not the specific database explicitly. An engine shutdown should shut down all booted databases, but it would be nice to rule out a bug in this area. Regards, -- Kristian Since posting, I found a solution, but I don't understand why it works. Below is a simplification of what the program does: 1. load the embedded driver set AutoCommit to false connect to the DB engine, and create a DB create tables load the tables with data, and commit all work 2. using SELECT, read some data records, and create an output file. 3. shutdown the DB engine 4. try to delete the DB directory (and all files and subdirectories) exit With regard to my initial post, Step 4 fails to delete all the files and directories (it's able to delete most of them). However: 1. If I omit Step 2 (the reading of the DB), Step 4 succeeds. 2. Or, if I add "xxx.commit()" between Step 2 and Step 3, then Step 4 succeeds. It seems that a commit() is needed even though Step 2 makes no change to the DB. This is contrary to my expectations for two reasons: a) Since Step 2 is a read-only operation, I don't see why commit() is needed. b) Even if a commit() is needed, the shutdown shouldease all DB resources (and not hang on to any files). Any thoughts? Kristian Waagan-4 wrote: Hello, What operating system are you using? Are you able to use the operation system's proper tool to check which process (if any) is hanging on to the file? (i.e. pfiles or lsof) Also, do you see the same behavior if you in addition shut down the database explicitly? (i.e. 'DriverManager.getConnection("jdbc:derby:myDB;shutdown=true");') Regards, -- Kristian
Re: can't delete my DB directory
On Windows the Process Explorer tool from Microsoft can be used to determine which file handles are open, and what process is holding them: http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx Brett On Thu, Jun 10, 2010 at 6:24 PM, Kristian Waagan wrote: > On 09.06.10 03:32, oldmhe wrote: > >> >> The OS is Windows XP. >> >> Since I'm using the embedded driver, I don't think it's possible to check >> what process is hanging on to the file. I.e., I believe it's the same >> process as my program. When my program exits, I'm able to delete the >> directory manually. >> > > Hi, > > It would still be useful to see which files the process has open handles > to. If there is no tool readily available for doing this, a crude way would > be to run the partly successful delete and then just post a listing of the > files that are left in the database directories. > > > >> Regarding your last question, my program does shutdown explicitly (as >> shown >> in my original post). >> > > Yes, you are shutting down the Derby engine, but not the specific database > explicitly. > An engine shutdown should shut down all booted databases, but it would be > nice to rule out a bug in this area. > > > Regards, > -- > Kristian > > >> Since posting, I found a solution, but I don't understand why it works. >> >> Below is a simplification of what the program does: >> >> 1. load the embedded driver >> set AutoCommit to false >> connect to the DB engine, and create a DB >> create tables >> load the tables with data, and commit all work >> >> 2. using SELECT, read some data records, and create an output file. >> >> 3. shutdown the DB engine >> >> 4. try to delete the DB directory (and all files and subdirectories) >> exit >> >> With regard to my initial post, Step 4 fails to delete all the files and >> directories (it's able to delete most of them). >> >> However: >> >> 1. If I omit Step 2 (the reading of the DB), Step 4 succeeds. >> >> 2. Or, if I add "xxx.commit()" between Step 2 and Step 3, then Step 4 >> succeeds. >> >> It seems that a commit() is needed even though Step 2 makes no change to >> the >> DB. This is contrary to my expectations for two reasons: >> >> a) Since Step 2 is a read-only operation, I don't see why commit() is >> needed. >> >> b) Even if a commit() is needed, the shutdown shouldease all DB >> >> resources (and not hang on to any files). >> >> Any thoughts? >> >> >> Kristian Waagan-4 wrote: >> >>> >>> Hello, >>> >>> What operating system are you using? >>> Are you able to use the operation system's proper tool to check which >>> process (if any) is hanging on to the file? >>> (i.e. pfiles or lsof) >>> >>> Also, do you see the same behavior if you in addition shut down the >>> database explicitly? >>> (i.e. 'DriverManager.getConnection("jdbc:derby:myDB;shutdown=true");') >>> >>> >>> Regards, >>> -- >>> Kristian >>> >>> >>> >>> >>> >> >
Re: Resetting ResultSet with first() or beforeFirst()
Kristian: thank you for being so observant. Actually, it was a programmatic typo on my part, as I just tried to type the pseudo-code into the email. My production code does follow the described pattern: final public Object getValue(ResultSet resultSet) throws SQLException { final Object object = resultSet.getObject(m_columnIdx); if (resultSet.wasNull()) { return null; } return object; } /**/ final Object object = resultSet.getObject(i); String string; if (object == null || resultSet.wasNull()) { string = m_nullValueIndicator; } else { switch (columnTypes[i]) { /**/ while (resultSet.next()) { tableBuilder.append(""); for (int i = 1; i <= columnCount; i++) { final String string = resultSet.getString(i); final String value = (string == null || resultSet.wasNull() ? " " : string.trim()); tableBuilder.append("").append(value).append(""); } tableBuilder.append("\n"); } Much appreciated, Pavel. Kristian Waagan Sent by: kristian.waa...@sun.com 06/10/2010 04:59 AM Please respond to "Derby Discussion" To derby-user@db.apache.org cc Subject Re: Resetting ResultSet with first() or beforeFirst() On 09.06.10 19:32, Pavel Bortnovskiy wrote: > > Hi: > > I am trying to reset a given ResultSet back to the first record, so that > I could iterate through its records one more time, after it's already > been done once. So, my code does something like this (it's pseudo-code, > and in the real application these two passes are done in different parts > of the application): > > // get ResultSet > final ResultSet resultSet = ((PreparedStatement) > m_statement).executeQuery(); > final int columnCount = resultSet.getMetaData().getColumnCount(); > // First Pass through the data > while (resultSet.next()) { > for (int i = 1; i <= columnCount; i++) { > if (resultSet.wasNull()) { Hi, I don't know of this issue is present in your actual code, but note that ResultSet.wasNull must be called *after* a getter method has been called. From the JavaDocs: "Reports whether the last column read had a value of SQL NULL. Note that you must first call one of the getter methods on a column to try to read its value and then call the method wasNull to see if the value read was SQL NULL." > doSomethingWithNULL(); > } else { > doSomethingWithObject(resultSet.getObject(i)); Assuming wasNull was called after the getter method, the second getObject above may raise an exception in Derby. The restriction that you can call a getter only once for a given column is limited to some data types (i.e. BLOB and CLOB) and some getters (getXXXStream, getObject for the data types mentioned). Just thought I'd mention it :) Regards, -- Kristian > } > } > } > // Reset ResultSet - but it generates SQLException: The 'beforeFirst()' > method is only allowed on scroll cursors > resultSet.beforeFirst(); > // Second Pass through the data > while (resultSet.next()) { > for (int i = 1; i <= columnCount; i++) { > if (resultSet.wasNull()) { > doSomethingElseWithNULL(); > } else { > doSomethingElseWithObject(resultSet.getObject(i)); > } > } > } > > > However, resultSet.beforeFirst() generates an exception: > > java.sql.SQLException: The 'beforeFirst()' method is only allowed on > scroll cursors. > 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.EmbedResultSet.checkScrollCursor(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedResultSet.beforeFirst(Unknown Source) > Caused by: java.sql.SQLException: The 'beforeFirst()' method is only > allowed on scroll cursors. > at > org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown > Source) > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown > Source) > ... 8 more > > Could someone please help me out to understand what may be done > incorrectly and what would be the right approach to enable two-pass > logic through the RecordSet? > > Thanks, > > Pavel. > > > > Jefferies archives and monitors outgoing and incoming e-mail. The > contents of this email, including any attachments, are confidential to > the ordinary user of the email address to which it was addressed. If you > are not the addressee of this email you may not copy, forward, disclose > or otherwise use it or any part of it in any form whatsoever. This email > may be produced at the request of regulators or in connection with civil > litigation. Jefferies accepts no liability for any errors or om