Data transfer between Mosel and Oracle

Data transfer beetween Mosel and Oracle is achieved by calls to the procedure OCIexecute. The value of the control parameter OCIndxcol and the type and structure of the second argument of the procedure decide how the data are transferred between the two systems.

From Oracle to Mosel

Information is moved from Oracle to Mosel when performing a SELECT command for instance. Assuming mt has been declared as follows:

mt: array(1..10,1..3) of integer

the execution of the call:

OCIexecute("SELECT c1,c2,c3 from T", mt)

behaves differently depending on the value of OCIndxcol. If this control parameter is true, the columns c1 and c2 are used as indices and c3 is the value to be assigned. For each row (i,j,k) of the result set, the following assignment is performed by mmoci:

mt(i,j):=k

With a table T containing:

c1 c2 c3 c4
 1  2  5  7
 4  3  6  8

We obtain the initialization:

m2(1,2)=5, m(4,3)=6

If the control parameter OCIndxcol is false, all columns are treated as data. In this case, for each row (i,j,k) the following assignments are performed:

mt(r,1):=i; mt(r,2):=j; mt(r,3):=k

where r is the row number in the result set.

Here, the resulting initialization is:

mt(1,1)=1, mt(1,2)=2, mt(1,3)=5
mt(2,1)=4, mt(2,2)=3, mt(2,3)=6

If the SQL statement selects 4 columns (instead of 3) as in:

OCIexecute("SELECT c1,c2,c3,c4 from T", mt)

and the control parameter OCIndxcol is false, the first column is used as the first array index while the remaining columns are treated as data. As a consequence, for each row (i,j,k,l) the following assignments are performed:

mt(i,1):=j; mt(i,2):=k; mt(i,3):=l

The resulting initialization is therefore:

mt(1,1)=2, mt(1,2)=5, mt(1,3)=7
mt(4,1)=3, mt(4,2)=6, mt(4,3)=8

The second argument of OCIexecute may also be a list of arrays. When using this version, the value of OCIndxcol is ignored and the first column(s) of the result set are always considered as indices and the following ones as values for the corresponding arrays. For instance, assuming we have the following declarations:

m1, m2: array(1..10) of integer

With the statement:

OCIexecute("SELECT c1,c2,c3 from T", [m1,m2])

for each row (i,j,k) of the result set, the following assignments are performed:

m1(i):=j; m2(i):=k

So, if we use the table T of our previous example, we get the initialization:

m1(1)=2, m1(4)=5
m2(1)=3, m2(4)=6

From Mosel to Oracle

Information is transferred from Mosel to Oracle when performing an INSERT command for instance. In this case, the way to use the Mosel arrays has to be specified by using parameters in the SQL command. These parameters are identified by their name in the expression. For instance in the following expression 3 parameters (:1, :2 and :3) are used:

INSERT INTO T (c1,c2,c3) VALUES (:1,:2,:3)

mmoci expects that parameters are always named :n where n is the parameter number starting at 1 but does not impose any order (i.e. :3,:1,:2 is also valid) and a given parameter may be used several times in an expression. The command is then executed repeatedly as many times as the provided data allows to build new tuples of parameters. The initialization of parameters is similar to what is done for a SELECT statement.

Assuming mt has been declared as follows:

mt: array(1..2,1..3) of integer

and initialized with this assignment:

mt::[1,2,3,
     4,5,6]

the execution of the call:

OCIexecute("INSERT INTO T (c1,c2,c3) VALUES (:1,:2,:3)",mt)

behaves differently depending on the value of OCIndxcol. If this control parameter is true, for each execution of the command, the following assignments are performed by mmoci:

':1':= i, ':2':= j, ':3':= mt(i,j)  

The execution is repeated for all possible values of i and j (in our example 6 times). The resulting table T is therefore:

c1 c2 c3
 1  1  1
 1  2  2
 1  3  3
 2  1  4
 2  2  5
 2  3  6

Note that mmoci uses the names of the parameters to perform an initialization and not their relative position. This property is particularly useful for UPDATE statements where the order of parameters needs to be changed. For instance, if we want to update the table T instead of inserting new rows, we can write:

OCIexecute("UPDATE T c3=:3 WHERE c1=:1, c2=:2",mt)

This command is executed exactly in the same way as the INSERT example above (i.e. we do not have ':3':=i, ':1':=j, ':2':=mt(i,j) as the order of appearance in the command suggests but ':1':=i, ':2':=j, ':3':=mt(i,j)).

The same functionality may also be used to reorder or repeat columns. With the same definition of the array mt as before and a 4-column table S in the database the execution of the command

OCIexecute("INSERT INTO S (c1,c2,c3,c4) VALUES (:1,:2,:3,:2)",mt)

results in the following contents of table S:

c1 c2 c3 c4
 1  1  1  1
 1  2  2  2
 1  3  3  3
 2  1  4  1
 2  2  5  2
 2  3  6  3

If the control parameter OCIndxcol is false, only the values of the Mosel array are used to initialize the parameters. So, for each execution of the command of our initial example (with 3 parameters), we have:

':1':=mt(i,1), ':2':=mt(i,2), ':3':=mt(i,3)

The execution is repeated for all possible values of i (in our example 2 times). The resulting table T is therefore:

c1 c2 c3
 1  2  3
 4  5  6

However if the SQL query defines 4 parameters (instead of 3) as in:

OCIexecute("INSERT INTO T (c1,c2,c3,c4) VALUES (:1,:2,:3,:4)",mt)

and the control parameter OCIndxcol is false, the first parameter is used as the first array index while the remaining parameters are populated with data. As a consequence, for each execution of the command, the following assignments are performed by mmoci:

':1':= i, ':2':= mt(i,1), ':3':= mt(i,2), ':4':=mf(i,3)

The execution is repeated for all possible values of i (in our example 2 times). The resulting table T is therefore:

c1 c2 c3 c4
 1  1  2  3
 2  4  5  6

When OCIexecute is used with a list of arrays, the behavior is again similar to what has been described earlier for the SELECT command: the first parameter(s) are assigned index values and the final ones the actual array values. For instance, assuming we have the following declarations:

m1,m2: array(1..3) of integer

And the arrays have been initialized as follows:

m1::[1,2,3]
m2::[4,5,6]

Then the following call:

OCIexecute("INSERT INTO T (c1,c2,c3) VALUES (:1,:2,:3)",[m1,m2])

executes 3 times the INSERT command. For each execution, the following parameter assignments are performed:

':1':=i, ':2':=m1(i), ':3':=m2(i)

The resulting table T is therefore:

c1 c2 c3
 1  1  4
 2  2  5
 3  3  6