Class # PostgreSQLDatabase

forsearch

Database --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ## Description Used to connect to a [PostgreSQL](http://www.postgresql.org/) database. ## Properties

table-centered_columns_3_and_4

| Name | Type | Read-Only | Shared | |-----------------------------------------------------|-------------------------------------|-----------|--------| | `AppName` | `String` | | | | `DatabaseName` | `String` | | | | `Host` | `String` | | | | `MetaData` | `String` | | | | `MultiThreaded` | `Boolean` | | | | `Password` | `String` | | | | `Port` | `Integer` | | | | `SSLAuthority` | `FolderItem` | | | | `SSLCertificate` | `FolderItem` | | | | `SSLKey` | `FolderItem` | | | | `SSLMode` | `Integer` | | | | `Tag` | `Variant` | | | | `UserName` | `String` | | | ## Methods

table-centered_column_4

| Name | Parameters | Returns | Shared | |-------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------|--------| | `AddRow` | tableName As `String`, row As `DatabaseRow` | | | | | tableName As `String`, row As `DatabaseRow`, idColumnName As `String` = "" | `Integer` | | | `AffectedRowCount` | | `UInt64` | | | `BeginTransaction` | | | | | `CheckForNotifications` | | | | | `Close` | | | | | `CommitTransaction` | | | | | `Connect` | `Optional` additionalOptions As `String` = "" | | | | `CreateLargeObject` | | `Integer` | | | `DeleteLargeObject` | oid As `Integer` | | | | `ExecuteSQL` | command As `String`, `ParamArray` values() As `Variant` | | | | `IsConnected` | | `Boolean` | | | `Listen` | Name As `String` | | | | `Notify` | Name As `String` | | | | `OpenLargeObject` | oid As `Integer`, `Optional` ReadOnly As `Boolean` = `False` | `PostgreSQLLargeObject` | | | `Prepare` | statement As `String` | `PostgreSQLPreparedStatement` | | | `RollbackTransaction` | | | | | `SelectSQL` | query As `String`, `ParamArray` values() As `Variant` | `RowSet` | | | `StopListening` | Name As `String` | | | | `TableColumns` | tableName As `String` | `RowSet` | | | `TableIndexes` | tableName As `String` | `RowSet` | | | `Tables` | | `RowSet` | | ## Events

table-centered_column_4

| Name | Parameters | Returns | |-----------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------|---------| | `ReceivedNotification` | Name As `String`, ID As `Integer`, Extra As `String` | | ## Property descriptions

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

forsearch

PostgreSQLDatabase.AppName **AppName** As `String` > Use this to specify the name of the app that is connected to the PostgreSQL database. > > When using PostgreSQL DB tools that show this name, setting this property makes it easier to know which apps owns the connection. > > ``` xojo > ' db is an existing PostgreSQLDatabase connection > db.AppName = App.ExecutableFile.Name > ```

forsearch

PostgreSQLDatabase.DatabaseName **DatabaseName** As `String` > The name of the database to open. > > The DatabaseName is typically used to identify the specific database to use on the server. > > This code connects to a PostgreSQL database called "BaseballLeague": > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > ' DB Connection error > MessageBox(error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.Host **Host** As `String` > The database host name or IP address of the database server. > > This examples connects to a PostgreSQL database called "BaseballLeague": > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > ' DB Connection error > MessageBox(error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.MetaData **MetaData** As `String` > Used to store custom data that doesn't belong in the other properties.

forsearch

PostgreSQLDatabase.MultiThreaded **MultiThreaded** As `Boolean` > If `True`, calls to SelectSQL and ExecuteSQL are threaded. The default is `True`. > > When MultiThreaded is `True`, database access does not block other threads (or otherwise lock your running application). > > However, performance is greatly improved if MultiThreaded = `False` > > ``` xojo > ' db is an existing PostgreSQLDatabase connection > db.MultiThreaded = False > ```

forsearch

PostgreSQLDatabase.Password **Password** As `String` > The password that is required for access to the database. Typically used in conjunction with `UserName`. > > This examples connects to a PostgreSQL database called "BaseballLeague": > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > ' DB Connection error > MessageBox(error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.Port **Port** As `Integer` > The port of the PostgreSQL database to connect to. PostgreSQL's default port is `5432`. > > This example opens an existing PostgreSQL database and changes the Port. > > ``` xojo > Var db As PostgreSQLDatabase > > db = New PostgreSQLDatabase > db.Host = "192.168.1.172" > db.port = 5516 > db.DatabaseName = "myDatabase" > db.Username = "Charlie" > db.Password = "mashie" > > Try > db.Connect > ' proceed with database operations > Catch error As DatabaseException > MessageBox("The connection failed: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.SSLAuthority **SSLAuthority** As `FolderItem` > Specifies the root SSL certificate file.

forsearch

PostgreSQLDatabase.SSLCertificate **SSLCertificate** As `FolderItem` > Specifies the file for the client SSL certificate, replacing the default ~/.postgresql/postgresql.crt. This parameter is ignored if an SSL connection is not made. > > This example specifies that the connection should try to use SSL using the specified certificate: > > ``` xojo > Var certFile As New FolderItem("LocalPGCertfile.crt", FolderItem.PathModes.Native) > > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.SSLMode = PostgreSQLDatabase.SSLAllow > db.SSLCertificate = certFile > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > MessageBox("Connection Failed: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.SSLKey **SSLKey** As `FolderItem` > Specifies the location for the secret key used for the client certificate. It can specify a file that will be used instead of the default ~/.postgresql/postgresql.key file. > > This example specifies that the connection should try to use SSL using the specified certificate and secret key: > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.SSLMode = PostgreSQLDatabase.SSLAllow > db.SSLCertificate = New FolderItem("LocalPGCertfile.crt", FolderItem.PathModes.Native) > db.SSLKey = New FolderItem("LocalKeyFile.key", FolderItem.PathModes.Native) > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > MessageBox("Connection failed: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.SSLMode **SSLMode** As `Integer` > Specifies the type of SSL connection to use to connect to the database server. Use the constant values SSLDisable, SSLAllow, SSLPrefer, SSLRequire, SSLVerifyCA and SSLVerifyFull. > > These PostgreSQL constants are used to specify SSLMode: > > - SSLDisable (default): Only try a non-SSL connection > - SSLAllow: First try a non-SSL connection; if that fails, try an SSL connection > - SSLPrefer: First try an SSL connection; if that fails, try a non-SSL connection > - SSLRequire: Only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified > - SSLVerifyCA: Only try an SSL connection, and verify that the server certificate is issued by a trusted CA > - SSLVerifyFull: Only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the server hostname matches that in the certificate > > For more information about SSL usage with PostgreSQL, refer to [Secure TCP/IP Connections with SSL](http://www.postgresql.org/docs/9.1/static/ssl-tcp.html). > > This example specifies that the connection should try to use SSL: > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.SSLMode = PostgreSQLDatabase.SSLAllow > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > MessageBox("Connection failed. Error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.Tag **Tag** As `Variant` > Used to store any value you wish to associated with this instance of the database.

forsearch

PostgreSQLDatabase.UserName **UserName** As `String` > The username that is required for access to the database. > > This code connects to a PostgreSQL database called "BaseballLeague": > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > ' DB Connection error > MessageBox(error.Message) > End Try > ``` ## Method descriptions

forsearch

PostgresqlDatabase.AddRow **AddRow**(tableName As `String`, row As `DatabaseRow`) > Inserts *row* as a new row in *tableName*. > > Always look for a `DatabaseException` to verify whether or not the data was added. > > This code adds a row to an existing Team table with the columns "Name", "Coach" and "City". It's assumed that the variable db contains an active Database connection: > > ``` xojo > Var row As New DatabaseRow > > ' ID will be updated automatically > row.Column("Name") = "Penguins" > row.Column("Coach") = "Bob Roberts" > row.Column("City") = "Boston" > > Try > db.AddRow("Team", row) > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ``` **AddRow**(tableName As `String`, row As `DatabaseRow`, idColumnName As `String` = "") As `Integer` > Inserts *row* as a new row in *tableName* returning the newly assigned ID in the *idColumnName* column. > > Always look for a `DatabaseException` to verify whether or not the data was added. > > This code adds a row to an existing Team table with the columns "Name", "Coach" and "City" and returns the primary key created by and assigned to the primary key column (id in this case). It's assumed that the variable db contains an active Database connection: > > ``` xojo > Var row As New DatabaseRow > > ' ID will be updated automatically > row.Column("Name") = "Penguins" > row.Column("Coach") = "Bob Roberts" > row.Column("City") = "Boston" > > Var primaryKey As Integer > > Try > primaryKey = db.AddRow("Team", row, "id") > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.AffectedRowCount **AffectedRowCount** As `UInt64` > Returns the number of rows affected by the most recently executed `INSERT`, `UPDATE`, or `DELETE` statement. > > Get the number of rows that were changed by an `UPDATE` statement: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > db.ExecuteSQL("UPDATE Team SET City = 'Boston' Where Coach = 'Bob Roberts'") > > Var changeCount As UInt64 > changeCount = db.AffectedRowCount > ```

forsearch

PostgreSQLDatabase.BeginTransaction **BeginTransaction** > Creates a new transaction. Changes to the database made after this call can be saved with `CommitTransaction` or undone with `RollbackTransaction`. > > A `DatabaseException` will be raised if the transaction could not begin or another transaction is already in progress. > > You typically want to Commit changes after ensuring there were no database errors: > > ``` xojo > ' Prior DB code has run > > Try > DB.BeginTransaction > DB.ExecuteSQL("CREATE TABLE AddressBook name VARCHAR, email VARCHAR") > DB.CommitTransaction > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > DB.RollbackTransaction > End Try > ```

forsearch

PostgreSQLDatabase.CheckForNotifications **CheckForNotifications** > Checks to see if there are any waiting notifications. You will receive notifications as a ReceivedNotification event. If you would like to receive notifications automatically, create a `Timer` and use it to call CheckForNotifications repeatedly. > > Notifications are received in the `PostgreSQLDatabase` event handler. > > This example is in the Action event of a `Timer`. > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > db.CheckForNotifications > ```

forsearch

PostgreSQLDatabase.Close **Close** > Closes or disconnects the database. > > Calling Close does not issue a Commit, but some databases will automatically Commit changes in a transaction when you Close the connection and some database will automatically Rollback changes in a transaction when the connection is closed. Refer to the documentation for your database to check what its behavior is. > > For desktop apps, you will often Connect to the database when the app starts and Close it when the app quits. > > For web apps, you usually Connect to the database when the Session starts and Close it when the Session quits. > > This code in the App.Closing event handler closes a previously connected database: > > ``` xojo > DB.Close ' DB is a property on App > ```

forsearch

PostgreSQLDatabase.CommitTransaction **CommitTransaction** > Commits an open transaction. This permanently saves changes to the database. > > A `DatabaseException` will be raised if the transaction could not be committed. > > You have to have an open transaction to be able to use CommitTransation. Use `BeginTransaction` to begin a transaction: > > ``` xojo > DB.BeginTransaction > ``` > > You typically want to commit changes after ensuring there were no database errors: > > ``` xojo > ' Prior DB code has run > > Try > DB.CommitTransaction > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > DB.RollbackTransaction > End Try > ```

forsearch

PostgreSQLDatabase.Connect **Connect**(`Optional` additionalOptions As `String` = "") > Connects to the database server and opens the database for access. > > The *additionalOptions* are passed to PostgreSQL as is and are not escaped in any way. The available options for this parameter are specified here: > > To connect using SSL, use the SSLMode, SSLCertificate and SSLKey properties as appropriate for how your server is configured. > > This code attempts to connect to the "BaseballLeague" database on a PostgreSQL server: > > ``` xojo > Var db As New PostgreSQLDatabase > > db.Host = "192.168.1.172" > db.Port = 5432 > db.DatabaseName = "BaseballLeague" > db.UserName = "broberts" > db.Password = "streborb" > > Try > db.Connect > ' Use the database > Catch error As DatabaseException > ' There was a database connection error > MessageBox(error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.CreateLargeObject **CreateLargeObject** As `Integer` > Creates a `PostgreSQLLargeObject` and returns its ID. Legal IDs are positive integers. Therefore, if CreateLargeObject returns 0 or less, then there was an error creating the large object. Use ErrorMessage to determine what the error was. > > 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: > > ``` xojo > db.ExecuteSQL("BEGIN TRANSACTION") > ``` > > After you have performed your last large object operation, you should close the transaction, like this: > > ``` xojo > db.ExecuteSQL("END TRANSACTION") > ``` > > Please see the `PostgreSQLLargeObject` class for information on how to work with large objects. > > Create a large object: > > ``` xojo > ' db is an existing PostgreSQLDatabase connection > Try > Var objectID As Integer = db.CreateLargeObject > Catch error As DatabaseException > MessageBox("Couldn't create large object: " + db.Message) > Return > End Try > ```

forsearch

PostgreSQLDatabase.DeleteLargeObject **DeleteLargeObject**(oid As `Integer`) > Deletes the large object identified by *oid*. > > 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: > > ``` xojo > db.ExecuteSQL("BEGIN TRANSACTION") > ``` > > After you have performed your last large object operation, you should close the transaction, like this: > > ``` xojo > db.ExecuteSQL("END TRANSACTION") > ``` > > Please see the `PostgreSQLLargeObject` class for information on how to work with large objects. > > Delete a large object: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > ' objectID is a reference to a previously created large object > > Try > db.DeleteLargeObject(objectID) > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.ExecuteSQL **ExecuteSQL**(command As `String`, `ParamArray` values() As `Variant`) > Used to execute an SQL *command*. Use this for commands that do not return any data, such as `CREATE TABLE` or `INSERT`. The *command* parameter contains the SQL statement. > > Passing values as parameters will protect your database by automatically creating a `PostgresqlPreparedStatement`. > >

> >

> > Tip > >

> > You can also pass an `array` of values as the first and only parameter instead of a `ParamArray`. This array **must** be of type `Variant`. > >

> >

> > Warning > >

> > A `DatabaseException` will be raised if the SQL passed is invalid or if an error occurs. > >

> > In this example, the database is being updated without the use of parameters and thus leaves the database vulnerable to a `SQL injection attack`: > > ``` xojo > ' Updates a table in the database (db) > Var sql As String = "UPDATE Customer SET City='" + CityField.Text + "' WHERE PostalCode='" + PostalCodeField.Text + "'" > > Try > db.ExecuteSQL(sql) > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ``` > > Here's the same example but using parameters which protects you against a `SQL injection attack`: > > ``` xojo > ' Updates a table in the database (db) > Var sql As String = "UPDATE Customer SET City=$1 WHERE PostalCode=$2" > > Try > db.ExecuteSQL(sql, CityField.Text, PostalCode.Text) > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ``` > > The parameter values can also be passed in as a variant array: > > ``` xojo > Var sql As String = "UPDATE Customer SET City=$1 WHERE PostalCode=$2" > Var values(1) As Variant > > values(0) = CityField.Text > values(1) = PostalCode.Text > > Try > db.ExecuteSQL(sql, values) > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ``` > > This code creates the *Team* table: > > ``` xojo > ' db is a database > Var sql As String = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));" > > Try > db.ExecuteSQL(sql) > MessageBox("Team table created successfully.") > Catch error As DatabaseException > MessageBox("DB Error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.IsConnected **IsConnected** As `Boolean` > Returns `True` if the connection to the database is still active.

forsearch

PostgreSQLDatabase.Listen **Listen**(Name As `String`) > Listens for notifications named *Name*. > > Used in conjunction with the `Notify` and `CheckForNotifications` methods. Notifications are received in the `ReceivedNotification` event handler. > > Start listening for a specific notification: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > db.Listen("RefreshAll") > ```

forsearch

PostgreSQLDatabase.Notify **Notify**(Name As `String`) > Sends a notification named *Name*. > > Clients that have used the `Listen` method for the *name* will be get the notification. To receive notifications, call the `CheckForNotifications` method. Notifications are received in the `PostgreSQLDatabase` event handler. > > The Notify method does not have a way to send a payload. As a workaround you can call the command directly instead: > > ``` xojo > db.ExecuteSQL("NOTIFY ""UpdateCustomers"", '5, 10, 15'") > ``` > > Send the "RefreshAll" notification: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > db.Notify("RefreshAll") > ```

forsearch

PostgreSQLDatabase.OpenLargeObject **OpenLargeObject**(oid As `Integer`, \[ReadOnly As `Boolean` = `False`\]) As `PostgreSQLLargeObject` > Opens the large object specified by the passed *oid*. The *ReadOnly* parameter is optional and defaults to `False`. If you pass `True`, then the large object will be opened in read-only mode. If OpenLargeObject is successful, it returns an instance of `PostgreSQLLargeObject`. > > 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: > > ``` xojo > db.ExecuteSQL("BEGIN TRANSACTION") > ``` > > After you have performed your last large object operation, you should close the transaction, like this: > > ``` xojo > db.ExecuteSQL("END TRANSACTION") > ``` > > Please see the `PostgreSQLLargeObject` class for information on how to work with large objects. > > Read the data in a large object: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > ' objectID is an integer referring to a previously created large object > Var largeObject As PostgreSQLLargeObject > > largeObject = db.OpenLargeObject(objectID) > > Var data As String = largeObject.Read(largeObject.Length) > ```

forsearch

PostgreSQLDatabase.Prepare **Prepare**(statement As `String`) As `PreparedStatement` > Creates a PreparedStatement using the SQL *statement* for use with the `PostgreSQLPreparedStatement` class. A prepared statement is an SQL statement with parameters that has been pre-processed by the database so that it can be executed more quickly if it is re-used with different parameters. Prepared statements also mitigate the risk of SQL injection in web apps. > > If the provided SQL statement has an error, a `DatabaseException` will occur when you call `SelectSQL` or `ExecuteSQL`. > > [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) is a way to attack database-driven applications. > > To create a prepared statement for PostgreSQL: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase object > > Var ps As PostgreSQLPreparedStatement > ps = db.Prepare("SELECT * FROM Country WHERE code = $1") > ```

forsearch

PostgreSQLDatabase.RollbackTransaction **RollbackTransaction** > Cancels an open transaction restoring the database to the state it was in before the transaction began. > > A `DatabaseException` will be raised if the rollback could not be completed. > > You will generally want to rollback database changes if a `DatabaseException` occurs within the transaction. > >

> >

> > Important > >

> > While rolling back a transaction restores the database to its previous state, it has no impact on the data you access in a `RowSet`. To update the `RowSet` so that its data matches the database, you will need to re-query the database to recreate the `RowSet`. > >

> > You have to have an open transaction to be able to use Rollback. Call `BeginTransaction` to begin a transaction: > > ``` xojo > DB.BeginTransaction > ``` > > This code uses rollback to revert database changes in a transaction when an error occurs: > > ``` xojo > ' Prior DB code has run > > Try > db.CommitTransaction > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > db.RollbackTransaction > End If > ```

forsearch

PostgreSQLDatabase.SelectSQL **SelectSQL**(query As `String`, `ParamArray` values() As `Variant`) As `RowSet` > Executes a SQL `SELECT` statement and returns the results in a `RowSet`. The *query* parameter contains the SQL statement. > > Passing values as parameters will protect your database by automatically creating a `PostgresqlPreparedStatement`. > >

> >

> > Warning > >

> > A `DatabaseException` will be raised if the SQL passed is invalid or if an error occurs. > >

> >

> > Tip > >

> > You can also pass an `array` of values as the first and only parameter instead of a `ParamArray`. This array **must** be of type `Variant`. > >

> > This sample adds the names of all customers in a particular postal code to a ListBox. It passes the entire SQL select as a single statement and appends the value from a `TextField` called PostalCode, leaving the database vulnerable to a `SQL injection attack`. > > ``` xojo > ' db is a valid connection to a database > Var rowsFound As RowSet > > Try > rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Text) > For Each row As DatabaseRow In rowsFound > ListBox1.AddRow(row.Column("Name").StringValue) > Next > rowsFound.Close > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > End Try > ``` > > This is the same code as above but instead it uses a **value identifier (\$1)** and then passes the value in separately to avoid a [SQL injection attack](https://en.wikipedia.org/wiki/SQL_injection): > > ``` xojo > ' db is a valid connection to a database > Var rowsFound As RowSet > > Try > rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=$1", PostalCode.Text) > For Each row As DatabaseRow In rowsFound > ListBox1.AddRow(row.Column("Name").StringValue) > Next > rowsFound.Close > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > End Try > ``` > > Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are both `DesktopTextField` controls: > > ``` xojo > ' db is a valid connection to a database > Var rowsFound As RowSet > > Try > rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=$1 AND PostalCode=$2", Age.Value, PostalCode.Text) > For Each row As DatabaseRow In rowsFound > ListBox1.AddRow(row.Column("Name").StringValue) > Next > rowsFound.Close > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.StopListening **StopListening**(Name As `String`) > Stops listening for notifications named *Name*. > > Tells the client to stop listening for the specified notification. > > Stop listening for the "RefreshAll" notification: > > ``` xojo > ' db is a previously connected PostgreSQLDatabase > db.StopListening("RefreshAll") > ```

forsearch

PostgreSQLDatabase.TableColumns **TableColumns**(tableName As `String`) As `RowSet` > Returns a `RowSet` with information about all the columns (fields) in the specified *tableName*. > >

> >

> > Note > >

> > If an error occurs, a `DatabaseException` will be raised. > >

> > TableColumns returns a `RowSet` with these columns: > > - ColumnName: A string that is the name of the column. > - FieldType: An integer that describes the type of the column (refer to the table below). > - IsPrimary: A boolean that indicates if the column is part of the primary key. > - NotNull: A boolean that indicates if the column can be set to NULL. > - Length: An integer that describes the length of the field (for some text fields), the precision of numeric data (for some numeric fields) or the default value for a field (for SQLite columns). > - cid: (SQLite Only) The ID of the column. > > This table identifies the Column Type based on the **FieldType** `Integer`: > > | Field Type | Value | Description | > |-----------------------|-------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| > | Null | 0 | Denotes the absence of any value, i.e., a missing value. | > | Byte | 1 | Stores the byte representation of a character string. | > | SmallInt | 2 | A numeric data type with no fractional part. The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. SQLite supports 4-byte smallints. If you are using another data source, check the documentation of your data source. | > | Integer | 3 | A numeric data type with no fractional part. The maximum number of digits is implementation-specific. SQLite supports 8-byte integer columns and the FieldType evaluates to 19 (64-bit integer). | > | Char | 4 | Stores alphabetic data, in which you specify the maximum number of characters for the field, i.e., CHAR (20) for a 20 character field. If a record contains fewer than the maximum number of characters for the field, the remaining characters will be padded with blanks. | > | Text or VarChar | 5 | Stores alphabetic data, in which the number of characters vary from record to record, but you don't want to pad the unused characters with blanks. For example, VARCHAR (20) specifies a VARCHAR field with a maximum length of 20 characters. | > | Float | 6 | Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5). | > | Double | 7 | Stores double-precision floating-point numbers. | > | Date | 8 | Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits. | > | Time | 9 | Stores hour, minute, and second values of a time in the format HH:MM:SS. The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero. | > | TimeStamp | 10 | Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component. | > | Currency | 11 | This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right. | > | Boolean | 12 | Stores the values of TRUE or FALSE. | > | Decimal | 13 | Stores a numeric value that can have both an integral and fractional part. You specify the total number of digits and the number of digits to the right of the decimal place, i.e., DECIMAL (5.2) specifies a decimal field that can contain values up to 999.99. DECIMAL (5) specifies a field that can contain values up to 99,999. | > | Binary | 14 | Stores code, images, and hexadecimal data. Consult the documentation of your data source for information on the maximum size of a Binary field. | > | Long Text (Blob) | 15 | Stores a text object. Consult the documentation of your data source for information on the maximum size of a Blob. | > | Long VarBinary (Blob) | 16 | Stores a binary object. SQLite supports blobs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source. | > | MacPICT | 17 | Stores a Macintosh PICT image. SQLite does not support this data type. Use a Blob to store images. | > | String | 18 | Text up to about 2 billion bytes. The same as VarChar. | > | Int64 | 19 | Stores a 64-bit integer. Integer fields in SQLite are 64 bits and FieldType returns 19. | > | Unknown | 255 | Unrecognized data type. | > >

> >

> > Note > >

> > Depending upon the version you are using, only a subset of these types may be supported. > >

> > The following code creates a table and then display each column name one at a time: > > ``` xojo > 'db is a valid connection to a database > Try > db.ExecuteSQL("CREATE TABLE Invoices (ID INTEGER, CustID INTEGER, Amount Double, Note TEXT)") > > Var columns As RowSet = db.TableColumns("Invoices") > > For Each c As DatabaseRow In columns > MessageBox("Column: " + c.Column("ColumnName").StringValue) > Next > Catch error As IOException > MessageBox("The database could not be created: " + error.Message) > Catch error As DatabaseException > MessageBox("Database error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.TableIndexes **TableIndexes**(tableName As `String`) As `RowSet` > Returns a `RowSet` containing the list of indexes for the passed *tableName*. Returns `Nil` if the table has no indexes or the database source does not support indexes. > > A `DatabaseException` will be raised if an error occurs. > > The `RowSet` returns one row for each index on the table and it has one field: IndexName As `String`. > > This code displays the indexes for the "Invoices" table (if it exists) in the specified database: > > ``` xojo > 'db is a valid connection to a database > Try > Var indexRS As RowSet > indexRS = db.TableIndexes("Invoices") ' A table with indexes in the DB > For Each row As DatabaseRow In indexRS > MessageBox("Index: " + row.ColumnAt(0).StringValue) > Next > indexRS.Close > Catch error As NilObjectException > MessageBox("This database has no indexes.") > Catch error As DatabaseException > MessageBox("Could not connect to database. Error: " + error.Message) > End Try > ```

forsearch

PostgreSQLDatabase.Tables **Tables** As `RowSet` > Returns a `RowSet` with a list of all tables in the database. > > A `DatabaseException` will be raised if an error occurs. > > Tables returns a `RowSet` with one field: TableName As `String`. > > The following code gets and displays the table names for the connected database: > > ``` xojo > ' App.db is a connected database > Var tables As RowSet = App.db.Tables > > Try > For Each row As DatabaseRow In tables > MessageBox(row.ColumnAt(0).StringValue) > Next > tables.Close > Catch error As NilObjectException > MessageBox("This database has no tables.") > End Try > ``` ## Event descriptions

forsearch

PostgreSQLDatabase.ReceivedNotification **ReceivedNotification**(Name As `String`, ID As `Integer`, Extra As `String`) > A notification named *Name* has been received. The process ID of the sender is passed in the *ID* parameter. > > Used by the Listen and Notify PostgreSQL feature. > > To receive notifications, use the `Listen` method to specify the name of each notification to listen for and call the `CheckForNotifications` method (usually in a Timer). > > Notifications are sent using the `Notify` method. > > The *Extra* parameter may contain payload information provided by another PostgreSQL system. Xojo does not have a way to provide a payload to a notification. as a workaround you can manually call NOTIFY yourself like this: > > ``` xojo > db.ExecuteSQL("NOTIFY ""UpdateCustomers"", '5, 10, 15'") > ``` > > This code in the ReceivedNotification event handler displays the notification that was received: > > ``` xojo > MessageBox("Received notification: " + sName + " from " + aPid.ToString) > ``` ## 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 `DatabaseColumn` method for a date representation of this data, or use StringValue to get a human-readable date and/or time. Be sure to refer to the [official PostgreSQL documentation](http://www.postgresql.org/docs/). --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### Threading `SelectSQL` and `ExecuteSQL` 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 `The 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. ``` xojo Var 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 `The Xojo Cloud Control Panel` topic 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: ``` xojo db.ExecuteSQL("BEGIN TRANSACTION") ``` After you have performed your last large object operation, you should close the transaction, like this: ``` xojo db.ExecuteSQL("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: ``` xojo ' 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. ## Sample code This code connects to an existing PostgreSQL database. ``` xojo Var 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" Try db.Connect Then ' proceed with database operations Catch error As DatabaseException MessageBox("The connection failed.") End Try ``` ## Compatibility | | | |-----------------------|-----------------------| | **Project Types** | Console, Desktop, Web | | **Operating Systems** | All |

`Database` parent class; `DatabaseRow`, `PostgreSQLLargeObject`, `PostgreSQLPreparedStatement`, `RowSet` classes, [PostgreSQL Web Site](http://www.postgresql.org/)