|
Although the tutorial program is simple, it contains elements that must be present in every embedded SQL source file used for database access.
The following list describes the key elements in the tutorial program. Use these steps as a guide when creating your own embedded SQL UltraLite application.
Include the appropriate header files.
The tutorial program includes the header files tchar.h to support use of the TCHAR type specification and windows.hto support the use of the MessageBox function to display messages on the CE device.
Define the SQL communications area, sqlca.
EXEC SQL INCLUDE SQLCA;
This definition must be the first embedded SQL statement in each source file; place it at the end of your include list.
Prefix SQL statementsAll SQL statements must be prefixed with the keywords EXEC SQL and must end with a semicolon. |
Define host variables by creating a declaration section.
Host variables are used to send values to the database server or receive values from the database server. In this tutorial, a query retrieves a product id, cost and product name from the database. Define the host variables as follows:
EXEC SQL BEGIN DECLARE SECTION; long pid=1; long cost; TCHAR pname[31]; EXEC SQL END DECLARE SECTION;
See Using host variables.
Declare local program variables (in this tutorial application the local variables are used for building and displaying screen messages:
TCHAR output[200]; TCHAR result[10];
Call the embedded SQL library function db_init to initialize the UltraLite runtime library.
Call this function as follows:
db_init(&sqlca);
Define a label where control is to be transferred in case of a runtime error while processing a SQL statement:
EXEC SQL WHENEVER SQLERROR GOTO error;
Connect to the database using the CONNECT statement.
To connect to the UltraLite sample database, the application must supply the name and location of the database file. The database file name is esqldb.udb located in the root directory of the device.
EXEC SQL CONNECT USING 'dbf=\esqldb.udb'
Insert data into database tables.
/* Fill table with data first */
EXEC SQL INSERT INTO ULProduct(
prod_id, price, prod_name)
VALUES (1, 400, '4x8 Drywall x100');
EXEC SQL INSERT INTO ULProduct (
prod_id, price, prod_name)
VALUES (2, 3000, '8''2x4 Studs x1000');
/* Commit changes (INSERTs) to database */
EXEC SQL COMMIT;
When you synchronize the remote database with the consolidated database, the tables are filled with values so that you may execute Select, Update, or Delete commands.
Rather than using synchronization, this code directly inserts data into the tables. Directly inserting data is a useful technique during the early stages of UltraLite development.
If you use synchronization and your application fails to execute a query, it might be due to a problem in the synchronization process or due to a mistake in your program. Locating the source of such a failure may be difficult. If you directly fill tables with data in your source code rather than relying on synchronization, if your application fails, you will know automatically that the failure is due to a mistake in your program.
After you have tested that there are no mistakes in your program, remove the insert statements and replace them with a call to the ULSynchronize function to synchronize the remote database with the consolidated database.
Execute your SQL query.
/* Fetch row 1 from database */
EXEC SQL SELECT price, prod_name
INTO :cost, :pname
FROM ULProduct
WHERE prod_id= :pid;
/* pid was initialized to 1, get first row inserted */
The tutorial program executes a query that returns one row of results. The results are stored in the previously defined host variables cost and pname. In the embedded SQL statement the variable names are prefixed by a colon character to indicate to the sql preprocessor that the names are program variables.
Display the results of the query:
/* Print query results */
wsprintf( output, TEXT("Product id: %d\n price: %d\n name: %s"), pid, cost, pname );
MessageBox(NULL, output, result, MB_OK);
Provide the error handling routine.
The Embedded SQL statement
EXEC SQL WHENEVER SQLERROR GOTO error;
that appears near the beginning of this tutorial program directs any error condition to be handled by transferring control to the program label error. The program defines this handler code to write a message containing the SQL error code from the field SQLCODE and exit the main function with a -1 result.
Disconnect from the database.
Before disconnecting from the database the application must be careful to commit any changes (by issuing the EXEC SQL COMMIT statement, otherwise pending changes are discarded during disconnection.
To disconnect, use the DISCONNECT statement as follows:
EXEC SQL DISCONNECT;
To properly close the database interface and free resources that were used, call the function db_fini:
db_fini(&sqlca);