revUp - Updates and news for the Revolution community
Issue 91 | April 22nd 2010 Contact the Editor | How to Contribute

A Rev DB App, in 3 Secs or Less - #2
Use FmPro Migrator Platinum Edition to unlock the power of SQL Yoga and the Rev Data Grid (and save 50% in April).

by David Simpson

This article is a continuation of the article series showing how to create Rev database applications using FmPro Migrator Platinum Edition. In this article I will show the basic features incorporated into the generated stack file. You can read article one here.

Architecture of the Generated Stack File

SQL Yoga Framework Diagram

This diagram shows the overall architecture of the generated stack file. At the highest level, is the revTalk code generated by FmPro Migrator. revTalk code is placed into the card scripts, object scripts and the Data Grid behaviors within the Data Grid template behaviors.

All of the revTalk code which involves reading, writing and updating data within a database uses high-level SQL Yoga commands. SQL Yoga makes it even easier to work with databases compared to using revDB functions because you can just set properties or work with data within arrays, instead of dealing with lower level revDB cursors and record sets. Working with different types of database servers is also abstracted to one single connection handler which contains the database connection parameters.

SQL Yoga, in turn, makes revDB calls directly in order to perform its tasks with any of the supported SQL databases which it supports.

About the Generated Application Stack - Features Overview

What do I mean by a full-featured database application? Rev stack files generated by FmPro Migrator Platinum Edition incorporate the following commonly used database application features:

Display Records - When the stack is opened and a connection is made to the converted SQL database, the records associated with the converted Layout/Form are displayed in form viewing mode. The fields, text labels, embedded graphics and images are displayed in the same manner as the original database file. Record navigation is implemented using 4 controls:
Next Button, Previous Button, Scrollbar and Record Number field entry. SQL database BLOB column fields containing images are automatically displayed.
Related records are automatically displayed within individual fields and portals/subforms are converted into Rev Data Grids. The related records displayed within Data Grids are automatically updated when advancing to another record in the parent table.

Update Records - Clicking into any field provides the user with the ability to update the contents of the field. Date fields are configured with a Date Picker control. Custom Value Lists from the original database are displayed as drop down menus, pop-up menus, radio button group or checkbox group depending upon the original field definition. Related records displayed within Data Grids can be updated just as easily as records in the main form.

Insert Records - Individual records can be inserted as a new record in either Browse or Find operating modes. Clicking the Insert Portal Row button to the top right of any Data Grid object will add a new record to the related database table. As with the original database implementation, the primary key for the parent table will be automatically inserted into the foreign key column of the new related record.

Delete Records - Individual records can be deleted in either Browse or Find modes. If a Delete Row button or image object was embedded in the original FileMaker Portal, then this same functionality will also be implemented to delete the selected Data Grid row.

Query Records - Query by example record searching is automatically implemented for each card in the stack file. Once a set of records has been found as a result of the query, the navigation buttons can be used to navigate thru the found set of records. Individual records can be inserted or deleted in found set mode as well as within the standard Browse mode.

The generated stack file includes menus which call revTalk card or stack handlers to perform basic tasks such as switching to find mode, showing all records, hiding or showing the status area, creating or deleting records. The status area includes navigation buttons and a place to display information about the current record such as its record number and the total number of records in the table.

Opening the Generated Stack File

The first time you open the generated stack file, there are a few tasks which need to be completed to finish the conversion process and prepare for using and deploying the stack as either an application or revLet. Double-click the Application.rev file which was written into the output directory, and it will open within the Rev IDE.

Application Stack

1) Add SQL Yoga as a substack to the Application stack (unless you are opening the finished stack file from here).

2) Add your SQL Yoga license key into the SQL Yoga custom properties of the Application.rev stack (if you have one). If you don't have a SQL Yoga license key, don't worry, you can still use SQL Yoga in demo mode with up to 10 database records, and periodic periodic reminder messages every 10 minutes.

Enter SQL Yoga License Key

The SQLYoga custom property set contains database connection info for any supported type of database which your application might be connecting. FmPro Migrator fills in this information automatically, but if you need to make a change after you generate the stack, this is the one location where you would change the connection info.

[Note: Later in this article series, I will show you how you can create an XML preferences file which will configure FmPro Migrator to add SQL Yoga and its license keys to your generated stack file automatically.]

3) If you are creating a revLet and you want to include a SQLite or Valentina database to be used locally with the revLet, you should click the LoadDB button, which will load the embedded database into a custom property within the stack file. Once this button has been clicked, the button is hidden, because the end-user doesn't need to see it. The LoadDB and ExtractDB buttons are only visible when generating a stack which will be used as a revLet. And you don't even have to use them at that time, because your revLet could connect directly to a SQL database thru a network connection.

4) If you look at the scripts within the Create SQL Yoga Objects and SQL Yoga Table Objects Behavior buttons they might not make a lot of sense. This is because most of the code which will get executed is stored within the ObjectScript property of each button. The code within the Create SQL Yoga Objects button is a little bit unique because first of all, it does some work to set the card scripts for each card in the generated stack, it sets the button script for the SQL Yoga Table Objects Behavior button, and after it finishes executing, it actually replaces its own script with a new script located within its own ObjectScript custom property. Then, after a 200 millisecond delay it sends a mouseUp message to itself which sets scripts for checkboxes, radio buttons and tab panel objects, builds the SQL Yoga Objects, saves the stack file and hides itself.

This dynamic code execution is only done once within the IDE in order to prepare the objects in the stack for their first use. Since FmPro Migrator is a compiled application, this technique is used to overcome the 10 line script limit for setting or modifying code from within compiled applications. This clever bit of dynamic code execution won't cause problems for iPhone/iPad development using revMobile, because this code only runs one time within the IDE, and it will never need to be run within a compiled application.

Click the Create SQL Yoga Objects button.

Connect to Database

Clicking the Connect button creates the SQL Yoga query objects and then reads custom properties from the stack which hold the database connection parameters. These parameters are then used to make the actual database connection, and then go to the first converted layout card which is card 2.

As soon as the Assets_v8 card opens, you should immediately notice a couple of things. First, upon expanding the size of the card you should notice that all of the elements of the original FileMaker layout have been re-created in the same positions and sizes as the original objects. So for instance you will see text labels, embedded images, rectangles and fields located in the correct locations on the new Rev card. The second (and most exciting) thing to notice is that all of the fields actually contain data which has been read directly from the local SQLite database file. The Data Grid is displaying fields from related records filled with data, and it also contains image fields, drop down menus, pop-up menus, checkboxes, radio buttons, vector graphics and embedded images.

Assets_v8 Card

click to zoom image

Clicking the Next/Previous buttons or dragging the slider enables you to navigate thru the records. Clicking in a field or selecting a menu enables you to change the data within any of the fields. Tabbing out of a field causes the closeField message to be sent to the field, which updates the record in the database. If you have edited a field and click a record navigation button or slider, the modified field will also be updated within the database before advancing to the next record.

So there you have it, a functional database front-end application, generated in a couple of seconds and without manually writing a single line of code. But what if you want to customize the revTalk code yourself? I will explain the code which implements various features of the generated stack in this article and the ones which follow in this series.

revTalk Code Review - Displaying and Updating Data

There are two card handlers which are used to fill the fields with data. The basic algorithm is to first gather the primary key values for every record in the database table. In general, even for large tables, the amount of time required to retrieve the list of primary keys is a relatively short period of time when using a LAN connection to the database. And then complete record data is only gathered for the current record being displayed. Retrieving a complete record of data for only the current record helps minimize the possibility of outdated data being cached on each individual user's computer in a multi-user database system. It is still possible that records could have been inserted or deleted by other users within the SQL database in a multi-user environment, thus making the list of primary keys invalid as well. Unlike a FileMaker Pro database, you won't be notified by a SQL database server when you are caching a deleted record, or if data within some of the fields of a record you are viewing have been modified by another user.

Records Menu

This problem is minimized by the code which runs when the Show All Records menu is selected. If this menu is selected while browsing a found set of records, then Browse mode is restored. If it is selected while running in Browse mode, all of the primary keys are retrieved from the table once again.

getData - Retrieves a list of Primary Key column values for all of the records within the table associated with the card. These values are stored within the array named gDataPKArray_asset_management2 for the assets_v8 card. If the PK array doesn't need retrieved again, then this process is skipped. This is because the user might have simply switched from one card to another and the data might not need to be retrieved again. Passing a value of 1 in the pRefreshAllFlag parameter forces a retrieval of all of the PK values regardless of whether the array already contains values.

refreshFields - Gathers the column data for the current record being displayed along with all of the related records from related tables. It fills all of the fields and Data Grids on the current card. The data for each column of the asset_management2 table is stored within the array named: gDataCRArray_asset_management2. You can think of "CR" as meaning Current Record data within the naming of this array. Setting a debugger breakpoint within this handler shows the data for the parent table.

gDataPKArray_asset_management2

Once the data for the fields has been retrieved, the FmPro["fieldList"] custom property is retrieved from the card. This property consists of a list of all of the fields which need to be refreshed on the card. The data is retrieved for each field, and a switch statement branches with appropriate code to fill each object with the data from the array.

Scrolling down further in the variable watcher shows that there are 2 records within the maintenance_record table which are also stored in the array.

gDataCRArray_asset_management2

 

Part of the beauty of using SQL Yoga is that since a relationship was defined between these two tables SQL Yoga gathered the related records automatically. It is then a simple matter to update the rows of the Data Grid with a one line command.

-- refresh portal data
set the dgData of group "Portal1" to 
gDataCRArray_asset_management2[1]["maintenance_record"] 

Insert Picture Processing for Image Fields

Insert Picture Menu

Microsoft Access OLE Object and FileMaker Container fields are converted into BLOB columns within SQL database tables. A pop-up menu is created over top of the Rev image object for each one of these fields. Clicking the field brings up the Insert Picture dialog box for selection of a file from the local disk. Canceling this dialog clears the image data from the field, and selecting a file saves the file into the database column and displays the new image. Here is the code which is embedded in the pop-up Insert Picture... menu:

on menuPick pItemName
   global gMode
   local tFileName,tImageData
   switch pItemName
      case "Insert Picture..."
         -- select file to insert
         answer file "Select picture to insert..." as sheet
         if it is not empty then
            put it into tFileName
            -- read image from file
            open file tFileName for binary read
            read from file tFileName until end
            close file tFileName
            put it into tImageData
            -- put image into image object
            put tImageData into image \
                   "fld__asset_management2__picture"
         else
            put empty into image \
                   "fld__asset_management2__picture"
         end if -- end of check for cancel
         updateDBRecord image "fld__asset_management2__picture" \
                , "image", "asset_management2", "picture"
         break 
   end switch
end menuPick 

If the user cancels inserting the image, then the field is cleared. If you don't want this to happen, then you could simply comment out the statement:

put empty into image "fld__asset_management2__picture" 

Or if you want to do something more user friendly, you could add an additional CASE block with an item like "Clear Image", and also add this additional value to the menu itself.

Though I have not implemented it here, non-image data could also be handled with additional application-specific code. You can often determine common file formats by checking the first 10 bytes of the file or its file extension. So if the file is an image type of file, you could put the image into the image object and if it is some other type of file you might substitute a different icon to be displayed in the image object.

By default, the image object is set with its lockLoc property set to true. So this means that smaller images are effectively scaled up to fill the size of the entire image object. But if the lockLoc property had not been set to true and a much larger image had been selected, the image object could have grown to fill the entire card. It would take additional image processing to figure out the actual size of the image in order to determine dynamically whether the lockLoc property should be set. This could potentially be done by setting the image data to a hidden image object and then checking the resulting object size.

The Insert Picture dialog also works for image fields displayed within the rows of a Data Grid, but the code looks a little different because it is necessary to figure out which row of the Data Grid is being modified for updating purposes. This is accomplished by getting "the dgIndex of the dgDataControl of me" line of code. The array of data retrieved for the current row is updated with the new data and then the record in the related table is updated with the sqlrecord_update statement.

on menuPick pItemName
   global gMode
   local tFileName,tImageData,tPortalRecordArray
   switch pItemName
      case "Insert Picture..."
         -- select file to insert
         answer file "Select picture to insert..." as sheet
         if it is not empty then
            put it into tFileName
            -- read image from file
            open file tFileName for binary read
            read from file tFileName until end
            close file tFileName
            put it into tImageData
         else
            put empty into tImageData
         end if -- end of check for cancel
         if gMode = "Browse" then 
            put the dgIndex of the dgDataControl of me into \
                   tIndex
            put the dgDataOfIndex[tIndex] of the dgControl of \
                   me into tPortalRecordArray
            put tImageData into tPortalRecordArray[the dgColumn \
                   of me]
            set the dgDataOfIndex[tIndex] of the dgControl of \
                   me to tPortalRecordArray
            -- update the displayed data in the DataGrid row
            sqlrecord_update tPortalRecordArray
            put the result into tError
            if tError is not empty then errorHandler \
                   "Error updating row#" & tIndex & space & tError
         end if -- end of check for browse mode
         break 
   end switch
end menuPick 

This code is located within the on the assets_v8 Portal1 Row Template card of the Data Grid Templates Fmig stack.

Data Grid Image Field

click to zoom image

You can modify any of this code or the objects on these Row Template cards as you find necessary. For instance, some objects like radio buttons and checkboxes will be drawn a little larger in Rev compared to how they appear within FileMaker Pro. So it may be necessary to resize and move objects a little bit.

Conclusion

The Database to Rev Conversion feature built into FmPro Migrator Platinum Edition enables Rev developers to quickly convert popular database files into functional Rev database front end application stack files. A working application can be created in seconds, without manual coding for commonly requested database features. In the next article, I will show how the query by example feature works along with the tab panel controls.

Special Offer

FmPro Migrator Platinum Edition is available for $599 from the RunRev online store, or $399 as an FmPro Migrator Developer Edition upgrade or annual renewal. As a limited time introductory offer until the end of the month of April 2010, .com Solutions Inc. is offering FmPro Migrator Platinum Edition for a 50% discount (new purchase for $299 or upgrade for $199). Use Coupon Code FMPROAPRIL for this special offer. FmPro Migrator Platinum Edition includes license keys for the following features:

  • PHPToRevTalk License Key (Qty = Unlimited)
  • BasicToRevTalk License Key (Qty = Unlimited)
  • AccessToFmPro License Key (Qty = 250)
  • FmProToAccess License Key (Qty = 250)
  • DBToRev License Key (Qty = 250)

Also, Blue Mango Learning Systems is providing a 50% discount for Rev developers who purchase FmPro Migrator Platinum Edition along with SQL Yoga, which is now priced at $99 compared to the regular price of $199 - just put both products in your basket together to apply the discount.

About the Author

David Simpson is the president of .com solutions inc, and a Revolution developer of database applications since 2001.

Main Menu

What's New

Buy revStudio and add the 'Supercharger' pack at the checkoutBuy revEnterprise and add the 'Supercharger' pack at the checkout