This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases. The example will use a simple Dodeca connection to a JDBC database. Here is the Dodeca SQL Connection object used for the connection.
CREATE TABLE [dbo].[Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestCode] [nvarchar](50) NULL,
[TestName] [nvarchar](50) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
([TestID] ASC)
)
First, I used the Dodeca SQL Excel View Wizard to create a simple view in Dodeca to retrieve the data into a spreadsheet. The view, before setting up writeback capabilities, looks like this.
To make this view writeable, follow these steps.
- Add the appropriate SQL insert, update, or delete statements to the Dodeca SQL Passthrough Dataset object. The values to be replaced in the SQL statement must be specified using the notation @ColumnName where ColumnName is the column name, or column alias, of the column containing the data.
- Add the column names of the primary key for the table to the PrimaryKey property of the SQL Passthrough DataSet object.
- Depending on the database used, define the column names and their respective JDBC datatypes in the Columns property of the SQL Passthrough Dataset. This mapping is optional for SQL Server because Dodeca can obtain the required information from the Microsoft JDBC driver, however, the Oracle and DB2 JDBC drivers do not provide this information and it must be entered by the developer.
For insert, update, and delete operations, Dodeca parses the SQL statement to read the parameters that use the @ indicator and creates a JDBC prepared statement to execute the statements. The prepared statement format is very efficient as it compiles the SQL statement once and then executes it multiple times. Each inserted row is also passed to the server during the transaction. The values from each row are then used in conjunction with the prepared statement to perform the operation.
Here is the completed Query definition.
Here is the completed Query definition.
Next, modify the DataSetRanges property of the Dodeca View object and, to enable insert operations, set the AllowAddRow property to True. Note that if you added update and/or delete SQL to your SQL Passthrough Dataset object, be sure to enable those operations on the worksheet via the AllowDeleteRow and AllowModifyRow properties.
The insert, update, and delete functionalities using plain SQL statements is limited to operations on a single table. If you need to do updates on multiple tables, you must use stored procedures to accomplish the functionality. You can call a stored procedure in Dodeca using syntax similar to the following example:
{call sp_InsertTest(@TestCode, @TestName)}
Dodeca customers can contact support for further information at support@appliedolap.com.
Posts
