How to convert BLOB to Binary then retrive it from Sqlite?

Question

I have sqlite db with BLOB column contains images. I have been searching for many solutions with no luck at all. I set the image field (column) a class of java.io.InputStream however, I get error says "jasperreports.engine.JRException: Unable to get value for result set field "image" of class java.io.InputStream"

I found this soluion below by @joyo. He says to convert the BLOB to binary first. I can't find anyway to do that. I think it is a more sql thing.

How to show blob object image using JasperReports from sqlite database?

What should I do to convert BLOB to Binary or any other workaround to display image dynamically in jaspersoft?

I have tried to set the field class to BLOB or InputStream, and this gives me error loading the fields even if not used.

ByteInputStream allows render the file, but I don't know how to work with image expression in this case?

My code looks like

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Blank_A4_3" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <queryString language="SQL">
        <![CDATA[SELECT PicT1 FROM HTNlist]]>
    </queryString>
    <field name="PicT1" class="java.io.InputStream"/>
    <detail>
        <band height="60" splitType="Stretch">
            <image>
                <reportElement x="252" y="10" width="50" height="50"/>
                <imageExpression><![CDATA[$F{PicT1}]]></imageExpression>
            </image>
        </band>
    </detail>
</jasperReport>

This give me error says

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Unable to get value for result set field "PicT1" of class java.io.InputStream.
    at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:536)
    at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$17(ReportController.java:511)
    at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:429)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)
Caused by: net.sf.jasperreports.engine.JRException: Unable to get value for result set field "PicT1" of class java.io.InputStream.
    at net.sf.jasperreports.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:385)
    at net.sf.jasperreports.engine.fill.JRFillDataset.setOldValues(JRFillDataset.java:1501)
    at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1402)
    at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1378)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1194)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:108)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:615)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: not implemented by SQLite JDBC driver
    at org.sqlite.Unused.unused(Unused.java:29)
    at org.sqlite.Unused.getBlob(Unused.java:96)
    at net.sf.jasperreports.engine.JRResultSetDataSource.readBytes(JRResultSetDataSource.java:703)
    at net.sf.jasperreports.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:251)
    ... 8 more

Duplicates: [How to view an image from blob column in Oracle with JasperReports?](https://stackoverflow.com/q/12272283/876298) & [How to export an image that is a blob in database from a jasper reports to excel?](https://stackoverflow.com/q/33894102/876298) & [Displaying image in Ireports using PostgreSql](https://stackoverflow.com/q/8430313/876298) & [How to show blob object image using JasperReports from sqlite database?](https://stackoverflow.com/q/42313024/876298) — Alex K, Feb 24 '19 at 19:11
I need further clarification of changing the column from BLOB to binary. I can't find a valid way! — Omar113, Feb 24 '19 at 19:24
Did you try queries like this: 1) `SELECT hex(PicT1) FROM HTNlist` 2) 1) `SELECT quote(PicT1) FROM HTNlist` ? — Alex K, Feb 24 '19 at 21:15
quote(PicT1) gives me the same error. hex(PicT1) is giving another error "Byte data not found at ........" — Omar113, Feb 24 '19 at 21:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188964/discussion-between-alex-k-and-omar113). — Alex K, Feb 24 '19 at 21:24

How to convert BLOB to Binary then retrive it from Sqlite?

0 Answers0