NOOB Using a Command to set column values on INSERT

Data Abstract
1

Hello,

I’ve been using RemObjects SDK for a long time now and I’m finally dipping my toes into Data Abstract / Relativity server. The latest version of Data Abstract is being used in Visual Studio 2013 and C#.

This particular project is an “Inspection” table with an integer “InspectionID” column. The inspection ID is not an auto increment / identity type of field because it is customer specific.

When an inspection is being inserted into the database I’d like to generate the InspectionID using a command with the logic shown below as a SQL Statement.

SELECT MAX([InspectionID]) + 1 FROM [Inspection] WHERE [CustID]=:CustID

Here is an part of the INSERT command showing only the necessary fields for this example.

INSERT INTO 
[Inspections] (
[custID], 
[inspectionID],
    ...) 
VALUES (
:custID, 
:inspectionID,
    ...)
  1. How do I return a value from a command?
  2. How do I modify the INSERT command to retrieve the value using the command?
2

Hello

Before we’ll go deep into the implementation I have to say tht this approach has a very dangerous flaw:

Eventually this SQL code will return same value for a customer and you’ll have a Primary Key validation error(s)

SELECT MAX([InspectionID]) + 1 FROM [Inspection] WHERE [CustID]=:CustID

This can happen once you’ll run more than 1 transaction simultaneously for the same customer.

Schema Commands can return values via outpur parameters.

F.e. it is possible to define an update command in the Schema, point this command to a Stored Procedure and do all needed calcualtions using T-SQL

Regards

3

Given you plan to use the Relativity server you cannot use the BusinessProcessor approach as in this topic: Can I get the record after Linq InsertRow() using C#

You’ll need to use the Scripting to achieve the similar result (ie server-calculated PrimaryKey field value).

F.e. let’s assume I have a Customers table (it can be found in the SQLite database C:\Users\Public\Documents\RemObjects Samples\Database\PCTrade.sqlite.db)
I need to have its ID field to be calculated on the server side.

THe 1st step is to enable the Server Autorefresh option for this field in the Schema Modeler:

Then go to the Scripts node for this table and define the following script:

// Called before each change is applied to the database
function beforeProcessDeltaChange(delta, change, wasRefreshed, canRemove)
{
	if (!change.isInsert) {
	  return;
	}
	
	// Query the database for a new ID value
	// Remember - this is DA SQL, not a real SQL
	var sql = 'SELECT COUNT(*) as newId FROM customers WHERE Name <> :pCustomerName';
	var params = { pCustomerName : 'Some Value' };
	var selectResult = lda.selectSQL(sql, params);
	var row = selectResult.nextRow();
	var newId = row['newId'] + 1;
	
	change.newValues['Id'] = 'CALC' + newId;
}

In this script a parametrized DA SQL is executed against the database and then the value returned by this script is used as a part of the primary key otf the record being inserted. Exactly the thing you need.

I can provide a full sample app if needed. If you want then you can give me the DDL of your table so the sample will be closer to exactly your case.