Using MBS SQL Plugin with PostgreSQL

We like to show you how to use our MBS Xojo SQL Plugin with the SQLDatabaseMBS class with a local PostgreSQL server. This includes connecting, inserting records and running a query to load them into a listbox.

To test locally, we can download the PostgreSQL app and install it locally on our Mac (or Windows/Linux version on PC). With the app, we get a way to launch the server locally and create a database. Then we can open a connection in the Terminal and directly run queries.

Let’s create a test table there with a few fields:

CREATE TABLE Persons ( PersonID SERIAL PRIMARY KEY, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );


First we connect to the database. For this we create a new instance of SQLDatabaseMBS class. We setup options for the specific database we like to connect. For PostgreSQL, this means we pass the path to the local client library. For testing you can just point to the libpq in the PostgreSQL application. Or you download some libraries from our website: Libraries.

Sub Connect()
	// Connct to our local database
	db = New SQLDatabaseMBS
	// where is the library?
	db.Option(SQLConnectionMBS.kOptionLibraryPostgreSQL) = "/Applications/"
	// your login credentials
	// ask plugin to raise exception for errors in API 1 commands.
	db.RaiseExceptions = true
	// and connect
	If db.Connect Then
		MessageBox "Server Version: "+db.ServerVersionString
		MessageBox db.ErrorMessage
	End If
End Sub


Now we insert records and show you four different ways:

  • Using DatabaseRow class in newer Xojo database API.
  • Using a dictionary, which is convenient, when you get data as dictionary, e.g. from ParseJSON function in Xojo. Our InsertRecord method in MBS Xojo SQL Plugin is a speciality, we made for clients, who like to avoid copying data to DatabaseRow or DatabaseRecord.
  • Using DatabaseRecord class, the older way with InsertRecord function.
  • Using prepared statement with named parameters. This has the advantage, that you can easily insert a new parameter without changing indexes.
  • Using prepared statement with indexed parameters.

All ways will insert the record and should work just fine.

Sub Insert()
// insert records in various ways:

// the test table was created using:
// CREATE TABLE Persons ( PersonID SERIAL PRIMARY KEY, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

// Use DatabaseRow in API 2
Dim r As New DatabaseRow

r.Column("FirstName") = "Joe"
r.Column("LastName") = "Miller"
r.Column("Address") = "Main Street 123"
r.Column("City") = "Test City"

db.AddRow "Persons", r

// Use Dictionary with MBS
Dim d As New Dictionary

d.value("FirstName") = "Tom"
d.value("LastName") = "Smith"
d.value("Address") = "Back Street 234"
d.value("City") = "Little Village"

db.InsertRecord "Persons", d

// Use Database with API 1
Dim a As New DatabaseRecord

a.Column("FirstName") = "Susen"
a.Column("LastName") = "Smith"
a.Column("Address") = "Back Street 234"
a.Column("City") = "Little Village"

db.InsertRecord "Persons", a

// Use prepared statement with named parameters
Dim p As SQLPreparedStatementMBS = db.Prepare("INSERT INTO Persons (FirstName, LastName, Address, City) VALUES (:FirstName, :LastName, :Address, :City)")

p.Bind("FirstName", "Sarah")
p.Bind("LastName", "Miller")
p.Bind("Address", "Main Street 123")
p.Bind("City", "Test City")


// Use prepared statement with indexed parameters
p = db.Prepare("INSERT INTO Persons (FirstName, LastName, Address, City) VALUES (?, ?, ?, ?)")

p.Bind(0, "Tim")
p.Bind(1, "Jones")
p.Bind(2, "First Avenue 567")
p.Bind(3, "Big City")


End Sub


Now let us do a query with both older RecordSet and newer RowSet. From the plugin point of view, both classes are identical. Just the names changed and the RowSet class raises exceptions in case of errors. We add rows to the listbox and you can view them:

Sub LoadRecords()
	Dim db As SQLDatabaseMBS = app.db
	// read rows in chunks of 100 rows
	db.Option(SQLCommandMBS.kOptionPreFetchRows) = "100"
	// API 2 with RowSet
	Dim rs As RowSet = db.SelectSQL("SELECT * FROM Persons")
	While Not rs.AfterLastRow
		Dim personid As Integer = rs.Column("personid").IntegerValue
		Dim FirstName As String = rs.Column("FirstName").StringValue
		Dim LastName As String = rs.Column("LastName").StringValue
		Dim Address As String = rs.Column("Address").StringValue
		Dim City As String = rs.Column("City").StringValue
		List.AddRow FirstName, LastName, Address, City
		List.RowTagAt(List.LastAddedRowIndex) = personid
	// API 1 with RecordSet
	Dim r As RecordSet = db.SQLSelect("SELECT * FROM Persons")
	While Not r.EOF
		Dim personid As Integer = r.Field("personid").IntegerValue
		Dim FirstName As String = r.Field("FirstName").StringValue
		Dim LastName As String = r.Field("LastName").StringValue
		Dim Address As String = r.Field("Address").StringValue
		Dim City As String = r.Field("City").StringValue
		List.AddRow FirstName, LastName, Address, City
		List.RowTag(List.LastIndex) = personid
End Sub

There is a little specialty for MBS Xojo SQL Plugin included: We can set options for queries. In this case we ask the plugin to load records in chunks of 100 records. This reduces the speed of MoveNext/MoveToNextRow methods. Instead of asking server for each new record, we ask it once every 100 records.

You can decide between using API 1 or 2 in Xojo, but also decide to use our additional methods. And with SQLConnectionMBS class we even got another interface, which is closer to the native APIs of the database clients and can do even more like, streaming BLOBs.

We hope this may get you started with connecting to PostgreSQL. Let us know if you have questions.

1 Like

What’s the difference with Xojo native ?

More database types to connect to and more options.

See list of points and types here:
MBS Xojo SQL Plugin

And in my experience, rock stable