HTML5 Builder – How to create a Server Mobile App with InterBase XE3 and Deploy to Android Tutorial

By: Al Mannarino

Abstract: This tutorial provides step-by-step instructions to create a mobile app connecting to a database using HTML5 Builder, and deploy the app to Android.

By Al Mannarino, Embarcadero Technologies

    Introduction

This tutorial provides step-by-step instructions to create a mobile app connecting to a database using HTML5 Builder, and deploy the app to Android.

We will create a Mobile App to access remote database Category information and drill down on the Sub-Categories.

Part 1 -Install the Android Project Tools on your computer. Use android-setup from HTML5 Builder installation files.

    Android Setup

Before you can build your mobile application for Android, you will need to setup the Android SDK and some additional software. HTML5 Builder includes an installation wizard to ease this process. In the folder or CD where you first run the HTML5 Builder installer, you will find this wizard under the name android-setup.exe.

Hide image

Run android-setup.exe and let it guide you through the installation process.

When you run the installer, you will be required to provide administration permissions. Your current user must be the administrator and you must provide administration permissions with it. Do not provide administration permissions through a different user account.

Please see http://docwiki.embarcadero.com/HTML5 Builder/en/Android_Setup for the detail steps for installing the HTML5 Builder Android Project Tools, Java SE Development Kit, Android SDK Tools and Apache Ant.

We will use the current Android 4.1 API 16 for this app. Using the Android SDK Manager, installed here:

"C:\Users\Al Mannarino Mac\AppData\Local\Android\android-sdk\SDK Manager.exe”, add (install) Android 4.1 API 16.

Then add a new AVD for Android 4.1 API 16. From the SDK Manager, use Tools > Manage AVDs > New.

Hide image
Click to see full-sized image

expand view>>

At the end, another wizard window will pop up, and will give you the option to reboot. Click Finish to reboot.

Note: You will not be able to build Android applications until you reboot your system.

    Part 2 - Set-up your InterBase database connection to the osCommerce database

This mobile app will use an InterBase osCommerce database created from Create_osCommerce.sql, located in Appendix A -How to create the InterBase osCommerce database.

a. If you don't already have InterBase installed from the HTML5 Builder installation, then download and Install the InterBase XE3 Developer Edition from: https://downloads.embarcadero.com/free/interbase

    InterBase XE3 Developer Edition

InterBase XE3 Developer Edition provides all developers the best cross-platform database to build and test database applications for embedded and SME segments. Developers are free to choose the standard connectivity they want, and make use of a mature SQL92 compliant database for their applications. Database Performance monitoring allows the developer to streamline applications for optimal deployment.

Warning: This version of InterBase is licensed for development use only. Deployment Edition must be purchased separately.

b. Follow the steps in Appendix A - How to create the InterBase osCommerce database.


    Part 3. Create a Server Mobile Project

1. Open HTML5 Builder.

You can create a new mobile application from Home > New > HTML5 Builder Projects > Server Mobile Application, or you can add a new Server Mobile Page form to an existing Server Mobile Application from Home > New > HTML5 Builder Projects > Files > Server Mobile Page.

Note: A Server Mobile Application is a web application that runs in a server and is specially designed for mobile devices. You can use any server or client-side web technologies, and built-in support for database interaction and AJAX is provided. Advanced mobile theme features are also available. And once deployed to the server, it can be accessed through a web browser from any device, but it can also be deployed as a standalone application installable in most mobile devices, without any additional code!

We will then get the first file (unit1.php) of our project opened in the Designer (Design view) and the code from the Code view:

Hide image
Click to see full-sized image Hide image
Click to see full-sized image

As you can see, the Designer for mobile applications includes the possibility to preview different devices and includes backgrounds for them. You can also customize available devices and include your own, just review the documentation about it.

expand view>>

expand view>>

I own an Android Samsung Galaxy SII phone, so I’m going to select that, but you can select whatever device or tablet you wish to use and/or deploy it.

Now, before anything else, we should set a meaningful name for our project and our first file, unit1.php. You can do so from the Project Manager pane, located by Home >> Project Manager.

Hide image

DO: Right-click Project1 >> Save As >> Create new folder called CategoryLookup to save the project >> Save unit1 as main.php. Save Project1 as CategoryLookupProject.

Hide image
Hide image

SAY: We will also change from the Design view >> Object Inspector the Caption property of our MPage container to the name of our application: Category Lookup

Hide image

Design the Mobile Interface

Right now I only care about the main screen, it should be a simple application, as simple as possible, and I want to use it quickly, the less I have to tap the better.

When working with mobile applications, we should be careful when we choose components. My rule is: use a mobile component whenever you can, use other components whenever you have to. This way we both avoid incompatibility issues and leave the door open to use Mobile Theming later (can only be used with mobile components).

In the Designer, on the Tool Palette, expand the Mobile and Mobile Hardware sections and find out there are mobile versions of every component I need for my mobile interface. For example, I’d use a MEdit instead of Edit, MButton instead of Button and MList instead of ListBox. For Mobile Hardware, I have access the Accelerometer, Camera, Geolocation, Mobile Database, etc.

Hide image
Click to see full-sized image Hide image

For my interface, I only need a MList and MLabel.

expand view>>


Do: Drag and drop a MList and MLabel component onto the MPage. Resize and place it so it looks nice. Change MLabel Caption to “osCommerce Category Lookup” and Color to whatever looks nice to you:

Hide image
Click to see full-sized image

A MList is a dynamic list that we can fill up with data, either entered manually or we can set-up a database connection and retrieve the data from a database.

expand view>>

MList allows you to create an ordered or unordered list. Every element on the list can have up to two links, the secondary link will render as an Extra Button. MLists can be nested.  With an MList, we can:

  • Control the Theme and color variation of a MList with the MobileTheme component associated to it.
  • With DividerTheme a MobileTheme can be associated to all the dividers included in the MList
  • ExtraButtonTheme allows you to associate another MobileTheme component to the Extra Button of every element on the list.
  • Change the SystemIcon of the Extra Button to a different icon.
  • Select your own Icon for the Extra Button.
  • Indicate the Type of list: tOrdered or tUnordered
  • With isFiltered we'll add a filter Bar on the top of the list
  • isWrapped wraps the list with a grouped style. 
  • use Items to add elements to the List. Every item has the following attributes:
    • Caption Text to display, HTML are tags allowed.
    • Link Url of the page to load.
    • MList Select a Mlist from a list of the available MLists on the form to include a nested MList.
    • ExtraButtonHint Alternative text for the Extra Button.
    • ExtraButtonLink URL of the Extra Button. 
    • isDivider Will make this element a list divider.
    • CounterValue Numeric input that will display on the right side of the element.
    • Thumbnail Image to use as thumbnail on the right of the element
    • isIcon indicates that the Thumbnail image is an icon and will be displayed in a diferent way.


    Part 4 - Connect to InterBase Database using HTML5 Builder

In HTML5 Builder, using the Data Explorer tab, create a new InterBase connection to your osCommerce database (created from Appendix A).

Data Explorer lets you add database connections, and generate components already setup to work with those databases. You can access it from the Design view of your project:

Hide image

Data Explorer makes developing database applications extremely easy and fast. Once you add a connection to a database, you can browse the whole database from HTML5 Builder, and drag and drop pieces of data from the Data Explorer into the Designer, to generate data-aware components already setup to work with that data.

1. Right-click on INTERBASE | Add New Connection | Connection Name = osCommerceXE3

Hide image
Hide image

Click OK.

2. Right-click on the osCommerceXE3 node >> Modify Connection.

3. Enter your connection values for your InterBase instance.

Database Name = C:\Embarcadero\InterBase\examples\database\OSCOMMERCE.gdb

User Name = sysdba Password = masterkey

4. Click Test Connection to verify connection.

Hide image

5. In Data Explorer, expand your "osCommerceXE3" connection and verify you have your tables:

Hide image

6. As an optional feature to explore, right-click on osCommerceXE3 | SQL Window |and select and drag categories and categories_descriptions onto the designer. Look at the "Code" and "Data" tabs.

Hide image
Click to see full-sized image Hide image
Hide image

expand view>>

Note: You have the option to select several background images for mobile devices for Android, iOS, BlackBerry and Windows Phone devices and tablets, plus the option to add a New Device to your project. HTML5 Builder includes many device images located here: C:\Program Files (x86)\Hi5Beta6\repository\images\

Note: It does not matter which background you select, you will get to target Android, iOS, BlackBerry or Windows Phone device from the Wizard for PhoneGap.


We’ll use an Android Samsung Galaxy S II for this project.

Hide image
Hide image

7. Next, to get the data, we'll use a database connection. We'll be using our osCommerceXE3 database. From Data Explorer, drop the categories table onto the mobile device, like this:

Hide image

This adds four components to the page (Database, Table, Datasource and DBRepeater) onto the page.

InterBase is a relational database management system developed by Embarcadero Technologies, the company that is also behind HTML5 Builder.

NOTE: InterBase is already set-up and configured to work with HTML5 Builder, so no changes need to be made to any of these configuration and/or .ini files listed here.


There are different technologies you can use to access InterBase from web applications developed with HTML5 Builder:

PHP PDO Extension

Enable the extension by uncommenting (removing the ; at the beginning) the following line in PHP’s configuration file (php.ini):

;extension=php_pdo_firebird.dll → extension=php_pdo_firebird.dll

    PHP Extension

Enable the extension by uncommenting (removing the ; at the beginning) the following line in PHP’s configuration file (php.ini):

;extension=php_interbase.dll → extension=php_interbase.dll

  • HTML5 Builder’s internal server supports InterBase by default.

    Configuration

Tip: You can use the Data Explorer to configure your InterBase connections the RAD way.

To use a Database component with InterBase, configure it with the following property values:

  • DriverName: ibase.
  • DatabaseName: This should be filled with the database server address and database file path, separated by a colon (:). For example: localhost:C:\Users\User\Desktop\database.gdb.

Then fill the rest of the mandatory properties as required. You can ignore the Host property.

    Help Resources

    Documentation

    Frequently Encountered Problems connecting to InterBase

Undefined service gds_db/tcp

Warning: ibase_pconnect(): Unable to complete network request to host "<host IP>". Failed to locate host machine. Undefined service gds_db/tcp. in […]

The error message above usually indicates that there is no entry of “gds_db 3050/tcp” in the services file on either the client or the server machine1. To fix the issue, add the entry to the file:

  • On Windows: %windir%\system32\drivers\etc\services.
  • On Linux: /etc/services.

gds_db 3050/tcp # InterBase

    See Also

Official website

8. Delete the DBRepeater component. We don't need the DBRepeater for our mobile application. We'll be using a MList to display the data on our mobile device.

9. Add a Query component (from Data Access) onto the page, and set Database = dbosCommerce1, set Active = true.

Hide image

10. On the same Query component, double-click the SQL property and add this SQL:

select * from categories

left join categories_descriptions on

categories_descriptions.categories_id=categories.categories_id

and categories_descriptions.language_id=1

Hide image

This query returns all the categories and their category descriptions for language = English (1).

Click OK.

11. Select the MList component. Set DataSource = dsCATEGORIES1, Set Dataset = Query1

Hide image

11. Set DataMapping on MList to correspond to your database table values to display on the MList, like this:

a. The BaseParentFieldValue = 0 will be the first value (column) to display on our MLIst.

b. Set Caption to be what we want to use for each element on the MList = CATEGORIES_NAME

c. Set idField = CATEGORIES_ID

d. Lastly we will establish the ParentField = PARENT_ID

Hide image
Hide image
Click to see full-sized image

SAVE ALL
12. Run the mobile application (F9). Your MList should display your CATEGORIES values, like this:

expand view>>

Hide image

13. Click on the Hardware category, and you will drill down to its sub-categories, like this:

Hide image
Click to see full-sized image

Congratulations!

expand view>>

NOTE: This sample osCommerce database only has this one level of drill down data, but if it had more, the application would continue to drill down as needed. If you click on any of these sub-categories, you will just get a blank screen, and that's normal expected behavior.

Next, we'll create an Android mobile application.

For our Android mobile application, we cannot put our large InterBase Server database, nor can we process the PHP files on the mobile device, so we'll have our application make some AJAX calls to another application hosted on a server that will provide the data. We will be using the same PHP Apache Server that we just started in step 12.

So the first thing we need to do is tell our main application to use AJAX.

14. Set UseAjax = true on the MPage1 component:

Hide image

15. Next, we'll establish the AJAX URI (UseAjaxUri) that we will connect to and collect the data.

NOTE: For the URI, do not use localhost, use your real IP address, such as the output from running ipconfig, for example:

IPv4 Address. . . . . . . . . . . : 10.1.1.72

For this demo, I'll use the http://192.168.74.131:3572/unit1.php that we have already been using in this example.

Hide image

16. Save ALL.

17. Run the app, and verify you connect to http://192.168.74.131:3571/unit1.php

18. IMPORTANT: Keep your PHP Web Server running (do not close the web window). The mobile apps we create will connect to this running PHP Web Server.

19. Next, we'll use the new "Wizard for PhoneGap" feature to create our Android mobile application.

    Part 5 - Deploy to Mobile

Do: Home >> Deploy to Mobile

Hide image
Click to see full-sized image

This is the “Wizard for PhoneGap”, a tool to help you generate all the files you need to create a native mobile application, based on web technologies. Here we have the option to create an iOS, Android, BlackBerry, Symbian, WebOS or Windows Phone mobile application. This wizard will generate all the files you need to create a native mobile application based on web technologies.

expand view>>

First, select the target device you want to build against.

1. We will select Android. Click Next.

Hide image

2. On the above "Application setup" screen:

The Index page is our unit.php from our current project.

The Application name, we will call MobileServer.

Company name; enter your Company name, for this example I'm using Embarcadero.

Click Next.

Hide image

You can adjust Android Graphics on this next screen, but for this example, we'll just keep the defaults.

Click Next.

If you get this Warnings screen, go back and set UseAjax = True and enter your UseAjaxUri.

Hide image
Click to see full-sized image

Else, you get this next screen:

expand view>>

Hide image

3. Select Debug to run on a simulator, or if you have your real Android device, you can USB connect to your computer and load the app directly to your real device by selecting Release.

a. Choose the destination folder for the generated app.

Click Next.

Hide image

From Home > Deploy to Mobile, you’ll see the results of the export:

Hide image

Hide image

Click Next.

Hide image

Here you have the option to build the app either using the SDK libraries or Phonegap:Build.

For this example, we will use SDK libraries.

Click Next.

Hide image

I have Android 4.1 API 16 emulator installed on my machine, so I’ll select Emulator.

NOTE: To expedite the launch and deploy, please have your emulator already running.

The HTML5 Builder Android set-up should have installed the SDK Manager here: C:\Program Files (x86)\Android\android-sdk

From the SDK Manager, Select Tools > Manage AVDs

Hide image
Click to see full-sized image

expand view>>

Select the Android41API16 emulator >> click Start.

Note: It can take several minutes for the Android Emulator to start…please be patient.

Hide image

Once your emulator is running, and you have OPENed the device,

Hide image

click Next from your HTML5 Builder “Run your app” screen.

NOTE: To open (unlock) the device, slide the Lock from Left to Right, to unlock your Android device. Note: Unlocking your emulator (varies by type of emulator you are using).

You should get:

Hide image

The Android adb server starts, SDK Tools launch, the Andorid project builds and your final Andorid .apk file gets created.

NOTE: If you do not have your Android Emulator running, you will get the message "Waiting for device", like you see in the screen below. If you do not have your emulator already running, the Android Setup should automatically start your Android Emulator, load and run the app.

Compile output:

Hide image

The app should get installed on your emulator, and it should aslo start running, like this:

Hide image

Select a Category and it will drill down to its sub-categories. Very cool!

Congratulations!

    Using Eclipse (Java) IDE and Android Developer’s ToolKit (ADT)

If you wish, the Java source code created by the Wizard for PhoneGap, and be imported into an Eclipse Java IDE.

Here are the steps:

Hide image
Hide image

Verify project shows you are using Android 4.1 and you do not have any errors:

    Hide image

FIRST, verify your PHP server is running. Run the app in HTML5 Builder and leave the web page running.

Hide image

Second, verify the IP address of the running PHP server, and verify you have the correct value in the UseAjaxUri from your MPage1.

Hide image

Remember, we are using Ajax to connect to the IP address of the PHP server, and that’s how we access the InterBase data remotely.

In the Eclipse (Java) IDE, right-click on project > Run as > Android Application.

Your MobleServer appp gets deployed to your Android emulator, like this:

    Hide image
Click to see full-sized image
expand view>>

Click on the app: Hide image

NOTE: If the app auto-runs at startup, then that’s OK. If you get a “connection error on your assets\www.index.html” file, then click OK. Find the app on the emulator, and click the app. That should resolve the initial “connection error”. Also look at the index.html and verify you have the correct IP and port for your running PHP server. Right-click on the index.html file > Open with > Text editor.

In two places, look for your IP and port:

<div data-role="page" id="MPage1" data-ajax-url="http://192.168.74.131:3572/unit1.php" >

<script>

jQuery(document).ready(function(){

     AjaxCall('http://192.168.74.131:3572/unit1.php');

You should get the first web page:

    Hide image

Select one of the categories and the app will make the Ajax call to your PHP server and access the InterBase data. Very cool!

    Congratulations!

  • spacer
  • Concluding Remarks

Embarcadero’s HTML5 Builder is the definitive software for Rapid Application Development with Web Technologies. HTML5 Builder and its library, the RAD PHP Component Library (RPCL), includes a lot of new features and improvements to make your life easier, and take the RAD methodology even further into the web development. HTML5 Builder features a brand-new interface that adapts itself to your workflow as you take care of the different aspects of your application: writing the logic, designing the Interface, managing databases, and so much more. For HTML5, the RPCL now supports HTML5, the new version of the web standard that is revolutionizing the way you write webpages! To learn more about HTML5 Builder, or to download a trial, visit http://www.embarcadero.com/products/HTML5-Builder


Appendix A - How to create the InterBase osCommerce database.

    Part 1 - InterBase Developer Edition Installation

Download your platform specific version of InterBase XE from: https://downloads.embarcadero.com/free/interbase

Select either the 32-bit or 64-bit Developer Edition depending on your specific OS platform:

InterBase XE 64-bit Developer Edition for Windows

InterBase XE 32-bit Developer Edition for Windows

Note: The download may email you the Serial Number to Register your product or use the serial number from your HTML5 Builder installation install.htm file located at C:\Program Files (x86)\Embarcadero\HTML5 Builder\5.0\Welcomepage\install.htm.

Run the ib_install.exe from the downloaded InterBase_XE_Win32.zip file.

Hide image

InterBase XE installs on port 3050 with an instance name of gds_db

Hide image

Assuming you don’t have any other versions or instances of InterBase running on your machine, select No for “Would you like to uniquely identify this instance?”.

Hide image

Select “Server and Client”, Documentation and Register

Hide image

Click Next

Hide image
Click to see full-sized image

Enter valid Serial Number and your DN login and password.

expand view>>

Click Register

Hide image

Click Finish.

  1. Verify the two InterBase Services are running (InterBase XE Guardian gds_db and InterBase XE Server gds_db).

Hide image

  1. Run the IBConsole (from C:\Embarcadero\InterBase\bin\IBConsole.exe)

Hide image

The IBConsole is an all-in-one database tool. It combines database administration, interactive SQL and communications testing capabilities in one easy to use application.

From this IBConsole, you can:

  • Manage local and remote servers.
  • Manage server security. Authorize new users, change user passwords, and remove users.
  • Manage server certificates (licenses).
  • Manage databases. Create new databases and set database properties.
  • Backup and restore databases
  • Perform database maintenance. Validate the integrity of a database, repair a corrupted database, sweep a database, and recover “limbo” transactions.
  • Shut down and restart a database
  • Execute SQL
  • View database metadata in DDL script format

IBConsole consists of:

  • A Main Window, the control center of IBConsole
  • An Object View Window, which displays detailed information and database objects
  • SQL Window, which can be used to execute SQL statements and scripts.
  • A Text Viewer, used to display metadata, logfiles, etc.
  • Several Visual Editors, to create and alter database object definitions
  1. Right-click on Local Server – gds_db >> Login (User Name = SYSDBA , Password = masterkey)

Hide image
Click to see full-sized image Hide image

expand view>>

  1. Right-click Databases >> Add >> C:\Embarcadero\InterBase\examples\database\employee.gdb

Hide image
Click to see full-sized image

Login Information (User Name = SYSDBA , Password = masterkey)

expand view>>

Hide image

Click OK.

Hide image
Click to see full-sized image

expand view>>

From the IBConsole, you have access to all your database objects (such as Domains, Tables, Indexes, Views, Stored Procedures, Triggers, External Functions, Generators, Exceptions, Blob Filters, Roles, and User Permissions.

For example, to see all the Stored Procedures for the Employee table, select Stored Procedures.

Right-click on DEPT_BUDGET gives you your options.

Double-click the DEPT_BUDGET procedure to see its properties.

Hide image
Click to see full-sized image

expand view>>

To see the data in a table, select Tables >> double-click Customer >> Data tab

Hide image
Click to see full-sized image

expand view>>

This was a quick overview on how to install InterBase and how to use the IBCosole.

InterBase also includes the Wise Installer to make it easy to embed InterBase with your application, to run the installer in silent mode.

Now that you are familiar with the IBConsole, we'll create the new osCommerce database, tables, indexes and populate the tables with data.

    Part 2 – Setting up InterBase Database

2.1 Create the database

1. First, we’ll create the osCommerce database. This is the file structure within which InterBase will subsequently store both the table structure, indexes etc. (this is called the Metadata), and the data itself (called the Data).

2. To create the database:

  1. Open IBConsole
  2. Click Database >> Create Database…

Hide image
Click to see full-sized image

expand view>>

  1. In File Name, Eenter the Database path name and database name:

C:\Embarcadero\InterBase\examples\database\osCommerce.gdb

Hide image
Hide image

  1. Enter user name (SYSDBA) and password (masterkey). Alias = IB_osCommerce. Check "Use Alias for DB connection". Keep all other default options.
  2. Click OK.
  3. Database Connect User Name (SYSDBA) and Password (masterkey).
  4. Click "Connect".
  5. Your new osCommerce database gets created.

Hide image

2.2 Create the tables

1. Next, we’ll create the table structures. This can be achieved in several ways. The easiest way is to take a simple text file, fill in the table structure, and "import" the structure to InterBase using IBConsole. For this database we’ll use this Create_osCommerce_Tables.sql file.

/* Create_osCommerce_Tables.sql */

/* Create the InterBase tables for the OSCOMMERCE database*/

/* Table: CATEGORIES, Owner: SYSDBA */

CREATE TABLE CATEGORIES ( CATEGORIES_ID INTEGER NOT NULL, PARENT_ID INTEGER NOT NULL );

/* Table: CATEGORIES, Owner: SYSDBA */

CREATE TABLE CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID INTEGER NOT NULL, LANGUAGE_ID INTEGER NOT NULL, CATEGORIES_NAME VARCHAR(32) NOT NULL );

COMMIT;

2. Assure you are logged into your osCommerce database, and it's selected on IBConsole.

We’ll use Tools >> Interactive SQL from the menu bar of the Main Window of the IBConsole to create a new SQL Window.

Hide image
Click to see full-sized image

If the currently selected branch in the connections tree is a connected database or one of its descendants, then the new SQL Window will have a connection established to that database. If you have an external SQL file to load, then use would use Query >> Load Script. For this example, we'll copy and paste the above Create_osCommerce_Tables.sql code into the SQL Window like this:

expand view>>

Hide image
Click to see full-sized image

3. Query >> Execute (or F5 key)

expand view>>

4. Select Tables from your osCommerce database should show you your created databases.

(Note: If you do not see your tables, try disconnect and reconnect to the osCommerce database; right-click Disconnect and Connect).

Hide image

2.3 Create the indexes

Next we’ll create some Indexes. Indexes allow InterBase to locate data (dramatically) more quickly. An index has two key components to it - the field(s) that you will want to search on and whether the field(s) are unique (e.g. a Reference number will probably need to be unique, but you may well need to accommodate several people sharing a birth date or a last name).

2.3.1 Primary key index

Next, we’ll create the tables Primary key indexes. The Primary key index is usually needed on the Field(s) to uniquely identify a record within a table (e.g. the unique reference number given to each record, or a Social Security ID, or a post code and House number/name combination within an Address table). For example the CATEGORIES table has PRIMARY KEY (CATEGORIES_ID) ;

For the indexes we'll use this Create_osCommerce_Indexes.sql file:

/* Create_OSCOMMERCE_Indexes.sql */

/* Create the Indexes for InterBase tables for the OSCOMMERCE database*/

CREATE UNIQUE INDEX CATEGORIES_ID ON CATEGORIES (CATEGORIES_ID) ;

CREATE INDEX LANGUAGE_ID ON CATEGORIES_DESCRIPTIONS (LANGUAGE_ID) ;

COMMIT;

  1. Tools >> Interactive SQL
  2. Copy and Paste the above "Create_osCommerce_Indexes.sql" into the SQL Window.
  3. Query >> Execute (or F5 key)
  4. Select Indexes from osCommerce, and verify your Indexes were created:

Hide image

2.4 Populate the tables

For far we created the database, tables and indexes. Next we want to fill up the database with test data. This can be achieved through the "old-fashioned" technique of manually entering data into the database (such as through a Client application which allows data entry).

A more robust technique is to create a series of SQL commands that insert data to the table within a simple text file, and import the SQL file to InterBase.

The advantages of this approach include the ability (a) to copy, paste and update lines to achieve a methodical selection of all types of data more easily, (b) to re-enter the data whenever you choose to clear all the data from the database and start again and (c) to reuse relevant test data within new database applications in future.

For the data we'll use this Insert_osCommerce_data.sql file:

/* Insert_osCommerce_data.sql */

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (1,0);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (2,0);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (3,0);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (5,1);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (6,1);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (7,1);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (8,1);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (16,1);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (17,1);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (18,2);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (19,2);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (20,2);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (10,3);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (11,3);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (12,3);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (13,3);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (14,3);

INSERT INTO CATEGORIES (CATEGORIES_ID, PARENT_ID) VALUES (15,3);

COMMIT;

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (1,1,'Hardware');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (2,1,'Software');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (3,1,'DVD Movies');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (4,1,'Graphics Cards');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (5,1,'Printers');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (6,1,'Monitors');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (7,1,'Speakers');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (8,1,'Keyboards');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (9,1,'Mice');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (10,1,'Action');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (11,1,'Science Fiction');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (12,1,'Comedy');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (13,1,'Cartoons');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (14,1,'Thriller');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (15,1,'Drama');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (16,1,'Memory');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (17,1,'CDROM Drives');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (18,1,'Simulation');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (19,1,'Action');

INSERT INTO CATEGORIES_DESCRIPTIONS ( CATEGORIES_ID, LANGUAGE_ID, CATEGORIES_NAME ) VALUES (20,1,'Strategy');

COMMIT;

To populate the database using this method:

  1. Tools >> Interactive SQL
  2. Copy and Paste the "Insert_osCommerce_data.sql" file

Hide image
Click to see full-sized image

expand view>>

  1. Query >> Execute (or F5 key)
  2. Select Tables >> double-click CATEGORIES >> Data tab from osCommerce, and verify your data was inserted:

Hide image

Hide image
Click to see full-sized image Hide image
Click to see full-sized image

5. Close out the Data tab.

expand view>>

expand view>>

6. Console | Exit out of theIBConsole.

Congratulations! Your InterBase osCommerce database has been created and populated with data!

Server Response from: ETNASC01