Class # ODBCDatabase

forsearch

Database --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ## Description Used to open an ODBC database using an ODBC drivers. You use the constants in the `ODBCConstant` module with the methods and properties of the ODBCDatabase class. ## Properties

table-centered_columns_3_and_4

| Name | Type | Read-Only | Shared | |-------------------------------------------------------|------------------------------------|-----------|--------| | `Attribute` | `Integer` | | | | `AttributeString` | `String` | | | | `DatabaseName` | `String` | | | | `DataSource` | `String` | | | | `DBMS` | `String` | ✓ | | | `IsExtendedSchema` | `Boolean` | | | | `IsScrollableCursor` | `Boolean` | | | | `Host` | `String` | | | | `MetaData` | `String` | | | | `Password` | `String` | | | | `Tag` | `Variant` | | | | `Timeout` | `Integer` | | | | `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` | | | `BeginTransaction` | | | | | `Close` | | | | | `CommitTransaction` | | | | | `Connect` | | | | | `ConnectionAttribute` | Attribute As `Integer` | `Variant` | | | `ConnectionInfo` | Attribute As `Integer` | `Variant` | | | `DataSources` | | `RowSet` | | | `DataTypeInfo` | DataType As `Integer` | `RowSet` | | | `Drivers` | | `RowSet` | | | `ExecuteSQL` | command As `String`, `ParamArray` values() As `Variant` | | | | `IsConnected` | | `Boolean` | | | `NextRowSet` | | `Boolean` | | | `Prepare` | statement As `String` | `ODBCPreparedSQLStatement` | | | `Privileges` | table As `String` | `RowSet` | | | | table As `String`, column As `String` | `RowSet` | | | `RollbackTransaction` | | | | | `SelectSQL` | query As `String`, `Paramarray` values() As `Variant` | `RowSet` | | | `StoredProcedureColumns` | ProcedureName As `String` | `RowSet` | | | `StoredProcedures` | | `RowSet` | | | `TableColumns` | tableName As `String` | `RowSet` | | | `TableIndexes` | tableName As `String` | `RowSet` | | | `TableForeignKeys` | primaryTable As `String`, foreignTable As `String` | `RowSet` | | | `TablePrimaryKeys` | table As `String` | `RowSet` | | | `TableSpecialColumns` | identifierType As `Integer`, table As `String`, scope As `Integer`, nullable As `Integer` | `RowSet` | | | `Tables` | | `RowSet` | | ## Property descriptions

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

forsearch

ODBCDatabase.Attribute **Attribute** As `Integer` > Optional attribute. This property is used in conjunction with the `AttributeString` property to set a string property using the SQLSetConnectAttr ODBC API function. > > Using the Attribute property, the attribute is only set before the connection is made to the database. Although the Attribute / AttributeString properties are readable, they are not updated with information from the database. > > The AttributeString property is not passed to the database if the Attribute property is set to zero (the default). > > For more information, see the documentation for the SQLSetConnectAttr function at msdn.microsoft.com. > > If you don't want to use *Attribute*, pass zero. > > The following code sets the `SQL_CURRENT_QUALIFIER` property: > > ``` xojo > db.Attribute = ODBCConstant.SQL_CURRENT_QUALIFIER > db.AttributeString = "NorthWind" > ```

forsearch

ODBCDatabase.AttributeString **AttributeString** As `String` > Optional attribute. This property is used in conjunction with the `Attribute` property to set a string property using the SQLSetConnectAttr ODBC API function. > > Using the Attribute property, the attribute is only set before the connection is made to the database. Although the Attribute / AttributeString properties are readable, they are not updated with information from the database. > > The AttributeString property is not passed to the database if the Attribute property is set to zero (the default). > > For more information, see the documentation for the SQLSetConnectAttr function at msdn.microsoft.com. > > If you don't want to pass *AttributeString*, pass the empty string. > > The following code sets the `SQL_CURRENT_QUALIFIER` property: > > ``` xojo > db.Attribute = ODBCConstant.SQL_CURRENT_QUALIFIER > db.AttributeString = "NorthWind" > ```

forsearch

ODBCDatabase.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 database called "BaseballLeague": > > ``` xojo > Var db As New ODBCDatabase > > db.Host = "192.168.1.172" > 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

ODBCDatabase.DataSource **DataSource** As `String` > The connection string to be used to establish a connection to an ODBC database. The connection string can either contain only the DSN name, or a string containing one or more keywords defined for the driver. > > If only the DSN is passed, then the UserName and Password properties are used for the connection. If the connection string contains keywords, then the UserName and Password properties are ignored. See your driver documentation for information about which keywords are supported. > > Pass an empty string ("") to the DataSource property to get an open-file dialog box. It will prompt you to choose a list of defined DSNs. The DataSource property may be updated by the ODBC driver with additional parameters used after a connection is made. > > For examples of connection strings for various databases, visit . > > This example prompts the user to select a Data Source: > > ``` xojo > Var db As New ODBCDatabase > > db.DataSource = "" > > Try > db.Connect > ' proceed with database operations > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > End Try > ``` > > This example specifies the DSN information to connect to a Microsoft Access database: > > ``` xojo > Var db As New ODBCDatabase > > db.DataSource = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\TestAccessDB.mdb;Uid=Admin;Pwd=;" > > Try > db.Connect > ' proceed with database operations > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > End Try > ```

forsearch

ODBCDatabase.DBMS **DBMS** As `String` > This property returns the type of the connected database, such as MySQL or Oracle. The property only contains a value after a successful connection has been made to the database. Writing to the property has no effect. > > This property is read-only. > > This property is equivalent to calling the GetInfo method with the `ODBCConstant`.SQL_DBMS_NAME. > > After connecting to a database with ODBC, you can display its type: > > ``` xojo > MessageBox("DB Type: " + db.DBMS) > ```

forsearch

ODBCDatabase.IsExtendedSchema **IsExtendedSchema** As `Boolean` > When set to `True`, additional columns are returned for FieldSchema and TableSchema. > > Additional columns are described below > > For FieldSchema: > > - TableName > - NonUnique > - IndexQualifier > - Type > - OrdinalPosition > - ColumnName > - TableCatalog > - TableSchema > - AscOrDesc > - Cardinality > - Pages > - FilterCondition > > For TableSchema: > > - TableType > - Remarks > - TableCatalog > - TableSchema

forsearch

ODBCDatabase.IsScrollableCursor **IsScrollableCursor** As `Boolean` > This property can be set to `True` in order to cause cursors opened using SQLSelect to be “scrollable.” A `RowSet` with a scrollable cursor can be navigated in both the forward and backward directions (i.e., using MoveToPreviousRow in addition to MoveToNextRow). The default behavior is for a cursor to be non-scrollable (forward only). This property only has an effect if supported by the underlying ODBC driver.

forsearch

ODBCDatabase.Host **Host** As `String` > The database host name or IP address of the database server. > > This examples connects to a database called "BaseballLeague": > > ``` xojo > Var db As New ODBCDatabase > > db.Host = "192.168.1.172" > 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

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

forsearch

ODBCDatabase.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 database called "BaseballLeague": > > ``` xojo > Var db As New ODBCDatabase > > db.Host = "192.168.1.172" > 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

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

forsearch

ODBCDatabase.Timeout **Timeout** As `Integer` > The timeout value (in seconds) to be used when connecting to the database. The default is 0, which will use the ODBC driver value. > > The timeout value is set before actually connecting to the database. The property can be read, but the value that was previously set will be returned. The value is not returned from the driver. To get the actual value from the driver, use the `ConnectionAttribute` method with the `ODBCConstant`.SQL_LOGIN_TIMEOUT parameter. > > Set the timeout to 60 seconds: > > ``` xojo > ' db is an existing ODBCDatabase connection > db.Timeout = 60 > ```

forsearch

ODBCDatabase.UserName **UserName** As `String` > The username that is required for access to the database. > > This code connects to a database called "BaseballLeague": > > ``` xojo > Var db As New ODBCDatabase > > db.Host = "192.168.1.172" > 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

odbcDatabase.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

ODBCDatabase.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

ODBCDatabase.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

ODBCDatabase.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

ODBCDatabase.Connect **Connect** > Connects to the database so that you can begin using it. > >

> >

> > Warning > >

> > A `DatabaseException` will be raised if the connection could not be made. > >

> > This example connects to the database object in the variable db: > > ``` xojo > Try > db.Connect > MessageBox("Connected!") > Catch error As DatabaseException > MessageBox("Error connecting to the database: " + error.Message) > End Try > ```

forsearch

ODBCDatabase.ConnectionAttribute **ConnectionAttribute**(Attribute As `Integer`) As `Variant` > Gets and sets the current setting of a connection attribute. Returns a `Variant` that contains either a `String` or an `Integer`, based on the attribute. > > The attribute can be specified using the `ODBCConstant` constants. The value is only set after the connection is made. Only `Integer` attributes may be set with this method. To set a `String` attribute before the connection is made, see the Attribute property. > > Microsoft provides additional information about the [SQLGetConnectAttr ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms710297(v=vs.85).aspx). > > Obtaining an attribute: > > ``` xojo > Var currentCatalog As Variant > > currentCatalog = db.ConnectionAttribute(ODBCConstant.SQL_CURRENT_QUALIFIER) > ``` > > Setting an attribute to disable AutoCommit: > > ``` xojo > db.ConnectionAttribute(ODBCConstant.SQL_AUTOCOMMIT) = ODBCConstant.SQL_AUTOCOMMIT_OFF > ```

forsearch

ODBCDatabase.ConnectionInfo **ConnectionInfo**(Attribute As `Integer`) As `Variant` > Returns general information about the driver and data source associated with a connection. > > Returns a `Variant` that contains either a `String` or an `Integer`, based on the attribute. > > `ODBCConstant` contains integer values that you can use with this function. > > Microsoft provides additional information about the [SQLGetInfo ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms711681(v=vs.85).aspx). > > Check if the connected ODBC database supports stored procedures: > > ``` xojo > 'db is a valid database connection > Try > db.Connect > MessageBox(db.ConnectionInfo(ODBCConstant.SQL_PROCEDURES)) > Catch error As DatabaseException > MessageBox("Error: " + error.Message) > End Try > ```

forsearch

ODBCDatabase.DataSources **DataSources** As `RowSet` > Returns a `RowSet` with a list of configured Data Source Names (DSN). Returns both User and System DSNs in a single `RowSet`. > > The instance of the ODBCDatabase does not have to be connected to a database in order to use this method. > > The `RowSet` returned by DataSources has two columns. They are: > > - **Name:** The data source name > - **Description:** The Name of the ODBC driver. > > Microsoft provides additional information about the [SQLDataSources ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms711004(v=vs.85).aspx). > > This code gets all the ODBC data source names and adds them to a ListBox: > > ``` xojo > DataSourceList.DeleteAllRows > > Var rs As RowSet = db.DataSources > > If rs <> Nil Then > While Not rs.AfterLastRow > DataSourceList.AddRow(rs.Column("Name").StringValue, rs.Column("Description").StringValue) > rs.MoveToNextRow > Wend > rs.Close > End If > ```

forsearch

ODBCDatabase.DataTypeInfo **DataTypeInfo**(DataType As `Integer`) As `RowSet` > Returns a `RowSet` containing information about data types supported by the data source. > > The *DataType* specifies the type for which the information should be returned. > > Use `ODBCConstant` values for the data types. > > To return a `RowSet` containing all defined types, specify `ODBCConstant`.SQL_ALL_TYPES. > > The returned RowSet typically contains these columns, but they could vary depending on the driver being used: > > - Data Type Name > - Data Type integer value > - Column size > > Microsoft provides additional information about the [SQLGetTypeInfo ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms714632(v=vs.85).aspx). > > Displays all the supported data types for the current ODBC connection in a ListBox: > > ``` xojo > ' Var db is a valid database connection > db.DataSource = "TeamExample" > > Try > db.Connect > > Var rs As RowSet > rs = db.DataTypeInfo(ODBCConstant.SQL_ALL_TYPES) > > If rs <> Nil Then > For Each row As DatabaseRow In rs > ListBox1.AddRow(rs.ColumnAt(0).StringValue) > Next > End If > Catch error As DatabaseException > MessageBox("Connection failed. Error:" + error.Message) > End Try > ```

forsearch

ODBCDatabase.Drivers **Drivers** As `RowSet` > Returns a `RowSet` with a list of configured ODBC drivers. > > The instance of the ODBCDatabase does not have to be connected to a database in order to use this method. > > The `RowSet` returned by DataSources has two columns. They are: > > - **Name:** The driver name > - **Description:** A description indicating if the driver is installed > - **Type:** The type (for example, System). > > Microsoft provides additional information about the [SQLDrivers ODBC function](https://msdn.microsoft.com/en-us/library/windows/desktop/ms712400(v=vs.85).aspx).

forsearch

ODBCDatabase.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 protects your database by automatically creating a `ODBCPreparedStatement`. > >

> >

> > 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 a 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 a database (db) > Var sql As String = "UPDATE Customer SET City=? WHERE PostalCode=?" > > 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=? WHERE PostalCode=?" > 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

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

forsearch

ODBCDatabase.NextRowSet **NextRowSet** As `Boolean` > Determines whether more results are available on a statement containing `SELECT`, `UPDATE`, `INSERT`, or `DELETE` statements and, if so, initializes processing for those results. This method only affects the most recently opened `RowSet`. Not all drivers support multiple RowSets. Returns `True` if another `RowSet` is available. You must execute MoveToNextRow on the previous `RowSet` to advance to the start of the next `RowSet` after calling NextRowSet. > > For a command that can return multiple record sets from the ODBC database and driver, you iterate though each of them using the `RowSet` class. When you reach the end of a record set, use MoveToNextRow to initialize the next ODBC record set for processing by the `RowSet` class. > > ``` xojo > ' db is a connected database > ' rs contains multiple RowSets from the ODBC database > > ' First RowSet > While Not rs.AfterLastRow > ' Process data in 1st record set > rs.MoveToNextRow > Wend > > If db.NextRowSet Then ' Initialize next ODBC record set (if one is available) > While Not rs.AfterLastRow > ' Process data in 2nd record set > rs.MoveToNextRow > Wend > End If > ```

forsearch

ODBCDatabase.Prepare **Prepare**(statement As `String`) As `PreparedSQLStatement` > Creates a PreparedSQLStatement using the SQL *statement* for use with the `ODBCPreparedStatement` 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, you use the appropriate class for the database you are using. For example, to create a prepared statement for an ODBC database: > > ``` xojo > ' db is a previously connected ODBCDatabase object > Var ps As ODBCPreparedStatement > > ps = db.Prepare("SELECT * FROM Country WHERE code = $1") > ```

forsearch

ODBCDatabase.Privileges **Privileges**(table As `String`, ColumnName As `String`) As `RowSet` > Returns a `RowSet` with a list of columns and associated privileges for the specified *table*. **Privileges**(table As `String`, column As `String` ) As `RowSet` > Returns a `RowSet` with a list of associated privileges for the specified *column*. > > If the database supports multiple catalogs and schemas, then the `RowSet` will contain records for all catalogs and schemas that can be accessed by the logged in user that match the given table name. > > The *column* is a search pattern for a column name of the given table. Use an empty string to match all columns in the table. > > Microsoft provides [additional information](http://msdn.microsoft.com/en-us/library/windows/desktop/ms716336(v=vs.85).aspx) regarding ODBC column privileges.

forsearch

ODBCDatabase.StoredProcedureColumns **StoredProcedureColumns**(ProcedureName As `String`) As `RowSet` > Returns a `RowSet` containing a list of columns associated with a given procedure stored in the database. > > Not supported by all ODBC drivers. > > Microsoft provides additional information about the [SQLProcedureColumns ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms711701(v=vs.85).aspx).

forsearch

ODBCDatabase.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

ODBCDatabase.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 protects your database by automatically creating a `ODBCPreparedStatement`. > >

> >

> > 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. > >

> > 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 (?)** 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=?", 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=? AND PostalCode=?", 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

ODBCDatabase.StoredProcedures **StoredProcedures** As `RowSet` > Returns a `RowSet` containing the names of the stored procedures in the database.

forsearch

ODBCDatabase.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. > > `ODBC Databases` may return different columns depending upon the database to which you are connecting. > > 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 database connection' > 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

ODBCDatabase.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 database connection > > Try > db.Connect > 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

ODBCDatabase.TableForeignKeys **TableForeignKeys**(primaryTable As `String`, foreignTable As `String`) As `RowSet` > Returns a `RowSet` containing either a list of foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables), or a list of foreign keys in other tables that refer to the primary key in the specified table. > > Not all ODBC drivers support this feature. If it is not supported, it will return `Nil`. > > Microsoft provides additional information about the [SQLForeignKeys ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms709315(v=vs.85).aspx).

forsearch

ODBCDatabase.TablePrimaryKeys **TablePrimaryKeys**(table As `String`) As `RowSet` > Returns a `RowSet` containing the column names that make up the primary key for a *table*. > > Displays the primary keys for a table: > > ``` xojo > Var db As New ODBCDatabase > > db.DataSource = "TeamExample" > > Try > db.Connect > > Var rs As RowSet = db.TablePrimaryKeys("Team") > > If rs <> Nil Then > While Not rs.AfterLastRow > ListBox1.AddRow(rs.ColumnAt(1).StringValue) > rs.MoveToNextRow > Wend > rs.Close > End If > Catch error As DatabaseExcecption > MessageBox("Error: " + error.Message) > End Try > ```

forsearch

ODBCDatabase.TableSpecialColumns **TableSpecialColumns**(identifierType As `Integer`, table As `String`, scope As `Integer`, nullable As `Integer`) As `RowSet` > Retrieves the optimal set of columns that uniquely identifies a row in the table and columns that are automatically updated when any value in the row is updated by a transaction. Returns a `RowSet`. > > The *identifierType* must be either `ODBCConstant`.SQL_BEST_ROWID or `ODBCConstant`.SQL_ROWVER, indicating whether to return the optimal list of columns to uniquely identify a row or to return the columns that are automatically updated by a transaction. > > *scope* is the minimum required scope of the row ID, which must be either `ODBCConstant`.SQL_SCOPE_CURROW, `ODBCConstant`.SQL_SCOPE_TRANSACTION, or `ODBCConstant`.SQL_SCOPE_SESSION. > > *nullable* indicates whether to return columns that can have a NULL value, which must be either `ODBCConstant`.SQL_NO_NULLS or `ODBCConstant`.SQL_SQL_NULLABLE. > > Microsoft provides additional information about the [SQLSpecialColumns ODBC function](http://msdn.microsoft.com/en-us/library/windows/desktop/ms714602(v=vs.85).aspx).

forsearch

ODBCDatabase.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 > ``` ## Notes In order to use this class, you must have the ODBCDatabase plug-in in your plugins folder. The ODBCDatabase class also requires a driver manager and driver. On the Windows platform, the driver manager is supplied by Microsoft as part of Windows or as part of the MDAC package. For non-Windows platforms, the iODBC driver manager is required, which is available from or is installed with most commercial drivers. ODBC drivers may be included with the OS, or may be obtained from the database vendor or a commercial developer of ODBC drivers. Non-Windows drivers must be compatible with the iODBC standard. For macOS, [Actual Technologies](http://www.actualtech.com) supplies iODBC compliant drivers that work well with Xojo apps. There are other [vendors](http://www.sqlsummit.com/odbcvend.htm) as well. The driver must match the architecture of the app. So a 64-bit app must use a 64-bit ODBC driver and a 32-bit app must use a 32-bit ODBC driver. The ODBCDatabase engine supports all four `RowSet` navigation methods: MoveToFirstRow, MoveToNextRow, MoveToPreviousRow, and MoveToLastRow. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### PostgreSQL notes On macOS, when connecting to PostgreSQL using ODBC you must specify a username and password. If you do not, the ODBC driver will crash your app. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### Threading `SelectSQL` and `ExecuteSQL` statements do not block when called from within `Threads`. ## Sample code This code prompts the user to select a Data Source from either the User or System data sources: ``` xojo Var db As New ODBCDatabase db.DataSource = "" Try db.Connect ' proceed with database operations Catch error As DatabaseExcecption MessageBox("Error: " + error.Message) End Try ``` --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- This code specifies a specific data source to use: ``` xojo Var db As New ODBCDatabase db.DataSource = "TestAccessDB" Try db.Connect ' proceed with database operations Catch error As DatabaseExcecption MessageBox("Error: " + error.Message) End Try ``` --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- This code specifies the DSN information to connect to a Microsoft Access database: ``` xojo Var db As New ODBCDatabase db.DataSource = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\TestAccessDB.mdb;Uid=Admin;Pwd=;" Try db.Connect ' proceed with database operations Catch error As DatabaseExcecption MessageBox("Error: " + error.Message) End Try ``` ## Compatibility | | | |-----------------------|-----------------------| | **Project Types** | Console, Desktop, Web | | **Operating Systems** | All |

`Database` parent class; `DatabaseColumn`, `DatabaseRow`, `ODBCPreparedStatement`, `RowSet` classes.