Data transfer between Mosel and the database

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

From the database to Mosel

Information is moved from the database 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:

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

behaves differently depending on the value of SQLndxcol. 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 mmodbc:

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 SQLndxcol 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:

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

and the control parameter SQLndxcol 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 SQLexecute may also be a list of arrays. When using this version, the value of SQLndxcol 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:

SQLexecute("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 the database

Information is transferred from Mosel to the database 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 the symbol `?' in the expression. For instance in the following expression 3 parameters are used:

INSERT INTO T (c1,c2,c3) VALUES (?,?,?)

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:

SQLexecute("INSERT INTO T (c1,c2,c3) VALUES (?,?,?)",mt)

behaves differently depending on the value of SQLndxcol. If this control parameter is true, for each execution of the command, the following assignments are performed by mmodbc (?1,?2,?3 denote respectively the first second and third parameter):

'?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

If the control parameter SQLndxcol is false, only the values of the Mosel array are used to initialize the parameters. So, for each execution of the command, 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:

SQLexecute("INSERT INTO T (c1,c2,c3,c4) VALUES (?,?,?,?)",mt)

and the control parameter SQLndxcol 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 mmodbc:

'?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 SQLexecute 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:

SQLexecute("INSERT INTO T (c1,c2,c3) VALUES (?,?,?)",[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