int zbx_db_statement_execute()
{
const char *__function_name = "zbx_db_statement_execute";
sword err;
ub4 nrows;
int ret;
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] within failed transaction", txn_level);
ret = ZBX_DB_FAIL;
goto out;
}
if (OCI_SUCCESS != (err = zbx_oracle_statement_execute(&nrows)))
{
zabbix_errlog(ERR_Z3007, err, zbx_oci_error(err));
ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else
ret = (int)nrows;
if (ZBX_DB_FAIL == ret && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query failed, setting transaction as failed");
txn_error = 1;
}
out:
zabbix_log(LOG_LEVEL_DEBUG, "%s():%d", __function_name, ret);
return ret;
}
int zbx_db_statement_prepare(const char *sql)
{
sword err;
int ret = ZBX_DB_OK;
if (0 == txn_init && 0 == txn_level)
zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] within failed transaction", txn_level);
return ZBX_DB_FAIL;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
/* Oracle */
if (OCI_SUCCESS != (err = zbx_oracle_statement_prepare(sql)))
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (ZBX_DB_FAIL == ret && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
return ret;
}
int zbx_db_bind_parameter(int position, void *buffer, unsigned char type)
{
sword err;
int ret = ZBX_DB_OK;
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] within failed transaction", txn_level);
return ZBX_DB_FAIL;
}
/* Oracle */
switch (type)
{
case ZBX_TYPE_ID:
err = zbx_oracle_bind_parameter((ub4)position, buffer, (sb4)sizeof(zbx_uint64_t), SQLT_INT);
break;
case ZBX_TYPE_INT:
err = zbx_oracle_bind_parameter((ub4)position, buffer, (sb4)sizeof(int), SQLT_INT);
break;
case ZBX_TYPE_TEXT:
err = zbx_oracle_bind_parameter((ub4)position, buffer, (sb4)strlen((char *)buffer), SQLT_LNG);
break;
default:
err = OCI_SUCCESS;
}
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3007, err, zbx_oci_error(err));
ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (ZBX_DB_FAIL == ret && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query failed, setting transaction as failed");
txn_error = 1;
}
return ret;
}
/******************************************************************************
* *
* Function: zbx_db_vselect *
* *
* Purpose: execute a select statement *
* *
* Return value: data, NULL (on error) or (DB_RESULT)ZBX_DB_DOWN *
* *
******************************************************************************/
DB_RESULT zbx_db_vselect(const char *fmt, va_list args)
{
char *sql = NULL;
DB_RESULT result = NULL;
double sec = 0;
#if defined(HAVE_IBM_DB2)
int i;
SQLRETURN ret = SQL_SUCCESS;
#elif defined(HAVE_ORACLE)
sword err = OCI_SUCCESS;
ub4 counter;
#elif defined(HAVE_POSTGRESQL)
char *error = NULL;
#elif defined(HAVE_SQLITE3)
int ret = FAIL;
char *error = NULL;
#endif
if (0 != CONFIG_LOG_SLOW_QUERIES)
sec = zbx_time();
sql = zbx_dvsprintf(sql, fmt, args);
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
goto clean;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
#if defined(HAVE_IBM_DB2)
result = zbx_malloc(result, sizeof(ZBX_IBM_DB2_RESULT));
memset(result, 0, sizeof(ZBX_IBM_DB2_RESULT));
/* allocate a statement handle */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &result->hstmt)))
goto error;
/* directly execute the statement */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLExecDirect(result->hstmt, (SQLCHAR *)sql, SQL_NTS)))
goto error;
/* identify the number of output columns */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLNumResultCols(result->hstmt, &result->ncolumn)))
goto error;
if (0 == result->ncolumn)
goto error;
result->nalloc = 0;
result->values = zbx_malloc(result->values, sizeof(char *) * result->ncolumn);
result->values_cli = zbx_malloc(result->values_cli, sizeof(char *) * result->ncolumn);
result->values_len = zbx_malloc(result->values_len, sizeof(SQLINTEGER) * result->ncolumn);
for (i = 0; i < result->ncolumn; i++)
{
/* get the display size for a column */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLColAttribute(result->hstmt, (SQLSMALLINT)(i + 1),
SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &result->values_len[i])))
{
goto error;
}
result->values_len[i] += 1; /* '\0'; */
/* allocate memory to bind a column */
result->values_cli[i] = zbx_malloc(NULL, result->values_len[i]);
result->nalloc++;
/* bind columns to program variables, converting all types to CHAR */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLBindCol(result->hstmt, (SQLSMALLINT)(i + 1),
SQL_C_CHAR, result->values_cli[i], result->values_len[i], &result->values_len[i])))
{
goto error;
}
}
error:
if (SUCCEED != zbx_ibm_db2_success(ret) || 0 == result->ncolumn)
{
zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, result->hstmt);
IBM_DB2free_result(result);
result = (SQL_CD_TRUE == IBM_DB2server_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
#elif defined(HAVE_MYSQL)
if (NULL == conn)
{
zabbix_errlog(ERR_Z3003);
result = NULL;
}
else
{
if (0 != mysql_query(conn, sql))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
switch (mysql_errno(conn))
{
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
case ER_UNKNOWN_ERROR:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
default:
result = NULL;
break;
}
}
else
result = mysql_store_result(conn);
}
#elif defined(HAVE_ORACLE)
result = zbx_malloc(NULL, sizeof(ZBX_OCI_DB_RESULT));
memset(result, 0, sizeof(ZBX_OCI_DB_RESULT));
err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&result->stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (OCI_SUCCESS == err)
{
err = OCIStmtPrepare(result->stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
}
if (OCI_SUCCESS == err)
{
err = OCIStmtExecute(oracle.svchp, result->stmthp, oracle.errhp, (ub4)0, (ub4)0,
(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
}
if (OCI_SUCCESS == err)
{
/* get the number of columns in the query */
err = OCIAttrGet((void *)result->stmthp, OCI_HTYPE_STMT, (void *)&result->ncolumn,
(ub4 *)0, OCI_ATTR_PARAM_COUNT, oracle.errhp);
}
if (OCI_SUCCESS != err)
goto error;
assert(0 < result->ncolumn);
result->values = zbx_malloc(NULL, result->ncolumn * sizeof(char *));
memset(result->values, 0, result->ncolumn * sizeof(char *));
for (counter = 1; OCI_SUCCESS == err && counter <= result->ncolumn; counter++)
{
OCIParam *parmdp = NULL;
OCIDefine *defnp = NULL;
ub4 char_semantics;
ub2 col_width;
/* request a parameter descriptor in the select-list */
err = OCIParamGet((void *)result->stmthp, OCI_HTYPE_STMT, oracle.errhp, (void **)&parmdp, (ub4)counter);
if (OCI_SUCCESS == err)
{
/* retrieve the length semantics for the column */
char_semantics = 0;
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&char_semantics, (ub4 *)0,
(ub4)OCI_ATTR_CHAR_USED, (OCIError *)oracle.errhp);
}
if (OCI_SUCCESS == err)
{
col_width = 0;
if (char_semantics)
{
/* retrieve the column width in characters */
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
(ub4)OCI_ATTR_CHAR_SIZE, (OCIError *)oracle.errhp);
}
else
{
/* retrieve the column width in bytes */
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
(ub4)OCI_ATTR_DATA_SIZE, (OCIError *)oracle.errhp);
}
}
col_width++;
result->values[counter - 1] = zbx_malloc(NULL, col_width);
memset(result->values[counter - 1], 0, col_width);
if (OCI_SUCCESS == err)
{
/* represent any data as characters */
err = OCIDefineByPos(result->stmthp, &defnp, oracle.errhp, counter,
(dvoid *)result->values[counter - 1], col_width, SQLT_STR,
(dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
}
/* free cell descriptor */
OCIDescriptorFree(parmdp, OCI_DTYPE_PARAM);
parmdp = NULL;
}
error:
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
OCI_DBfree_result(result);
result = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
#elif defined(HAVE_POSTGRESQL)
result = zbx_malloc(NULL, sizeof(ZBX_PG_DB_RESULT));
result->pg_result = PQexec(conn, sql);
result->values = NULL;
result->cursor = 0;
result->row_num = 0;
if (NULL == result->pg_result)
zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
if (PGRES_TUPLES_OK != PQresultStatus(result->pg_result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result->pg_result)),
PQresultErrorMessage(result->pg_result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
PG_DBfree_result(result);
result = (CONNECTION_OK == PQstatus(conn) ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
else /* init rownum */
result->row_num = PQntuples(result->pg_result);
#elif defined(HAVE_SQLITE3)
if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
{
zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
exit(FAIL);
}
result = zbx_malloc(NULL, sizeof(ZBX_SQ_DB_RESULT));
result->curow = 0;
lbl_get_table:
if (SQLITE_OK != (ret = sqlite3_get_table(conn,sql, &result->data, &result->nrow, &result->ncolumn, &error)))
{
if (SQLITE_BUSY == ret)
goto lbl_get_table;
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
SQ_DBfree_result(result);
switch (ret)
{
case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
are this far into execution, zbx_db_connect() was successful */
case SQLITE_NOMEM: /* a malloc() failed */
case SQLITE_MISMATCH: /* data type mismatch */
result = NULL;
break;
default:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
}
}
if (0 == txn_level)
php_sem_release(&sqlite_access);
#endif /* HAVE_SQLITE3 */
if (0 != CONFIG_LOG_SLOW_QUERIES)
{
sec = zbx_time() - sec;
if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
}
if (NULL == result && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
clean:
zbx_free(sql);
return result;
}
/*
* Execute SQL statement. For non-select statements only.
*/
int zbx_db_vexecute(const char *fmt, va_list args)
{
char *sql = NULL;
int ret = ZBX_DB_OK;
double sec = 0;
#if defined(HAVE_IBM_DB2)
SQLHANDLE hstmt = 0;
SQLRETURN ret1;
SQLLEN row1;
SQLLEN rows = 0;
#elif defined(HAVE_MYSQL)
int status;
#elif defined(HAVE_ORACLE)
OCIStmt *stmthp = NULL;
sword err = OCI_SUCCESS;
#elif defined(HAVE_POSTGRESQL)
PGresult *result;
char *error = NULL;
#elif defined(HAVE_SQLITE3)
int err;
char *error = NULL;
#endif
if (0 != CONFIG_LOG_SLOW_QUERIES)
sec = zbx_time();
sql = zbx_dvsprintf(sql, fmt, args);
if (0 == txn_init && 0 == txn_level)
zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
ret = ZBX_DB_FAIL;
goto clean;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
#if defined(HAVE_IBM_DB2)
/* allocate a statement handle */
if (SUCCEED != zbx_ibm_db2_success(SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &hstmt)))
ret = ZBX_DB_DOWN;
/* directly execute the statement; returns SQL_NO_DATA_FOUND when no rows were affected */
if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success_ext(SQLExecDirect(hstmt, (SQLCHAR *)sql, SQL_NTS)))
ret = ZBX_DB_DOWN;
/* get number of affected rows */
if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &rows)))
ret = ZBX_DB_DOWN;
/* process other SQL statements in the batch */
while (ZBX_DB_OK == ret && SUCCEED == zbx_ibm_db2_success(ret1 = SQLMoreResults(hstmt)))
{
if (SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &row1)))
ret = ZBX_DB_DOWN;
else
rows += row1;
}
if (ZBX_DB_OK == ret && SQL_NO_DATA_FOUND != ret1)
ret = ZBX_DB_DOWN;
if (ZBX_DB_OK != ret)
{
zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, hstmt);
ret = (SQL_CD_TRUE == IBM_DB2server_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else if (0 <= rows)
{
ret = (int)rows;
}
if (hstmt)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
#elif defined(HAVE_MYSQL)
if (NULL == conn)
{
zabbix_errlog(ERR_Z3003);
ret = ZBX_DB_FAIL;
}
else
{
if (0 != (status = mysql_query(conn, sql)))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
switch (mysql_errno(conn))
{
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
case ER_UNKNOWN_ERROR:
ret = ZBX_DB_DOWN;
break;
default:
ret = ZBX_DB_FAIL;
break;
}
}
else
{
do
{
if (0 != mysql_field_count(conn))
{
zabbix_log(LOG_LEVEL_DEBUG, "cannot retrieve result set");
break;
}
else
ret += (int)mysql_affected_rows(conn);
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
if (0 < (status = mysql_next_result(conn)))
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
}
while (0 == status);
}
}
#elif defined(HAVE_ORACLE)
err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (OCI_SUCCESS == err)
{
err = OCIStmtPrepare(stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
}
if (OCI_SUCCESS == err)
{
err = OCIStmtExecute(oracle.svchp, stmthp, oracle.errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
if (OCI_SUCCESS == err)
{
ub4 nrows = 0;
err = OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, (ub4 *)&nrows,
(ub4 *)0, OCI_ATTR_ROW_COUNT, oracle.errhp);
ret = nrows;
}
}
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (NULL != stmthp)
{
(void)OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
stmthp = NULL;
}
#elif defined(HAVE_POSTGRESQL)
result = PQexec(conn,sql);
if (NULL == result)
{
zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else if (PGRES_COMMAND_OK != PQresultStatus(result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result)),
PQresultErrorMessage(result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (ZBX_DB_OK == ret)
ret = atoi(PQcmdTuples(result));
PQclear(result);
#elif defined(HAVE_SQLITE3)
if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
{
zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
exit(FAIL);
}
lbl_exec:
if (SQLITE_OK != (err = sqlite3_exec(conn, sql, NULL, 0, &error)))
{
if (SQLITE_BUSY == err)
goto lbl_exec;
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
switch (err)
{
case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
are this far into execution, zbx_db_connect() was successful */
case SQLITE_NOMEM: /* A malloc() failed */
case SQLITE_TOOBIG: /* String or BLOB exceeds size limit */
case SQLITE_CONSTRAINT: /* Abort due to constraint violation */
case SQLITE_MISMATCH: /* Data type mismatch */
ret = ZBX_DB_FAIL;
break;
default:
ret = ZBX_DB_DOWN;
break;
}
}
if (ZBX_DB_OK == ret)
ret = sqlite3_changes(conn);
if (0 == txn_level)
php_sem_release(&sqlite_access);
#endif /* HAVE_SQLITE3 */
if (0 != CONFIG_LOG_SLOW_QUERIES)
{
sec = zbx_time() - sec;
if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
}
if (ZBX_DB_FAIL == ret && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
clean:
zbx_free(sql);
return ret;
}