iAnywhere.com - Connecting to a database

Developing Applications Using the UltraLite C++ Component

Connecting to a database

UltraLite applications must connect to a database before carrying out operations on the data in it. This section describes how to connect to an UltraLite database. You can find sample code in Samples\UltraLite\CustDB.

Using the Connection object

The following properties of the Connection object govern global application behavior.

Commit behavior In the UltraLite C++ component, there is no autocommit mode. Each transaction must be followed by a Connection.Commit statement.
For more information, see Managing transactions.
User authentication You can change the user ID and password for the application from the default values of DBA and SQL by using methods to Grant and Revoke connection permissions. Each application can have a maximum of four user IDs.
For more information, see User authentication in UltraLite and Authenticating users.
Synchronization A set of objects governing synchronization is accessed from the Connection object.
For more information, see Synchronizing data.
Tables UltraLite tables are accessed using methods of the Connection object.
For more information, see Accessing data with the Table API.
Prepared statements A set of objects is provided to handle the execution of dynamic SQL statements and to navigate result sets.
For more information, see Accessing data using dynamic SQL.

For more information about the Connection object, see Class UltraLite_Connection.

To connect to an UltraLite database

Use the UltraLite namespace.

Using the UltraLite namespace allows you to use simple names for classes in the C++ Component interface.
```
using namespace UltraLite;
```
Create and initialize a DatabaseManager object and an UltraLite SQL communications area (ULSqlca). The ULSqlca is a structure that handles communication between the application and the database.

The DatabaseManager object is at the root of the object hierarchy. You create only one DatabaseManager object per application. It is often best to declare the DatabaseManager object as global to the application.
```
ULSqlca sqlca;
sqlca.Initialize();
DatabaseManager * dbMgr = ULInitDatabaseManager(sqlca);
```
For more information, see Class UltraLite_DatabaseManager_iface.

On the Palm OS, you should precede the call to ULInitDatabaseManager by a call to ULEnableFileDB or ULEnablePalmRecordDB. This sets the database store to use the Palm data store or the virtual file system. For more details, see ULEnablePalmRecordDB function and ULEnableFileDB function.
Declare a Connection object.

Most applications use a single connection to an UltraLite database and leave the connection open. Multiple connections are only required for multi-threaded data access. For this reason, it is often best to declare the Connection object as global to the application.
```
Connection *  conn = UL_NULL;
```
For more information, see Class UltraLite_Connection_iface

Open a connection to an existing database, or create a new database if the specified database file does not exist.

Most UltraLite applications deploy a schema file rather than a database file, and let UltraLite create the database file on the first connection attempt. Thus, the following code attempts to connect to an existing database. If the database file does not exist, the application creates a database file.

// specify the location of the database file
static ul_char * parms = "file_name=mydata.udb";
ULValue lp( parms );
conn = dbMgr->OpenConnection( sqlca, lp );
if( sqlca.GetSQLCode() == 
  SQLE_ULTRALITE_DATABASE_NOT_FOUND ) {
  static ul_char * parms = UL_TEXT("schema_file=mydata.usm")
  UL_TEXT(";file_name=mydata.udb");
  ULValue lp( parms );
  conn = dm->CreateAndOpenDatabase( sqlca, lp );
  if( sqlca.GetSQLCode() < SQLE_NOERROR ){
    printf( "Open failed with sql code: %d.\n" ,
    sqlca.GetSQLCode() );
  }
}

For more information, see OpenConnection Function and CreateAndOpenDatabase Function.

Example

The following code opens a connection to an UltraLite database named mydata.udb.

#include "uliface.h"
using namespace UltraLite; 
static ul_char *  parms =
UL_TEXT( ";file_name=tutcustomer.udb"  )
UL_TEXT( ";schema_file=tutcustomer.usm" );
ULSqlca sqlca;
DatabaseManager * dm   = UL_NULL;
Connection *   conn   = UL_NULL;
sqlca.Initialize();
dm = ULInitDatabaseManager( sqlca );
if( dm == UL_NULL ){
   // You may have mismatched UNICODE vs. ANSI runtimes.
   return 1;
}
ULValue  lp( parms );
conn = dm->OpenConnection( sqlca, lp );
if( sqlca.GetSQLCode() == 
    SQLE_ULTRALITE_DATABASE_NOT_FOUND ) {
   conn = dm->CreateAndOpenDatabase( sqlca, lp );
   if( sqlca.GetSQLCode() < SQLE_NOERROR ) {
      printf( "Open failed with sql code: %d.\n" , 
      sqlca.GetSQLCode() );
      return NULL;
   }  else {
      printf( "Connected to a new database.\n" );
   }
} else {
   printf( "Connected to an existing database.\n" );
}
return( conn );

Multi-threaded applications

Each Connection and all objects created from it should be used on a single thread. If your application requires multiple threads accessing the UltraLite database, each thread requires a separate connection.