static int db_odbc_select(DC_ITEM *item, AGENT_REQUEST *request, AGENT_RESULT *result)
{
const char *__function_name = "db_odbc_select";
int ret = NOTSUPPORTED;
ZBX_ODBC_DBH dbh;
ZBX_ODBC_ROW row;
zabbix_log(LOG_LEVEL_DEBUG, "In %s() query:'%s'", __function_name, item->params);
if (2 != request->nparam)
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "Invalid number of parameters."));
goto out;
}
if (SUCCEED != odbc_DBconnect(&dbh, request->params[1], item->username, item->password, CONFIG_TIMEOUT))
{
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
goto out;
}
if (NULL != odbc_DBselect(&dbh, item->params))
{
if (NULL != (row = odbc_DBfetch(&dbh)))
{
if (NULL == row[0])
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "SQL query returned NULL value."));
}
else if (SUCCEED == set_result_type(result, item->value_type, item->data_type, row[0]))
{
ret = SUCCEED;
}
}
else
{
const char *last_error = get_last_odbc_strerror();
if ('\0' != *last_error)
SET_MSG_RESULT(result, zbx_strdup(NULL, last_error));
else
SET_MSG_RESULT(result, zbx_strdup(NULL, "SQL query returned empty result."));
}
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
odbc_DBclose(&dbh);
out:
zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __function_name, zbx_result_string(ret));
return ret;
}
ZBX_ODBC_ROW odbc_DBfetch(ZBX_ODBC_RESULT pdbh)
{
SQLCHAR
err_stat[10],
err_msg[100];
SQLINTEGER
err_int;
SQLSMALLINT
err_msg_len;
SQLRETURN retcode;
SQLSMALLINT i;
if(pdbh == NULL) return NULL;
clean_odbc_strerror();
zabbix_log(LOG_LEVEL_DEBUG, "ODBC fetch");
retcode = SQLFetch(pdbh->hstmt);
if (retcode == SQL_ERROR) goto lbl_err_exit;
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
zabbix_log(LOG_LEVEL_DEBUG, "odbc_DBfetch [end of rows received]");
return NULL;
}
for(i=0; i < pdbh->col_num; i++)
{
rtrim_spaces(pdbh->row_data[i]);
zabbix_log(LOG_LEVEL_DEBUG, "Featched [%i col]: %s", i, pdbh->row_data[i]);
}
return pdbh->row_data;
lbl_err_exit:
SQLGetDiagRec(SQL_HANDLE_STMT,
pdbh->hstmt,
1,
err_stat,
&err_int,
err_msg,
sizeof(err_msg),
&err_msg_len
);
set_last_odbc_strerror("Failed data fetching [%s] (%d)", err_msg, err_int);
zabbix_log(LOG_LEVEL_ERR, "%s", get_last_odbc_strerror());
return NULL;
}
int odbc_DBexecute(ZBX_ODBC_DBH *pdbh, const char *query)
{
SQLCHAR
err_stat[10],
err_msg[100];
SQLINTEGER
err_int;
SQLSMALLINT
err_msg_len;
SQLRETURN retcode;
clean_odbc_strerror();
odbc_free_row_data(pdbh);
retcode = SQLExecDirect(pdbh->hstmt, (SQLCHAR*) query, SQL_NTS);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) goto lbl_err_exit;
return SUCCEED;
lbl_err_exit:
SQLGetDiagRec(SQL_HANDLE_STMT,
pdbh->hstmt,
1,
err_stat,
&err_int,
err_msg,
sizeof(err_msg),
&err_msg_len
);
set_last_odbc_strerror("Failed select execution [%s] (%d)", err_msg, err_int);
zabbix_log(LOG_LEVEL_ERR, "%s", get_last_odbc_strerror());
return FAIL;
}
static int db_odbc_discovery(DC_ITEM *item, AGENT_REQUEST *request, AGENT_RESULT *result)
{
const char *__function_name = "db_odbc_discovery";
int ret = NOTSUPPORTED, i, j;
ZBX_ODBC_DBH dbh;
ZBX_ODBC_ROW row;
char **columns, *p, macro[MAX_STRING_LEN];
struct zbx_json json;
zabbix_log(LOG_LEVEL_DEBUG, "In %s() query:'%s'", __function_name, item->params);
if (2 != request->nparam)
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "Invalid number of parameters."));
goto out;
}
if (SUCCEED != odbc_DBconnect(&dbh, request->params[1], item->username, item->password, CONFIG_TIMEOUT))
{
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
goto out;
}
if (NULL != odbc_DBselect(&dbh, item->params))
{
columns = zbx_malloc(NULL, sizeof(char *) * dbh.col_num);
if (SUCCEED == get_result_columns(&dbh, columns))
{
for (i = 0; i < dbh.col_num; i++)
zabbix_log(LOG_LEVEL_DEBUG, "%s() column[%d]:'%s'", __function_name, i + 1, columns[i]);
for (i = 0; i < dbh.col_num; i++)
{
for (p = columns[i]; '\0' != *p; p++)
{
if (0 != isalpha((unsigned char)*p))
*p = toupper((unsigned char)*p);
if (SUCCEED != is_macro_char(*p))
{
SET_MSG_RESULT(result, zbx_dsprintf(NULL,
"Cannot convert column #%d name to macro.", i + 1));
goto clean;
}
}
for (j = 0; j < i; j++)
{
if (0 == strcmp(columns[i], columns[j]))
{
SET_MSG_RESULT(result, zbx_dsprintf(NULL,
"Duplicate macro name: {#%s}.", columns[i]));
goto clean;
}
}
}
zbx_json_init(&json, ZBX_JSON_STAT_BUF_LEN);
zbx_json_addarray(&json, ZBX_PROTO_TAG_DATA);
while (NULL != (row = odbc_DBfetch(&dbh)))
{
zbx_json_addobject(&json, NULL);
for (i = 0; i < dbh.col_num; i++)
{
zbx_snprintf(macro, MAX_STRING_LEN, "{#%s}", columns[i]);
zbx_json_addstring(&json, macro, row[i], ZBX_JSON_TYPE_STRING);
}
zbx_json_close(&json);
}
zbx_json_close(&json);
SET_STR_RESULT(result, zbx_strdup(NULL, json.buffer));
zbx_json_free(&json);
ret = SUCCEED;
clean:
for (i = 0; i < dbh.col_num; i++)
zbx_free(columns[i]);
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, "Cannot obtain column names."));
zbx_free(columns);
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
odbc_DBclose(&dbh);
out:
zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __function_name, zbx_result_string(ret));
return ret;
}
/******************************************************************************
* *
* Function: get_value_db *
* *
* Purpose: retrieve data from database *
* *
* Parameters: item - item we are interested in *
* *
* Return value: SUCCEED - data successfully retrieved and stored in result *
* NOTSUPPORTED - requested item is not supported *
* *
* Author: Eugene Grigorjev *
* *
* Comments: *
* *
******************************************************************************/
int get_value_db(DC_ITEM *item, AGENT_RESULT *result)
{
const char *__function_name = "get_value_db";
#ifdef HAVE_ODBC
ZBX_ODBC_DBH dbh;
ZBX_ODBC_ROW row;
char *db_dsn = NULL, *db_user = NULL, *db_pass = NULL, *db_sql = NULL;
#endif
int ret = NOTSUPPORTED;
init_result(result);
zabbix_log(LOG_LEVEL_DEBUG, "In %s() key_orig:'%s'", __function_name, item->key_orig);
#ifdef HAVE_ODBC
#define DB_ODBC_SELECT_KEY "db.odbc.select["
if (0 == strncmp(item->key, DB_ODBC_SELECT_KEY, strlen(DB_ODBC_SELECT_KEY)))
{
db_dsn = get_param_value(item->params, "DSN");
db_user = get_param_value(item->params, "user");
db_pass = get_param_value(item->params, "password");
db_sql = get_param_value(item->params, "sql");
if (SUCCEED == odbc_DBconnect(&dbh, db_dsn, db_user, db_pass, CONFIG_TIMEOUT))
{
if (NULL != odbc_DBselect(&dbh, db_sql))
{
if (NULL != (row = odbc_DBfetch(&dbh)))
{
if (NULL == row[0])
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "SQL query returned NULL "
"value."));
}
else if (SUCCEED == set_result_type(result, item->value_type, item->data_type,
row[0]))
{
ret = SUCCEED;
}
}
else
{
const char *last_error = get_last_odbc_strerror();
if ('\0' != *last_error)
SET_MSG_RESULT(result, zbx_strdup(NULL, last_error));
else
SET_MSG_RESULT(result, zbx_strdup(NULL, "SQL query returned empty "
"result."));
}
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
odbc_DBclose(&dbh);
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
zbx_free(db_dsn);
zbx_free(db_user);
zbx_free(db_pass);
zbx_free(db_sql);
}
#undef DB_ODBC_SELECT_KEY
#endif /* HAVE_ODBC */
zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __function_name, zbx_result_string(ret));
return ret;
}
int odbc_DBconnect(ZBX_ODBC_DBH *pdbh, const char *db_dsn, const char *user, const char *pass)
{
SQLCHAR
err_stat[10],
err_msg[100];
SQLINTEGER
err_int;
SQLSMALLINT
err_msg_len;
SQLRETURN retcode;
clean_odbc_strerror();
memset(pdbh, 0 , sizeof(ZBX_ODBC_DBH));
zabbix_log(LOG_LEVEL_DEBUG, "ODBC connect [%s] [%s]", db_dsn, user);
/*Allocate environment handle */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &(pdbh->henv));
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
{
set_last_odbc_strerror("%s","failed environment handle allocation.");
}
else
{
/* Set the ODBC version environment attribute */
retcode = SQLSetEnvAttr(pdbh->henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
{
set_last_odbc_strerror("%s","failed ODBC version setting.");
}
else
{
/* Allocate connection handle */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, pdbh->henv, &(pdbh->hdbc));
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
{
set_last_odbc_strerror("%s","failed connection handle allocation.");
}
else
{
/* Set login timeout to 5 seconds. */
SQLSetConnectAttr(pdbh->hdbc, (SQLINTEGER)SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, (SQLINTEGER)0);
/* Connect to data source */
retcode = SQLConnect(pdbh->hdbc,
(SQLCHAR*) db_dsn, SQL_NTS,
(SQLCHAR*) user, SQL_NTS,
(SQLCHAR*) pass, SQL_NTS
);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
{
SQLGetDiagRec(SQL_HANDLE_DBC,
pdbh->hdbc,
1,
err_stat,
&err_int,
err_msg,
sizeof(err_msg),
&err_msg_len
);
set_last_odbc_strerror("failed connection [%s] (%d)", err_msg, err_int);
}
else
{
pdbh->connected = 1;
/* Allocate statement handle */
retcode = SQLAllocHandle(SQL_HANDLE_STMT, pdbh->hdbc, &(pdbh->hstmt));
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
return SUCCEED;
}
else
{
SQLFreeHandle(SQL_HANDLE_STMT, pdbh->hstmt);
pdbh->hstmt = NULL;
}
SQLDisconnect(pdbh->hdbc);
}
SQLFreeHandle(SQL_HANDLE_DBC, pdbh->hdbc);
pdbh->hdbc = NULL;
}
}
SQLFreeHandle(SQL_HANDLE_ENV, pdbh->henv);
pdbh->henv = NULL;
}
zabbix_log(LOG_LEVEL_ERR, "Failed to connect to DSN '%s' : Error: %s", db_dsn, get_last_odbc_strerror());
return FAIL; /* error */
}
ZBX_ODBC_RESULT odbc_DBselect(ZBX_ODBC_DBH *pdbh, const char *query)
{
SQLCHAR
err_stat[10],
err_msg[100];
SQLINTEGER
err_int;
SQLSMALLINT
err_msg_len;
SQLRETURN retcode;
SQLSMALLINT
i = 0,
col_num = 0;
clean_odbc_strerror();
odbc_free_row_data(pdbh);
zabbix_log(LOG_LEVEL_DEBUG, "ODBC select [%s]", query);
retcode = SQLExecDirect(pdbh->hstmt, (SQLCHAR*) query, SQL_NTS);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) goto lbl_err_exit;
retcode = SQLNumResultCols(pdbh->hstmt, &col_num);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) goto lbl_err_exit;
pdbh->col_num = col_num;
pdbh->row_data = zbx_malloc(pdbh->row_data, sizeof(char*) * col_num);
memset(pdbh->row_data, 0, sizeof(char*) * col_num);
pdbh->data_len = zbx_malloc(pdbh->data_len, sizeof(SQLINTEGER) * col_num);
memset(pdbh->data_len, 0, sizeof(SQLINTEGER) * col_num);
for(i=0; i < col_num; i++)
{
pdbh->row_data[i] = zbx_malloc(pdbh->row_data[i], MAX_STRING_LEN);
SQLBindCol(pdbh->hstmt, i+1, SQL_C_CHAR, pdbh->row_data[i], MAX_STRING_LEN, &pdbh->data_len[i]);
}
zabbix_log(LOG_LEVEL_DEBUG, "selected %i cols", col_num);
return (ZBX_ODBC_RESULT) pdbh;
lbl_err_exit:
SQLGetDiagRec(SQL_HANDLE_STMT,
pdbh->hstmt,
1,
err_stat,
&err_int,
err_msg,
sizeof(err_msg),
&err_msg_len
);
set_last_odbc_strerror("Failed selection [%s] (%d)", err_msg, err_int);
zabbix_log(LOG_LEVEL_ERR, "%s", get_last_odbc_strerror());
return NULL;
}
/******************************************************************************
* *
* Function: get_value_db *
* *
* Purpose: retrieve data from database *
* *
* Parameters: item - item we are interested in *
* *
* Return value: SUCCEED - data successfully retrieved and stored in result *
* NOTSUPPORTED - requested item is not supported *
* *
* Author: Eugene Grigorjev *
* *
* Comments: *
* *
******************************************************************************/
int get_value_db(DC_ITEM *item, AGENT_RESULT *result)
{
const char *__function_name = "get_value_db";
ZBX_ODBC_DBH dbh;
ZBX_ODBC_ROW row;
AGENT_REQUEST request;
int ret = NOTSUPPORTED;
zabbix_log(LOG_LEVEL_DEBUG, "In %s() key_orig:'%s'", __function_name, item->key_orig);
init_request(&request);
if (SUCCEED != parse_item_key(item->key, &request))
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "Key is badly formatted."));
goto out;
}
if (0 != strcmp(request.key, "db.odbc.select"))
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "Item key is not supported."));
goto out;
}
if (2 != request.nparam)
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "Invalid number of parameters."));
goto out;
}
if (SUCCEED == odbc_DBconnect(&dbh, request.params[1], item->username, item->password, CONFIG_TIMEOUT))
{
if (NULL != odbc_DBselect(&dbh, item->params))
{
if (NULL != (row = odbc_DBfetch(&dbh)))
{
if (NULL == row[0])
{
SET_MSG_RESULT(result, zbx_strdup(NULL, "SQL query returned NULL value."));
}
else if (SUCCEED == set_result_type(result, item->value_type, item->data_type, row[0]))
{
ret = SUCCEED;
}
}
else
{
const char *last_error = get_last_odbc_strerror();
if ('\0' != *last_error)
SET_MSG_RESULT(result, zbx_strdup(NULL, last_error));
else
SET_MSG_RESULT(result, zbx_strdup(NULL, "SQL query returned empty result."));
}
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
odbc_DBclose(&dbh);
}
else
SET_MSG_RESULT(result, zbx_strdup(NULL, get_last_odbc_strerror()));
out:
free_request(&request);
zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __function_name, zbx_result_string(ret));
return ret;
}
