Обсуждение: Display Bytea field
I have migrated over a Table from Mssql that had an Image column I now have it in Postgres
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
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 itOn 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:
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 itOn 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 PostgresDid 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!
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 itOn 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 PostgresDid 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ó:
could it be done using Powershell?
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], 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
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!
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..
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..
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
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> > 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
"/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
I bet ImageSource 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/4AAQSkZJRgABAQAAAQABAADOn 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!
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 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
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