[View]  [Edit]  [Lock]  [References]  [Attachments]  [History]  [Home]  [Changes]  [Search]  [Help] 

[coco8] Accessing iOS local Databases with S8

How to access local databases in iOS devices, using S8

The SQLite database engine is built-in to the iOS (and Android) operating system.
SQLite is considered an industry standard for lightweight embedded SQL database programming.

S8 currently accesses SQLite DB Engine by wrapping a third-party Objective-C library called
FMDB. (Building a direct interface avoiding third-parties libs is a work in progress).
So... By now, Be sure to have installed the following FMDB classes in your XCode project:
(You can find the latest version of these classes here )

S8 API for Local Database accessing

Creating a Database

A Database is an instance of FMDatabase
An FMDatabase is created with a path to an actual SQLite database file. If it doesn't exist it will be created.
The following snippet code create an instance of an FMDatabase on 'Sample.sqlite' sqlite database
 | db |

	db := FMDatabase withPath: 'Sample.sqlite' asLocalPathName.

Open and Close a Database

An instance of FMDatabase can be opened and closed using the standard protocol
FMDatabase>>#open
and
FMDatabase>>#close
both of them returns a boolean representing the success of the operation.
Sample Code
 | db |

	db := FMDatabase withPath: 'Sample.sqlite' asLocalPathName.
        db open ifTrue: [ self print: 'DB Opened Successfully'.].
        db close ifTrue: [ self print: 'DB Closed Successfully'.].

Create a Table

A table creation, as well as insertion, modification or deletion of data, is an update to the database, so, it will be done using the standard protocol
FMDatabase>>#executeUpdate: sqlStatement
It returns a boolean representing the success of the operation.
Sample Code
 | db |

	db := FMDatabase withPath: 'Sample.sqlite' asLocalPathName.
        db open ifTrue: [
		success := db executeUpdate:'create table if not exists Books(Id INTEGER PRIMARY KEY, Title TEXT, Author TEXT, Pages INTEGER)'.
		success ifTrue: [self print: 'Table Created successfully'.]
				ifFalse: [self print: 'Can not create the table - '. ].
		db close.
	].

Inserting Records

This is another update to the database, so, it will be done using the standard protocol
FMDatabase>>#executeUpdate: sqlStatement
It returns a boolean representing the success of the operation.
Sample Code
 | db |

	db open ifTrue: [
		db executeUpdate: 'insert into Books (Title, Author, Pages) values (?,?,?)' withArgumentsInArray: #('Smalltalk-80: The Language and its Implementation'  'Adele Goldberg' 714).
		db executeUpdate: 'insert into Books (Title, Author, Pages) values (?,?,?)' withArgumentsInArray: #('Smalltalk, Objects, and Design'  'Chamond Liu' 289).
		db executeUpdate: 'insert into Books (Title, Author, Pages) values (?,?,?)' withArgumentsInArray: #('Smalltalk With Style'  'Edward Klimas' 127).
		db executeUpdate: 'insert into Books (Title, Author, Pages) values (?,?,?)' withArgumentsInArray: #('Smalltalk Best Practice Patterns 1st Edition'  'Kent Beck' 240).
		db close. 
	].

Retrieving Records

It will be done using the standard protocol
FMDatabase>>#executeQuery: sqlQueryStatement
It returns a FMResultSet.
In the following Sample Code, You can see how to retrieve records, and how to deal with the answered resultSet
 | db |

		db open ifTrue: [
		rs := db executeQuery: 'select * from Books where Pages > 200'.
		[rs next ] whileTrue: [
			self print: (' // ', (rs stringForColumn: 'Title')). 
		].
		db close. 
	].


In addition to FMResultSet>>#stringForColumn: aColumnName
FMResultSet provides us with similar messages:

And if You prefer to identify the column by its index instead of it's name: