Doc No: | N3459 = 12-0149
Date: | 2012-10-13
Reply to: | Bill Seymour <stdbill`.`h@pobox`.`com>

## Comparison of Two Database Access Methodologies

### Bill Seymour
2012-10-13

### Abstract:

In response to N3415, A Database Access Library, Thomas Neumann of the Technische Universität München has prepared an alternate proposal, N3458, Simple Database Integration in C++11, which will appear in the post-Portland mailing (and is on the LWG wiki now as D3458). This paper compares the two proposed interfaces.

For each of two examples, I show some small bits of code currently running in production in a United States Postal Service system (in Oracle’s proprietary language, PL/SQL); and then I attempt to rewrite them as complete C++ programs using the interface proposed in N3415 and the one that Neumann suggests (expanded a bit with some additional features that I think are needed).

(I also did rewrites in Java just as reference points, but I decided that they don’t really matter to WG21. If anyone wants to see the Java versions, they’re still in the paper inside HTML comments.)

### Executive Summary:

After actually trying to use the two interfaces, I think I like Neumann’s better; although there are probably a few features that need to be added to make it usable in a business environment.

Although Neumann’s paper is technically too late for Portland, I would very much like to see it presented to LWG in Portland, if there’s time, so that we can both, at least, get encouragement to continue.

### Disclaimers:

“Beware of bugs in the [C++ code below]. I have only proved it correct, not tried it.” — Donald E. Knuth

“If this were my employer’s opinion, I wouldn’t be allowed to post it.” — Norman Diamond

### Example 1:

The Postal Service occasionally changes its “service standards”, the number of days it should take mail of various classes to get from point A to point B. When this happens, it’s important for the new service standards to be reflected in all existing dispatches.

    DECLARE
        dummy NUMBER(38);  -- not used herein, but it's an IN OUT param
                           -- in the proc that writes the audit trail
    BEGIN
        FOR rec IN (SELECT disp.dsptch_id,
                           disp.svc_std AS current_std,
                           vdat.mail_srv_std AS desired_std
                    FROM apl_dispatch disp,
                         ref_atomic_mail_class atmc,
                         ref_aggregate_mail_class agmc,
                         apl_tops_3d_volume_data vdat,
                         ref_facility ofac,
                         ref_facility dfac
                    WHERE atmc.atomic_mail_class_id = disp.atomic_mail_class_id
                      AND agmc.aggregate_mail_class_id = atmc.aggregate_mail_class_id
                      AND vdat.atomic_mail_class_id = agmc.dsptch_dflt_mail_cls_id
                      AND vdat.mail_srv_std IS NOT NULL
                      AND ofac.facility_id = disp.orig_facility_id
                      AND vdat.orig_zip3 = SUBSTR(ofac.zip_4, 1, 3)
                      AND dfac.facility_id = disp.dest_facility_id
                      AND vdat.dest_zip3 = SUBSTR(dfac.zip_4, 1, 3))
        LOOP
            IF rec.current_std <> rec.desired_std
            THEN
                UPDATE apl_dispatch d
                SET d.svc_std = rec.desired_std,
                    d.last_user_id = 'FixSSD',
                    d.last_updtd_dt = SYSTIMESTAMP
                WHERE d.dsptch_id = rec.dsptch_id;

                dummy := NULL;
                pkg_legrep_common.sp_write_disp_header_audit
                    (rec.dsptch_id, 'FixSSD', 'FixSSD', 'C', dummy);

                COMMIT; -- per dispatch actually changed
            END IF;
        END LOOP;
    END;

In this first example, I don’t worry about exceptions escaping from `main()`.

    #include "dbacc.hpp"
    using dbacc::connection;
    using dbacc::statement;
    using dbacc::call_statement;
    using dbacc::query;
    using dbacc::cursor;
    using dbacc::row;
    using dbacc::column;

    #include <string>
    #include <cstdint>

    int main()
    {
        //
        // Regardless of which interface is chosen, we'll need a way
        // for a trusted program to get a connection from some kind of
        // connection pool without passing passwords around in the clear.
        // This is a security issue.  I don't have that yet either.
        //
        connection conn("Oracle Call Interface",
                        "dtops", // looked up in tnsnames.ora
                        "user_id",
                        "password");
        conn.auto_commit(false);

        statement upd(conn, "UPDATE apl_dispatch "
                            "SET svc_std = ?, "
                                "last_user_id = 'FixSSD', "
                                "last_updtd_dt = SYSTIMESTAMP "
                            "WHERE dsptch_id = ?");

        call_statement audit(conn,
                             "{call pkg_legrep_common.sp_write_disp_header_audit"
                             "(?, 'FixSSD', 'FixSSD', 'C', ?)}");

        query<cursor> qry(conn,
                          "SELECT disp.dsptch_id, "
                                 "disp.svc_std AS current_std, "
                                 "vdat.mail_srv_std AS desired_std "
                          "FROM apl_dispatch disp, "
                               "ref_atomic_mail_class atmc, "
                               "ref_aggregate_mail_class agmc, "
                               "apl_tops_3d_volume_data vdat, "
                               "ref_facility ofac, "
                               "ref_facility dfac "
                          "WHERE atmc.atomic_mail_class_id = "
                                "disp.atomic_mail_class_id "
                            "AND agmc.aggregate_mail_class_id = "
                                "atmc.aggregate_mail_class_id "
                            "AND vdat.atomic_mail_class_id = "
                                "agmc.dsptch_dflt_mail_cls_id "
                            "AND vdat.mail_srv_std IS NOT NULL "
                            "AND ofac.facility_id = disp.orig_facility_id "
                            "AND vdat.orig_zip3 = SUBSTR(ofac.zip_4, 1, 3) "
                            "AND dfac.facility_id = disp.dest_facility_id "
                            "AND vdat.dest_zip3 = SUBSTR(dfac.zip_4, 1, 3)");

        std::string disp_id;
        int desired_std;
        std::uintmax_t dummy = 0;
        statement::indicator indic = statement::null_indicator_value;

        upd.prepare();
        upd.bind(1, &desired_std);
        upd.bind(2, &disp_id);

        audit.prepare();
        audit.bind(1, &disp_id);
        audit.bind(2, &dummy, &indic);

        qry.execute();

        for (cursor c : qry.results())
        {
            const row& r = *c;
            const column& des = r["desired_std"];
            if (r["current_std"] != des)
            {
                r["dsptch_id"].get(disp_id);
                des.get(desired_std);
                indic = statement::null_indicator_value;

                upd.execute();
                audit.execute();
                conn.commit();
            }
        }
    }

    #include <string>
    #include <cstdint>
    using std::string;

    #include "tdb.hpp"
    using namespace tdb;

    //
    // I like the nullable template; but that can easily be added to N3415.
    //
    typedef nullable<std::uintmax_t> audit_id;

    int main()
    {
        connection conn("credentials", connection::access_mode::read_write);

        //
        // Note that Neumann's connection is, among other things,
        // a statement factory (like a java.sql.Connection).
        //
        prepared_statement<int, string> upd = conn.prepare_statement(
            "UPDATE apl_dispatch d "
            "SET d.svc_std = ?, "
                "d.last_user_id = 'FixSSD', "
                "d.last_updtd_dt = SYSTIMESTAMP "
            "WHERE d.dsptch_id = ?");

        //
        // We really need a way to call stored procedures
        // when using databases that have them.
        //
        prepared_call<string, audit_id> audit = conn.prepare_call(
            "{call pkg_legrep_common.sp_write_disp_header_audit"
                "(?, 'FixSSD', 'FixSSD', 'C', ?)}");

        prepared_query<> qry = conn.prepare_query(
            "SELECT disp.dsptch_id, "
                   "disp.svc_std AS current_std, "
                   "vdat.mail_srv_std AS desired_std "
            "FROM apl_dispatch disp, "
                 "ref_atomic_mail_class atmc, "
                 "ref_aggregate_mail_class agmc, "
                 "apl_tops_3d_volume_data vdat, "
                 "ref_facility ofac, "
                 "ref_facility dfac "
            "WHERE atmc.atomic_mail_class_id = disp.atomic_mail_class_id "
              "AND agmc.aggregate_mail_class_id = atmc.aggregate_mail_class_id "
              "AND vdat.atomic_mail_class_id = agmc.dsptch_dflt_mail_cls_id "
              "AND vdat.mail_srv_std IS NOT NULL "
              "AND ofac.facility_id = disp.orig_facility_id "
              "AND vdat.orig_zip3 = SUBSTR(ofac.zip_4, 1, 3) "
              "AND dfac.facility_id = disp.dest_facility_id "
              "AND vdat.dest_zip3 = SUBSTR(dfac.zip_4, 1, 3)");

        string disp_id;
        int current_std, desired_std;
        audit_id dummy;

        for (auto row : qry().into(disp_id, current_std, desired_std))
        {
            if (current_std != desired_std)
            {
                transaction trans(conn);

                //
                // It would be nice to have a way to "bind"/"describe"
                // program variables so that they don't always have to be
                // passed to operator() or into().  Think of INSERTing
                // into a table with many tens of columns, or a query
                // with many tens of columns in the SELECT list.
                //
                upd(desired_std, disp_id);

                dummy.set(nullptr);
                audit(disp_id, dummy).into(dummy);

                trans.commit();
            }
        }
    }

### Example 2:

Volume projections sent to air carriers can be generated automatically by the system or manually by a user. In the latter case, they need to be moved from one table to another, but with the effective and discontinue dates changed to match the relevant “planning week”. (Don’t ask why. `8-)`)

    CREATE OR REPLACE PROCEDURE
        sp_move_user_projections(job_in IN job_job.job_id%TYPE)
    IS
        flag       CHAR(1);
        week_beg   DATE;
        week_end   DATE;
        row_cnt    PLS_INTEGER;

    BEGIN
        SELECT outbound_source INTO flag FROM dmd_vol_config;
        IF flag = 'U' -- else nothing to do
        THEN
            SELECT h.start_horizon, h.end_horizon
            INTO week_beg, week_end
            FROM ref_plan_horizon h, job_job j, job_calendar c
            WHERE j.job_id = job_in
              AND c.calendar_id = j.calendar_id
              AND h.horizon_id = c.horizon_id;

            pkg_tops_util.sp_logger('INFO', 'sp_move_user_projections',
                                    'Moving user volume projections for week of '
                                        || TO_CHAR(week_beg, 'YYYY-MM-DD'),
                                    NULL, 'sp_move_user_projections', job_in);

            DELETE FROM dmd_vol_upload_outbound WHERE 1 = 1;  -- rollbackable

            INSERT INTO dmd_vol_upload_outbound
                (origin, destination, day_of_week,
                 effective_date, discontinue_date, volume)
                (SELECT origin, destination, day_of_week,
                        week_beg, week_end, volume
                 FROM dmd_vol_upload
                 WHERE effective_date <= week_beg
                   AND discontinue_date >= week_end);
            row_cnt := SQL%ROWCOUNT;

            IF row_cnt > 0
            THEN
                COMMIT;
                pkg_tops_util.sp_logger('INFO', 'sp_move_user_projections',
                                        'Moved ' || row_cnt || ' rows.',
                                        NULL, 'sp_move_user_projections', job_in);
            ELSE
                ROLLBACK;
                pkg_tops_util.sp_logger('SEVERE', 'sp_move_user_projections',
                                        'No data found.',
                                        NULL, 'sp_move_user_projections', job_in);
                RAISE_APPLICATION_ERROR(pkg_tops_util.TOPS_EXCEPTION_CODE,
                                        'No data found in DMD_VOL_UPLOAD');
            END IF;
        END IF;

    END sp_move_user_projections;

In this example, I pay more attention to exceptions, although I don’t bother to check the command-line argument for validity.

    #include "dbase.hpp"
    using dbacc::connection;
    using dbacc::query;
    using dbacc::table;
    using dbacc::row;
    using dbacc::cursor;
    using dbacc::sql_error;

    //
    // Any reasonable database access library will need to support
    // all SQL types including datetime types.  Assume that we have
    // a date type with a tm() member function that returns a
    // const struct tm&.
    //
    using dbacc::date;

    #include <exception>
    #include <iostream>
    #include <sstream>
    #include <string>

    #include <cstdlib>  // strtoull, EXIT_SUCCESS, EXIT_FAILURE
    #include <ctime>    // tm, strftime

    using namespace std;

    int main(int, char** argv)
    {
        int completion_code = EXIT_FAILURE;

        try
        {
            connection conn("Oracle Call Interface", "dtops", "user", "pswd");
            conn.auto_commit(false);

            query<cursor> qry(conn, "SELECT outbound_source FROM dmd_vol_config");
            qry.execute();
            table<cursor> result = qry.results();
            const row& r = *result.begin();
            if (r["outbound_source"].get<string>() == "U")
            {
                unsigned long long job = strtoull(argv[1], NULL, 0);

                date week_beg, week_end;

                qry.prepare("SELECT h.start_horizon, h.end_horizon "
                            "FROM ref_plan_horizon h, job_job j, job_calendar c "
                            "WHERE j.job_id = ? "
                              "AND c.calendar_id = j.calendar_id "
                              "AND h.horizon_id = c.horizon_id");
                qry.set(1, job);
                qry.execute();
                result = qry.results();
                r = *result.begin();
                r["start_horizon"].get(week_beg);
                r["end_horizon"].get(week_end);

                statement log(conn, "{call pkg_tops_util.sp_logger(?, "
                                    "'move_user_projections', ?, NULL, "
                                    "'move_user_projections', ?)}");
                log.prepare();

                string severity("INFO");
                log.bind(1, &severity);
                log.bind(3, &job);

                static char start_date[] = "YYYY-MM-DD";
                strftime(start_date, sizeof start_date, "%Y-%m-%d", &week_beg.tm());

                string msg("Moving user volume projections for week of ");
                msg.append(start_date);
                log.set(2, msg);  // 1 & 3 were bound to program variables
                log.execute();

                dml_statement dml(conn);
                dml.execute("DELETE FROM dmd_vol_upload_outbound WHERE 1 = 1");

                dml.prepare("INSERT INTO dmd_vol_upload_outbound "
                            "(origin, destination, day_of_week, "
                             "effective_date, discontinue_date, volume) "
                            "(SELECT origin, destination, day_of_week, "
                                    "?, ?, volume "
                             "FROM dmd_vol_upload "
                             "WHERE effective_date <= ? "
                               "AND discontinue_date >= ?");
                dml.set(1, week_beg);
                dml.set(2, week_end);
                dml.set(3, week_beg);
                dml.set(4, week_end);
                dml.execute();

                size_t rows = dml.results();
                if (rows > 0)
                {
                    conn.commit();

                    ostringstream os;
                    os << "Moved " << rows << " rows.";
                    log.set(2, os.string());
                    log.execute();
                }
                else
                {
                    conn.rollback();
                    severity.assign("SEVERE");  // severity is a bound variable
                    log.set(2, "No data found.");
                    log.execute();
                    throw exception("No data found in DMD_VOL_UPLOAD");
                }
            }

            completion_code = EXIT_SUCCESS;
        }
        catch (const sql_error& e)
        {
            cerr << "SQL error " << e.sqlcode() << ", SQLSTATE \""
                 << e.sqlstate() << "\", \"" << e.errmsg() << "\"\n";
        }
        catch (const exception& e)
        {
            cerr << e.what() << '\n';
        }
        catch (...)
        {
            cerr << "Unrecognized exception thrown.\n";
        }

        return completion_code;
    }

    #include "tdb.hpp"
    using namespace tdb;

    #include <exception>
    #include <iostream>
    #include <sstream>
    #include <string>

    #include <cstdlib>
    #include <ctime>

    using namespace std;

    typedef unsigned long long job_id;

    int main(int, char** argv)
    {
        int completion_code = EXIT_FAILURE;

        try
        {
            connection conn("credentials", connection::access_mode::read_write);

            string flag;
            prepared_query<> source = conn.prepare_query(
                "SELECT outbound_source FROM dmd_vol_config");
            source().into(flag);
            if (flag == "U")
            {
                transaction trans(conn);

                job_id job = strtoull(argv[1], NULL, 0);

                prepared_query<job_id> qry = conn.prepare_query(
                    "SELECT h.start_horizon, h.end_horizon "
                    "FROM ref_plan_horizon h, job_job j, job_calendar c "
                    "WHERE j.job_id = ? "
                      "AND c.calendar_id = j.calendar_id "
                      "AND h.horizon_id = c.horizon_id");

                date week_beg, week_end;
                qry(job).into(week_beg, week_end);

                static char start_date[] = "YYYY-MM-DD";
                strftime(start_date, sizeof start_date, "%Y-%m-%d", &week_beg.tm());

                string msg("Moving user volume projections for week of ");
                msg.append(start_date);

                prepared_call<string, string, job_id> log = conn.prepare_call(
                    "{call pkg_tops_util.sp_logger(?, "
                        "'move_user_projections', ?, NULL, "
                        "'move_user_projections', ?)}");
                log("INFO", msg, job);

                prepared_statement<date, date, date, date> dml =
                    conn.prepare_statement(
                        "INSERT INTO dmd_vol_upload_outbound "
                        "(origin, destination, day_of_week, "
                         "effective_date, discontinue_date, volume) "
                        "(SELECT origin, destination, day_of_week, ?, ?, volume "
                         "FROM dmd_vol_upload "
                         "WHERE effective_date <= ? "
                           "AND discontinue_date >= ?)");

                size_t rows = dml(week_beg, week_end, week_beg, week_end);
                if (rows > 0)
                {
                    trans.commit();

                    ostringstream os;
                    os << "Moved " << rows << " rows.";
                    log("INFO", os.string(), job);
                }
                else
                {
                    trans.rollback();  // or just let the dtor do it
                    log("SEVERE", "No data found.", job);
                    throw exception("No data found in DMD_VOL_UPLOAD");
                }
            }

            completion_code = EXIT_SUCCESS;
        }
        //
        // How about an exception that stores a vendor-specific error code
        // (SQLCODE) and a SQLSTATE value for databases that support it?
        //
        catch (const exception& e)
        {
            cerr << e.what() << '\n';
        }
        catch (...)
        {
            cerr < "Unrecognized exception thrown.\n";
        }

        return completion_code;
    }

Reply to Bill Seymour <`stdbill.h`@`pobox.com`>