To create a database and tables from within REALbasic you may as well forget about the rubbish and buggy SQLite ‘browser’ that is built into REALbasic and instead use code. If writing a commercial application you need to use code anyway as presumably you want the database to be created on the users machine either as part of a startup method or following a specific ‘Create Database’ instruction from the user.

Lets assume you are creating a Contacts Manager program. Your user can create a new Contacts database by selecting ‘New Database’ and they are presented with a window similar to the one shown below:




The code behind the Select button looks like this:

Dim f as FolderItem
f=SelectFolder
If f<> Nil Then
edtDBPath.Text = f.URLPath
End If
edtDBPath.Text = edtDBPath.Text + "ContactsDB"
pbProceed.Enabled = True
pbProceed.Default = True

The code behind the Proceed button, which is now enabled (pbProceed.Enabled = True) , looks like this:

dim booResult as Boolean = mSQLite.fCreateDBIfNotExist(edtDBPath.Text)
if booResult = True then
dim booInsert as Boolean = mSQLite.fCreateDefaultRecords
if booInsert = True then
dim booConnect as Boolean = mSQLite.fConnectDB
wMain.SETpDirtyWindow(True)
end if
end if
Self.Close

A couple of explanations are in order here I think. mSQLite is a Module which contains all the code that interacts directly with the database. wMain.SETpDirtyWindow is a method on my main application window, wMain, this method sets the Property pDirtyWindow on wMain to True. In the Activate event for the window wMain the Property is checked and if True the window reloads its data. This is necessary because you have just created or selected a new database.

So the code above attempts to create the selected database if it does not exist. If this is successful (booResult = True) it then attempts to populate this database with default data. If this is successfull (booInsert = True) then a connection is made to the database.

Next we’ll look at the code for the three functions in the mSQLite module that do the actual work.

mSQLite.fCreateDBIfNotExist
This method is passed the pathname to the database you have just created / selected (edtDBPath.Text). The code looks like this:

Protected Function fCreateDBIfNotExists(strFileName as String) As Boolean
Dim f as FolderItem
f = GetFolderItem(strFileName,2)
if f.exists Then
Return True
Else
'Create Database
dim folTarget as FolderItem
dim booResult as Boolean

db = New REALSQLDatabase
folTarget = GetFolderItem(strFileName,2)

db.DatabaseFile = folTarget
booResult = db.CreateDatabaseFile

'Contacts Table
dim strCreateContactsTable as String = "CREATE TABLE contacts (con_pk INTEGER PRIMARY KEY,"
strCreateContactsTable = strCreateContactsTable + "con_company_name VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_title VARCHAR(20),"
strCreateContactsTable = strCreateContactsTable + "con_first_name VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_last_name VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_phone_number VARCHAR(20),"
strCreateContactsTable = strCreateContactsTable + "con_email VARCHAR(20),"
strCreateContactsTable = strCreateContactsTable + "con_free_text VARCHAR(1000),"
strCreateContactsTable = strCreateContactsTable + "con_addr1 VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_addr2 VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_town VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_county VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_postcode VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_country VARCHAR(50))"
dim strCreateContactsIndex1 as String = "CREATE INDEX con_pk ON contacts (con_pk)"
dim strCreateContactsIndex2 as String = "CREATE INDEX con_company_name ON contacts (con_company_name)"
dim strCreateContactsIndex3 as String = "CREATE INDEX con_last_name ON contacts (con_last_name)"

'Country Table
dim strCreateCountryTable as String = "CREATE TABLE countries (cry_pk INTEGER PRIMARY KEY,"
strCreateCountryTable = strCreateCountryTable + "cry_code VARCHAR(20) NOT NULL,"
strCreateCountryTable = strCreateCountryTable + "cry_name VARCHAR(50) NOT NULL)"
dim strCreateCountryIndex1 as String = "CREATE INDEX cry_pk ON countries (cry_pk)"
dim strCreateCountryIndex2 as String = "CREATE INDEX cry_code ON countries (cry_code)"

if booResult = True then
'Create Schemas
db.SQLExecute(strCreateContactsTable)
db.SQLExecute(strCreateContactsIndex1)
db.SQLExecute(strCreateContactsIndex2)
db.SQLExecute(strCreateContactsIndex3)
db.SQLExecute(strCreateCountryTable)
db.SQLExecute(strCreateCountryIndex1)
db.SQLExecute(strCreateCountryIndex2)

db.Commit
db.Close
Return True
else
‘ Error Message Shown Here
Return False
end if
End if
End Function

The code above attempts to open the database file at the location you selected via your SQLite window earlier. If the file exists already then True is returned and nothing further is executed within this method. If however the file does not exist a new REALSQLDatabase is created.

The SQL code necessary to create the required tables is created by building up a string for each table and index we require. Here we are creating a Contacts Table and a Country table (to use for showing the Countries in a PopupMenu when the address is being entered). In this tutorial I’m not going to attempt to teach SQL, there are many sources for SQL introductory texts, Google is your friend

Once we have created the SQL strings necessary to create our tables and views we peform an Execute against ‘db’ (our new database) passing the SQL strings as a parameter.

The db.Commit is not actually required as the above will automatically commit as we are making changes to the database structure. All being well we return the value True and the next mSQLite function is called.

This post has gone on too long so I’ll cover creating the default database records and connecting to the database in the next post.

Any feedback, good, bad, indifferent and also any suggestions for what you would like to see covered - leave a comment or drop me an email to stevecholerton@mac.com

RSS Feed: http://feeds2.feedburner.com/Lonelyhacker

Tags: REALbasic, Tutorial

REALbasic comes with build in support for the REALsql database which is basically a single user version of SQLite. This is ideal for use as a local cache for preferences information, window settings etc. It is also useful for so much more.

In this, the first of a series of tutorial posts I will present ideas and code for utilising the database from within REALbasic. I don’t suggest this is the only way or the best way - but it works and works well.

When writing database applications I try and support multiple databases. To ensure that the code for each is optimised to that particular database I generally design this as follows:

First I setup a property that tells me which database I have chosen to work with. For the purposes of this tutorial we will setup this property against the Application and refer to it as App.pDatabase.

I also setup a pair of modules:

mDBWhich
mDBSQLite

When a database access is required, for example a ‘Save’ button is pressed on a Form, the simplified sequence of events looks like this:

Form
mDBWhich.SaveCustomer(parameters)

mDBWhich.SaveCustomer
Select Case App.pDatabase
Case ‘SQLite’
mDBSQlite.SaveCustomer(parameters)
Case’Oracle’
.
.
.
End Select

One advantage of this method is that your form knows nothing about your database so if and when you decide to add support for an additional database to your application it is necessary only to add additional switching to the mDBWhich module and create a new module for your new database. There is no need for your GUI to be altered.

We’ll see some real examples of this in action in later posts, but for know bear it in mind but don’t worry about it. All will become clear. In the next post I will show you how to create a database, populate it with tables and indexes and populate them with some default data. All ‘on the fly’ from REALbasic.

Any feedback, good, bad, indifferent and also any suggestions for what you would like to see covered - leave a comment or drop me an email to stevecholerton@mac.com

RSS Feed: http://feeds2.feedburner.com/Lonelyhacker

Tags: REALbasic, Tutorial

Took the train down to Wolverhampton today to pickup my new motorbike, a 2008 Triumph Scramber. Lovely condition, 1000 miles on the clock and with only 60 Bhp it’s guaranteed to slow me down

The Scrambler is based on the bike that Steve McQueen was often photographed with at the end of the 60’s. A Bonneville with ‘off road’ style tyres and a high level exhaust system was what passed for ‘motorcross’ in those days. The 2008 model is reminiscent of the models from the 60’s but with up to date fuel injection and reliability.

First impressions are good with a traditional upright riding position which encourages you to look around and enjoy the scenery, the seat wasn’t particularly comfortable after 30 miles or so and after riding my V4 for the last few months I had forgotten that twins vibrate - although not enough to be uncomfortable. The bike came with some of the extras fitted, the competition exhaust, the sump protector and the headlight protector and I think the addition of the optional tiny flyscreen will round it off nicely.

Up until November last year I had a Harley Davidson Night Rod Special and that came with guaranteed ‘pavement appeal’. Men, women and children of all ages would give the thumbs up, wave or approach you and talk to you about your bike.

This was sometimes fun and other times a pain in the ass if you were in a hurry. The Triumph - based on only one day - is going to be similar, only it appears it’s only the men of a certain ago who are interested !

Looking at the picture of the Harley above I’m starting to wonder why I got rid of it ... I still think it is one of the best looking production bikes ever - and with the Revolution engine it kicked some serious Japanese ass I definitely need more funds and a bigger garage.

RSS Feed: http://feeds2.feedburner.com/Lonelyhacker

Tags: General, Triumph, Scrambler, Harley Night Rod Special, Triumph Scrambler

They all drive a fair bit of traffic and they are in my opinion essential to drive visitors to your site and therefore sales. Some download sites have their fair share of problems though ...

Anonymous Star Ratings

You’re competitors love this. It took me a while to click onto this but competitors love rating your software as 1 star with no explanation asked for or recorded

User Reviews and Non Anonymous Star Ratings

You’re competitors have to go to the trouble of registering a User ID but they find it’s worth the effort. In addition you are fair game for anybody with access to a computer ... This was recently recorded against one of my products which I have since had removed.

Review: ‘This product is not freeware !!!!’
Rating: 1 Star

So, someone has seen your product and decided that as it is not free that you deserve a 1 star rating. Because all software should be free - right ?

Maybe there should be a minimum age or IQ requirement for people leaving software ‘reviews’

Website Badges

Some site takes your product (without your permission) and upload it to their server. They automatically issue you a 5/5 or a ‘Recommended’ badge which you are encouraged to put on your website. Ridiculous - hopefully you are not that desperate !

RSS Feed: http://feeds2.feedburner.com/Lonelyhacker

Tags: Download Sites

I had been in search of a hosting site who will provide a platform for my Cloud-based applications. I needed SFTP, MySQL, unlimited storage and unlimited bandwidth, all at a reasonable cost. I could have provided my own server with these items, but have found the cable network unreliable in the past and the service lacks support (from me) when I go on holidays! There was always some limitation with hosting services even for those who said they're unlimited. I have now found a provider who seems truly unlimited, but that is another story.

The point is that we all have our limits even when we are offered an unlimited service. Given an unlimited choice of ice-cream, most of us would have a heart-attack after five. Offered a 'free' 5 kg steak if we can eat it beats us after just 1 kg. Given unlimited music downloads, we find we can never listen to more than a few days worth, it clogs our iPod screens causing us to gain RSI from scrolling and we delete the chaff down to a few thousand. We have limits on friends, cars, wives/husbands (hopefully one!), holidays, even money. Winning the lottery has destroyed families, friendships and lives. The more we can get of something, does not lead us to want even more. We all have our limits.

Working in IT and living in the UK I have a hard time with contentment. There is always something new daily that I must have. Sometimes I dream what would I really want if I could have everything I wanted. In the end I find the scope of my dreams is much smaller than I imagined. This helps me get my dreams under control and become content.

BTW my host www.siteground.com has removed their 5 Gb limit and gone unlimited. There is no way I can begin to fill their servers before the problem of managing all that data brings me crashing to reality.

RSS Feed: http://feeds2.feedburner.com/Lonelyhacker

Tags: Dreams