TweetFollow Us on Twitter

Nov 01 Databases

Volume Number: 17 (2001)
Issue Number: 11
Column Tag: Database Basics

REALbasic Database Basics

by Colin Faulkingham

Introduction

It is hard to find an application today that does not rely on some sort of database. Even on the Macintosh we're seeing an increased use of databases, although some find the state of databases on the Mac to be somewhat behind what you would see on a PC. REALbasic is changing that. Mac users can now join the legions of VB users in creating simple, fast, and effective database applications. REAL Software, Inc. makes databases accessible for the beginner, but also provides the advanced user with powerful tools for connectivity. With REALbasic Professional you can connect to Oracle, 4th, Dimension, PostGreSQL, and ODBC data sources. Valentina has even created a REALbasic Plug-in for using their database technology in REALbasic. For our project we will use the built-in database technology that REAL Software, Inc. has provided: the Real Database. This built-in database is a powerful single user solution that covers the needs of most applications.

Requirements

To build the example project, you will need REALbasic 2.1. If you don't own REALbasic, you can download a 30-day demo version via the web at http://www.realsoftware.com. This article will provide you with the fundamentals for creating and working with databases in REALbasic. It will show you the tools that REALbasic provides for creating database applications quickly and efficiently. This article assumes that you are already familiar with REALbasic.

Getting Started

Let's get started by walking through the application to see what the basic functions need to be. This article will teach you how to create databases dynamically, open databases, query databases, add, update and delete records. First, you should know that there are two ways of working with the Real Database. You can simply create the database using the built-in database schema editor or you can write code that creates your database. Using a database made in the schema editor can be used for the quick application where you could simply reference the database object in your project window. For the purposes of this article you will create a database using code. The application that you will be building will be part of an address book manager for managing your email addresses and phone numbers.

REALbasic uses SQL (structured query language) to create and query your database. You will be using built-in database functions in the form of classes that are built into REALbasic to edit, delete and add records.

Once you know the structure of the database, shown here in Table 1, you can easily create the database document and then add the address table and columns with a simple SQL statement.

Column Name Data Type
ID Integer
FirstName Varchar
LastName Varchar
EmailAddress Varchar
Phone Varchar

Table 1. Creating the database

To create the address table with a SQL statement, do this:

  • Launch REALbasic.
  • Drag a pushbutton control from the Tools palette on to window1
  • Change the caption property of the button to "Create"
  • Double-click on the button1 to display the Code Editor
  • Choose New Property from the Edit menu
  • Type "db as database" in the Declaration field and click the OK button
  • Add the database file type by selecting the File Types from the Edit menu, click the Add button, and fill in the necessary data, as shown in Figure 1.

File types are used by you application to define what file type your application will use or create.


Figure 1.

In the Action event handler of the Create button, enter the following code:

Dim dbfile as folderItem

// Create the file reference and and create the database
dbfile=getsaveFolderItem("AddressDB",".rdb")
if dbfile <> nil then

//using the built in function to create the database
db = NewREALDatabase(dbFile)

//Execute the SQL statement to create the table and columns
db.SQLExecute("create table Addresses (Id integer not null, FirstName varchar, 
LastName varchar,Email varchar not null,Phone varchar, primary key (Id)")

End If

The code above for this button displays a Save As dialog box, creates a document that will store the database tables and records, and creates the necessary tables and columns that your application will use. While you probably recognize the integer data type, varchar is not so obvious. A varchar column is a column that will store strings/text. As you can see there is a Primary Key reference in the SQL statement; this indicates which column uniquely identifies each row and is a requirement for creating your database. REALbasic will not allow you to create a database without it.

Now choose Run from the Debug menu and click the Create button to create the new file. To check to see if you have actually done it correctly, drop the database file you created into your project window. Double click to view it, using the built-in Schema Editor. Figure 2 shows the list of tables in the Schema Editor and Figure 3 shows the Edit Table window. REALbasic has quite a few column types: varchar, integer, double, smallint, float, Boolean, date and time. The other attribute that you should be aware of is "not null" which tells the database that the corresponding field must contain data. This is extremely important if your application needs to use the data in any particular field for all the records. It also is a requirement for the primary key field.


Figure 2.


Figure 3.

Adding Records

At this point you need to add a couple of items to your window so you can add records to your database.

  • Drag a button from the Tools palette to window1.
  • Change the caption property of the pushbutton to "New".
  • Add the following code the Action event of the New button.

REALbasic's built-in databaserecord class is for creating and accessing records. You will be using it to build a record that you are going to insert into your database table.

Dim rec as databaserecord

//Create a new Record object
Rec=new databaserecord

//You will see that there are various column types in the //databaseRecord class. Column being of the 
varchar type.

Rec.column("FirstName")="Steve"
Rec.column("LastName")="Jobs"
Rec.column("Phone")="(111)123-456"
Rec.column("Email")="Sjobs@apple.com"
Rec.integerColumn("Id")=1

//insertrecord is a method of the database class
db.insertrecord("Addresses",rec)
db.commit

You will notice that you are using the commit method of the database class. This method commits the changes to the database. This is essentially a safety net. In a transactional database like the REALdatabase, commit and rollback are used to protect your database. Commit actually makes the changes and the rollback method brings the database back to the state before the last commit was made. Note: REALbasic also has an implicit commit when the user quits the application.

Opening the Database

Before you add these records to the database you need to add a couple more items to your project so you can view the records that you are going to add. Let's add an Open button that will open the database and display all the records in the Addresses table.

  • Add another button to Window1 and change its caption to "Open".
  • Drag a Listbox into your window.
  • In your properties window change the Listbox1 column count to 5
  • Make sure your Listbox1 is wide enough to show the columns.
  • Drag a DatabaseQuery control in to your window.

REALbasic comes with a DatabaseQuery control that can execute a SQL query and automatically deliver the results of that query into a Listbox or Popupmenu control. You tell the DatabaseQuery control where to put the results of the query using a concept called "binding." Binding lets you connect two controls with an action. One control is the source and the other is the target. In this case, the source is the DatabaseQuery control, which will perform the query, and the target is the Listbox control, which will display the results of the query. To bind the DatabaseQuery control to the Listbox, do this:

  • While holding the Command and Shift keys, drag from the DatabaseQuery control to the Listbox control.
  • When the New Binding dialog box appears, choose "Bind Listbox1 with list data from DatabaseQuery1 results," as shown in Figure 4.
  • Click OK.


Figure 4.

The DatabaseQuery has a couple of properties that you need to be aware of. One is the reference to the database, which is a property of the DatabaseQuery control; since the database is not being referenced in your project you will have to add the database property in code at the time you make the query. Another is the SQL Query. The SQL Query property will hold the SQL query statement that you want the DatabaseQuery control to perform. Now, as you can see in Figure 5, you are going to add the SQLQuery in the Properties window under the Behavior heading for the DatabaseQuery control since you will be executing the same SQL query over and over again.


Figure 5.
"Select FirstName,LastName,Phone,Email,Id from Addresses"

The SQL SELECT statement is most commonly used to choose the columns of data you wish to see from a specific table in the database based on a criterion. You could also use an asterisk, which would indicate that all of the columns should be returned.

To execute the query and display the results in the Listbox, the DatabaseQuery control's RunQuery method must be called. This will cause the DatabaseQuery control to perform the query. Since the DatabaseQuery control is bound to the Listbox, the results from the select statement will display in the Listbox. So, to make the Open button open the database file, perform the query and display the results, enter the following code into the Action event handler of the "Open" button.

Dim f as folderitem
f=getopenfolderitem("addressDB")

//OpenRealDatabase which is a global method to open your REALdatabase
if f<> nil then
db=openRealDatabase(f)

//Execute your query control to update your listbox
DatabaseQuery1.database=db
DatabaseQuery1.runquery
end if

The code above first presents the user with an open dialog and then uses the global method openRealDatabase (File as a Folderitem) to open the database; then a query is made by the DatabaseQuery control. Now from the Debug menu, choose run and click the Open button. Navigate to the database file you created earlier and open it. Click the add button and as you can see in Figure 6, the records you added to your database are displayed in the Listbox automatically.


Figure 6.

Editing and Deleting Records

The next step to building any database application is being able to update and remove records at will. This involves creating a DatabaseCursor, which is not much more difficult than creating a record. A DatabaseCursor is simply a pointer to a set of records returned by a query. It contains the actual rows and columns of data returned by your query. Let's use the spreadsheet in Figure 7 as an example database of 4 addresses. Let's say you performed a query that selected the FirstName, LastName and Phone columns for people whose ID is less than or equal to 2. Figure 8 shows the data would make up the cursor returned by such a query.


Figure 7.

To create the cursor you would need to execute this SQL query

"SELECT FirstName,LastName,Phone From Addresses WHERE Id=2"


Figure 8.

Now that you have a better understanding of what a cursor is you should be ready to manipulate your data. First you need to build your cursor with an SQL statement (note: make sure your SQL statement is on one line, for formatting reasons we cannot show it on one line in this article).

To change the record you created you will need to add an "Edit" button to do that follow these steps.

Add a button to Window1 and change its caption to "Edit"

In the Action event handler for that button insert this code:

Dim updateCursor as databasecursor

updateCursor = db.SQLSelect("select * from Addresses where Email='Sjobs@apple.com'")

//To edit the cursor that you have selected you need to call the 
//databasecursor edit method Calling the edit method on a multi-user 
//database will lock the necessary tables.

updatecursor.Edit

In the code below, field is returning a cursorfield object and the setstring is a method of that class and is used to change the column in a record. There are a couple of ways you can step through your fields. You can either use the below method of simply referencing the field by name or you can use the IdxField (Index as integer) to reference it by number in a 1-based array.

updateCursor.field("Firstname").setstring "Billy"
updateCursor.field("Lastname").setstring "Jobs"
updateCursor.field("Phone").setstring "(111)000-0000"
updateCursor.field("email").setstring "Bjobs@apple.com"

//Next, you need to call the update method from the DatabaseCursor 
//class so that it updates the updateCursor object not the database.
updateCursor.Update

// If you do not use the close method of the databasecursor class REALbasic will do an 
//implicit close.

UpdateCursor.close

//commit the changes to the database
db.commit

//run query to update the ListBox 
DatabaseQuery1.database=db
DatabaseQuery1.runQuery

Deleting records is a fairly simple operation and it also involves building a databasecursor. After selecting a row, you simply need to call the cursor's DeleteRecord method. Let's add a Remove button that will delete Billy Jobs record:

Drag a new button from your tools palette and make the caption property "Remove"
In your Remove button action event handler insert this code:

Dim cur as databasecursor
//Select a record that is in your database based on your criteria
cur=db.SQLSelect("select * from Addresses where Email ='Bjobs@apple.com'") 
//Call the DatabaseCursor DeleteRecord method .
cur.deleteRecord
cur.close 
//commit the changes to the database
db.commit
//Run the database query control to update the ListBox results
DatabaseQuery1.database=db
DatabaseQuery1.runquery

Conclusion

The code snippets above are a good starting point, but you really need to get under the hood of the database class and the database cursor class to perform a wide range of functions.

These are the basic functions that you need to create a database driven application. The tools provided in REALbasic are easy enough for a beginner, yet powerful enough to give the advanced user leverage in making production level data-driven applications. If you're planning a commercial or enterprise level application, using the built-in database probably won't cut it; you would probably want to investigate using other databases such as Valentina or a tried and true server such as Oracle or 4D Server. Whatever your database tasks may be you will find REALbasic a pleasure to work with.

References

REALbasic
http://www.realbasic.com
http://www.realsoftware.com Valentina
http://www.paradigmasoft.com/ 4D Server
http://www.acius.com/ Oracle
http://www.oracle.com
 

Community Search:
MacTech Search:

Software Updates via MacUpdate

iShowU Instant 1.3.3 - Full-featured scr...
iShowU Instant gives you real-time screen recording like you've never seen before! It is the fastest, most feature-filled real-time screen capture tool from shinywhitebox yet. All of the features you... Read more
Affinity Photo 1.8.3 - Digital editing f...
Affinity Photo - redefines the boundaries for professional photo editing software for the Mac. With a meticulous focus on workflow it offers sophisticated tools for enhancing, editing and retouching... Read more
Affinity Designer 1.8.3 - Vector graphic...
Affinity Designer is an incredibly accurate vector illustrator that feels fast and at home in the hands of creative professionals. It intuitively combines rock solid and crisp vector art with... Read more
Pinegrow 5.95 - Mockup and design web pa...
Pinegrow (was Pinegrow Web Designer) is desktop app that lets you mockup and design webpages faster with multi-page editing, CSS and LESS styling, and smart components for Bootstrap, Foundation,... Read more
WhatRoute 2.3.1 - Geographically trace o...
WhatRoute is designed to find the names of all the routers an IP packet passes through on its way from your Mac to a destination host. It also measures the round-trip time from your Mac to the router... Read more
Dropbox 94.4.384 - Cloud backup and sync...
Dropbox for Mac is a file hosting service that provides cloud storage, file synchronization, personal cloud, and client software. It is a modern workspace that allows you to get to all of your files... Read more
Boom 3D 1.3.6 - $19.99
Boom 3D is a revolutionary app with 3D Surround Sound and phenomenally rich and intense audio that is realistic and works on any headphones. Features 3D surround sound Built-in audio player... Read more
Remotix 6.1.4 - Access all your computer...
Remotix is a fast and powerful application to easily access multiple Macs (and PCs) from your own Mac. Features: Complete Apple Screen Sharing support - including Mac OS X login, clipboard... Read more
iMazing 2.11.4 - Complete iOS device man...
iMazing (was DiskAid) is the ultimate iOS device manager with capabilities far beyond what iTunes offers. With iMazing and your iOS device (iPhone, iPad, or iPod), you can: Copy music to and from... Read more
EtreCheck Pro 6.2.4 - For troubleshootin...
EtreCheck is an app that displays the important details of your system configuration and allow you to copy that information to the Clipboard. It is meant to be used with Apple Support Communities to... Read more

Latest Forum Discussions

See All

Hearthstone's new Demon Class is no...
A couple of weeks ago Blizzard announced that the game would be introducing its first new class since the game first launched in 2014. It's called the Demon Hunter and as of today, players will be able to get a glimpse and feel for how it will... | Read more »
The 5 Best Games Like Dark Souls
There's no escaping the fact that everyone loves Dark Souls. If you've not heard of it, it's a super tough action RPG that punished your every mistake with swift retribution. It's the sort of game where you die a lot, and it can be pretty... | Read more »
The Curse of Issyos is a retro-inspired...
The Curse of Issyos is the latest game to be ported to mobile and published by Abylight S.L who previously worked on Cursed Castilla. Much like that port, this game was also originally created by Spanish developers Locomalito. The Curse of Issyos... | Read more »
Same Room Games is a great collection of...
Staying at home under lockdown kinda sucks. Totally necessary, yes, but also more than a little anxiety-inducing and often boring. Thankfully, we've got lots of great multiplayer games to play with friends and keep us sane, the latest of which,... | Read more »
Bark Park is a joyfully silly multiplaye...
Bark Park is a charming, good-humoured game about competing against other players to claim as much territory as possible by peeing on nearby trees. Did I mention that everyone is playing as a dog? Makes things a little less weird. [Read more] | Read more »
The 5 Best Games Like Grindstone
Apple Arcade made a splash when it first launched, granting access to over 50 games as soon as it became available. Of those titles, the one that seemed to grab most people’s attention was Grindstone, a matching puzzle game from Capy Games. [Read... | Read more »
Awarding-winning puzzler Make One is now...
Make One is a game with a simple concept that it executes with flair and impressive levels of polish. It comes to us from developer Brazle and publisher PiG, and it's just landed this week for Android. At its core, Make One is a logic-based... | Read more »
Marvel Contest of Champions' latest...
Kabam's Marvel Contest of Champions has now teased some of the latest characters heading to the Battlerealm next month. Here's your chance to watch the new motion comic embedded below, Barons' War, before I spoil the surprise. [Read more] | Read more »
Why Calibria: Crystal Guardians is the p...
We could all use escapism right now, something to focus on and a project to feel part of during these times of social isolation. If you’re one of the many who’s turned to gaming to fill the lockdown void, Calibria: Crystal Guardians should be on... | Read more »
Mini Tank: Fire is a Worms-like, turn-ba...
Mini Tank: Fire is an online multiplayer game that takes a few cues from the likes of Worms and Shell Shock Live. It will feature 5v5 turn-based battles and is available now for both iOS and Android as a free-to-play game. [Read more] | Read more »

Price Scanner via MacPrices.net

Clearance 13″ 128GB MacBook Air in stock toda...
Guitar Center has clearance 2019 13″ 1.6GHz/128GB Space Gray MacBook Airs in stock today for $899 shipped. That’s $200 off Apple’s original MSRP for this model and the cheapest price available today... Read more
Weekend sale: 21″ & 27″ iMacs for up to $...
B&H Photo has new 21″ and 27″ 5K iMacs in stock today and on sale for up to $200 off Apple’s MSRP. These are the same iMacs sold by Apple in their retail and online stores, and B&H’s prices... Read more
Here’s how you can save $50 today on one of A...
– 11″ 128GB WiFi iPad Pro: $789 $10 off – 11″ 256GB WiFi iPad Pro: $849 $50 off – 11″ 512GB WiFi iPad Pro: $1049 $50 off – 11″ 1TB WiFi iPad Pro: $1249.99 $50 off – 11″ 128GB WiFi + Cell iPad Pro: $... Read more
Apple AirPods Pro back on sale at Amazon for...
Amazon has Apple’s AirPods Pro in stock and on sale today for $234.98 shipped. Their price is about $15 (6%) off Apple’s MSRP and the cheapest price available for these new models from any Apple... Read more
Apple restocks clearance, previous-generation...
Apple has clearance, Certified Refurbished, 2019 13″ MacBook Airs available for only $779. Each MacBook features a new outer case, comes with a standard Apple one-year warranty, and is shipped free.... Read more
Retro Reading: PowerBook Central Coverage of...
FEATURE: 04.03.20- Welcome to another edition of “Retro Reading,” a series here in my column, “Mac Potpourri,” where we dig deep into the archives for content written on today’s date several years... Read more
Price drop! 21″ 3.6GHz Quad-Core 4K iMac on s...
B&H Photo has the 21″ 3.6GHz Quad-Core 4K iMac in stock today and on sale for $1099.99 including free next day shipping to many locations in the US. That’s $200 off Apple’s MSRP: – 21″ 3.6GHz... Read more
Amazon is again offering $129 Apple AirPods,...
Amazon has Apple AirPods with Charging Case on sale again for $129.98 shipped. Their price is $30 off Apple’s MSRP for these AirPods. Their price is the cheapest available for AirPods from any Apple... Read more
Apple continues to offer iMac discounts rangi...
Apple has Certified Refurbished 2019 21″ & 27″ iMacs available starting at $929 and up to $350 off the cost of new models. Apple’s one-year warranty is standard, shipping is free, and each iMac... Read more
Apple offers Certified Refurbished iPhone XS...
Apple has Certified Refurbished iPhone XS models available for up to $350 off MSRP, with prices starting at $699. Each iPhone is unlocked and comes with Apple’s standard one-year warranty and a new... Read more

Jobs Board

Tier II Support Specialist- *Apple* - KeenL...
…will be responsible for Desktop Support of an environment composed of Apple workstations and several custom business applications. Job Responsibilities: + Providing Read more
Sephora Merchandise/Operations Supervisor -...
Sephora Merchandise/Operations Supervisor - Apple Blossom Mall Location:Winchester, VA, United States- Apple Blossom Mall 1850 Apple Blossom Dr Job Read more
Sales Supervisor - *Apple* Blossom Mall - J...
Sales Supervisor - Apple Blossom Mall Location:Winchester, VA, United States- Apple Blossom Mall 1850 Apple Blossom Dr Job ID:1083621Store Hourly Positions Read more
*Apple* Mac Product Engineer - Barclays (Uni...
Apple Mac Engineer Whippany, NJ Support the development and delivery of solutions, products, and capabilities into the Barclays environment working across technical Read more
Essbase Developer - *Apple* - Theorem, LLC...
Job Summary Apple is seeking an experienced, detail-minded Essbase developer to join our worldwide business development and strategy team. If you are someone who Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.