Обсуждение: 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.