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
