Обсуждение: how to extract data from bytea so it is be used in blob for mysql database

On 13/05/2009 21:56, George Kao wrote:
> I used pg_dump to extract the data from my database with bytea fields
> (having pictures) in plain text format. I encoutered some problems to
> restore those bytea data to the blobs field in a Mysql database. Any advice?

A bit more detail would be nice.... we're only guessing otherwise.

- Platform(s)?
- Version?
- Commands executed?
- Error messages?
- Log entries?
- ....

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

The platform is WinXP. I have database with fields of bytea in Posgresql
8.1. The objective was to transfer the entire databasae (schema + data)
to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to extract the
schema and data. With some minor touch-up of the sql script generated, I
am able to run the script with mysql.exe to store the database except
for the bytea data...


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Raymond
O'Donnell
Sent: Wednesday, May 13, 2009 5:52 PM
To: George Kao
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to extract data from bytea so it is be used
in blob for mysql database

On 13/05/2009 21:56, George Kao wrote:
> I used pg_dump to extract the data from my database with bytea fields
> (having pictures) in plain text format. I encoutered some problems to
> restore those bytea data to the blobs field in a Mysql database. Any
advice?

A bit more detail would be nice.... we're only guessing otherwise.

- Platform(s)?
- Version?
- Commands executed?
- Error messages?
- Log entries?
- ....

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie Galway Cathedral Recitals:
http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 14/05/2009 14:18, George Kao wrote:
> The platform is WinXP. I have database with fields of bytea in Posgresql
> 8.1. The objective was to transfer the entire databasae (schema + data)
> to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to extract the
> schema and data. With some minor touch-up of the sql script generated, I
> am able to run the script with mysql.exe to store the database except
> for the bytea data...

OK, grand - but you still haven't said what problems you're running
into. We can't really help until you give a bit more detail.

Have you read up on the bytea type in the docs? When you do a
text-format dump, the data in bytea columns is dumped in escaped octal,
so you need to handle this somehow when reloading into MySQL.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Thanks for the replying.

The problem has been narrowed down to restoring a particular table with
Bytea field to Mysql database.
The data in escaped octal is not acceptable. Is there a way to dump the
bytea from posgresql database in hex format? I think it is acceptable by
mysql.exe.

George
-----Original Message-----
From: Raymond O'Donnell [mailto:rod@iol.ie]
Sent: Thursday, May 14, 2009 2:14 PM
To: George Kao
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to extract data from bytea so it is be used in
blob for mysql database

On 14/05/2009 14:18, George Kao wrote:
> The platform is WinXP. I have database with fields of bytea in
> Posgresql 8.1. The objective was to transfer the entire databasae
> (schema + data) to Mysql 5.1. I used the pg_dump from Posgresql 8.1 to
> extract the schema and data. With some minor touch-up of the sql
> script generated, I am able to run the script with mysql.exe to store
> the database except for the bytea data...

OK, grand - but you still haven't said what problems you're running into. We
can't really help until you give a bit more detail.

Have you read up on the bytea type in the docs? When you do a text-format
dump, the data in bytea columns is dumped in escaped octal, so you need to
handle this somehow when reloading into MySQL.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On 14/05/2009 20:07, George Kao wrote:
> The data in escaped octal is not acceptable. Is there a way to dump the
> bytea from posgresql database in hex format? I think it is acceptable by

No, I'm fairly sure that you can't do it directly in the pg_dump output.
However, you could maybe use the functions here -

  http://www.postgresql.org/docs/8.3/static/functions-binarystring.html

- to write something that will output what you need.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

The functions, encode, converts the binary string to HEX representation. It
does seem to work as mysql's blob accepts the HEX data representation. Any
program that you are aware of for dumping the data to the SQL script (i.e.
INSERT INTO ...) while having the option to convert those binary string to
hex?

-----Original Message-----
From: Raymond O'Donnell [mailto:rod@iol.ie]
Sent: Thursday, May 14, 2009 3:15 PM
To: george.kao@group-upc.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to extract data from bytea so it is be used in
blob for mysql database

On 14/05/2009 20:07, George Kao wrote:
> The data in escaped octal is not acceptable. Is there a way to dump
> the bytea from posgresql database in hex format? I think it is
> acceptable by

No, I'm fairly sure that you can't do it directly in the pg_dump output.
However, you could maybe use the functions here -

  http://www.postgresql.org/docs/8.3/static/functions-binarystring.html

- to write something that will output what you need.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------