Android Question SQL Read Blob Error loading bitmap

Discussion in 'Android Questions' started by Devan, Jun 17, 2015.

  1. Devan

    Devan Member Licensed User

    Hope Everyone is fine,
    Just having a problem with SQL Blob code.
    I'M using EZCamera code to initialize Device Camera.
    I'm using SQL Tutorial Code for InserBlob & ReadBlob.
    Now im having problem when I try to Read the image from SQL.
    Any Suggestion will be very helpful.

    Pls find my code and also Error Message as below:
    The code Stop at This Line in Debug Mode: Bitmap1.Initialize2(InputStream1)


    Code:
    Sub InsertBlob
           
        
    'convert the image file to a bytes array
        Dim InputStream1 As InputStream
        
    'cam.TakePicture(File.DirDefaultExternal, "" &Pnl6Lbl2DO_no.Text& ".jpg")
        InputStream1 = File.OpenInput(File.DirRootExternal, "" &Pnl5EdtTxt1PoDo_no.Text& ".jpg")
       
       
        
    Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(
    1000)
       
        
    File.Copy2(InputStream1, OutputStream1)
       
        
    Dim Buffer() As Byte 'declares an empty array
        Buffer = OutputStream1.ToBytesArray
           
    'Log(Buffer)
      
           
    Log(Pnl6Lbl2DO_no.Text)
        
    'write the image to the database
                                                            
        
    Log(Pnl5EdtTxt1PoDo_no.Text)
                                               
        SQL2.ExecNonQuery2(
    "UPDATE " &DBTableNameupdate& " SET " &ColNames2(12)& " = ? WHERE " &ColNames2(1)& " = ?"Array As Object(Buffer, "'" &Pnl5EdtTxt1PoDo_no.Text& "'")

     
    End Sub

    'Using a Cursor.GetBlob we fetch the previously stored image.
    'Now we are using an input stream that reads from this Array AND load the image
    Sub ReadBlob
        
    Dim Cursor1 As Cursor
        
    'Using ExecQuery2 is safer as it escapes special characters automatically.
        'In this case it doesn't really matter.
        Cursor1 = SQL2.ExecQuery2("SELECT image FROM " & DBTableNameupdate & " WHERE " &ColNames2(12)& " = ?"Array As String("'"&Pnl6Lbl2DO_no.Text&"';"))
       
        
    'Cursor1 = SQL2.ExecQuery("SELECT " &ColNames2(12)& " FROM " & DBTableNameupdate & " WHERE " &ColNames2(12)& " = '" &Pnl5EdtTxt1PoDo_no.Text& "'")
        Log(Cursor1)
       
        Cursor1.Position = 
    0
       
        
    Dim Buffer() As Byte 'declare an empty byte array
        Buffer = Cursor1.GetBlob(ColNames2(12))
        
    Dim InputStream1 As InputStream
        InputStream1.InitializeFromBytesArray(Buffer, 
    01)
      
        
    Dim Bitmap1 As Bitmap
        Bitmap1.Initialize2(InputStream1)
        InputStream1.Close
        
    Activity.SetBackgroundImage(Bitmap1)
    End Sub
    ** Activity (main) Create, isFirst = true **

    ** Activity (main) Resume **
    100
    ** Activity (main) Pause, UserClosed = false **
    ** Activity (update_delivery) Create, isFirst = true **
    true
    ** Activity (update_delivery) Resume **
    1434556800000
    SELECT ID,DONumber,DODate,DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName FROM DeliveryRegister WHERE DONumber = 'S11'
    (Cursor) Not initialized
    ExecuteMemoryTable: SELECT ID,DONumber,DODate,DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName FROM DeliveryRegister WHERE DONumber = 'S11'
    (SQLiteCursor) android.database.sqlite.SQLiteCursor@21dbec20
    SELECT ID,DONumber,DODate,DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName FROM DeliveryRegister WHERE DONumber = 'S11'
    SELECT ID,DONumber,DODate,DOReceiveDate, DeliveryAddress, DeliveryDate, OutletName FROM DeliveryRegister WHERE DONumber = 'S11'
    0
    [Ljava.lang.String;@21dbf210
    net.garstangs.ezcamera.EZcamera@21dc72b0
    ** Activity (update_delivery) Pause, UserClosed = false **
    sending message to waiting queue (OnActivityResult)
    running waiting messages (1)
    DO: S11
    S11
    ** Activity (update_delivery) Resume **
    UPDATE DeliveryRegister Set Status = '' WHERE DONumber = 'S11'
    1434556800000
    18/06/2015
    INSERT INTO DeliveryUpdate VALUES (Null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    (SQLiteCursor) android.database.sqlite.SQLiteCursor@21e38ca8
    Error occurred on line: 1438 (update_delivery)
    java.lang.RuntimeException: Error loading bitmap.
    at anywheresoftware.b4a.objects.drawable.CanvasWrapper$BitmapWrapper.Initialize2(CanvasWrapper.java:521)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:680)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:308)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.objects.ViewWrapper$2.onLongClick(ViewWrapper.java:87)
    at android.view.View.performLongClick(View.java:4428)
    at android.widget.TextView.performLongClick(TextView.java:8572)
    at android.view.View$CheckForLongPress.run(View.java:18135)
    at android.os.Handler.handleCallback(Handler.java:730)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:150)
    at android.app.ActivityThread.main(ActivityThread.java:5406)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
    at dalvik.system.NativeStart.main(Native Method)
     
  2. imbault

    imbault Well-Known Member Licensed User

    You can adapt this piece of code:
    Code:
    Private ImageView1 As ImageView

    Sub ReadBlob
        
    Dim Cursor1 As Cursor
        
    'signature is a blob containing a png image file
        Cursor1 = Main.SQLpi.ExecQuery2("SELECT signature FROM job WHERE pro_job = ?"Array As String(Main.mainJobId))
        Cursor1.Position = 
    0
        
    Dim Buffer() As Byte 'declare an empty byte array
        Buffer = Cursor1.GetBlob("signature")
        
    If Buffer <> Null And Buffer.Length >1 Then
            
    Dim InputStream1 As InputStream
            InputStream1.InitializeFromBytesArray(Buffer, 
    0, Buffer.Length)
          
            
    Dim Bitmap1 As Bitmap
            Bitmap1.Initialize2(InputStream1)
            InputStream1.Close


        
    End If
        Cursor1.close
    End Sub
     
    DonManfred, Peter Simpson and Devan like this.
  3. Devan

    Devan Member Licensed User

    Morning Imbault,
    Thank you for your help. Let me try and update the status. Have a nice day.
     
  4. Devan

    Devan Member Licensed User

    Hi imbault,
    Bad luck still struggling with the blob...
    Thank you for you code, if there is no image in the SQL database, than it jump to next line.
    But now problem Is image is inside SQL db, but than cannot view.
    Even I used SQLiteViewer to view the database, but still get the below error.
    Can some one give me idea what could be the reason.
    Thank you folk.. Have a good weekend....


    LogCat connected to: B4A-Bridge: asus K01A
    --------- beginning of /dev/log/main
    ** Activity (main) Resume **
    ** Service (service1) Create **
    ** Service (service1) Start **
    Connected to B4A-Bridge (Wifi)
    Installing file.
    ** Activity (main) Pause, UserClosed = false **
    PackageAdded: package:com.google.android.googlequicksearchbox
    ** Activity (main) Resume **
    Installing file.
    ** Activity (main) Pause, UserClosed = false **
    PackageAdded: package:anywheresoftware.b4a.sqliteviewer
    ** Activity (main) Create, isFirst = true **
    ** Activity (main) Resume **
    ExecuteMemoryTable: SELECT name FROM sqlite_master WHERE type = 'table'
    ** Activity (main) Pause, UserClosed = false **
    ** Activity (tableactivity) Create, isFirst = true **
    ExecuteMemoryTable: PRAGMA table_info ('DeliveryUpdate')
    ExecuteHtml: SELECT [ID],[DONumber],[DeliveryDate],[ActualDeliveryDate],[DriverName],[TruckNumber],[TruckType],[TruckSupplier],[JobNumber],[Remark],[Status],[RejectItems],[DOPhoto1],[DOPhoto2],[DOPhoto3] FROM DeliveryUpdate
    Error occurred on line: 287 (dbutils)
    android.database.sqlite.SQLiteException: unknown error (code 0): Unable to convert BLOB to string
    at android.database.CursorWindow.nativeGetString(Native Method)
    at android.database.CursorWindow.getString(CursorWindow.java:434)
    at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
    at anywheresoftware.b4a.sql.SQL$CursorWrapper.GetString2(SQL.java:346)
    at anywheresoftware.b4a.sqliteviewer.dbutils._executehtml(dbutils.java:291)
    at anywheresoftware.b4a.sqliteviewer.tableactivity._loadtablecontent(tableactivity.java:501)
    at anywheresoftware.b4a.sqliteviewer.tableactivity._loadtable(tableactivity.java:437)
    at anywheresoftware.b4a.sqliteviewer.tableactivity._activity_create(tableactivity.java:352)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:305)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
    at anywheresoftware.b4a.sqliteviewer.tableactivity.afterFirstLayout(tableactivity.java:100)
    at anywheresoftware.b4a.sqliteviewer.tableactivity.access$100(tableactivity.java:17)
    at anywheresoftware.b4a.sqliteviewer.tableactivity$WaitForLayout.run(tableactivity.java:78)
    at android.os.Handler.handleCallback(Handler.java:730)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:150)
    at android.app.ActivityThread.main(ActivityThread.java:5406)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
    at dalvik.system.NativeStart.main(Native Method)
    ** Activity (tableactivity) Resume **
    ** Activity (main) Resume **
     
  5. imbault

    imbault Well-Known Member Licensed User

    Ok try with this more complete code, assuming that you have Imageview1 in your layout, declared in Globals (Private ImageView1 As ImageView)


    Code:
    Sub ReadBlob
        
    Dim Cursor1 As Cursor
        Cursor1 = Main.SQLpi.ExecQuery2(
    "SELECT signature FROM job WHERE pro_job = ?"Array As String(Main.mainJobId))
        Cursor1.Position = 
    0
        
    Dim Buffer() As Byte 'declare an empty byte array
        Buffer = Cursor1.GetBlob("signature")
        
    If Buffer <> Null And Buffer.Length >1 Then
            
    Dim InputStream1 As InputStream
            InputStream1.InitializeFromBytesArray(Buffer, 
    0, Buffer.Length)
          
            
    Dim Bitmap1 As Bitmap
            Bitmap1.Initialize2(InputStream1)
            InputStream1.Close
            FitCenterBitmap(ImageView1,Bitmap1)
            ImageView1.Visible=
    True
        
    End If
        Cursor1.close
    End Sub

    Sub FitCenterBitmap(Imv As ImageView, bmp As Bitmap)
      
        
    Private cvs As Canvas

        cvs.Initialize(Imv)
      
        
    Dim rectDest As Rect
        
    Dim delta As Int
        
    If bmp.Width / bmp.Height > Imv.Width / Imv.Height Then
            delta = (Imv.Height - bmp.Height / bmp.Width * Imv.Width) / 
    2
            rectDest.Initialize(
    0, delta,Imv.Width, Imv.Height - delta)
        
    Else
            delta = (Imv.Width - bmp.Width / bmp.Height * Imv.Height) / 
    2
            rectDest.Initialize(delta, 
    0, Imv.Width - delta, Imv.Height)
        
    End If
        cvs.DrawBitmap(bmp, 
    Null, rectDest)
        Imv.Invalidate
    End Sub
     
    Devan likes this.
  6. Devan

    Devan Member Licensed User

    Hi Imbault,
    thank you very much for your help. Really appreciate that.
    The code works fantastic. I can view the photo no issues.

    Just one more problem, when I try to load the table in Webview, it gives error Unable to convert BLOB to string.
    What could be the reason? Since the photo stored as byte, why cannot launch table in WebView.
    Again thank you for your help.
    Have a Great weekend and enjoy your time....
     
  7. imbault

    imbault Well-Known Member Licensed User

    What are you exactly trying to do with the Webview? to show the records using a ExecuteHtml, or something like that?

    Is that case, this is normal, your blob contains binary data, and there is no way for B4a to know what kind of binary data it stores, and anyway it tries to cast binary to string and raises an error, in that case put a Try ... catch to ignore that blob, or select all fields except the blob to grid your data in the Webview (used to display HTML)

    Patrick
     
    Devan likes this.
  8. Devan

    Devan Member Licensed User

    Hi Good Morning Imbault,
    Yes I'm using DBUtils.ExecuteHtml.
    I tried Try..Catch but than didn't work. I don't know how to manipulate the code.

    So I change the Query as per your advice and only selected columns without blob and it worked. Thank you for your idea & solution.

    Maybe when I have free time, will try to use code from SQLite Viewer. If it works than will update here.
    Till than have a great Weekend buddy...
    Cheers....
     
Loading...