Subject: Re: Display Bytea field

From: Andy Hartman <[email-masked]>
Date: Thu, 9 Jan 2025 15:31:13 -0500

could it be done using Powershell?

On Thu, Jan 9, 2025 at 3:25 PM Ron Johnson <[email-masked]> wrote:

> On Thu, Jan 9, 2025 at 3:17 PM Andy Hartman <[email-masked]>
> 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 <[email-masked]>
>> 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!
>

---

From: Ron Johnson <[email-masked]>
Date: Thu, 9 Jan 2025 15:37:37 -0500

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 <[email-masked]> wrote:

>> whatever) images*, and so call the relevant decoding library then
>> display at the appropriate location in a window.
>>
>> --

---

From: [email-masked]
Date: Thu, 09 Jan 2025 23:10:13 +0100

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

---

From: Erik Wienhold <[email-masked]>
Date: Fri, 10 Jan 2025 18:35:38 +0100

> 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

---

From: Andy Hartman <[email-masked]>
Date: Sat, 11 Jan 2025 06:05:31 -0500

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..

---

From: Ron Johnson <[email-masked]>
Date: Sat, 11 Jan 2025 07:50:41 -0500

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 <[email-masked]>
wrote:

> 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
>

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 11 Jan 2025 08:50:06 -0800

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.

> 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...
> 
> On Fri, Jan 10, 2025 at 12:35 PM Erik Wienhold <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     <https://blog.cleverelephant.ca/2021/04/psql-binary.html>

-- 
Adrian Klaver
[email-masked]

---

From: Andy Hartman <[email-masked]>
Date: Sat, 11 Jan 2025 18:49:06 -0500

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

---

From: Ron Johnson <[email-masked]>
Date: Sat, 11 Jan 2025 19:03:28 -0500

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.

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 11 Jan 2025 16:08:32 -0800

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:

> "/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.

---

From: Andy Hartman <[email-masked]>
Date: Sat, 11 Jan 2025 19:09:44 -0500

In mssql its a image datatype.

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 11 Jan 2025 16:25:17 -0800

> 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 <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     <https://www.postgresql.org/docs/current/datatype-binary.html>
> 
>     [email-masked] <mailto:[email-masked]>
>