Horton
Menu

Thread

  1. Display Bytea field

    Andy Hartman <hartman60home@gmail.com> — 2025-01-09T20:16:40Z

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

  2. Re: Display Bytea field

    Andy Hartman <hartman60home@gmail.com> — 2025-01-09T20:17:34Z

    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
>

  3. Re: Display Bytea field

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-09T20:24:32Z

    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!

  4. Re: Display Bytea field

    Andy Hartman <hartman60home@gmail.com> — 2025-01-09T20:31:13Z

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

  5. Re: Display Bytea field

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-09T20:37:37Z

    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!

  6. Re: Display Bytea field

    Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2025-01-09T22:10:13Z

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

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

> could it be done using Powershell? 

  7. Re: Display Bytea field

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-09T22:11:10Z

    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

  8. Re: Display Bytea field

    Daniel Verite <daniel@manitou-mail.org> — 2025-01-10T12:49:40Z

    	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], and then the large object exported
into a local file with \lo_export [2]

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.


[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

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


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

  9. Re: Display Bytea field

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-10T14:39:10Z

    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!

  10. Re: Display Bytea field

    Erik Wienhold <ewie@ewie.name> — 2025-01-10T17:35:38Z

    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

  11. Re: Display Bytea field

    Andy Hartman <hartman60home@gmail.com> — 2025-01-11T11:05:31Z

    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
>

  12. Re: Display Bytea field

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-11T12:50:41Z

    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!

  13. Re: Display Bytea field

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-11T16:50:06Z

    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>
>     But I don't know if that translates to PowerShell.
> 
>     -- 
>     Erik Wienhold
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  14. Re: Display Bytea field

    Andy Hartman <hartman60home@gmail.com> — 2025-01-11T23:49:06Z

    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>
> >     But I don't know if that translates to PowerShell.
> >
> >     --
> >     Erik Wienhold
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

  15. Re: Display Bytea field

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-12T00:03:28Z

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

  16. Re: Display Bytea field

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-12T00:08:32Z

    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

  17. Re: Display Bytea field

    Andy Hartman <hartman60home@gmail.com> — 2025-01-12T00:09:44Z

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

  18. Re: Display Bytea field

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-12T00:25:17Z

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