How to get internal Images out of Firebird database

[RobertG]

Have changed a internal HSQLDB to Firebird.
Macro code for reading data for an internal images directly from a query in HSQLDB is:

…
stSql = "SELECT ""MyInternalImage"" FROM ""tbl_Image_intern"""
oResult = oSQL_Statement.executeQuery(stSql)
WHILE oResult.next
oStream = oResult.getBinaryStream(1)
REM Do something with this stream (write to tmp-folder …)
WEND
…

Works well in HSQLDB, fails in Firebird. Stream is NULL there. Have tried with Blob, but didn’t get it running.

Could get all images by connecting to the field through a form:

oStream = oForm.getBinaryStream(oForm.findColumn("MyInternalImage"))
REM Do something with this stream (write to tmp-folder …)

So I could solve the problem by running through the form. But whats the “trick” of the form to do what a direct connection to the data won’t do?

[elmau]

I’m sorry, is Python, you need translate to Basic.

import io
import util
import uno


def main():

    dbctx = util.create_instance('com.sun.star.sdb.DatabaseContext')
    db = dbctx['images']
    con = db.getConnection('', '')

    sql = 'SELECT name, image FROM images'
    s = con.createStatement()
    result = s.executeQuery(sql)
    while result.next():
        name = result.getString(1)
        stream = result.getBlob(2)
        size, data = stream.readBytes(io.BytesIO(), stream.available())
        path = Path(uno.fileUrlToSystemPath(db.DatabaseDocument.URL))
        path = f'{path.parent}/{name}.jpg'
        Path(path).write_bytes(data.value)

    con.close()

    return

[RobertG]

Thanks! Now I know I have to use getBlob. But I don’t know anything special for Python code. Special a construction like

@elmau

size, data = stream.readBytes(io.BytesIO(), stream.available())

I have never seen before. So I tried readBytes from the Blob-Object, but couldn’t get it working well.

[cpb]

Version: 7.4.6.2 (x64) / LibreOffice Community
Build ID: 5b1f5509c2decdade7fda905e3e1429a67acd63d
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded
.
despite the above I actually used a windows 11 pc for this.
.
the images are stored in a field type of BLOB and the basic uses oResult.GETBLOB.
.
seems to work fine, I include the attachment for no other reason than clarity.
ExtractImage_fb.odb (113.9 KB)

[RobertG]

@cpb: Have tried this way before. Wondered why it worked in your example database, not in mine. Then I had a look at the size of the images. One of mine is about 100 kB while images in the example you provided are max. 15 kB.

So I tried it with little images and oResult.getBlob instead of oResult.getBinaryStream in HSQLDB will work well. But getBinaryStream will show bigger images also while getBlob in Firebird will fail to read bigger images and will set the size to 0.

So: Seems to be the right way, but hasn’t just finished…

Try it with this image - ca. 95 kB here and couldn’t be extracted this way.

[cpb]

try this using my upload:

1. hit Tables icon
2. menu:Tools>SQL

drop table "tImages";

3. close Tools>SQL
4. menu:>View>Refresh Tables
5. save database
6. menu:Tools>SQL

create table "tImages"
(
	ID int generated by default as identity not null primary key,
	"Image" blob(64000) not null,
	"Name" varchar(50) not null
);

7. close Tools>SQL
8. menu:>View>Refresh Tables
9. save database
10. open the form and add “Name” = star.png, add your star image, save the record then close the form.
11. save database
12. run the macro
    .
    sorry to be so pedantic I know that you don’t need a step by step guide but others might.
    your star image on my system is 32k, using the GUI to create the table seems to guarantee failure, tools SQL on the other hand allows us to increase the BLOB size.

[elmau]

With Python works properly.

and extract it…