Solved

How to export a blob field to Windows folder?

Posted on 2010-09-24
5
2,274 Views
Last Modified: 2012-05-10
Hello ,

My postgres table has a blob field that stores MS-Word files. I need to loop the table and export each blob field as a Word document to a Windows folder.

I'm using pgAdmin, and trying to do this:

SELECT lo_export(bl_mydocument, 'C:\x\MyDocument.doc')
  FROM MyTable
  where bl_field='514-2009';

Got this error:

ERROR:  function lo_export(bytea, "unknown") does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

********** Error **********

ERROR: function lo_export(bytea, "unknown") does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to add explicit type casts.

My Postgres database has this software version:
- PostgreSQL 7.4.2
- O/S: Conectiva Linux 10

Client:
pgAdmin Version 1.10.5, in Windows XP
0
Comment
Question by:miyahira
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:miyahira
ID: 33756156
Also, I'm trying to run that function using OID parameter:

SELECT lo_export(1202474, 'C:\x\MyDocument.doc')
  FROM MyTable
  where bl_field='514-2009';

but got error:


ERROR:  large object 1202474 does not exist

********** Error **********

ERROR: large object 1202474 does not exist
SQL state: 42704
0
 
LVL 22

Expert Comment

by:earth man2
ID: 33759771
SELECT bl_mydocument FROM MyTable where bl_field='514-2009';
SELECT bl_mydocument::oid FROM MyTable where bl_field='514-2009';
SELECT lo_export(bl_mydocument::oid, 'C:\x\MyDocument.doc') FROM MyTable where bl_field='514-2009';
SELECT lo_export(1202474:oid, 'C:\x\MyDocument.doc') ;

MyTable.bl_mydocument has probably been defined as bytea type where it is probably better off being an oid type.
0
 
LVL 1

Accepted Solution

by:
miyahira earned 0 total points
ID: 33761421
Thanks for the response. You are right, field bl_mydocument is bytea type, not oid type. Actually, I realized that lo_export function does not work for bytea types. At the end, I had to make a program with .net to extract all blobs.

I think that postgres has no function to export data from bytea types. Am I right?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 34416056
The author took my hints and arrived at a solution, then asks a rhetorical question....
0
 
LVL 1

Author Closing Comment

by:miyahira
ID: 34426481
Didn't get response after my last comment.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question