Harbour and PostgresSQL

[Marek Olszewski "MOL"]

Hi!

I need to place my binary files (pictures, pdf) into postgresql table.

Do sb. know how to realize that?

Greetings, Marek

[jgt]

I would only keep the location of the file in the database as in:

      replace picture with "/u/docs/year/month/abcde.pdf"

or

      replace picture with "e:\docs\year\month\abcde.pdf"

You can open it in a Windows environment with:

      run "explore.exe picture"

without knowing which program should be used to open the file.

[Marek Olszewski "MOL"]

But I had to view context of file on another computers. Storing filenames causes that files should be placed in shared folder to get access from another machine

[jgt]

Will this help:

https://www.postgresql.org/message-id/VisenaEmail.1b.33b3c4fcc89334b.17dd76de494@tc7-visena

[jgt]

PS.  I searched for "blob" on the postgresql website.

[Francesco Perillo]

I'd say don't do it... but too few informations...

How many files? How big? Are they updated or never modified?

What about backups?

Anyway, use 2 tables, one for the index, the other for the blob. 

I deleted other suggestions... too few informations about the problem.

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/dcfb0c81-1759-4577-afb7-aeb691b6996dn%40googlegroups.com.

[Marek Olszewski "MOL"]

I need to store few thousands of scanned documents or pdfs in a year.

It's hard to say the number of it.

[Francesco Perillo]

And you want to store them in postgres???  I'm not with you... :-)))

Filesystem, or a document db... s3?

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/47f3f64d-6797-443a-a885-85ecc33935c4n%40googlegroups.com.

[Appliserver]

I use the filesystem capabilities (and no SQL). The table has the file name and sudirectory. Be careful, a single directory under NTFS can handle about 17000 files IIRC, not mentioning the slowness to access such a crowded directory. So I create a subdir for every day, named after the date() (dtos(date)), thus the limit is 17000 documents per day. The file also is opened by the OS with whatever PDF reader you have as default viewer. Even in networked environment the technique shows an acceptable access time. The program is quite efficient, in other words.

I fear using blobs and similar solutions. With this technique, if the database goes nuts, you'll have all your original files intact and you could reconstruct the database starting from the documents dir/subdirs (a mess, but feasible).

Dan

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/47f3f64d-6797-443a-a885-85ecc33935c4n%40googlegroups.com.

[Marek Olszewski "MOL"]

I also feel fear storing documents in database. But my client  wants to realize it in this way. I'm trying to collect opinions and solutions of this problem

[jgt]

I have been using the technique that I outlined in my earlier post for over 25 years, although the environment is different (Foxpro for Unix on SCO,  with Samba and Windows workstations (Win95 to Win10).)

We store documents by year\month\day and try to keep less that 255 entries per directory.  Currently there are 300gb of documents over the last 7 years.  We keep a variety of documents: pdf, word docs. emails as eml. These documents are accessed by employees in a call center while on the phone.

I have another smaller site where the documents are primarily invoices, these are stored in directories based on the last two digits of the invoice number [mod(invoice_number,100)].   These are available to the customer via a website, and internally to view and re-send copies of invoices.

[Auge & Ohr]

hi,

the Question is not "if" Marek want to store in Database or "which" Database.
the Question is "how" to store a BLOB in PostgreSQL

---

what if Customer want to "crypt" Document and save it in Database ?

normal you must "share" Folder so everybody can access it.

but when save in Database and "crypt" Data it is "save"

so there are Reason when store "crypt" Document into Database

---

i use HEX Format to save a BLOB in PostgreQSL
harbour have

// c:\hmg.3.4.4\HARBOUR\include\hbcompat.ch

   #xtranslate hb_HexToStr([<c,...>])      => HexToStr(<c>)
   #xtranslate hb_StrToHex([<c,...>])      => StrToHex(<c>)

when INSERT into Type "bytea" i use "Prefix" \x + hb_StrToHex(CRYPT(BLOB))
when "read" from SQL i have to "remove Prefix" \x and than hb_HexToStr(UNCRYPT(BLOB))

Jimmy

[Gerald Drouillard]

I like using the recno() or an unique ID field in the database to use to store the image.  If the ID is 12345, then the image would be saved as:

IMAGES/1/2/3/4/5.JPG

If you have many images for that ID then you could do create a directory with the ID:

IMAGES/1/2/3/4/5/ONE.JPG

IMAGES/1/2/3/4/5/EXAMPLE.JPG

IMAGES/1/2/3/4/5/PO.JPG

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/27571896-5924-4bbd-8d31-b91f11f97162n%40googlegroups.com.

[Marek Olszewski "MOL"]

Hi!

I resolved my problem with inserting data do pgsql table. My application was built in ANSI, but database created as dafault in UTF-8 encoding. It causes problems with insert and update. Changing database to encoding='WIN1250' everything works OK.