Thread: Insert image into bytea question
Greetings, all.
This might be a stupid question, but I have not found one good post or article on the syntax for inserting an image into a PostgreSQL table bytea field on the Internet.
I am currently learning version 9.1, which I had read should work pretty well for inserting and retrieving images in the bytea format, but i don't know how. I have seen examples of the bytea_import command (which I couldn't get to work), and other posts that state that the image must be encoded using a "SELECT encode(XXXX)" statement in the query. I had originally intended to store the location of the image into a field in the database and just pull that reference from the table, and was hopeful when I saw that PostgreSQL can handle storing images directly, and that it may be even be the preferred method in some cases.
I have been looking online for a couple of hours and have left the Net about as confused as when I started! I think that some of the reason is that this appears to be a newer method of doing things, and I have been reading older information.
Any guidance that anyone can offer would be greatly appreciated.
George R.
This might be a stupid question, but I have not found one good post or article on the syntax for inserting an image into a PostgreSQL table bytea field on the Internet.
I am currently learning version 9.1, which I had read should work pretty well for inserting and retrieving images in the bytea format, but i don't know how. I have seen examples of the bytea_import command (which I couldn't get to work), and other posts that state that the image must be encoded using a "SELECT encode(XXXX)" statement in the query. I had originally intended to store the location of the image into a field in the database and just pull that reference from the table, and was hopeful when I saw that PostgreSQL can handle storing images directly, and that it may be even be the preferred method in some cases.
I have been looking online for a couple of hours and have left the Net about as confused as when I started! I think that some of the reason is that this appears to be a newer method of doing things, and I have been reading older information.
Any guidance that anyone can offer would be greatly appreciated.
George R.
groberge wrote > Greetings, all. > > This might be a stupid question, but I have not found one good post or > article on the syntax for inserting an image into a PostgreSQL table > bytea field on the Internet. > > I am currently learning version 9.1, which I had read should work pretty > well for inserting and retrieving images in the bytea format, but i > don't know how. I have seen examples of the bytea_import command (which > I couldn't get to work), and other posts that state that the image must > be encoded using a "SELECT encode(XXXX)" statement in the query. I had > originally intended to store the location of the image into a field in > the database and just pull that reference from the table, and was > hopeful when I saw that PostgreSQL can handle storing images directly, > and that it may be even be the preferred method in some cases. > > I have been looking online for a couple of hours and have left the Net > about as confused as when I started! I think that some of the reason is > that this appears to be a newer method of doing things, and I have been > reading older information. > > Any guidance that anyone can offer would be greatly appreciated. > George R. The easiest way is to do things the same way you would store and retrieve a number or text in your programming language of choice; but of course send the image bytes variable contents instead of the text variable contents. There should not be any need to encode/decode if your language provides a decent API. You likely do not want to use the large object API (lo_*) but deciding between data and links requires application and usage knowledge. The main trade-off is database size (and thus ease of backup/upgrade, etc...) and administrative overhead synchronizing the data library and the database. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Insert-image-into-bytea-question-tp5802084p5802085.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of George Roberge
>Sent: Wednesday, April 30, 2014 5:39 PM
>To: pgsql-novice@postgresql.org
>Subject: [NOVICE] Insert image into bytea question
>Greetings, all.
>This might be a stupid question, but I have not found one good post or article on the syntax for inserting an image into a PostgreSQL table bytea field on the Internet.
>I am currently learning version 9.1, which I had read should work pretty well for inserting and retrieving images in the bytea format, but i don't know how. I have seen examples of the bytea_import command (which I couldn't get to work), and other posts that state that the image must be encoded using a "SELECT encode(XXXX)" statement in the query. I had originally intended to store the location of the image into a field in the database and just pull that reference from the table, and was hopeful when I saw that PostgreSQL can handle storing images directly, and that it may be even be the preferred method in some cases.
>I have been looking online for a couple of hours and have left the Net about as confused as when I started! I think that some of the reason is that this appears to be a newer method of doing things, and I have been reading older information.
I wrote a couple functions to convert the byte data to and from a string in VS 2010 .Net and then store it to a text field in the table. I’m sure there is a more “proper” way to do this but like you, I had a hard time finding any useful examples.
Imports System.Text.RegularExpressions
*
*
*
Public Function ToBase64(ByVal data() As Byte) As String
If data Is Nothing Then Throw New ArgumentNullException("data")
Return Convert.ToBase64String(data)
End Function
Public Function FromBase64(ByVal base64 As String) As Byte()
If base64 Is Nothing Then Throw New ArgumentNullException("base64")
Return Convert.FromBase64String(base64)
End Function
My issue was that I retrieve and store data in XML so I must always deal with text.
Regards,
Chris
On April 30, 2014 9:31:10 PM EDT, David G Johnston <david.g.johnston@gmail.com> wrote:
groberge wroteGreetings, all.
This might be a stupid question, but I have not found one good post or
article on the syntax for inserting an image into a PostgreSQL table
bytea field on the Internet.
I am currently learning version 9.1, which I had read should work pretty
well for inserting and retrieving images in the bytea format, but i
don't know how. I have seen examples of the bytea_import command (which
I couldn't get to work), and other posts that state that the image must
be encoded using a "SELECT encode(XXXX)" statement in the query. I had
originally intended to store the location of the image into a field in
the database and just pull that reference from the table, and was
hopeful when I saw that PostgreSQL can handle storing images directly,
and that it may be even be the preferred method in some cases.
I have been looking online for a couple of hours and have left the Net
about as confused as when I started! I think that some of the reason is
that this appears to be a newer method of doing things, and I have been
reading older information.
Any guidance that anyone can offer would be greatly appreciated.
George R.
The easiest way is to do things the same way you would store and retrieve a
number or text in your programming language of choice; but of course send
the image bytes variable contents instead of the text variable contents.
There should not be any need to encode/decode if your language provides a
decent API.
You likely do not want to use the large object API (lo_*) but deciding
between data and links requires application and usage knowledge. The main
trade-off is database size (and thus ease of backup/upgrade, etc...) and
administrative overhead synchronizing the data library and the database.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Insert-image-into-bytea-question-tp5802084p5802085.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Ok, very good, thanks to everyone who replied! I was hoping to see a methos within PostgreSQL itself (via INSERT or something), but I can test out a method using PHP instead, since that's the ultimate destination, anyhow.
Thank you again,
George R.
--
George Roberge
www.andforthelamb.org
Free Software Foundation Member #11716
Computers are like air conditioners: both stop working
when you open Windows.
Ok, very good, thanks to everyone who replied! I was hoping to see a methos within PostgreSQL itself (via INSERT or something), but I can test out a method using PHP instead, since that's the ultimate destination, anyhow.
Not sure how you expected to be able to issue an INSERT w/o some kind of client interface. If you need to do this via "psql" (i.e. the official PostgreSQL client) there are ways - though I am not that familiar with them. Typically I'd store the data into a file then do something like:
\set variable_name `cat filename.ext`
INSERT INTO tbl (col) VALUES (func_if_needed(:'variable_name'));
In which case you might or might not find it easier to use some text intermediary in order to avoid difficulty with "cat".
However, I assumed you were using something like PHP or Java and that you would most likely want to have any solution be within the same framework...
David J.
View this message in context: Re: Insert image into bytea question
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On 2014-05-01 19:39, David G Johnston wrote:
Ok, very good, thanks to everyone who replied! I was hoping to see a methos within PostgreSQL itself (via INSERT or something), but I can test out a method using PHP instead, since that's the ultimate destination, anyhow.Not sure how you expected to be able to issue an INSERT w/o some kind of client interface. If you need to do this via "psql" (i.e. the official PostgreSQL client) there are ways - though I am not that familiar with them. Typically I'd store the data into a file then do something like:\set variable_name `cat filename.ext`INSERT INTO tbl (col) VALUES (func_if_needed(:'variable_name'));In which case you might or might not find it easier to use some text intermediary in order to avoid difficulty with "cat".However, I assumed you were using something like PHP or Java and that you would most likely want to have any solution be within the same framework...David J.
View this message in context: Re: Insert image into bytea question
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
I was going to test the table using exactly that. Since I'm still relatively fresh into PostgreSQL and rusty with my web coding (and PHP), I wanted to make sure that I was going to insert data properly into the table the "easy way" before I started writing code to do it using a web interface. After looking into it and finding next to no documentation (syntax examples), it may be that it's better to "just do it" with PHP to start.
George R.
Hi all,
I'm new to PostgreSQL and generally to database engines
(background from MS Access but I started to learn PostgreSQL).
My question is about database architecture.
There is a small private school with 2 branches in 2 seperate locations.
Their database will manage student and teacher informations (contact informations, payments etc.)
Since a student or a teacher can go to both these two locations, database must be shared.
But, there might be failures to internet connections from time to time and I wonder how can we maintain data integrity after connection established.
Syncronization speed is not crucial. Mostly their data stays internal . I want (ideally):
2 Mirrored databases in 2 location, after an update they try to synchronise, if a connection occurs try later.
How can I do this with PostgreSQL?
Thanks,
Yuksel
Jov
blog: http:amutu.com/blog
2014-05-02 9:39 GMT+08:00 David G Johnston <david.g.johnston@gmail.com>:
Ok, very good, thanks to everyone who replied! I was hoping to see a methos within PostgreSQL itself (via INSERT or something), but I can test out a method using PHP instead, since that's the ultimate destination, anyhow.Not sure how you expected to be able to issue an INSERT w/o some kind of client interface. If you need to do this via "psql" (i.e. the official PostgreSQL client) there are ways - though I am not that familiar with them. Typically I'd store the data into a file then do something like:\set variable_name `cat filename.ext`
psql variable can only hold text string,so only "cat" do not work here if the ext is jpeg or some other binary file extension.But if we encode the binary to base64 text first,then it will "work" somehow.
I wrote a blog about input and output the bytea as binary file in psql:
INSERT INTO tbl (col) VALUES (func_if_needed(:'variable_name'));In which case you might or might not find it easier to use some text intermediary in order to avoid difficulty with "cat".However, I assumed you were using something like PHP or Java and that you would most likely want to have any solution be within the same framework...David J.
View this message in context: Re: Insert image into bytea question
Quick hint: Please do not start a new thread in a mailing list by replying to an old one and changing the title. A good email client can tell. ;) --As of May 2, 2014 4:01:33 PM +0300, Yuksel Pinarbasi is alleged to have said: > > Hi all, > I'm new to PostgreSQL and generally to database engines > (background from MS Access but I started to learn PostgreSQL). > > My question is about database architecture. > There is a small private school with 2 branches in 2 separate locations. > Their database will manage student and teacher informations (contact > informations, payments etc.) > Since a student or a teacher can go to both these two locations, database > must be shared. > But, there might be failures to internet connections from time to time > and I wonder how can we maintain data integrity after connection > established. > > Syncronization speed is not crucial. Mostly their data stays internal . I > want (ideally): > 2 Mirrored databases in 2 location, after an update they try to > synchronise, if a connection occurs try later. > > How can I do this with PostgreSQL? First question: Does the database need to be local to both locations? Could you use a front-end that connects to the same backend database from both campuses? (Like a web front end?) That's your easiest solution. Next question is *if* that's not acceptable, do both locations need to be able to modify the database? Or can one be the master and the other just a replication? If both locations need to be able to modify the data, you are going to have to have some way to deal with conflicts that arise. Those questions will help you define your options. There's a section of the handbook then dealing with different solutions: <http://www.postgresql.org/docs/9.3/static/high-availability.html> Going with your above description, and assuming there isn't a way to simplify your requirements, the page above points to the Bucardo project as a solution you'll want to look at: <http://bucardo.org/wiki/Bucardo> Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
> First question: Does the database need to be local to both locations? > Could you use a front-end that connects to the same backend database from > both campuses? (Like a web front end?) That's your easiest solution. Main concern is to be able to continue to work during a network failure. Internet connections are not very reliable in the area. > Next question is *if* that's not acceptable, do both locations need to be > able to modify the database? Yes > Going with your above description, and assuming there isn't a way to > simplify your requirements, the page above points to the Bucardo project > as a solution you'll want to look at: > <http://bucardo.org/wiki/Bucardo> Thanks, my online research pointed me to the same direction also. Yuksel
On Fri, May 2, 2014 at 3:32 PM, Yuksel Pinarbasi <yukselp@gmail.com> wrote:
Main concern is to be able to continue to work during a network failure.First question: Does the database need to be local to both locations? Could you use a front-end that connects to the same backend database from both campuses? (Like a web front end?) That's your easiest solution.
Internet connections are not very reliable in the area.YesNext question is *if* that's not acceptable, do both locations need to be able to modify the database?Thanks, my online research pointed me to the same direction also.Going with your above description, and assuming there isn't a way to simplify your requirements, the page above points to the Bucardo project as a solution you'll want to look at:
<http://bucardo.org/wiki/Bucardo>
Yuksel
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
You're going down a rather complex path, especially for someone new to full RDBMS systems. Multi-master (as this is frequently called) is one of the most difficult problems to solve in database administration. A poor network between the two locations is going to make it even more difficult to manage. The conflict resolution for both sites editing the same data is more complex of a problem than I think you realize right now. If there's any chance you can stick with a single, more reliable offsite location that both locations can access, I'd highly recommend starting there, perhaps in Amazon EC2 or RDS. Then one site being down isn't a bottleneck to the other.
Keith
Thanks Keith.
Those two branches will mostly be handling independent data.
Only time to time they will need to see a record that the other branch has been created.
I don't think there will be ant conflict. Because they will not read or edit the same row in a close time frame.
I don't think there will be ant conflict. Because they will not read or edit the same row in a close time frame.
It seems a Multi Master- Asynchronous replication will be the right solution in the end.
Anyway, I will start with a single database and work my way around later.
Yuksel
You're going down a rather complex path, especially for someone new to full RDBMS systems. Multi-master (as this is frequently called) is one of the most difficult problems to solve in database administration. A poor network between the two locations is going to make it even more difficult to manage. The conflict resolution for both sites editing the same data is more complex of a problem than I think you realize right now. If there's any chance you can stick with a single, more reliable offsite location that both locations can access, I'd highly recommend starting there, perhaps in Amazon EC2 or RDS. Then one site being down isn't a bottleneck to the other.Keith
If they both have independent data, that's not quite so bad. I'd just caution you that at some people a request may come in that they want to edit each other's data. I'd make sure and establish a firm plan for that before implementing a solution. Either clearly state it won't be allowed or figure out exactly how it will be handled. Bucardo has some conflict resolution management built in, but you have to be sure that's the method of resolution you want to implement.
If this is a relatively small database, and they will be kept as independent databases, bucardo can be a bit of a chore to set up and maintain. I've been working on a smaller (hopefully easier to use) logical table replication extension. https://github.com/omniti-labs/mimeo
On Fri, May 2, 2014 at 5:04 PM, Yuksel Pinarbasi <yukselp@gmail.com> wrote:
Thanks Keith.Those two branches will mostly be handling independent data.Only time to time they will need to see a record that the other branch has been created.
I don't think there will be ant conflict. Because they will not read or edit the same row in a close time frame.It seems a Multi Master- Asynchronous replication will be the right solution in the end.Anyway, I will start with a single database and work my way around later.YukselYou're going down a rather complex path, especially for someone new to full RDBMS systems. Multi-master (as this is frequently called) is one of the most difficult problems to solve in database administration. A poor network between the two locations is going to make it even more difficult to manage. The conflict resolution for both sites editing the same data is more complex of a problem than I think you realize right now. If there's any chance you can stick with a single, more reliable offsite location that both locations can access, I'd highly recommend starting there, perhaps in Amazon EC2 or RDS. Then one site being down isn't a bottleneck to the other.Keith