Using northwind programatically Professional

It is time to leverage the power of the PostgreSQL server using Xbase++. As with any database server, accessing the PostgreSQL server requires an application to first establish a connection to the server. After the connection is established, SQL commands can be executed from within the application.

Loading the DatabaseEngine

Access to the PostgreSQL DBMS is provided by the PostgreSQL DatabaseEngine (PGDBE). Like any other Xbase++ DatabaseEngine (DBE), the PGDBE must be loaded using DbeLoad(). Unlike other DBEs however, the PostgreSQL DBE cannot be used to build compound DBEs using DbeBuild(). This is because the PGDBE is a full-fledged engine which already includes features such as SQL relational and ISAM navigational support.

01: PROCEDURE DbeSys 
02: 
03:    IF ! DbeLoad( "PGDBE" ) 
04:       Alert( "Unable to load PostgreSQL PGDBE", {"Ok"} ) 
05:    ENDIF 
06:  
07:    DbeSetDefault( "PGDBE" ) 
08: RETURN 

Although loading a DBE can be done at any time, the procedure DbeSys() is typically used in Xbase++ applications to automatically load the required DBE(s) at application startup. Since the PGDBE already is a full-featured engine, the function DbeBuild() is not used. The PGDBE is made the default DBE using DbeSetDefault() (line #7). This step is not strictly required, though. The DBE could also be specified later in the VIA clauses of USE or Universal SQL statements.

Connecting to the Server

Before data can be accessed on the PostgreSQL server, a connection must be established. This step is required because applications using the PGDBE are client/server applications in nature. For this reason, a connection must be established even if the PostgreSQL server is installed on the local machine. The DacSession() class is included in Xbase++ for establishing connections to a database server.

15:   cConnect := "DBE=pgdbe;SERVER=dbsrv01;DB=northwind;UID=postgres;PWD=postgres" 
16:   oSession := DacSession():New( cConnect ) 
17: 
18:   IF .NOT. oSession:isConnected() 
19      ? oSession:getLastMessage() 
20:     Alert( "Unable to establish connection to dbsrv01", {"Quit"} ) 
21:     QUIT 
22:   ENDIF 
23: 

The connection string used by the DacSession object is defined in line #15. It contains all the information required for establishing a connection to the server. In this example, the connection string contains the name of the database engine ("PGDBE"), the name of the server ("dbsrv01") and the database ("northwind") we want to connect to. Finally, the username ("postgres") and password ("postgres") are included for proper authentication with the server.

A DacSession object automatically establishes a connection when it is created with the method :new(). This is why the successful connection needs to be tested using the :isConnected()method in line #18. The sample program quits in case the connection fails and outputs a message with the reason for the connection failure beforehand. Error information can be retrieved with the :getLastMessage() method of the session object.

Now that the application is connected to the server, it is ready to access the server's functionality. An example illustrating how this might be accomplished is provided below. The sample brings everything together by performing all of the required steps, from loading the PostgresSQL DatabaseEngine to executing an SQL statement and browsing the result delivered by the PostgreSQL server.

The easiest way to run the example code is by executing it in the Xbase++ Workbench. To do this, open up the Workbench and create a new project for compiling the code. This is accomplished by first selecting the File->New menu item to display the New Items dialog. Afterwards, select Windows Target->Console Application in the dialog, and enter a meaningful name for the new project. For example, "firststep". Click the Ok button, and the workbench creates an empty console application project. Now the example code needs to be transferred into the project. Double-click the file main.prg in the Object Inspector at the left-hand side. This loads the file into the source code editor. Erase all of the template code in the file, and transfer the source code provided below over into the editor. To do this, select the code with your mouse and use the Copy command to transfer it into the clipboard. Afterwards, switch back to the Xbase++ Workbench and insert the code using the Paste command. When done, hit F9 to build and execute the sample code.

// PostgresSQL DBE header file is required 
#include "pgdbe.ch" 

PROCEDURE Main 
LOCAL cConnect 
LOCAL oSession 

// Load the PostgreSQL DatabaseEngine 
IF(!DbeLoad("pgdbe")) 
  Alert( "Unable to load the PostgreSQL DatabaseEngine", {"Quit"} ) 
  QUIT 
ENDIF 

// Establish the connection 
cConnect := "DBE=pgdbe;SERVER=localhost;DB=northwind;UID=postgres;PWD=postgres" 
oSession := DacSession():New( cConnect ) 

// Check for connection success 
IF .NOT. oSession:isConnected() 
  ? oSession:GetLastMessage() 
  Alert( "Unable to establish connection to server", {"Quit"} ) 
  QUIT 
ENDIF 

// Perform simple select and browse 
// the result set 
SELECT * FROM customers VIA (oSession) 
Browse() 

// Disconnect from server 
oSession:disconnect() 
RETURN 

Summary

In this section, Xbase++ was used programmatically to connect to the PostgreSQL server and to access the data in a database by using SQL statements. This section concludes the introductional "first steps" chapter.