Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of gcaddd-derby-dev@m.gmane.org
 designates 80.91.229.2 as permitted sender)
To: derby-dev@db.apache.org
From: David Pickett <David.Pickett@PHLX.com>
Subject: Table Import Suggestions
Date: Mon, 18 Jul 2005 18:49:29 +0000 (UTC)
Lines: 44
Message-ID: <loom.20050718T204505-523@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)
Sender: news <news@sea.gmane.org>

Originally, I posted this on Cloudscape's board, and was urged to send it on:

Using the table import for the first time, I had a few ideas where it could be 
more friendly:

- I had a good old fashioned fixed length file, but had to cut it up with other 
tools to import it. A non-delimited format would be nice. Access and Excel, for 
instance, have no problem with this.

- My delimited file tools trim away empty trailing fields, but Cloudscape 
insisted on having them, so I had to modify my tools. Access and Excel, for 
instance, have no problem with this.

- I suppose it is a subtle characteristic of Cloudscape, as with several other 
RDBMS, that ',,' is NULL and I suppose ', ,' is blank. Maybe there should be an 
option to have ',,' be considered blank, especially if the column is Not Null. 
(I haven't tried, maybe for Not Null it accomodates you, but I would be 
surprised if it did.) Admittedly, signalling NULL on import and export is a 
challenge often met through the 'blank is one space' solution.

- It'd be really helpful if the record blowing up the import was *always* 
printed in the error message.

- It'd be nice if I could skip the first line, which is often a header.

- It'd be really handy if the input file could be a pipe, especially stdin. 
Then, I could stream unlimited amounts of data from the source to the 
destination, editing it on the fly.  I might just write a 'pipe fitting' that 
chops off N lines to a temp file and imports in pieces.

- Input file names seem to have to be absolute, which is a bit fussy. BTW, 
Cloudscape documentation generally has a weakness about saying whether things 
like the file name are for the client or the server (s/b the client, here). I 
see too many client-server examples using localhost, and wonder if they work as 
presented on 2 boxes. It is as if the writer assumes the client-server concept 
only works using one box. Many shops have nothing on the RDBMS server box but 
the server(s); it makes tuning and administration easier. Localhost will never 
work for those users. I do feel the documents are great for an open source 
product, but there's always room at the top, and my target here is their 
improvement.

- I was glad to find that quoting was optional. The instructions are a bit 
terse. Hopefully, it supports the doubled doublequotes and doublequoted commas 
of formal CSV better then my Win2000 version of MS Access!