Class

MSSQLServerDatabase


Description

Used to connect to MS SQL Server databases.

Property descriptions


MSSQLServerDatabase.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":

Var db As New MSSQLServerDatabase
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

MSSQLServerDatabase.Host

Host As String

The database host name or IP address of the database server.

This examples connects to a database called "BaseballLeague":

Var db As New MSSQLServerDatabase
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

MSSQLServerDatabase.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":

Var db As New MSSQLServerDatabase
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

MSSQLServerDatabase.Port

Port As Integer

The port to use to connect to the MS SQL Server database.

// db is a MSSQLServerDatabase
db.Port = 8090

MSSQLServerDatabase.Timeout

Timeout As Integer

The connection timeout value (in seconds). The default is 30.

Sets the timeout to 60 seconds:

// db is a MSSQLServerDatabase
db.Timeout = 60

MSSQLServerDatabase.UserName

UserName As String

The username that is required for access to the database.

This code connects to a database called "BaseballLeague":

Var db As New MSSQLServerDatabase
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


MSSQLServerDatabase.AddRow

AddRow(tableName As String, row As DatabaseRow)

Inserts Data (a populated DatabaseRow) 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:

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

MSSQLServerDatabase.AffectedRowCount

AffectedRowCount As UInt64

Returns the number of rows that were modified by the most recent SQL statement passed to ExecuteSQL.

// db is a previously connected MSSQLServerDatabase
db.ExecuteSQL("UPDATE Team SET City = 'Boston' Where Coach = 'Bob Roberts'")
Var changeCount As UInt64
changeCount = db.AffectedRowCount

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

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

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

DB.Close // DB is a property on App

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

DB.BeginTransaction

You typically want to commit changes after ensuring there were no database errors:

// Prior DB code has run

Try
  DB.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try

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

Try
  db.Connect
  MessageBox("Connected!")
Catch error As DatabaseException
  MessageBox("Error connecting to the database: " + error.Message)
End Try

MSSQLServerDatabase.ExecuteSQL

ExecuteSQL(SQL As String, Optional 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. SQL contains the SQL statement.

Tip

You can also pass an array of values as the first and only parameter instead of a ParamArray.

Warning

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

Passing values as parameters protects your database by automatically creating a MSSQLServerPreparedStatement.

In this example, the database is being updated without the use of parameters and thus leaves the database vulnerable to a SQL injection attack:

// Updates a table in a database (db)
Var sql As String
sql = "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:

// Updates a table in a database (db)
Var sql As String
sql = "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:

Var sql As String
sql = "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:

// db is a database
Var sql As String
sql = "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

MSSQLServerDatabase.Prepare

Prepare(statement As String) As PreparedSQLStatement

Creates a PreparedSQLStatement using the SQL statement for use with the MSSQLServerPreparedStatement 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 is a way to attack database-driven applications.

To create a prepared statement for MS SQL Server:

// db is a previously connected MSSQLServerDatabase object

Var ps As MSSQLServerPreparedStatement
ps = db.Prepare("SELECT * FROM Country WHERE code = $1")

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

You have to have an open transaction to be able to use Rollback. Call BeginTransaction to begin a transaction:

DB.BeginTransaction

This code uses rollback to revert database changes in a transaction when an error occurs:

// Prior DB code has run

Try
  db.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  db.RollbackTransaction
End If

MSSQLServerDatabase.SelectSQL

SelectSQL(SQL As String, Optional ParamArray values() As Variant) As RowSet

Executes a SQL SELECT statement and returns the results in a RowSet. The SQL parameter contains the SQL statement.

Tip

You can also pass an array of values as the first and only parameter instead of a ParamArray.

Warning

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

Passing values as parameters protects your database by automatically creating a MSSQLServerPreparedStatement.

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:

// 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:

// 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:

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

MSSQLServerDatabase.TableColumns

TableColumns(TableName As String) As RowSet

Returns a RowSet with information about all the columns (fields) in the specified TableName.

The following code creates a table and then display each column name one at a time:

'db is a valid MSSSQLServerDatabase 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

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

'db is a valid MSSQLServerDatabase 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

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

// App.db is a connected database
Var tables As RowSet
tables = 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 MSSQServerDatabase plug-in in your plugins folder. The plug-in is included in the installation.

When the plug-in is installed, this class becomes available and you can also added a MS SQL Server database connection directly to the project.

This plug-in is a Windows-only feature. Other platforms will get a PlatformNotSupportedException when any method is called.

In terms of navigating a RowSet, MSSQLServerDatabase only supports MoveToFirstRow.

Multiple result sets are not supported. If your query returns multiple results sets you will only get back the first result set.


Threading

SelectSQL and ExecuteSQL statements do not block when called from within Threads.

Sample code

To connect to a local MS SQL Server database:

Try
  Var db As New MSSQLServerDatabase
  db.Host = "192.168.0.1\InstanceName" // or just the IP if using the default instance
  db.DatabaseName = "NorthWind"
  db.UserName = "MyUserID" // or "Domain\UserID for trusted domain accounts
  db.Password = "mypassword"
  db.Connect
Catch error As DatabaseException
  System.Beep
  MessageBox("The database connection was unsuccessful.")
End Try

Compatibility

All project types on all supported operating systems.

See also

Database parent class; MSSQLServerPreparedStatement, RowSet classes