RE: syntax for column names
Sounds like you are using an ordinary SQL92 identifier. Convert it to a delimited identifier by placing quotes around it and I think you'll be ok. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 06, 2007 10:05 AM To: derby-user@db.apache.org Subject: syntax for column names Hi I have a legacy DB2 table which has a special character like '#' in the column name like ACCT#. I am using derby for in-memory unit testing. Derby does not allow me to have a special character like '#' in the column name in my create table statement. Is there anyway to escape the special character so that I can have special characters in column names. I would really appreciate any help in this regard. Thanks Saad Khawaja == Confidentiality Notice: The information contained in and transmitted with this communication is strictly confidential, is intended only for the use of the intended recipient, and is the property of Countrywide Financial Corporation or its affiliates and subsidiaries. If you are not the intended recipient, you are hereby notified that any use of the information contained in or transmitted with the communication or dissemination, distribution, or copying of this communication is strictly prohibited by law. If you have received this communication in error, please immediately return this communication to the sender and delete the original message and any copy of it in your possession. == The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you.
RE: Results portion
This question has bean asked several times already . . . Once by me ;-) The answer is NO. There is no way to retrieve rows in this fashion. I had to create an extra index column in my table in order to simulate this kind of behavior. Big pain in the . . . -Original Message- From: Sergey Zolotaryov [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 09, 2006 7:43 AM To: derby-user@db.apache.org Subject: Results portion Is there a way to retrieve a portion of results using Derby? For example, I want to get rows from 10 to 20 from a resultset. All dbs I know of, have this feature, smth like LIMIT n, m , or ROWS n TO m. Also is there a way to retrieve the total number of rows in last select? Thank you. The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you.
RE: Results portion
Using a scrollable cursor is a possibility. However, you incur a performance penalty, which in my experience, is quite severe if your result set is very large. Let's say you need to move the cursor to the last row in the result set. Derby will iterate through all the rows in the result set from the current position of the cursor until the last row is reached. In my application I felt that the amount of time the user was forced to wait while this took place was unacceptable. What Sergey, and several others would like to be able to do, is use a LIMIT command with an OFFSET modifier which will allow you to generate a result set that only has rows 10 - 20 in it. The result set does not contain rows 1 - 9, nor does it contain any rows after 20. This is what Sergey is asking for. This behavior is possible in databases like MySQL and Postgres, but of course those are not Java based embedded implementations. So, Sergey, if you execute your query using a scrollable cursor you can move the cursor to your desired offset and then read the desired number or rows (in this case you'll probably want to call setMaxRows() on your Statement object so you don't retrieve any additional rows that you don't need). You may find this to be perfectly acceptable for your needs. This approach didn't work well for me due to the rather large size of my result set. -Original Message- From: Michael Segel [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 09, 2006 1:00 PM To: 'Derby Discussion' Subject: RE: Results portion Hmmm... I realize its been a while since I've read Derby's manual, but doesn't Derby support Scrollable cursors? A scrollable cursor will allow you to run a query and to fetch specific rows from the result set. At a minimum, that is what Sergey is originally asking and which is supported by multiple RDBMSs. -Original Message- From: Fantry, John [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 09, 2006 10:00 AM To: Derby Discussion Subject: RE: Results portion This question has bean asked several times already . . . Once by me ;-) The answer is NO. There is no way to retrieve rows in this fashion. I had to create an extra index column in my table in order to simulate this kind of behavior. Big pain in the . . . -Original Message- From: Sergey Zolotaryov [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 09, 2006 7:43 AM To: derby-user@db.apache.org Subject: Results portion Is there a way to retrieve a portion of results using Derby? For example, I want to get rows from 10 to 20 from a resultset. All dbs I know of, have this feature, smth like LIMIT n, m , or ROWS n TO m. Also is there a way to retrieve the total number of rows in last select? Thank you. The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you. The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you.
How to monitor progress during data import
I am using Derby to import a large text file into a table. I would like to be able to display a progress dialog to the user while the import is processing. In order to provide a useful progress bar I need to be able to calculate the % complete of the import procedure. Does anyone know if this can be done? The table is locked during the import so I can't execute any SELECT COUNT(*) statements in a separate thread. Perhaps Derby outputs some statistics to a log file? I did stumble across the RunTimeStatistics attributein the Derby docs, but this attribute is only meaningful after the import is complete. I need insight into the import procedure while it's executing. Any ideas? Thanks, -John The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you.
RE: How to monitor progress during data import
I am importing the data using the built in system procedure SYSCS_UTIL.SYSCS_IMPORT_TABLE. This procedure takes the name of the file to import as an argument. Derby opens the file using whatever mechanism it wants. I don't have an opportunity to wrap the stream myself. -Original Message- From: Mike Matrigali [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 11:27 AM To: Derby Discussion Subject: Re: How to monitor progress during data import Andreas Korneliussen wrote: Fantry, John wrote: I am using Derby to import a large text file into a table. I would like to be able to display a progress dialog to the user while the import is processing. In order to provide a useful progress bar I need to be able to calculate the % complete of the import procedure. Does anyone know if this can be done? The table is locked during the import so I can't execute any SELECT COUNT(*) statements in a separate thread. Perhaps Derby outputs some statistics to a log file? I did stumble across the RunTimeStatistics attribute in the Derby docs, but this attribute is only meaningful after the import is complete. I need insight into the import procedure while it's executing. Any ideas? Have you tried running the SELECT COUNT (*) statement using transaction isolation level read-uncommitted ? Do note that derby must scan all the rows in the table to execute count(*), so checking on the progress using count(*) is a significant overhead depending on the size of the table. For your application it may be possible to layer a stream on top of the file which could tell your app how much has been read while otherwise just passing the bytes from the file to derby. Regards Andreas The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you.
Paginating result sets
I need some help figuring how to paginate result sets with Derby. Ihave scanned the list archives and have found one similar thread that never received a complete answer from May 2005. From what I understand this pagination behavior is possible in MySQL by using the 'LIMIT' query. This will allow me to select the first 100 rows, the second 100 rows, or any arbitrary chunk of rows that may be part of the result set. I understand that Derby does not support the LIMIT query. I also understand that the JDBC Statement class has two methods that are related (setFetchSize() and setMaxRows()), but they do not support the pagination behavior I desire. So let's say that a given SELECT statement will return 1000 rows. How do I create a SELECT statement that will only return me rows 600 - 699? Having spent a lot of time searching through Derby docs I have concluded that this is not possible in Derby, but I am hoping I am wrong. Does anyone know how to do this, or do I have to continue my search for an embedded database that supports LIMIT? Thanks, -John The information transmitted in this message is confidential and may be privileged. Any review, retransmission, dissemination, or other use of this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify Analogic Corporation immediately - by replying to this message or by sending an email to [EMAIL PROTECTED] - and destroy all copies of this information, including any attachments, without reading or disclosing them. Thank you.