Обсуждение: UPDATE cell from text file

This has got to be easy but I am having a hard time finding the right
docs.

What I want to do is analogous to:

UPDATE website SET content='contents of a big ass file that I dont want to
type on the command line' where filename='index.html';

How can I do this?

Can I do this without writing a seperate shell and/or perl script?

Also, is there a stdio stream that I can use to pipe data into SQL
commands? Man, If I had this I could get a lot done in a hurry.

FWIW, I am not looking for COPY-ing delimited data from a flat file into a
table. I don't want to make new rows. I am trying to bring the contents of
an entire file into an existing single cell (namely content) of a table.

Am I going to be stuck just putting pointers to files in my database? Ugg,
I really want ALL my data in one place.

Please CC this address.

Thank You,     | http://students.washington.edu/jcwells
Jason Wells    | "Those who would trade freedom for security deserve neither
        | freedom nor security." - Benjamin Franklin

On Sun, 24 Oct 1999, Jason C. Wells wrote:

> This has got to be easy but I am having a hard time finding the right
> docs.
>
> What I want to do is analogous to:
>
> UPDATE website SET content='contents of a big ass file that I dont want to
> type on the command line' where filename='index.html';
>
> How can I do this?
>
> Can I do this without writing a seperate shell and/or perl script?
>
> Also, is there a stdio stream that I can use to pipe data into SQL
> commands? Man, If I had this I could get a lot done in a hurry.
>
> FWIW, I am not looking for COPY-ing delimited data from a flat file into a
> table. I don't want to make new rows. I am trying to bring the contents of
> an entire file into an existing single cell (namely content) of a table.
>
> Am I going to be stuck just putting pointers to files in my database? Ugg,
> I really want ALL my data in one place.
>

Just out of curiosity, what's the drawback of a perl script here?  It
would be pretty trivial.

    ....
    open BAF, "$ARGV[0]" or die "Can't open $ARGV[0]: $!";
    $contents = "";
    while (read, BAF, $buf, 10240) { $contents .= $buf; }
    $sth = $dbh->prepare("UPDATE website
                             SET content = ?
                           WHERE filename = ?");
    $sth->execute($content, $ARGV[1]) or die $DBI::errstr;
    ....

--
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com

On Sat, 23 Oct 1999, Bob Kline wrote:

>Just out of curiosity, what's the drawback of a perl script here?  It
>would be pretty trivial.
>
>    ....
>    open BAF, "$ARGV[0]" or die "Can't open $ARGV[0]: $!";
>    $contents = "";
>    while (read, BAF, $buf, 10240) { $contents .= $buf; }
>    $sth = $dbh->prepare("UPDATE website
>                             SET content = ?
>                           WHERE filename = ?");
>    $sth->execute($content, $ARGV[1]) or die $DBI::errstr;

Thank you for your reply.

The drawback of perl is that I use it so often that I have to relearn it
from scratch every time I use it. :( In fact, my perl is so weak, I cannot
read your script without opening up the book. I will make good use of your
reply.

I was hoping there was an SQL construct but I half suspected there was
not.

Thank You,     | http://students.washington.edu/jcwells
Jason Wells    | "Those who would trade freedom for security deserve neither
        | freedom nor security." - Benjamin Franklin

On Oct 24, Jason C. Wells mentioned:

> This has got to be easy but I am having a hard time finding the right
> docs.
>
> What I want to do is analogous to:
>
> UPDATE website SET content='contents of a big ass file that I dont want to
> type on the command line' where filename='index.html';
>
> How can I do this?

In the psql interface this is not easily possible (read: impossible) short
of some sort of copy&paste as of this date. A mechanism which should get
this done (I hope) will appear in the new version, which, coincidentally,
will appear sometime next week, if you want to give it a shot.

Another issue to consider is whether all your data will fit into a row,
which will be false if "big ass" > BLCKSZ (8192 unless you changed it). In
that case you will have to use the large object interface for which there
is no mechanism in psql yet, but -- you guessed it -- there will be one
next week :)

    -Peter

--
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

On Sun, 24 Oct 1999, Peter Eisentraut wrote:

>which will be false if "big ass" > BLCKSZ (8192 unless you changed it). In

I eagerly await next week.

It must be funny/odd/difficult for so many of you communicating by the
internet to read the colloquialisms that Americans like me presume
everyone understands. :)

Thank You,     | http://students.washington.edu/jcwells
Jason Wells    | "Those who would trade freedom for security deserve neither
        | freedom nor security." - Benjamin Franklin

At 01:03 AM 25-10-1999 +0000, Jason C. Wells wrote:
>On Sun, 24 Oct 1999, Peter Eisentraut wrote:
>
>>which will be false if "big ass" > BLCKSZ (8192 unless you changed it). In
>
>I eagerly await next week.
>
>It must be funny/odd/difficult for so many of you communicating by the
>internet to read the colloquialisms that Americans like me presume
>everyone understands. :)

Nah we get MTV too :).

Cheerio,

Link.