Tips for using databases with REALbasic

Mike Bailey · **Posted:** Wed Aug 01, 2007 10:05 am

21. Using null values with your databases. SQL databases have a concept of a NULL value which indicates that there is not a value in that column. NULL is different than an empty string '' or a 0, since these are both actual values.

One thing to remember about nulls is that two nulls are not alike since they do not actually contain a value. This means that null does not equal null. It is almost like each null value is a separate instance of a class, they may look the same but they don't compare.

A common mistake is to treat nulls like any other value in SQL:

select * from people where pet = null

This will not return any results, even if there are null values in the pet column, because of the note above. The null value contained in the pet column is not the same as the literal null value in the SQL. In order to check to see if the value is a null value, then you have to use the IS NULL operator. Going back to the class reference, this is like using the ISA operator on a class in REALbasic:

select * from people where pet is null

To get values where the value is not a null value then you can use the opposite:

select * from people where pet is not null

Now, you have your data in a RecordSet class, but you need to check to see if the value is null or assign to it. The DatabaseField.Value (you get a DatabaseField from RecordSet.IdxField or RecordSet.Field) property returns a variant that holds the value in the field. The Variant class has an IsNull property which allows you to determine if the value is null or set the value to null.

When inserting a record with the DatabaseRecord class, if you want to insert a null value, then you can just leave that value out of the DatabaseRecord and the engine will use the default value for that column, which is null unless otherwise specified in the schema.

Mike Bailey · **Posted:** Sun Aug 19, 2007 5:56 pm

22. I've seen a number of people looking to move data from a physicaly database to an in-memory database, or the reverse. Here are two functions which copy the contents of a REALSQLDatabase to an In-Memory database and also the reverse going back out to disk, no matter what the schema is:

Function CopyToDisk(extends memdb as realSQLDatabase, file as folderItem) As boolean
  // Copy the contents of an in-memory database to disk
  
  // Create the on disk database
  dim diskdb as new realSQLDatabase
  diskdb.databaseFile = file
  if not diskdb.createDatabaseFile() then
    return false
  end
  
  // Copy the database schema
  dim rs as recordSet
  dim table,tables(-1) as string
  rs = memdb.sqlSelect( "select tbl_name, sql from sqlite_master where type = 'table'" )
  memdb.sqlExecute "begin transaction"
  while not rs.eof
    tables.append rs.field( "tbl_name" ).stringValue
    diskdb.sqlExecute rs.field( "sql" ).stringValue
    rs.moveNext
  wend
  memdb.commit
  rs.close
  
  // Attach the disk database to the in-memory one
  if not memdb.attachDatabase( diskdb.databaseFile, "diskdb" ) then
    return false
  end
  
  // Now copy the records over
  for each table in tables
    memdb.sqlExecute "begin transaction"
    memdb.sqlExecute "insert into diskdb."+ table +" select * from "+ table
    if memdb.error then
      memdb.rollback
      return false
    end
    memdb.commit
  next
  
  // Detach the disk database
  memdb.detachDatabase "diskdb"
  
  // Indicate that the copy was successful
  return true
  
  exception err as runtimeException
  return false
End Function


Function CopyToMemory(extends diskdb as realSQLDatabase) As realSQLDatabase
  // Copy the contents of a REAL SQL Database file to an in-memory database
  
  // Create the in-memory database
  dim memdb as new realSQLDatabase
  call memdb.connect()
  
  // Copy the database schema
  dim rs as recordSet
  dim table,tables(-1) as string
  rs = diskdb.sqlSelect( "select tbl_name, sql from sqlite_master where type = 'table'" )
  memdb.sqlExecute "begin transaction"
  while not rs.eof
    tables.append rs.field( "tbl_name" ).stringValue
    memdb.sqlExecute rs.field( "sql" ).stringValue
    rs.moveNext
  wend
  memdb.commit
  rs.close
  
  // Attach the disk database to the in-memory one
  if not memdb.attachDatabase( diskdb.databaseFile, "diskdb" ) then
    return nil
  end
  
  // Now copy the records over
  for each table in tables
    memdb.sqlExecute "begin transaction"
    memdb.sqlExecute "insert into "+ table +" select * from diskdb."+ table
    if memdb.error then
      memdb.rollback
      return nil
    end
    memdb.commit
  next
  
  // Detach the disk database
  memdb.detachDatabase "diskdb"
  
  // Return the resulting in-memory database
  return memdb
  
  exception err as runtimeException
  return nil
End Function

You can use these functions with the following syntax:

// copy the contents of a physical database file to memory
dim memdb as REALSQLDatabase = diskdb.copyToMemory()

// copy an in-memory database to a file on disk
dim f as folderItem = desktopFolder.child( "data.sdb" )
if memdb.copyToDisk( f ) then
end

These functions could be modified to take an array of table names if you want to only copy over specific tables. These functions do not transfer over indexes, triggers, etc, but that would not be difficult to add. You can get the sql command for those in the same manner that the table sql is retrieved.

Mike Bailey · **Posted:** Wed Feb 11, 2009 11:26 am

23. REALbasic and REAL Server both use the SQLite database engine. Often times people want to know which specific version of the SQLite engine is included with certain releases. To find this out connect to your database and run the following SQL command:

dim rs as recordSet
rs = db.sqlSelect( "select sqlite_version()" )
msgBox rs.idxField( 1 ).stringValue
rs.close

Mike Bailey · **Posted:** Wed Oct 28, 2009 2:44 pm

24. Tip 10 shows a very basic way to add data into a SQL command with the ReplaceAll function, however this can be problematic. If the string being placed into the SQL command has a similar placeholder (?2) then you could end up with corrupt data in your SQL command.

Here are a couple functions that will give you a nicer syntax and prevent these types of problems:

Protected Function BindSQLData(sql as string, paramArray values() as string) As string
  // Bind data to a SQL command with a param array syntax.
  return bindSQLData( sql, values )
End Function

Protected Function BindSQLData(sql as string, values() as string) As string
  // Bind data into a SQL command in place identified by a question mark placeholder '?'.  The
  // placeholder can either be enclosed in quotes or not.
  // The data can either be provided as a single array or multiple parameters to the function.
  // A value of chr(0) will be set to null in the SQL command.
  
  dim result as string
  
  // Split the sql command into chunks
  dim chunks(-1) as string = split( sql, "?" )
  
  // Now loop through all the chunks and add in the data.
  dim i, dataIdx as integer
  for i = 0 to ubound( chunks )
    // Get the next chunk of data
    dim chunk as string = chunks(i)
    dim nextChar as string = left( chunk, 1 )
    
    // Get the data value to bind in this position.
    dim value as string
    if i > 0 then// Skip the first chunk since there isn't a value preceeding the statement
      value = "null"// Default to null if there is no data.
      
      if dataIdx <= ubound( values ) then// Make sure we have data for this chunk
        value = values( dataIdx )
        
        // Format the data
        if value = chr(0) and nextChar <> "'" then// Data is null and not quoted
          value = "null"
        else// Data is not null
          value = replaceAll( value, "'", "''" )
          if nextChar <> "'" then// Add quotes if there aren't any already
            value = "'" + value + "'"
          end
        end
        
        // Move to the next piece of data
        dataIdx = dataIdx + 1
      end
    end
    
    // Add the new data to the sql command
    result = result + value + chunk
  next
  
  return result
End Function

To uses these functions you simply write your SQL command with a question mark in places where the data will be placed, then you call the BindSQLData function with either an array of values or a param array. For example:

dim sql as string
sql = "insert into people (first_name, last_name) values (?, ?)"

// Param array
msgBox dbUtils.bindSQLData( sql, "Mike", "Bailey" )

// Array of values
dim values() as string = array( "Tucker", "O'reilly" )
msgBox dbUtils.bindSQLData( sql, values )

Quotes will automatically be escaped out, and if you set a value to chr(0) then a null value will be placed in your SQL command.

These functions are a lot safer to use and will work even if the data being added to the SQL has question marks in it.

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

Tips for using databases with REALbasic