Hard-Coded SQL Statements

Applications that perform a fixed task usually contain hard-coded SQL statements. For example, an order entry system might use the following call to list open sales orders:

SQLExecDirect(hstmt, "SELECT OrderID FROM Orders WHERE Status = 'OPEN'", SQL_NTS);

There are several advantages to hard-coded SQL statements: they can be tested when the application is written, they are simpler to implement than statements constructed at run time, and they simplify the application.

Using statement parameters and preparing statements provide even better ways to use hard-coded SQL statements. For example, suppose the Parts table contains the PartID, Description, and Price columns. One way to insert a new row into this table would be to construct and execute an INSERT statement:

#define DESC_LEN 51
#define STATEMENT_LEN 51

SQLUINTEGER PartID;
SQLCHAR   Desc[DESC_LEN], Statement[STATEMENT_LEN];
SQLREAL   Price;

// Set part ID, description, and price.
GetNewValues(&PartID, Desc, &Price);

// Build INSERT statement.
sprintf(Statement, "INSERT INTO Parts (PartID, Description,  Price) "
    "VALUES (%d, '%s', %f)", PartID, Desc, Price);

// Execute the statement.
SQLExecDirect(hstmt, Statement, SQL_NTS);

An even better way is to use a hard-coded, parameterized statement. This has two advantages over a statement with hard-coded data values. First, it is easier to construct a parameterized statement because the data values can be sent in their native types, such as integers and floating point numbers, rather than converting them to strings. Second, such a statement can be used easily more than once by just changing the parameter values and re-executing it; there is no need to rebuild it.

#define DESC_LEN 51

SQLCHAR * Statement = "INSERT INTO Parts (PartID, Description,  Price) "
        "VALUES (?, ?, ?)";
SQLUINTEGER PartID;
SQLCHAR   Desc[DESC_LEN];
SQLREAL   Price;
SQLINTEGER  PartIDInd = 0, DescLenOrInd = SQL_NTS, PriceInd = 0;

// Bind the parameters.
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,
       &PartID, 0, &PartIDInd);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0,
       Desc, sizeof(Desc), &DescLenOrInd);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,
       &Price, 0, &PriceInd);

// Set part ID, description, and price.
GetNewValues(&PartID, Desc, &Price);

// Execute the statement.
SQLExecDirect(hstmt, Statement, SQL_NTS);

Assuming this statement is to be executed more than once, it can be prepared for even greater efficiency:

#define DESC_LEN 51

SQLCHAR *Statement = "INSERT INTO Parts (PartID, Description,  Price) "
        "VALUES (?, ?, ?)";
SQLUINTEGER PartID;
SQLCHAR   Desc[DESC_LEN];
SQLREAL   Price;
SQLINTEGER  PartIDInd = 0, DescLenOrInd = SQL_NTS, PriceInd = 0;

// Prepare the INSERT statement.
SQLPrepare(hstmt, Statement, SQL_NTS);

// Bind the parameters.
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,
       &PartID, 0, &PartIDInd);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0,
       Desc, sizeof(Desc), &DescLenOrInd);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,
       &Price, 0, &PriceInd);

// Loop to continually get new values and insert them.
while (GetNewValues(&PartID, Desc, &Price))
 SQLExecute(hstmt);

Perhaps the most efficient way to use the statement is to construct a procedure containing the statement, as shown in the following code example. Because the procedure is constructed at development time and stored on the data source, it does not need to be prepared at run time. A drawback of this method is that the syntax for creating procedures is DBMS-specific and procedures must be constructed separately for each DBMS on which the application is to run.

#define DESC_LEN 51

SQLUINTEGER PartID;
SQLCHAR   Desc[DESC_LEN];
SQLREAL   Price;
SQLINTEGER  PartIDInd = 0, DescLenOrInd = SQL_NTS, PriceInd = 0;

// Bind the parameters.
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,
       &PartID, 0, &PartIDInd);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0,
       Desc, sizeof(Desc), &DescLenOrInd);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,
       &Price, 0, &PriceInd);

// Loop to continually get new values and insert them.
while (GetNewValues(&PartID, Desc, &Price))
 SQLExecDirect(hstmt, "{call InsertPart(?, ?, ?)}", SQL_NTS);

For more information about parameters, prepared statements, and procedures, see “Executing a Statement” in Chapter 9, “Executing Statements.”