You Are Here Home > Databases

Databases

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

MySQL: ERROR 1114 (HY000) at line 4424: The table ‘X’ is full

Even though this could be a limitation by your file system check the value of the system variable:

myisam_data_pointer_size

If it’s set to 4 then the table can be a maximum of 4GB in size but if it’s set to 6 then the table can be a maximum of 256TB…

To fix this try:

edit /etc/my.cnf

And add this line to the end of it:

myisam_data_pointer_size = 6

Save it and restart mysql:

service mysql restart

I hope this helps…

MySQL: ERROR 1114 (HY000) at line 4424: The table ‘X’ is full
Comments (0)   Filed under: MySQL,Operating Systems,Server   Posted by: Codehead

WHM/cPanel: MySQL Errors While Manually Transferring Accounts

Errors like:

DELIMITER must be followed by a ‘delimiter’ character or string
or
ERROR X (X): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near

If you get those errors the chances are that the MySQL version on the source and destination servers don’t match, most probably the destination server runs an older version of MySQL, to fix this issue, you must let /scripts/pkgacct know your version of MySQL, find out the version, then do something like this:

/scripts/pkgacct UNAME –mysql 4.1
Note: 2 dashes before mysql, they are not showing up here…

Please note that, in my case, my MySQL server was version 4.1 so you may need to change this version number to match your own destination server’s MySQL server version.

I hope this helps someone :)

WHM/cPanel: MySQL Errors While Manually Transferring Accounts
Comments (0)   Filed under: MySQL,Server,WHM/cPanel   Posted by: Codehead

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

Rearrange/move MySQL Table Columns

To *move* a column to a particular position:

ALTER TABLE `user`
MODIFY COLUMN `permission` int(10)
AFTER `password`

To *move* a column to the beginning:

ALTER TABLE `user`
MODIFY COLUMN `permission` int(10)
FIRST
Rearrange/move MySQL Table Columns
Comments (0)   Filed under: MySQL   Posted by: Hamid

Add a Column To The Beginning Of a Table

Assume that we want to add a column ‘id’ in the beginning of the table ‘test’:

ALTER TABLE test ADD id INT NOT NULL FIRST;
Add a Column To The Beginning Of a Table
Comments (0)   Filed under: MySQL   Posted by: Codehead

Change or Reset The Auto Increment Value

Here is how to change the auto increment value to say 10 on the table ‘test’:

ALTER TABLE test AUTO_INCREMENT 1;
Change or Reset The Auto Increment Value
Comments (0)   Filed under: MySQL   Posted by: Codehead

Add a Column After a Specific Column On a Table

Assume that we want to add a column ‘number’ after ‘pass’ column on table ‘test’:

ALTER TABLE test ADD number INT(12) NOT NULL AFTER pass;
Add a Column After a Specific Column On a Table
Comments (0)   Filed under: MySQL   Posted by: Codehead
Older Posts »