| Doc No: | N3459 = 12-0149 |
| Date: | 2012-10-13 |
| Reply to: | Bill Seymour <stdbill.h@pobox.com> |
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.)
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.
“If this were my employer’s opinion, I wouldn’t be allowed to post it.” — Norman Diamond
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
#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();
}
}
}
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;
}