From 26b30efd0cf93478be68218090435f995809e546 Mon Sep 17 00:00:00 2001 From: Hiroshi Inoue Date: Mon, 18 May 2020 19:53:48 +0900 Subject: [PATCH] Improve execution of parameterized SQL statements with arrays of parameters by sending chunks of SQL statements. If SQL_ATTR_CURSOR_TYPE of an statement is SQL_CURSOR_FORWARD_ONLY, SQL_ATTR_CONCURRENCY is SQL_CONCUR_READ_ONLY and extended protocol isn't used, the batch execution of the statement is possible. A new option Batch Size was introduced for such cases. Batch Size: Split an array (of parameters) into chunks of Batch Size to execute statements. The last chunk may contain less than Batch Size elements. Setting 1 to this option forces the current one by one execution. Also turn off use_server_side_prepare option temporarily when batch executuion is possible. --- connection.c | 5 ++ connection.h | 1 + descriptor.c | 2 + dlg_specific.c | 12 +++ dlg_specific.h | 3 + dlg_wingui.c | 11 +++ docs/config-opt.html | 11 +++ docs/config.html | 13 ++- execute.c | 196 ++++++++++++++++++++++++++++++++++++------- pgapi30.c | 7 ++ pgapifunc.h | 1 + psqlodbc.h | 1 + psqlodbc.rc | 15 +++- resource.h | 4 +- statement.c | 34 +++++++- statement.h | 14 ++++ 16 files changed, 289 insertions(+), 41 deletions(-) diff --git a/connection.c b/connection.c index 254093a..abd8b07 100644 --- a/connection.c +++ b/connection.c @@ -991,6 +991,8 @@ receive_libpq_notice(void *arg, const PGresult *pgres) { notice_receiver_arg *nrarg = (notice_receiver_arg *) arg; + if (NULL != nrarg->stmt) + nrarg->stmt->has_notice = 1; handle_pgres_error(nrarg->conn, pgres, nrarg->comment, nrarg->res, FALSE); } } @@ -1924,6 +1926,7 @@ CC_send_query_append(ConnectionClass *self, const char *query, QueryInfo *qi, UD nrarg.conn = self; nrarg.comment = func; nrarg.res = NULL; + nrarg.stmt = stmt; PQsetNoticeReceiver(self->pqconn, receive_libpq_notice, &nrarg); QLOG(0, "PQsendQuery: %p '%s'\n", self->pqconn, query_buf.data); @@ -2077,6 +2080,8 @@ MYLOG(DETAIL_LOG_LEVEL, "Discarded a RELEASE result\n"); break; case PGRES_NONFATAL_ERROR: handle_pgres_error(self, pgres, "send_query", res, FALSE); + if (stmt) + stmt->has_notice = 1; break; case PGRES_BAD_RESPONSE: diff --git a/connection.h b/connection.h index 5ae7b9b..a002694 100644 --- a/connection.h +++ b/connection.h @@ -492,6 +492,7 @@ typedef struct ConnectionClass *conn; const char *comment; QResultClass *res; + StatementClass *stmt; } notice_receiver_arg; void receive_libpq_notice(void *arg, const PGresult *pgres); diff --git a/descriptor.c b/descriptor.c index 295b58c..407c1d6 100644 --- a/descriptor.c +++ b/descriptor.c @@ -278,10 +278,12 @@ void InitializeEmbeddedDescriptor(DescriptorClass *self, StatementClass *stmt, { case SQL_ATTR_APP_ROW_DESC: memset(&(self->ardf), 0, sizeof(ARDFields)); + InitializeARDFields(&(self->ardf)); stmt->ard = self; break; case SQL_ATTR_APP_PARAM_DESC: memset(&(self->apdf), 0, sizeof(APDFields)); + InitializeAPDFields(&(self->apdf)); stmt->apd = self; break; case SQL_ATTR_IMP_ROW_DESC: diff --git a/dlg_specific.c b/dlg_specific.c index 0e517fd..d89777a 100644 --- a/dlg_specific.c +++ b/dlg_specific.c @@ -679,6 +679,8 @@ copyConnAttributes(ConnInfo *ci, const char *attribute, const char *value) ci->keepalive_idle = atoi(value); else if (stricmp(attribute, INI_KEEPALIVEINTERVAL) == 0 || stricmp(attribute, ABBR_KEEPALIVEINTERVAL) == 0) ci->keepalive_interval = atoi(value); + else if (stricmp(attribute, INI_BATCHSIZE) == 0 || stricmp(attribute, ABBR_BATCHSIZE) == 0) + ci->batch_size = atoi(value); else if (stricmp(attribute, INI_OPTIONAL_ERRORS) == 0 || stricmp(attribute, ABBR_OPTIONAL_ERRORS) == 0) ci->optional_errors = atoi(value); else if (stricmp(attribute, INI_SSLMODE) == 0 || stricmp(attribute, ABBR_SSLMODE) == 0) @@ -1055,6 +1057,9 @@ MYLOG(0, "drivername=%s\n", drivername); if (SQLGetPrivateProfileString(DSN, INI_KEEPALIVEINTERVAL, NULL_STRING, temp, sizeof(temp), ODBC_INI) > 0) if (0 == (ci->keepalive_interval = atoi(temp))) ci->keepalive_interval = -1; + if (SQLGetPrivateProfileString(DSN, INI_BATCHSIZE, NULL_STRING, temp, sizeof(temp), ODBC_INI) > 0) + if (0 == (ci->batch_size = atoi(temp))) + ci->batch_size = DEFAULT_BATCH_SIZE; if (SQLGetPrivateProfileString(DSN, INI_SSLMODE, NULL_STRING, temp, sizeof(temp), ODBC_INI) > 0) STRCPY_FIXED(ci->sslmode, temp); @@ -1341,6 +1346,11 @@ writeDSNinfo(const ConnInfo *ci) INI_KEEPALIVEINTERVAL, temp, ODBC_INI); + ITOA_FIXED(temp, ci->batch_size); + SQLWritePrivateProfileString(DSN, + INI_BATCHSIZE, + temp, + ODBC_INI); #ifdef _HANDLE_ENLIST_IN_DTC_ ITOA_FIXED(temp, ci->xa_opt); SQLWritePrivateProfileString(DSN, INI_XAOPT, temp, ODBC_INI); @@ -1752,6 +1762,7 @@ CC_conninfo_init(ConnInfo *conninfo, UInt4 option) conninfo->disable_keepalive = -1; conninfo->keepalive_idle = -1; conninfo->keepalive_interval = -1; + conninfo->batch_size = DEFAULT_BATCH_SIZE; conninfo->wcs_debug = -1; #ifdef _HANDLE_ENLIST_IN_DTC_ conninfo->xa_opt = -1; @@ -1851,6 +1862,7 @@ CC_copy_conninfo(ConnInfo *ci, const ConnInfo *sci) CORR_VALCPY(extra_opts); CORR_VALCPY(keepalive_idle); CORR_VALCPY(keepalive_interval); + CORR_VALCPY(batch_size); #ifdef _HANDLE_ENLIST_IN_DTC_ CORR_VALCPY(xa_opt); #endif diff --git a/dlg_specific.h b/dlg_specific.h index 38e635e..20aa704 100644 --- a/dlg_specific.h +++ b/dlg_specific.h @@ -168,6 +168,8 @@ extern "C" { #define ABBR_PQOPT "D5" #define INI_OPTIONAL_ERRORS "OptionalErrors" #define ABBR_OPTIONAL_ERRORS "D7" +#define INI_BATCHSIZE "BatchSize" +#define ABBR_BATCHSIZE "D8" #define INI_DTCLOG "Dtclog" /* "PreferLibpq", abbreviated "D4", used to mean whether to prefer libpq. * libpq is now required @@ -267,6 +269,7 @@ extern "C" { #define DEFAULT_SSLMODE SSLMODE_DISABLE #define DEFAULT_NUMERIC_AS (-101) #define DEFAULT_OPTIONAL_ERRORS 0 +#define DEFAULT_BATCH_SIZE 100 #ifdef _HANDLE_ENLIST_IN_DTC_ #define DEFAULT_XAOPT 1 diff --git a/dlg_wingui.c b/dlg_wingui.c index d9dbccd..39f2681 100644 --- a/dlg_wingui.c +++ b/dlg_wingui.c @@ -208,6 +208,16 @@ MYLOG(0, "entering src=%d\n", src); SetDlgItemInt(hdlg, DRV_VARCHAR_SIZE, comval->max_varchar_size, FALSE); SetDlgItemInt(hdlg, DRV_LONGVARCHAR_SIZE, comval->max_longvarchar_size, TRUE); SetDlgItemText(hdlg, DRV_EXTRASYSTABLEPREFIXES, comval->extra_systable_prefixes); + switch (src) + { + case 1: + ShowWindow(GetDlgItem(hdlg, DS_BATCH_SIZE), SW_SHOW); + SetDlgItemInt(hdlg, DS_BATCH_SIZE, ci->batch_size, FALSE); + break; + default: + ShowWindow(GetDlgItem(hdlg, DS_BATCH_SIZE), SW_HIDE); + break; + } /* Driver Connection Settings */ EnableWindow(GetDlgItem(hdlg, DRV_CONNSETTINGS), FALSE); @@ -257,6 +267,7 @@ MYLOG(3, "entering\n"); * SQL_NO_TOTAL */ GetDlgItemText(hdlg, DRV_EXTRASYSTABLEPREFIXES, comval->extra_systable_prefixes, sizeof(comval->extra_systable_prefixes)); + ci->batch_size = GetDlgItemInt(hdlg, DS_BATCH_SIZE, NULL, FALSE); /* fall through */ return 0; diff --git a/docs/config-opt.html b/docs/config-opt.html index b4802a7..5665a43 100644 --- a/docs/config-opt.html +++ b/docs/config-opt.html @@ -496,6 +496,17 @@ D7 + + + Chunk size when executing batches for parameterized SQL statements with arrays of parameters. + + + BatchSize + + + D8 + +



diff --git a/docs/config.html b/docs/config.html index 66061b2..ce7bcc9 100644 --- a/docs/config.html +++ b/docs/config.html @@ -125,6 +125,10 @@ as System Tables. Tables that begin with "pg_" are always treated as system tables, even without this option. Separate each prefix with a semicolon (;)
  +

  • Batch Size:Chunk size when executing batches with arrays of +parameters. Setting 1 to this option forces one by one execution (the +behavior before). +
     

    • Advanced Options 2/3 Dialog Box

    @@ -157,6 +161,8 @@ option well. See the faq for details on what you need to do to your database to allow for the row versioning feature to be used.
      +
  • Display Optional Error Message: Display optional(detail, hint, statement position etc) error messages.
     
    • +
  • True is -1: Represent TRUE as -1 for compatibility with some applications.
     
    • @@ -168,22 +174,25 @@ versioning feature to be used.
      case, the query that is sent to the server for parsing will have the parameter markers replaced with the actual parameter values, or NULL literals if the values are not known yet. +
     
  • Int8 As: Define what datatype to report int8 columns as.
     
    • +
  • Numeric As: Specify the map from numeric items without precision to SQL data types. numeric(default), varchar, double or memo(SQL_LONGVARCHAR) can be specified.
     
    • +
  • Extra Opts: combination of the following bits.

      0x1: Force the output of short-length formatted connection string. Check this bit when you use MFC CDatabase class.
      0x2: Fake MS SQL Server so that MS Access recognizes PostgreSQL's serial type as AutoNumber type.
      0x4: Reply ANSI (not Unicode) char types for the inquiries from applications. Try to check this bit when your applications don't seem to be good at handling Unicode data.
     
    • -
  • Protocol: The libpq protocol version to use.

    +
  • Level of rollback on errors: Specifies what to rollback should an error occur.
      diff --git a/execute.c b/execute.c index e9419e9..337c808 100644 --- a/execute.c +++ b/execute.c @@ -203,7 +203,7 @@ inquireHowToPrepare(const StatementClass *stmt) conn = SC_get_conn(stmt); ci = &(conn->connInfo); - if (!ci->use_server_side_prepare) + if (!stmt->use_server_side_prepare) { /* Do prepare operations by the driver itself */ return PREPARE_BY_THE_DRIVER; @@ -403,18 +403,41 @@ const char *GetSvpName(const ConnectionClass *conn, char *wrk, int wrksize) /* * The execution after all parameters were resolved. */ + +#define INVALID_EXPBUFFER PQExpBufferDataBroken(stmt->stmt_deffered) +#define VALID_EXPBUFFER (!PQExpBufferDataBroken(stmt->stmt_deffered)) + +static +void param_status_batch_update(IPDFields *ipdopts, RETCODE retval, SQLLEN target_row, int count_of_deffered) +{ + if (NULL != ipdopts->param_status_ptr) + { + for (int i = target_row, j = 0; i >= 0 && j <= count_of_deffered; i--) + { + if (SQL_PARAM_UNUSED != ipdopts->param_status_ptr[i]) + { + ipdopts->param_status_ptr[i] = retval; + j++; + } + } + } +} + static -RETCODE Exec_with_parameters_resolved(StatementClass *stmt, BOOL *exec_end) +RETCODE Exec_with_parameters_resolved(StatementClass *stmt, EXEC_TYPE exec_type, BOOL *exec_end) { CSTR func = "Exec_with_parameters_resolved"; RETCODE retval; - SQLLEN end_row; + SQLLEN start_row, end_row; SQLINTEGER cursor_type, scroll_concurrency; ConnectionClass *conn; QResultClass *res; APDFields *apdopts; IPDFields *ipdopts; BOOL prepare_before_exec = FALSE; + char *stmt_with_params; + SQLLEN status_row = stmt->exec_current_row; + int count_of_deffered; *exec_end = FALSE; conn = SC_get_conn(stmt); @@ -430,15 +453,33 @@ RETCODE Exec_with_parameters_resolved(StatementClass *stmt, BOOL *exec_end) if (HowToPrepareBeforeExec(stmt, FALSE) >= allowParse) prepare_before_exec = TRUE; -MYLOG(DETAIL_LOG_LEVEL, "prepare_before_exec=%d srv=%d\n", prepare_before_exec, conn->connInfo.use_server_side_prepare); +MYLOG(DETAIL_LOG_LEVEL, "prepare_before_exec=%d srv=%d\n", prepare_before_exec, stmt->use_server_side_prepare); /* Create the statement with parameters substituted. */ - retval = copy_statement_with_parameters(stmt, prepare_before_exec); - stmt->current_exec_param = -1; - if (retval != SQL_SUCCESS) + stmt_with_params = stmt->stmt_with_params; + if (LAST_EXEC == exec_type) { - stmt->exec_current_row = -1; - *exec_end = TRUE; - RETURN(retval) /* error msg is passed from the above */ + if (NULL != stmt_with_params) + { + free(stmt_with_params); + stmt_with_params = stmt->stmt_with_params = NULL; + } + if (INVALID_EXPBUFFER || + !stmt->stmt_deffered.data[0]) + RETURN(SQL_SUCCESS); + } + else + { + retval = copy_statement_with_parameters(stmt, prepare_before_exec); + stmt->current_exec_param = -1; + if (retval != SQL_SUCCESS) + { + stmt->exec_current_row = -1; + *exec_end = TRUE; + RETURN(retval) /* error msg is passed from the above */ + } + stmt_with_params = stmt->stmt_with_params; + if (!stmt_with_params) // Extended Protocol + exec_type = DIRECT_EXEC; } MYLOG(0, " stmt_with_params = '%s'\n", stmt->stmt_with_params); @@ -446,10 +487,80 @@ MYLOG(DETAIL_LOG_LEVEL, "prepare_before_exec=%d srv=%d\n", prepare_before_exec, /* * The real execution. */ -MYLOG(0, "about to begin SC_execute\n"); - retval = SC_execute(stmt); +MYLOG(0, "about to begin SC_execute exec_type=%d\n", exec_type); + ipdopts = SC_get_IPDF(stmt); + apdopts = SC_get_APDF(stmt); + if (start_row = stmt->exec_start_row, start_row < 0) + start_row = 0; + if (end_row = stmt->exec_end_row, end_row < 0) + { + end_row = (SQLINTEGER) apdopts->paramset_size - 1; + if (end_row < 0) + end_row = 0; + } + if (LAST_EXEC == exec_type && + NULL != ipdopts->param_status_ptr) + { + for (int i = end_row; i >= start_row; i--) + { + if (SQL_PARAM_UNUSED != ipdopts->param_status_ptr[i]) + { + status_row = i; + break; + } + } + } + count_of_deffered = stmt->count_of_deffered; + if (DIRECT_EXEC == exec_type) + { + retval = SC_execute(stmt); + stmt->count_of_deffered = 0; + } + else if (DEFFERED_EXEC == exec_type && + stmt->exec_current_row < end_row && + stmt->count_of_deffered + 1 < stmt->batch_size) + { + if (INVALID_EXPBUFFER) + initPQExpBuffer(&stmt->stmt_deffered); + if (INVALID_EXPBUFFER) + { + retval = SQL_ERROR; + SC_set_error(stmt, STMT_NO_MEMORY_ERROR, "Out of memory", __FUNCTION__); + } + else + { + if (NULL != stmt_with_params) + { + if (stmt->stmt_deffered.data[0]) + appendPQExpBuffer(&stmt->stmt_deffered, ";%s", stmt_with_params); + else + printfPQExpBuffer(&stmt->stmt_deffered, "%s", stmt_with_params); + } + if (NULL != ipdopts->param_status_ptr) + ipdopts->param_status_ptr[stmt->exec_current_row] = SQL_PARAM_SUCCESS; // set without exec + stmt->count_of_deffered++; + stmt->exec_current_row++; + RETURN(SQL_SUCCESS); + } + } + else + { + if (VALID_EXPBUFFER) + { + if (NULL != stmt_with_params) + appendPQExpBuffer(&stmt->stmt_deffered, ";%s", stmt_with_params); + stmt->stmt_with_params = stmt->stmt_deffered.data; + } + retval = SC_execute(stmt); + stmt->stmt_with_params = stmt_with_params; + stmt->count_of_deffered = 0; + if (VALID_EXPBUFFER) + resetPQExpBuffer(&stmt->stmt_deffered); + } if (retval == SQL_ERROR) { +MYLOG(0, "count_of_deffered=%d\n", count_of_deffered); + param_status_batch_update(ipdopts, SQL_PARAM_ERROR, stmt->exec_current_row, count_of_deffered); stmt->exec_current_row = -1; *exec_end = TRUE; RETURN(retval) @@ -477,21 +588,18 @@ MYLOG(0, "about to begin SC_execute\n"); switch (retval) { case SQL_SUCCESS: - ipdopts->param_status_ptr[stmt->exec_current_row] = SQL_PARAM_SUCCESS; + ipdopts->param_status_ptr[status_row] = SQL_PARAM_SUCCESS; break; case SQL_SUCCESS_WITH_INFO: - ipdopts->param_status_ptr[stmt->exec_current_row] = SQL_PARAM_SUCCESS_WITH_INFO; +MYLOG(0, "count_of_deffered=%d has_notice=%d\n", count_of_deffered, stmt->has_notice); + param_status_batch_update(ipdopts, (count_of_deffered > 0 && !stmt->has_notice) ? SQL_PARAM_SUCCESS : SQL_PARAM_SUCCESS_WITH_INFO, status_row, count_of_deffered); break; default: - ipdopts->param_status_ptr[stmt->exec_current_row] = SQL_PARAM_ERROR; + param_status_batch_update(ipdopts, SQL_PARAM_ERROR, status_row, count_of_deffered); break; } } - if (end_row = stmt->exec_end_row, end_row < 0) - { - apdopts = SC_get_APDF(stmt); - end_row = (SQLINTEGER) apdopts->paramset_size - 1; - } + stmt->has_notice = 0; if (stmt->exec_current_row >= end_row) { *exec_end = TRUE; @@ -503,9 +611,6 @@ MYLOG(0, "about to begin SC_execute\n"); { EnvironmentClass *env = (EnvironmentClass *) CC_get_env(conn); const char *cmd = QR_get_command(res); - SQLLEN start_row; - if (start_row = stmt->exec_start_row, start_row < 0) - start_row = 0; if (retval == SQL_SUCCESS && NULL != cmd && @@ -824,11 +929,12 @@ PGAPI_Execute(HSTMT hstmt, UWORD flag) APDFields *apdopts; IPDFields *ipdopts; SQLLEN i, start_row, end_row; - BOOL exec_end, recycled = FALSE, recycle = TRUE; + BOOL exec_end = FALSE, recycled = FALSE, recycle = TRUE; SQLSMALLINT num_params; MYLOG(0, "entering...%x\n", flag); + stmt->has_notice = 0; conn = SC_get_conn(stmt); apdopts = SC_get_APDF(stmt); @@ -898,7 +1004,11 @@ PGAPI_Execute(HSTMT hstmt, UWORD flag) if (start_row = stmt->exec_start_row, start_row < 0) start_row = 0; if (end_row = stmt->exec_end_row, end_row < 0) + { end_row = (SQLINTEGER) apdopts->paramset_size - 1; + if (end_row < 0) + end_row = 0; + } if (stmt->exec_current_row < 0) stmt->exec_current_row = start_row; ipdopts = SC_get_IPDF(stmt); @@ -912,9 +1022,18 @@ PGAPI_Execute(HSTMT hstmt, UWORD flag) parameters even in case of non-prepared statements. */ int nCallParse = doNothing; - + BOOL maybeBatch = FALSE; + + if (end_row > start_row && + SQL_CURSOR_FORWARD_ONLY == stmt->options.cursor_type && + SQL_CONCUR_READ_ONLY == stmt->options.scroll_concurrency && + stmt->batch_size > 1) + maybeBatch = TRUE; +MYLOG(0, "prepare=%d prepared=%d batch_size=%d start_row=" FORMAT_LEN "end_row=" FORMAT_LEN " => maybeBatch=%d\n", stmt->prepare, stmt->prepared, stmt->batch_size, start_row, end_row, maybeBatch); if (NOT_YET_PREPARED == stmt->prepared) { + if (maybeBatch) + stmt->use_server_side_prepare = 0; switch (nCallParse = HowToPrepareBeforeExec(stmt, TRUE)) { case shouldParse: @@ -930,6 +1049,13 @@ MYLOG(0, "prepareParameters was %s called, prepare state:%d\n", shouldParse == n { SC_set_Result(stmt, NULL); } + if (0 != (PREPARE_BY_THE_DRIVER & stmt->prepare) && + maybeBatch) + stmt->exec_type = DEFFERED_EXEC; + else + stmt->exec_type = DIRECT_EXEC; + +MYLOG(0, "prepare=%d maybeBatch=%d exec_type=%d\n", stmt->prepare, maybeBatch, stmt->exec_type); if (ipdopts->param_processed_ptr) *ipdopts->param_processed_ptr = 0; /* @@ -951,19 +1077,27 @@ MYLOG(0, "prepareParameters was %s called, prepare state:%d\n", shouldParse == n } next_param_row: - if (apdopts->param_operation_ptr) + if (stmt->exec_current_row > end_row) + exec_end = TRUE; + else if (apdopts->param_operation_ptr) { while (apdopts->param_operation_ptr[stmt->exec_current_row] == SQL_PARAM_IGNORE) { if (stmt->exec_current_row >= end_row) { - stmt->exec_current_row = -1; - retval = SQL_SUCCESS; - goto cleanup; + exec_end = TRUE; + break; } ++stmt->exec_current_row; } } + if (exec_end) + { + if (DEFFERED_EXEC == stmt->exec_type ) + retval = Exec_with_parameters_resolved(stmt, LAST_EXEC, &exec_end); + stmt->exec_current_row = -1; + goto cleanup; + } /* * Initialize the current row status */ @@ -1042,7 +1176,7 @@ next_param_row: if (0 != (flag & PODBC_WITH_HOLD)) SC_set_with_hold(stmt); - retval = Exec_with_parameters_resolved(stmt, &exec_end); + retval = Exec_with_parameters_resolved(stmt, stmt->exec_type, &exec_end); if (!exec_end) { stmt->curr_param_result = 0; @@ -1329,7 +1463,7 @@ MYLOG(DETAIL_LOG_LEVEL, "ipdopts=%p\n", ipdopts); BOOL exec_end; UWORD flag = SC_is_with_hold(stmt) ? PODBC_WITH_HOLD : 0; - retval = Exec_with_parameters_resolved(estmt, &exec_end); + retval = Exec_with_parameters_resolved(estmt, stmt->exec_type, &exec_end); if (exec_end) { /**SC_reset_delegate(retval, stmt);**/ diff --git a/pgapi30.c b/pgapi30.c index 1fc7101..3f8203d 100644 --- a/pgapi30.c +++ b/pgapi30.c @@ -458,6 +458,9 @@ PGAPI_GetConnectAttr(HDBC ConnectionHandle, case SQL_ATTR_PGOPT_MSJET: *((SQLINTEGER *) Value) = conn->ms_jet; break; + case SQL_ATTR_PGOPT_BATCHSIZE: + *((SQLINTEGER *) Value) = conn->connInfo.batch_size; + break; default: ret = PGAPI_GetConnectOption(ConnectionHandle, (UWORD) Attribute, Value, &len, BufferLength); } @@ -1785,6 +1788,10 @@ PGAPI_SetConnectAttr(HDBC ConnectionHandle, conn->ms_jet = CAST_PTR(SQLINTEGER, Value); MYLOG(0, "ms_jet => %d\n", conn->ms_jet); break; + case SQL_ATTR_PGOPT_BATCHSIZE: + conn->connInfo.batch_size = CAST_PTR(SQLINTEGER, Value); + MYLOG(0, "batch size => %d\n", conn->connInfo.batch_size); + break; default: if (Attribute < 65536) ret = PGAPI_SetConnectOption(ConnectionHandle, (SQLUSMALLINT) Attribute, (SQLLEN) Value); diff --git a/pgapifunc.h b/pgapifunc.h index 58a6ff9..9469b52 100644 --- a/pgapifunc.h +++ b/pgapifunc.h @@ -314,6 +314,7 @@ enum { ,SQL_ATTR_PGOPT_MAXLONGVARCHARSIZE = 65547 ,SQL_ATTR_PGOPT_WCSDEBUG = 65548 ,SQL_ATTR_PGOPT_MSJET = 65549 + ,SQL_ATTR_PGOPT_BATCHSIZE = 65550 }; RETCODE SQL_API PGAPI_SetConnectAttr(HDBC ConnectionHandle, SQLINTEGER Attribute, PTR Value, diff --git a/psqlodbc.h b/psqlodbc.h index 4e29506..dc12dc2 100644 --- a/psqlodbc.h +++ b/psqlodbc.h @@ -642,6 +642,7 @@ typedef struct UInt4 extra_opts; Int4 keepalive_idle; Int4 keepalive_interval; + Int4 batch_size; #ifdef _HANDLE_ENLIST_IN_DTC_ signed char xa_opt; #endif /* _HANDLE_ENLIST_IN_DTC_ */ diff --git a/psqlodbc.rc b/psqlodbc.rc index 224665a..da452bb 100644 --- a/psqlodbc.rc +++ b/psqlodbc.rc @@ -80,7 +80,8 @@ END #define MISC_X #define MISC_Y 155 #define MISC_Y1 (MISC_Y+11) -#define MISC_Y2 (MISC_Y+33) +#define MISC_Y2 (MISC_Y+28) +#define MISC_Y3 (MISC_Y+45) DLG_OPTIONS_DRV DIALOG DISCARDABLE 0, 0, 350, 241 STYLE DS_MODALFRAME | DS_3DLOOK | DS_CENTER | WS_POPUP | WS_CAPTION | @@ -118,7 +119,7 @@ BEGIN "Button",BS_AUTOCHECKBOX | WS_TABSTOP,123,132,117,10 CONTROL "Char‚Æ‚µ‚ÄBools‚ðˆµ‚¤",DRV_BOOLS_CHAR,"Button", BS_AUTOCHECKBOX | WS_TABSTOP,243,132,96,10 - GROUPBOX "‚»‚Ì‘¼",IDC_STATIC,5,MISC_Y,340,54 + GROUPBOX "‚»‚Ì‘¼",IDC_STATIC,5,MISC_Y,340,57 LTEXT "Å‘å&Varchar:",IDC_STATIC,13,MISC_Y1+1,54,8 EDITTEXT DRV_VARCHAR_SIZE,70,MISC_Y1,35,12,ES_AUTOHSCROLL LTEXT "Å‘åLon&gVarChar:",IDC_STATIC,148,MISC_Y1+2,66,8 @@ -127,6 +128,8 @@ BEGIN EDITTEXT DRV_CACHE_SIZE,69,MISC_Y2-1,35,12,ES_AUTOHSCROLL LTEXT "¼½ÃÑðÌÞÙ ÌßŲ́¸½:",IDC_STATIC,140,MISC_Y2+1,74,9 EDITTEXT DRV_EXTRASYSTABLEPREFIXES,219,MISC_Y2-1,71,12,ES_AUTOHSCROLL + LTEXT "ƒoƒbƒ`ƒTƒCƒY:",IDC_STATIC,13,MISC_Y3+1,74,9 + EDITTEXT DS_BATCH_SIZE,69,MISC_Y3-1,35,12,ES_AUTOHSCROLL DEFPUSHBUTTON "OK",IDOK,5,221,50,15,WS_GROUP PUSHBUTTON "ƒLƒƒƒ“ƒZƒ‹",IDCANCEL,68,221,50,15 PUSHBUTTON "“K—p",IDAPPLY,132,221,50,15 @@ -537,9 +540,11 @@ END #undef MISC_Y #undef MISC_Y1 #undef MISC_Y2 +#undef MISC_Y3 #define MISC_Y 145 #define MISC_Y1 (MISC_Y+15) -#define MISC_Y2 (MISC_Y+37) +#define MISC_Y2 (MISC_Y+32) +#define MISC_Y3 (MISC_Y+49) DLG_OPTIONS_DRV DIALOG DISCARDABLE 0, 0, 287, 231 STYLE DS_MODALFRAME | DS_CENTER | WS_POPUP | WS_CAPTION | WS_SYSMENU @@ -576,7 +581,7 @@ BEGIN "Button",BS_AUTOCHECKBOX | WS_TABSTOP,105,125,105,10 CONTROL "Bools as Char",DRV_BOOLS_CHAR,"Button",BS_AUTOCHECKBOX | WS_TABSTOP,215,125,67,10 - GROUPBOX "Miscellaneous",IDC_STATIC,5,MISC_Y,277,58 + GROUPBOX "Miscellaneous",IDC_STATIC,5,MISC_Y,277,62 LTEXT "Max &Varchar:",IDC_STATIC,13,MISC_Y1+1,54,8 EDITTEXT DRV_VARCHAR_SIZE,70,MISC_Y1,35,12,ES_AUTOHSCROLL LTEXT "Max Lon&gVarChar:",IDC_STATIC,125,MISC_Y1+1,67,8 @@ -585,6 +590,8 @@ BEGIN EDITTEXT DRV_CACHE_SIZE,69,MISC_Y2-1,35,12,ES_AUTOHSCROLL LTEXT "SysTable &Prefixes:",IDC_STATIC,126,MISC_Y2+1,61,18 EDITTEXT DRV_EXTRASYSTABLEPREFIXES,199,MISC_Y2-1,71,12,ES_AUTOHSCROLL + LTEXT "&Batch &Size:",IDC_STATIC,14,MISC_Y3+1,61,18 + EDITTEXT DS_BATCH_SIZE,69,MISC_Y3-1,35,12,ES_AUTOHSCROLL DEFPUSHBUTTON "OK",IDOK,5,212,50,14,WS_GROUP PUSHBUTTON "Cancel",IDCANCEL,81,211,50,15 PUSHBUTTON "Apply",IDAPPLY,156,212,50,14 diff --git a/resource.h b/resource.h index fe29e2d..7b14173 100644 --- a/resource.h +++ b/resource.h @@ -117,6 +117,8 @@ #define DS_NUMERIC_AS_VARCHAR 1109 #define DS_NUMERIC_AS_DOUBLE 1110 #define DS_NUMERIC_AS_LONGVARCHAR 1111 +#define DS_ARRAY_BATCH_EXEC 1112 +#define DS_BATCH_SIZE 1113 // Next default values for new objects // @@ -124,7 +126,7 @@ #ifndef APSTUDIO_READONLY_SYMBOLS #define _APS_NEXT_RESOURCE_VALUE 106 #define _APS_NEXT_COMMAND_VALUE 40001 -#define _APS_NEXT_CONTROL_VALUE 1112 +#define _APS_NEXT_CONTROL_VALUE 1114 #define _APS_NEXT_SYMED_VALUE 101 #endif #endif diff --git a/statement.c b/statement.c index 4255f92..540d5b9 100644 --- a/statement.c +++ b/statement.c @@ -457,7 +457,15 @@ SC_Constructor(ConnectionClass *conn) rv->callbacks = NULL; GetDataInfoInitialize(SC_get_GDTI(rv)); PutDataInfoInitialize(SC_get_PDTI(rv)); + rv->use_server_side_prepare = conn->connInfo.use_server_side_prepare; rv->lock_CC_for_rb = FALSE; + // for batch execution + memset(&rv->stmt_deffered, 0, sizeof(rv->stmt_deffered)); + if ((rv->batch_size = conn->connInfo.batch_size) < 1) + rv->batch_size = 1; + rv->exec_type = DIRECT_EXEC; + rv->count_of_deffered = 0; + rv->has_notice = 0; INIT_STMT_CS(rv); } return rv; @@ -506,6 +514,8 @@ SC_Destructor(StatementClass *self) cancelNeedDataState(self); if (self->callbacks) free(self->callbacks); + if (!PQExpBufferDataBroken(self->stmt_deffered)) + termPQExpBuffer(&self->stmt_deffered); DELETE_STMT_CS(self); free(self); @@ -687,10 +697,12 @@ SC_initialize_stmts(StatementClass *self, BOOL initializeOriginal) { ProcessedStmt *pstmt; ProcessedStmt *next_pstmt; + ConnectionClass *conn = SC_get_conn(self); if (self->lock_CC_for_rb) { - LEAVE_CONN_CS(SC_get_conn(self)); + if (conn) + LEAVE_CONN_CS(conn); self->lock_CC_for_rb = FALSE; } if (initializeOriginal) @@ -721,6 +733,8 @@ SC_initialize_stmts(StatementClass *self, BOOL initializeOriginal) self->join_info = 0; SC_init_parse_method(self); SC_init_discard_output_params(self); + if (conn) + self->use_server_side_prepare = conn->connInfo.use_server_side_prepare; } if (self->stmt_with_params) { @@ -732,6 +746,7 @@ SC_initialize_stmts(StatementClass *self, BOOL initializeOriginal) free(self->load_statement); self->load_statement = NULL; } + self->has_notice = 0; return 0; } @@ -1275,9 +1290,13 @@ SC_create_errorinfo(const StatementClass *self, PG_ErrorInfo *pgerror_fail_safe) if (NULL != sqlstate && strnicmp(res->sqlstate, "00", 2) == 0) continue; sqlstate = res->sqlstate; + if (!QR_command_maybe_successful(res)) + loopend = TRUE; + /* if ('0' != sqlstate[0] || '1' < sqlstate[1]) loopend = TRUE; + */ } if (NULL != res->message) { @@ -2072,9 +2091,17 @@ SC_execute(StatementClass *self) if (0 < SC_get_errornumber(self)) ; else if (was_ok) - SC_set_errornumber(self, STMT_OK); + { + if (self->has_notice && + 0 == SC_get_errornumber(self)) + SC_set_errornumber(self, STMT_INFO_ONLY); + } else if (was_nonfatal) - SC_set_errornumber(self, STMT_INFO_ONLY); + { + self->has_notice = 1; + if (0 == SC_get_errornumber(self)) + SC_set_errornumber(self, STMT_INFO_ONLY); + } else SC_set_errorinfo(self, res, 0); /* set cursor before the first tuple in the list */ @@ -2465,6 +2492,7 @@ QResultClass *add_libpq_notice_receiver(StatementClass *stmt, notice_receiver_ar nrarg->conn = SC_get_conn(stmt); nrarg->comment = __FUNCTION__; nrarg->res = res; + nrarg->stmt = stmt; PQsetNoticeReceiver(nrarg->conn->pqconn, receive_libpq_notice, nrarg); return newres; diff --git a/statement.h b/statement.h index 5abeca8..02f7ad5 100644 --- a/statement.h +++ b/statement.h @@ -12,6 +12,7 @@ #include "psqlodbc.h" #include +#include "pqexpbuffer.h" #include "pgtypes.h" #include "bind.h" #include "descriptor.h" @@ -167,6 +168,12 @@ enum STMT_FETCH_NORMAL, STMT_FETCH_EXTENDED }; +/* Type of the 3rd parameter of Exec_with_parameters_resolved() */ +typedef enum { + DIRECT_EXEC, + DEFFERED_EXEC, + LAST_EXEC +} EXEC_TYPE; #define PG_NUM_NORMAL_KEYS 2 @@ -271,6 +278,7 @@ struct StatementClass_ po_ind_t join_info; /* have joins ? */ po_ind_t parse_method; /* parse_statement is forced or ? */ po_ind_t curr_param_result; /* current param result is set ? */ + po_ind_t has_notice; /* exec result contains notice messages ? */ pgNAME cursor_name; char *plan_name; @@ -291,6 +299,12 @@ struct StatementClass_ SQLLEN last_fetch_count_include_ommitted; time_t stmt_time; struct tm localtime; + // for batch execution + signed char use_server_side_prepare; + int batch_size; + EXEC_TYPE exec_type; + int count_of_deffered; + PQExpBufferData stmt_deffered; /* SQL_NEED_DATA Callback list */ StatementClass *execute_delegate; StatementClass *execute_parent; -- 2.39.5