Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Thu Dec 14, 2017 10:02 am
xojo

All times are UTC - 5 hours




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 19 posts ]  Go to page Previous  1, 2
Author Message
 Post subject:
PostPosted: Wed Aug 01, 2007 10:05 am 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 19, 2007 5:56 pm 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
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.


Top
 Profile  
 
 Post subject: Re: Tips for using databases with REALbasic
PostPosted: Wed Feb 11, 2009 11:26 am 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
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


Top
 Profile  
 
 Post subject: Re: Tips for using databases with REALbasic
PostPosted: Wed Oct 28, 2009 2:44 pm 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
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.

_________________
Mike Bailey
Ekim Software
http://www.ekimsoftware.com/


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 19 posts ]  Go to page Previous  1, 2

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group