Platforms to show: All Mac Windows Linux Cross-Platform
/SQL/SQLDatabaseMBS PostgreSQL
Required plugins for this example: MBS SQL Plugin
You find this example project in your Plugins Download as a Xojo project file within the examples folder: /SQL/SQLDatabaseMBS PostgreSQL
This example is the version from Sun, 9th Apr 2022.
Project "SQLDatabaseMBS PostgreSQL.xojo_binary_project"
Class App Inherits Application
Const kEditClear = "&Löschen"
Const kFileQuit = "Beenden"
Const kFileQuitShortcut = ""
EventHandler Sub Open()
Connect
Insert
End EventHandler
Sub Connect()
// Connct to our local database
db = New SQLDatabaseMBS
// where is the library? You may need to change this!
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
Function FindFile(name as string) As FolderItem
// Look for file in parent folders from executable on
dim parent as FolderItem = app.ExecutableFile.Parent
while parent<>Nil
dim file as FolderItem = parent.Child(name)
if file<>Nil and file.Exists then
Return file
end if
parent = parent.Parent
wend
End Function
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
Property db As SQLDatabaseMBS
End Class
Class MainWindow Inherits Window
Control List Inherits Listbox
ControlInstance List Inherits Listbox
End Control
EventHandler Sub Open()
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 EventHandler
End Class
MenuBar MenuBar1
MenuItem FileMenu = "&Ablage"
MenuItem FileQuit = "#App.kFileQuit"
MenuItem EditMenu = "&Bearbeiten"
MenuItem EditUndo = "&Rückgängig"
MenuItem UntitledMenu1 = "-"
MenuItem EditCut = "&Ausschneiden"
MenuItem EditCopy = "&Kopieren"
MenuItem EditPaste = "&Einfügen"
MenuItem EditClear = "#App.kEditClear"
MenuItem UntitledMenu0 = "-"
MenuItem EditSelectAll = "&Alles auswählen"
End MenuBar
End Project
See also:
- /SQL/SQLDatabaseMBS Firebird Connect
- /SQL/SQLDatabaseMBS Microsoft SQL Stored Procedure
- /SQL/SQLDatabaseMBS Microsoft SQL via ODBC on Linux
- /SQL/SQLDatabaseMBS MySQL Connect
- /SQL/SQLDatabaseMBS ODBC Connect
- /SQL/SQLDatabaseMBS Oracle Connect
- /SQL/SQLDatabaseMBS SQLite Connect console
- /SQL/SQLDatabaseMBS SQLite Encryption Fetch values
- /SQL/SQLDatabaseMBS SQLite Fetch values
- /SQL/SQLDatabaseMBS SQLite select version
The items on this page are in the following plugins: MBS SQL Plugin.