UserGuide:PostgreSQL Database

Connecting to PostgreSQL

To connect to PostgreSQL, you need to have a PostgreSQL server installed on either your computer or an accessible server. You’ll need to know several things about this installation, including:

The Host IP address or name
The Port being used (usually 5432)
The name of the database on the server
The username to use to connect to the server
The password to use to connect to the server

With this information, you can connect to the database on the server using the PostgreSQLDatabase class:

Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
db.AppName = "MyApp"
If db.Connect Then
// Use the database
Else
// DB Connection error
MessageBox(db.ErrorMessage)
End If

Secure Connections

You can also connect to a PostgreSQL database using SSL for a secure connection. You do this using the SSLMode property to specify the type of secure connection to use:

Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.SSLMode = PostgreSQLDatabase.SSLRequire
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
db.AppName = "MyApp"
If db.Connect Then
// Use the database
End If

Using PostgreSQL

Creating a Table

This SQL creates the Team table used in previous examples:

CREATE TABLE Team (ID INTEGER NOT NULL PRIMARY KEY, Name TEXT, Coach TEXT, City TEXT);

In place of the TEXT data type, which allows an unlimited length string, you might also use the VARCHAR data type which allows you to specify a maximum size for the string:

CREATE TABLE Team (ID INTEGER NOT NULL, Name VARCHAR(100), Coach VARCHAR(100), City VARCHAR(100));

Case Sensitivity

PostgreSQL converts your SQL to lowercase by default. So take a look at this SQL:

SELECT FullName FROM Person;

This gets converted to:

SELECT fullname FROM person;

The only time case is maintained is when you use quotes ("") around the names in the SQL statement. It is best to avoid using quotes in this manner since you'll be forced to use the quotes everywhere so that the case-sensitivity is maintained.

Auto-Incrementing Primary Keys

PostgreSQL does not allow you to create a primary key that auto-increments. But the equivalent functionality is available by using Sequences. A Sequence is a database object that manages unique values for use by primary keys. You use the sequence when you create new rows in a table.

This SQL declares a sequence for the Team table with values starting at 1:

CREATE SEQUENCE TeamSeq START 1;

You use the Sequence in INSERT SQL statements like this:

INSERT INTO Team (ID, Name)
VALUES (nextval('TeamSeq'), 'Seagulls');

The nextval, lastval and currval functions are used to access the next value in the sequence, the last value in the sequence and the current value of the sequence respectively.

Large Objects

Large Objects, or BLOBS, allow you to store non-traditional data in the database such as files, pictures and anything that is binary. Large Objects are stored independently of tables and are referenced using their own unique identifier. To work with BLOBs in PostgreSQL, you use the PostgreSQLLargeObject class.

This example saves a file to a LargeObject:

db.SQLExecute("BEGIN TRANSACTION;")

// Create the Large Object and save its reference
Var oid As Integer
oid = db.CreateLargeObject

// Open the newly created Large Object
Var lo As PostgreSQLLargeObject
lo = db.OpenLargeObject(oid)

// Write the file to the Large Object
Var bs As BinaryStream
bs.Open(inputFile)
Var data As String
While Not bs.EOF
data = bs.Read(1000)
lo.Write(data)
Wend
bs.Close
lo.Close
db.SQLExecute("END TRANSACTION;")

PostgreSQL requires that all large object operations be performed inside of a transaction as shown in the above example.

Notifications

Another feature of PostgreSQL is the Listen and Notify protocol. With Listen and Notify, you can have the PostgreSQL Server generate a notification for events that an app can listen for. To listen for notifications, call the Listen method with the name of the notification to listen for:

db.Listen("LogInNotification")

To actually ask the PostgreSQL Server if any of the notifications being listened for have arrived, call the CheckForNotifications method. Typically you want to do this with a timer so that your app checks for notifications regularly:

db.CheckForNotifications

When actual notifications arrive, the ReceivedNotificationEvent is called with the name of the notification. Send notifications using the Notify method like this:

db.Notify("LogInNotification")

Additional Information

For more information about PostgreSQL:

Video: PostgreSQL
Examples
- Database/PostgreSQL/PostgreSQLExample
- Database/PostgreSQL/LargeObjects
- Database/PostgreSQL/ListenAndNotify
Official Site: www.postgresql.org
Official Docs: www.postgresql.org/docs
Postgres.app

UserGuide

PostgreSQL Database

From Xojo Documentation

Contents