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) );
Connect
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/Postgres.app/Contents/Versions/14/lib/libpq.5.dylib"
// your login credentials
db.DatabaseName="PostgreSQL:127.0.0.1,5432@cs"
db.UserName="cs"
db.Password=""
// 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
Else
MessageBox db.ErrorMessage
End If
End Sub
Insert
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")
p.ExecuteSQL
// 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")
p.ExecuteSQL
End Sub
Query
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
rs.MoveToNextRow
Wend
// 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
r.MoveNext
Wend
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.