pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting .png file into bytea column
6+ messages / 5 participants
[nested] [flat]
* Inserting .png file into bytea column
@ 2015-11-19 14:07 Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>
0 siblings, 2 replies; 6+ messages in thread
From: Ferrell, Denise D CTR NSWCDD, H11 @ 2015-11-19 14:07 UTC (permalink / raw)
To: pgsql-admin
Good Morning All,
I am using PostgreSQL 9.3 on Linux Rehat...
I'm trying to insert an image (.png format) into a table of flags. I've tried the following but keep getting errors.
CREATE TABLE FLAGS (country_code text, flag bytea);
INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
Get the following error:
ERROR: absolute path not allowed
*********ERROR*************
ERROR: absolute path not allowed
SQL State: 42501
Any assistance would be greatly appreciated.
Denise Ferrell
Attachments:
smime.p7s (application/pkcs7-signature, 5.5K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inserting .png file into bytea column
@ 2015-11-19 14:19 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2015-11-19 14:19 UTC (permalink / raw)
To: Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>; pgsql-admin
On 19/11/2015 16:07, Ferrell, Denise D CTR NSWCDD, H11 wrote:
> Good Morning All,
>
> I am using PostgreSQL 9.3 on Linux Rehat...
>
> I'm trying to insert an image (.png format) into a table of flags. I've tried the following but keep getting errors.
>
> CREATE TABLE FLAGS (country_code text, flag bytea);
>
> INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
>
> Get the following error:
> ERROR: absolute path not allowed
> *********ERROR*************
> ERROR: absolute path not allowed
> SQL State: 42501
>
> Any assistance would be greatly appreciated.
http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE
use |pg_read_binary_file(filename text [, offset bigint, length bigint])| as documented
>
> Denise Ferrell
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inserting .png file into bytea column
@ 2015-11-19 15:09 Craig James <cjames@emolecules.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Craig James @ 2015-11-19 15:09 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>; pgsql-admin
On Thu, Nov 19, 2015 at 6:19 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
> On 19/11/2015 16:07, Ferrell, Denise D CTR NSWCDD, H11 wrote:
>
> Good Morning All,
>
> I am using PostgreSQL 9.3 on Linux Rehat...
>
> I'm trying to insert an image (.png format) into a table of flags. I've tried the following but keep getting errors.
>
> CREATE TABLE FLAGS (country_code text, flag bytea);
>
> INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
>
> Get the following error:
> ERROR: absolute path not allowed
> *********ERROR*************
> ERROR: absolute path not allowed
> SQL State: 42501
>
> Any assistance would be greatly appreciated.
>
>
>
> http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE
>
> use pg_read_binary_file(filename text [, offset bigint, length bigint])
> as documented
>
The documentation you linked to explains the problem:
The functions shown in Table 9-66 provide native access to files *on the
machine hosting the server*. Only files *within the database cluster
directory* and the log_directory can be accessed. *Use a relative path* for
files in the cluster directory, and a path matching the
log_directoryconfiguration setting for log files. Use of these functions is
restricted to superusers.
You can't access files on the client side, and on the server side you can
only access files that are internal to Postgres (e.g. inside
/data/postgres, or whatever path your Postgres uses for the database). And
only super-users can use this function at all because it allows access to
sensitive server files.
The easiest way to do this on the client side is to read the file into a
client-side variable and then use a prepared statement. In Perl, something
like this:
use DBI;
my $dbh = $dbi->connect(...);
my $photo;
open(PHOTO, "/home/flags");
binmode PHOTO;
while (<PHOTO>) {
$photo .= $_;
}
my $sth = $dbh->prepare("insert into flags(country_code, flag) values(?,
?)");
$sth->execute('AD', $photo);
Craig
> Denise Ferrell
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Non-DoD Source] Re: Inserting .png file into bytea column
@ 2015-11-19 15:20 Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>
parent: Craig James <cjames@emolecules.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ferrell, Denise D CTR NSWCDD, H11 @ 2015-11-19 15:20 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
Thank you!
-----Original Message-----
From: Craig James [mailto:cjames@emolecules.com]
Sent: Thursday, November 19, 2015 10:09 AM
To: Achilleas Mantzios
Cc: Ferrell, Denise D CTR NSWCDD, H11; pgsql-admin@postgresql.org
Subject: [Non-DoD Source] Re: [ADMIN] Inserting .png file into bytea column
On Thu, Nov 19, 2015 at 6:19 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 19/11/2015 16:07, Ferrell, Denise D CTR NSWCDD, H11 wrote:
Good Morning All,
I am using PostgreSQL 9.3 on Linux Rehat...
I'm trying to insert an image (.png format) into a table of flags. I've tried the following but keep getting errors.
CREATE TABLE FLAGS (country_code text, flag bytea);
INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
Get the following error:
ERROR: absolute path not allowed
*********ERROR*************
ERROR: absolute path not allowed
SQL State: 42501
Any assistance would be greatly appreciated.
http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE
use pg_read_binary_file(filename text [, offset bigint, length bigint]) as documented
The documentation you linked to explains the problem:
The functions shown in Table 9-66 provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directoryconfiguration setting for log files. Use of these functions is restricted to superusers.
You can't access files on the client side, and on the server side you can only access files that are internal to Postgres (e.g. inside /data/postgres, or whatever path your Postgres uses for the database). And only super-users can use this function at all because it allows access to sensitive server files.
The easiest way to do this on the client side is to read the file into a client-side variable and then use a prepared statement. In Perl, something like this:
use DBI;
my $dbh = $dbi->connect(...);
my $photo;
open(PHOTO, "/home/flags");
binmode PHOTO;
while (<PHOTO>) {
$photo .= $_;
}
my $sth = $dbh->prepare("insert into flags(country_code, flag) values(?, ?)");
$sth->execute('AD', $photo);
Craig
Denise Ferrell
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------
Attachments:
smime.p7s (application/pkcs7-signature, 5.5K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inserting .png file into bytea column
@ 2015-11-19 18:35 Dorian Machado <dorian599@gmail.com>
parent: Craig James <cjames@emolecules.com>
1 sibling, 0 replies; 6+ messages in thread
From: Dorian Machado @ 2015-11-19 18:35 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>; pgsql-admin
Hello
Try to use a TEXT column for the flags in the table.
You musto to encode the image has base64 and then to retrive the image to
the web you must to decode de content of the field.
It is a simple a easy way to store and retrive images from databases
For example
*For HTML*
<img alt="Embedded Image"
src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAEEAAABBCAIAAAABlV4SAAAABGdBTUEAAK/INwWK6QAAABl0RVh0
U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAABJFSURBVHja1F . ." />
*For PHP*
echo '<img src="data:image/gif;base64,' . base64_decode($data) . '" />';
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inserting .png file into bytea column
@ 2015-11-19 19:32 Thomas Kellerer <spam_eater@gmx.net>
parent: Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>
1 sibling, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2015-11-19 19:32 UTC (permalink / raw)
To: pgsql-admin
Ferrell, Denise D CTR NSWCDD, H11 schrieb am 19.11.2015 um 15:07:
> Good Morning All,
>
> I am using PostgreSQL 9.3 on Linux Rehat...
>
> I'm trying to insert an image (.png format) into a table of flags. I've tried the following but keep getting errors.
>
> CREATE TABLE FLAGS (country_code text, flag bytea);
>
> INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
>
> Get the following error:
> ERROR: absolute path not allowed
> *********ERROR*************
> ERROR: absolute path not allowed
> SQL State: 42501
>
> Any assistance would be greatly appreciated.
>
> Denise Ferrell
>
If the image is on the computer where the SQL client is running, pg_read_file() can not be used
because it is reading files on the server, not the client.
If you are open to a different SQL client, the one I am maintaining - SQL Workbench/J - can
do precisely what you want:
INSERT INTO flags VALUES ('AD', {$blobfile=/home/flags});
This will read the file from the computer where SQL Workbench is running:
But again: this syntax *only* works with SQL Workbench/J
http://www.sql-workbench.net/blob_example.html
You can also do this directly from within a result of a SELECT statement:
http://www.sql-workbench.net/BlobDisplay_png.html
Full disclosure: I am the author of that tool.
Thomas
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-11-19 19:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-19 14:07 Inserting .png file into bytea column Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>
2015-11-19 14:19 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2015-11-19 15:09 ` Craig James <cjames@emolecules.com>
2015-11-19 15:20 ` Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil>
2015-11-19 18:35 ` Dorian Machado <dorian599@gmail.com>
2015-11-19 19:32 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox