Managing Data Batches

Use SQL_ATTR_BATCH_PARAMS, a Sybase-specific connection attribute, to manage the batches of parameters sent to Adaptive Server.

Set SQL_ATTR_BATCH_PARAMS using SQLSetConnectAttr.
Valid values are:

  • SQL_BATCH_ENABLED – informs Adaptive Server ODBC Driver to batch the parameters. When in this state, the driver sends an error if a statement other than the statement being processed—the first statement executed after setting SQL_ATTR_BATCH_PARAMS to SQL_BATCH_ENABLED—by SQLExecute is executed on the connection.

  • SQL_BATCH_LAST_DATA – specifies that the next batch of parameters is the last batch, and that the parameters contain data.

  • SQL_BATCH_LAST_NO_DATA – specifies that the next batch of parameters is the last batch, and to ignore the parameters.

  • SQL_BATCH_CANCEL – informs the Adaptive Server ODBC Driver to cancel the batch and to roll back the transactions.

    Only uncommitted transactions can be rolled back.

  • SQL_BATCH_DISABLED – (default) Adaptive Server ODBC Driver returns to this state after processing the last batch of parameters. You cannot manually set SQL_ATTR_BATCH_PARAMS to this value.

Example1 – sends a batch of parameters to the server without binding parameter arrays:
// Setting the SQL_ATTR_BATCH_PARAMS attribute to start
// the batch
sr = SQLSetConnectAttr(dbc, SQL_ATTR_BATCH_PARAMS,
   (SQLPOINTER)SQL_BATCH_ENABLED, SQL_IS_INTEGER);
printError(sr, SQL_HANDLE_DBC, dbc);

// Bind the parameters. This can be done once for the entire batch
sr = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, 
   SQL_C_LONG, SQL_INTEGER, l1, 0, &c1, l1, &l1);
sr = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_LONGVARCHAR, l2, 0, buffer, l2, &l2);
}

// Run a batch of 10 for (int i = 0; i < 10; i++)
{
   c1 = i;
   memset(buffer, 'a'+i, l2);
   sr = SQLExecDirect(stmt, insertStmt, SQL_NTS);
   printError(sr, SQL_HANDLE_STMT, stmt);
}
Example 2 – ends and closes a batch:
// Setting the SQL_ATTR_BATCH_PARAMS attribute to end
// the batch
sr = SQLSetConnectAttr(dbc, SQL_ATTR_BATCH_PARAMS,
  (SQLPOINTER)SQL_BATCH_LAST_NO_DATA, SQL_IS_INTEGER);
printError(sr, SQL_HANDLE_DBC, dbc);

// Call SQLExecDirect one more time to close the batch
// - Due to SQL_BATCH_LAST_NO_DATA, this will not 
// process the parameters
sr = SQLExecDirect(stmt, insertStmt, SQL_NTS);
printError(sr, SQL_HANDLE_STMT, stmt);
Parent topic: ODBC Data Batching Without Binding Parameter Arrays