Executing a repetition column

This example inserts 123 and 456 in the first column of the ex table:

Although the sample program is coded in Visual C# .NET, the program in Visual Basic.NET would be almost the same (if necessary, change information as appropriate).

 
    // Create objects such as a connection object
    HiRDBConnection    pConn = new HiRDBConnection("connection-character-string");
    HiRDBCommand    pCom = pConn.CreateCommand();
 
    // Connect to the database
    pConn.Open();
 
    // Create a table
    pCom.Connection = pConn;
    pCom.CommandText = "create table ex(a int array[3])";
    pCom.ExecuteNonQuery();
 
    // Create a parameter object
    HiRDBParameter    pPar = pCom.CreateParameter();
 
    // Set parameters
    pPar.Direction = ParameterDirection.Input;
    pPar.HiRDBType = HiRDBType.Integer;
    object [] aValue = new object[2];
    aValue[0] = 123;
    aValue[1] = 456;
    pPar.Value = aValue;
    // Set the maximum number of elements for column a of table ex
    pPar.Repetition =3;
    pCom.Parameters.Add(pPar);  ........................................1
 
    // Use parameters to execute SQL statement
    pCom.CommandText = "insert into ex values(?)";
    pCom.ExecuteNonQuery();
 
    // Execute the select statement
    pCom.CommandText = "select * from ex";
    HiRDBDataReader pReader = pCom.ExecuteReader();
 
    // Fetch until there is no more data
    while (pReader.Read())
    {
        for (int i = 0; i < pReader.FieldCount; ++ i)
            for (int j = 0; j < pReader.GetFieldArrayCount(i); ++ j)
                Console.WriteLine(pReader.GetValue(i, j));
    }  .................................................................2
 
    // Disconnect from the database
    pConn.Close();

Explanation

The object array is set in value for the same reason as for the INSERT statement using an array. For a repetition column, also set the Repetition extended property. This property specifies the number of repetition columns. There is no argument during the execution of the SQL statement.
For FETCH, an extended method for repetition columns is also provided with DataReader. First, use GetFieldArrayCount to acquire the number of repetition columns for the data obtained by FETCH. To acquire the value of the data obtained by FETCH, use :overload of GetValue. In the second argument, specify the number of the repetition column. An indexer [int,int] equivalent to this method is also provided.

Note: The usage of repetition columns is similar to that of the INSERT facility using arrays. The differences occur in the part that specifies the repetition count in the parameter and the part that executes the SQL statement.

16.11.6 Executing a repetition column