UltraLite Embedded SQL User's Guide
  Tutorial: Build an Application Using Embedded SQL

Lesson 2: Write an embedded SQL source file

The following procedure creates a sample program that establishes a connection with the UltraLite CustDB sample database and executes a query.

To build the sample embedded SQL UltraLite application

1. Start Microsoft eMbedded Visual C++.

   Choose Start > Programs > Microsoft eMbedded Visual Tools > eMbedded Visual C++.

2. Create a new workspace named UltraLite:

   • Select File > New.

   • Click the Workspaces tab.

   • Choose Blank Workspace. Specify a workspace name UltraLite and specify C:\tutorial as the location to save this workspace. Click OK.

     The UltraLite workspace is added to the Workspace window.

3. Create a new project named esql and add it to the UltraLite workspace.

   • Select File > New.

   • Click the Projects tab.

   • Choose WCE Pocket PC 2002 Application. Specify a project name esql and select Add To Current Workspace. Select the applicable CPUs. Click OK.

   • Choose Create An Empty Project and click Finish.

     The project is saved in the c:\tutorial\esql folder.

4. Create the sample.sqc source file.

   • Choose File > New.

   • Click the Files tab.

   • Select C++ Source File.

   • Select Add to Project and select esql from the dropdown list.

   • Name the file sample.sqc. Click OK.

   • Copy the following source code into the file:

     #include <stdio.h>
     #include <wingdi.h>
     #include <winuser.h>
     #include <string.h>
     #include "uliface.h"
     EXEC SQL INCLUDE SQLCA;
     int WINAPI WinMain( HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int nShowCmd)
     {
        /* Declare fields */
        EXEC SQL BEGIN DECLARE SECTION;
           long pid=1;
           long cost;
           char pname[31];
        EXEC SQL END DECLARE SECTION;   
        /* Before working with data*/
        db_init(&sqlca);
        /* Connect to database */
        EXEC SQL CONNECT "DBA" IDENTIFIED BY "SQL";
        /* 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');
        EXEC SQL COMMIT;   
        /* Fetch row from database */
        EXEC SQL SELECT price, prod_name
              INTO :cost, :pname
              FROM ULProduct
              WHERE prod_id= :pid;
        /* Error handling.  If the row does not exist,
           or if    an error occurs, -1 is returned */
        if((SQLCODE==SQLE_NOTFOUND)||(SQLCODE<0)) {
           return(-1);
        }

     /* Print query results */
        wchar_t query[100];
        wchar_t result[10];
        wchar_t wpname[31];
        mbstowcs(wpname, pname, 31);
        wcscpy(query, L"Product id: ");
        _ltow(pid, result, 10);
        wcscat(query, result);
        wcscat(query, L" Price: ");
        _ltow(cost, result, 10);
        wcscat(query, result);
        wcscat(query, L" Product name: ");
        wcscat(query, wpname);
        wcscpy(result, L"Result");
        MessageBox(NULL, query, result, MB_OK);
        /* Preparing to exit:
        rollback any outstanding changes and disconnect */
        EXEC SQL DISCONNECT;
        db_fini(&sqlca);
        return(0);
     }

   • Save the file.

5. Configure the sample.sqc source file settings to invoke the SQL preprocessor to preprocess the source file:

   • Right-click sample.sqc in the Workspace window and select Settings.

     The Project Settings dialog appears.

   • From the Settings For drop down menu, choose All Configurations.

   • In the Custom Build tab, enter the following statement in the Commands box. Ensure that the statement is entered all on one line.

     The following statement runs the SQL preprocessor sqlpp on the sample.sqc file, and writes the processed output in a file named sample.cpp. The SQL preprocessor translates SQL statements in the source file into C/C++.

     "%asany9%\win32\sqlpp.exe" -q -o WINDOWS -c  "dsn=Ultralite 9.0 Sample" $(InputPath) sample.cpp

     For more information about the SQL preprocessor, see The SQL preprocessor.

   • Specify sample.cpp in the Outputs box.

   • Click OK to submit the changes.

6. Start the Adaptive Server Anywhere personal database server.

   By starting the database server, both the SQL preprocessor and the UltraLite analyzer will have access to your reference database. The sample application uses the CustDB sample database custdb.db as a reference database and as consolidated database.

   Start the database server at the command line from the Samples\UltraLite\CusDB directory containing custdb.db as follows:

   dbeng9 custdb.db

   Alternatively, you can start the database server by selecting Start > Programs > SQL Anywhere 9 > UltraLite > Personal Server Sample for UltraLite.

7. Preprocess the sample.sqc file.

   Because the sample application consists of only one source file, the preprocessor automatically runs the UltraLite analyzer as well and appends extra C/C++ code to the generated source file.

   • Select sample.sqc in the Workspace window. Choose Build > Compile sample.sqc. A sample.cpp file will be created and saved in the tutorial\esql folder.

8. Add sample.cpp to the project:

   • Right-click the Source Files folder in the Workspace window and select Add Files to Folder.

   • Browse to c:\tutorial\esql\sample.cpp and click OK.

     The sample.cpp file appears inside the Source Files folder.

Explanation of the sample program