An Open SQL Insert Example

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

Example 7 – Executing an INSERT statement using an RWDBOSql object

This example shows how to execute an INSERT statement using RWDBOSql to insert data in 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($1, $2, $3, $4, $5, $6)",

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 in one 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 equal the number of rows to be inserted.

//3 Fills the buffers with data for the first row using the index operator on the RWDBTBuffer objects. Similarly, fill other rows with the data. You may want to 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 sequence of 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.