From effe0f97e4b869dd2b333fe7f213d55f3363cb82 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Thu, 13 Nov 2014 13:54:29 +0200 Subject: Bulk update implementation --- odb/mssql/error.cxx | 4 +- odb/mssql/simple-object-statements.hxx | 22 ++- odb/mssql/simple-object-statements.txx | 18 ++- odb/mssql/statement.cxx | 284 ++++++++++++++++++++------------- odb/mssql/statement.hxx | 63 ++++++-- odb/mssql/statement.ixx | 4 +- 6 files changed, 258 insertions(+), 137 deletions(-) diff --git a/odb/mssql/error.cxx b/odb/mssql/error.cxx index acc48dc..23d5be9 100644 --- a/odb/mssql/error.cxx +++ b/odb/mssql/error.cxx @@ -191,7 +191,7 @@ namespace odb break; else if (SQL_SUCCEEDED (r) && n != SQL_NO_ROW_NUMBER && - n == SQL_ROW_NUMBER_UNKNOWN && + n != SQL_ROW_NUMBER_UNKNOWN && n != static_cast (pos + 1)) // 1-based continue; } @@ -237,6 +237,8 @@ namespace odb 0, 0); + cerr << i << " "; + // check error if (n == SQL_NO_ROW_NUMBER) cerr << "not associated with any row" << endl; diff --git a/odb/mssql/simple-object-statements.hxx b/odb/mssql/simple-object-statements.hxx index 7c5c868..dbe1f30 100644 --- a/odb/mssql/simple-object-statements.hxx +++ b/odb/mssql/simple-object-statements.hxx @@ -278,7 +278,7 @@ namespace odb image_type& image (std::size_t i = 0) { - return image_[i]; + return images_[i].obj; } // Insert binding. @@ -323,7 +323,7 @@ namespace odb // Object id image and binding. // id_image_type& - id_image (std::size_t i = 0) {return id_image_[i];} + id_image (std::size_t i = 0) {return images_[i].id;} std::size_t id_image_version () const {return id_image_version_;} @@ -388,7 +388,8 @@ namespace odb new (details::shared) update_statement_type ( conn_, object_traits::update_statement, - object_traits::versioned, // Process if versioned. + true, // Unique (0 or 1). + object_traits::versioned, // Process if versioned. update_image_binding_, object_traits::rowversion, false)); @@ -434,7 +435,7 @@ namespace odb extra_statement_cache () { return extra_statement_cache_.get ( - conn_, image_, id_image_binding_, od_.id_image_binding ()); + conn_, images_[0].obj, id_image_binding_, od_.id_image_binding ()); } public: @@ -484,7 +485,17 @@ namespace odb extra_statement_cache_ptr extra_statement_cache_; - image_type image_[object_traits::batch]; + // The UPDATE statement uses both the object and id image. Keep + // them next to each other so that the same skip distance can + // be used in batch binding. + // + struct images + { + image_type obj; + id_image_type id; + }; + + images images_[object_traits::batch]; SQLUSMALLINT status_[object_traits::batch]; // Select binding. @@ -517,7 +528,6 @@ namespace odb // Id image binding (only used as a parameter or in OUTPUT for // auto id and version). Uses the suffix in the update bind. // - id_image_type id_image_[object_traits::batch]; std::size_t id_image_version_; binding id_image_binding_; diff --git a/odb/mssql/simple-object-statements.txx b/odb/mssql/simple-object-statements.txx index fa1b6e7..a0626fa 100644 --- a/odb/mssql/simple-object-statements.txx +++ b/odb/mssql/simple-object-statements.txx @@ -46,28 +46,32 @@ namespace odb insert_image_binding_ (insert_image_bind_, insert_column_count, object_traits::batch, - sizeof (image_type), + sizeof (images), status_), update_image_binding_ (update_image_bind_, update_column_count + id_column_count + - managed_optimistic_column_count), + managed_optimistic_column_count, + object_traits::batch, + sizeof (images), + status_), id_image_binding_ (update_image_bind_ + update_column_count, id_column_count, object_traits::batch, - sizeof (id_image_type), + sizeof (images), status_), od_ (update_image_bind_ + update_column_count) { - image_[0].version = 0; // @@ TODO [0] + images_[0].obj.version = 0; // @@ TODO [0] + images_[0].id.version = 0; // @@ TODO + select_image_version_ = 0; insert_image_version_ = 0; update_image_version_ = 0; update_id_image_version_ = 0; - - id_image_[0].version = 0; // @@ TODO id_image_version_ = 0; - select_image_binding_.change_callback = image_[0].change_callback (); + select_image_binding_.change_callback = + images_[0].obj.change_callback (); std::memset (insert_image_bind_, 0, sizeof (insert_image_bind_)); std::memset (update_image_bind_, 0, sizeof (update_image_bind_)); diff --git a/odb/mssql/statement.cxx b/odb/mssql/statement.cxx index e49627c..22b1de9 100644 --- a/odb/mssql/statement.cxx +++ b/odb/mssql/statement.cxx @@ -783,12 +783,12 @@ namespace odb bool ok (SQL_SUCCEEDED (r) || r == SQL_NO_DATA); // If the statement failed as a whole, assume no parameter sets - // were attempted. Otherwise, the documentation says that the - // native client driver keeps processing remaining sets even - // in case of an error. + // were attempted in case of a batch. Otherwise, the documentation + // says that the native client driver keeps processing remaining + // sets even in case of an error. // i_ = 0; - n_ = (ok ? n : 0); + n_ = (ok ? n : (status_ == 0 ? 1 : 0)); if (mex_ != 0) { @@ -828,6 +828,73 @@ namespace odb return r; } + size_t bulk_statement:: + extract_errors () + { + size_t e (0); + + for (size_t i (0); i != n_; ++i) + { + if (status_[i] != SQL_PARAM_SUCCESS && + status_[i] != SQL_PARAM_SUCCESS_WITH_INFO) + { + translate_error (SQL_ERROR, conn_, stmt_, i, mex_); + e++; + } + } + + return e; + } + + unsigned long long bulk_statement:: + affected (SQLRETURN r, size_t errors, bool unique) + { + unsigned long long rows (0); + + // SQL_NO_DATA indicates that the statement hasn't affected any rows. + // + if (r != SQL_NO_DATA) + { + SQLLEN n; + r = SQLRowCount (stmt_, &n); + + if (!SQL_SUCCEEDED (r)) + translate_error (r, conn_, stmt_); + + // If all the parameter sets failed, then the returned count is -1, + // which means "not available" according to the documentation. + // + rows = (n != -1 ? static_cast (n) : 0); + } + + cerr << "total: " << rows << endl; + + if (n_ > 1) // Batch. + { + if (rows != 0) // Some rows did get affected. + { + // Subtract the parameter sets that failed since they haven't + // affected any rows. + // + size_t p (n_ - errors); + + if (p > 1) // True batch. + { + if (unique) // Each can affect 0 or 1 row. + { + rows = (p == static_cast (rows) + ? 1 + : result_unknown); + } + else + rows = result_unknown; + } + } + } + + return rows; + } + // // select_statement // @@ -1085,10 +1152,29 @@ namespace odb // (value in n_). Note that in the OUTPUT case if there is an // error, the processed count seems to jump by 2 for some reason. // + // The OUTPUT case can be handled in two different ways: we can + // "execute" (with SQLMoreResults()) each set as the user moves + // from one result to the next (result() call). The advantage of + // this approach is that the returned data ends up in the right + // place automatically. The potential drawback is that the total + // affected row count will only be available at the end. As a + // result, this approach probably won't work if we need to handle, + // say, UPDATE with OUTPUT (SQLRowCount() does not return an + // intermediate total, at least not for INSERT). + // + // The alternative implementation would call SQLMoreResults() + // inside execute() until all the parameter sets are executed. + // In this case we will have to copy the extracted data into + // the right place in the bindings (or update the binding before + // each call to SQLMoreResults()). It is also not clear whether + // the diagnostic records for the failed sets would accumulate. + // If not, those will have to be stashed into mex on each + // iteration. + // SQLRETURN r (bulk_statement::execute (n, mex)); // Statement failed as a whole, assume no parameter sets were - // attempted. + // attempted in case of a batch. // if (!SQL_SUCCEEDED (r)) { @@ -1096,8 +1182,8 @@ namespace odb return n_; } - if (n == 1) // Note: not n_; n and n_ not be the same, see command above. - fetch (SQL_SUCCESS); // Non-batch case. + if (status_ == 0) // Non-batch case. + fetch (SQL_SUCCESS); else fetch (status_[i_] == SQL_PARAM_SUCCESS || status_[i_] == SQL_PARAM_SUCCESS_WITH_INFO @@ -1209,7 +1295,7 @@ namespace odb } } - // Fetch the row containing the id/version if this statement if + // Fetch the row containing the id/version if this statement is // returning. // if (result_ && (returning_id_ || returning_version_)) @@ -1313,12 +1399,15 @@ namespace odb update_statement:: update_statement (connection_type& conn, const string& text, + bool unique, bool process, binding& param, bool returning_version) - : statement (conn, - text, statement_update, - (process ? ¶m : 0), false), + : bulk_statement (conn, + text, statement_update, + (process ? ¶m : 0), false, + param.batch, param.skip, param.status), + unique_ (unique), returning_version_ (returning_version) { if (!empty ()) @@ -1333,14 +1422,17 @@ namespace odb update_statement:: update_statement (connection_type& conn, const char* text, + bool unique, bool process, binding& param, bool returning_version, bool copy_text) - : statement (conn, - text, statement_update, - (process ? ¶m : 0), false, - copy_text), + : bulk_statement (conn, + text, statement_update, + (process ? ¶m : 0), false, + param.batch, param.skip, param.status, + copy_text), + unique_ (unique), returning_version_ (returning_version) { if (!empty ()) @@ -1367,53 +1459,77 @@ namespace odb translate_error (r, conn_, stmt_); } - unsigned long long update_statement:: - execute () + size_t update_statement:: + execute (size_t n, multiple_exceptions* mex) { - SQLRETURN r (statement::execute ()); - - // SQL_NO_DATA indicates that the statement hasn't affected any rows. + // In batch UPDATE without the OUTPUT clause (which is the + // only kind we currently support) all the parameter sets + // are processed inside SQLExecute() and the total count of + // affected rows is available after it returns. // - if (r == SQL_NO_DATA) - return 0; + assert (!returning_version_ || n == 1); - if (!SQL_SUCCEEDED (r)) - translate_error (r, conn_, stmt_); + SQLRETURN r (bulk_statement::execute (n, mex)); - // Get the number of affected rows. + // Statement failed as a whole, assume no parameter sets were + // attempted in case of a batch. // - SQLLEN rows; - r = SQLRowCount (stmt_, &rows); - - if (!SQL_SUCCEEDED (r)) - translate_error (r, conn_, stmt_); + if (!(SQL_SUCCEEDED (r) || r == SQL_NO_DATA)) + { + translate_error (r, conn_, stmt_, 0, mex_); + return n_; + } - // Fetch the row containing the version if this statement is - // returning. We still need to close the cursor even if we - // haven't updated any rows. + // Extract error information for failed parameter sets. If we do + // this after calling SQLRowCount(), all the diagnostics records + // that we need will be gone. // - if (returning_version_) - { - r = SQLFetch (stmt_); + size_t errors (n_ > 1 ? extract_errors () : 0); - if (r != SQL_NO_DATA && !SQL_SUCCEEDED (r)) - translate_error (r, conn_, stmt_); + // Figure out the affected row count. + // + result_ = affected (r, errors, unique_); + if (status_ == 0) // Non-batch case. + { + // Fetch the row containing the version if this statement is + // returning. We still need to close the cursor even if we + // haven't updated any rows. + // + if (returning_version_) { - SQLRETURN r (SQLCloseCursor (stmt_)); // Don't overwrite r. + r = SQLFetch (stmt_); - if (!SQL_SUCCEEDED (r)) + if (r != SQL_NO_DATA && !SQL_SUCCEEDED (r)) translate_error (r, conn_, stmt_); - } - if (rows != 0 && r == SQL_NO_DATA) - throw database_exception ( - 0, - "?????", - "result set expected from a statement with the OUTPUT clause"); + { + SQLRETURN r (SQLCloseCursor (stmt_)); // Don't overwrite r. + + if (!SQL_SUCCEEDED (r)) + translate_error (r, conn_, stmt_); + } + + if (result_ != 0 && r == SQL_NO_DATA) + throw database_exception ( + 0, + "?????", + "result set expected from a statement with the OUTPUT clause"); + } } - return static_cast (rows); + return n_; + } + + unsigned long long update_statement:: + result (size_t i) + { + assert ((i_ == i || i_ + 1 == i) && i < n_); + + if (i != i_) + mex_->current (++i_); // mex cannot be NULL since this is a batch. + + return result_; } // @@ -1467,91 +1583,35 @@ namespace odb SQLRETURN r (bulk_statement::execute (n, mex)); // Statement failed as a whole, assume no parameter sets were - // attempted. + // attempted in case of a batch. // if (!(SQL_SUCCEEDED (r) || r == SQL_NO_DATA)) { - fetch (r); + translate_error (r, conn_, stmt_, 0, mex_); return n_; } - // Figure out the affected row count. SQL_NO_DATA indicates that - // the statement hasn't affected any rows. + // Extract error information for failed parameter sets. If we do + // this after calling SQLRowCount(), all the diagnostics records + // that we need will be gone. // - if (r == SQL_NO_DATA) - result_ = 0; - else - { - SQLLEN rows; - r = SQLRowCount (stmt_, &rows); + size_t errors (n_ > 1 ? extract_errors () : 0); - if (!SQL_SUCCEEDED (r)) - translate_error (r, conn_, stmt_); - - result_ = static_cast (rows); - } - - cerr << "total: " << result_ << endl; - - if (n_ > 1) // Batch. - { - if (result_ != 0) // Some rows did get affected. - { - size_t p (n_); - - // Subtract the parameter sets that failed since they haven't - // affected any rows. - // - for (size_t i (0); i != n_; ++i) - if (status_[i] != SQL_PARAM_SUCCESS && - status_[i] != SQL_PARAM_SUCCESS_WITH_INFO) - p--; - - if (p > 1) // True batch. - { - if (unique_) // Each can affect 0 or 1 row. - { - result_ = (p == static_cast (result_) - ? 1 - : result_unknown); - } - else - result_ = result_unknown; - } - } - } - - if (n == 1) // n and n_ are really the same here. - fetch (SQL_SUCCESS); // Non-batch case. - else - fetch (status_[i_] == SQL_PARAM_SUCCESS || - status_[i_] == SQL_PARAM_SUCCESS_WITH_INFO - ? SQL_SUCCESS : SQL_ERROR); + // Figure out the affected row count. + // + result_ = affected (r, errors, unique_); return n_; } - void delete_statement:: - fetch (SQLRETURN r) - { - if (!SQL_SUCCEEDED (r)) - translate_error (r, conn_, stmt_, i_, mex_); // Can return. - } - unsigned long long delete_statement:: result (size_t i) { assert ((i_ == i || i_ + 1 == i) && i < n_); if (i != i_) - { mex_->current (++i_); // mex cannot be NULL since this is a batch. - fetch (status_[i_] == SQL_PARAM_SUCCESS || - status_[i_] == SQL_PARAM_SUCCESS_WITH_INFO - ? SQL_SUCCESS : SQL_ERROR); - } - return result_; } } diff --git a/odb/mssql/statement.hxx b/odb/mssql/statement.hxx index ea53a38..158964d 100644 --- a/odb/mssql/statement.hxx +++ b/odb/mssql/statement.hxx @@ -133,6 +133,7 @@ namespace odb virtual ~bulk_statement () = 0; + protected: bulk_statement (connection_type&, const std::string& text, statement_kind, @@ -160,13 +161,23 @@ namespace odb SQLRETURN execute (std::size_t n, multiple_exceptions*); + // Return the number of failed parameter sets. + // + std::size_t + extract_errors (); + + static const unsigned long long result_unknown = ~0ULL; + + unsigned long long + affected (SQLRETURN, std::size_t errors, bool unique); + private: void init (std::size_t skip); protected: - SQLULEN processed_; // Number of batch rows processed so far. - SQLUSMALLINT* status_; // Row status array. + SQLULEN processed_; // Number of parameter sets processed so far. + SQLUSMALLINT* status_; // Parameter sets status array. std::size_t n_; // Actual batch size. std::size_t i_; // Position in result. multiple_exceptions* mex_; @@ -335,28 +346,63 @@ namespace odb bool result_; }; - class LIBODB_MSSQL_EXPORT update_statement: public statement + class LIBODB_MSSQL_EXPORT update_statement: public bulk_statement { public: virtual ~update_statement (); + // SQL Server native client ODBC driver does not expose individual + // affected row counts for batch operations, even though it says it + // does (SQLGetInfo(SQL_PARAM_ARRAY_ROW_COUNTS) returns SQL_PARC_BATCH). + // Instead, it adds them all up and returns a single count. This is + // bad news for us. + // + // In case of updating by primary key (the affected row count is + // either 1 or 0), we can recognize the presumably successful case + // where the total affected row count is equal to the batch size + // (we can also recognize the "all unsuccessful" case where the + // total affected row count is 0). The unique_hint argument in the + // constructors below indicates whether this is a "0 or 1" UPDATE + // statement. + // + // In all other situations (provided this is a batch), the result() + // function below returns the special result_unknown value. + // update_statement (connection_type& conn, const std::string& text, + bool unique_hint, bool process, binding& param, bool returning_version); update_statement (connection_type& conn, const char* text, + bool unique_hint, bool process, binding& param, bool returning_version, bool copy_text = true); + // Return the number of parameter sets (out of n) that were attempted. + // + std::size_t + execute (std::size_t n = 1, multiple_exceptions* = 0); + + // Return the number of rows affected (deleted) by the parameter + // set. If this is a batch (n > 1 in execute() call above) and it + // is impossible to determine the affected row count for each + // parameter set, then this function returns result_unknown. All + // other errors are reported by throwing exceptions. + // + using bulk_statement::result_unknown; + unsigned long long - execute (); + result (std::size_t i = 0); + // Note that currently the implementation does not support batch + // with the OUTPUT clause. + // unsigned long long version (); @@ -369,8 +415,11 @@ namespace odb init_result (); private: + bool unique_; bool returning_version_; + unsigned long long result_; + unsigned char version_[8]; SQLLEN version_size_ind_; }; @@ -420,7 +469,7 @@ namespace odb // parameter set, then this function returns result_unknown. All // other errors are reported by throwing exceptions. // - static const unsigned long long result_unknown = ~0ULL; + using bulk_statement::result_unknown; unsigned long long result (std::size_t i = 0); @@ -430,10 +479,6 @@ namespace odb delete_statement& operator= (const delete_statement&); private: - void - fetch (SQLRETURN); - - private: bool unique_; unsigned long long result_; }; diff --git a/odb/mssql/statement.ixx b/odb/mssql/statement.ixx index 66316f4..642a5d7 100644 --- a/odb/mssql/statement.ixx +++ b/odb/mssql/statement.ixx @@ -18,7 +18,7 @@ namespace odb : statement (c, text, k, process, optimize), status_ (batch == 1 ? 0 : status) { - if (batch != 1) + if (batch != 1 && !empty ()) init (skip); } @@ -35,7 +35,7 @@ namespace odb : statement (c, text, k, process, optimize, copy_text), status_ (batch == 1 ? 0 : status) { - if (batch != 1) + if (batch != 1 && !empty ()) init (skip); } -- cgit v1.1