1

I have converted from a MySQL database to Postgres. During the conversion, the picture column in Postgres was created as bytea.

This Xojo code works in MySQL but not Postgres.

Dim mImage as Picture

mImage = rs.Field("Picture").PictureValue

Any ideas?

kipsoft
  • 359
  • 4
  • 16

1 Answers1

1

I don't know about this particular issue, but here's what you can do to find out yourself, perhaps:

Pictures are stored as BLOBs in the database. Now, this means that the column must also be declared as BLOB (or a similar binary type). If it was accidentally marked as TEXT, this would work as long as the database does not get exported by other means. I.e, as long as only your Xojo code reads and writes to the record, using the PictureValue functions, that takes care of keeping the data in BLOB form. But if you'd then convert to another database, the BLOB data would be read as text, and in that process it might get mangled.

So, it may be relevant to let us know how you converted the DB. Did you perform a export as SQL commands and then imported it into Postgres by running these commands again? Do you still have the export file? If so, find a record with picture data in it and see if that data is starting with: x' and then contains hex byte code, e.g. x'45FE1200... and so on. If it doesn't, that's another indicator for my suspicion.

So, check the type of the Picture column in your old DB first. If that specifies a binary data type, then the above probably does not apply.

Next, you can look at the actualy binary data that Xojo reads. To do that, get the BlobValue instead of the PictureValue, and store that in a MemoryBlock. Do the same for a single picture, both with the old and the new database. The memoryblock should contain the same bytes. If not, that would suggest that the data was not transferred correctly. Why? Well, that depends on how you converted it.

Thomas Tempelmann
  • 11,045
  • 8
  • 74
  • 149
  • The picture was stored as a LONGBLOB in MySQL and then exported to Postgres using Navicat data transfer with option 'Use hexidecimal format for blob'. The first characters of the data in the field are: \377\330\377\340\000\020JFIF\000\001\001\000\000\001\000\001\000\000\377\355\000XPhotoshop 3.0\0008BIM\004\004\000\000\000\000\000\037\034\001Z\000\003\033%G\034\002\000\000\002\000\002\034\002\031\000\013Photo Booth\0008BIM\004%\000\000\000\000\000\020qyd (...) if that gives you any clue. – kipsoft Mar 19 '14 at 15:44
  • So if, if I understand this, the data transfer did not maintain hex format even though it was specified as an option in Navicat. Am I right? If so, how would I go about porting the data to Postgres and retain the data as an image? – kipsoft Mar 19 '14 at 15:53
  • It is certainly strange that the contents use a textual representation with the octal \xxx escape codes. I wonder if you can manually transfer the BLOB data of one of those pictures in hex format to see if that makes it work in postgres then. Try using a different SQL editor for that, maybe the one you're using has a bug there. – Thomas Tempelmann Mar 19 '14 at 17:51
  • This is interesting. In Navicat, you can view the image in the Postgres data file - so the data file has to be intact. When I copied the data stream above, I was in PGAdminIII and did a SELECT for picture > '' and copied the information from the result in the table beneath (maybe that converts it to text somehow). When I open PGAdmin in the list view, the column shows the information as . – kipsoft Mar 19 '14 at 19:38
  • So since the image is viewable in Navicat and since the binary data seems intact, I just have to figure out how to display this data in Xojo. Not sure if there is another option besides rs.Field("Picture").PictureValue – kipsoft Mar 19 '14 at 19:39
  • 2
    The other way is to get the blob value and then use Picture.FromData to construct the picture. That's what I am doing when storing images in a sqlite database. BTW, please do not forget to update and accept answers - see your other question which I apparently answered the other day. – Thomas Tempelmann Mar 20 '14 at 00:50
  • I tried this but it does not work: Dim mb as MemoryBlock mb = rs.Field("Picture").NativeValue mImage = Picture.FromData(mb) – kipsoft Mar 28 '14 at 15:16
  • Here is what finally worked: If (Len(rs.Field("Picture").StringValue)>0) then Dim a as string = DecodeHex(rs.Field("Picture").StringValue) mImage = Picture.FromData(a) End if – kipsoft Mar 28 '14 at 22:01
  • Not sure why the reverse does not store the picture. If (mImage <> Nil) then Dim mb As MemoryBlock mb = mImage.GetData(Picture.FormatJPEG,Picture.QualityHigh) rs.Field("picture").StringValue = EncodeHex(mb) End if – kipsoft Mar 29 '14 at 01:12
  • So, the picture data was not stored as a BLOB but as a Text with hex bytes. That is quite wasteful in disk space. No idea why the encoding isn't working. You probably need to look at the data that you try to store and what actually ends up in the DB more closely. – Thomas Tempelmann Mar 29 '14 at 16:29
  • Here is the solution. Postgres needs the hex string preceded by \x (to distinguish it from the escape format). See this link: [link](http://www.postgresql.org/docs/9.0/static/datatype-binary.html) So storing of the image was accomplished with the following code: `If (mImage <> Nil) then Dim mb As MemoryBlock If mImage <> Nil Then // Get the image data mb = "\x"+EncodeHex(mImage.GetData(Picture.FormatJPEG, Picture.QualityHigh)) rs.Field("picture").Value = mb End if End if` – kipsoft Mar 30 '14 at 22:17
  • Glad to see you figured it out. – Thomas Tempelmann Mar 30 '14 at 22:39