New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't get from fields of type Blob #3

Open
stdavis opened this Issue Apr 4, 2012 · 11 comments

Comments

Projects
None yet
3 participants
@stdavis

stdavis commented Apr 4, 2012

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

This comment has been minimized.

Show comment
Hide comment
@coomsie

coomsie Apr 4, 2012

Owner

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.

Owner

coomsie commented Apr 4, 2012

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

This comment has been minimized.

Show comment
Hide comment
@coomsie

coomsie Apr 4, 2012

Owner

see comments from ns-1m

davibe/Phonegap-SQLitePlugin#15

Owner

coomsie commented Apr 4, 2012

see comments from ns-1m

davibe/Phonegap-SQLitePlugin#15

@coomsie coomsie closed this Apr 4, 2012

@coomsie coomsie reopened this Apr 4, 2012

@coomsie

This comment has been minimized.

Show comment
Hide comment
@coomsie

coomsie Apr 4, 2012

Owner

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

Owner

coomsie commented Apr 4, 2012

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

This comment has been minimized.

Show comment
Hide comment
@ns-1m

ns-1m Apr 5, 2012

Probably this one can help solve this blob problem.

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

ns-1m commented Apr 5, 2012

Probably this one can help solve this blob problem.

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

@ns-1m

This comment has been minimized.

Show comment
Hide comment
@ns-1m

ns-1m Apr 5, 2012

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 commented Apr 5, 2012

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

This comment has been minimized.

Show comment
Hide comment
@ns-1m

ns-1m Apr 5, 2012

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"];

ns-1m commented Apr 5, 2012

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

This comment has been minimized.

Show comment
Hide comment
@coomsie

coomsie Apr 5, 2012

Owner

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

Owner

coomsie commented Apr 5, 2012

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

@ns-1m

This comment has been minimized.

Show comment
Hide comment
@ns-1m

ns-1m Apr 5, 2012

@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 commented Apr 5, 2012

@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

This comment has been minimized.

Show comment
Hide comment
@ns-1m

ns-1m Apr 5, 2012

@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 commented Apr 5, 2012

@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

This comment has been minimized.

Show comment
Hide comment
@ns-1m

ns-1m Apr 12, 2012

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

ns-1m commented Apr 12, 2012

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

This comment has been minimized.

Show comment
Hide comment
@stdavis

stdavis Apr 25, 2012

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!

stdavis commented Apr 25, 2012

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment