Karl & Betty Schendel wrote:

I notice that an Improved Data Load is a "High-Priority Project Awaiting
Resources" (http://community.ingres.com/wiki/Improved_Data_Load). There
is very little description but the title sounds good.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Karl & Betty Schendel wrote:
http://manuals.sybase.com/onlinebook...View/1806;hf=0

http://www.freetds.org/reference/a00287.html

I think you're describing Sybase's bcp utility. It loads text files into
tables; it uses the table metadata to drive client-side conversion. Like
COPY, the bits on the wire are row images.

A lot of the code has been written already, for FreeTDS. Just a SMP.

If you don't like the unlovely syntax of Sybase's utility, here's an idea
for a better one:

http://www.schemamania.org/dbstreams/dbcat.pdf

--jkl

James K. Lowden wrote:

First off, I do like the idea of a command-line utility to do this job.
But am I right in thinking the focus of FreeTDS is on developing the
libTDS API, and that dbcat is just a sample implementation? It's not
intended to be a comprehensive or endlessly versatile tool?

As it stands I can see how adding a tds_convert_uuid() function to the
API might be quite straight-forward.

But we are still left with the problem that neither Ingres nor dbcat
(for example) know that a particular field in a file is intended to be
treated as a UUID to be converted to the 16 byte binary form. There
needs to be some means to request conversions like that. I see the need
for a moderately complex language to specify that kind of thing. Zany
though the COPY...FROM syntax looks, it is not impossibly far from the
sort of thing that is probably needed.

Another comment is that in an ideal world I would want to use the same
syntax to load a file into a table whether I am using a utility or the
sql monitor. That's not an insurmountable problem--I'm just noting the
requirement.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Roy Hann wrote:
Basically, yes. The part of libtds that does data conversion is
protocol-independent. I was just pointing out that text->binary
conversions exist for a large subset of what any such tool would need.

dbcat is a sample application, but not for FreeTDS per se. I've done some
work on a C++ database library (based on std::iostreams). dbcat is my
demonstration project for that library. Not that I'm asking for help, but
if Ingres were added to the list of supported servers, you'd get dbcat for
free! :-)

Yes and no. The datatypes of the target table can be ascertained from the
metadata returned by "select * from tablename where 0=1". In the simplest
case, the file is mapped 1:1 to the columns. When the target column is a
UUID, the tds_convert_uuid() function is used.

I find that to be enough. Any self-respecting unixhead these days can use
Perl or somesuch to prepare a "table-ready" file i.e., one line per row,
one field on the line for each column in the row. Doing more requires, as
you say, quite a bit of description. Sybase can use a "format file" to
describe the field-column relationship. To me, it's more trouble to
understand it than it's worth.

Regards,

--jkl

James K. Lowden wrote:

Yes, but my point is that even Ingres doesn't know the column contains
UUIDs.

UUIDs are 128 bit values that are stored as a BYTE(16) column
(usually), so that is all Ingres knows about the column. What makes
this a problem when loading data from the text file I've been given is
that the human-readable 36 character string representation of UUIDs
has been used (e.g. 'f703c440-b35c-01d5-8637-00805fc13ce5', see
http://www.ietf.org/rfc/rfc4122 for more info). I wanted a way to
convert it to the binary form during loading. It is exactly analagous
to having a string like '16-jan-2009' in my text file and wanting to
load it into a DATE column, except in that case Ingres would know it's
a DATE so it can tell a utility what the utility would need to know to
do the appropriate interpretation.

I want a way to tell the utility what Ingres can't.

(UUIDs is just one case. I can easily imagine others.)

BTW, I am now loading the string into a CHAR(36) column and then doing
an update using uuid_from_char() to load the BYTE(16) column. It works
fine; it's just inelegant.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Roy Hann wrote:
Oy! I missed that. Thank you for the the lesson.

Just to noodle: I wonder if there's value in adding that as a type, if
only as an alias? The system wouldn't have to do anything different, but
if the putative datatype IIAPI_UUID_TYPE were exposed through
IIapi_getDescriptor(), surely it would serve more purposes than just this
one.

Short of that, and to work with the many systems that lack the datatype --
all of them to date -- the utility could read a configuration file.
That's what Sybase's utility does. Each line in the configuration file
would describe the target column's datatype. And while it's at it, it
might as well map the column order to re-arrange the fields as need be.

On reflection ISTR Ingres is open source again these days, and
src/common/aif/aip/apicnvrt.c appears to be the proper home for your
char->UUID conversion routine....

Regards,

--jkl