An Open SQL Insert Example

SourcePro DB : DB Access Module for Microsoft SQL Server User’s Guide : Technical Information : Open SQL and Microsoft SQL Server ODBC : Open SQL Examples : An Open SQL Insert Example

This example shows how to execute an INSERT statement using RWDBOSql to insert data into the emp table.

const size_t NUM_OF_ROWS = 14; //1

RWDBTBuffer<int> empnoBuff(NUM_OF_ROWS), mgrBuff(NUM_OF_ROWS),

deptBuff(NUM_OF_ROWS);

RWDBTBuffer<RWCString> enameBuff(NUM_OF_ROWS), posnBuff(NUM_OF_ROWS);

RWDBTBuffer<RWDecimalPortable> salBuff(NUM_OF_ROWS); //2

empnoBuff[0] = 1;

enameBuff[0] = "ABC XYZ";

posnBuff[0] = "ADMIN";

mgrBuff[0] = 6;

salBuff[0] = "58000.00";

deptBuff[0] = 4; //3

// Populate rest of the rows

RWDBOSql openSql("INSERT INTO emp VALUES(?, ?, ?, ?, ?, ?)",

RWDBOSql::NonQuery); //4

openSql << empnoBuff << enameBuff << posnBuff

<< mgrBuff << salBuff << deptBuff; //5

openSql.execute(cn); //6

long rowsInserted = openSql.rowsAffected(); //7

std::cout << (openSql.isValid() ? "Data insertion successful." //8

: "Data insertion failed.") << std::endl;

if (rowsInserted >= 0) {

std::cout << "Inserted " << rowsInserted << " rows." << std::endl;

}

//1 Defines a constant for the number of rows to insert into the table for each insert operation. This is the array size of the bound variables.

//2 Defines buffers for holding the data to be inserted. A buffer is defined for every column in the table. The datatype on which each RWDBTBuffer object is templatized depends on the server type of each column in the table. Mappings between the C++ types and server datatypes are provided in Datatypes. The number of entries in the buffer equals the number of rows to be inserted.

//3 Fills the buffers with data for the first row and subsequent rows using the index operator on the RWDBTBuffer objects. Alternatively, you could write a separate subroutine to fill the buffer with data.

//4 Creates an RWDBOSql object with the INSERT SQL statement using the statement type hint of RWDBOSql::NonQuery. The INSERT statement uses placeholders for each column in the table. Buffers need to be bound for the placeholders before the RWDBOSql is ready for execution.

//5 Binds the input buffers to the RWDBOSql in the same sequence as columns in the table.

//6 Executes the RWDBOSql on the supplied RWDBConnection.

//7 Fetches the number of rows inserted using method rowsAffected(). Note that some rows may be inserted even if the insert operation fails.

//8 Determines the success of the insert operation using the isValid() method on RWDBOSql.