Allow Early Termination + Early Terminate Optional Fields

I’ve just run a test on how the combination of “Allow Early Termination” and “Early Terminate Optional Fields” affects parsing positional flat files. The reason for this is that I’m currently working on a BizTalk integration system which requires accepting all kinds of not-so-nicely-formatted flat files.

In order to simplify things let us assume the following format of the positional flat file:
AAABBBCCC;
where records are delimited with “;” and each record is supposed to have 3 fields 3 characters long. Second and third fields are optional.

Here are the test results (all of the instance files included in the test are required to be accepted by the schema):

	Allow Early Termination + Early Terminate Optional Fields	Yes + Yes	Yes + No	No + N/A
	Flat File Instance:
1	AAABBBCCC;	PARSED	PARSED	PARSED
2	AAABBBC;	PARSED	PARSED	FAIL
3	AAABBB;	PARSED	PARSED	PARSED
4	AAAB;	PARSED	FAIL	FAIL
5	AAA;	PARSED	PARSED	PARSED
6	AAABBBCCCDDD;	FAIL	PARSED	FAIL

Note: The value of “Early Terminate Optional Fields” is only taken into account when “Allow Early Termination” is set to “Yes”.

So, the setting of “Yes + Yes” looks the best for my requirements with the exception of case 6 when there is rubbish after the last record. In most of the real world scenarios this case need to be failed anyway because it simply does not comply with the record format. But in my case the system occasionally received otherwise valid files with a space at the end of each record (i.e. “DDD” = ” ” in my case). To solve this particular problem I have increased the Positional Length property of the last field from 3 characters to 6, effectively transforming case 6 into case 1 in my initial test.

DevExpress GridControl Data Binding

When binding a datagrid (GridControl) to a table do the following:

1. Create a typed dataset class by calling something like    myDs.WriteXmlSchema(“D:\MyDataset.xsd”) in a web service web method (assuming the DAL is done on the web service). It will generate and save the schema into the file. Copy the file into the client application project.
2. Create a dataset component by dragging the created dataset onto the form. This will create global variable of type MyDataset (say, m_MyDataset).
3. Set your GridControl’s DataSource property to m_MyDataset.MY_TABLE_1 and leave DataMember property empty (as opposed to setting DataSource to m_MyDataset and DataMember to MY_TABLE_1).
   NOTE: it is not possible to do this step in VS designer. You will need to manually modify MyForm.Designer.vb file.
4. If you have another grid control which should display child records, set its DataSource property to the same value (m_MyDataset.MY_TABLE_1) but also specify the relation name in the DataMember property.

WARNING: GetChildRows() method of DataRow class unexpectedly ignores trailing spaces in columns which participate in the relation. The logic behind GridControls does not ignore the spaces. This behaviour may result in situation when GetChildRows() returns an array of child rows which is larger than the number of rows displayed in the child GridControl.

Calling Oracle Associative Arrays from .NET – Passing empty array

Oracle may throw binding exceptions if the array to be passed is empty. Moreover, an empty array cannot be substituted by Nothing value as Nothing value of Oracle Parameter means the parameter value was not set (all input parameters values must be set before the command execution). DBNull value (which is equivalent of Oracle Nothing value) cannot be converted to an associative array.

When it is required to pass an empty array an array containing single null value can be used. That allows correct binding of the parameter by Oracle Provider and passing an empty array to stored procedure:

.NET caller:

'--------- INPUT parameter -------
Dim paramInput As New OracleParameter("thearray", OracleDbType.Varchar2, ParameterDirection.Input)
'1. set CollectionType property
paramInput.CollectionType = OracleCollectionType.PLSQLAssociativeArray
'2. set Value property
paramInput.Value = New String() {Nothing}

‘——- OUTPUT parameter ——–

Dim paramOutput As New OracleParameter(“results”, OracleDbType.Varchar2, ParameterDirection.Output)
‘1. set CollectionType property
paramOutput.CollectionType = OracleCollectionType.PLSQLAssociativeArray
‘2. set Size property
paramOutput.Size = 3
‘3. set ArrayBindSize property
paramOutput.ArrayBindSize = New Integer() {100, 100, 100}> lCmd.Parameters.Clear()
lCmd.Parameters.Add(paramInput)
lCmd.Parameters.Add(paramAgentIds)

lCnn.Open()
lCmd.ExecuteNonQuery()
lCnn.Close()

Dim result() As OracleString = CType(paramOutput.Value, OracleString())

PL/SQL stored procedure:

create or replace
package A_mypack5 as
PROCEDURE test_it(
thearray IN INS_PKG_GLB.strings, -- it is an array of strings indexed by PLS_INTEGER
results OUT NOCOPY
END;

create or replace
PACKAGE BODY A_MYPACK5 AS
PROCEDURE test_it(
thearray IN INS_PKG_GLB.strings,
results OUT NOCOPY INS_PKG_GLB.strings) IS
begin
results(1) := ‘array has ‘ || thearray.count || ‘ elements’;
results(2) := ‘second’;
results(3) := ‘third’;
END;
END;

* This source code was highlighted with Source Code Highlighter.