PostgreSQLDatabase

From Xojo Documentation
Jump to: navigation, search

Class (inherits from Database)

Used to connect to a PostgreSQL database.

Events
ReceivedNotification


Properties
AppName Host SSLCertificate
DatabaseName MultiThreaded SSLKey
Error ReadOnlyProperty.png Password SSLMode
ErrorCode ReadOnlyProperty.png Port UserName
ErrorMessage ReadOnlyProperty.png SSLAuthority


Methods
CheckForNotifications FieldSchema Prepare
Close IndexSchema Rollback
Commit InsertRecord SQLExecute
Connect Listen SQLSelect
CreateLargeObject Notify TableSchema
DeleteLargeObject OpenLargeObject Unlisten

Notes

In order to use this class, you must have the PostgreSQLPlugin database plug-in in your plugins folder.

The PostgreSQL plug-in supports MD5 password authentication.

A field specified as type Float is actually implemented by PostgreSQL as a double (8 bytes).

PostgreSQL dates: field types Date, Time, and TimeStamp are supported by using the DateColumn and DateValue methods for a date representation of this data, or use StringValue to get a human-readable date and/or time.

The PostgreSQLDatabase engine supports only the MoveNext RecordSet navigation method.

Also be sure to refer to the official PostgreSQL documentation.

Threading

SQLSelect and SQLExecute statements do not block when called from within Threads.

Xojo Cloud

Xojo Cloud includes built-in support for PostgreSQL databases, which you can enable in your Xojo Cloud control panel.

To access an external PostgreSQL databases from web apps running on Xojo Cloud, you will first have to use the FirewallPort class to open the port used to connect to PostgreSQL, which is usually 5432.

Dim fwp As New XojoCloud.FirewallPort(5432, _
XojoCloud.FirewallPort.Direction.Outgoing)
fwp.Open // This call is synchronous
If fwp.IsOpen Then
// Do what you need to do
End If

Refer to the Xojo Cloud page for more information about using PostgreSQL with Xojo Cloud.

Large Objects

PostgreSQLDatabase implements PostgreSQL's large objects. PostgreSQL requires that all large object operations be performed inside of a transaction. Therefore, you must start a transaction before you perform your first large object operation:

db.SQLExecute("BEGIN TRANSACTION")

After you have performed your last large object operation, you should close the transaction, like this:

db.SQLExecute("END TRANSACTION")

Please see the PostgreSQLLargeObject class for information on how to work with large objects.

Listen and Notify Protocol

The PostgreSQLDatabase class implements the listen and notify protocol of PostgreSQL databases.

To send a notification, call the PostgreSQLDatabase.Notify method with the name of the notification you want to send. For example, if you wanted to send a notification called "Hello World", you would call Notify like this:

// db is a previously connected PostgreSQL database
db.Notify("Hello World")

To check for notifications, call the PostgreSQLDatabase.CheckForNotifications method. You will receive notifications as a PostgreSQLDatabase.ReceivedNotification event. The ReceivedNotification event is called with three arguments: the name of the notification as a String, the ID of the process sending the notification as an Integer, and an extra argument that the PostgreSQL documentation says is not used at this time. The name parameter is the same name that you used with the Notify method.

If you would like to check for notifications automatically, at some set interval, then use a Timer and call CheckForNotifications in its Action event.

Examples

This example opens an existing PostgreSQL database.

Dim db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "myDatabase"
db.AppName = "MyApp"
db.Username = "Charlie"
db.Password = "mashie"
If db.Connect Then
//proceed with database operations
Else
MsgBox("The connection failed.")
End If

Example Projects

  • Example Projects/Database/PostgreSQL/LargeObjects
  • Example Projects/Database/PostgreSQL/ListenAndNotify

See Also

Database Class, DatabaseRecord, PostgreSQLLargeObject, PreparedSQLStatement, PostgreSQLPreparedStatement, RecordSet classes, PostgreSQL Web Site

Personal tools
Namespaces

Variants
Actions
Main
Content
Starting Out
Dig Deeper
More Help
Toolbox
About