Solved

How to export a blob field to Windows folder?

Posted on 2010-09-24
5
2,172 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The author took my hints and arrived at a solution, then asks a rhetorical question....
0
 
LVL 1

Author Closing Comment

by:miyahira
Comment Utility
Didn't get response after my last comment.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now