Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?

[Dan]

On Jan 4, 2008, at 7:57 AM, Jerry Krinock wrote:

I need to read an sqlite database generated by others. So I wrote an outer loop which steps through the rows of a table using sqlite3_step, and an inner loop which steps through the columns. The inner loop finds the type using sqlite3_column_type(), then 'switches' to get the value using the appropriate sqlite3_column_XXXXX() function.

It works fine if, when encountering an SQLITE_INTEGER type, I use sqlite_column_int64() to get the data.

Internally, integers are all 64-bits. If you call sqlite3_column_int()
to retrieve a value, it is truncated to 32-bits before returning it.
So to be safe, you're better off always using sqlite3_column_int64().

Dan.

I don't know whether or not I'm just "lucky" that the application which wrote the database uses 64 bit for all of its integers? If so, what if someone throws a 32-bit integer at me someday? How can I tell whether integer data objects in a table are 32 or 64 bit? The column specifications I get from pragma_table_info() are likewise uninformative, saying simply type=INTEGER.

Thanks again,

Jerry Krinock


// Method Implementation (Objective-C for Mac OS X)

- (NSArray*)dicsOfRowsInTable:(NSString*)table {
    // Will return nil if fails, empty array if no rows
    void* db = [self db] ;
    //char* errMsg = NULL ;
    int result ;

NSString* statement = [[NSString alloc] initWithFormat:@"SELECT * FROM '%@'", table] ;

    // Compile the statement into a virtual machine
    sqlite3_stmt* preparedStatement ;

result = sqlite3_prepare(db, [statement UTF8String], -1, &preparedStatement, NULL) ;

    [statement release] ;

    NSArray* output = nil ;
    if (result != SQLITE_OK) {
        [self showError:"prepare" from:11 code:result] ;
    }
    else {
        NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
        NSArray* keys = [self keysInTable:table] ;
        int nColumns = [keys count] ;

while (result = sqlite3_step(preparedStatement) == SQLITE_ROW) { NSMutableDictionary* rowDic = [[NSMutableDictionary alloc] init] ;

            int iColumn  ;
            for (iColumn= 0; iColumn<nColumns; iColumn++) {

int type = sqlite3_column_type(preparedStatement, iColumn) ; // The sqlite3_column_type() routine returns datatype code

                // for the initial data type of the result column.
                // The returned value is one of SQLITE_INTEGER,

// SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL

                // Initialize to null in case object is not found
                const void* pFirstByte = NULL ;
                int nBytes = 0 ;
                id object = nil ;
                long long int intValue ;
                const unsigned char* utf8String ;
                double doubleValue ;
                switch(type) {
                    case SQLITE_BLOB:

nBytes = sqlite3_column_bytes (preparedStatement, iColumn) ; // "The return value from sqlite3_column_blob() for a zero-length // blob is an arbitrary pointer, possibly even a NULL pointer."

                        // Therefore, we qualify...
                        if (nBytes > 0) {

pFirstByte = sqlite3_column_blob (preparedStatement, iColumn) ; object = [[NSData alloc] initWithBytes:pFirstByte length:nBytes] ;

                        }
                        break ;
                    case SQLITE_INTEGER:

intValue = sqlite3_column_int64 (preparedStatement, iColumn) ; object = [NSNumber numberWithLongLong:intValue] ;

                        break ;
                    case SQLITE_TEXT:

// "Strings returned by sqlite3_column_text () and sqlite3_column_text16(), // even zero-length strings, are always zero terminated." // So, we ignore the length and just convert it utf8String = sqlite3_column_text (preparedStatement, iColumn) ; object = [NSString stringWithUTF8String: (char*)utf8String] ;

                        break ;
                    case SQLITE_FLOAT:

doubleValue = sqlite3_column_double (preparedStatement, iColumn) ; object = [NSNumber numberWithDouble:doubleValue] ;

                        break ;
                    case SQLITE_NULL:
                    default:

// Just leave object nil, will replace with [NSNull null] soon.

                        ;
                }

                if (object == nil) {
                    object = [NSNull null] ;
                }

[rowDic setObject:object forKey:[keys objectAtIndex:iColumn]] ;

            }

            NSDictionary* rowDicCopy = [rowDic copy] ;
            [rowDics addObject:rowDicCopy] ;
            [rowDicCopy release] ;
        }

        output = [rowDics copy] ;
        [rowDics release] ;
    }

// Finalize the statement (this releases resources allocated by sqlite3_prepare()

    result = sqlite3_finalize(preparedStatement) ;
    if (result != SQLITE_OK) {
        [self showError:"finalize" from:13 code:result] ;
    }

    return [output autorelease] ;
}

---------------------------------------------------------------------- -------

To unsubscribe, send email to [EMAIL PROTECTED]

---------------------------------------------------------------------- -------

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------