MSSQL data source functionality

Record identification for MSSQL

To support modification or deletion of records, this data source inspects the table schema for a primary key that can be used to uniquely identify each record. If that is not available, the table essentially becomes read-only. Attempting to refresh/reload a record after it was initially loaded, or saving changes after modifying or deleting, will fail with errors. Also, inserting new records will fail with errors.

Optimistic locking for MSSQL

When optimistic locking is operating, there will be a time stamp on each record to indicate the last time it was modified. When saving changes to a record, the time stamp on the record in the database is compared to the time stamp from the last time the record was fetched. If they are different, an error is raised. This avoids inadvertently overwriting changes that have occurred since the record was fetched.

Microsoft SQL Server does not provide a generic time stamp for all records. Therefore, optimistic locking is not provided for collections coming from this data source unless a time stamp column is configured.

If the data source is paired with a Mongoose data source, then the RecordDate column will automatically be used. Otherwise, you must explicitly declare which column can be used as the time stamp to get the optimistic locking functionality.

Triggers in the data base must maintain this column so that it is updated every time the record is changed.

For example, a trigger may look like:

CREATE TRIGGER [dbo].[tableAfterUpdate]
ON [dbo].[table]
AFTER Update
AS UPDATE table SET time_stamp = GETDATE() FROM inserted WHERE...

To specify which column to use as the time stamp, functionality has been added to the Mapping field in the IPF Collection Definitions list.

A new attribute named TimeStampColumn has been added. For example:

<Mapping TimeStampColumn="column_name" />

Supported statements for MSSQL

You can load, insert, modify and delete data from tables as long as they have a primary key. Optimistic locking will be available as long as a TimeStampColumn is available.

Data can be loaded from views. However, because they do not have a primary index, inserts, updates and deletes do not work.

You can execute stored procedures and scalar functions with InvokeMethod and get the return value and output parameters

You can execute stored procedures that return a single result set and table functions using InvokeTableMethod and get the return value and output parameters. Stored procedures that return more than one result set will work, but result sets after the first are ignored.

In the case of table functions, there is not a scalar return value separate from the resulting table itself. The scalar return value from InvokeTableMethod will be null.

Transactions for MSSQL

All data handling and method invocations are performed within a transaction. If an error occurs, the transaction will be rolled back.

If the data source is acting as a Mongoose database in a paired configuration, methods that return an integer of 5 or greater will be rolled back to be consistent with expected transaction handling for Mongoose.

Otherwise, you must use RAISERROR or THROW or otherwise have a runtime error to cause the transaction to rollback.

RAISERROR with a severity 11 or higher will result in a rollback.

BLOB handling for MSSQL

SQL supports two large object data types, varbinary(max) and varchar(max). These appear as properties with a type of IPFDocumentI. (This is the same data type that you get from the Mongoose or MSSQL data source for images and files).

You can fetch and use these columns like any other column.However, for performance, they are not actually loaded into memory when fetched. Instead, they are streamed from the database only when their data is actually called for.

For example, if a table contains a set of videos, they will be streamed directly from the database to the video player in the browser without ever being loaded into memory. This avoids out-of-memory situations that can come from staging large data in memory before use.

The IPFImage formatter has been enhanced to take an IPFDocumentI value. If this formatter is placed on a component with an IPFDocumentI value, it will be assumed to be an image and attempt to display it in the browser. The formatter takes two optional parameters, Width and Height, that work the same as the SLImage formatter.