Display Bytea field

How thru a simple query can I make sure data matches and I can display it

On Thu, Jan 9, 2025 at 3:16 PM Andy Hartman <hartman60home@gmail.com> wrote:

On Thu, Jan 9, 2025 at 3:17 PM Andy Hartman <hartman60home@gmail.com> wrote:

How thru a simple query can I make sure data matches and I can display it

On Thu, Jan 9, 2025 at 3:16 PM Andy Hartman <hartman60home@gmail.com>
wrote:

I have migrated over a Table from Mssql that had an Image column I now
have it in Postgres

Did SSMS see the column, and then display thousands (or millions) of
images?

What you're going to need here is a GUI app (that can be a web app) which
"you" have programmed to know *that column is full of JPEG (or PNG or
whatever) images*, and so call the relevant decoding library then display
at the appropriate location in a window.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

could it be done using Powershell?

On Thu, Jan 9, 2025 at 3:25 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Sure. There's at least one Postgresql driver for PS, and Google says there
are 3rd party libraries to display images.

It's just a Simple Matter Of Programming!

On Thu, Jan 9, 2025 at 3:31 PM Andy Hartman <hartman60home@gmail.com> wrote:

could it be done using Powershell?

On Thu, Jan 9, 2025 at 3:25 PM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:

On Thu, Jan 9, 2025 at 3:17 PM Andy Hartman <hartman60home@gmail.com>
wrote:

How thru a simple query can I make sure data matches and I can display it

On Thu, Jan 9, 2025 at 3:16 PM Andy Hartman <hartman60home@gmail.com>
wrote:

I have migrated over a Table from Mssql that had an Image column I now
have it in Postgres

Did SSMS see the column, and then display thousands (or millions) of
images?

What you're going to need here is a GUI app (that can be a web app) which
"you" have programmed to know *that column is full of JPEG (or PNG or
whatever) images*, and so call the relevant decoding library then
display at the appropriate location in a window.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Maybe tools like DBeaver can help? It has a free trial period.

Andy Hartman <hartman60home@gmail.com> escribió:

On 1/9/25 12:17, Andy Hartman wrote:

How thru a simple query can I make sure data matches and I can display it

What you are facing:

1) Bytea stores binary strings in the formats specified here:

https://www.postgresql.org/docs/current/datatype-binary.html

2) The database has no 'knowledge' of what those binary strings
represent. They could be an image, a PDF, a spreadsheet file, etc.

3) To achieve what you want you need some sort of client code that can
take the binary strings and convert them to the correct output. Then you
can match and display.

On Thu, Jan 9, 2025 at 3:16 PM Andy Hartman <hartman60home@gmail.com
<mailto:hartman60home@gmail.com>> wrote:

I have migrated over a Table from Mssql that had an Image column I
now have it in Postgres

--
Adrian Klaver
adrian.klaver@aklaver.com

Andy Hartman wrote:

How thru a simple query can I make sure data matches and I can display it

On Thu, Jan 9, 2025 at 3:16 PM Andy Hartman <hartman60home@gmail.com> wrote:

I have migrated over a Table from Mssql that had an Image column I now
have it in Postgres

Within psql, the bytea field can be copied into a large object
with lo_from_bytea() [1]https://www.postgresql.org/docs/current/lo-funcs.html, and then the large object exported
into a local file with \lo_export [2]https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-LO-EXPORT

Alternatively, you could compare image checksums before and
after moving them into postgres. The advantage is that you
don't need to export or view any file, and you compare globally
all your images. If the checksums are identical, the data are identical.
On the MSSQL side, checksums can be computed with hashbytes()
as suggested in this stackoverflow answer: [3]https://stackoverflow.com/a/33256990/
On the postgres side, use functions like md5() or sha256()
directly on the bytea column.

[1]: https://www.postgresql.org/docs/current/lo-funcs.html

[2]: https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-LO-EXPORT
https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-LO-EXPORT

[3]: https://stackoverflow.com/a/33256990/

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On Fri, Jan 10, 2025 at 7:49 AM Daniel Verite <daniel@manitou-mail.org>
wrote:
[snip]

Alternatively, you could compare image checksums before and
after moving them into postgres. The advantage is that you
don't need to export or view any file, and you compare globally
all your images. If the checksums are identical, the data are identical.
On the MSSQL side, checksums can be computed with hashbytes()
as suggested in this stackoverflow answer: [3]
On the postgres side, use functions like md5() or sha256()
directly on the bytea column.

This is what I did when migrating Oracle xLOB columns to bytea. Had to use
upper(md5()).

I didn't suggest this earlier, since I don't know the details of MSSQL's
Image data type.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On 2025-01-09 21:31 +0100, Andy Hartman wrote:

could it be done using Powershell?

I use this: https://blog.cleverelephant.ca/2021/04/psql-binary.html
But I don't know if that translates to PowerShell.

--
Erik Wienhold

I used PS to pull the data from mssql to Postgres dumping data to csv. I
then used csv to load Postgres and the table that has Bytea

# Convert the image data to a base64 string -- powershell
$base64Image = [Convert]::ToBase64String($row.ImageSource)

AFter data was loaded the developer said in his app frontend that the Image
wouldn't open thru his code. -- I'm trying to get that code to help debug

He said the size of the array is 1368. from bytea The size coming from the
SQL-Server db is 46935 and the image correctly appears...

Could that be caused by my PS dump to csv process or maybe still a
code(frontend) issue..

Still trying to figure out using a single record if data loaded to the
bytea field matches the mssql record.

I tried to use the tool SimplySql to connect mssql to postgresql to
transfer data but it failed ...

any help would be appreciated..

On Fri, Jan 10, 2025 at 12:35 PM Erik Wienhold <ewie@ewie.name> wrote:

1. Do you still have the CSV file (or can you regenerate it from the
still-existing MSSQL DB)?
2. Did you load the base64 string into PG, or did you decode before loading
into PG?
3. A base64 string would be about 62KB. Either you did something wrong
when loading, or the programmer is doing something wrong.
4. When I migrated from Oracle LOBs to PB bytea, the Perl program ora2pg
generated CSV files with "hex" strings for those columns. They were
preceded by "\x", I think. They loaded directly into the PG database, with
the COPY command.

On Sat, Jan 11, 2025 at 6:05 AM Andy Hartman <hartman60home@gmail.com>
wrote:

I used PS to pull the data from mssql to Postgres dumping data to csv. I
then used csv to load Postgres and the table that has Bytea

# Convert the image data to a base64 string -- powershell
$base64Image = [Convert]::ToBase64String($row.ImageSource)

AFter data was loaded the developer said in his app frontend that the
Image wouldn't open thru his code. -- I'm trying to get that code to help
debug

He said the size of the array is 1368. from bytea The size coming from the
SQL-Server db is 46935 and the image correctly appears...

Could that be caused by my PS dump to csv process or maybe still a
code(frontend) issue..

Still trying to figure out using a single record if data loaded to the
bytea field matches the mssql record.

I tried to use the tool SimplySql to connect mssql to postgresql to
transfer data but it failed ...

any help would be appreciated..

On Fri, Jan 10, 2025 at 12:35 PM Erik Wienhold <ewie@ewie.name> wrote:

On 2025-01-09 21:31 +0100, Andy Hartman wrote:

could it be done using Powershell?

I use this: https://blog.cleverelephant.ca/2021/04/psql-binary.html
But I don't know if that translates to PowerShell.

--
Erik Wienhold

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On 1/11/25 03:05, Andy Hartman wrote:

I used PS to pull the data from mssql  to Postgres dumping data to csv.
I then used csv to load Postgres and the table that has Bytea

What data type was used to store data in MySQL?

Show command used to pull data from MySQL.

# Convert the image data to a base64 string  -- powershell
    $base64Image = [Convert]::ToBase64String($row.ImageSource)

AFter data was loaded the developer said in his app frontend that the
Image wouldn't open thru his code. -- I'm trying to get that code to
help debug

He said the size of the array is 1368. from bytea The size coming from

How does an array enter into this?

the SQL-Server db is 46935 and the image correctly appears...

Could that be caused by my PS dump to csv process or maybe still a
code(frontend) issue..

Still trying to figure out using a single record if data loaded to the
bytea field matches the mssql record.

I tried to use the tool SimplySql to connect mssql to postgresql to
transfer data but it failed  ...

any help would be appreciated..

On Fri, Jan 10, 2025 at 12:35 PM Erik Wienhold <ewie@ewie.name
<mailto:ewie@ewie.name>> wrote:

On 2025-01-09 21:31 +0100, Andy Hartman wrote:

could it be done using Powershell?

I use this: https://blog.cleverelephant.ca/2021/04/psql-binary.html
<https://blog.cleverelephant.ca/2021/04/psql-binary.html&gt;
But I don't know if that translates to PowerShell.

--
Erik Wienhold

--
Adrian Klaver
adrian.klaver@aklaver.com

I still have csv files and loaded right into PG no decoding and look like
this little snippet and I did the COpy command into PG

"/9j/4AAQSkZJRgABAQAAAQABAAD

On Sat, Jan 11, 2025 at 11:50 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

I bet Image*Source* doesn't contain what you think it does. I'd query that
table using SSMS, to see what's really in that column.

On Sat, Jan 11, 2025 at 6:49 PM Andy Hartman <hartman60home@gmail.com>
wrote:

I still have csv files and loaded right into PG no decoding and look like
this little snippet and I did the COpy command into PG

"/9j/4AAQSkZJRgABAQAAAQABAAD

On Sat, Jan 11, 2025 at 11:50 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 1/11/25 03:05, Andy Hartman wrote:

I used PS to pull the data from mssql to Postgres dumping data to csv.
I then used csv to load Postgres and the table that has Bytea

What data type was used to store data in MySQL?

Show command used to pull data from MySQL.

# Convert the image data to a base64 string -- powershell
$base64Image = [Convert]::ToBase64String($row.ImageSource)

AFter data was loaded the developer said in his app frontend that the
Image wouldn't open thru his code. -- I'm trying to get that code to
help debug

He said the size of the array is 1368. from bytea The size coming from

How does an array enter into this?

the SQL-Server db is 46935 and the image correctly appears...

Could that be caused by my PS dump to csv process or maybe still a
code(frontend) issue..

Still trying to figure out using a single record if data loaded to the
bytea field matches the mssql record.

I tried to use the tool SimplySql to connect mssql to postgresql to
transfer data but it failed ...

any help would be appreciated..

On Fri, Jan 10, 2025 at 12:35 PM Erik Wienhold <ewie@ewie.name
<mailto:ewie@ewie.name>> wrote:

On 2025-01-09 21:31 +0100, Andy Hartman wrote:

could it be done using Powershell?

I use this: https://blog.cleverelephant.ca/2021/04/psql-binary.html
<https://blog.cleverelephant.ca/2021/04/psql-binary.html&gt;
But I don't know if that translates to PowerShell.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On 1/11/25 15:49, Andy Hartman wrote:

I still have csv files and loaded right into PG no decoding and look
like this little snippet and I did the COpy command into PG

You have not answered:

What data type was used to store data in MySQL?

Show command used to pull data from MySQL.

"/9j/4AAQSkZJRgABAQAAAQABAAD

Are you sure?

The acceptable formats are shown here:

https://www.postgresql.org/docs/current/datatype-binary.html

And the above does not look like either.

--
Adrian Klaver
adrian.klaver@aklaver.com

In mssql its a image datatype.

On Sat, Jan 11, 2025 at 7:08 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 1/11/25 16:09, Andy Hartman wrote:

In mssql its a image datatype.

Oops, I was referring to the wrong database(MySQL) in previous posts.

So:

Show command used to pull data from MS SQL Server.

On Sat, Jan 11, 2025 at 7:08 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 1/11/25 15:49, Andy Hartman wrote:

I still have csv files and loaded right into PG no decoding and look
like this little snippet and I did the COpy command into PG

You have not answered:

What data type was used to store data in MySQL?

Show command used to pull data from MySQL.

"/9j/4AAQSkZJRgABAQAAAQABAAD

Are you sure?

The acceptable formats are shown here:

https://www.postgresql.org/docs/current/datatype-binary.html
<https://www.postgresql.org/docs/current/datatype-binary.html&gt;

And the above does not look like either.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com