Data manipulation: Insert, Delete, and Update

With UltraLite, you can perform SQL data manipulation by using the ExecuteStatement method, a member of the ULPreparedStatement class. See ULPreparedStatement class.

Referencing parameters in prepared statements

UltraLite indicates query parameters using the ? character. For any INSERT, UPDATE, or DELETE statement, each ? is referenced according to its ordinal position in the prepared statement. For example, the first ? is referred to as parameter 1, and the second as parameter 2.

To insert a row

Declare a ULPreparedStatement using the following code:
ULPreparedStatement * prepStmt;

Prepare a SQL statement for execution.

The following code prepares an INSERT statement for execution:

prepStmt = conn->PrepareStatement("INSERT INTO MyTable(MyColumn1) VALUES (?)");

Check for errors when preparing the statement.

For example, the following code is useful when checking for SQL syntax errors:

if( prepStmt == NULL ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
    return;
}

Set values to replace ? characters in the prepared statement.

The following code sets ? characters to "some value" while error checking. For example, an error is caught when the parameter ordinal is out of range for the number of parameters in the prepared statement.

if( !prepStmt->SetParameterString(1, "some value") ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
    return;
}

Execute the prepared statement, inserting the data into the database.

The following code checks for errors that could occur after executing the statement. For example, an error is returned if a duplicate index value is found in a unique index.

bool success;
success = prepStmt->ExecuteStatement();
if( !success ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
} else {
    // Use the following line if you are interested in the number of rows inserted ...
    ul_u_long rowsInserted = prepStmt->GetRowsAffectedCount();
}

Clean up the prepared statement resources.

The following code releases the resources used by the prepared statement object. This object should no longer be accessed after the Close method is called.
prepStmt->Close();
Commit the data to the database.

The following code saves the data to the database and prevents data loss. The data from step 5 is lost if the device application terminates unexpectedly before the application completes a commit call.
conn->Commit();

To delete a row

Declare a ULPreparedStatement using the following code:
ULPreparedStatement * prepStmt;

Prepare a SQL statement for execution.

The following code prepares a DELETE statement for execution:

prepStmt = conn->PrepareStatement("DELETE FROM MyTable(MyColumn1) VALUES (?)");

Check for errors when preparing the statement.

For example, the following code is useful when checking for SQL syntax errors:

if( prepStmt == NULL ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
    return;
}

Set values to replace ? characters in the prepared statement.

The following code sets ? characters to 7 while error checking. For example, an error is caught when the parameter ordinal is out of range for the number of parameters in the prepared statement.

ul_s_long value_to_delete = 7;
if( !prepStmt->SetParameterInt(1, value_to_delete) ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error.
    return;
}

Execute the prepared statement, deleting the data from the database.

The following code checks for errors that could occur after executing the statement. For example, an error is returned if you try deleting a row that has a foreign key referenced to it.

bool success;
success = prepStmt->ExecuteStatement();
if( !success ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
} else {
    // Use the following line if you are interested in the number of rows deleted ...
    ul_u_long rowsDeleted = prepStmt->GetRowsAffectedCount();
}

Clean up the prepared statement resources.

The following code releases the resources used by the prepared statement object. This object should no longer be accessed after the Close method is called.
prepStmt->Close();
Commit the data to the database.

The following code saves the data to the database and prevents data loss. The data from step 5 is lost if the device application terminates unexpectedly before the application completes a commit call.
conn->Commit();

To update a row

Declare a ULPreparedStatement using the following code:
ULPreparedStatement * prepStmt;

Prepare a SQL statement for execution.

The following code prepares an UPDATE statement for execution:

prepStmt = conn->PrepareStatement("UPDATE MyTable SET MyColumn = ? WHERE MyColumn = ?");

Check for errors when preparing the statement.

For example, the following code is useful when checking for SQL syntax errors:

if( prepStmt == NULL ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
    return;
}

Set values to replace ? characters in the prepared statement.

The following code sets ? characters to integer values while error checking. For example, an error is caught when the parameter ordinal is out of range for the number of parameters in the prepared statement.

bool success;
success = prepStmt->SetParameterInt( 1, 25 );
if( success ) {
    success = prepStmt->SetParameterint( 2, -1 );
}
if( !success ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
    return;
}

Execute the prepared statement, updating the data in the database.

The following code checks for errors that could occur after executing the statement. For example, an error is returned if a duplicate index value is found in a unique index.

success = prepStmt->ExecuteStatement();
if( !success ) {
    const ULError * ulerr;
    ulerr = conn->GetLastError();
    // write code to handle the error
} else {
    // if you are interested in the number of rows updated ...
    ul_u_long rowsUpdated = prepStmt->GetRowsAffectedCount();
}

Clean up the prepared statement resources.

The following code releases the resources used by the prepared statement object. This object should no longer be accessed after the Close method is called.
prepStmt->Close();
Commit the data to the database.

The following code saves the data to the database and prevents data loss. The data from step 5 is lost if the device application terminates unexpectedly before the application completes a commit call.
conn->Commit();

Discuss this page in DocCommentXchange.