473,855 Members | 1,986 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Picture with Postgres and Delphi

Hi !! Everybody
I am developing app using Delphi and I have a question:
I have to save pictures into my database. Each picture has 20 o 30k aprox.
What is the way more optimus?
That 's table will have 500000 records around. Somebody said the best way to
do that was encoder the picture to field bytea but I dont know about this.
Another way is save the path to the picture file but I dont like so much
because I need to write to disk by OS and have permission to write a
directory to OS.
What do u think??
Edwin Quijada

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 11 '05
48 11952
text types are limited to ~1 Gig depending on things like encoding and
what not, you might get as much as 2 gig per record.

On Wed, 10 Sep 2003, Derrick Betts wrote:
Is the size limit 8K for 'text' field types as well?

----- Original Message -----
From: "Guy Fraser" <gu*@incentre.n et>
To: <pg***********@ postgresql.org>
Sent: Wednesday, September 10, 2003 9:40 AM
Subject: Re: [GENERAL] Picture with Postgres and Delphi

What is the size limit of bytea, I thought it was 8K?

How do you dump your database when you have bytea, do you need to do a
binary dump?

What are you using to insert the binary data?

Thank you in advance.

Guy

Jonathan Bartlett wrote:
>For the education of me and maybe others too, why was
>that? i.e. what problems did you run into, that bytea avoids?
>
>
>

Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon


>_____________ _______________ ______
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>http://sitebuilder.yahoo.com
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #21
Is the size limit 8K for 'text' field types as well?

----- Original Message -----
From: "Guy Fraser" <gu*@incentre.n et>
To: <pg***********@ postgresql.org>
Sent: Wednesday, September 10, 2003 9:40 AM
Subject: Re: [GENERAL] Picture with Postgres and Delphi

What is the size limit of bytea, I thought it was 8K?

How do you dump your database when you have bytea, do you need to do a
binary dump?

What are you using to insert the binary data?

Thank you in advance.

Guy

Jonathan Bartlett wrote:
For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?


Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon

_____________ _______________ ______
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #22
> > What is the size limit of bytea, I thought it was 8K?

No limit that I've found. Some are several meg.
How do you dump your database when you have bytea, do you need to do a
binary dump?
Nope. pg_dump automagically escapes everything.
What are you using to insert the binary data?
Perl example:

my $COMPLETED_TEMP LATE_VARS_INSER T = <<EOF; insert into
completed_templ ate_vars (completed_temp late, name, value, binvalue) VALUES
(?, ?, ?, ?)
EOF

$sth = $dbh->prepare($COMPL ETED_TEMPLATE_V ARS_INSERT);
$value = undef;
$binvalue = $field->{BINANS};
$value = $field->{ANS} unless $binvalue;
$sth->bind_param(1 , $self->getOID);
$sth->bind_param(2 , $name);
$sth->bind_param(3 , $value);
$sth->bind_param(4 , $binvalue, DBI::SQL_BINARY );
$sth->execute || die("DBERROR:${ DBI::errstr}:") ;

Note that I explicityl set DBI::SQL_BINARY .

Now, for php, you do the following:

$logodata = pg_escape_bytea ($tmpdata);
$tmpsql = "update advertisements set $column_name = '$logodata'::by tea where object_id = $advertisement_ oid";
$tmp = $db->query($tmpsql) ;

I never got it to work with parameterized queries, but this works fine for
me. To select it back out, you need to do:

$q = $db->query("selec t teaser_logo_gif _image from advertisements where
object_id = ?::int8", array($_GET['advertisement']));
$row = $q->fetchrow();
$data = pg_unescape_byt ea($row[0]);

NOTE that many versions of PHP include pg_escape_bytea but NOT
pg_unescape_byt ea. Look in the docs to see which function appeared in
which version.

Jon

Jonathan Bartlett wrote:
>For the education of me and maybe others too, why was
>that? i.e. what problems did you run into, that bytea avoids?
>
>
>

Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon


>_____________ _______________ ______
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>http://sitebuilder.yahoo.com
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 11 '05 #23
"Derrick Betts" <De*****@griffl ink.com> writes:
Is the size limit 8K for 'text' field types as well?


There is no size limit (OK, a very very large one) on either "text" or
"bytea" in modern versions of Postgres.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #24
text types are limited to ~1 Gig depending on things like encoding and
what not, you might get as much as 2 gig per record.

On Wed, 10 Sep 2003, Derrick Betts wrote:
Is the size limit 8K for 'text' field types as well?

----- Original Message -----
From: "Guy Fraser" <gu*@incentre.n et>
To: <pg***********@ postgresql.org>
Sent: Wednesday, September 10, 2003 9:40 AM
Subject: Re: [GENERAL] Picture with Postgres and Delphi

What is the size limit of bytea, I thought it was 8K?

How do you dump your database when you have bytea, do you need to do a
binary dump?

What are you using to insert the binary data?

Thank you in advance.

Guy

Jonathan Bartlett wrote:
>For the education of me and maybe others too, why was
>that? i.e. what problems did you run into, that bytea avoids?
>
>
>

Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon


>_____________ _______________ ______
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>http://sitebuilder.yahoo.com
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #25
How do you deal with backing up the images? Right now i can remote
backup my filesystem using rsync to an offsite location many times a
day, only taking a very small amount of I/O, bandwidth and time.
Dealing with the backup scared me away from using postgres in the first
place. The idea of doing a 200gb dump multiple times a day on an
image database scares me. So does doing a vacuum on it. The I/O,
time and bandwidth required to do this is daunting.

Are there any suggestions on how to do incremental backups of the
images and any other suggestions on performance? In the future I'd
like to move some filesystem images to postgres to have a centralized
storage. It would make some things easier, but i'm not sure it's worth
the additional problems. Hopefully i'm imagining the problems.

--brian

On Tuesday, September 9, 2003, at 08:56 PM, Jonathan Bartlett wrote:
For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?


Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so
you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with
a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon

_______________ _______________ ____
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #26
On Wednesday 10 September 2003 17:17, Derrick Betts wrote:
Is the size limit 8K for 'text' field types as well?
What is the size limit of bytea, I thought it was 8K?


Not for some time now - the TOAST system (Tom Lane's work IIRC) means you can
store large text fields in a table. By large I mean MB large, not 64k or
something. I think there's a section on TOAST in the manuals.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 11 '05 #27
"Derrick Betts" <De*****@griffl ink.com> writes:
Is the size limit 8K for 'text' field types as well?


There is no size limit (OK, a very very large one) on either "text" or
"bytea" in modern versions of Postgres.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #28
Darko Prenosil wrote:

There is no need for manually storing files on filesystem, because large
objects are doing that for You. I am storing whole binary files in
blobs(synonym for large objects from some other platforms), and I do not
remember that I had a single problem with that. Do not forget that libpq has
great support for large objects, and you can store large object without
actually storing them on server filesystem, so You do not need any file
permissions on "upload directory" or something like that.
The pictures are prepared for web. Storing in files is faster from
that side. That system is calling db every 30 mins...
If You are using Delphi, there is great project called "Zeos objects", and
if I remember correctly it has support for large objects.

Zeos are useing a lot of memory...

Regards,
Micha³ Zaborowski (TeXXaS)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #29
How do you deal with backing up the images? Right now i can remote
backup my filesystem using rsync to an offsite location many times a
day, only taking a very small amount of I/O, bandwidth and time.
Dealing with the backup scared me away from using postgres in the first
place. The idea of doing a 200gb dump multiple times a day on an
image database scares me. So does doing a vacuum on it. The I/O,
time and bandwidth required to do this is daunting.

Are there any suggestions on how to do incremental backups of the
images and any other suggestions on performance? In the future I'd
like to move some filesystem images to postgres to have a centralized
storage. It would make some things easier, but i'm not sure it's worth
the additional problems. Hopefully i'm imagining the problems.

--brian

On Tuesday, September 9, 2003, at 08:56 PM, Jonathan Bartlett wrote:
For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?


Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so
you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with
a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon

_______________ _______________ ____
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #30

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
8710
by: warwick.poole | last post by:
I am interested in finding out about Enterprise scale Postgres installations and clustering, especially on Linux. Essentially I would like to know the possibility that Postgres can store the database data in a central location (ex: on a SAN fiber array) and have a cluster of machines sharing processor/RAM/IO bandwidth to do the application processing. Or perhaps there is another solution similar to what www.emicnetworks.com have...
18
5158
by: Joe Lester | last post by:
This thread was renamed. It used to be: "shared_buffers Question". The old thread kind of died out. I'm hoping to get some more direction by rephrasing the problem, along with some extra observations I've recently made. The core of the problem is that Postgres is filling up my hard drive with swap files at the rate of around 3 to 7 GB per week (that's Gigabytes not Megabytes) . At this rate it takes roughly two months to fill up my 40...
1
2453
by: eric_berlin | last post by:
I am writing a multithreaded application that has 6 threads each writing 5 frames per second video bitmaps to a different picture box. I have just found out that only the main UI thread is supposed to interact with Windows Fourms. What is the fastest way to have a non UI thread safely write a bit map to a picture box?
0
2708
by: NM | last post by:
Hello, I've got a problem inserting binary objects into the postgres database. I have binary objects (e.g. images or smth else) of any size which I want to insert into the database. Funny is it works for files larger than 8000 Bytes. If a file is less than 1000 Bytes I get the following message: Error message: --invalid input syntax for type oid: "\074\077......";
0
2283
by: varathasiva | last post by:
Dear All, I have one Delphi 7 application software.In that software i am using backend for PostgreSQL 8.1 with linux server fedura version.I am using this software through internet.After getting more data in PostgreSQL database server will be slow.In delphi 7 i am using data access component Zeos Lib.I cann't fund where is the problem.Please advice where is the problem.Advance thanks to all. Kindly Regards Siva
0
9903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9754
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10375
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7927
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7084
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5754
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4567
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3194
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.