MIME-Version: 1.0
References: <CAEZv3co=t+YzkTmRMPR8Ev1izcb8VxYg-7jB2RCHSQGATsdHSg@mail.gmail.com>
 <CAEZv3copeUd7mNf98qxBtkqqW=g3MtCJe62jdMEEzFC4M=-+4A@mail.gmail.com>
 <CANzqJaDRQU2KsJt76TgPpES8qVg3i_sv5cZSmo=qoSN4VUzwNw@mail.gmail.com>
 <CAEZv3cqebrs+GT1YmWdk4sko7hgtstVKkcP+2OS07UtJ48x=dA@mail.gmail.com>
 <10fe353b-a5ba-4522-badf-89119179b4f0@ewie.name> <CAEZv3cqQ-LRPLM8iMsKh_Wc023pdHiW34AeuuY9jH-OF8FUnqg@mail.gmail.com>
In-Reply-To: <CAEZv3cqQ-LRPLM8iMsKh_Wc023pdHiW34AeuuY9jH-OF8FUnqg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 11 Jan 2025 07:50:41 -0500
Message-ID: <CANzqJaAK9pSMbQh6e8LWVoGyqTHMW_3szEpspGO8JkkYQcq-tw@mail.gmail.com>
Subject: Re: Display Bytea field
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004ff8cc062b6da915"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAK9pSMbQh6e8LWVoGyqTHMW_3szEpspGO8JkkYQcq-tw%40mail.gmail.com>
Precedence: bulk

--0000000000004ff8cc062b6da915
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM Andy Hartman <hartman60home@gmail.c=
om>
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 =3D [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 th=
e
> 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=E2=80=AFPM Erik Wienhold <ewie@ewie.name> w=
rote:
>
>> 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
>>
>

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

--0000000000004ff8cc062b6da915
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><br></div><div>1. Do you still have the CSV file (or =
can you regenerate it from the still-existing MSSQL DB)?</div><div>2. Did y=
ou load the base64 string into PG, or did you decode before loading into PG=
?</div><div>3. A base64 string would be about 62KB.=C2=A0 Either you did so=
mething wrong when loading, or the programmer is doing something wrong.</di=
v><div>4. When I=C2=A0migrated from Oracle LOBs to PB bytea, the Perl progr=
am ora2pg generated CSV files with &quot;hex&quot; strings for those column=
s.=C2=A0 They were preceded by &quot;\x&quot;, I think.=C2=A0 They loaded d=
irectly into the PG database, with the COPY command.</div><div><br></div><d=
iv dir=3D"ltr">On Sat, Jan 11, 2025 at 6:05=E2=80=AFAM Andy Hartman &lt;<a =
href=3D"mailto:hartman60home@gmail.com">hartman60home@gmail.com</a>&gt; wro=
te:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=
=3D"ltr">I used PS to pull the data from mssql=C2=A0 to Postgres dumping da=
ta to csv. I then used csv to load Postgres and the table that has Bytea<br=
><br><div># Convert the image data to a base64 string=C2=A0 -- powershell</=
div><div>=C2=A0 =C2=A0 $base64Image =3D [Convert]::ToBase64String($row.Imag=
eSource)<br><br>AFter data was loaded the developer said in his app fronten=
d that the Image wouldn&#39;t open thru his code. -- I&#39;m trying to get =
that code to help debug<br><br><span>He said=C2=A0the size of the array is =
1368. from bytea The size coming from the SQL-Server db is 46935 and the im=
age correctly appears...<br><br>Could that be caused by my PS dump to csv p=
rocess or maybe still a code(frontend) issue..</span></div><div><span><br><=
/span></div><div><span>Still trying to figure out using a single record if =
data loaded to the bytea field matches the mssql record.<br><br>I tried to =
use the tool SimplySql to connect mssql to postgresql to transfer data but =
it failed=C2=A0 ...=C2=A0<br><br>any help=C2=A0would be appreciated..<br><b=
r><br><br><br><br></span></div><div><br></div><div><br><br><br></div></div>=
</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_=
attr">On Fri, Jan 10, 2025 at 12:35=E2=80=AFPM Erik Wienhold &lt;<a href=3D=
"mailto:ewie@ewie.name" target=3D"_blank">ewie@ewie.name</a>&gt; wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex">On 2025-01-09 21:31=
 +0100, Andy Hartman wrote:<br>
&gt; could it be done using Powershell?<br>
<br>
I use this: <a href=3D"https://blog.cleverelephant.ca/2021/04/psql-binary.h=
tml" rel=3D"noreferrer" target=3D"_blank">https://blog.cleverelephant.ca/20=
21/04/psql-binary.html</a><br>
But I don&#39;t know if that translates to PowerShell.<br>
<br>
-- <br>
Erik Wienhold<br>
</blockquote></div>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--0000000000004ff8cc062b6da915--