Re: [OT] PostgreSQL: bytea help needed.

From: mlw (mlw_at_nospam.no)
Date: 02/03/05 

Date: Thu, 03 Feb 2005 16:43:29 -0500

Mike Cox wrote:

> Christopher Browne <cbbrowne@acm.org> writes:
>
>> Centuries ago, Nostradamus foresaw when Mike Cox <mikecoxlinux@yahoo.com>
>> would write:
>> > Does anyone know how to add data to a bytea in PostgreSQL? I googled
>> > the archives, and they mostly talk about PHP. I'd like to put binary
>> > data in the bytea from my local filesystem, but google comes up empty
>> > other than using PHP.
>>
>> Well, you draw the data in, and then append it to the field.
>
> Well this is elementary stuff. Lets say I wanted to pull a jpg into
> the database from my local filesystem for example.

My first reaction would be to say you are insane. Using a database as a file
system isn't its best used. I would store the file path and name and have a
separate volume or directory for the image. Especially if you are using
large images.

> How would what you
> just wrote help me in this regard? I already know how to append to a
> bytea from within the database and using an interface like JDBC, but
> what about the psql terminal?

You probably can't do it in psql unless you create a C function to read the
data in, parse it and escape the characters correctly.

>
>> tutorial=# create table ba1 (stuff bytea);
>> CREATE TABLE
>> tutorial=# insert into ba1 values ('foo');
>> INSERT 2749794 1
>> tutorial=# update ba1 set stuff = stuff || ' more stuff';
>> UPDATE 1
>> tutorial=# select * from ba1;
>> stuff
>> ----------------
>> foo more stuff
>> (1 row)
>>
>> Your application needs to be in some language; that presumably needs
>> to be where the data is loaded from disk into a variable. You would
>> then push the variable into the update query.
>>
>> When you were googling, you mostly found PHP details; can you suggest
>> details about what language you're planning to use instead, be that
>> Perl, C, Python, Java, or such?
>
> Well I wanted to grab binary data off my filesystem directly from the
> psql terminal. Something like lo_import that is used for BLOBS or the
> COPY command which is used to pull text data in.

Again, you may want to create a C sql function to do this as I don't think
the psql program includes this functionality.

Something like this:

create or replace function bytealoader(varchar)
        returns bytea
        as 'mylib.so','bytealoader'
        language 'c';

< This function can be made more efficient and better, but this is just
typing off the top of my head. >

Datum bytealoader(PG_FUNCTION_ARGS)
{
        if(fcinfo->nargs >= 1)
        {
                text *tp = PG_GETARG_TEXT_P(0);
                char *filename = (char *)VARDATA(tp);
                FILE *fi = fopen(filename);
                if(fi)
                {
                        long file_len;
                        fseek(fi, 0, SEEK_END);
                        file_len = ftell(fi);
                        fseek(fi,0,SEEK_SET);
                        p = palloc(file_len + VARHDRSZ);
                        VARATT_SIZEP(p) = file_len;
                        fread(VARDATA(p), file_len, 1, fi);
                        fclose(fi);
                        PG_RETURN_BYTEA(p);
                }
        }
        PG_RETURN_NULL;
}

>
> I know how to pull data from Java and those other interfaces, but then
> I'd have to get JDBC or install Apache, PHP etc. I want to be able
> to, like I posted, be able to do this within the psql terminal.

The psql terminal is designed to use a text console. Dumping binary image
data to a text screen will make a lot of noise can put a lot of jibberish
on the screen.

Relevant Pages

  • Re: Parameter dialog box pops up
    ... > I need to update my Access database from an Excel list. ... The question is how do I append each field from ... > append query for each table from my TableN and run them separately). ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database Connection Problem. Please Help
    ... I have a process which first pulls one time all application IDs from a database and stores them in a table. ... The problem is that after processing about 100 records, using SqlDataReader, my database connection will starts failing. ... I've been trying to find a way to pull all details for all records at once, instead of one at a time but I have not been able to do that. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: form to email document
    ... >> append the Session ID for that user Session to the file name. ... >> name uploaded.This can come from a simple text file or a database. ... >>> I was hoping for something like the procedure used to upload a form, ...
    (microsoft.public.frontpage.programming)
  • Re: Populating Databse
    ... that wasn't an Access database for sure! ... Same with SSN and so on. ... you can use the Append query; ... to append some clearly unreasonable date into the datefield (which you ...
    (microsoft.public.access.tablesdbdesign)
  • Database Connection Problem. Please Help
    ... database and stores them in a table. ... The problem is that after processing about 100 records, using SqlDataReader, ... my database connection will starts failing. ... I've been trying to find a way to pull ...
    (microsoft.public.dotnet.languages.vb)