Connecting to a database

Accessing and manipulating data with dynamic SQL

UltraLite C++ User's Guide
Understanding UltraLite Development

Connecting to a database

Any UltraLite application must connect to a database before it can carry out any operation on the data. This section describes how to write code to connect to an UltraLite database.

To connect to an UltraLite database

Create a DatabaseManager object.

The DatabaseManager object requires a SQL Communications area.

The SQL communications area (SQLCA) is a structure that handles communication between the application and the database.

You can create only one DatabaseManager object per application. This object is at the root of the object hierarchy. For this reason, it is often best to declare the DatabaseManager object global to the application.

The following code creates a DatabaseManager object named dbMgr.
```
ULSqlca sqlca;
sqlca.Initialize();
DatabaseManager * dbMgr   = ULInitDatabaseManager( sqlca );
```
Declare a Connection object.

Most applications use a single connection to an UltraLite database and keep the connection open all the time. Multiple connections are only required for multi-threaded data access. For this reason, it is often best to declare the Connection object global to the application.
```
Connection *  conn = UL_NULL;
```
Attempt to open a connection to an existing database.
- The following code establishes a connection to an existing database held in the mydata.udb file on Windows.
```
static ul_char * parms = "file_name=mydata.udb";
ULValue lp( parms );
conn = dbMgr->OpenConnection( sqlca, lp );
```
  If you are using the Unicode version of the UltraLite runtime library, set the connection string (parms) as follows:
```
static ul_char * parms =
UL_TEXT( "file_name=mydata.udb"
```
- The method establishes a connection to an existing UltraLite database file and returns that open connection as a Connection object.
- It is common to deploy a schema file rather than a database file, and to let UltraLite create the database file on the first connection attempt. If no database file exists, you should check for the error and create a database file.
  
  The following code illustrates how to catch the error when the database file does not exist:
```
if( sqlca.GetSQLCode() == 
   SQLE_ULTRALITE_DATABASE_NOT_FOUND )
{
    // action here
}
```

If no database exists, create a database and establish a connection to it.

The following code carries out this task on Windows, using a schema file of mydata.usm.

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() );
}

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( "uid=dba;pwd=sql" )
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 );

In general, you will want to specify a more complete path to the file.

Using the Connection object

Properties or methods of the Connection object govern global application behavior, including the following:

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.
Synchronization A set of objects governing synchronization are accessed from the Connection object.

For more information, see the API Reference in the online documentation.
Tables UltraLite tables are accessed using methods of the Connection application.
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 Connection.SyncParms and Connection.SyncResult in the API Reference in the online documentation.

Multi-threaded applications

Each Connection and all objects created from it should be used on a single thread. If you need to have multiple threads accessing the UltraLite database, then each thread should have its own connection.