« MBS Xojo ChartDirecto… | Home | MBS Xojo Plugins, ver… »

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)
);

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.

The biggest plugin in space...
10 04 22 - 08:57

Start Chat