PostgreSQL insert image lazarus / 16-Databases ---------------------------------------------------------------------- lainz · 2021-10-01 15:49 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 · 2021-10-01 16:39 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 · 2021-10-01 16:56 Quote from: lainz on October 01, 2021, 05:49:18 pmHi, 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. Quote from: lainz on October 01, 2021, 05:49:18 pmAny 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 · 2021-10-01 16:59 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 · 2021-10-01 18:12 Quote from: y.ivanov on October 01, 2021, 06:56:22 pmIf 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 · 2021-10-01 20:15 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i PostgreSQLConnectionMain : Execution of query failed  (PostgreSQL: ERROR:  must be superuser to read files Severity: ERRORSQL State: 42501Primary Error: must be superuser to read files) ---------------------------------------------------------------------- lainz · 2021-10-01 23:02 Quote from: loaded on October 01, 2021, 06:39:54 pmHi, 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 · 2021-10-01 23:03 Quote from: lainz on October 01, 2021, 10:15:17 pmWhat 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 quotesIf it is printable ASCII - just append itIf 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 · 2021-10-01 23:42 Quote from: y.ivanov on October 02, 2021, 01:03:39 amEdit: 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  fs: TFileStream;  i: integer;  b: byte;  s: String = '';begin  fs := TFileStream.Create('a.jpg', fmOpenRead);  fs.Position:=0;  for i:=0 to fs.Size-1 do  begin    b := fs.ReadByte;    case char(b) of         'a'..'z', 'A'..'Z': s := s + char(b);         else             s := s + ('\\'+OctStr(b, 3));    end;  end;  Memo1.Lines.Add(s);  fs.Free; ---------------------------------------------------------------------- alpine · 2021-10-02 08:56 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...