SODA is an interface used for Oracle Database Lite C++ development that provides object-oriented data access using method calls, relational access using SQL and object-relational mapping to bridge the gap between the two. Object functionality is roughly three times faster than ODBC for simple operations. It enables rich datatypes—such as arrays and object pointers—as well as standard SQL columns. A programmer can store any data structure in the database and not think about relational design or performing joins.
A C++ developer can also use an interface for executing SQL statements. The resulting code is shorter and cleaner than ODBC. SQL queries can return objects to be examined and modified directly through the object-oriented layer, without calling any additional SQL statements.
Object-relational mapping enables the application to access relational data as if it was object hierarchy. Thus, your application can replicate rich data types or object pointers to the Oracle database server.
Oracle Database Lite includes SODA Forms, which is a library that simplifies the development of GUI applications for PocketPC devices. See Section 12.6.2, "Develop Your GUI Using the SODA Forms Library" for more details.
The SODA API method calls are documented in the SODA: Simple Object Data Access API Reference, which is located off the <ORACLE_HOME>
/Mobile/index.htm
page. The full sample code that is demonstrated in this chapter is located in the <ORACLE_HOME>
/Mobile/doc/soda/sodadoc/html/sodasimple_8cpp-source.html
file.
Section 12.2, "Using SQL Queries in SODA Code for PocketPC Platforms"
Section 12.3, "Virtual Columns and Object-Relational Mapping"
Section 12.4, "Behavior of Reference-Counted and Copy-By-Assignment Objects"
In order to get started with SODA quickly, the following sections discuss the most frequently used classes:
When developing your C++ application, you would use the following classes the most:
DBSession
connects to the database and find and create classes.
DBClass
creates new database objects.
DBObject
modifies existing objects.
DBData
wraps an attribute value and is used for type conversion.
DBQueryExpr
builds single-table queries supported by SODA.
DBString (olString)
is a C string wrapper used by SODA.
DBList (olList)
is a template to store lists of values.
DBColList
and DBDataList
instantiate these objects.
For example, implement the DBObject
method, as follows:
DBObject obj; ... obj["NAME"] = "Jack"
For full documentation for the SODA API method calls, see the SODA APIs, which you can find off the <ORACLE_HOME>
/Mobile/index.htm
page.
The following example demonstrates most of the SODA object-oriented functionality, as discussed in Section 12.1.1, "Overview of the SODA Classes".
void helloSODA() { puts("Hello SODA"); try { DBSession sess("POLITE"); // Connect to the DSN, creating it if necessary // Find or create our class DBClass cls; try { cls = sess["PEOPLE"]; } catch(DBException e) { cls = sess.createClass("PEOPLE", DBAttrList() << DBAttr("ID", DB_INT) << DBAttr("NAME", DB_STRING)); } // Create several objects. We can identify columns by name or positions DBObject o = cls.create("ID", 10, "NAME", "Alice"); // The previous syntax of col1, val1, col2, val2 ... works for up to // 32 columns. This version works for any number of columns cls.create(DBSetList() << "ID" << 10 << "NAME" << "Alice"); cls.create(0, 20, 1, "Bob"); // Note the automatic type conversion cls.create("ID", "314", 1, 3.14159265358); // Execute a query (will return two objects) DBCursor c = cls.createCursor(DBColumn("ID") == 10 || DBColumn("NAME") == "Bob"); DBObject ob; while (ob = ++c) { DBString s = ob["NAME"]; puts(s); o["ID"] = (int)o["ID"]+1; } // Delete an object o.remove(); // Clean up so that create class is successful next time sess.rollback(); } catch(DBException e) { DBString s = e.getMessage(); printf("Error: %s\n", (const char *)s); } }
To add SQL queries to your SODA code for PocketPC platforms, do the following:
Include sodasql.h
, instead of soda.h
.
Link your program with sodasql.lib
.
Install sodasql.dll
at runtime.
Create a DBSqlSession
object instead of the DBSession
object. Execute relational queries and other SQL statements with the help of DBSqlStmt
and DBSqlCursor
classes. Query results can be returned either as column values or as DBObjects
for matching rows.
The following is a sample that uses the SODA relational interface:
void helloSQL() { try { puts("Hello SQL"); DBSqlSession sess("POLITE"); sess.execute("create table odtest(c1 int, c2 varchar(80))"); DBSqlStmt stmt = sess.prepare("insert into odtest values(?,?)"); // The values stand in for two ?'s in the statement above stmt.execute(5, "John"); stmt.execute(10, "Mike"); stmt.execute(15, "Alice"); // Execute a single-table query that will return DBObject's for matching // rows. Use a standard SODA interface to access the objects DBSqlCursor c = sess.execute("odtest", "c1 < 15"); while (++c) { DBObject o = c.getObject(); DBString s = o["c2"]; int val = o["c1"]; printf("%d %s\n", val, (const char *)s); o["c1"] = val+1; // Can modify objects in addition to just reading them } // Execute a usual relational query c = sess.execute("select * from odtest"); while (++c) { DBString s = c["c2"]; printf("%d %s\n", (int)c["c1"], (const char *)s); } } catch(DBException e) { DBString s = e.getMessage(); printf("Error: %s\n", (const char *)s); } }
A programmer does not view the data as column values that are stored in the database. For example, a master-detail relationship might be expressed as matching values in two tables, but for a program it is more natural to access a column in the master object, which contains an array of pointers to details.
The DBVirtualCol
class enables the translation between the conceptual view of the data and the actual data in the tables. You can create a column that is completely under programmer's control through the get
, set
and remove
methods and adding it to a class at runtime.
In fact, SODA contains a specialized DBValueRel
class that extends the DBVirtualCol
class to map master-detail relationships to object pointers. The following sample builds a binary search tree in the database using object-relational mapping:
struct HelloVirtual { int lpos, rpos, vpos; void visit(DBObject o); HelloVirtual(); }; void HelloVirtual :: visit(DBObject o) { while (o) { visit(o[lpos]); printf("%d\n", (int)o[vpos]); o = o[rpos]; } } HelloVirtual :: HelloVirtual() { try { DBSession sess("POLITE"); // TreeNode represent a binary tree with left and right pointers // that point back to parent's id column DBClass cls = sess.createClass("TreeNode", DBAttrList() << DBAttr("val", DB_INT) << DBAttr("id", DB_INT) << DBAttr("lchild", DB_INT) << DBAttr("rchild", DB_INT)); // Create an index on id to speed up search cls.createIndex("i1", DBColList() << "id", true); // Set a sequence as a default value of id, so that we don't have to set it // explicitely DBSequence seq = sess.createSequence("s1"); cls.defaultVal("id", seq); // Create the virtual columns DBValueRel lref("left", DBSrcCol(cls, "lchild") -> DBDstCol(cls, "id"), DB_UPD_DETAIL); DBValueRel rref("right", DBSrcCol(cls, "rchild") -> DBDstCol(cls, "id"), DB_UPD_DETAIL); // Cache column positions for frequent access lpos = cls["left"], rpos = cls["right"], vpos = cls["val"]; // Root of the binary tree DBObject root; // Insert some random numbers into our binary search tree for (int i = 0; i < 50; i++) { int v = rand(); DBObject o = cls.create(vpos, v); // Note automatically generated ids DBObject par; int dir; for(DBObject cur = root; cur; par = cur, cur = cur[dir]) dir = (int)cur[vpos] >= v ? lpos : rpos; if (par) par[dir] = o; else root = o; } // Do in-order traversal of the tree, printing out numbers in sorted order visit(root); } catch(DBException e) { DBString s = e.getMessage(); puts(s); } }
Most C++ classes in SODA are reference-counted, which means that assigning one variable of one type to another cannot copy an object, but creates another way to refer to the same object. For example, the DBData
class represents values that can be stored in persistent objects.
The following example demonstrates reference-counting:
DBData d = 5; // Create a new object containing value 5 // and make a reference to it DBData d2 = d; // Both reference the same object d << 20; // Add another value to existing object. // Both d and d2 reference the new array d2 = 10; // d references the array, d2 is reassigned to the new data. d.clear(); // Clear the last reference to the array of 5 and 20 //and free the array
The programmer does not need to free objects when they are no longer used. However, this method is relatively expensive and not practical for objects that are created and destroyed often, such as when new lists of values, such as DBSetList
, are allocated for each SODA call. Various lists in SODA, such as DBSetList
, DBDataList
and so on, are copy-on-assignment rather than reference-counted objects.
The following example demonstrates copy-by-assignment:
DBSetList ls << "cost" << 1000; DBSetList ls2 = ls; // Created a copy of ls ls << "value" << "priceless" // Only ls is changed ls2.clear(); // Just set this copy to size 0
Note:
SODA includes non-database classes and templates for your convenience, which have names that start withol
rather than DB
—such as olHash
. Avoid making unnecessary copies of these classes.You can optimize your implementation by not creating an unnecessary copy by passing a reference or a const
reference to the object, rather than an object, for both reference-counted and copy-on-assignment classes when calling a function. For example, void func(const DBData &v)
avoids creating an unnecessary copy.
Note:
Theclear
method only nullifies a particular reference to reference-counted objects. DBSession
and DBCursor
classes provide a close
method that releases the underlying database resources, even while the objects are still referenced. Using anything that relies on a closed cursor or database connection throws a DBException
.Many embedded compilers, such as Visual C++ for PocketPC, do not support C++ exceptions. Oracle Database Lite includes ALE, which is a library that closely mimics C++ exceptions. The following sections describe how to use ALE:
A decorated class is one that uses ALE for handling its exceptions. If your embedded compiler does not support exception handling, then use ALE, which relies on careful accounting of all objects that are already constructed or are being constructed. ALE supports stack unwinding and catching exceptions based on object type.
To use ALE, C++ source code needs to be modified where the try, catch and throw blocks are replaced with the ALE macros, which is known as decorating classes. The following is an example of a decorated class:
#include "ale.h" struct Error { const char *msg; Error(const char *msg) :msg(msg) {} }; aleTry { olArray<char> a(5); aleThrow(Error,Error("Adios"));// Or aleThrowObj(Error,("Adios")); } aleCatch(Error,e) { puts(e.msg); } aleCatch(aleBadAlloc,e) { puts("Tough!"); } aleCatchAll { puts("Some other exception happened\n"); aleReThrow; } aleEnd;
Table 12-1 ALE Macros for C++ Exceptions
Macro | Action |
---|---|
|
Equivalent to C++ try. Use to enclose the code that might encounter any exception. |
|
Catch exception of a given type and store it in the variable |
|
Throw an exception contained in |
|
Construct a new object of |
|
Catch any exceptions that are not handled explicitly. |
|
Rethrow the exception that is caught in the innermost |
|
Close the exception handling construct. Add a semi-colon |
If your embedded compiler does not support exception handling, then use ALE, which relies on careful accounting of all objects that are already constructed or are being constructed. Your class is involved in exception handling if the class is on the stack when an exception is thrown, its constructor may throw an exception, or it has a decorated superclass and member—even if the class does not do any additional exception-related processing.
When you decorate one class with ALE, you must decorate all classes involved with this class. If you omit a decoration in one of the classes, then the program might fail. Therefore, it is best to decorate all your classes except for plain C-style structures that do not have any constructors or destructors.
If your class does not have any constructors with a body that throws exceptions (it is OK if the superclass or member constructor does), then you can use a simple form of class decoration by adding ALELAST(ClassName)
, without a semicolumn, at the end of class declaration. ALELAST
informs the library to register the class and clean-up if an error occurs. The following demonstrates how to use ALELAST
:
template<class T> class PtrHolder { T *ptr; public: ~PtrHolder() { delete ptr; } operator T *() { return ptr; } ALELAST(PtrHolder) };
If any of the constructors throw exceptions, then you need to do the following:
Add ALECLAST(ClassName)
, rather than ALELAST
to the end of the class body.
Add ALECONS(ClassName)
in the beginning of the body of each constructor.
This is demonstrated, as follows:
template<class T> class Array { T *a; size_t len; public: Array(size_t len=0} : len(len) { ALECONS(Array); a = new T[len]; } Array(const Array &arr) : len(arr.len) { ALECONS(Array); for (size_t i = 0; i < len; i++) a[i] = arr.a[i]; } ... ALECLAST(Array) };
In this example, the class contains an explicit copy constructor. If your class does not contain an explicit copy constructor and instances can be copied, then you need to explicitly write a copy constructor and add ALECONS(ClassName);
rather than using a copy constructor that is generated by the compiler. If you need a more complicated initialization than a default or copy constructor, then use a global pointer—which can be initialized by another global object, rather than a global instance.
Decorated classes can be safely used with the new
and delete
functions, including using new
and delete
for array
and placement new
. However, systems that do not support exceptions usually do not declare std::bad_alloc
and std::no_throw
types. Use aleBadAlloc
and aleNoThrow
instead of using std::bad_alloc
and std::no_throw
types.
One design decision is whether to decorate classes with constructors that only throw bad::alloc
if they run out of memory using ALELAST
or ALECLAST
. If you are writing classes for a single application that does not allocate much memory, you might dispense with error checking and just use ALELAST
. Your program might crash because of incorrect cleanup calls if it runs out of memory. If your class allocates a lot of memory or you are writing a highly-reusable framework, then it is best to use ALECLAST
and decorate all the constructors.
If you need a global or static variable to be decorated with ALE, declare it using aleGlobal
template, as follows:
aleGlobal<MyType> myGlobal; // Initialized with default constructor aleGlobal<MyType> myGlobal1(MyType("Hello", 5")); // Initialized with copy constructor. ... MyType *t = myGlobal; // Declared variables behave as pointers
Declare all global or static decorated instances using aleGlobal
or you may receive runtime errors.
Unlike regular C++ exception handling, ALE requires that class names in aleThrow
and aleCatch
match exactly. Typedef names, throwing a subclass, and catching a superclass will not work. To build a hierarchy of exceptions, add ALEPARENT
declaration to the subclass, as follows:
class BaseE { ALELAST(BaseE) }; class DerivedE : public BaseE { ALELAST(DerivedE) ALEPARENT(BaseE) };
DerivedE
can be caught as BaseE
. If you use multiple inheritance, then the first base class must be declared as a parent.
The Microsoft Embedded Visual C++ for ARM has a bug that is triggered when an ALE-decorated object (or any object with embedded pointers) is passed by value to a function or method. The affected code receives an ALE fatal error message at runtime. To avoid this problem, always pass SODA objects and instances of other classes that use ALE as a constant reference rather than value. For example, modify the following code:
void createName(DBClass cls, DBString name) { cls.create("name", name); }
to the following implementation:
void createName(const DBClass &cls, const DBString &name) { cls.create("name", name);
If your classes are not decorated properly, then you will receive runtime errors. On PocketPC, ALE displays a message box explaining the problem, and then the program terminates. In addition, the error and the dump of the ALE stack is appended to aleDump.txt
, which exists in the root directory of the device. In simple cases, the error message pinpoints the exact problem; for example ALECONS is missing for class MyArray
. Usually, one of the classes found near the top of the ALE stack is not decorated properly. If you do not decorate a class and its superclasses or members are decorated, then you may receive a runtime error and see the superclasses/members on the stack.
To build a program that uses ALE, include ale.h
from the Oracle Database Lite SDK and link with the olStdDll.lib
library. You need olStdDll.dll
at runtime.
For systems that already support C++ exceptions, like Win32, Oracle Lite includes a dummy ale.h
that defines the same macros, but uses regular C++ exceptions to implement them. If you are writing code that must execute on both Win32 and PocketPC, remember to test the code with the actual ALE library to ensure that all your classes are decorated correctly. ALELAST
, ALECLAST
or ALECONS
have no effect on the Win32 platform.
The following sections describe how to create a SODA Forms Application for PocketPC platforms:
Section 12.6.2, "Develop Your GUI Using the SODA Forms Library"
Section 12.6.5, "Binding UI to Data in the PocketPC Environment"
Section 12.6.6, "Setting List Choices for Status Contol on PocketPC"
SODA Forms relies on SODA, which is an easy-to-use C++ interface for the Oracle Lite database engine. Read SODA documentation before continuing with this manual. Make sure you understand objects, queries, cursors and virtual columns as a way to customize database schema for a particular application.
When developing for the PocketPC environment, refer to the Microsoft Development Network. Focus on how to use Embedded Visual C++ to create resources and compile programs for PocketPC. Also, familiarize yourself with Windows UI controls, including their formats and styles.
If you want to use SQL in your application, understand the SODA SQL support. The FormsOrder
demo uses SODA SQL to support custom queries.
Find and open FormOrders.vcp
in SodamFormCE\FormOrders
directory under the samples for SODA on the PocketPC. This is the demo for PPC2003.
SODA Forms is a quick way to create data entry GUI.
The GUI for data entry applications performs the same actions, as follows:
Copy values from different columns of a database record into controls, such as text fields and checkboxes.
Track what the user is doing by handling various UI events, like "field entered" or "button clicked". The user typically examines or edits the data on the screen and presses a button or picks a menu item to designate the next action.
To perform the action requested by the user, the application performs database and UI calls. For example, to save changes, the application retrieves the values in the UI controls and eventually executes a SQL update statement. An application needs to be able to discard changes, delete records, create new records, go to the previous or the next entry, and search for data that satisfies user-selected conditions.
Normally, you duplicate the UI code for each screen supported by the program, which can result in a bulky program. Instead, use Soda Forms, as described in Section 12.6.2.2, "Trimming Your PocketPC UI Code With SODA Forms", to streamline your UI code.
We moved the boilerplate UI code to a library and to enable the programmer to concentrate on application logic, not the tasks of copying values from database rows to UI controls. Oracle Database Lite provides this library for PocketPC.
The following example creates a form:
DBSession sess("OrdersODB"); // Open a database connection DBClass cls = sess["ORD_MASTER"]; // Locate a table in the database DBForm frm(OrderForm, cls, orderCols, OL_COUNTOF(orderCols)); // Initialize a form frm.edit(cls.createCursor()); // Let the user edit all records in ORD_MASTER table
When designing an application for PocketPC, the form is created with Embedded Visual C++ resource editor with values from the first row of ORD_MASTER
table1, as follows:
The user can update the current record and then either save the changes or discard them and reload from the database. He or she can also search data using any values visible on the screen, scroll through all the records as well as delete existing entries or create new ones. No special code is needed to support these operations.
On PocketPC, you can also see a lens button on the toolbar. When this button is clicked, the application can launch a cusomtized dialog for entering search conditions, retrieve their values, an dexecute custom queries. For PocketPC, we only support queries implemented by the application.
For the PocketPC platform, a SODA form is a Windows dialog. Thus, the first step is to design dialogs for every form using the Embedded Visual C++ resource editor. In the FormOrders
project, click on the resource tab, open the dialog folder and click on the IDD_FORMORDERS_MASTER
dialog. This shows the dialog for the master form, as follows:
The controls on the master form, labels (also called static controls, such as "Date:", "Status:", and so on), edit controls (name and description fields), Date-Time picker (for date field), ComboBox (for status field), and list view control for the detail table. There are corresponding icons on the toolbox used to create these controls.
Each control has separate formats and styles that can be customized through the resource editor to find the appearance and behavior you want. For every control there is a resource symbol with numeric id assigned to it. Right-click on the FormOrders.rc
and select Resource Symbols
, as follows:
You can either create your own identifiers or let the resource editor create them for each symbol. Look at the header file, resource.h
, which is generated by the resource editor, to see how identifiers are assigned to resource symbols using #define
statements.
The following SODA code connects the application to an Oracle Lite database and retrieves the DBClass
objects for two tables used in the demo—ORD_MASTER
and ORD_DETAIL
:
DBSession sess("OrdersODB"); // Open a database connection DBClass mCls = sess["ORD_MASTER"]; DBClass dCls = sess["ORD_DETAIL"];
However, values stored in the database do not exactly match what should be shown to the user. SODA allows each application to customize how the schema is shown. Both the ORD_MASTER
and ORD_DETAIL
tables have ID columns, which are primary keys and should be initialized to some unique value when a new record is created. To avoid asking the user to pick unique values, set the default value of each column to a sequence, as follows:
mCls.defaultVal("ID", sess.findSequence("OrderSeq")); dCls.defaultVal("ID", sess.findSequence("DetailSeq"));
In addition, the UI shows the order status as "Open", "Closed" or "Pending"; however, the "STATUS" column in ORD_MASTER
stores numbers, such as 0, 1 or 2. The following line creates a virtual column named strStatus
that contains a mapped value:
DBMapCol mc("strStatus", mCls, "STATUS", DBDataList() << 0 << "Open" << 1 << "Closed" << 2 << "Pending" << DBNULL << DBNULL);
Finally, modify the Order screen to contain a table of items included in the order. SODA Forms populates a table by reading a column that stores a list of DBObject
pointers and copying values from each object to a table row. In our case, ORD_DETAIL
rows relate to a row in ORD_MASTER
through the values of the KEY
column, which matches the value of the master ID. The following declaration creates a pseudo-column that contains an array of pointers and updates KEY
values as assigned:
DBValueRel rDet("detail", DBSrcCol(mCls, "ID") -> DBDstCol(dCls, "KEY"), DB_UPD_CASCADE);
If a mapping you are looking for is not part of SODA, then you can create your own by subclassing the DBVirtualCol
class. Default values and virtual columns are transient and not stored in the database. Thus, declare them in every application that requires access to your abstractions.
Once you complete customizing the database schema, you can map columns in the database to values that appear on the user screen when editing a particular record. You can map the columns by binding UI to data in the PocketPC environment. For each column, you need to define a DBFormCols
structure that has the following fields.
The resource id assigned to a particular UI control.
The column name to which the UI control will be bound.
The optional argument that specifies how the control should be edited. For PocketPC, the only relevant value is DBFormEditListIndex
, which asks to use the index of the selection, rather than its string value, for ListBox
and ComboBox
controls. If omitted, the default method is used.
Here is the mapping for our two forms:
//Mapping for ORD_MASTER table static const DBFormCols orderCols[] = { { IDC_FORMORDERS_DATE, "DDATE", DBFormEditDate}, { IDC_FORMORDERS_NAME, "NAME"}, { IDC_FORMORDERS_DESC, "DESCRIPTION"}, { IDC_FORMORDERS_STATUS, "strStatus"}, { IDC_FORMORDERS_DETAIL, "detail"} }; //Mapping for ORD_DETAIL table static const DBFormCols detailCols[] = { { IDC_FORMDETAIL_DATE, "DDATE", DBFormEditDate }, { IDC_FORMDETAIL_ITEMS, "DESCRIPTION" }, { IDC_FORMDETAIL_ORDERED, "QTYORDERED", DBFormEditDigits }, { IDC_FORMDETAIL_SHIPPED, "QTYSHIPPED", DBFormEditDigits }, { IDC_FORMDETAIL_RECEIVED, "QTYRECEIVED", DBFormEditDigits }, { IDC_FORMDETAIL_COST, "COST", DBFormEditDigits } };
Once the mapping is in place, create the DBForm
objects by specifying the resource ID of the form itself, the DBClass
of objects that the form will be used to edit and the mapping table with the number of elements it contains, as follows:
DBForm mFrm(OrderForm, mCls, orderCols, OL_COUNTOF(orderCols)); DBForm dFrm(DetailForm, dCls, detailCols, OL_COUNTOF(detailCols));
For listbox and ComboBox controls in the PocketPC environment, set the string list of choices. You cannot set the list choices for the listbox in the resource editor, and it can be difficult to set for the combobox. Thus, set the list choices for both the listbox and the combobox programmatically through the setListItems
function of DBFormCol
, as follows:
DBList<DBString> stList; stList << "Open" << "Closed" << "Pending"; mFrm[IDC_FORMORDERS_STATUS].setListItems(stList);
Specify which columns of the ORD_DETAIL
table appear in the table and how the values are aligned within a cell and in the title of each column. The following array contains the information:
static const DBFormTblCols detailTblCols[] = { { "DESCRIPTION", "Items" }, { "QTYORDERED", "Ordered", DBFormColRight }, { "COST", "Cost", DBFormColRight } };
Retrieve a handle to the table column and set its format, as follows:
DBFormCol tCol = mFrm[OrderDetailTable]; tCol.setTableInfo(dCls, detailTblCols, OL_COUNTOF(detailTblCols));
The following few lines of code constitute the logic of FormOrders
application:
// Load all objects of ORD_MASTER into a form DBCursor c = mCls.createCursor(); mFrm.load(c); // Handle table select event in master form to launch the detail form while (mFrm.edit() == OrderDetailTable) dFrm.edit(mFrm[OrderDetailTable]);
From this, the following occurs:
A list of objects from ORD_MASTER
to edit is loaded into the order form.
The mFrm.edit
function displays the UI and handles many of the user actions internally. Before the call returns, the user could have made changes, created new records, searched through the data, and so on.
The return values from the function call are an identifier of the UI control that was activated, which was not handled internally. In the case of the Orders demo, it would either be the OrderDetailTable
—meaning that a row in that table was clicked—or the DBFormItemExit
—if the exit icon on the toolbar was clicked.
If the user clicked the table, the demo asks the DBForm
detail to edit the list of objects contained in the table.
Once the edit is completed, any pointers to new objects are saved in the detail
virtual column of the ORD_MASTER
row. The DBMapCol
updates the KEY
column of the objects to the identifier value that matches the master.
You can use SODA Forms to write concise UI code without replicating a boilerplate. The next sections explore more advanced capabilities of the library.
SODA can be compiled on Windows and PocketPC environments. To build a SODA application on Win32, perform the following:
Add the Oracle Database Lite SDK to the include and library path in your compiler options.
Include the appropriate .h
files and link with appropriate library files, as follows:
For most applications, include <soda.h>
and link with olStdDll.lib
and sodadll.lib
.
If you are using Visual Studio.Net and you are building an application using SODA SQL binding, include the <sodasql.h>
file instead of soda.h
and link with olStdDll.lib
, sodadll.lib
, and sodasql.lib
.
If you are using Visual C++ 6.0 and building and application using SODA SQL binding, then link with olStdDll6.lib
, sodadll6.lib
, and sodasql6.lib
libraries. The olStdDll
library contains utility classes that are not related to database, such as olString
and olHash
. It does not depend on the rest of Oracle Database Lite runtime, except ceansi.dll
, on PocketPC platforms.
To build a SODA Forms application for PocketPC platform, include SodaForm.h
into your program and link with the following import libraries: sodadll.lib
(soda library), sodasql.lib
(library for soda sql) and sodaform.lib
(sodaform runtime). Also, install the Oracle Database Lite runtime and sodadll.cab
in order to run your application. Sodadll.cab
contains sodadll.dll
, sodasql.dll
, sodaform.dll
and SodaFormHelp.html
(default help file for SODA Forms). Currently, we support SODA Forms for two Pocket PC platforms: Pocket PC 2002 and Pocket PC 2003. The soda libraries and sodadll.cab
files are provided in the SDK for each platform—both for the device and the emulator.
SODA includes a software emulation library that requires some changes in syntax when using C++ exceptions, but keeps the program structure intact. See Section 12.5, "Another Library for Exceptions (ALE)" on how to support C++ exceptions.
The following sections describe the SODA Forms edit modes:
The code below enables a user to edit a specific object. To edit a list of objects, see Section 12.7.2, "Editing a List of Objects":
DBObject o; DBFormItem id = frm.edit(o);
In this case, toolbar will not have arrows to scroll or "new" icon to create a new object. Query is disabled. If the user saves the changes or deletes the object, DBFormItemSave
and DBFormItemDelete
are returned respectively. With list edit, save or delete do not return, since the user can still make additional changes to another record.
The following example loads a list of objects into a form, and enables the user to insert, delete, update or query:
olList<DBObject> ls; ls << obj1 << obj2 << obj3; DBFormItem id = frm.edit(ls);
When the edit returns, the DBObject
list is updated with the new list of objects that reflects creation and deletion. The DBFormItem
variable has one of the following values:
Table 12-2 DBFormItem Identifier Values
Value | Explanation |
---|---|
DBFormItemExit |
On PocketPC, the user clicked an OK button on the navigation bar |
Identifier of a table column |
User clicked on a row in a table. Use the frm[id].getSelectedRowfunction to determine which one it is. |
Identifier of a button |
User clicked on a button in the form. |
Identifier of a menu item |
User clicked on a menu item not handled internally by SODA forms. |
Identifier of a pop-up list or checkbox |
The UI control was changed and you called frm[resId].setChangeNotify(true) on that column. |
DBFormItemRevert |
You set DBFormRevertExit edit flag and the user reverted a change. |
There are several ways to customize the editing. The following example demonstrates a customization:
DBCursor cur = cls.createCursor(DBColumn("status") == "Active"); frm.load(cur, DBFormNew|DBFormUpdate|DBFormDirtyExit, DBSetList() << "zip" << 94403 << "status" << "Active"); DBFormItem id; while ((id = frm.edit()) != DBFormItemExit) if (id == CustomerBelmontButton) { frm[CustomerZipField] = 94002; frm.dirty(); } olList<DBObject> ls; frm.getList(ls);
The DBFormNew
or DBFormUpdate
edit modes are specified instead of the default DBFormListEdit
mode, which disallows the deletion of records while creating and updates are OK. DBFormReadOnly
enables users to view and search the data.
The DBFormDirtyExit
flag means that a button press or menu selection would exit edit even if the form is dirty. The default is to beep and wait until the user saves or reverts the change.
Specifying a DBSetList
provides initial values that are given when a new object is created. For bound, enabled columns, the user can change that value. For unbound database columns or columns bound to read-only UI controls, this is the final value.
You can specify objects to edit in two ways – by giving an explicit list of objects or by providing a DBCursor
. In the later case, the results of a query are loaded into the form.
In this case, load the list of objects once, call edit one or more times, and then retrieve the final edited list. SODA Forms provides you a choice between calling the edit method with all the arguments or calling load, edit without arguments and then getList
or getObject
functions. Examples in this document only show one possibility and do not discuss each edit flag.
The example below loads initial values into a form and asks the user to modify the values. The user then either clicks Save to create a new record or clicks Delete to cancel the creation.
DBObject o = frm.create(DBSetList() << "zip" << 94403 << "status" << "Active");
This call returns the new object or DBNULL
, if the creation was canceled. However, if you use the load/edit sequence, then call the getObject
method to retrieve the object handle, if DBFormItemSave
is returned by the edit
method.
Although SODA Forms is designed for editing database records, you can use the same interface to retrieve input from the user. The following example enables the user to edit two fields and then retrieve the result:
DBForm frm(CustomerForm); frm[CustomerZipField] = 94002; frm[CustomerStatusField] = "Active"; DBFormItem id = frm.dialog(); if (id == DBFormItemSave) { DBString status = frm[CustomerStatusField]; int zip = frm[CustomerZipField]; ... }
The values set before the frm.dialog
call are default values. The user can make changes and then click Revert to restore the defaults and try again. Finally, the user clicks Save to send the changes to the program.
If you want to search on the list of objects loaded into a form, you can perform a custom query, which is the query logic implemented by the application. Execute the FormOrders
demo and click on the lens toolbar button on the main form. It pops up a form—a dialog—to enter the search parameters for a custom query. Examine the IDD_FORMORDERS_QUERY
dialog under the FormOrders
resources to see how it enters the following search criteria: from- and to- dates, multiple list choices for the order status, and keyword search on the company name.
The following code sets up the query form:
//orders query form //this constructor will automatically put the form into a dialog mode DBForm qFrm(IDD_FORMORDERS_QUERY); //set list choices qFrm[IDC_FORMQUERY_STATUS].setListItems(stList); //same list as for the master form qFrm[IDC_FORMQUERY_STATUS].setEditType(DBFormEditListIndex); //cannot use virtual column here
In order to enable the lens toolbar button, specify DBFormCustomQuery
mode when loading the master form, as follows:
// Load all objects of ORD_MASTER into a form DBCursor c = mCls.createCursor(); long mMode = DBFormListEdit | DBFormCustomQuery; mFrm.load(c, mMode);
The full logic of the FormOrders
application is as follows:
for(;;) { DBFormItem i = mFrm.edit(); if (i == IDC_FORMORDERS_DETAIL) dFrm.edit(mFrm[i]); else if (i == DBFormItemQuery) { DBFormItem j = qFrm.edit(); if (j == DBFormItemSave) { mFrm.load(doQuery(sess, qFrm), mMode); mFrm.setTitle("Search Results"); } else if (j == DBFormItemDelete) { mFrm.load(mCls.createCursor(), mMode); mFrm.setTitle("Order"); } } else //DBFormItemExit break; }
Call the edit
method on the master form and look at the return value. If the row in the table was clicked (i == IDC_FORMORDERS_DETAIL
), then call the edit
method on the detail form and then return to the master form through the loop as shown earlier in the document. If the user clicks on the query(lens) button (i == DBFormItemQuery
), then pop up a query dialog (qFrm.edit
). Once the user sets up the query parameters, the user can click on the Save button to execute the query (j == DBFormItemSave
), Delete button to cancel the query (j == DBFormItemDelete) or exit the form to come back to the previous screen. The doQuery
function creates the DBSqlCursor
based on the search parameters in the query form. To execute the query, load the master form using the DBSqlCursor
and change the form title to Search Results
. If the query was canceled, reload the master form with the original list of objects (mCls.createCursor
) and change its title back to the original.The doQuery
function retrieves the search parameters from the query form as DBData
from its columns. Then it creates a SQL where-clause string and the binding list for it. The multiple choices for the status list are stored inside DBData
as an array of integers. For the company name field, the search is performed using a LIKE
expression—LIKE %s%
—so that any substring matches.
You can customize the UI of your application by configuring your resource file. Also, edit the sodares.rsrc
file to modify the UI resources used by the SODA Forms library.
On PocketPC, when the user clicks on the help button on the toolbar, SODA Forms launches the Windows CE help editor and loads the help file. The default help file is SodaFormHelp.html
, which describes editing the database record. This help file is copied under the \Windows
directory on PocketPC during installation. If you want to display different helpfiles or customize help for every screen in your application, then SODA Forms calls the frm.setHelpFile
method where you can pass in your help file name. Your custom help file should be located under the \Windows
directory on your PocketPC. You may include images and hyperlinks in your help file.
For PocketPC, SODA Forms uses the PocketPC Menubar, which is a combination of menus and the toolbar. The default Menubar comes with the SODA Forms and includes the "Edit" menu and tool bar buttons ("Left", "Right", "New", "Delete", "Save", "Revert", "Query", "Help"). The menubar can be created in the resource editor, so you can create your own or modify the default one. Read the MSDN library on how to create Menubar resources.
Remember to keep the same identifiers for the Menubar itself (IDR_DBFORM_MENUBAR
) and the predefined menu items. You can locate these identifiers in the file SodaRes.h
, which comes with the SODA Forms source code. You should have all the menu and toolbar items that are in the default menubar with the identifiers defined in SodaRes.h
. You can modify toolbar icons, caption, and place toolbar items in your menus, as long as you keep the same identifiers.
To create or modify the existing menubar within SodaForms, then rebuild the project and copy the new SodaForm.dll
on the device for the changes to take effect.
SODA Forms allows one table control on a form to be bound to a list of objects. This list can be specified by the user, using one of the overloaded setObjects
methods in the DBFormCol
class. You can also bind a table to a database column that contains a list of objects. SODA supports the object pointer array datatype directly, but only normalized relational data can be replicated to the Mobile Server. Use DBValueRel
virtual columns to map master-detail relationships into pointers.
To bind a table to the database column, perform the following:
Add the column name for the PocketPC environment by adding the column name and the ListView
resource identifier to the DBFormCols
array that you pass to the DBForm
constructor.
Call the setTableInfo
method in the corresponding DBFormCol
object to specify which columns of the objects are to be displayed in the table, what are the column titles, and how the data is to be aligned.
Users can scroll through the table, click on the headers to sort on the column value and click a row to select it. In this case, the edit
call returns with the object identifier of the selected row and executing the getSelectedPos
method on the DBFormCol
returns the row selected or DB_NEW_POS
, if the user clicked on an empty space. These events are suppressed if the form is dirty.
The application handles any table click. However, you should load the list of objects into another DBForm
, let user edit it, and then save the changes back to the table and to the corresponding database column. DBForm
contains an overloaded edit(DBFormCol)
method that does all the work automatically for you if you only have a single level of a master-detail relationship. For more complicated cases, you could do the following:
Maintain a stack of DBForm
objects.
Call the DBForm::load(DBFormCol)
method when you push a new form on the stack.
Call DBFormCol::saveTo(DBFormCol)
before the pop.
SODA Forms supports multiple kinds of UI resources. For each, the values stored in the database and editing behavior can be customized by specifying an edit type in the third field of DBFormCols
, as described in Section 12.6.5, "Binding UI to Data in the PocketPC Environment", and optionally calling methods on the corresponding DBFormCol
object.
The form developed on PocketPC can have controls that are not bound to database, such as push buttons. Clicking on a button returns from the edit method with the button Id. When developing on PocketPC, use the following control types:
Table 12-3 Control Types for PocketPC
Control Type | Edit Behavior | Value stored in database |
---|---|---|
Checkbox |
The checkbox can be checked on or off. If Tri-state style is specified, then the checkbox can have a greyed-out appearance if the value is unknown. |
True, false or null (for unknown value) is stored in the database. |
ComboBox |
This is a combination of a listbox and the edit control. |
Same as listbox. For "Dropdown" format, with the default edit type, the strings which are not in the original list can be loaded into the edit area of the combo box or stored in the database. |
Edit |
This is a text field for entering characters. Different styles can be specified in the resource editor, to change the appearance and the set of characters that can be entered into the field. For example, the |
Any trailing spaces or newlines will be stripped from user's input. If the result is an empty string, null value will be stored in the database. Otherwise, the trimmed result will be stored. If the control is bound to a numeric column (control should have "Number" style), the entered number will be stored. |
Date-Time Picker |
The control stores and shows date and/or time values. It displays a month calendar to modify the value. You can use any of the following formats: |
Date/time value is stored in the database. If the control has the |
Listbox |
Displays a list of text items. Set iether single or multiple selection type in the resource editor. The latter is useful for the query mode when you want to query by multiple choices within the list. |
Single-selection listbox: If the column edit type is default, the string of the selected item is stored in the database. On input, unknown values or null are shown as an empty string. To allow user to choose a null value, include an empty string as one of the list selections. If the column edit type is DBFormEditListIndex, 0-based index of the selection is stored in the database. This mode is useful where the selection strings are language specific but the database column should always have the same values. Multiple-selection listbox: List selections are stored as array of strings or numbers (when edit type is DBFormEditListIndex). Remember that DBData can store arrays of values of certain type. If there are no selections, null value is stored. |