TweetFollow Us on Twitter

Getting Started with REALBasic-Part 4

Volume Number: 25 (2009)
Issue Number: 01
Column Tag: Programming

Getting Started with REALBasic-Part 4

Designing the Application

by Norman Palardy

REALbasic is a Rapid Application Development (RAD) tool from REALSoftware. In the last column we implemented a big chunk of the database for our stock tracker application. REALbasic 4 and 4.1 have been released in the interim and we'll move up to using that version as there are a number of bug fixes and improvements in it.

Back to it

I've been extremely busy getting the last two releases of REALbasic out and apologize to everyone for the hiatus.

This time the focus is going to be on getting several stocks symbols into the database, retrieving quotes for each of them and storing the data back in the database.

Right now the database is cleaned out every time we start up. While it makes for a nice clean starting point, it's really not that useful in an application that is designed to store data for a long period of time.

We'll need to start by looking at the things that happen when we start up our application. If the database already exists, that's great and we don't need to worry about doing much more. If it doesn't, we need to create it.

But, how are we going to know what database the user wants to use and where it might be?

There are at least a couple ways of dealing with this. One would be to read a preference from some repository like a preferences file and use that. The other would be to use a "well defined place" to store this. Conveniently on OS X and Windows there is a convention for exactly this purpose. On OS X this is in the users Application Data directory. And REALbasic makes it easy to access.

So let's alter the App.Open event to handle all this for us.

// if the Application Data directory has a directory for our app
// and the directory holds our database then we should use that one
if SpecialFolder.ApplicationData.Child(app.kAppName).Exists = true _
and SpecialFolder.ApplicationData.Child(app.kAppName).Child(kDBName).Exists = true then
  app.db.DatabaseFile =  SpecialFolder.ApplicationData.Child(app.kAppName).Child(kDBName)
    app.db = new REALSQLDatabase
    
    app.db.DatabaseFile = SpecialFolder.ApplicationData.Child(app.kAppName).Child(kDBName)
    // see if we can open the database
    if app.db.Connect() <> true then
      // oh oh .... something bad 
      app.db = nil
      msgbox "Unable to connect to database"
      return
    end if
  else
    // hmm is there a directory for our application ?
    if SpecialFolder.ApplicationData.Child(app.kAppName).Exists <> true then
      // no so better create one
      SpecialFolder.ApplicationData.Child(app.kAppName).CreateAsFolder()
    end if
    
    // is there a database ?
    if SpecialFolder.ApplicationData.Child(app.kAppName).Child(app.app.kDBName).Exists <> true then
      
      // no database file
      
      // create a new instance REASQLDatabase Class
      app.db = new REALSQLDatabase
      
      // set the file to the one in the users Library > Application Support
      app.db.DatabaseFile =  SpecialFolder.ApplicationData.Child(app.kAppName).Child(app.kDBName)
      
      // create the container database file
      if app.db.CreateDatabaseFile() <> true then
        
        // something bad      
        app.db = nil
        
        msgbox "Unable to create database"
        
        return 
      else
        
        // ok so now create the database tables
        CreateDBTables()
        
      end if
      
    end if
    
  end if  

As you look this code over you should notice that I've used two constants for the name of the directory, App.kAppName, and the name of the database, App.kDBName.

Add a constant to the App instance in your project and name it kAppName. Set the value for it to MyStockApp. And add the second constant, kDBName, and set its value to "MyStocks.db"

You'll also need to create an empty method in the App instance called CreateDBTables.

We'll fill this one in later.

If you run the app at this point you should find things still work, but you are no longer prompted to select a database. Instead one is automatically place in ~/Library/Application Support/MyStockApp called MyStocks.db.

This is exactly as it should be.

This new database is completely empty though. It doesn't even have the tables we need and this is what we'll fix in the new CreateDBTables method.

But how to get the same definitions we came up with before? It turns out that OS X includes sqlite and we can actually get it to dump out all the SQL we'll need to create the database. Open Terminal.app (located in /Applications/Utilities) and type in

sqlite3

and then drag the database into the terminal window. sqlite will open the database and then you can run the one SQL command needed

select sql from sqlite_master where type = 'table' 

In my case this gives me the exact sql I need to create the tables

CREATE TABLE StocksOfInterest(Name Varchar, Symbol VarChar)
CREATE TABLE StockQuote(Symbol Text, Price Double, quoteDateTime Timestamp)

I can take this and incorporate it into the CreateDBTables method as follows:

  app.db.sqlexecute("CREATE TABLE StocksOfInterest(Name Varchar, Symbol VarChar)")
  app.db.sqlexecute("CREATE TABLE StockQuote(Symbol Text, Price Double, quoteDateTime Timestamp)")

Note that what we have is one language, REALbasic, telling another tool, sqlite, to execute a command in it's language, SQL, that it understands and can do something useful with.

REALbasic does not know anything about SQL. It's just strings of characters to REALbasic.

And SQL knows nothing about REALbasic and it's variables, methods, or anything else.

This is very important to realize that they know nothing about each other and that getting them to work properly together is sometimes and exercise in making sure the string you've created in your REALbasic code IS the right SQL. Sometimes testing things out by hand is the only good way to figure out where your error is if you have trouble. This makes the inclusion of sqlite very handy on OS X.

In fact, let's test this out. If you have a database in ~/Library/Application Support/MyStockApp then delete it and run the program again. Once it has run, quit and then, in Terminal, ahave sqlite3 ope nthe newly created database.

Again use the sql command

select sql from sqlite_master where type = 'table' 

and you should see that there are the two commands that we had in our application to create the tables. We've now created the tables programmatically.

Moving On

Now that we have created the database lets see how we can get quotes into it in an automated way.

I happen to like using Yahoo because the service is easy to grab data from. That said you can probably use whatever service you want. Some will be easier and some more difficult.

What we need to know is how to get the "current" quote from Yahoo for a specific stock in a form we can do something useful with.

Yahoo has a nice simple URL for this exact thing

http://download.finance.yahoo.com/d/quotes.csv?f=sl1d1t1c1ohgv&e=.csv&s=<symbol>

In a browser if you replace <symbol> with AAPL this will download a file to your machine that is a csv delimited text file with the most recent quote for AAPL. You can alter a number of other attributes to get other data but this is the form we want.

So what we need to do is get REALbasic to go to this URL, get the data and parse it apart and put it in our database and the redisplay the data. And to do this every "once in a while". Nothing to it :)

It turns out that REALbasic has just the thing for this task: the HTTP socket. There are two ways to use this; synchronously and asynchronously. The simpler of the two is synchronously and so that's what we'll use.

But how to get REALbasic to do this "once in a while"? Again, REALbasic has a nice object called a Timer that can be used to do things periodically. Note that timers are "patient" in that they try to work as close to the timing you set but if something else is really busy they will wait until there is a chance for them to do their thing. So they are reasonably accurate in running periodically but not 100% precise.

We'll make use of the timer as it is good enough for what we need.

Open the wStocks window and scroll down the list of controls, select the timer, and drag one onto the workspace.


Figure 1 - Selecting the timer


Figure 2 - Timer placed on Workspace

A timer has several useful properties that we will want to adjust.


Figure 3 - Timer Properties

The Mode property controls whether the timer is on and active or not and if it is one whether the timer runs and performs its action once, or repeatedly. You can toggle this setting in your program as well as setting it directly in the IDE.

The other setting is the Period, or minimum time between the timer performing its action. If the Timer is set to run its action multiple times this will be the minimum time between actions. Careful though, as the period is specified in milliseconds and the setting shown above is only 1 second. We should set this to something more like 300,000, which is every 5 minutes.

Let's make a method that gets the stock data from the web and inserts into the database first.

Open the App instance and create a new method called GetStockPrice. This should take one parameter; the Symbol of the stock we want the quote for. Mine looks like

Sub GetStockPrice(symbol as string)
  dim HTTP as HTTPSocket
  dim data as string
  
  if trim(symbol) = "" then return
  
  // we're going to grab the price from Yahoo
  // the URL is like
  // http://download.finance.yahoo.com/d/quotes.csv?f=sl1d1t1c1ohgv&e=.csv&s=<symbol>
  dim url as string = "http://download.finance.yahoo.com/d/quotes.csv?f=sl1d1t1c1ohgv&e=.csv&s=" + symbol
  
// make a new HTTP socket to use
  http = new HTTPSocket
  
// tell it to get the data and wait no more than 30 seconds for a reply
  data = HTTP.Get(url, 30)
  
  // the data we get back is like
  // "AAPL",117.05,"8/16/2007","4:00pm",-2.85,116.91,118.50,111.62,66667828
  
// pull out the price (the seccond field)
  dim stockPrice as double = val(nthfield(data,",", 2) )
  
// and use our already existing method to put it in the database
  app.AddDataForStock Symbol, stockPrice , new Date
  
End Sub

Now if you double click the Timer we can add the code that will grab the stock quotes every time the timer runs its Action. In the timer simply put

App.GetStockPrice("AAPL")

This will grab the new quotes for AAPL every 5 minutes. But we also want to update the display with those. And eventually we want to be able to make this grab a list of stocks, not just one or two.

First let's deal with displaying the stock quotes.

If you look in the open event for the listbox that shows the stock quotes it has a bunch of code already that we just need to move somewhere else so we can have it redisplay things on demand, not just when the window opens. This is a perfect opportunity to refactor this code so it's more generally useful.

Start by creating a new method called ReDisplayData.

Take all the code from the Open Event of the listbox and move it into this new method. You'll have to change every place it says "me." to "lstStocks."

Then, in the Listbox open event simply have it call ReDisplayData so it looks like

  RedisplayData()

And add a call to RedisplayData at the end of the timer's actions as well so it looks like

  GetStockPrice("AAPL")

  RedisplayData()

If you run now you should see the display get a new entry for AAPL every few minutes. However, the display of the stock proce might look a little odd. On mine, as of this writing it shows "9.74000000056e+1".

So lets change Redisplay data a little.

If you look at the line that reads

lstStocks.Cell(lstStocks.LastIndex,2) = rs.Field("Price").StringValue

and change it to

lstStocks.Cell(lstStocks.LastIndex,2) = Format(rs.Field("Price").DoubleValue,"#.00")

This will make things look right. The original line just used an implicit conversion of a floating point double to a string. The default is to give you scientific notation. The FORMAT command gives us control over how the conversion to a string occurs and so it looks better.

Lots of Progress

Just in this short sprint we've made a lot of progress. We've made the application create it's own database if none existed. We've added the ability to periodically grab new quotes from the internet and redisplay them. And, when you quit and restart your application it will start up with all the data you had before. Not bad for this time around.

There are some things you might want to look into until next time.

ReDisplayData just keeps adding to the list shown all the time and so if you leave this application running for a long time it will eventually have an enormously long list of items displayed. You might see if you can chnage that.

And for the really adventurous, see if you can change JUST the SQL that ReDisplayData uses so it only grabs the latest quote instead of all of them.


Norman Palardy has worked with SQL databases since 1992, and has programmed in C, C++, Java, REALbasic and other languages on a wide variety of platforms. In his 15+ years of IT experience, Norman has developed innovative and award-winning applications for TransCanada Pipelines, Minerva Technologies (now XWave), Zymeta Corporation, and the dining and entertainment industry. He holds a BSc from the University of Calgary in Alberta. He's also a founder of the Association of REALbasic Professionals (http://www.arbp.org) and currently works for REAL Software

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

FotoMagico 5.6.12 - Powerful slideshow c...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more
OmniGraffle Pro 7.12.1 - Create diagrams...
OmniGraffle Pro helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use... Read more
beaTunes 5.2.1 - Organize your music col...
beaTunes is a full-featured music player and organizational tool for music collections. How well organized is your music library? Are your artists always spelled the same way? Any R.E.M. vs REM?... Read more
HandBrake 1.3.0 - Versatile video encode...
HandBrake is a tool for converting video from nearly any format to a selection of modern, widely supported codecs. Features Supported Sources VIDEO_TS folder, DVD image or real DVD (unencrypted... Read more
Macs Fan Control 1.5.1.6 - Monitor and c...
Macs Fan Control allows you to monitor and control almost any aspect of your computer's fans, with support for controlling fan speed, temperature sensors pane, menu-bar icon, and autostart with... Read more
TunnelBear 3.9.3 - Subscription-based pr...
TunnelBear is a subscription-based virtual private network (VPN) service and companion app, enabling you to browse the internet privately and securely. Features Browse privately - Secure your data... Read more
calibre 4.3.0 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more
Lyn 1.13 - Lightweight image browser and...
Lyn is a fast, lightweight image browser and viewer designed for photographers, graphic artists, and Web designers. Featuring an extremely versatile and aesthetically pleasing interface, it delivers... Read more
Visual Studio Code 1.40.0 - Cross-platfo...
Visual Studio Code provides developers with a new choice of developer tool that combines the simplicity and streamlined experience of a code editor with the best of what developers need for their... Read more
OmniGraffle 7.12.1 - Create diagrams, fl...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more

Latest Forum Discussions

See All

The House of Da Vinci 2 gets a new gamep...
The House of Da Vinci launched all the way back in 2017. Now, developer Blue Brain Games is gearing up to deliver a second dose of The Room-inspired puzzling. Some fresh details have now emerged, alongside the game's first official trailer. [Read... | Read more »
Shoot 'em up action awaits in Battl...
BattleBrew Productions has just introduced another entry into its award winning, barrelpunk inspired, BattleSky Brigade series. Whilst its previous title BattleSky Brigade TapTap provided fans with idle town building gameplay, this time the... | Read more »
Arcade classic R-Type Dimensions EX blas...
If you're a long time fan of shmups and have been looking for something to play lately, Tozai Games may have just released an ideal game for you on iOS. R-Type Dimensions EX brings the first R-Type and its sequel to iOS devices. [Read more] | Read more »
Intense VR first-person shooter Colonicl...
Our latest VR obsession is Colonicle, an intense VR FPS, recently released on Oculus and Google Play, courtesy of From Fake Eyes and Goboogie Games. It's a pulse-pounding multiplayer shooter which should appeal to genre fanatics and newcomers alike... | Read more »
PUBG Mobile's incoming update bring...
PUGB Mobile's newest Royale Pass season they're calling Fury of the Wasteland arrives tomorrow and with it comes a fair chunk of new content to the game. We'll be seeing a new map, weapon and even a companion system. [Read more] | Read more »
PSA: Download Bastion for free, but wait...
There hasn’t been much news from Supergiant Games on mobile lately regarding new games, but there’s something going on with their first game. Bastion released on the App Store in 2012, and back then it was published by Warner Bros. This Warner... | Read more »
Apple Arcade: Ranked - 51+ [Updated 11.5...
This is Part 2 of our Apple Arcade Ranking list. To see part 1, go here. 51. Patterned [Read more] | Read more »
NABOKI is a blissful puzzler from acclai...
Acclaimed developer Rainbow Train's latest game, NABOKI, is set to launch for iOS, Android, and Steam on November 13th. It's a blissful puzzler all about taking levels apart in interesting, inventive ways. [Read more] | Read more »
A Case of Distrust is a narrative-driven...
A Case of Distrust a narrative-focused mystery game that's set in the roaring 20s. In it, you play as a detective with one of the most private eye sounding names ever – Phyllis Cadence Malone. You'll follow her journey in San Francisco as she... | Read more »
Brown Dust’s October update offers playe...
October is turning out to be a productive month for the Neowiz team, and a fantastic month to be a Brown Dust player. First, there was a crossover event with the popular manga That Time I Got Reincarnated as a Slime. Then, there was the addition of... | Read more »

Price Scanner via MacPrices.net

Score a 37% discount on Apple Smart Keyboards...
Amazon has Apple Smart Keyboards for current-generation 10″ iPad Airs and previous-generation 10″ iPad Pros on sale today for $99.99 shipped. That’s a 37% discount over Apple’s regular MSRP of $159... Read more
Apple has refurbished 2019 13″ 1.4GHz MacBook...
Apple has a full line of Certified Refurbished 2019 13″ 1.4GHz 4-Core Touch Bar MacBook Pros available starting at $1099 and up to $230 off MSRP. Apple’s one-year warranty is included, shipping is... Read more
2019 13″ 1.4GHz 4-Core MacBook Pros on sale f...
Amazon has new 2019 13″ 1.4GHz 4-Core Touch Bar MacBook Pros on sale for $150-$200 off Apple’s MSRP. These are the same MacBook Pros sold by Apple in its retail and online stores: – 2019 13″ 1.4GHz/... Read more
11″ 64GB Gray WiFi iPad Pro on sale for $674,...
Amazon has the 11″ 64GB Gray WiFi iPad Pro on sale today for $674 shipped. Their price is $125 off MSRP for this iPad, and it’s the lowest price available for the 64GB model from any Apple reseller. Read more
2019 15″ MacBook Pros available for up to $42...
Apple has a full line of 2019 15″ 6-Core and 8-Core Touch Bar MacBook Pros, Certified Refurbished, available for up to $420 off the cost of new models. Each model features a new outer case, shipping... Read more
2019 15″ MacBook Pros on sale this week for $...
Apple resellers B&H Photo and Amazon are offering the new 2019 15″ MacBook Pros for up to $300 off Apple’s MSRP including free shipping. These are the same MacBook Pros sold by Apple in its... Read more
Sunday Sale: AirPods with Wireless Charging C...
B&H Photo has Apple AirPods with Wireless Charging Case on sale for $159.99 through 11:59pm ET on November 11th. Their price is $40 off Apple’s MSRP, and it’s the lowest price available for these... Read more
Details of Sams Club November 9th one day App...
Through midnight Saturday night (November 9th), Sams Club online has several Apple products on sale as part of their One Day sales event. Choose free shipping or free local store pickup (if available... Read more
Sprint is offering the 64GB Apple iPhone 11 f...
Sprint has the new 64GB iPhone 11 available for $15 per month for new lines. That’s about 50% off their standard monthly lease of $29.17. Over is valid until November 24, 2019. The fine print: “Lease... Read more
New Sprint November iPhone deal: Lease one iP...
Switch to Sprint and purchase an Apple iPhone 11, 11 Pro, or 11 Pro Max, and get a second 64GB iPhone 11 for free. Requires 2 new lines or 1 upgrade-eligible line and 1 new line. Offer is valid from... Read more

Jobs Board

*Apple* Mobility Pro - Best Buy (United Stat...
**746087BR** **Job Title:** Apple Mobility Pro **Job Category:** Store Associates **Store NUmber or Department:** 000319-Harlem & Irving-Store **Job Description:** Read more
Best Buy *Apple* Computing Master - Best Bu...
**743392BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Store Associates **Store NUmber or Department:** 001171-Southglenn-Store **Job Read more
Best Buy *Apple* Computing Master - Best Bu...
**746015BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Store NUmber or Department:** 000372-Federal Way-Store **Job Description:** Read more
*Apple* Mobility Pro - Best Buy (United Stat...
**744658BR** **Job Title:** Apple Mobility Pro **Job Category:** Store Associates **Store NUmber or Department:** 000586-South Hills-Store **Job Description:** At Read more
Best Buy *Apple* Computing Master - Best Bu...
**741552BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Store NUmber or Department:** 000277-Metcalf-Store **Job Description:** **What Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.