Android Question SQL Does not read blob image from SQLFile

[ronovar]

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:

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]

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]

Try changing

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

to

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

 

[LucaMs]

Try changing

to

"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.

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

Should also work.

 

[Jeffrey Cameron]

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]

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]

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?

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

 

[LucaMs]

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

 

[ronovar]

Using:

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:

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:

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

But get error on this line:

Buffer = Cursor1.GetBlob("png_logo")

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

 

[Jeffrey Cameron]

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:

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

or

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]

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]

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]

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]

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

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]

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]

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]

Thank you all

 

[ronovar]

Thanks to all, your help was great.

 

[LucaMs]

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:

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

DirAssets is read only, if you mean this.

 

[LucaMs]

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