Using Visual Basic to Connect to PostgreSQL

The first thing you have to do when working with PostgreSQL is connect to the database. In Visual Basic, ODBC is used for interacting with PostgreSQL. In this section, you learn how to connect to the database using ODBC.

The First Contact

Microsoft's Visual Studio is one of those extremely widespread killer applications on Windows. Like MS Office, Visual Studio is a key application providing nearly everything you will need to develop Windows applications ”including Visual Basic and a C++ compiler.

After the core distribution has been installed on the system, we have to install the libraries we will need for interacting with the database. These libraries are known as the MSDN libraries and are not installed with Visual Studio (in version 6.0). Because Microsoft provides a simple user interface for setting up the MSDN libraries, it is rather simple to install the required software packages.

Visual Basic is often used to build database applications because there is hardly a programming language in Windows that enables you to develop applications faster. The key lies in the simplicity of the language. No badly used pointers can endanger the programmer and the stability of the software, and not much code has to be written to handle the memory management, as opposed to what is done in C. But Visual Basic also has disadvantages: Many say that the language is far too slow and not suitable for building huge applications.

Why you want to use Visual Basic is always a question you will have to answer. No matter how you decide, it is always worth it to have a brief look at the programming language. We do so in this section.

A Simple Example

To give you a basic idea of how Visual Basic can be used in combination with PostgreSQL and ODBC, we offer a small application. The target of our application is to write a rudimentary SQL client for PostgreSQL. We want to send SQL statements to the server and display the result in the window of our SQL client. The application will be implemented in Visual Basic 6.0.

Before we start writing the source code, we have to add a very important module to the program that we need for interacting with the database. To add the modules to your project, we click Projects and select References in the menu. The module we have to add is called Microsoft ActiveX Data Objects Library (ADODB). In some cases, this module is activated automatically ”for example, if we add an ADO element to the toolbox or if you add a DataEnvironment or a DataReport component to your program. We recommend adding it manually so that you can be 100% sure that the module is included. If it is not, you will not be able to compile the project.

After adding the module, the next step is to implement the user interface. Designing a user interface is easy, because Visual Studio has been designed to have strong graphical capabilities (see Figure 19.5).

Figure 19.5. Designing the user interface.

In the next step, we define the code our script has to execute when the button in the middle of our window is pressed. The code is very simple (see Figure 19.6).

Figure 19.6. Defining what to do when the button is pressed.

The code calls the Main function, which we have implemented. This function handles all other tasks that have to be done when we click the button.

Here is the function code of the function:

 Option Explicit Sub Main()      ' declaring variables      Set conn = New Connection      Dim result As Recordset      Dim i As Integer      Dim mytext As String      Dim cols As Integer      ' connecting to the database and executing  query      On Error GoTo myend      conn.open "Data Source = PostgreSQL; Database = mydb; Server = www.cybertec.at"      On Error Resume Next      Set result = conn.Execute(sqlwindow.Text1.Text)      ' initializing variables and checking amount of columns in result      cols = 0      mytext = ""      While cols >= 0          On Error GoTo mydisplay          mytext = result.Fields(cols)          cols = cols + 1      Wend      If cols < 1 Then          GoTo myend      End If  mydisplay:      MsgBox "number of columns found in result: " & cols      mytext = ""      ' going through the records returned by the query      While Not result.EOF          ' processing all columns and added the value found to the output window          For i = 1 To cols              mytext = mytext & result.Fields(i - 1) & "  "          Next i          mytext = mytext & Chr$(13) & Chr$(10)          result.MoveNext      Wend      ' adding the data to the output window      sqlwindow.Text2.Text = mytext  myend:      ' this is the end :)      conn.close  End Sub 

The first line tells Visual Basic to postulate an explicit variable declaration. It is not necessary to use the explicit mode, but we recommend turning it on, because it protects you from using undeclared variables. With undeclared variables, if you have a misprint in your variable name , Visual Basic won't raise an error, but compiles the script anyway. This leads to bugs , which can be very annoying for the programmer.

At the beginning of the Main function in this example, we declare several variables that we will need later in the script. The most interesting of these variables are conn and result . conn contains our connection handle. result is defined as Recordset and contains the data returned by the query. In the next step, we open the connection to the database by using conn.open . Data Source is set to PostgreSQL ; this is the name we defined when setting up the parameters of the ODBC connection. We also define the host we want to connect to and the name of the database we want to use. Now we process a While loop to find out how many columns are returned by the query. We try to access all columns and stop counting when the column with the desired id cannot be accessed. In case of an error, the script jumps to mydisplay , which processes the result of the query line by line and generates a string that we can use for displaying the output in the text box at the bottom of our window.

One crucial point is that we have to add line breaks to the string. On Unix systems, this is usually done by adding a simple \n to the string. On Windows, however, the process is a little more complicated, because \n is not supported by Visual Basic. As you can see in the previous example, we have to use Chr$(13) & Chr$(10) to add a line break to our string.

After adding the line break, we use result.MoveNext to move to the next record. After processing all records, we display the result of our query in the text field by using sqlwindow.Text2.Text = mytext . Figure 19.7 shows our script in action.

Figure 19.7. Our script in action.

Our prototype shows how simple it is to develop rudimentary applications with the help of Visual Basic. With just a few easy-to-understand lines of code, it is possible to implement programs that can readily be used for practical reasons. This is one of the most important advantages of Visual Basic.