Imagedatei in ein IMAGE Feld schreiben / How to store an image file in an IMAGE field?

[former_SQLA_member1694897]

Grüße Euch, wie schreibt an SQL z.B. eine .JPG Imagedatei in ein IMAGE Feld?

VG Franz

[VolkerBarth · Accepted solution]

You can use xp_read_file() to load the file's contents, and then just use the return value as value within an INSERT and/or UPDATE statement, such as

UPDATE MyTable
SET MyImage = xp_read_file('c:\\\\Gallery\\\\MyTest.jpg')
WHERE ID = 1;

Column MyTable should be of LONG BINARY (or IMAGE) data type. This should work for any kind of BLOBs (binary large objects).

[former_SQLA_member1694897 · Accepted solution]

Danke, hat geklappt! Wie funktioniert der umgekehrte Fall, also das BLOB auslesen...? VG Franz

[VolkerBarth · Accepted solution]

Similarly you can use xp_write_file() to store a BLOB's contents in a OS file, such as

SELECT xp_write_file('c:\\\\Gallery\\\\MyTestKopie.jpg', MyImage)
FROM MyTable
WHERE ID = 1;

Of course, you can also use local variables within SQL to handle BLOBs, such as

begin
   declare myVar long binary;
   set myVar = xp_read_file('c:\\\\Gallery\\\\MyTest.jpg');
   xp_write_file(myBlob, ('c:\\\\Gallery\\\\MyTestKopie.jpg', myVar);
end;

Both file functions are server-based and use path specifications that must be accessible for the database engine. For client-side access, you can use READ_CLIENT_FILE() resp. WRITE_CLIENT_FILE().

[former_SQLA_member1694897 · Accepted solution]

Danke, hat auch geklappt. Aber mit den Proportionen der Bilder hab ich meine Probleme, aber das ist ein Anzeigeproblem. Vielen Dank nochmal!

[former_SQLA_member1694897 · Accepted solution]

... noch eine Frage: Kann man VOR xp_write_file feststellen, welche Extention das Bild Hat? VG Franz

[VolkerBarth · Accepted solution]

The type of file stored in a BLOB has nothing to do with SQL Anywhere...

AFAIK, most picture formats will have identifying values in the first bytes of the contents - but it sure would be waaayyyy easier to store the file name and/or MIME type when storing the file contents, so it is already available when you want to export the file. 🙂

[former_SQLA_member1694897 · Accepted solution]

... so habe ich es auch schon gemacht. Vielen Dank!