Applications that use SQLPrepare and multiple SQLExecute calls should use SQLParamOptions. Passing arrays of parameter values reduces the ODBC call load and network traffic.
Consider the following example that inserts data:
rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...)
VALUES (?,?,...)", SQL_NTS);
// bind parameters
...
do {
// read ledger values into bound parameter buffers
...
rc = SQLExecute (hstmt); // insert row
} while ! (eof);
If there are 100 rows to insert, SQLExecute is called 100 times, resulting in 100 network requests to the server.
Alternatively, consider an algorithm that uses parameter arrays by calling SQLParamOptions:
rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...)
// read up to 50 ledger values into bound parameter buffers
...
rc = SQLExecute (hstmt); // insert row
The call load is reduced from 100 to just 2 SQLExecute calls. Furthermore, network traffic is reduced considerably. To achieve the best performance, applications should contain algorithms for using SQLParamOptions. SQLParamOptions is ideal for copying data into new tables or bulk loading tables. Note, however, that some ODBC drivers do not support SQLParamOptions.
