PostgreSQL insert image

[lainz]

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?

[loaded]

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?

[alpine]

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]

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

[alpine]

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)

[lainz]

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]

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

[alpine]

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.

[lainz]

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;

[alpine]

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