[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to export a blob field to Windows folder?

Posted on 2010-09-24
5
Medium Priority
?
2,390 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

649 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