Recent

Author Topic: PostgreSQL insert image  (Read 2744 times)

lainz

  • Hero Member
  • *****
  • Posts: 3874
PostgreSQL insert image
« on: October 01, 2021, 05:49:18 pm »
Hi, I'm connecting to a balance with PostgreSQL, where I can insert images like this: Attached SQL query.

Any ideas on how to convert from PNG to this format?
« Last Edit: October 01, 2021, 06:06:28 pm by lainz »

loaded

  • Sr. Member
  • ****
  • Posts: 374
Re: PostgreSQL insert image
« Reply #1 on: October 01, 2021, 06:39:54 pm »
Hi,
Is this query you are using from the design phase? Or is it a query that you use all the time?
Instead of transferring from png in text format, why not send it directly in binary format?
« Last Edit: October 01, 2021, 06:45:05 pm by loaded »
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

y.ivanov

  • Sr. Member
  • ****
  • Posts: 306
Re: PostgreSQL insert image
« Reply #2 on: October 01, 2021, 06:56:22 pm »
Hi, I'm connecting to a balance with PostgreSQL, where I can insert images like this: Attached SQL query.
Not quite sure what do you mean.

Any ideas on how to convert from PNG to this format?
If you're talking about the "binarydata" column, the beginning of it looks like JPEG/JFIF to me:
Code: [Select]
\\377\\330 FF D8 start of image
\\377\\340 FF E0 APP0
\\000\\020 00 10 length of the segment=16
JFIF\\000 JFIF#0
\\001\\001 01 01 v 1.1
\\000 00   density - no density
\\000d 00 64 Xdensity=144
\\000d 00 64 Ydensity=144
\\000 00 Xthumbnail=0
\\000 00 Ythumbnail=0
\\377\\342 FF E2 APP2, ICC_PROFILE
\\014X 0C 58 length of the segment=3160
ICC_PROFILE\\000
\\001 01 sequence=1
\\001 01 total number=1
...

MarkMLl

  • Hero Member
  • *****
  • Posts: 3499
Re: PostgreSQL insert image
« Reply #3 on: October 01, 2021, 06:59:10 pm »
You'll probably find that described as "quoted printable" or similar.

Frankly you'd be /far/ better off storing them as Binary Large Objects.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

y.ivanov

  • Sr. Member
  • ****
  • Posts: 306
Re: PostgreSQL insert image
« Reply #4 on: October 01, 2021, 08:12:30 pm »
If you're talking about the "binarydata" column, the beginning of it looks like JPEG/JFIF to me:
Now we know how it looks like  ;)

P.S. if I knew Spanish I wouldn't need to convert it ... (korean squash)
« Last Edit: October 01, 2021, 08:17:05 pm by y.ivanov »

lainz

  • Hero Member
  • *****
  • Posts: 3874
Re: PostgreSQL insert image
« Reply #5 on: October 01, 2021, 10:15:17 pm »
What I mean with all this is that I can't use any other format because I don't own the database.

About using SQL instead of binary, because I get an error trying to read the binary file:

Code: Pascal  [Select][+][-]
  1. PostgreSQLConnectionMain : Execution of query failed  (PostgreSQL: ERROR:  must be superuser to read files
  2.  
  3. Severity: ERROR
  4. SQL State: 42501
  5. Primary Error: must be superuser to read files)

lainz

  • Hero Member
  • *****
  • Posts: 3874
Re: PostgreSQL insert image
« Reply #6 on: October 02, 2021, 01:02:02 am »
Hi,
Is this query you are using from the design phase? Or is it a query that you use all the time?
Instead of transferring from png in text format, why not send it directly in binary format?

Is a query I obtained with a third party tool dumped from the database records.

I tried sending it into binary format without success, it says I don't have superuser rights to do that.

For that I'm asking a way to use that format that's on the attachment. Say I have a PNG file and I can convert it to SQL query...

y.ivanov

  • Sr. Member
  • ****
  • Posts: 306
Re: PostgreSQL insert image
« Reply #7 on: October 02, 2021, 01:03:39 am »
What I mean with all this is that I can't use any other format because I don't own the database.

What I see for the binarydata column is that it is a JPEG/JFIF binary encoded as C-style string constant according to https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS , 4.1.2.2. The only thing is it seems to be encoded twice (?!).  I can see double backslashes (\\). So if we replace them with a single backslash (\) and replace the double single quote ('') with a single single quote (sorry for that) we should get a C-style string again.  Un-escaped once more (for the octal values: e.g. \\377\\330 --> \337\330 --> #$FF#$D8) it will become the binary image.

The only thing that disturbs me is that if we escape a single quote (') twice, we must get four quotes ('''') but in your sample we have occurrences of only two ('').

The opposite conversion should be straightforward: convert PNG to JPG, with LCL it is quite easy, I believe, and then, for each byte from the JPEG binary stream:
 
  • If it is a single quote - append two single quotes
  • If it is printable ASCII - just append it
  • If it is not - append two backslashes, then three digits of the byte octal representation


Edit: Just to be on the safe side - obviously you can encode any ambiguous symbol with two backslashes and three octal digits, including the backslash, single quote, etc.
« Last Edit: October 02, 2021, 01:37:02 am by y.ivanov »

lainz

  • Hero Member
  • *****
  • Posts: 3874
Re: PostgreSQL insert image
« Reply #8 on: October 02, 2021, 01:42:00 am »
Edit: Just to be on the safe side - obviously you can encode any ambiguous symbol with two backslashes and three octal digits, including the backslash, single quote, etc.

Many thanks is that I was for asking.

This is my code, hope it works:

Code: Pascal  [Select][+][-]
  1. var
  2.   fs: TFileStream;
  3.   i: integer;
  4.   b: byte;
  5.   s: String = '';
  6. begin
  7.   fs := TFileStream.Create('a.jpg', fmOpenRead);
  8.   fs.Position:=0;
  9.   for i:=0 to fs.Size-1 do
  10.   begin
  11.     b := fs.ReadByte;
  12.     case char(b) of
  13.          'a'..'z', 'A'..'Z': s := s + char(b);
  14.          else
  15.              s := s + ('\\'+OctStr(b, 3));
  16.     end;
  17.   end;
  18.   Memo1.Lines.Add(s);
  19.   fs.Free;

y.ivanov

  • Sr. Member
  • ****
  • Posts: 306
Re: PostgreSQL insert image
« Reply #9 on: October 02, 2021, 10:56:17 am »
I'll bet it works.
Glad to help on a real issue and not in the "Fastest flying pig in the sky" contest or alike...  O:-)

 

TinyPortal © 2005-2018