Android Question SQL Does not read blob image from SQLFile

ronovar · Feb 8, 2016

I have sql file and put there one image i cannot read it it always get error:

java.lang.IllegalArgumentException: column 'png_logo' does not exist

It for sure exist...here is code:

B4X:

Dim SQL1 As SQL

'INIT - SQL1
            SQL1.Initialize(File.DirInternal, "logos.db", True)

'INIT - Cursors
    Dim Cursor1 As Cursor

    'QUERY - DB
    Cursor1 = SQL1.ExecQuery2("SELECT 'tvg_logo', 'png_logo' FROM logos WHERE 'tvg_logo'=?", Array As String("hrt1"))

    'SET - Position
    Cursor1.Position = 0
   
    'DECLARE - Buffers
    Dim Buffer() As Byte
   
    'READ - Bloob
    Buffer = Cursor1.GetBlob("png_logo")
   
    'DECLARE - InputStreams
    Dim InputStream1 As InputStream
   
    'INITIALIZE - InputStreams
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)

'INIT - Bitmaps
    bmpChLogo.Initialize2(InputStream1)

InputStream1.Close

Here is logos.db file.

LucaMs · Feb 8, 2016

Probably the db in the internal folder is not the same you attached.

Try to use DirRootExternal to be sure (so it will be accessed by external tools, like SQLite Viewer).

Moreover, I suspect that png_logo of the attached db does not contain correct data (I see a suspect "png").
Finally, why use txt as extension?

Jeffrey Cameron · Feb 8, 2016

Try changing

ronovar said:
"SELECT 'tvg_logo', 'png_logo' FROM logos WHERE 'tvg_logo'=?",

to

B4X:

"SELECT [tvg_logo], [png_logo] FROM logos WHERE [tvg_logo]=?",

LucaMs · Feb 8, 2016

Jeffrey Cameron said:
Try changing

to

B4X:

"SELECT [tvg_logo], [png_logo] FROM logos WHERE [tvg_logo]=?",

~~The query is correct (there are no spaces in the field names).~~

ops, but there are apostrophes.

B4X:

 Cursor1 = SQL1.ExecQuery2("SELECT tvg_logo, png_logo FROM logos WHERE tvg_logo = ?", Array As String("hrt1"))

Should also work.

Jeffrey Cameron · Feb 8, 2016

LucaMs said:
The query is correct (there are no spaces in the field names).

How the column names are escaped may make a difference depending on which flavor of SQL is being used:
According to SQLite,

'foo' is an SQL string
"foo" is an SQL identifier (column/table/etc.)
[foo] is an identifier in MS SQL
`foo` is an identifier in MySQL

Since the error is "column name does not exist" as opposed to "can't open database" that was where I focused. I assumed (possibly incorrectly) the OP has verified the location/layout of the database being used since that's always the first thing I check.

LucaMs · Feb 8, 2016

Jeffrey Cameron said:
Since the error is "column name not found" as opposed to "can't open database" that was where I focused.

I saw many times question like that, with error: "column name not found".
Often you change a field name, use the old db and then the error will be not "can't open database".

I had not noticed apostrophes

ronovar · Feb 8, 2016

txt extension is used to upload here on forum...on android device is logos.db

I tested if database is successfully opened and initialized with this code and i im getting from log: db open... so database is open successfully and initialized only sql query is wrong..i get from blob error message column does not exist witch conatins blob image...where i im doing wrong?

B4X:

If SQL1.IsInitialized=True Then
                Log("db open...")
            Else
                Log("db failed to open...")
            End If

LucaMs · Feb 8, 2016

change the query (as Jeffrey has written or removing the apostrophes around the field names)

ronovar · Feb 8, 2016

Using:

B4X:

Cursor1 = SQL1.ExecQuery2("SELECT [tvg_logo], [png_logo] FROM logos WHERE [tvg_logo]=?", Array As String("hrt1"))

i get error:

android.database.sqlite.SQLiteException: no such column: tvg_logo (code 1): , while compiling: SELECT [tvg_logo], [png_logo] FROM logos WHERE [tvg_logo]=?

using this:

B4X:

Cursor1 = SQL1.ExecQuery2("SELECT tvg_logo, png_logo FROM logos WHERE tvg_logo = ?", ArrayAsString("hrt1"))

I get error:

android.database.sqlite.SQLiteException: no such column: tvg_logo (code 1): , while compiling: SELECT tvg_logo, png_logo FROM logos WHERE tvg_logo = ?

Any ides what to try next?

Only compiles fine with this:

B4X:

Cursor1 = SQL1.ExecQuery2("SELECT 'tvg_logo', 'png_logo' FROM logos WHERE 'tvg_logo' = ?", Array As String("hrt1"))

But get error on this line:

B4X:

Buffer = Cursor1.GetBlob("png_logo")

java.lang.IllegalArgumentException: column 'png_logo' does not exist

Jeffrey Cameron · Feb 8, 2016

The problem is in your query, as I indicated in post #5 you cannot escape column names with an apostrophe in SQLite; you should use double quotes or brackets:

B4X:

SELECT "tvg_logo", "png_logo" FROM logos WHERE "tvg_logo"='hrt1'

or

B4X:

SELECT [tvg_logo], [png_logo] FROM logos WHERE [tvg_logo]='hrt1'

I generally use the "[]" escape as this seems to work for me on MySQL, SQLite and MS SQL.

I tried both of these queries on the database you supplied in your post and they returned 1 row. I would go with LucaMS' suggestion and make sure the database I am using on the device is the same one you posted above (use File Manager on the device to compare date/time, size, etc.).

LucaMs · Feb 8, 2016

no, this (used) should work for sure:

Cursor1 = SQL1.ExecQuery2("SELECT tvg_logo, png_logo FROM logos WHERE tvg_logo = ?", ArrayAsString("hrt1"))

You are using a db with a different structure!

Mahares · Feb 8, 2016

B4X:

Cursor1 = SQL1.ExecQuery2("SELECT tvg_logo, png_logo FROM logos WHERE tvg_logo = ?", Array As String("hrt1"))

@LucaMs is correct. The above SQL statement is good. You do not need brackets around the column names because they do not have spaces. They have underscores which are ok. Your problem is somewhere else.

LucaMs · Feb 8, 2016

You are using the internal folder.

Probably you use something like:

if db exitst = false
copy from assets

but you have a new version of the db in your assets folder (which has a field named png_logo)

delete the db from the internal folder.

ronovar · Feb 8, 2016

Here is full code that downloads db from my server and save it to device...it says unknown column 'png_logo'

B4X:

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim SQL1 As SQL
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    'DECLARE - Variables

    Dim Job1 As HttpJob
 
    'INITIALIZE - Job1
    Job1.Initialize("Job1", Me)

    'GET - Logos
    Job1.Username = "username"
    Job1.Password = "password"
    Job1.Download("http://myurl/logos.db")
End Sub

Sub JobDone(Job As HttpJob)
    If Job.Success = True Then
        Select Job.JobName
            Case "Job1"
                'INITIALIZE - OutputStream
                Dim OutStream As OutputStream
 
                'READ - db file
                OutStream = File.OpenOutput(File.DirDefaultExternal, "logos.db", False)
             
                'SAVE - db file
                File.Copy2(Job.GetInputStream,OutStream)
 
                'CLOSE - OutputStream
                OutStream.Close 
             
             
                    'DEFINE - Bitmaps
    Dim bmpChLogo As Bitmap
             
                'INIT - Cursors
    Dim Cursor1 As Cursor

'INIT - SQL1
            SQL1.Initialize(File.DirDefaultExternal, "logos.db", True)
         
    'QUERY - DB
    Cursor1 = SQL1.ExecQuery2("SELECT tvg_logo, png_logo FROM logos WHERE tvg_logo = ?", Array As String("hrt1"))

    'SET - Position
    Cursor1.Position = 0
 
    'DECLARE - Buffers
    Dim Buffer() As Byte
 
    'READ - Bloob
    Buffer = Cursor1.GetBlob("png_logo")
 
    'DECLARE - InputStreams
    Dim InputStream1 As InputStream
 
    'INITIALIZE - InputStreams
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
 
    'INIT - Bitmaps
    bmpChLogo.Initialize2(InputStream1)
 
        End Select
    End If
End Sub
[CODE]

LucaMs · Feb 9, 2016

1) try adding OutStream.Flush before OutStream.Close
2) check the two DBs (the one on site and the other on device) using an external tool (SQLite Data Browser, for example).

ronovar · Feb 9, 2016

It works now...i was using and reading old database whole time...now it works..i just manually copy logos.db to com.example.ch/files/ folder and now it works.

So the problem is job1 it does not download correctly to this folder logos.db from server...can i get example code how is correct to download logos.db from server to:

com.example.ch/files/logos.db?

LucaMs · Feb 9, 2016

Thank you all

ronovar · Feb 9, 2016

Thanks to all, your help was great.

LucaMs · Feb 10, 2016

ronovar said:
So the problem is job1 it does not download correctly to this folder logos.db from server...can i get example code how is correct to download logos.db from server to:

com.example.ch/files/logos.db?

The destination folder is the one that you write in:

ronovar said:
OutStream = File.OpenOutput(File.DirDefaultExternal, "logos.db", False)

DirAssets is read only, if you mean this.

LucaMs · Feb 10, 2016

OutStream = File.OpenOutput(File.DirInternal, "logos.db", False)

Android Question SQL Does not read blob image from SQLFile

Active Member

Attachments

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Active Member

Expert

Active Member

Well-Known Member

Expert

Expert

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency