NULL Values not saved or returned properly

[ecanas]

It seems that null values are either not being saved or returned properly. Instead of saving a null value in a field I get 0 or '' depending on the datatype.

Below is code from QuickConnect which has a native client, maybe you can use some of the code, I noticed they have a blob section which you can use on your code and they have a function called "sqlite3_bind_null" not sure what it's doing exactly but could fix the null value problem.

/*
Copyright (c) 2008, 2009 Lee Barney
Permission is hereby granted, free of charge, to any person obtaining a
copy of this software and associated documentation files (the "Software"),
to deal in the Software without restriction, including without limitation the
rights to use, copy, modify, merge, publish, distribute, sublicense,
and/or sell copies of the Software, and to permit persons to whom the Software
is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

The end-user documentation included with the redistribution, if any, must
include the following acknowledgment:
"This product was created using the QuickConnect framework. http://www.quickconnectfamily.org",
in the same place and form as other third-party acknowledgments. Alternately, this acknowledgment
may appear in the software itself, in the same form and location as other
such third-party acknowledgments.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

*/

import "SQLiteDataAccess.h"

import "QCParameter.h"

import <unistd.h>

static NSString *dbSemaphore = @"dblock";

// Private interface for AppDelegate - internal only methods.
@interface SQLiteDataAccess (Private)

• (DataAccessResult_)dbAccess:(NSString_)SQL withParameters:(NSArray*)parameters treatAsChangeData:(BOOL)treatAsChangeData;
  //internal bind methods
• (int) bind_blob:(sqlite3_stmt*)statement withIndex:(int) andBindVariable:(id)aVariable;
• (int) bind_double:(sqlite3_stmt*)statement withIndex:(int) andBindVariable:(id)aVariable;
• (int) bind_int:(sqlite3_stmt*)statement withIndex:(int) andBindVariable:(id)aVariable;
• (int) bind_text:(sqlite3_stmt*)statement withIndex:(int) andBindVariable:(id)aVariable;
• (int) bind_zeroblob:(sqlite3_stmt*)statement withIndex:(int) andBindVariable:(id)aVariable;
• (int) bind_null:(sqlite3_stmt*)statement withIndex:(int) andBindVariable:(id)aVariable;

@EnD

@implementation SQLiteDataAccess

• (SQLiteDataAccess_)initWithDatabase: (NSString_) dbName isWriteable: (BOOL) isWriteable{
  //if (self = [super init]) {
  NSString *path = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:dbName];
  if(isWriteable){

  // The application ships with a default database in its bundle. If anything in the application
  // bundle is altered, the code sign will fail. We want the database to be editable by users, 
  // so we need to create a copy of it in the application's Documents directory.   
  
  BOOL success;
  NSFileManager *fileManager = [NSFileManager defaultManager];
  NSError *error;
  NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  NSString *documentsDirectory = [paths objectAtIndex:0];
  NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:dbName];
  //NSLog(@"%@",writableDBPath);
  success = [fileManager fileExistsAtPath:writableDBPath];
  if (!success){
      // The writable database does not exist, so copy the default to the appropriate location.
      //NSLog(@"%@",path);
      //NSLog(@"%@",writableDBPath);
  
      @synchronized(dbSemaphore) {
          success = [fileManager copyItemAtPath:path toPath:writableDBPath error:&error];
      }
      if (!success) {
          NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
          return nil;
      }
  }
  path = writableDBPath;
  

  }
  sqlite3 *aDatabase;
  //NSLog(@"path: %@",path);
  if (sqlite3_open([path UTF8String], &aDatabase) == SQLITE_OK) {
  //NSLog(@"database opened");
  self->database = aDatabase;

  //NSLog(@"assigned");
  //create the dictionary that maps parameter types to bind method calls
  NSArray *keys = [NSArray arrayWithObjects:[NSString class], [NSDecimalNumber class], nil];
  ////NSLog(@"keys %@", keys);
  NSMutableArray * values = [[NSMutableArray alloc] init];
  [values addObject:@"bind_text:withIndex:andBindVariable:"];
  [values addObject:@"bind_double:withIndex:andBindVariable:"];
  //NSLog(@"values");
  //NSLog(@"keys and objects ready");
  NSDictionary *aDictionary = [NSDictionary dictionaryWithObjects:values forKeys:keys];
  [aDictionary retain];
  [values release];
  //NSLog(@"dictionary ready");
  self->bindTypeDictionary = aDictionary;
  //NSLog(@"dictionary set");
  //NSLog(@"successfully loaded database");
  return self;
  

  }
  else{
  //since we failed to open the database completely close it down to make sure that everyting is cleaned up
  sqlite3_close(aDatabase);
  NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(aDatabase));
  }
  //}
  return nil;
  }

• (DataAccessResult_)getData:(NSString_)SQL withParameters:(NSArray*)parameters{
  //NSLog(@"getting data");
  return [self dbAccess:SQL withParameters:parameters treatAsChangeData:FALSE];
  }

• (DataAccessResult_)setData:(NSString_)SQL withParameters:(NSArray*)parameters{
  DataAccessResult *retVal;
  @synchronized(dbSemaphore) {
  retVal = [self dbAccess:SQL withParameters:parameters treatAsChangeData:TRUE];
  }
  return retVal;
  }

• (DataAccessResult_)dbAccess:(NSString_)SQL withParameters:(NSArray_)parameters treatAsChangeData:(BOOL)treatAsChangeData{
  SQL = [SQL stringByReplacingPercentEscapesUsingEncoding:NSUTF8StringEncoding];
  //NSLog(@"paramArrayString after 1: %@\n\n\n",SQL);
  //NSLog(@"in dbAccess");
  DataAccessResult *theResult; //what is being returned
  theResult = [DataAccessResult alloc];
  if(parameters != nil && [parameters count] > 0){
  //make sure the the number of parameters is equal to the number of qestion marks in the SQL string
  }
  int numResultColumns = 0;
  sqlite3_stmt *statement = nil; // Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
  // The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.
  const char_ SQLChar = [SQL UTF8String];//this needs to be deallocated after we don't need it any more.
  //NSLog(@"about to prepare %@",SQL);
  if (sqlite3_prepare_v2(database, SQLChar, -1, &statement, NULL) == SQLITE_OK) {
  if(!treatAsChangeData){
  //NSLog(@"columns not changing");
  //retrieve the number of columns in the result of the execution of the select statement
  numResultColumns = sqlite3_column_count(statement);
  //NSLog(@"numRecentColumns: %i",numResultColumns);
  NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
  NSMutableArray *fieldNames = [[NSMutableArray alloc] initWithCapacity:0];
  for(int i = 0; i < numResultColumns; i++){
  const char *name = sqlite3_column_name(statement, i);
  NSString * columnName = [[NSString alloc]initWithCString:name encoding:NSUTF8StringEncoding];
  [fieldNames addObject:columnName];
  [columnName autorelease];
  }
  [theResult setFieldNames:fieldNames];
  [fieldNames autorelease];

      [pool drain];
  }
  //NSLog(@"Checking SQL prepared statement parameters: %@", parameters);
  if(parameters != nil && [parameters count] > 0){
      int numParams = [parameters count];
      //NSLog(@"numQueryParams: %i", numParams);
      NSAutoreleasePool *pool =  [[NSAutoreleasePool alloc] init];
      for (int i = 0; i < numParams; i++) {
          id value = [parameters objectAtIndex:i];
          //NSLog(@"value type: %@", [value class]);
          if([value respondsToSelector:NSSelectorFromString(@"getCharacters:")]){
              value = (NSString*)value;
          }
          //NSLog(@"value type: %@", [value class]);
          //NSString *name = [parameter name];
          //id value = [parameter value];
          ////NSLog(@"name: %@, value: %@", name, value);
          //[parameter autorelease];
          NSString *funcType;
          //NSStrings don't show up as NSStrings but as NSCFStrings
          if([value respondsToSelector:NSSelectorFromString(@"getCharacters:")]){
              funcType = [bindTypeDictionary objectForKey:[NSString class]];
          }
          else{
              funcType = [bindTypeDictionary objectForKey:[value class]];
          }
  
  
          SEL aSelector = NSSelectorFromString(funcType);
          [funcType autorelease];
          //bind the variables here
          objc_msgSend(self, aSelector, statement, i+1, value);
  
  
          //sqlite3_bind_text(statement, i+1, value, [value lengthOfBytesUsingEncoding:NSASCIIStringEncoding], SQLITE_TRANSIENT);
          /*
           const char* valueChars = [value cStringUsingEncoding:NSASCIIStringEncoding];
           sqlite3_bind_text(statement, i+1, valueChars, [value lengthOfBytesUsingEncoding:NSASCIIStringEncoding], SQLITE_TRANSIENT);
           */
      }
      [pool drain];
  }
  NSMutableArray *results = [NSMutableArray arrayWithCapacity:0];//[[NSMutableArray alloc] initWithCapacity:0];
  
  //rows changed is 0 if not a change
  [theResult setRowsAffected:sqlite3_changes(database)];
  [theResult setErrorDescription:[NSString stringWithUTF8String:sqlite3_errmsg(database)]];
  
  NSAutoreleasePool *pool =  [[NSAutoreleasePool alloc] init];
  // We "step" through the results - once for each row.
  // if the statement executed is not a select statement sqlite3_step will return SQLITE_DONE on the first iteration.
  int numTimesToTry = 20;
  int numTriesAttempted = 0;
  int queryResult = 0;
  //NSLog(@"about to attempt");
  while((numTriesAttempted++) <= numTimesToTry){
      //NSLog(@"attempting %i",numTriesAttempted);
      while (YES) {
          queryResult = sqlite3_step(statement);
          if(queryResult == SQLITE_BUSY){
              usleep(20);
              break;
          }
          else if(queryResult == SQLITE_ROW){
              if([theResult columnTypes] == nil){
                  NSAutoreleasePool *pool =  [[NSAutoreleasePool alloc] init];
                  NSMutableArray *columnTypes = [[NSMutableArray alloc] initWithCapacity:0];
                  for(int i = 0; i < numResultColumns; i++){
                      NSNumber * columnType = [NSNumber numberWithInt:sqlite3_column_type(statement,i)];
                      [columnTypes addObject:columnType];
                      //[columnType autorelease];
                  }
                  [theResult setColumnTypes:columnTypes];
                  [columnTypes release];
                  [pool drain];
              }
          }
          else if(queryResult == SQLITE_DONE){
              numTriesAttempted = numTimesToTry+1;
              break;
          }
          else{
              numTriesAttempted = numTimesToTry+1;
              NSString *errorString = [NSString stringWithUTF8String:sqlite3_errmsg(database)];
              //NSLog(@"error %i message  %@",queryResult, errorString);
              [theResult setErrorDescription:errorString];
              break;
          }
          /*
           *  Iterate over all of the columns.  Determine their type and retrieve its value
           *   SQLITE_INTEGER
           *  SQLITE_FLOAT
           *  SQLITE_BLOB
           *  SQLITE_NULL
           *  SQLITE_TEXT
           */
  
          NSMutableArray *row = [[NSMutableArray alloc] initWithCapacity:numResultColumns];
          NSAutoreleasePool *pool =  [[NSAutoreleasePool alloc] init];
          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"];
              }
          }
          [results addObject:row];
          //NSLog(@"current results: %@", results); 
          [pool release];
          [row release];
          row = nil;
      }
  }
  [pool drain];
  if(results == nil){
      results = [NSArray array];
  }
  [theResult setResults:results];
  //[results release];
  //NSLog(@"final results: %@", theResult.results);
  

  }
  else{
  /*NSString *error;
  error = [[NSString alloc]initWithCString:sqlite3_errmsg(database) encoding:NSASCIIStringEncoding];
  [theResult setErrorDescription:error];
  [error release];
  */
  [theResult setErrorDescription:[NSString stringWithUTF8String:sqlite3_errmsg(database)]];

  [theResult setResults:[NSArray array]];
  

  }
  // "Finalize" the statement - releases the resources associated with the statement.
  sqlite3_finalize(statement);
  //[pool drain];
  //NSLog(@"returning: %@",theResult);
  return theResult;
  }

• (DataAccessResult_)startTransaction{
  NSString_ sql = @"BEGIN EXCLUSIVE TRANSACTION";
  return [self setData:sql withParameters:nil];
  }

• (DataAccessResult_)endTransaction{
  NSString_ sql = @"COMMIT";
  return [self setData:sql withParameters:nil];

}

• (DataAccessResult_)rollback{
  NSString_ sql = @"ROLLBACK";
  return [self setData:sql withParameters:nil];
  }
• (void)close{
  if (sqlite3_close(database) != SQLITE_OK) {
  NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
  }
  }

// internal bind methods

• (int) bind_blob:(sqlite3_stmt_)statement withIndex:(int)parameterIndex andBindVariable:(NSData_)aVariable{

  if (![aVariable respondsToSelector:@selector(lengthOfBytes:)]) {
  return -1;
  }
  //by default have the library make a copy, SQLITE_TRANSIENT, since we don't know if the variable may be changed
  //by something else in the application.
  return sqlite3_bind_blob(statement, parameterIndex, aVariable, [aVariable length], SQLITE_TRANSIENT);
  }

• (int) bind_double:(sqlite3_stmt*)statement withIndex:(int)parameterIndex andBindVariable:(id)aVariable{
  //NSLog(@"binding parameter %i", parameterIndex);
  return sqlite3_bind_double(statement, parameterIndex, [aVariable doubleValue]);
  }

• (int) bind_int:(sqlite3_stmt*)statement withIndex:(int)parameterIndex andBindVariable:(id)aVariable{
  return sqlite3_bind_int(statement, parameterIndex, [aVariable integerValue]);
  }

• (int) bind_text:(sqlite3_stmt_)statement withIndex:(int)parameterIndex andBindVariable:(id)aVariable{
  //NSLog(@"binding parameter %i", parameterIndex);
  //assume an ASCII string
  const char_ valueChars = [aVariable cStringUsingEncoding:NSUTF8StringEncoding];
  return sqlite3_bind_text(statement, parameterIndex, valueChars, [aVariable lengthOfBytesUsingEncoding:NSUTF8StringEncoding], SQLITE_TRANSIENT);
  }

• (int) bind_zeroblob:(sqlite3_stmt*)statement withIndex:(int)parameterIndex andBindVariable:(int)aVariable{
  return sqlite3_bind_zeroblob(statement, parameterIndex, aVariable);
  }

• (int) bind_null:(sqlite3_stmt*)statement withIndex:(int)parameterIndex andBindVariable:(id)aVariable{
  return sqlite3_bind_null(statement, parameterIndex);
  }

• (void)dealloc {
  [bindTypeDictionary release];
  [super dealloc];
  }

@EnD

[ns-1m]

This is the function,

             else if(type == SQLITE_BLOB){
                    //NSLog(@"blob");
                    NSData *aData = [[NSData alloc]dataWithBytes:sqlite3_column_blob(statement, colNum) length:sqlite3_column_bytes(statement,colNum)];
                    [row addObject:aData];
                    [aData autorelease];
                }
                else{//SQLITE_NULL
                    //NSLog(@"column type: %d",type);
                    [row addObject:@"null"];
                }

[ns-1m]

case SQLITE_BLOB:
columnValue = [NSString stringWithUTF8String:(char *)sqlite3_column_blob(statement, i)];
columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
[entry setObject:columnValue forKey:columnName];

                        break;

[coomsie]

yip, missing something to do for null and blob

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++;

ns-1m comments might work ...

[ecanas]

The problem is also when entering data it will not enter as null. Was able add the option for case SQLITE_NULL and SQLITE_BLOB as per my code above, however that's only part of the problem since data is not saved as null.

[marcucio]

This is a fix I came up with:

https://github.com/marcucio/Phonegap-SQLitePlugin/commit/8660ba16fd796ad764e0c8a1eda906c47dbf8b57

maybe it fixes your issue

[ecanas]

Looks like will work.. will give it a go. Can you also update the project and add the corresponding code for the case SQLITE_BLOB and SQLITE_NULL.. I updated my version with the following..

case SQLITE_ROW:
i = 0;
entry = [NSMutableDictionary dictionaryWithCapacity:0];
count = sqlite3_column_count(statement);
NSMutableArray *row = [[NSMutableArray alloc] initWithCapacity:count];

            while (i < count) {
                column_type = sqlite3_column_type(statement, i);
                switch (column_type) {
                    case SQLITE_INTEGER:
                        //columnValue = [NSNumber numberWithInt: sqlite3_column_int(statement, i)];
                        //columnName = [NSString stringWithFormat:@"%s", sqlite3_column_name(statement, i)];
                        //[entry setObject:columnValue forKey:columnName];
                        [row addObject:[NSNumber numberWithInt: sqlite3_column_int(statement, i)]];

                        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];
                        [row addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, i)]];
                        break;
                    case SQLITE_BLOB:

                        //NSData *aData = [[NSData alloc]dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement,i)];
                        [row addObject:[NSData dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement,i)]];
                        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]; 
                        [row addObject:[NSNumber numberWithFloat: sqlite3_column_double(statement, i)]];
                        break;
                    //case SQLITE_NULL:
                    default:
                        [row addObject:@"null"];

                        break;
                }
                i++;

            }
            //[resultRows addObject:entry];
            [resultRows addObject:row];

            [row release];
            row = nil;
            break;

[marcucio]

@ecanas your code seems not to work for me, please post a diff or the entire file, maybe i missed something

[ecanas]

Here is the complete code, with the latest change you made. Will see if tomorrow I can post just the difference.. but the only changes are on the line "NSMutableArray *row = [[NSMutableArray alloc] initWithCapacity:count];"

/*

• Copyright (C) 2011 Davide Bertola
  *
• Authors:
• Davide Bertola [email protected]
• Joe Noon [email protected]
  *
• This library is available under the terms of the MIT License (2008).
• See http://opensource.org/licenses/alphabetical for full text.
  */

#import "PGSQLitePlugin.h"

@implementation PGSQLitePlugin

@synthesize openDBs;
@synthesize appDocsPath;

-(PGPlugin_) initWithWebView:(UIWebView_)theWebView
{
self = (PGSQLitePlugin*)[super initWithWebView:theWebView];
if (self) {
openDBs = [NSMutableDictionary dictionaryWithCapacity:0];
[openDBs retain];

    PGFile* pgFile = [[self appDelegate] getCommandInstance: @"com.phonegap.file"];
    NSString *docs = [pgFile appDocsPath];
    [self setAppDocsPath:docs];

}
return self;

}

-(void) respond: (id)cb withString:(NSString )str withType:(NSString *)type {
if (cb != NULL) {
NSString jsString = [NSString stringWithFormat:@"PGSQLitePlugin.handleCallback('%@', '%@', %@);", cb, type, str ];
[self writeJavascript:jsString];
}
}

-(id) getDBPath:(id)dbFile {
if (dbFile == NULL) {
return NULL;
}
NSString *dbPath = [NSString stringWithFormat:@"%@/%@", appDocsPath, dbFile];
return dbPath;
}

-(void) open: (NSMutableArray_)arguments withDict:(NSMutableDictionary_)options
{
NSString *callback = [options objectForKey:@"callback"];
NSString *dbPath = [self getDBPath:[options objectForKey:@"path"]];

if (dbPath == NULL) {
    [self respond:callback withString:@"{ message: 'You must specify database path' }" withType:@"error"];
    return;
}

sqlite3 *db;
const char *path = [dbPath UTF8String];

if (sqlite3_open(path, &db) != SQLITE_OK) {
    [self respond:callback withString:@"{ message: 'Unable to open DB' }" withType:@"error"];
    return;
}

NSValue *dbPointer = [NSValue valueWithPointer:db];
[openDBs setObject:dbPointer forKey: dbPath];
[self respond:callback withString: @"{ message: 'Database opened' }" withType:@"success"];

}

-(void) backgroundExecuteSqlBatch: (NSMutableArray_)arguments withDict:(NSMutableDictionary_)options
{
[self performSelector:@selector(_executeSqlBatch:) withObject:options afterDelay:0.001];
}

-(void) backgroundExecuteSql: (NSMutableArray_)arguments withDict:(NSMutableDictionary_)options
{
[self performSelector:@selector(_executeSql:) withObject:options afterDelay:0.001];
}

-(void) _executeSqlBatch:(NSMutableDictionary*)options
{
[self executeSqlBatch:NULL withDict:options];
}

-(void) _executeSql:(NSMutableDictionary*)options
{
[self executeSql:NULL withDict:options];
}

-(void) executeSqlBatch: (NSMutableArray_)arguments withDict:(NSMutableDictionary_)options
{
NSMutableArray *executes = [options objectForKey:@"executes"];
for (NSMutableDictionary *dict in executes) {
[self executeSql:NULL withDict:dict];
}
}

-(void) executeSql: (NSMutableArray_)arguments withDict:(NSMutableDictionary_)options
{
NSString *callback = [options objectForKey:@"callback"];
NSString *dbPath = [self getDBPath:[options objectForKey:@"path"]];
NSMutableArray *query_parts = [options objectForKey:@"query"];
NSString *query = [query_parts objectAtIndex:0];

if (dbPath == NULL) {
    [self respond:callback withString:@"{ message: 'You must specify database path' }" withType:@"error"];
    return;
}
if (query == NULL) {
    [self respond:callback withString:@"{ message: 'You must specify a query to execute' }" withType:@"error"];
    return;
}

NSValue *dbPointer = [openDBs objectForKey:dbPath];
if (dbPointer == NULL) {
    [self respond:callback withString:@"{ message: 'No such database, you must open it first' }" withType:@"error"];
    return;
}
sqlite3 *db = [dbPointer pointerValue];

const char *sql_stmt = [query UTF8String];
char *errMsg = NULL;
sqlite3_stmt *statement;
int result, i, column_type, count;
int previousRowsAffected, nowRowsAffected, diffRowsAffected;
long long previousInsertId, nowInsertId;
BOOL keepGoing = YES;
BOOL hasInsertId;
NSMutableDictionary *resultSet = [NSMutableDictionary dictionaryWithCapacity:0];
NSMutableArray *resultRows = [NSMutableArray arrayWithCapacity:0];
NSMutableDictionary *entry;
//NSObject *columnValue;
//NSString *columnName;
NSString *bindval;
NSObject *insertId;
NSObject *rowsAffected;

hasInsertId = NO;
previousRowsAffected = sqlite3_total_changes(db);
previousInsertId = sqlite3_last_insert_rowid(db);

if (sqlite3_prepare_v2(db, sql_stmt, -1, &statement, NULL) != SQLITE_OK) {
    errMsg = (char *) sqlite3_errmsg (db);
    keepGoing = NO;
} else {
    for (int b = 1; b < query_parts.count; b++) {
        bindval = [NSString stringWithFormat:@"%@", [query_parts objectAtIndex:b]];
        if([bindval isEqualToString:@"<null>"])
            bindval = NULL;
        sqlite3_bind_text(statement, b, [bindval UTF8String], -1, SQLITE_TRANSIENT);
    }
}

while (keepGoing) {
    result = sqlite3_step (statement);
    switch (result) {

        case SQLITE_ROW:
            i = 0;
            entry = [NSMutableDictionary dictionaryWithCapacity:0];
            count = sqlite3_column_count(statement);
            NSMutableArray *row = [[NSMutableArray alloc] initWithCapacity:count];

            while (i < count) {
                column_type = sqlite3_column_type(statement, i);
                switch (column_type) {
                    case SQLITE_INTEGER:
                        [row addObject:[NSNumber numberWithInt: sqlite3_column_int(statement, i)]];
                        break;
                    case SQLITE_TEXT:
                        [row addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, i)]];
                        break;
                    case SQLITE_BLOB:
                        [row addObject:[NSData dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement,i)]];
                        break;
                    case SQLITE_FLOAT:
                        [row addObject:[NSNumber numberWithFloat: sqlite3_column_double(statement, i)]];
                        break;
                    case SQLITE_NULL:
                        [row addObject:@"null"];
                        break;
                    default:
                        [row addObject:@""];
                        break;
                }
                i++;

            }

            [resultRows addObject:row];
            [row release];
            row = nil;
            break;
        case SQLITE_DONE:
            nowRowsAffected = sqlite3_total_changes(db);
            diffRowsAffected = nowRowsAffected - previousRowsAffected;
            rowsAffected = [NSNumber numberWithInt:diffRowsAffected];
            nowInsertId = sqlite3_last_insert_rowid(db);
            if (previousInsertId != nowInsertId) {
                hasInsertId = YES;
                insertId = [NSNumber numberWithLongLong:sqlite3_last_insert_rowid(db)];
            }
            keepGoing = NO;
            break;

        default:
            errMsg = "SQL statement error";
            keepGoing = NO;
    }
}

sqlite3_finalize (statement);

if (errMsg != NULL) {
    [self respond:callback withString:[NSString stringWithFormat:@"{ message: 'SQL statement error : %s' }", errMsg] withType:@"error"];
} else {
    [resultSet setObject:resultRows forKey:@"rows"];
    [resultSet setObject:rowsAffected forKey:@"rowsAffected"];
    if (hasInsertId) {
        [resultSet setObject:insertId forKey:@"insertId"];
    }
    [self respond:callback withString:[resultSet JSONRepresentation] withType:@"success"];
}

}

-(void) close: (NSMutableArray_)arguments withDict:(NSMutableDictionary_)options
{
NSString *callback = [options objectForKey:@"callback"];
NSString *dbPath = [self getDBPath:[options objectForKey:@"path"]];
if (dbPath == NULL) {
[self respond:callback withString:@"{ message: 'You must specify database path' }" withType:@"error"];
return;
}

NSValue *val = [openDBs objectForKey:dbPath];
sqlite3 *db = [val pointerValue];
if (db == NULL) {
    [self respond:callback withString: @"{ message: 'Specified db was not open' }" withType:@"error"];
}
sqlite3_close (db);
[self respond:callback withString: @"{ message: 'db closed' }" withType:@"success"];

}

-(void)dealloc
{
int i;
NSArray *keys = [openDBs allKeys];
NSValue *pointer;
NSString *key;
sqlite3 *db;

/* close db the user forgot */ 
for (i=0; i<[keys count]; i++) {
    key = [keys objectAtIndex:i];
    pointer = [openDBs objectForKey:key];
    db = [pointer pointerValue];
    sqlite3_close (db);
}

[openDBs release];
[appDocsPath release];
[super dealloc];

}

@EnD

[ns-1m]

I try to use this patch above Cordova 1.5, app is crashing.

[ecanas]

marcucio, I made a few other changes now it's 100% working with nulls. Where can I post the 2 updated files.

[marcucio]

If you are set up for github you can add it to your account if not you can use gist:

https://gist.github.com/

[ecanas]

Ok..used gist.. here is the link
https://gist.github.com/2340433