I've got an ASCII data file from an external source that I want to load
into a table.

One of the fields is a char representation of a Version 1 UUID that
looks like f703c440-b35c-01d5-8637-00805fc13ce5 (i.e. what you'd get
from calling uuid_to_char(...)). I want to load it into a BYTE(16)
attribute (i.e. in binary form) using COPY...FROM.

I am starting to think I am out of luck with this. Any suggestions
other than creating a table with an extra CHAR(36) column to accept the
char representation and doing an update?

--
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.

I'm surprised Ingres won't do the conversion itself - there's nothing
in the manuals (yes, I'm sad, I looked!) to say otherwise...

Sounds like it'd probably be best to do it the way you suggest, a nice
little script would take care of that quickly.

Sorry I can't be any more help (do I get points for checking the
manual though?!)

Sounds to me like a nice little community project, i.e. the ability to pass
data through functions during a COPY statement.

Peter Gale

2009/1/13 <seanjand (AT) googlemail (DOT) com>



--
Peter Gale
pgale61 (AT) gmail (DOT) com

seanjand (AT) googlemail (DOT) com wrote:

It can't do the conversion itself because unlike, say, a date in a DATE
column, a UUID would be stored in a generic BYTE(16) column, so the
server has no clue that the string has to be parsed and converted to a
UUID (in the manner of uuid_from_char(...)).

What is needed is a syntax extension to the COPY...FROM format
specification, to request the conversion. As far as I can tell there is
no such syntax.

Sure. :-) Have two in fact; they're small.

--
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.

Peter Gale wrote:

Yeah, I was thinking that. If my day job slacks off I might just get
serious about it. Or not.

If someone else wanted to take it on, I'd suggest a full-blown data
transformation utility. :-)

--
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.

On Jan 13, 2009, at 11:08 AM, Peter Gale wrote:

Yikes. Good luck with that one.

Anything is possible in theory, but COPY is particularly intractable
because
all formatting is done on the client end, not the server end. The
server
sees bulk-copy style binary rows. (That is why COPY via the API looks
more like a bag of bolts than an SQL statement; the API only takes
care of the relatively complicated client/server handshaking, and the
user program gets to do all the row by row work.)

On the client side, you might be able to add formats, but adding the
ability to execute arbitrary code would add a whole new layer to the
existing COPY architecture. A format that invokes a function call,
avoiding arbitrary expressions, might be within the realm of
possibility.
I'm not sure what the syntax would look like.

I don't want to discourage anyone from improving COPY, because
goodness knows it needs it. I just enjoy shooting down
unrealistic expectations. :-)

Karl

Karl & Betty Schendel wrote:

And I'd rather read a 20 line outline of why it's heroes' work than
spend a couple of days finding out the hard way then giving up. :-)

--
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.

On Jan 13, 2009, at 11:24 AM, Roy Hann wrote:

That's actually how Datallegro did it at one point, although I
don't know about the "full-blown" part. Someone wrote a text
to binary formatter that was meant to sit in front of fastload.
I think it was relatively basic though. (I never had anything
to do with the convert utility, so my knowledge is limited.)

A reasonably complete data transformer that glued itself onto
the core bits of the COPY statement (perhaps even using the API)
would seem to be a good way to approach the general problem.
That way you wouldn't be tied to the existing COPY syntax,
nor the existing COPY libq implementation.

Karl

Hi,

I need to copy out a large table and after 2-3 hours, the session gets killed.

I see this in the errlog.log.
E_GC100A_AUTH_EXPIRED Authentication certificate has expired.

Where do I set the expiry time for the Authentication certificate ?

Andre

On Jan 13, 2009, at 1:16 PM, Laframboise, André wrote:

Dunno why this should happen, but ... look for
ii.$.gcf.mech.ingres.expiration_time

and set it to a nice big number.

Karl