Can't get from fields of type Blob

[stdavis]

When I try to query for blob values, I get back empty rows. Here's the query that I'm trying: SELECT tile_data FROM images where tile_id = "63915b6cd0f955a59b23f6a2c853bf09" and here's the related database file: http://dl.dropbox.com/u/14814828/road-trip.sqlite3

The database is a .mbtiles file exported from TileMill.

Thanks for your help.

[coomsie]

Yip,

just had a look at the code ...

looks like the creator didnt know what to do with blobs ...

switch (column_type) {
                        case SQLITE_INTEGER:
                            columnValue = [NSNumber numberWithDouble: sqlite3_column_double(statement, i)];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];
                            break;
                        case SQLITE_TEXT:
                            columnValue = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, i)];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];
                            break;
                        case SQLITE_BLOB:

                            break;
                        case SQLITE_FLOAT:
                            columnValue = [NSNumber numberWithFloat: sqlite3_column_double(statement, i)];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];                            
                            break;
                        case SQLITE_NULL:
                            break;
                    }
                    i++;

nothing is done in that case nor nulls.

[coomsie]

see comments from ns-1m

davibe/Phonegap-SQLitePlugin#15

[coomsie]

mmmm...

i had a look at this ...

i would have thought this would work ..

case SQLITE_BLOB:
                            columnValue = [NSData dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement, i)];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];
                            break;
                        case SQLITE_FLOAT:
                            columnValue = [NSNumber numberWithFloat: sqlite3_column_double(statement, i)];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];                            
                            break;
                        case SQLITE_NULL:
                            columnValue = [NSString stringWithUTF8String:(char *)@"null"];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];
                            break;

but it seems it doesn't?

i'm not a objective guy .... soo poking in the dark a bit....

maybe someone else can help ..

[ns-1m]

Probably this one can help solve this blob problem.

http://stackoverflow.com/questions/4217384/sqlite3-blob-problem

[ns-1m]

Try to have a look how MapBox for PhoneGap uses blob and try to find out "case SQLITE_BLOB:"

It is using FMDatabase.

https://github.com/tmcw/mapboxpg/tree/master/MapBox%20iPhone

[ns-1m]

I found out the problem. It is not NSString but NSData for the blob.

See this snippet that I cut and paste from QuickConnect Hybrid. I don't know how to patch it.

columnValue = [NSData dataWithData: sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement, i)];

or

NSData alloc]dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement,i)];

columnName = [NSData

Now, what would be the format?

stringWithFormat:@"%s", sqlite3_column_name(statement, i)];

Noli

##########################################

            for(int i = 0; i < numResultColumns; i++){
                int type  = [[[theResult columnTypes] objectAtIndex:i] intValue];
                if(type == SQLITE_INTEGER){
                    //NSLog(@"integer: %i",sqlite3_column_int(statement, i));
                    NSNumber *aNum = [[NSNumber alloc] initWithInt:sqlite3_column_int(statement, i)];
                    [row addObject:aNum];
                    [aNum autorelease];
                }
                else if(type == SQLITE_FLOAT){
                    //NSLog(@"float");
                    NSNumber *aFloat = [[NSNumber alloc] initWithFloat:sqlite3_column_double(statement, i)];
                    [row addObject:aFloat];
                    [aFloat autorelease];//this is not releasing
                }
                else if(type == SQLITE_TEXT){
                    //sqlite3_column_text returns a const unsigned char *.  initWithCString requires a const char *.
                    char *cText = (char*)sqlite3_column_text(statement, i);
                    NSString *aText = [[NSString alloc]initWithCString:cText encoding:NSUTF8StringEncoding];
                    [row addObject:aText];
                    [aText autorelease];
                }
                else if(type == SQLITE_BLOB){
                    //NSLog(@"blob");
                    NSData *aData = [[NSData alloc]dataWithBytes:sqlite3_column_blob(statement, i)   length:sqlite3_column_bytes(statement,i)];
                    [row addObject:aData];
                    [aData autorelease];
                }
                else{//SQLITE_NULL
                    [row addObject:@"null"];

[coomsie]

@ns-1m , have tried that .. see code above ... doesnt seem to work ...

[ns-1m]

@coomsie, I think ecanas codes might work.

davibe/Phonegap-SQLitePlugin#15 (comment)

I guess you can a copy of this as well in your emailbox

[ns-1m]

@coomsie,

I found this, SQLite Tutorial – Saving images in the database

http://www.iphonesdkarticles.com/2009/02/sqlite-tutorial-saving-images-in.html

NSData *imgData = UIImagePNGRepresentation(self.coffeeImage);

int returnValue = -1;
if(self.coffeeImage != nil)
returnValue = sqlite3_bind_blob(updateStmt, 3, [imgData bytes], [imgData length], NULL);
else
returnValue = sqlite3_bind_blob(updateStmt, 3, nil, -1, NULL);

NSData *data = [[NSData alloc] initWithBytes:sqlite3_column_blob(detailStmt, 1) length:sqlite3_column_bytes(detailStmt, 1)];

Noli

[ns-1m]

Cordova-1-6.0 was released yesterday.

Changes in plugins e.g. Cordova to cordova.

Download from here http://phonegap.com/ a complete and accurate version.

Anything on blobs fix?

Thanks.

Noli

[stdavis]

OK. I've got it working for .mbtiles format at least:

case SQLITE_BLOB:
                            nsData = [[NSData alloc] initWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement, i)];
                            columnValue = [nsData base64EncodingWithLineLength:0];
                            columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                            [entry setObject:columnValue forKey:columnName];
                            break;

base64EncodingWithLineLength comes from NSData.Base64.m.

You may want to go with a more generic solution since this is specific to data that is encoded with base64.

Thanks so much for your help!