/************************************************************************** Saves pconn fields to the database. If the username already exists, replace the data. **************************************************************************/ static bool auth_db_save(struct connection *pconn) { #ifdef HAVE_AUTH char buffer[1024] = ""; const int bufsize = sizeof(buffer); char *name_buffer = NULL; char *pw_buffer = NULL; MYSQL *sock, mysql; int str_result; mysql_init(&mysql); /* attempt to connect to the server */ if (!(sock = mysql_real_connect(&mysql, auth_config.host.value, auth_config.user.value, auth_config.password.value, auth_config.database.value, atoi(auth_config.port.value), NULL, 0))) { freelog(LOG_ERROR, "Can't connect to server! (%s)", mysql_error(&mysql)); return FALSE; } name_buffer = alloc_escaped_string(&mysql, pconn->username); pw_buffer = alloc_escaped_string(&mysql, pconn->server.password); if (name_buffer == NULL || pw_buffer == NULL) { free_escaped_string(name_buffer); mysql_close(sock); return FALSE; } /* insert new user into table. we insert the following things: name * md5sum of the password, the creation time in seconds, the accesstime * also in seconds from 1970, the users address (twice) and the logincount */ str_result = my_snprintf(buffer, bufsize, "insert into %s values " "(NULL, '%s', '%s', NULL, " "unix_timestamp(), unix_timestamp()," "'%s', '%s', 0)", auth_config.table.value, name_buffer, pw_buffer, pconn->server.ipaddr, pconn->server.ipaddr); /* Password is not needed for further queries. */ free_escaped_string(pw_buffer); pw_buffer = NULL; if (str_result < 0 || str_result >= bufsize || mysql_query(sock, buffer)) { freelog(LOG_ERROR, "db_save insert failed for new user: %s (%s)", pconn->username, mysql_error(sock)); mysql_close(sock); return FALSE; } /* insert an entry into our log */ memset(buffer, 0, bufsize); str_result = my_snprintf(buffer, bufsize, "insert into %s (name, logintime, address, succeed) " "values ('%s',unix_timestamp(),'%s', 'S')", auth_config.login_table.value, name_buffer, pconn->server.ipaddr); free_escaped_string(name_buffer); name_buffer = 0; if (str_result < 0 || str_result >= bufsize || mysql_query(sock, buffer)) { freelog(LOG_ERROR, "db_load insert loginlog failed for user: %s (%s)", pconn->username, mysql_error(sock)); } mysql_close(sock); #endif return TRUE; }
word db_connect(void) { _log(PROC, "db_connect()"); if(!mysql_object) { _log(DEBUG, " Connecting to database."); mysql_object = mysql_init(NULL); if(mysql_object == NULL) { _log(CRITICAL, "db_connect() error 1: mysql_init() returned NULL"); return 1; } if(mysql_real_connect(mysql_object, server, user, password, database, 0, NULL, 0) == NULL) { char zs[1024]; sprintf(zs, "db_connect() error 2: Connect failed: Error %u: %s", mysql_errno(mysql_object), mysql_error(mysql_object)); _log(CRITICAL, zs); db_disconnect(); return 2; } // Disable auto-reconnect my_bool reconnect = 0; mysql_options(mysql_object, MYSQL_OPT_RECONNECT, &reconnect); { char zs[128]; sprintf(zs, "Connection to database \"%s\" opened.", database); _log(GENERAL, zs); } } return 0; }
word db_row_count(void) { // Returns number of rows affected by preceding DELETE or UPDATE. // Doesn't work after a SELECT. MYSQL_RES * result; MYSQL_ROW row; if(db_connect()) return 9; if (mysql_query(mysql_object, "SELECT row_count()")) { char zs[1024]; sprintf(zs, "db_row_count(): mysql_query() Error %u: %s", mysql_errno(mysql_object), mysql_error(mysql_object)); _log(CRITICAL, zs); db_disconnect(); return 3; } result = mysql_store_result(mysql_object); row = mysql_fetch_row(result); mysql_free_result(result); return atoi(row[0]); }
int oph_odb_set_job_status_and_nchildrencompleted(int id_job, enum oph__oph_odb_job_status status, int nchildren, int force_nchildren_saving, ophidiadb * oDB) { if (!oDB) { pmesg(LOG_ERROR, __FILE__, __LINE__, "Null input parameter\n"); return OPH_ODB_NULL_PARAM; } if (oph_odb_check_connection_to_ophidiadb(oDB)) { pmesg(LOG_ERROR, __FILE__, __LINE__, "Unable to reconnect to OphidiaDB.\n"); return OPH_ODB_MYSQL_ERROR; } char insertQuery[MYSQL_BUFLEN]; int n; switch (status) { case OPH_ODB_STATUS_UNKNOWN: if (nchildren >= 0) { pmesg(LOG_DEBUG, __FILE__, __LINE__, "Status is not changed\n"); return OPH_ODB_SUCCESS; } n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_DELETE_OPHIDIADB_JOB, id_job); break; case OPH_ODB_STATUS_CLOSED: pmesg(LOG_ERROR, __FILE__, __LINE__, "Status %s is not allowed\n", OPH_ODB_STATUS_UNKNOWN_STR); return OPH_ODB_MYSQL_ERROR; case OPH_ODB_STATUS_PENDING: case OPH_ODB_STATUS_WAIT: case OPH_ODB_STATUS_START: case OPH_ODB_STATUS_SET_ENV: case OPH_ODB_STATUS_INIT: case OPH_ODB_STATUS_DISTRIBUTE: case OPH_ODB_STATUS_EXECUTE: case OPH_ODB_STATUS_REDUCE: case OPH_ODB_STATUS_DESTROY: case OPH_ODB_STATUS_UNSET_ENV: if (nchildren < 0) n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_1, oph_odb_convert_status_to_str(status), id_job); else n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_PARENT_1, oph_odb_convert_status_to_str(status), nchildren, id_job); break; case OPH_ODB_STATUS_RUNNING: if (nchildren < 0) n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_2, oph_odb_convert_status_to_str(status), id_job); else if (force_nchildren_saving) n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_PARENT_1, oph_odb_convert_status_to_str(status), nchildren, id_job); else n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_PARENT_2, oph_odb_convert_status_to_str(status), nchildren, id_job); break; case OPH_ODB_STATUS_COMPLETED: case OPH_ODB_STATUS_ERROR: case OPH_ODB_STATUS_START_ERROR: // Exception due to errors in operator arguments case OPH_ODB_STATUS_ABORTED: case OPH_ODB_STATUS_EXPIRED: if (nchildren < 0) n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_3, oph_odb_convert_status_to_str(status), id_job); else n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_PARENT_3, oph_odb_convert_status_to_str(status), nchildren, id_job); break; default: if (nchildren < 0) n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_4, id_job); else n = snprintf(insertQuery, MYSQL_BUFLEN, MYSQL_QUERY_UPDATE_OPHIDIADB_JOB_STATUS_PARENT_4, nchildren, id_job); } if (n >= MYSQL_BUFLEN) { pmesg(LOG_ERROR, __FILE__, __LINE__, "Size of query exceed query limit.\n"); return OPH_ODB_STR_BUFF_OVERFLOW; } if ((nchildren >= 0) && mysql_set_server_option(oDB->conn, MYSQL_OPTION_MULTI_STATEMENTS_ON)) { pmesg(LOG_ERROR, __FILE__, __LINE__, "MySQL query error: %s\n", mysql_error(oDB->conn)); return OPH_ODB_MYSQL_ERROR; } if (mysql_query(oDB->conn, insertQuery)) { pmesg(LOG_ERROR, __FILE__, __LINE__, "MySQL query error: %s\n", mysql_error(oDB->conn)); return OPH_ODB_MYSQL_ERROR; } pmesg(LOG_DEBUG, __FILE__, __LINE__, "Job status changed into '%s' using: %s\n", oph_odb_convert_status_to_str(status), insertQuery); return OPH_ODB_SUCCESS; }
int OGRMySQLDataSource::Open( const char * pszNewName, char** papszOpenOptionsIn, int bUpdate ) { CPLAssert( nLayers == 0 ); /* -------------------------------------------------------------------- */ /* Use options process to get .my.cnf file contents. */ /* -------------------------------------------------------------------- */ int nPort = 0; char **papszTableNames=NULL; std::string oHost, oPassword, oUser, oDB; CPLString osNewName(pszNewName); const char* apszOpenOptions[] = { "dbname", "port", "user", "password", "host", "tables" }; for(int i=0; i <(int)(sizeof(apszOpenOptions)/sizeof(char*)); i++) { const char* pszVal = CSLFetchNameValue(papszOpenOptionsIn, apszOpenOptions[i]); if( pszVal ) { if( osNewName[osNewName.size()-1] != ':' ) osNewName += ","; if( i > 0 ) { osNewName += apszOpenOptions[i]; osNewName += "="; } if( EQUAL(apszOpenOptions[i], "tables") ) { for( ; *pszVal; ++pszVal ) { if( *pszVal == ',' ) osNewName += ";"; else osNewName += *pszVal; } } else osNewName += pszVal; } } /* -------------------------------------------------------------------- */ /* Parse out connection information. */ /* -------------------------------------------------------------------- */ char **papszItems = CSLTokenizeString2( osNewName+6, ",", CSLT_HONOURSTRINGS ); if( CSLCount(papszItems) < 1 ) { CSLDestroy( papszItems ); CPLError( CE_Failure, CPLE_AppDefined, "MYSQL: request missing databasename." ); return FALSE; } oDB = papszItems[0]; for( int i = 1; papszItems[i] != NULL; i++ ) { if( STARTS_WITH_CI(papszItems[i], "user="******"password="******"host=") ) oHost = papszItems[i] + 5; else if( STARTS_WITH_CI(papszItems[i], "port=") ) nPort = atoi(papszItems[i] + 5); else if( STARTS_WITH_CI(papszItems[i], "tables=") ) { CSLDestroy(papszTableNames); papszTableNames = CSLTokenizeStringComplex( papszItems[i] + 7, ";", FALSE, FALSE ); } else CPLError( CE_Warning, CPLE_AppDefined, "'%s' in MYSQL datasource definition not recognised and ignored.", papszItems[i] ); } CSLDestroy( papszItems ); /* -------------------------------------------------------------------- */ /* Try to establish connection. */ /* -------------------------------------------------------------------- */ hConn = mysql_init( NULL ); if( hConn == NULL ) { CPLError( CE_Failure, CPLE_AppDefined, "mysql_init() failed." ); } /* -------------------------------------------------------------------- */ /* Set desired options on the connection: charset and timeout. */ /* -------------------------------------------------------------------- */ if( hConn ) { const char *pszTimeoutLength = CPLGetConfigOption( "MYSQL_TIMEOUT", "0" ); unsigned int timeout = atoi(pszTimeoutLength); mysql_options(hConn, MYSQL_OPT_CONNECT_TIMEOUT, (char*)&timeout); mysql_options(hConn, MYSQL_SET_CHARSET_NAME, "utf8" ); } /* -------------------------------------------------------------------- */ /* Perform connection. */ /* -------------------------------------------------------------------- */ if( hConn && mysql_real_connect( hConn, oHost.length() ? oHost.c_str() : NULL, oUser.length() ? oUser.c_str() : NULL, oPassword.length() ? oPassword.c_str() : NULL, oDB.length() ? oDB.c_str() : NULL, nPort, NULL, CLIENT_INTERACTIVE ) == NULL ) { CPLError( CE_Failure, CPLE_AppDefined, "MySQL connect failed for: %s\n%s", pszNewName + 6, mysql_error( hConn ) ); mysql_close( hConn ); hConn = NULL; } if( hConn == NULL ) { CSLDestroy( papszTableNames ); return FALSE; } else { // Enable automatic reconnection // Must be called after mysql_real_connect() on MySQL < 5.0.19 // and at any point on more recent versions. my_bool reconnect = 1; mysql_options(hConn, MYSQL_OPT_RECONNECT, &reconnect); } pszName = CPLStrdup( pszNewName ); bDSUpdate = bUpdate; /* -------------------------------------------------------------------- */ /* Get a list of available tables. */ /* -------------------------------------------------------------------- */ if( papszTableNames == NULL ) { MYSQL_RES *hResultSet; MYSQL_ROW papszRow; if( mysql_query( hConn, "SHOW TABLES" ) ) { ReportError( "SHOW TABLES Failed" ); return FALSE; } hResultSet = mysql_store_result( hConn ); if( hResultSet == NULL ) { ReportError( "mysql_store_result() failed on SHOW TABLES result."); return FALSE; } while( (papszRow = mysql_fetch_row( hResultSet )) != NULL ) { if( papszRow[0] == NULL ) continue; if( EQUAL(papszRow[0],"spatial_ref_sys") || EQUAL(papszRow[0],"geometry_columns") ) continue; papszTableNames = CSLAddString(papszTableNames, papszRow[0] ); } mysql_free_result( hResultSet ); } /* -------------------------------------------------------------------- */ /* Get the schema of the available tables. */ /* -------------------------------------------------------------------- */ int iRecord; for( iRecord = 0; papszTableNames != NULL && papszTableNames[iRecord] != NULL; iRecord++ ) { // FIXME: This should be fixed to deal with tables // for which we can't open because the name is bad/ OpenTable( papszTableNames[iRecord], bUpdate ); } CSLDestroy( papszTableNames ); return nLayers > 0 || bUpdate; }
/** * Returns the id of the exporter table entry or 0 in the case of an error */ int IpfixDbWriter::getExporterID(const IpfixRecord::SourceID& sourceID) { list<ExporterCacheEntry>::iterator iter; MYSQL_RES* dbResult; MYSQL_ROW dbRow; int id = 0; uint32_t expIp = 0; ostringstream statement; iter = exporterCache.begin(); while(iter != exporterCache.end()) { if (equalExporter(iter->sourceID, sourceID)) { // found exporter in exporterCache DPRINTF("Exporter (ODID=%d, id=%d) found in exporter cache", sourceID.observationDomainId, iter->id); exporterCache.push_front(*iter); exporterCache.erase(iter); // update current exporter currentExporter = &exporterCache.front(); return exporterCache.front().id; } iter++; } // convert IP address if(sourceID.exporterAddress.len == 4) expIp = *(uint32_t*)(sourceID.exporterAddress.ip); // search exporter table statement << "SELECT id FROM exporter WHERE sourceID=" << sourceID.observationDomainId << " AND srcIp=" << expIp; DPRINTF("SQL Query: %s", statement.str().c_str()); if(mysql_query(conn, statement.str().c_str()) != 0) { msg(MSG_ERROR,"IpfixDbWriter: Select on exporter table failed. Error: %s", mysql_error(conn)); return 0;// If a failure occurs, return 0 } dbResult = mysql_store_result(conn); if(( dbRow = mysql_fetch_row(dbResult))) { // found in table id = atoi(dbRow[0]); mysql_free_result(dbResult); DPRINTF("ExporterID %d is in exporter table", id); } else { mysql_free_result(dbResult); // insert new exporter table entry statement.str(""); statement.clear(); statement << "INSERT INTO exporter (ID,sourceID,srcIP) VALUES ('NULL','" << sourceID.observationDomainId << "','" << expIp << "')"; DPRINTF("SQL Query: %s", statement.str().c_str()); if(mysql_query(conn, statement.str().c_str()) != 0) { msg(MSG_ERROR,"IpfixDbWriter: Insert in exporter table failed. Error: %s", conn); return 0; } id = mysql_insert_id(conn); msg(MSG_INFO,"IpfixDbWriter: new exporter (ODID=%d, id=%d) inserted in exporter table", sourceID.observationDomainId, id); } // insert exporter in cache ExporterCacheEntry tmp = {sourceID, id}; exporterCache.push_front(tmp); // update current exporter currentExporter = &exporterCache.front(); // pop last element if exporter cache is to long if(exporterCache.size() > MAX_EXPORTER) exporterCache.pop_back(); return id; }
int stmd_RuleSetSelect(int time_type, char *table_type) { char condBuf[4096], tmpBuf[1024]; int sts_code; char str_time[10]; int snd_cnt = 0; char query[4096], query_head[4096]; MYSQL_RES *result; MYSQL_ROW row; time_t now; char tm[DIR_NUM][8]; char path[60]; char fileName[60]; FILE *fp; int i,j; char title[4][16] = {"SYSTEM", "RULE SET", "Session", "Block"}; char title1[5][16] = {"", "", "UpStream(Mbps)", "DnStream(Mbps)", "Total(Mbps)"}; char title2[5][16] = {"", "", "UpByte(MBytes)", "DnByte(MBytes)", "Total(MBytes)"}; int row_index; char SysName[2][8]; int realSysCnt =0; int realItemCnt =0, row_cnt=0, index; for(i=0; i<SCE_CNT; i++ ) { sprintf(SysName[i], "%s", g_stSCE[i].sce_name); realSysCnt++; } now = time(0); if(time_type != STMD_WEEK) now = now - (printTIME[time_type]*60) - STAT_OFFSET_UNIT; else now = now - (printTIME[time_type]*60); getFilePath (path, tm, &now); // ~/LOG/STAT 까지 만든다. makeDirectory (time_type,path,tm); sprintf (fileName, "%s", path ); makeFileName ( fileName, STMD_RULE_SET, time_type, tm ); if ( ( fp = fopen(fileName, APPEND ) ) == NULL ) { sprintf(trcBuf, "%s Open Fail\n", fileName); trclib_writeLog(FL, trcBuf); return -1; } //char title[4][16] = {"SYSTEM", "RULE SET", "Session", "Block"}; //char title1[5][16] = {"", "", "UpStream(Mbps)", "DnStream(Mbps)", "Total(Mbps)"}; //char title2[5][16] = {"", "", "UpByte(MBytes)", "DnByte(MBytes)", "Total(MBytes)"}; switch (time_type) { case STMD_HOUR : sts_code = STSCODE_STM_PERIODIC_RULESET_HOUR; sprintf(str_time, "%s", STMD_STR_HOUR); sprintf(query_head, "SELECT pkg_id, rule_set_id, rule_set_name, " " IFNULL(SUM(session),0), " " IFNULL(SUM(block_cnt),0), " " IFNULL(ROUND(SUM(upstream_volume)*8/1024/1024/3600,3),0), " " IFNULL(ROUND(SUM(downstream_volume)*8/1024/1024/3600,3),0), " " ROUND((IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))*8/1024/1024/3600, 3), " " IFNULL(ROUND(SUM(upstream_volume)/1024/1024,3),0), " " IFNULL(ROUND(SUM(downstream_volume)/1024/1024,3),0), " " ROUND((IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))/1024/1024, 3) " " from %s ", table_type); break; case STMD_DAY : sts_code = STSCODE_STM_PERIODIC_RULESET_DAY; sprintf(str_time, "%s", STMD_STR_DAY); sprintf(query_head, "SELECT pkg_id, rule_set_id, rule_set_name, " " IFNULL(SUM(session),0), " " IFNULL(SUM(block_cnt),0), " " IFNULL(ROUND(SUM(upstream_volume)*8/1024/1024/86400,3),0), " " IFNULL(ROUND(SUM(downstream_volume)*8/1024/1024/86400,3),0), " " ROUND((IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))*8/1024/1024/86400, 3), " " IFNULL(ROUND(SUM(upstream_volume)/1024/1024,3),0), " " IFNULL(ROUND(SUM(downstream_volume)/1024/1024,3),0), " " ROUND((IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))/1024/1024, 3) " " from %s ", table_type); break; case STMD_WEEK : sts_code = STSCODE_STM_PERIODIC_RULESET_WEEK; sprintf(str_time, "%s", STMD_STR_WEEK); sprintf(query_head, "SELECT pkg_id, rule_set_id, rule_set_name, " " IFNULL(SUM(session),0), " " IFNULL(SUM(block_cnt),0), " " ROUND(IFNULL(SUM(upstream_volume)*8/1024/1024/(86400*7),0),3), " " ROUND(IFNULL(SUM(downstream_volume)*8/1024/1024/(86400*7),0),3), " " ROUND((IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))*8/1024/1024/(86400*7), 3), " " ROUND(IFNULL(SUM(upstream_volume)/1024/1024,0),3), " " ROUND(IFNULL(SUM(downstream_volume)/1024/1024,0),3), " " ROUND( (IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))/1024/1024, 3) " " from %s ", table_type); break; case STMD_MONTH : sts_code = STSCODE_STM_PERIODIC_RULESET_MONTH; sprintf(str_time, "%s", STMD_STR_MONTH); sprintf(query_head, "SELECT pkg_id, rule_set_id, rule_set_name, " " IFNULL(SUM(session),0), " " IFNULL(SUM(block_cnt),0), " " ROUND(IFNULL(SUM(upstream_volume)*8/1024/1024/(86400*30),0),3), " " ROUND(IFNULL(SUM(downstream_volume)*8/1024/1024/(86400*30),0),3), " " ROUND((IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))*8/1024/1024/(86400*30), 3), " " ROUND(IFNULL(SUM(upstream_volume)/1024/1024,0),3), " " ROUND(IFNULL(SUM(downstream_volume)/1024/1024,0),3), " " ROUND( (IFNULL(SUM(upstream_volume),0) + IFNULL(SUM(downstream_volume),0))/1024/1024, 3) " " from %s ", table_type); break; } sprintf(condBuf," %s %s\n S%04d RULE SET PERIODIC STATISTICS MESSAGE\n", "SCE", // 현재는 OMP로 고정하지만 실질적인 시스템 이름으로 변경 필요 hslim_oper commlib_printTStamp(), // 현재시각 time stamp (년,월,일,시,분,초,요일) sts_code); sprintf(tmpBuf, " PERIOD = %s\n MEASURETIME = %s - %s\n\n", str_time, get_period_start_time(time_type), get_period_end_time(time_type)); strcat(condBuf,tmpBuf); sprintf(tmpBuf, " =======================================================================================\n"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s \n","",title[0],title[1],title[2],title[3]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n","","","",title1[2],title1[3],title1[4]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n","","","",title2[2],title2[3],title2[4]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, " =======================================================================================\n"); strcat(condBuf, tmpBuf); for(i = 0; i < realSysCnt; i++) { sprintf(query, "%s " " where record_source = '%s' AND stat_date = '%s' " " group by pkg_id, rule_set_id, rule_set_name " " order by pkg_id, rule_set_id, rule_set_name ", query_head, SysName[i], get_period_select_time(time_type)); realItemCnt = g_stSCERule[i].ruleSetCnt; logPrint(trcLogId, FL, "periodic query: %s \n",query); if ( trcLogFlag == TRCLEVEL_SQL ) { sprintf(trcBuf, "query = %s\n", query); trclib_writeLog(FL, trcBuf); } if (stmd_mysql_query (query) < 0) { sprintf(trcBuf,">>> mysql_query fail; query:%s, err=%s\n", query,mysql_error(conn)); trclib_writeLogErr (FL,trcBuf); if(fp != NULL) fclose(fp); return -1; } row_cnt = 0; result = mysql_store_result(conn); while( (row = mysql_fetch_row(result)) != NULL) { row_index = 3; if( row_cnt == 0 ) { sprintf(tmpBuf, "%3s %6s %-28s %15s %15s\n", "",SysName[i], row[2], row[row_index],row[row_index+1]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "","", "",row[row_index+2],row[row_index+3],row[row_index+4]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "","", "",row[row_index+5],row[row_index+6],row[row_index+7]); strcat(condBuf, tmpBuf); } else { sprintf(tmpBuf, "%3s %6s %-28s %15s %15s \n", "","", row[2],row[row_index],row[row_index+1]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "","", "",row[row_index+2],row[row_index+3],row[row_index+4]); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "","", "",row[row_index+5],row[row_index+6],row[row_index+7]); strcat(condBuf, tmpBuf); } if(strlen(condBuf) > 3000) { strcat(condBuf, "\nCONTINUE\n"); stmd_txMsg2Cond (condBuf, (sts_code - STSCODE_TO_MSGID_STATISTICS), 1, snd_cnt); fprintf (fp, "%s",condBuf); memset(condBuf, 0x00, sizeof(condBuf)); } row_cnt++; } mysql_free_result(result); if( row_cnt == 0 ) // no query result set { for( j = 0; j < realItemCnt; j++ ) { index = g_ruleIdBuf[j]; if( j == 0 ) { sprintf(tmpBuf, "%3s %6s %-28s %15s %15s \n", "", SysName[i], g_stSCERule[i].stRule[index].rName, "0", "0"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "", "", "", "0.000", "0.000", "0.000"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "", "", "", "0.000","0.000","0.000"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, " --------------------------------------------------------------------------------------\n"); strcat(condBuf, tmpBuf); } else { sprintf(tmpBuf, "%3s %6s %-28s %15s %15s \n", "", "", g_stSCERule[i].stRule[index].rName, "0","0"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "", "", "", "0.000","0.000","0.000"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, "%3s %6s %-28s %15s %15s %15s\n", "", "", "", "0.000","0.000","0.000"); strcat(condBuf, tmpBuf); sprintf(tmpBuf, " ---------------------------------------------------------------------------------------\n"); } if(strlen(condBuf) > 3000) { strcat(condBuf, "\nCONTINUE\n"); stmd_cron_txMsg2Cond (condBuf, (sts_code - STSCODE_TO_MSGID_STATISTICS), 1, snd_cnt); fprintf (fp, "%s",condBuf); memset(condBuf, 0x00, sizeof(condBuf)); } } } sprintf(tmpBuf, " =======================================================================================\n"); strcat(condBuf, tmpBuf); } sprintf(tmpBuf, " COMPLETED\n\n\n"); strcat(condBuf, tmpBuf); if(fp != NULL) { fprintf (fp, "%s",condBuf); fclose(fp); } stmd_txMsg2Cond(condBuf, (sts_code - STSCODE_TO_MSGID_STATISTICS), 0, 1); return 1; }
// Print the right error message to standard output void print_error( RETURNCODE code ){ const char *pErrorMsg; if( bUseColor ) pErrorMsg = "\x1B[0;31m ERROR:\x1B[1;37m"; else pErrorMsg = " ERROR:"; switch( code ){ case NO_DB_SELECTED: printf("%s no database selected, please use one.\n", pErrorMsg); break; case NO_FLAG_SPECIFIED: printf("%s no flags specified, cannot continue.\n", pErrorMsg); break; case TOO_MANY_DB: printf("%s Too many Database selected, please use only one.\n", pErrorMsg); break; case MYSQL_PARAM_ERROR: printf("%s MySQL invalid parameters, please see the help menu.\n", pErrorMsg); break; case SQLITE_PARAM_ERROR: printf("%s SQLite invalid parameters, please see the help menu.\n", pErrorMsg); break; case CREATE_TABLE_ERROR: printf("%s Create table invalid parameter, please see the help menu.\n", pErrorMsg); break; case UNKNOW_PARAM: printf("%s: invalid option: %s\nTry `%s --help' for more information.\n", pProgramName, pError, pProgramName); break; case OPENDIR_ERROR: printf("%s While opening source directory.\n", pErrorMsg); break; case VERSION_USAGE_OUTPUTTED: // do nothing break; case BAD_FORMAT: printf( "%s Bad filenameformat entered: %s\n", pErrorMsg, pFileNameFormat ); break; case FNFORMAT_PARAM_ERROR: printf("%s Filename invalid parameters, please see the help menu.\n", pErrorMsg); break; case SPACECHAR_PARAM_ERROR: printf("%s Space character invalid parameters, please see the help menu.\n", pErrorMsg); break; case DBOPEN_ERROR: switch( UseDB ){ #ifdef __MYSQL case USE_MYSQL: printf( "%s %s\n", pErrorMsg, mysql_error( DB_handle.mysql_handle ) ); break; #endif #ifdef __SQLITE case USE_SQLITE: printf( "%s Unable to open SQLite database file.\n", pErrorMsg ); break; #endif default: break; } break; case DBUNKNOWN_ERROR: printf( "%s DB no proper selected.\n", pErrorMsg ); break; default: printf("%s Unknown error message recived, exit.\n", pErrorMsg); break; } printf( "\x1B[0m" ); // force the reset of the shell color scheme exit(0); }
void dbInit(char * iniFilename, int channelCount, char dbColumns[][MAX_COLUMN_NAME_LEN], char dbTypes[][MAX_COLUMN_TYPE_LEN]) { char server[30]; char database[30]; char rootUsername[30]; char rootPassword[30]; char username[30]; char password[30]; char query[2000]; int errno; int channelNr; strcpy(_iniFilename, iniFilename); _channelCount = channelCount; GetPrivateProfileString_("DB", "root_username", "root", rootUsername, sizeof(rootUsername), _iniFilename); GetPrivateProfileString_("DB", "root_password", "", rootPassword, sizeof(rootPassword), _iniFilename); GetPrivateProfileString_("DB", "username", "sma", username, sizeof(username), _iniFilename); GetPrivateProfileString_("DB", "password", "smysqlogger", password, sizeof(password), _iniFilename); GetPrivateProfileString_("DB", "server", "localhost", server, sizeof(server), _iniFilename); GetPrivateProfileString_("DB", "database", "sma", database, sizeof(database), _iniFilename); printLog(LEVEL_DETAIL, "Connecting to MySQL as %s@%s:%s (client version: %s)\n", trim(username), trim(server), trim(database), mysql_get_client_info()); // Init connection structure _conn = mysql_init(NULL); if (_conn == NULL) { printLog(LEVEL_FATAL, "Error %u initializing connection structure : %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-10); } // Connect to DB if (mysql_real_connect(_conn, server, username, password, database, 0, NULL, 0) == NULL) { errno = mysql_errno(_conn); if (errno != 1049 && errno != 1045) { printLog(LEVEL_FATAL, "Error %u connecting to Database: %s\n", errno, mysql_error(_conn)); exit(-11); } else { // DB instance doesn't exist. Let's create it. // Information is sent to stdout and not logged to file for this one-shot code printf("Database '%s' doesn't exist. Trying to create it...", database); // Connect without specifying DB if (mysql_real_connect(_conn, server, rootUsername, rootPassword, NULL, 0, NULL, 0) == NULL) { printf("Error %u connecting to Database: %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-12); } // Create DB if it doesn't exist sprintf(query, "create database if not exists %s", database); if (mysql_query(_conn, query)) { printf("Error %u creating Database: %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-13); } // Create User if it doesn't exist sprintf(query, "create user %s@localhost identified by '%s'", username, password); if (mysql_query(_conn, query)) { printf("Error %u creating user: %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-14); } // Grant User rights sprintf(query, "GRANT ALL PRIVILEGES ON %s.* TO %s@localhost;", database, username); if (mysql_query(_conn, query)) { printf("Error %u granting rights: %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-15); } // Succeeded. Let's reconnect to new DB mysql_close(_conn); printf("Done. Reconnecting to newly created database...\n", database); _conn = mysql_init(NULL); if (_conn == NULL) { printf("Error %u re-initializing connection structure : %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-16); } if (mysql_real_connect(_conn, server, username, password, database, 0, NULL, 0) == NULL) { printLog(LEVEL_FATAL, "Error %u connecting to new Database: %s\n", mysql_errno(_conn), mysql_error(_conn)); exit(-17); } printf("\nCongratulations. A new database called '%s' has been created, with the table 'logged_values'.\n\ A new user '%s' has also been created and was granted access to the new DB.\n\ It is now strongly recommended to edit yasdi.ini to remove the\n\ root_username/root_password information.\n\nWaiting 20 seconds...\n", database, username); fflush(stdout); sleep(20); } }
BOOL SlaveStatus(const char * strParas , char * szReturn, int &nSize) { AFX_MANAGE_STATE(AfxGetStaticModuleState()); bool bRet = false; CString strHost = _T(""); CString strUserName = _T(""); CString strPWD = _T(""); int nPort = 3306; MYSQL_RES * res; //MYSQL数据库的记录集指针 MYSQL_ROW row; MYSQL *mysql; //接收MySQL数据的对象 CStringList paramList; MakeStringListByChar(paramList, strParas); POSITION pos = paramList.GetHeadPosition(); while(pos) { CString strTemp = paramList.GetNext(pos); if(strTemp.Find(__MACHINENAME__, 0)==0) { strHost = strTemp.Right(strTemp.GetLength() - (int)strlen(__MACHINENAME__)); } else if(strTemp.Find(__PORT__, 0)==0) { nPort = atoi(strTemp.Right(strTemp.GetLength() - (int)strlen(__PORT__))); } else if(strTemp.Find(__USERACCOUNT__ , 0)==0) { strUserName = strTemp.Right(strTemp.GetLength() - (int)strlen(__USERACCOUNT__)); } else if(strTemp.Find(__PASSWORD__ , 0)==0) { strPWD = strTemp.Right(strTemp.GetLength() - (int)strlen(__PASSWORD__)); } } if(strHost.IsEmpty()) { sprintf(szReturn,"error=%s",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_HOST_IS_NULL")); return false; } if(nPort<=0) { sprintf(szReturn,"error=%s",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_PORT_IS_BAD")); return false; } unsigned int i=0; CString strTMP = _T(""); ////////////////////////////////////////////////// CString strSlaveIOState = _T(""); CString strMasterHost = _T(""); CString strMasterUser = _T(""); CString strSlaveIORunning = _T(""); //Slave是否在运行 CString strSlaveSQLRunning = _T(""); int nMasterPort = 3306; CString strSecondsBehindMaster = _T(""); //最后一次同步时间 ////////////////////////////////////////////////// if((mysql = mysql_init((MYSQL *)NULL)) && mysql_real_connect(mysql,strHost, strUserName, strPWD, NULL, nPort, NULL, 0)) { mysql_query(mysql, "show slave status"); res = mysql_store_result(mysql); while(row = mysql_fetch_row(res)) { /* PrintLog(row[1]); PrintLog(row[2]); PrintLog(row[3]); for (i=0; i<mysql_num_fields(res); i++) { PrintLog(row[i]); } */ strSlaveIOState = row[0]; strMasterHost = row[1]; strMasterUser = row[2]; nMasterPort = atoi(row[3]); strSlaveIORunning =row[10]; strSlaveSQLRunning = row[11]; strSecondsBehindMaster = row[32]; } if(strSlaveIOState.IsEmpty()) strSlaveIOState="null"; if(strMasterHost.IsEmpty()) strMasterHost="null"; if(strMasterUser.IsEmpty()) strMasterUser="******"; if(strSlaveIORunning.IsEmpty()) strSlaveIORunning="null"; sprintf(szReturn,"SlaveIOState=%s$MasterHost=%s$MasterUser=%s$MasterPort=%d$SlaveIORunning=%s$SlaveSQLRunning=%s$SecondsBehindMaster=%s$", strSlaveIOState.GetBuffer(1), strMasterHost.GetBuffer(1), strMasterUser.GetBuffer(1), nMasterPort, strSlaveIORunning.GetBuffer(1), strSlaveSQLRunning.GetBuffer(1), strSecondsBehindMaster.GetBuffer(1)); CString strInput ; strInput =szReturn; MakeCharByString(szReturn,nSize,strInput); mysql_free_result(res); bRet = TRUE; } else { sprintf(szReturn, "error=%s(%s:%d)$", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_CONNECT_DATABASE_FAILED"), strHost, nPort); fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(mysql)); mysql_close( mysql ) ; return FALSE; } mysql_close(mysql); /* char * strEnd = szReturn; while(*strEnd) { PrintLog(strEnd); strEnd+=strlen(strEnd)+1; } */ return bRet; }
BOOL MemoryStatus(const char * strParas , char * szReturn, int &nSize) { AFX_MANAGE_STATE(AfxGetStaticModuleState()); bool bRet = FALSE; CString strHost = _T(""); CString strUserName = _T(""); CString strPWD = _T(""); int nPort = 3306; //MySQL的默认端口号 MYSQL * mysql; //MySQL对象 MYSQL_RES * res; //MySQL记录集指针 MYSQL_ROW row; //MySQL行指针 CStringList strList; MakeStringListByChar(strList, strParas); POSITION pos=strList.GetHeadPosition(); while(pos) { CString strTemp=strList.GetNext(pos); if(strTemp.Find(__MACHINENAME__)==0) { strHost = strTemp.Right(strTemp.GetLength() - (int)strlen(__MACHINENAME__)); } else if(strTemp.Find(__USERACCOUNT__)==0) { strUserName = strTemp.Right(strTemp.GetLength() - (int)strlen(__USERACCOUNT__)); } else if(strTemp.Find(__PASSWORD__)==0) { strPWD = strTemp.Right(strTemp.GetLength() - (int)strlen(__PASSWORD__)); } else if(strTemp.Find(__PORT__)==0) { nPort = atoi(strTemp.Right(strTemp.GetLength() - (int)strlen(__PORT__))); } } if(strHost.IsEmpty()) { sprintf(szReturn,"error=%s$",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_HOST_IS_NULL")); return FALSE; } if(nPort<=0) { sprintf(szReturn,"error=%s$",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_PORT_IS_BAD")); return FALSE; } //////////////////////////////////////////////// int Innodb_page_size=0; //页大小(单位:字节) int Innodb_buffer_pool_pages_total =0; //缓冲池总大小(单位:页数) int Innodb_buffer_pool_pages_free = 0; //空页数(单位:页) int Innodb_buffer_pool_pages_data = 0; //包含数据的页(单位:页) int Innodb_buffer_pool_pages_dirty =0; //当前的脏页(单位:页),脏页是指该页包含尚未写磁盘的修改 int Innodb_buffer_pool_pages_latched = 0; //在InnoDB缓冲池中锁定的页数。这是当前正读或写或由于其它原因不能清空或删除的页数 unsigned int nCurrentID=0; //////////////////////////////////////////////// if((mysql = mysql_init((MYSQL *)NULL)) && mysql_real_connect(mysql, strHost, strUserName, strPWD, NULL, nPort, NULL, 0)) { mysql_query(mysql,"show status"); res = mysql_store_result(mysql); while(row = mysql_fetch_row(res)) { if(!strcmp(row[0], "Innodb_page_size")) { Innodb_page_size=atoi(row[1]); Innodb_page_size=Innodb_page_size/1024; //将页的单位从字节转化为Kb } else if(!strcmp(row[0], "Innodb_buffer_pool_pages_total")) { Innodb_buffer_pool_pages_total = atoi(row[1]); } else if(!strcmp(row[0], "Innodb_buffer_pool_pages_free")) { Innodb_buffer_pool_pages_free = atoi(row[1]); } else if(!strcmp(row[0], "Innodb_buffer_pool_pages_data")) { Innodb_buffer_pool_pages_data = atoi(row[1]); } else if(!strcmp(row[0], "Innodb_buffer_pool_pages_dirty")) { Innodb_buffer_pool_pages_dirty = atoi(row[1]); } else if(!strcmp(row[0], "Innodb_buffer_pool_pages_latched")) { Innodb_buffer_pool_pages_latched = atoi(row[1]); } } sprintf(szReturn,"PageSize=%d$TotalPages=%d$FreePages=%d$DataPages=%d$DirtyPages=%d$LatchedPages=%d$", Innodb_page_size, Innodb_buffer_pool_pages_total, Innodb_buffer_pool_pages_free, Innodb_buffer_pool_pages_data, Innodb_buffer_pool_pages_dirty, Innodb_buffer_pool_pages_latched); CString strInput ; strInput =szReturn; MakeCharByString(szReturn,nSize,strInput); mysql_free_result(res); bRet = TRUE; } else { sprintf(szReturn, "error=%s(%s:%d)$", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_CONNECT_DATABASE_FAILED"), strHost, nPort); fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(mysql)); mysql_close( mysql ) ; return FALSE; } /* const char * strEnd = szReturn; while(*strEnd) { PrintLog(strEnd); strEnd+=strlen(strEnd)+1; } */ mysql_close(mysql); return TRUE; }
BOOL ProList(const char * strParas , char * szReturn , int &nSize) { AFX_MANAGE_STATE(AfxGetStaticModuleState()); bool bRet = false; CString strHost = _T(""); CString strUserName = _T(""); CString strPWD = _T(""); int nPort = 3306; //MySQL的默认端口号是3306 MYSQL_RES * res; //MYSQL数据库的记录集指针 MYSQL_ROW row; MYSQL *mysql; //接收MySQL数据的对象 CStringList paramList; MakeStringListByChar(paramList, strParas); POSITION pos = paramList.GetHeadPosition(); while(pos) { CString strTemp = paramList.GetNext(pos); if(strTemp.Find(__MACHINENAME__, 0)==0) { strHost = strTemp.Right(strTemp.GetLength() - (int)strlen(__MACHINENAME__)); } else if(strTemp.Find(__PORT__, 0)==0) { nPort = atoi(strTemp.Right(strTemp.GetLength() - (int)strlen(__PORT__))); } else if(strTemp.Find(__USERACCOUNT__ , 0)==0) { strUserName = strTemp.Right(strTemp.GetLength() - (int)strlen(__USERACCOUNT__)); } else if(strTemp.Find(__PASSWORD__ , 0)==0) { strPWD = strTemp.Right(strTemp.GetLength() - (int)strlen(__PASSWORD__)); } } if(strHost.IsEmpty()) { sprintf(szReturn,"error = %s",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_HOST_IS_NULL")); return false; } if(nPort<=0) { sprintf(szReturn,"error = %s",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_PORT_IS_BAD")); return false; } unsigned int i=0; CString strTMP = _T(""); ////////////////////////////////////////////////// int nID =0; //MYSQL线程标识,你要kill一个语句的时候很有用 unsigned long nCurrentID = 0; ////////////////////////////////////////////////// if((mysql = mysql_init((MYSQL *)NULL)) && mysql_real_connect(mysql,strHost, strUserName, strPWD, NULL, nPort, NULL, 0)) { res = mysql_list_processes(mysql); nCurrentID = mysql_thread_id(mysql); //获取当前线程ID号。 while(row = mysql_fetch_row(res)) { nID = atoi(row[0]); if(nID==nCurrentID) { break; } sprintf(szReturn,"%d=%d$",nID,nID); strTMP+=szReturn; } sprintf(szReturn,"%s$",strTMP.GetBuffer(1)); CString strInput ; strInput =szReturn; MakeCharByString(szReturn,nSize,strInput); mysql_free_result(res); //关闭查询的结果集 mysql_close(mysql); bRet = TRUE; } else { sprintf(szReturn, "error=%s(%s:%d)$", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_CONNECT_DATABASE_FAILED"), strHost, nPort); fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(mysql)); mysql_close( mysql ) ; return FALSE; } /* const char * strEnd = szReturn; while(*strEnd) { PrintLog(strEnd); strEnd+=strlen(strEnd)+1; } */ return TRUE; }
BOOL ProInfo(const char * strParas , char * szReturn , int &nSize) { AFX_MANAGE_STATE(AfxGetStaticModuleState()); bool bRet = false; CString strHost = _T(""); CString strUserName = _T(""); CString strPWD = _T(""); int nProID=0; //保存传进来的进程ID号 int nPort = 3306; //MySQL的默认端口号是3306 MYSQL_RES * res; //MYSQL数据库的记录集指针 MYSQL_ROW row; MYSQL *mysql; //接收MySQL数据的对象 CStringList paramList; MakeStringListByChar(paramList, strParas); POSITION pos = paramList.GetHeadPosition(); while(pos) { CString strTemp = paramList.GetNext(pos); if(strTemp.Find(__MACHINENAME__, 0)==0) { strHost = strTemp.Right(strTemp.GetLength() - (int)strlen(__MACHINENAME__)); } else if(strTemp.Find(__PORT__, 0)==0) { nPort = atoi(strTemp.Right(strTemp.GetLength() - (int)strlen(__PORT__))); } else if(strTemp.Find(__USERACCOUNT__ , 0)==0) { strUserName = strTemp.Right(strTemp.GetLength() - (int)strlen(__USERACCOUNT__)); } else if(strTemp.Find(__PASSWORD__ , 0)==0) { strPWD = strTemp.Right(strTemp.GetLength() - (int)strlen(__PASSWORD__)); } else if(strTemp.Find(__PROID__, 0)==0) { nProID = atoi(strTemp.Right(strTemp.GetLength() - (int)strlen(__PROID__))); } } if(strHost.IsEmpty()) { sprintf(szReturn,"error = %s",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_HOST_IS_NULL")); return false; } if(nPort<=0) { sprintf(szReturn,"error = %s",FuncGetStringFromIDS("SV_MYSQL","MYSQL_SERVER_PORT_IS_BAD")); return false; } unsigned int i=0; CString strTMP = _T(""); ////////////////////////////////////////////////// int nID = 0; //MYSQL线程标识,你要kill一个语句的时候很有用 CString strDBUser = _T(""); //user列,显示当前用户,如果不是root,这个命令就只显示你权限范围内的sql语句 CString strDBHost = _T(""); //host列,显示这个语句是从哪个ip的哪个端口上发出的 CString strDatabase = _T(""); //db列,显示这个进程目前连接的是哪个数据库 CString strCommand = _T(""); //command列,显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。 int nTime = 0; //time列,此这个状态持续的时间,单位是秒 CString strState = _T(""); //state列,显示使用当前连接的sql语句的状态 CString strInfo = _T(""); //info列,显示这个sql语句,因为长度有限,所以长的sql语句就显示不全 unsigned long nCurrentID = 0; //当前线程ID int nTotalConn=0; //连接总数 map<CString,int> hostMap; map<CString,int>::const_iterator ptrHost; typedef pair <CString,int> hostPair; ////////////////////////////////////////////////// if((mysql = mysql_init((MYSQL *)NULL)) && mysql_real_connect(mysql,strHost, strUserName, strPWD, NULL, nPort, NULL, 0)) { // res = mysql_list_processes(mysql); CString strCommand=_T(""); mysql_query(mysql,"show processlist"); res = mysql_store_result(mysql); nCurrentID = mysql_thread_id(mysql); //获取当前线程ID strCommand.Format("kill %d",nCurrentID); while(row = mysql_fetch_row(res)) { nID = atoi(row[0]); strDBUser = row[1]; strDBHost = row[2]; strDatabase = row[3]; strCommand = row[4]; nTime = atoi(row[5]); strState = row[6]; strInfo = row[7]; strDBHost=strDBHost.Left(strDBHost.Find(':')); //擦掉获得的主机端口号 ptrHost = hostMap.find(strDBHost.GetBuffer(strDBHost.GetLength())); if(nID!=nCurrentID) { if(ptrHost==hostMap.end()) { hostMap.insert(hostPair(strDBHost.GetBuffer(strDBHost.GetLength()),1)); } //用MAP保存主机名,并且将主机出现次数统计在键值中 else { hostMap[strDBHost.GetBuffer(strDBHost.GetLength())]++; } } /* else { PrintLog(row[0]); PrintLog(row[2]); } */ nTotalConn++; } nTotalConn=nTotalConn-1; //减去自身的连接 for(ptrHost=hostMap.begin();ptrHost!=hostMap.end();ptrHost++) { strTMP.Format("%s%s的连接数是%d,",strTMP.GetBuffer(strTMP.GetLength()),ptrHost->first,ptrHost->second); } strTMP.Delete(strTMP.GetLength()-1,1); //去除返回值后多余的一个“,” sprintf(szReturn,"nTotalConn=%d$result=%s",nTotalConn,strTMP.GetBuffer(strTMP.GetLength())); CString strInput ; strInput =szReturn; MakeCharByString(szReturn,nSize,strInput); mysql_free_result(res); //释放结果集使用的内存。 mysql_close(mysql); // 关闭服务器连接。 bRet = TRUE; } else { sprintf(szReturn, "error=%s(%s:%d)$", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_CONNECT_DATABASE_FAILED"), strHost, nPort); fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(mysql)); mysql_close( mysql ) ; return FALSE; } /* const char * strEnd=szReturn; while(*strEnd) { PrintLog(strEnd); strEnd+=strlen(strEnd)+1; } */ return bRet; }
BOOL MySQL(const char * strParas, char * szReturn, int& nSize) //(CStringList ¶mList, char *szReturn) { BOOL bRet = TRUE; CString strHost = _T(""), //MySqL Server //strDBName = _T("mysql"),//Database Name,default mysql strUser = _T(""), //UserName strPwd = _T("");// Password int nPort = 3306;//MySQL Server Port,default 3306 ///////////////////////////////////////////////////////////////////////////// const char *pszT;//Temp string MYSQL * myData ;//Access Mysql objcect ///////////////////////////////////////////////////////////////////////////// CStringList paramList; MakeStringListByChar(paramList,strParas); POSITION pos = paramList.GetHeadPosition(); while(pos) { CString strTemp = paramList.GetNext(pos); if(strTemp.Find(__MACHINENAME__, 0) == 0) {//Get Host strHost = strTemp.Right(strTemp.GetLength() - (int)strlen(__MACHINENAME__)); } else if(strTemp.Find(__PORT__, 0) == 0) {//Get Port nPort = atoi(strTemp.Right(strTemp.GetLength() - (int)strlen(__PORT__))); } else if(strTemp.Find(__USERACCOUNT__, 0) == 0) {//Get Username strUser = strTemp.Right(strTemp.GetLength() - (int)strlen(__USERACCOUNT__)); } else if(strTemp.Find(__PASSWORD__, 0) == 0) {//Get password strPwd = strTemp.Right(strTemp.GetLength() - (int)strlen(__PASSWORD__)); } /*else if(strTemp.Find(__DBNAME__, 0) == 0) {//Get database name strDBName = strTemp.Right(strTemp.GetLength() - strlen(__DBNAME__)); }*/ } if(strHost.IsEmpty()) {//host is empty sprintf(szReturn, "error=%s", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_SERVER_HOST_IS_NULL")); return FALSE; } if(nPort <= 0) {//Invalid port sprintf(szReturn, "error=%s", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_SERVER_PORT_IS_BAD")); return FALSE; } printf("----------%s--%s--%s--------\n",strHost,strUser,strPwd); if((myData = mysql_init((MYSQL*) 0)) && mysql_real_connect( myData, strHost, strUser, strPwd, NULL, nPort, NULL, 0 ) ) { /*if ( mysql_select_db( myData, strDBName ) < 0 ) { sprintf(szReturn, "error=%s(%s)$", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_SELECT_DATABASE_FAILED"), strDBName); //printf( "Can't select the %s database !\n", strDBName) ; mysql_close( myData ) ; return FALSE; }*/ pszT = mysql_stat( myData ) ; //mysql_stat(MYSQL * myData)取得目录数据库系统状况 if(pszT) { Parser(pszT, strHost.GetBuffer(strHost.GetLength()), szReturn); // mysql_free_result(pszT); CString strInput ; strInput =szReturn; MakeCharByString(szReturn,nSize,strInput); } else { sprintf(szReturn, "error=%s", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_QUERY_STAT_FAILED")); mysql_close( myData ) ; return FALSE; } } else { sprintf(szReturn, "error=%s(%s:%d)$", FuncGetStringFromIDS("SV_MYSQL", "MYSQL_CONNECT_DATABASE_FAILED"), strHost, nPort); //printf( "Can't connect to the mysql server on port %d !\n", // MYSQL_PORT ) ; fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(myData)); mysql_close( myData ) ; return FALSE; } mysql_close( myData ) ; return TRUE; }
bool MySQLConnection::Open() { MYSQL *mysqlInit; mysqlInit = mysql_init(NULL); if (!mysqlInit) { TC_LOG_ERROR("sql.sql", "Could not initialize Mysql connection to database `%s`", m_connectionInfo.database.c_str()); return false; } int port; char const* unix_socket; //unsigned int timeout = 10; mysql_options(mysqlInit, MYSQL_SET_CHARSET_NAME, "utf8"); //mysql_options(mysqlInit, MYSQL_OPT_READ_TIMEOUT, (char const*)&timeout); #ifdef _WIN32 if (m_connectionInfo.host == ".") // named pipe use option (Windows) { unsigned int opt = MYSQL_PROTOCOL_PIPE; mysql_options(mysqlInit, MYSQL_OPT_PROTOCOL, (char const*)&opt); port = 0; unix_socket = 0; } else // generic case { port = atoi(m_connectionInfo.port_or_socket.c_str()); unix_socket = 0; } #else if (m_connectionInfo.host == ".") // socket use option (Unix/Linux) { unsigned int opt = MYSQL_PROTOCOL_SOCKET; mysql_options(mysqlInit, MYSQL_OPT_PROTOCOL, (char const*)&opt); m_connectionInfo.host = "localhost"; port = 0; unix_socket = m_connectionInfo.port_or_socket.c_str(); } else // generic case { port = atoi(m_connectionInfo.port_or_socket.c_str()); unix_socket = 0; } #endif m_Mysql = mysql_real_connect(mysqlInit, m_connectionInfo.host.c_str(), m_connectionInfo.user.c_str(), m_connectionInfo.password.c_str(), m_connectionInfo.database.c_str(), port, unix_socket, 0); if (m_Mysql) { if (!m_reconnecting) { TC_LOG_INFO("sql.sql", "MySQL client library: %s", mysql_get_client_info()); TC_LOG_INFO("sql.sql", "MySQL server ver: %s ", mysql_get_server_info(m_Mysql)); // MySQL version above 5.1 IS required in both client and server and there is no known issue with different versions above 5.1 // if (mysql_get_server_version(m_Mysql) != mysql_get_client_version()) // TC_LOG_INFO("sql.sql", "[WARNING] MySQL client/server version mismatch; may conflict with behaviour of prepared statements."); } TC_LOG_INFO("sql.sql", "Connected to MySQL database at %s", m_connectionInfo.host.c_str()); mysql_autocommit(m_Mysql, 1); // set connection properties to UTF8 to properly handle locales for different // server configs - core sends data in UTF8, so MySQL must expect UTF8 too mysql_set_character_set(m_Mysql, "utf8"); return PrepareStatements(); } else { TC_LOG_ERROR("sql.sql", "Could not connect to MySQL database at %s: %s\n", m_connectionInfo.host.c_str(), mysql_error(mysqlInit)); mysql_close(mysqlInit); return false; } }
bool DatabaseMysql::Initialize(const char *infoString) { if(!Database::Initialize(infoString)) return false; tranThread = NULL; MYSQL *mysqlInit; mysqlInit = mysql_init(NULL); if (!mysqlInit) { sLog.outError( "Could not initialize Mysql connection" ); return false; } InitDelayThread(); Tokens tokens = StrSplit(infoString, ";"); Tokens::iterator iter; std::string host, port_or_socket, user, password, database; int port; char const* unix_socket; iter = tokens.begin(); if(iter != tokens.end()) host = *iter++; if(iter != tokens.end()) port_or_socket = *iter++; if(iter != tokens.end()) user = *iter++; if(iter != tokens.end()) password = *iter++; if(iter != tokens.end()) database = *iter++; mysql_options(mysqlInit,MYSQL_SET_CHARSET_NAME,"utf8"); #ifdef WIN32 if(host==".") // named pipe use option (Windows) { unsigned int opt = MYSQL_PROTOCOL_PIPE; mysql_options(mysqlInit,MYSQL_OPT_PROTOCOL,(char const*)&opt); port = 0; unix_socket = 0; } else // generic case { port = atoi(port_or_socket.c_str()); unix_socket = 0; } #else if(host==".") // socket use option (Unix/Linux) { unsigned int opt = MYSQL_PROTOCOL_SOCKET; mysql_options(mysqlInit,MYSQL_OPT_PROTOCOL,(char const*)&opt); host = "localhost"; port = 0; unix_socket = port_or_socket.c_str(); } else // generic case { port = atoi(port_or_socket.c_str()); unix_socket = 0; } #endif mMysql = mysql_real_connect(mysqlInit, host.c_str(), user.c_str(), password.c_str(), database.c_str(), port, unix_socket, 0); if (mMysql) { DETAIL_LOG( "Connected to MySQL database at %s", host.c_str()); sLog.outString( "MySQL client library: %s", mysql_get_client_info()); sLog.outString( "MySQL server ver: %s ", mysql_get_server_info( mMysql)); /*----------SET AUTOCOMMIT ON---------*/ // It seems mysql 5.0.x have enabled this feature // by default. In crash case you can lose data!!! // So better to turn this off // --- // This is wrong since mangos use transactions, // autocommit is turned of during it. // Setting it to on makes atomic updates work if (!mysql_autocommit(mMysql, 1)) DETAIL_LOG("AUTOCOMMIT SUCCESSFULLY SET TO 1"); else DETAIL_LOG("AUTOCOMMIT NOT SET TO 1"); /*-------------------------------------*/ // set connection properties to UTF8 to properly handle locales for different // server configs - core sends data in UTF8, so MySQL must expect UTF8 too PExecute("SET NAMES `utf8`"); PExecute("SET CHARACTER SET `utf8`"); #if MYSQL_VERSION_ID >= 50003 my_bool my_true = (my_bool)1; if (mysql_options(mMysql, MYSQL_OPT_RECONNECT, &my_true)) { sLog.outDetail("Failed to turn on MYSQL_OPT_RECONNECT."); } else { sLog.outDetail("Successfully turned on MYSQL_OPT_RECONNECT."); } #else sLog.outDetail("Your mySQL client lib version does not support reconnecting after a timeout."); sLog.outDetail("If this causes you any trouble we advice you to upgrade"); sLog.outDetail("Your mySQL client libs to at least mySQL 5.0.13 to resolve this problem."); #endif return true; } else { sLog.outError( "Could not connect to MySQL database at %s: %s\n", host.c_str(),mysql_error(mysqlInit)); mysql_close(mysqlInit); return false; } }
int main( int argc, char *argv[] ) { zmDbgInit( "zmfix", "", -1 ); zmLoadConfig(); // Only do registered devices static char sql[BUFSIZ]; snprintf( sql, sizeof(sql), "select distinct Device from Monitors where not isnull(Device) and Type = 'Local'" ); if ( mysql_query( &dbconn, sql ) ) { Error( "Can't run query: %s", mysql_error( &dbconn ) ); exit( mysql_errno( &dbconn ) ); } MYSQL_RES *result = mysql_store_result( &dbconn ); if ( !result ) { Error( "Can't use query result: %s", mysql_error( &dbconn ) ); exit( mysql_errno( &dbconn ) ); } for( int i = 0; MYSQL_ROW dbrow = mysql_fetch_row( result ); i++ ) { fixDevice( dbrow[0] ); } if ( mysql_errno( &dbconn ) ) { Error( "Can't fetch row: %s", mysql_error( &dbconn ) ); exit( mysql_errno( &dbconn ) ); } // Yadda yadda mysql_free_result( result ); snprintf( sql, sizeof(sql), "select distinct ControlDevice from Monitors where not isnull(ControlDevice)" ); if ( mysql_query( &dbconn, sql ) ) { Error( "Can't run query: %s", mysql_error( &dbconn ) ); exit( mysql_errno( &dbconn ) ); } result = mysql_store_result( &dbconn ); if ( !result ) { Error( "Can't use query result: %s", mysql_error( &dbconn ) ); exit( mysql_errno( &dbconn ) ); } for( int i = 0; MYSQL_ROW dbrow = mysql_fetch_row( result ); i++ ) { fixDevice( dbrow[0] ); } if ( mysql_errno( &dbconn ) ) { Error( "Can't fetch row: %s", mysql_error( &dbconn ) ); exit( mysql_errno( &dbconn ) ); } // Yadda yadda mysql_free_result( result ); if ( config.opt_x10 ) { if ( config.x10_device ) { fixDevice( config.x10_device ); } } return( 0 ); }
/* {{{ _pdo_mysql_error */ int _pdo_mysql_error(pdo_dbh_t *dbh, pdo_stmt_t *stmt, const char *file, int line TSRMLS_DC) /* {{{ */ { pdo_mysql_db_handle *H = (pdo_mysql_db_handle *)dbh->driver_data; pdo_error_type *pdo_err; pdo_mysql_error_info *einfo; pdo_mysql_stmt *S = NULL; PDO_DBG_ENTER("_pdo_mysql_error"); PDO_DBG_INF_FMT("file=%s line=%d", file, line); if (stmt) { S = (pdo_mysql_stmt*)stmt->driver_data; pdo_err = &stmt->error_code; einfo = &S->einfo; } else { pdo_err = &dbh->error_code; einfo = &H->einfo; } #if HAVE_MYSQL_STMT_PREPARE || PDO_USE_MYSQLND if (S && S->stmt) { einfo->errcode = mysql_stmt_errno(S->stmt); } else #endif { einfo->errcode = mysql_errno(H->server); } einfo->file = file; einfo->line = line; if (einfo->errmsg) { pefree(einfo->errmsg, dbh->is_persistent); einfo->errmsg = NULL; } if (einfo->errcode) { if (einfo->errcode == 2014) { einfo->errmsg = pestrdup( "Cannot execute queries while other unbuffered queries are active. " "Consider using PDOStatement::fetchAll(). Alternatively, if your code " "is only ever going to run against mysql, you may enable query " "buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.", dbh->is_persistent); } else if (einfo->errcode == 2057) { einfo->errmsg = pestrdup( "A stored procedure returning result sets of different size was called. " "This is not supported by libmysql", dbh->is_persistent); } else { einfo->errmsg = pestrdup(mysql_error(H->server), dbh->is_persistent); } } else { /* no error */ strcpy(*pdo_err, PDO_ERR_NONE); PDO_DBG_RETURN(0); } #if HAVE_MYSQL_SQLSTATE || PDO_USE_MYSQLND # if HAVE_MYSQL_STMT_PREPARE || PDO_USE_MYSQLND if (S && S->stmt) { strcpy(*pdo_err, mysql_stmt_sqlstate(S->stmt)); } else # endif { strcpy(*pdo_err, mysql_sqlstate(H->server)); } #else strcpy(*pdo_err, pdo_mysql_get_sqlstate(einfo->errcode)); #endif if (!dbh->methods) { PDO_DBG_INF("Throwing exception"); zend_throw_exception_ex(php_pdo_get_exception(), einfo->errcode TSRMLS_CC, "SQLSTATE[%s] [%d] %s", *pdo_err, einfo->errcode, einfo->errmsg); } PDO_DBG_RETURN(einfo->errcode); }
/** * (re)connect to database */ int IpfixDbWriter::connectToDB() { ostringstream statement; dbError = true; // close (in the case that it was already connected) if (conn) mysql_close(conn); /** get the mysl init handle*/ conn = mysql_init(0); if(conn == 0) { msg(MSG_FATAL,"IpfixDbWriter: Get MySQL connect handle failed. Error: %s", mysql_error(conn)); return 1; } msg(MSG_DEBUG,"IpfixDbWriter: mysql init successful"); /**Connect to Database*/ if (!mysql_real_connect(conn, dbHost.c_str(), dbUser.c_str(), dbPassword.c_str(), 0, dbPort, 0, 0)) { msg(MSG_FATAL,"IpfixDbWriter: Connection to database failed. Error: %s", mysql_error(conn)); return 1; } msg(MSG_DEBUG,"IpfixDbWriter: succesfully connected to database"); /** make query string to create database**/ statement << "CREATE DATABASE IF NOT EXISTS " << dbName; DPRINTF("SQL Query: %s", statement.str().c_str()); /**create database*/ if(mysql_query(conn, statement.str().c_str()) != 0 ) { msg(MSG_FATAL, "IpfixDbWriter: Creation of database %s failed. Error: %s", dbName.c_str(), mysql_error(conn)); return 1; } msg(MSG_INFO,"IpfixDbWriter: Database %s created", dbName.c_str()); /** use database with dbName**/ if(mysql_select_db(conn, dbName.c_str()) !=0) { msg(MSG_FATAL, "IpfixDbWriter: Database %s not selectable. Error: %s", dbName.c_str(), mysql_error(conn)); return 1; } msg(MSG_DEBUG,"IpfixDbWriter: Database %s selected", dbName.c_str()); /**create table exporter*/ statement.str(""); statement.clear(); statement << "CREATE TABLE IF NOT EXISTS exporter (id SMALLINT(5) NOT NULL AUTO_INCREMENT, sourceID INTEGER(10) UNSIGNED DEFAULT NULL, srcIP INTEGER(10) UNSIGNED DEFAULT NULL, PRIMARY KEY(id))"; DPRINTF("SQL Query: %s", statement.str().c_str()); if(mysql_query(conn, statement.str().c_str()) != 0) { msg(MSG_FATAL,"IpfixDbWriter: Creation of exporter table failed. Error: %s", mysql_error(conn)); return 1; } msg(MSG_INFO,"IpfixDbWriter: Exporter table created"); dbError = false; return 0; }
static JSVAL error(JSARGS args) { HandleScope scope; const char *error = mysql_error(handle); return scope.Close(String::New(error)); }
int main(int argc, char *argv[], char *envp[]) { int i; int res; int status; const char *userp = getenv("USER"); /* vem är inloggad? */ char databas[25]="olfix"; char usr[21]; /* userid 20070214 utökat från 15 till 21 tecken */ /* for(i=0;i<=argc;i++){ fprintf(stderr,"argv[%d]=%s\n",i,argv[i]); } */ char temp1a[]="SELECT KTONR, BENAMNING FROM KTOPLAN WHERE ARID=\""; char temp1b[]=" ORDER BY KTONR"; char temp2[]="\""; char temp5[200]=""; char arid[3]=""; /* ================================================================================ */ /* Val av databas, START */ /* ================================================================================ */ status = which_database(envp); if (status != 0) exit(status); strncpy(usr,userp,sizeof(usr)); /* Den inloggades userid 20070214 */ /* fprintf(stderr,"status=%d ANTARG=%d len(database)=%d\n",status,ANTARG,strlen(database)); */ if (argc < ANTARG+1){ if (strlen(database)!= 0){ strncpy(databas,database,15); }else{ strncpy(databas,"olfixtst",15); /* olfixtst = testföretag */ } }else{ if (strlen(argv[ANTARG]) != 0){ if (strncmp(argv[ANTARG],"99",2)==0){ strncpy(databas,"olfixtst",15); }else{ strncpy(databas,argv[ANTARG],15); } } } /* fprintf(stderr,"ANTARG=%d,argv[ANTARG]=%s\n",ANTARG,argv[ANTARG]); */ /* Om usr (userid) börjar på 'test' eller 'prov' använd databas 'olfixtst' */ if (strncmp(usr,"test",4)==0 || strncmp(usr,"prov",4)==0 ) { strncpy(databas,"olfixtst",15); } /* fprintf(stderr,"Databas=%s\n",databas); */ /* ================================================================================ */ /* Val av databas, END! */ /* ================================================================================ */ if (argv[1] != NULL){ strncpy(arid,argv[1],sizeof(arid)); /* 2005-02-24 */ } else{ fprintf(stderr,"Error: KTOVIEW ARID Ange årtal!\n"); exit(-1); } strncpy(temp5,temp1a,strlen(temp1a)); /* SELECT KTONR, BENAMNING FROM KTOPLAN WHERA ARID=" */ strncat(temp5,arid,strlen(arid)); /* 2002 */ /* SELECT KTONR, BENAMNING FROM KTOPLAN WHERA ARID="2002 */ strncat(temp5,temp2,strlen(temp2)); /* " */ /* SELECT KTONR, BENAMNING FROM KTOPLAN WHERA ARID="2002" */ strncat(temp5,temp1b,strlen(temp1b)); /* SELECT KTONR, BENAMNING FROM KTOPLAN WHERA ARID="2002" ORDER BY KTONR */ // fprintf(stderr,"KTOVIEWmain: temp5 = %s\n",temp5); mysql_init(&my_connection); if (mysql_real_connect(&my_connection, "localhost", "olfix", "olfix", databas, 0, NULL, 0)){ /* fprintf(stderr,"KTOVIEW:Connection success\n"); */ res = mysql_query(&my_connection,temp5); if (res){ fprintf(stderr,"Error: KTOVIEW SELECT error: %s\n",mysql_error(&my_connection)); }else{ res_ptr=mysql_store_result(&my_connection); if (res_ptr){ i=1; /* fprintf(stderr,"KTOVIEW:Retrieved %lu rows\n",(unsigned long)mysql_num_rows(res_ptr)); */ if ( (unsigned long)mysql_num_rows(res_ptr) > 0){ fprintf(stdout,"NR_%lu_:",(unsigned long)mysql_num_rows(res_ptr)); while ((sqlrow=mysql_fetch_row(res_ptr))) { /* fprintf(stderr,"KTOVIEW:Fetched data....}"); */ display_row(); i++; } }else{ if ((unsigned long)mysql_num_rows(res_ptr) == 0){ fprintf(stdout,"VARNING: KTOVIEW. Det finns inga poster!\n"); }else{ fprintf(stderr,"Error: KTOVIEW Felaktigt årtal!\n"); } } if (mysql_errno(&my_connection)) { fprintf(stderr,"Error: KTOVIEW Retriev error: %s\n", mysql_error(&my_connection)); } } mysql_free_result(res_ptr); } mysql_close(&my_connection); } else { fprintf(stderr,"Error: KTOVIEW Connection failed\n"); if (mysql_errno(&my_connection)) { fprintf(stderr,"Error: KTOVIEW Connection error %d: %s\n", mysql_errno(&my_connection), mysql_error(&my_connection)); } } fprintf(stdout,"\n"); return EXIT_SUCCESS; }
void database::execute_1( std::string query, std::vector <std::vector <std::string>>& result_set, std::vector <std::vector<bool>> *null_fields_ptr, unsigned int& num_rows, unsigned int& num_cols, MYSQL_RES *&result) { MYSQL_ROW row; // We need to be connected to a database to continue. if (!db_connected) throw std::runtime_error("No database connection"); // Execute the SQL query. if (mysql_query(db_connection, query.c_str()) != 0) throw std::runtime_error(mysql_error(db_connection)); // If the query produced a result set, then num_cols will be non-zero. num_cols = mysql_field_count(db_connection); if (num_cols == 0) { // No result set was sent from the database server, but that is what should happen for // most non-SELECT queries that alter the database (such as INSERT, UPDATE, DELETE). // In this case, set num_rows to the number of rows that were changed (affected). num_rows = (unsigned int) mysql_affected_rows(db_connection); } else { // Get the result set from the database server. result = mysql_store_result(db_connection); if (result == NULL) throw std::runtime_error(mysql_error(db_connection)); // Get the number of rows in the result set. num_rows = (int)mysql_num_rows(result); // If there are no rows in the result set, then just return. if (num_rows == 0) return; // Resize the data arrays to hold the correct number of rows. if (null_fields_ptr != nullptr) (*null_fields_ptr).resize(num_rows); result_set.resize(num_rows); // Copy the data in the result set to where it should go. for (unsigned int i = 0; i < num_rows; i++) { // Get one row of data from the result set. row = mysql_fetch_row(result); if (row == NULL) throw std::runtime_error(mysql_error(db_connection)); // Resize the data arrays to hold the correct number of columns. if (null_fields_ptr != nullptr) (*null_fields_ptr)[i].resize(num_cols); result_set[i].resize(num_cols); // Copy the data to the final location. for (unsigned int j = 0; j < num_cols; j++) { if (row[j] == nullptr) { if (null_fields_ptr != nullptr) (*null_fields_ptr)[i][j] = true; } else { if (null_fields_ptr != nullptr) (*null_fields_ptr)[i][j] = false; result_set[i][j] = row[j]; } } } } }
int oph_odb_get_uncompleted_job_number(int parent_idjob, int *number, ophidiadb * oDB) { if (!oDB || !number) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "Null input parameter\n"); return OPH_ODB_NULL_PARAM; } *number = 0; if (oph_odb_check_connection_to_ophidiadb(oDB)) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "Unable to reconnect to OphidiaDB.\n"); return OPH_ODB_MYSQL_ERROR; } char query[MYSQL_BUFLEN]; int n = snprintf(query, MYSQL_BUFLEN, MYSQL_RETRIEVE_UNCOMPLETED_JOB_NUMBER, parent_idjob); if (n >= MYSQL_BUFLEN) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "Size of query exceed query limit.\n"); return OPH_ODB_STR_BUFF_OVERFLOW; } pmesg_safe(&global_flag, LOG_DEBUG, __FILE__, __LINE__, "Extract number of pending children using: %s.\n", query); if (mysql_query(oDB->conn, query)) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "MySQL query error: %s\n", mysql_error(oDB->conn)); return OPH_ODB_MYSQL_ERROR; } MYSQL_RES *res; MYSQL_ROW row; res = mysql_store_result(oDB->conn); if (mysql_num_rows(res) < 1) { pmesg_safe(&global_flag, LOG_DEBUG, __FILE__, __LINE__, "No row found by query\n"); mysql_free_result(res); return OPH_ODB_NO_ROW_FOUND; } if (mysql_num_rows(res) > 1) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "More than one row found by query\n"); mysql_free_result(res); return OPH_ODB_TOO_MANY_ROWS; } if (mysql_field_count(oDB->conn) != 1) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "Not enough fields found by query\n"); mysql_free_result(res); return OPH_ODB_TOO_MANY_ROWS; } if ((row = mysql_fetch_row(res)) != NULL) { if (!row[0]) { pmesg_safe(&global_flag, LOG_ERROR, __FILE__, __LINE__, "No parent job found\n"); mysql_free_result(res); return OPH_ODB_ERROR; } *number = (int) strtol(row[0], NULL, 10); } mysql_free_result(res); return OPH_ODB_SUCCESS; }
//---------------------------------------------------------------------- //!\brief On affecte chaque jour du planning avec les paramètres caractéristiques du type de jour et saison //!\return 1 si erreur, 0 sinon //---------------------------------------------------------------------- int affectation_jour_planning(void){ //pour chaque jour de l'année, défini par un l'Id=1 à l'Id=nNbJour (365) for(stJourTraite.nIdJour = 1; stJourTraite.nIdJour <= nNbJour ; stJourTraite.nIdJour++){ //récupérer le type de jour et saison recup_info_jour(stJourTraite.nIdJour); //A partir de la saison , on récupére la température de chauffe read_consigne_chauffe(stJourTraite.inf_saison); unsigned int nPrio = 1; // Maintenant, on va traiter chaque créneau de chauffe du type de jour , puis remplir la table planning en conséquence (température de consigne à mettre à jour dans les créneau concernés) // Preparation de la requete MySQL // on ne récupère que les infos du jour en cours sprintf(query, "SELECT `heure_debut`, `heure_fin`, `priorite` FROM `calendrier_type_jour` WHERE `type_jour` = '%s'", stJourTraite.inf_type_jour); // envoi de la requete printf("\n\nEnvoi de la requete : %s", query); if (mysql_query(conn, query)) { // si la requete echoue on retourne 1 fprintf(stderr, "%s\n", mysql_error(conn)); return(1); } // la requête s'est bien passée, on rend le jeu de résultats disponible via le pointeur result printf("\nRecuperation des donnees pour le type de jour"); result = mysql_store_result(conn); while ((row = mysql_fetch_row(result))) { // on stocke la taille dans le pointeur lengths = mysql_fetch_lengths(result); // on récupère l'heure de début du créneau de l'action a effectuer sprintf(stJourTraite.stheure_debut,"%.*s", (char) lengths[ROW_HEURE_DEBUT], row[ROW_HEURE_DEBUT]); // on récupère l'heure de fin du créneau de l'action a effectuer sprintf(stJourTraite.stheure_fin,"%.*s", (char) lengths[ROW_HEURE_FIN], row[ROW_HEURE_FIN]); // on récupère la priorité sprintf(stJourTraite.stheure_priorite,"%.*s", (char) lengths[ROW_PRIORITE], row[ROW_PRIORITE]); // send SQL query // Exemple , ajout condition AND addtime( `date` , `heure_fin` ) > addtime( `date` , `heure_debut` ) pour éviter que le créneau 23:30:00 - 00:00:00 soit compté (minuit étant plus petit que 23:30, on écrirait une consigne de température!) // SELECT `id` , `date` , `heure_debut` , `heure_fin` // FROM `calendrier_30min` // WHERE `date` = '2012-11-05' // AND `heure_debut` >= '18:00:00' // AND `heure_fin` <= '22:00:00' // AND addtime( `date` , `heure_fin` ) > addtime( `date` , `heure_debut` ) sprintf(query, "SELECT `id` FROM `calendrier_30min` WHERE `date` = FROM_UNIXTIME( %d, '%%Y-%%m-%%d' ) AND `heure_debut` >= '%s' AND `heure_fin` <= '%s' AND addtime( `date` , `heure_fin` ) > addtime( `date` , `heure_debut` );", stJourTraite.nUnixTime, stJourTraite.stheure_debut, stJourTraite.stheure_fin); // envoi de la requete printf("\nEnvoi de la requete : %s", query); if (mysql_query(conn, query)) { // si la requete echoue on retourne 1 fprintf(stderr, "%s\n", mysql_error(conn)); return(1); } // la requête s'est bien passée, on rend le jeu de résultats disponible via le pointeur result2 printf("\nRecuperation de l'id pour le creneau"); result2 = mysql_store_result(conn); unsigned int nIdCreneau = 0; while ((row2 = mysql_fetch_row(result2))) { // on récupère l'id du créneau horaire nIdCreneau = atoi(row2[0]); // send SQL query sprintf(query, "UPDATE `domotique`.`calendrier_30min` SET `temperature` = '%.1f', `priorite` = '%d' WHERE `calendrier_30min`.`id` = %d;", stJourTraite.fconsigne_temperature, nPrio, nIdCreneau); printf("\n\nMise à jour de la temperature pour l'id : %s", query); if (mysql_query(conn, query)) { fprintf(stderr, "%s\n", mysql_error(conn)); return(1); } } // on libère la requête mysql_free_result(result2); nPrio++; } // on libère la requête mysql_free_result(result); } //l'affectation s'est bien passé, on retourne 0 return(0); }
int CMysqlDb::Insert(DataContainerList &DataList) { if(DataList.size() == 0) return true; while (!DataList.empty()) { string ColumnsString = "("; string ValuesString = "("; DataContainer Data = DataList.front(); DataContainer::iterator LastElement = Data.end(); --LastElement; for( DataContainer::iterator ii=Data.begin(); ii!=Data.end(); ++ii) { ColumnsString += (*ii).first; ValuesString += (*ii).second; if(ii == LastElement) { ColumnsString += ")"; ValuesString += ")"; } else { ColumnsString += ","; ValuesString += ","; } } string query = "INSERT IGNORE INTO " + m_table + " " + ColumnsString + " VALUES " + ValuesString + ";"; pthread_mutex_lock(&queryMutex); /* send SQL query */ if (mysql_query(&m_conn, query.c_str())) { int error = mysql_errno(&m_conn); pthread_mutex_unlock(&queryMutex); Log.log("MySQL Error (%d): %s", error, mysql_error(&m_conn)); // Server error (fatal). Most probably because of a bug or something like that // Discard the entry so the program doesn't stop sending data because of it if(error >= 1000 && error < 2000) { Log.log("[%s]\n", query.c_str()); DataList.pop_front(); continue; } // Client error (transient). Do not delete the results and retry sending them else return false; } else pthread_mutex_unlock(&queryMutex); DataList.pop_front(); } return true; }
/** * Monitor an individual server * * @param handle The MySQL Monitor object * @param database The database to probe */ static void monitorDatabase(MONITOR *mon, MONITOR_SERVERS *database) { GALERA_MONITOR* handle = (GALERA_MONITOR*)mon->handle; MYSQL_ROW row; MYSQL_RES *result,*result2; int isjoined = 0; char *uname = mon->user; char *passwd = mon->password; unsigned long int server_version = 0; char *server_string; if (database->server->monuser != NULL) { uname = database->server->monuser; passwd = database->server->monpw; } if (uname == NULL) return; /* Don't even probe server flagged as in maintenance */ if (SERVER_IN_MAINT(database->server)) return; /** Store previous status */ database->mon_prev_status = database->server->status; if (database->con == NULL || mysql_ping(database->con) != 0) { char *dpwd = decryptPassword(passwd); int connect_timeout = mon->connect_timeout; int read_timeout = mon->read_timeout; int write_timeout = mon->write_timeout; if(database->con) mysql_close(database->con); database->con = mysql_init(NULL); mysql_options(database->con, MYSQL_OPT_CONNECT_TIMEOUT, (void *)&connect_timeout); mysql_options(database->con, MYSQL_OPT_READ_TIMEOUT, (void *)&read_timeout); mysql_options(database->con, MYSQL_OPT_WRITE_TIMEOUT, (void *)&write_timeout); if (mysql_real_connect(database->con, database->server->name, uname, dpwd, NULL, database->server->port, NULL, 0) == NULL) { free(dpwd); server_clear_status(database->server, SERVER_RUNNING); /* Also clear Joined, M/S and Stickiness bits */ server_clear_status(database->server, SERVER_JOINED); server_clear_status(database->server, SERVER_SLAVE); server_clear_status(database->server, SERVER_MASTER); server_clear_status(database->server, SERVER_MASTER_STICKINESS); if (mysql_errno(database->con) == ER_ACCESS_DENIED_ERROR) { server_set_status(database->server, SERVER_AUTH_ERROR); } database->server->node_id = -1; if (mon_status_changed(database) && mon_print_fail_status(database)) { LOGIF(LE, (skygw_log_write_flush( LOGFILE_ERROR, "Error : Monitor was unable to connect to " "server %s:%d : \"%s\"", database->server->name, database->server->port, mysql_error(database->con)))); } return; } else { server_clear_status(database->server, SERVER_AUTH_ERROR); } free(dpwd); } /* If we get this far then we have a working connection */ server_set_status(database->server, SERVER_RUNNING); /* get server version string */ server_string = (char *)mysql_get_server_info(database->con); if (server_string) { database->server->server_string = realloc(database->server->server_string, strlen(server_string)+1); if (database->server->server_string) strcpy(database->server->server_string, server_string); } /* Check if the the Galera FSM shows this node is joined to the cluster */ if (mysql_query(database->con, "SHOW STATUS LIKE 'wsrep_local_state'") == 0 && (result = mysql_store_result(database->con)) != NULL) { if(mysql_field_count(database->con) < 2) { mysql_free_result(result); skygw_log_write(LE,"Error: Unexpected result for \"SHOW STATUS LIKE 'wsrep_local_state'\". Expected 2 columns." " MySQL Version: %s",version_str); return; } while ((row = mysql_fetch_row(result))) { if (strcmp(row[1], "4") == 0) isjoined = 1; /* Check if the node is a donor and is using xtrabackup, in this case it can stay alive */ else if (strcmp(row[1], "2") == 0 && handle->availableWhenDonor == 1) { if (mysql_query(database->con, "SHOW VARIABLES LIKE 'wsrep_sst_method'") == 0 && (result2 = mysql_store_result(database->con)) != NULL) { if(mysql_field_count(database->con) < 2) { mysql_free_result(result); mysql_free_result(result2); skygw_log_write(LE,"Error: Unexpected result for \"SHOW VARIABLES LIKE 'wsrep_sst_method'\". Expected 2 columns." " MySQL Version: %s",version_str); return; } while ((row = mysql_fetch_row(result2))) { if (strncmp(row[1], "xtrabackup", 10) == 0) isjoined = 1; } mysql_free_result(result2); } } } mysql_free_result(result); } /* Check the the Galera node index in the cluster */ if (mysql_query(database->con, "SHOW STATUS LIKE 'wsrep_local_index'") == 0 && (result = mysql_store_result(database->con)) != NULL) { long local_index = -1; if(mysql_field_count(database->con) < 2) { mysql_free_result(result); skygw_log_write(LE,"Error: Unexpected result for \"SHOW STATUS LIKE 'wsrep_local_index'\". Expected 2 columns." " MySQL Version: %s",version_str); return; } while ((row = mysql_fetch_row(result))) { local_index = strtol(row[1], NULL, 10); if ((errno == ERANGE && (local_index == LONG_MAX || local_index == LONG_MIN)) || (errno != 0 && local_index == 0)) { local_index = -1; } database->server->node_id = local_index; } mysql_free_result(result); } if (isjoined) server_set_status(database->server, SERVER_JOINED); else server_clear_status(database->server, SERVER_JOINED); }
word db_query(const char * const query) { char zs[2048]; if(strlen(query) > 2000) { _log(MAJOR, "db_query() called with overlong query."); return 99; } sprintf(zs, "db_query(\"%s\")", query); _log(PROC, zs); if(db_connect()) return 9; if(mysql_query(mysql_object, query)) { sprintf(zs, "db_query(): mysql_query() Error %u: %s Query:", mysql_errno(mysql_object), mysql_error(mysql_object)); _log(CRITICAL, zs); char report[128]; strncpy(report, query, 96); report[96] = 0; if(strlen(query) > 96) { strcat(report, "..."); } sprintf(zs,"\"%s\"", report); _log(CRITICAL, zs); db_disconnect(); return 3; } return 0; }
bool kGUIDBRecord::Save(void) { unsigned int i; unsigned int e; int numchanged; bool changed; bool where; bool uselimit; kGUIString update; kGUIString field; kGUIDBRecordEntry *re; const char *errormsg; assert(m_tablename.GetLen()!=0,"Name not defined for table, call SetTableName(name)!"); /* locate using old key */ /* make sure all fields at the same as before changed */ /* if any are diffent, then return changed by other user error code, unlock table */ /* update fields that are different */ /* unlock table */ update.Alloc(8192); field.Alloc(1024); for(e=0; e<m_numentries; ++e) { re=m_entries.GetEntry(e); if(re->m_delete==true) { uselimit=true; where=true; changed=true; update.SetString("DELETE FROM "); update.Append(m_tablename.GetString()); } else if(re->m_new==true) { where=false; uselimit=false; update.SetString("INSERT "); update.Append(m_tablename.GetString()); update.Append(" SET "); changed=false; for(i=0; i<m_numfields; ++i) { if(changed==true) update.Append(", "); update.Append(GetFieldName(i)); update.Append("='"); m_db->EncodeField(&re->m_newfieldvalues[i],&field); update.Append(field.GetString()); update.Append("'"); changed=true; } } else /* update an existing record */ { update.SetString("UPDATE "); update.Append(m_tablename.GetString()); where=true; uselimit=true; update.Append(" SET "); changed=false; for(i=0; i<m_numfields; ++i) { if(strcmp(re->m_newfieldvalues[i].GetString(),re->m_fieldvalues[i].GetString())) { if(changed==true) update.Append(", "); update.Append(GetFieldName(i)); update.Append("='"); m_db->EncodeField(&re->m_newfieldvalues[i],&field); update.Append(field.GetString()); update.Append("'"); changed=true; } } } if(changed==true) { /* does this table have a unique primary key? */ if(where==true) { update.Append(" WHERE "); if(m_numprikeyfields) { unsigned int f; for(i=0; i<m_numprikeyfields; ++i) { f=m_prikey.GetEntry(i); if(i) update.Append("AND "); update.Append(GetFieldName(f)); update.Append("='"); m_db->EncodeField(&re->m_fieldvalues[f],&field); update.Append(field.GetString()); update.Append("' "); } } #if 0 if(m_unique==true) { update.Append(m_keyname.GetString()); update.Append("='"); update.Append(m_key.GetString()); update.Append("'"); } #endif else { for(i=0; i<m_numfields; ++i) { if(i) update.Append("AND "); update.Append(GetFieldName(i)); update.Append("='"); m_db->EncodeField(&re->m_fieldvalues[i],&field); update.Append(field.GetString()); update.Append("' "); } } } /* this is necessary as there could be multiple matches */ if(uselimit==true) update.Append(" LIMIT 1"); /* lock table */ LockTable(); m_db->UpdateLocks(); mysql_query(m_db->GetConn(),update.GetString()); if(m_db->GetTrace()) m_db->GetTrace()->ASprintf("->%s\n",update.GetString()); errormsg=mysql_error(m_db->GetConn()); if(errormsg[0]) assert(false,errormsg); numchanged=(int)mysql_affected_rows(m_db->GetConn()); assert(numchanged==1,"Error,number of records changed should have been one!"); /* fields were sucessfully written, copy new fields over previous ones */ /* hmmm, deleted entries? */ for(i=0; i<m_numfields; ++i) { re->m_new=false; if(strcmp(re->m_newfieldvalues[i].GetString(),re->m_fieldvalues[i].GetString())) re->m_fieldvalues[i].SetString(re->m_newfieldvalues[i].GetString()); } /* remove lock from this table */ UnLockTable(); m_db->UpdateLocks(); } } /* delete all entries that are flagged for deletion */ i=0; for(e=0; e<m_numentries; ++e) { re=m_entries.GetEntry(e-i); if(re->m_delete) { delete re; m_entries.DeleteEntry(e-i); ++i; /* ajust for scrolling entries */ } } m_numentries-=i; /* update number of entries */ return(true); /* ok! */ }
int cgiMain() { #ifdef MYSQL_DB static MYSQL *dbh; /* database connect handle */ static MYSQL_RES *result; /* database query results */ static MYSQL_ROW values; /* query data returned */ unsigned int colcount =0; /* number of returned columns */ int server_version; /* returned server version */ #endif #ifdef ORACLE_DB sqlo_db_handle_t dbh; /* database handle */ sqlo_stmt_handle_t sth1; /* statement handle 1 */ char server_version[1024]=""; /* string for returned server version */ int stat =0; /* status of sqlo calls */ int handle =0; /* handle of the interrupt handler */ const char ** values; /* values */ #endif char sqlquery_str[1024] =""; /* SQL query string */ int allrows =0; /* number of returned rows */ int rowcount =0; /* row iteration counter */ div_t oddline_calc; /* calculates even/odd row color */ char ipaddr[16] =""; /* selected IP address */ char start_date[11] =""; /* selected start date */ char start_time[6] =""; /* selected start time */ char end_date[11] =""; /* selected end date */ char end_time[6] =""; /* selected end time */ char order_by[13] =""; /* sort list by column */ char sort_order[5] =""; /* ascending or descending */ char **form_data; /* string array for query data */ char title[256] =""; /* cgi title string */ struct tm *tm_ptr; /* containing time structure */ time_t now, old; /* containing timestamp */ char err_str[2048] =""; /* use for combined error string */ int period = 0; /* the period to display */ char dataunit[255] = "0 Bytes"; /* holds the calculated KB/MB */ unsigned long long sum_bin = 0; /* summary of all bytes in */ unsigned long long sum_bout = 0; /* summary of all bytes out */ unsigned long long sum_ball = 0; /* summary of all bytes total */ char sum_buf[255] = "0"; /* summary string buffer */ _abort_flag = 0; #ifdef ORACLE_DB /* ------------------------------------------------------------------- * * ORACLE_HOME is needed for OCI8 to find tnsnames.ora * * ------------------------------------------------------------------- */ putenv(WEB_ORACLE_ENV); /* initialize the connection */ if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100)) cgi_error("Error: Failed to init libsqlora8."); /* register the interrupt handler */ sqlo_register_int_handler(&handle, sigint_handler); /* login to the database */ if (SQLO_SUCCESS != sqlo_connect(&dbh, WEB_TNS_STRING)) cgi_error("Error: Cannot connect to database."); RETURN_ON_ABORT; /* finish if SIGINT was catched */ if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version, sizeof(server_version))) cgi_error(sqlo_geterror(dbh)); RETURN_ON_ABORT; /* finish if SIGINT was catched */ /* enable autocommit, each statement is commited as a single transaction */ stat = sqlo_set_autocommit(dbh, 1); #endif #ifdef MYSQL_DB /* initialize the connection */ dbh = mysql_init(NULL); if(dbh == NULL) cgi_error("Error: Failed to init MySQL DB."); /* login to the database */ if (mysql_real_connect(dbh, MYSQLIP, EDACSADMIN, ADMIN_PASS, DB_NAME, DB_PORT, NULL, 0) == 0) cgi_error("Error: Cannot connect to database."); /* Get the database version */ server_version = mysql_get_server_version(dbh); #endif /* we load the cgi form values into form_data */ if (cgiFormEntries(&form_data) != cgiFormSuccess) cgi_error("Error: Could not retrieve form data."); if(form_data[0] == NULL) { /* ------------------------------------------------------------------- * * Start the HTML output to display the query selection * * ------------------------------------------------------------------- */ /* define the CGI title */ snprintf(title, sizeof(title), "Latest IP Address Session Activity"); pagehead(title); fprintf(cgiOut, "<div id=\"content\">\n"); fprintf(cgiOut, "<form action=\"ip-actlast.cgi\" method=\"get\">\n"); fprintf(cgiOut, "<table class=\"inner\">\n"); /* 1st row, display headers */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<th class=\"inner\" width=150>"); fprintf(cgiOut, "IP Address</th>"); fprintf(cgiOut, "<th class=\"inner\" width=150>"); fprintf(cgiOut, "Time Frame</th>"); fprintf(cgiOut, "<th class=\"inner\" width=150>"); fprintf(cgiOut, "Order By</th>"); fprintf(cgiOut, "<th class=\"inner\" width=150>"); fprintf(cgiOut, "Sort Order</th>"); fprintf(cgiOut, "</tr>\n"); /* 2nd row */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<td class=\"inner\"></td>"); fprintf(cgiOut, "<td class=\"inner\">"); fprintf(cgiOut, "<input type=radio value=\"6\" checked name=\"start\"> Last 6 Hours</td>"); fprintf(cgiOut, "<td class=\"inner\"></td>"); fprintf(cgiOut, "<td class=\"inner\"></td>"); fprintf(cgiOut, "</tr>\n"); /* 3rd row */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<td class=\"inner-ctr\">"); fprintf(cgiOut, "(192.168.111.222)</td>"); fprintf(cgiOut, "<td class=\"inner\">"); fprintf(cgiOut, "<input type=radio value=\"12\" name=\"start\"> Last 12 Hours</td>"); fprintf(cgiOut, "<td class=\"inner-ctr\"> (choose one)</td>"); fprintf(cgiOut, "<td class=\"inner\">"); fprintf(cgiOut, "<input type=radio value=\"asc\" checked name=\"sort_order\">"); fprintf(cgiOut, " Ascending</td>"); fprintf(cgiOut, "</tr>\n"); /* 4th row, request values */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<td class=\"inner-ctr\">"); fprintf(cgiOut, "<input type=text name=\"ipaddr\" size=\"15\"></td>"); fprintf(cgiOut, "<td class=\"inner\">"); fprintf(cgiOut, "<input type=radio value=\"24\" name=\"start\"> Last 24 Hours</td>"); fprintf(cgiOut, "<td class=\"inner-ctr\"><select name=\"order_by\" size=\"1\">"); fprintf(cgiOut, "<option value=\"router\">Router</option>"); fprintf(cgiOut, "<option value=\"service\">Service</option>"); fprintf(cgiOut, "<option value=\"ip_or_phone\">IP or Phone</option>"); fprintf(cgiOut, "<option selected value=\"start_date\">Start Date</option>"); fprintf(cgiOut, "<option value=\"stop_date\">Stop Date</option>"); fprintf(cgiOut, "<option value=\"elapsed_mins\">Elapsed Time</option>"); fprintf(cgiOut, "<option value=\"bytes_in\">Bytes In</option>"); fprintf(cgiOut, "<option value=\"bytes_out\">Bytes Out</option>"); fprintf(cgiOut, "<option value=\"throughput\">Throughput</option>"); fprintf(cgiOut, "</select></td>"); fprintf(cgiOut, "<td class=\"inner\">"); fprintf(cgiOut, "<input type=radio name=\"sort_order\" value=\"desc\"> Descending</td>"); fprintf(cgiOut, "</tr>\n"); /* 5th row */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<td class=\"inner\"></td>"); fprintf(cgiOut, "<td class=\"inner\">"); fprintf(cgiOut, "<input type=radio value=\"168\" name=\"start\"> Last Week</td>"); fprintf(cgiOut, "<td class=\"inner\"></td>"); fprintf(cgiOut, "<td class=\"inner\"></td>"); fprintf(cgiOut, "</tr>\n"); /* 6th and last row, close the frame */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<th class=\"inner\" colspan=4>"); fprintf(cgiOut, "<input type=submit value=\"Run Query\"></th>"); fprintf(cgiOut, "</tr>\n"); fprintf(cgiOut, "</table>\n"); fprintf(cgiOut, "</form>\n"); fprintf(cgiOut, "<h3>Additional Information</h3>\n"); fprintf(cgiOut, "<hr>\n"); fprintf(cgiOut, "<p>\n"); fprintf(cgiOut, "This query returns the list of user sessions for this IP address during the last time period."); fprintf(cgiOut, "<ul>"); fprintf(cgiOut, "<li>Type the IP address into the text field. If unsure, query the last sessions to see which IP's are given out."); fprintf(cgiOut, "<li>The time frame can be selected from the radio menu, time is counting back from now."); fprintf(cgiOut, "<li>Choosing a large time frame can result in a long query and a very large result set (thousands of rows)."); fprintf(cgiOut, "<li>The results list can be ordered using criteria from the \"Order By\" drop down list."); fprintf(cgiOut, "</ul></font>"); fprintf(cgiOut, "</p>\n"); pageside(); } /* end if for displaying the query request */ else { /* ------------------------------------------------------------------- * * check if we got all information to make the SQL query * * --------------------------------------------------------------------*/ if ( cgiFormString("ipaddr", ipaddr, sizeof(ipaddr)) != cgiFormSuccess ) cgi_error("Error retrieving the IP address."); if ( cgiFormIntegerBounded( "start", &period, 1, 2160, 6) != cgiFormSuccess ) cgi_error("Error retrieving start period information."); if ( cgiFormString("order_by", order_by, sizeof(order_by)) != cgiFormSuccess ) cgi_error("Error retrieving order_by information."); if ( cgiFormString("sort_order", sort_order, sizeof(sort_order)) != cgiFormSuccess ) cgi_error("Error retrieving sort_order information."); /* ------------------------------------------------------------------- * * The calculate query start and end time from given period in hours * * ------------------------------------------------------------------- */ now = time(NULL); tm_ptr = localtime(&now); strftime(end_date, sizeof(end_date), "%d.%m.%Y", (tm_ptr)); strftime(end_time, sizeof(end_time), "%H:%M", tm_ptr); old = time(NULL) - (period * 3600); tm_ptr = localtime(&old); strftime(start_date, sizeof(start_date), "%d.%m.%Y", tm_ptr); strftime(start_time, sizeof(start_time), "%H:%M", tm_ptr); /* ------------------------------------------------------------------- * * check we got all parts and can start doing the SQL query below * * --------------------------------------------------------------------*/ #ifdef ORACLE_DB snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT USERNAME, ROUTER, SERVICE, IP_OR_PHONE, IP_ADDR, %s, %s, ELAPSED_MINS_STR, TTY, BYTES_IN_STR, BYTES_OUT_STR, PACKETS_IN_STR, PACKETS_OUT_STR, KBS_STR FROM %s.V_EDACS WHERE IP_ADDR = '%s' AND START_DATE BETWEEN TO_DATE('%s %s', 'dd.mm.yyyy hh24:mi') and TO_DATE ('%s %s', 'dd.mm.yyyy hh24:mi') ORDER BY %s %s", "TO_CHAR(START_DATE, 'dd-mm-yyyy hh24:mi:ss')", "TO_CHAR(STOP_DATE, 'dd-mm-yyyy hh24:mi:ss')", EDACSADMIN, ipaddr, start_date, start_time, end_date, end_time, order_by, sort_order); /* initialize the statement handle */ sth1 = SQLO_STH_INIT; /* opens a cursor for the query statement */ if ( 0 > (sqlo_open2(&sth1, dbh, sqlquery_str, 0, NULL))) { if(DEBUG == 0) cgi_error(sqlo_geterror(dbh)); else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s", sqlo_geterror(dbh), sqlquery_str); cgi_error(err_str); } RETURN_ON_ABORT; /* finish if SIGINT was catched */ /* get the output column names */ //if (SQLO_SUCCESS != sqlo_ocol_names2(sth1, &colcount, &colnames)) // cgi_error("Error getting the DB columns with sqlo_ocol_names2()"); //RETURN_ON_ABORT; /* finish if SIGINT was catched */ #endif #ifdef MYSQL_DB snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT username, router, service, ip_or_phone, ip_addr, %s, %s, elapsed_mins_str, bytes_in, bytes_out, throughput FROM v_edacs WHERE ip_addr = '%s' AND start_date BETWEEN STR_TO_DATE('%s %s', '%s') and STR_TO_DATE('%s %s', '%s') ORDER BY %s %s", "DATE_FORMAT(start_date, '%d-%m-%Y %H:%i:%s')", "DATE_FORMAT(stop_date, '%d-%m-%Y %H:%i:%s')", ipaddr, start_date, start_time, "%d.%m.%Y %H:%i", end_date, end_time, "%d.%m.%Y %H:%i", order_by, sort_order); /* Prepare and execute the SQL statement */ if(mysql_query(dbh, sqlquery_str) != 0) { if(DEBUG == 0) cgi_error(mysql_error(dbh)); else snprintf(err_str, sizeof(err_str), "DB error %s\n\nQuery string %s", mysql_error(dbh), sqlquery_str); cgi_error(err_str); } /* get query results set */ result = mysql_store_result(dbh); if (result == NULL) { snprintf(err_str, sizeof(err_str), "No results for query: %s\n", sqlquery_str); cgi_error( err_str); } allrows = mysql_num_rows(result); colcount = mysql_num_fields(result); #endif /* ------------------------------------------------------------------------ * * start the html output * * -------------------------------------------------------------------------*/ snprintf(title, sizeof(title), "Latest Session Activity for IP Address %s", ipaddr); pagehead(title); fprintf(cgiOut, "<div id=\"content-wide\">\n"); fprintf(cgiOut, "<p>\n"); fprintf(cgiOut, "<b>IP Address:</b> %s <b>Timeperiod:</b> %s %s - %s %s <b>Data Records:</b> %d", ipaddr, start_date, start_time, end_date, end_time, allrows); fprintf(cgiOut, "</p>\n"); fprintf(cgiOut, "<table class=\"inner\" width=100%%>\n"); fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<th class=\"inner\">#</th>\n"); fprintf(cgiOut, "<th class=\"inner\">User</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Router</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Service</th>\n"); fprintf(cgiOut, "<th class=\"inner\">IP / Phone</th>\n"); fprintf(cgiOut, "<th class=\"inner\">IP Address</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Session Start</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Session End</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Duration</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Data In</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Data Out</th>\n"); fprintf(cgiOut, "<th class=\"inner\">Throughput</th>\n"); fprintf(cgiOut, "</tr>\n"); /* fetch the data */ #ifdef ORACLE_DB while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth1, 1)))) { /* get one record */ values = sqlo_values(sth1, NULL, 1); #endif #ifdef MYSQL_DB while((values = mysql_fetch_row(result)) != NULL) { #endif rowcount++; /* check for even/odd rows */ oddline_calc = div(rowcount, 2); if(oddline_calc.rem) fprintf(cgiOut, "<tr class=\"odd\">\n"); else fprintf(cgiOut, "<tr class=\"even\">\n"); /* calculate transer data sums */ if (values[8]) sum_bin = sum_bin + atoll(values[8]); if (values[9]) sum_bout = sum_bout + atoll(values[9]); fprintf(cgiOut, "<td>%d</td>\n", rowcount); fprintf(cgiOut, "<td>"); if (values[0]) fprintf(cgiOut, "%s</td>\n", values[0]); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[1]) fprintf(cgiOut, "%s</td>\n", values[1]); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[2]) fprintf(cgiOut, "%s</td>\n", values[2]); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[3]) fprintf(cgiOut, "%s</td>\n", values[3]); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[4]) fprintf(cgiOut, "%s</td>\n", values[4]); else fprintf(cgiOut, "none</td>"); fprintf(cgiOut, "<td>"); if (values[5]) fprintf(cgiOut, "%s</td>\n", values[5]); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[6]) fprintf(cgiOut, "%s</td>\n", values[6]); else fprintf(cgiOut, "in progress</td>"); fprintf(cgiOut, "<td>"); if (values[7]) fprintf(cgiOut, "%s</td>\n", values[7]); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[8]) fprintf(cgiOut, "%s</td>\n", calc_units(values[8], dataunit)); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[9]) fprintf(cgiOut, "%s</td>\n", calc_units(values[9], dataunit)); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "<td>"); if (values[10]) fprintf(cgiOut, "%s/s</td>\n", calc_units(values[10], dataunit)); else fprintf(cgiOut, " </td>"); fprintf(cgiOut, "</tr>\n"); } /* end while fetch row data */ #ifdef ORACLE_DB if (SQLO_SUCCESS != sqlo_close(sth1)) cgi_error("Error Closing the SQL statment handle."); RETURN_ON_ABORT; /* finish if SIGINT was catched */ #endif #ifdef MYSQL_DB mysql_close(dbh); #endif /* ----------------------------------------------------------------- * * IF there was no data for the selection, display a notification * * ----------------------------------------------------------------- */ if(rowcount == 0) { fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<td colspan=12>"); fprintf(cgiOut, "No data found for IP address %s between %s %s and %s %s.", ipaddr, start_date, start_time, end_date, end_time); fprintf(cgiOut, "</td>\n"); fprintf(cgiOut, "</tr>\n"); } /* end if rowcount is zero */ fprintf(cgiOut, "<tr>\n"); fprintf(cgiOut, "<th class=\"inner\" colspan=12>Inbound Data Total:\n"); sprintf(sum_buf, "%llu", sum_bin); fprintf(cgiOut, " %s Outbound Data Total:", calc_units(sum_buf, dataunit)); sprintf(sum_buf, "%llu", sum_bout); fprintf(cgiOut, " %s Transfered Data Total:", calc_units(sum_buf, dataunit)); sum_ball = sum_ball + sum_bin + sum_bout; sprintf(sum_buf, "%llu", sum_ball); fprintf(cgiOut, " %s</th>\n", calc_units(sum_buf, dataunit)); fprintf(cgiOut, "</tr>\n"); fprintf(cgiOut, "</table>\n"); } /* end else we were called with form data */ pagefoot(); return(0); }
/************************************************************************** Loads a user from the database. **************************************************************************/ static enum authdb_status auth_db_load(struct connection *pconn) { #ifdef HAVE_AUTH char buffer[512] = ""; const int bufsize = sizeof(buffer); int num_rows = 0; MYSQL *sock, mysql; MYSQL_RES *res; MYSQL_ROW row; char *name_buffer; int str_result; mysql_init(&mysql); /* attempt to connect to the server */ if (!(sock = mysql_real_connect(&mysql, auth_config.host.value, auth_config.user.value, auth_config.password.value, auth_config.database.value, atoi(auth_config.port.value), NULL, 0))) { freelog(LOG_ERROR, "Can't connect to server! (%s)", mysql_error(&mysql)); return AUTH_DB_ERROR; } name_buffer = alloc_escaped_string(&mysql, pconn->username); if (name_buffer != NULL) { /* select the password from the entry */ str_result = my_snprintf(buffer, bufsize, "select password from %s where name = '%s'", auth_config.table.value, name_buffer); if (str_result < 0 || str_result >= bufsize || mysql_query(sock, buffer)) { freelog(LOG_ERROR, "db_load query failed for user: %s (%s)", pconn->username, mysql_error(sock)); free_escaped_string(name_buffer); mysql_close(sock); return AUTH_DB_ERROR; } res = mysql_store_result(sock); num_rows = mysql_num_rows(res); /* if num_rows = 0, then we could find no such user */ if (num_rows < 1) { mysql_free_result(res); free_escaped_string(name_buffer); mysql_close(sock); return AUTH_DB_NOT_FOUND; } /* if there are more than one row that matches this name, it's an error * continue anyway though */ if (num_rows > 1) { freelog(LOG_ERROR, "db_load query found multiple entries (%d) for user: %s", num_rows, pconn->username); } /* if there are rows, then fetch them and use the first one */ row = mysql_fetch_row(res); mystrlcpy(pconn->server.password, row[0], sizeof(pconn->server.password)); mysql_free_result(res); /* update the access time for this user */ memset(buffer, 0, bufsize); str_result = my_snprintf(buffer, bufsize, "update %s set accesstime=unix_timestamp(), " "address='%s', logincount=logincount+1 " "where strcmp(name, '%s') = 0", auth_config.table.value, pconn->server.ipaddr, name_buffer); free_escaped_string(name_buffer); name_buffer = NULL; if (str_result < 0 || str_result >= bufsize || mysql_query(sock, buffer)) { freelog(LOG_ERROR, "db_load update accesstime failed for user: %s (%s)", pconn->username, mysql_error(sock)); } } mysql_close(sock); #endif return AUTH_DB_SUCCESS; }