You Are Here Home > SQLite

SQLite

SQLite And The Problem Of Storing Long Long Integers

Recently, I was trying to store a very large integer value in a SQLite column, it didn’t matter wether I used INTEGER or UNSIGNED BIG INT, SQLite rounded it for me and I was left with an integer value that wasn’t even close to what I needed.

So one solution was to just use a VARCHAR or TEXT or event a BLOB and insert the number with quotes around it but guess what? SQLite did it again!!!

It turns out that SQLite tries to convert your values to ints to give you some benefits with sorting and comparing etc. – in case you didn’t know you really wanted an int rather than a BLOB – and that happens here in one of it’s functions:

/*
** Try to convert a value into a numeric representation if we can
** do so without loss of information.  In other words, if the string
** looks like a number, convert it into a number.  If it does not
** look like a number, leave it alone.
*/
static void applyNumericAffinity(Mem *pRec){
  if( (pRec->flags & (MEM_Real|MEM_Int))==0 ){
    double rValue;
    i64 iValue;
    u8 enc = pRec->enc;
    if( (pRec->flags&MEM_Str)==0 ) return;
    if( sqlite3AtoF(pRec->z, &rValue, pRec->n, enc)==0 ) return;
    if( 0==sqlite3Atoi64(pRec->z, &iValue, pRec->n, enc) ){
      pRec->u.i = iValue;
      pRec->flags |= MEM_Int;
    }else{
      pRec->r = rValue;
      pRec->flags |= MEM_Real;
    }
  }
}

The solution to this issue is to prepend a 0 to your number and use a BLOB or TEXT type column and insert it like this: ’0THEVERYLARGENUMBER’

Don’t get me wrong, SQLite is a great library that I’ve been using, it’s very well tested and works great, it’s fast and thread safe, I specially like it’s licensee:

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************

I hope this helps…

SQLite And The Problem Of Storing Long Long Integers
Comments (0)   Filed under: C Programming,C/C++,Databases,iPhone SDK,Mac,Mac Programming,Objective-C,SQLite   Posted by: Hamid

Objective-C: A Function For Escaping Values Before Inserting Into SQLite

- (NSString *)escape:(NSObject *)value {
    if (value == nil)
        return nil;
    NSString *escapedValue = nil;
    if ([value isKindOfClass:[NSString class]] || [value isKindOfClass:[NSMutableString class]]) {
        NSString *valueString = (NSString *) value;
        char *theEscapedValue = sqlite3_mprintf("'%q'", [valueString UTF8String]);
        escapedValue = [NSString stringWithUTF8String:(const char *)theEscapedValue];
        sqlite3_free(theEscapedValue);
    } else
        escapedValue = [NSString stringWithFormat:@"%@", value];
    return escapedValue;
}
Objective-C: A Function For Escaping Values Before Inserting Into SQLite
Comments (0)   Filed under: Databases,iPhone SDK,Mac Programming,Objective-C,SQLite,Xcode   Posted by: Hamid

Get a List Of Tables In An SQLite Database

Here is how to do it:

SELECT * FROM sqlite_master WHERE type='table';
Get a List Of Tables In An SQLite Database
Comments (0)   Filed under: Databases,SQLite   Posted by: Hamid

Python SQLite3 In Multiple Threads

If you create your database in a thread – usually the main thread – and try to use it in another thread your will get:

ProgrammingError: SQLite objects created in a thread can only be used in that
same thread.The object was created in thread id SOME_ID and this is thread
id SOME_ID

This is because you created the ‘cursor’ object in the main thread, if you for example create only the connection object in the main thread and create ‘cursor’s when you want to query the database, your problem will be solved.

Of course you must take care of the concurrency issues that you might have but other than that, you could do something like this:

import sqlite3
 
class datastore:
 
    def __init__(self):
        self.data_file = 'path_to_db_file'
 
    def connect(self):
        self.conn = sqlite3.connect(self.data_file)
        return self.conn.cursor()
 
    def disconnect(self):
        self.cursor.close()
 
    def free(self, cursor):
        cursor.close()
 
    def write(self, query, values = ''):
        cursor = self.connect()
        if values != '':
            cursor.execute(query, values)
        else:
            cursor.execute(query)
        self.conn.commit()
        return cursor
 
    def read(self, query, values = ''):
        cursor = self.connect()
        if values != '':
            cursor.execute(query, values)
        else:
            cursor.execute(query)
        return cursor

This will fix the issue, it creates cursors for each query and returns it to the caller, the only thing is that you must ‘close’ the cursor when you are done with it. Here I have a member function ‘free’. (probably because of my PHP brain damage)

I hope this helps…

Python SQLite3 In Multiple Threads
Comments (0)   Filed under: Programming,Python,SQLite   Posted by: Codehead