Data manipulation: Insert, Delete, and Update

With UltraLite, you can perform SQL data manipulation by using the ExecuteStatement method (a member of the PreparedStatement class).

See UltraLite_PreparedStatement 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
  1. Declare a PreparedStatement.

    PreparedStatement * prepStmt;

    See PrepareStatement function.

  2. Assign a SQL statement to the PreparedStatement object.

    prepStmt = conn->PrepareStatement( UL_TEXT("INSERT INTO MyTable(MyColumn) values (?)") );
  3. Assign input parameter values for the statement.

    The following code shows a string parameter.

    prepStmt->SetParameter( 1, UL_TEXT("newValue") );
  4. Execute the prepared statement.

    The return value indicates the number of rows affected by the statement.

    ul_s_long rowsInserted;
    rowsInserted = prepStmt->ExecuteStatement();
  5. Commit the change.

    conn->Commit();
To delete a row
  1. Declare a PreparedStatement.

    PreparedStatement * prepStmt;
  2. Assign a SQL statement to the PreparedStatement object.

    ULValue sqltext( );
    prepStmt = conn->PrepareStatement( UL_TEXT("DELETE FROM MyTable WHERE MyColumn = ?") );
  3. Assign input parameter values for the statement.

    prepStmt->SetParameter( 1, UL_TEXT("deleteValue") );
  4. Execute the statement.

    ul_s_long rowsDeleted;
    rowsDeleted = prepStmt->ExecuteStatement();
  5. Commit the change.

    conn->Commit();
To update a row
  1. Declare a PreparedStatement.

    PreparedStatement * prepStmt;
  2. Assign a statement to the PreparedStatement object.

    prepStmt = conn->PrepareStatement( 
        UL_TEXT("UPDATE MyTable SET MyColumn1 = ? WHERE MyColumn1 = ?") );
  3. Assign input parameter values for the statement.

    prepStmt->SetParameter( 1, UL_TEXT("newValue") );
    prepStmt->SetParameter( 2, UL_TEXT("oldValue") );
  4. Execute the statement.

    ul_s_long rowsUpdated;
    rowsUpdated = prepStmt->ExecuteStatement();
  5. Commit the change.

    conn->Commit();