Thread: Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
From
"Aitor Gil Martin"
Date:
Hi,
I,ve got a clients table in PostgreSQL. Each client has different documents (more than 7.000 files in total in different extensions JPG, XLS,DOC,PDF…) stored in a bytea field in another PostgreSQL Table. My intention is to create a form using Microsoft Access (or some other software) to be able to manage those files (Add more files, modify files or delete files).
What should I do in order Access could understand (open) each file or store new files?
Regards,
Aitor
Re: Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
From
Vincent Veyron
Date:
Le vendredi 07 juin 2013 à 11:35 +0200, Aitor Gil Martin a écrit : > Hi, > > I,ve got a clients table in PostgreSQL. Each client has different > documents (more than 7.000 files in total in different extensions JPG, > XLS,DOC,PDF…) stored in a bytea field in another PostgreSQL Table. My > intention is to create a form using Microsoft Access (or some other > software) to be able to manage those files (Add more files, modify > files or delete files). > > What should I do in order Access could understand (open) each file or > store new files? I do this from a navigator now, but the general idea is : use this table structure: create table ( id_document serial, content bytea, extension text) In your form, display a list of links, one for each file, pointing to a directory you can write to, with the file's id and extension. So if file id is 7001, extension is pdf, path is : your/directory/7001.doc Write the file's data to disk, obviously using the same name for the file as in the link, once the user clicks on the link, using the on_click event : file will be served to the user. -- Salutations, Vincent Veyron http://marica.fr/site/demonstration Progiciel de gestion des sinistres assurance et des dossiers contentieux pour le service juridique
Re: Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
From
"Aitor Gil Martin"
Date:
Ok. I got the general idea. Thanks Vincent. My next question is: How do I convert the bytea content stored in Postgresql into the real file so I can put it in a local directory? (and theopposite process: store a file on a path to a postgresql database) I know this could be more an access issue but I'm quite newbie... Regards, -----Mensaje original----- De: Vincent Veyron [mailto:vv.lists@wanadoo.fr] Enviado el: viernes, 07 de junio de 2013 14:41 Para: Aitor Gil Martin CC: pgsql-general@postgresql.org Asunto: Re: [GENERAL] Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form Le vendredi 07 juin 2013 à 11:35 +0200, Aitor Gil Martin a écrit : > Hi, > > I,ve got a clients table in PostgreSQL. Each client has different > documents (more than 7.000 files in total in different extensions JPG, > XLS,DOC,PDF...) stored in a bytea field in another PostgreSQL Table. My > intention is to create a form using Microsoft Access (or some other > software) to be able to manage those files (Add more files, modify > files or delete files). > > What should I do in order Access could understand (open) each file or > store new files? I do this from a navigator now, but the general idea is : use this table structure: create table ( id_document serial, content bytea, extension text) In your form, display a list of links, one for each file, pointing to a directory you can write to, with the file's id and extension. So if file id is 7001, extension is pdf, path is : your/directory/7001.doc Write the file's data to disk, obviously using the same name for the file as in the link, once the user clicks on the link, using the on_click event : file will be served to the user. -- Salutations, Vincent Veyron http://marica.fr/site/demonstration Progiciel de gestion des sinistres assurance et des dossiers contentieux pour le service juridique
Re: Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
From
Pavel Stehule
Date:
Hello 2013/6/7 Aitor Gil Martin <agmartin@getxo.net>: > Ok. I got the general idea. Thanks Vincent. > My next question is: > How do I convert the bytea content stored in Postgresql into the real file so I can put it in a local directory? (and theopposite process: store a file on a path to a postgresql database) > I know this could be more an access issue but I'm quite newbie... > Regards, some useful links, you should to use lo_import, lo_exports functions. http://snipplr.com/view/62576/convert-oid-to-bytea/ http://postgresql.1045698.n5.nabble.com/GENERAL-Large-Object-to-Bytea-Conversion-td1870627.html http://www.postgresql.org/docs/9.1/static/lo-funcs.html Regards Pavel > > -----Mensaje original----- > De: Vincent Veyron [mailto:vv.lists@wanadoo.fr] > Enviado el: viernes, 07 de junio de 2013 14:41 > Para: Aitor Gil Martin > CC: pgsql-general@postgresql.org > Asunto: Re: [GENERAL] Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form > > Le vendredi 07 juin 2013 à 11:35 +0200, Aitor Gil Martin a écrit : >> Hi, >> >> I,ve got a clients table in PostgreSQL. Each client has different >> documents (more than 7.000 files in total in different extensions JPG, >> XLS,DOC,PDF...) stored in a bytea field in another PostgreSQL Table. My >> intention is to create a form using Microsoft Access (or some other >> software) to be able to manage those files (Add more files, modify >> files or delete files). >> >> What should I do in order Access could understand (open) each file or >> store new files? > > I do this from a navigator now, but the general idea is : > > use this table structure: > > create table ( > id_document serial, > content bytea, > extension text) > > In your form, display a list of links, one for each file, pointing to a > directory you can write to, with the file's id and extension. So if file > id is 7001, extension is pdf, path is : your/directory/7001.doc > > Write the file's data to disk, obviously using the same name for the > file as in the link, once the user clicks on the link, using the > on_click event : file will be served to the user. > > > -- > Salutations, Vincent Veyron > http://marica.fr/site/demonstration > Progiciel de gestion des sinistres assurance et des dossiers contentieux pour le service juridique > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
From
Vincent Veyron
Date:
Le vendredi 07 juin 2013 à 15:13 +0200, Aitor Gil Martin a écrit : > How do I convert the bytea content stored in Postgresql into the real file so I can put it in a local directory? (and theopposite process: store a file on a path to a postgresql database) > I know this could be more an access issue but I'm quite newbie... Search google groups' archives of comp.databases.ms-access for blob + write + disk Blob means binary large object, the equivalent of bytea, you'll find some code samples. -- Salutations, Vincent Veyron http://marica.fr/ Gestion des contrats, des contentieux juridiques et des sinistres d'assurance