void MySQLConnection::connect()
{
bool reconnecting = false;
if (_myConn != nullptr) //reconnection attempt
{
if (!mysql_ping(_myConn)) //ping ok
return;
else
reconnecting = true;
}
//remove any state from previous session
this->clear();
Poco::Logger& logger = _dbEngine->getLogger();
for(;;)
{
const char* unix_socket = nullptr;
if (_unix_socket.length() > 0)
unix_socket = _unix_socket.c_str();
_myConn = mysql_real_connect(_myHandle, _host.c_str(), _user.c_str(), _password.c_str(), _database.c_str(), _port, unix_socket, CLIENT_REMEMBER_OPTIONS);
if (!_myConn)
{
const char* actionToDo = "connect";
if (reconnecting)
actionToDo = "reconnect";
unsigned int errNo = mysql_errno(_myHandle);
if (IsConnectionErrFatal(errNo))
throw SqlException(errNo,mysql_error(_myHandle),actionToDo);
static const long sleepTime = 1000;
logger.warning(Poco::format("Could not %s to MySQL database at %s: %s, retrying in %d seconds",
string(actionToDo),_host,string(mysql_error(_myHandle)),static_cast<int>(sleepTime/1000)));
Poco::Thread::sleep(sleepTime);
continue;
}
break;
}
string actionDone = (reconnecting)?string("Reconnected"):string("Connected");
poco_information(logger,Poco::format( actionDone + " to MySQL database %s:%d/%s client ver: %s server ver: %s",
_host, _port,_database,string(mysql_get_client_info()),string(mysql_get_server_info(_myConn)) ));
//Autocommit should be ON because without it, MySQL would require everything to be wrapped into a transaction
if (!mysql_autocommit(_myConn, 1))
poco_trace(logger,"Set autocommit to true");
else
poco_error(logger,"Failed to set autocommit to true");
//set connection properties to UTF8 to properly handle locales for different server configs
//core sends data in UTF8, so MySQL must expect UTF8 too
if (!mysql_set_character_set(_myConn,"utf8"))
poco_trace(logger,Poco::format("Character set changed to %s",string(mysql_character_set_name(_myConn))));
else
poco_error(logger,Poco::format("Failed to change charset, remains at %s",string(mysql_character_set_name(_myConn))));
}
static int wsql_connection_set_charset(wsql_connection *self, PyObject *arg, void* closure)
{
int error = 0;
const char* charset = PyString_AsString(arg);
if (!charset)
return -1;
CHECK_CONNECTION(self, -1);
Py_BEGIN_ALLOW_THREADS
if (strcmp(mysql_character_set_name(&(self->connection)), charset) != 0) {
#if MYSQL_VERSION_ID >= 40100
error = mysql_set_character_set(&(self->connection), charset);
#else
char query[256];
snprintf(query, 256, "SET NAMES %s", charset);
error = mysql_query(&(self->connection), query);
#endif
}
Py_END_ALLOW_THREADS
if (error)
{
wsql_raise_error(self);
return -1;
}
return 0;
}
BOOL initDB(void)
{
char string[256];
LogWrite(LT_SYSTEM, "Init: DB: Initialize MYSQL.\n");
gpMysql = NULL;
if (!mysql_init(&mysql))
{
sprintf(string, "MYSQL initialize Error.");
LogWrite(LT_SYSTEM, string);
return FALSE;
}
gpMysql = mysql_real_connect(&mysql, ConfigData.mysqlHostName, ConfigData.mysqlUserName, ConfigData.mysqlPassword, ConfigData.mysqlDatabase, 0, NULL, 0);
printf("Connect %s,%s,%s,%s\n", ConfigData.mysqlHostName, ConfigData.mysqlUserName, ConfigData.mysqlPassword, ConfigData.mysqlDatabase);
if (!gpMysql)
{
sprintf(string, "Mysql Connect Error (%s).\n", mysql_error(&mysql));
LogWrite(LT_SYSTEM, string);
return FALSE;
}
if (!mysql_set_character_set(&mysql, "utf8"))
{
sprintf(string, "Mysql set character to (%s).\n", mysql_character_set_name(&mysql));
LogWrite(LT_SYSTEM, string);
}
sprintf(string, "Init: DB: DB connected successfully!\n");
printf(string);
LogWrite(LT_SYSTEM, string);
return TRUE;
}
bool CDataBaseThread::InitDataBase( const char* pIP,unsigned pPort , const char* pUserName,const char* pPassword, const char* pDBName )
{
// connect to data base ;
// init my_sql ;
m_pMySql = mysql_init(NULL);
char bReconnect = 1 ;
mysql_options(m_pMySql,MYSQL_OPT_RECONNECT,&bReconnect);
if ( !mysql_real_connect(m_pMySql,pIP,pUserName,pPassword,pDBName,pPort,NULL,CLIENT_MULTI_STATEMENTS) )
{
fprintf(stderr, "Failed to connect to database: Error: %s\\n", mysql_error(m_pMySql));
m_bRunning = false ;
mysql_close(m_pMySql) ;
m_pMySql = NULL ;
return false ;
}
if (!mysql_set_character_set(m_pMySql, "utf8"))
{
printf("New client character set: %s\n",
mysql_character_set_name(m_pMySql));
}
m_bRunning = true ;
//mysql_set_server_option( m_pMySql, MYSQL_OPTION_MULTI_STATEMENTS_ON ); s
m_tNextMysqlPingTime = time(NULL) + MYSQL_PING_TIME;
return true ;
}
static void set_session_options(void)
/*
* session variables can be set once for the whole session
*/
{
/* Anton Dobkin <*****@*****.**>, VIAN, Ltd. */
#if MYSQL_VERSION_ID >= 41000
const char *character_set=read_env("MYSQL_CHARACTER_SET"), *check;
if(character_set){
/*
* This function works like the SET NAMES statement, but also sets
* the value of mysql->charset, and thus affects the character set
* used by mysql_real_escape_string()
*
* (return value apparently work the opposite of what is documented)
*/
mysql_set_character_set(mysql, character_set);
check = mysql_character_set_name(mysql);
if (strcmp(character_set, check) != 0)
{
err("Cannot set MySQL character set \"%s\", working with \"%s\"\n",
character_set, check);
}
else
{
DPRINTF("Install of a character set for MySQL: %s", character_set);
}
}
#endif /* 41000 */
}
static QTextCodec* codec(MYSQL* mysql)
{
#if MYSQL_VERSION_ID >= 32321
QTextCodec* heuristicCodec = QTextCodec::codecForName(mysql_character_set_name(mysql));
if (heuristicCodec)
return heuristicCodec;
#endif
return QTextCodec::codecForLocale();
}
int main(int argc, char *argv[])
{
MYSQL *mysql;
MYSQL_RES *result;
mysql= mysql_init(NULL);
mysql_real_connect(mysql, "localhost", "example", "example_pw",
"example_db", 0, NULL, 0);
printf("Current character set: %s\n",
mysql_character_set_name(mysql));
mysql_set_character_set(mysql, "latin2");
printf("New character set: %s\n",
mysql_character_set_name(mysql));
mysql_close(mysql);
return 0;
}
static PyObject* wsql_connection_get_charset(wsql_connection *self, void* closure)
{
const char *s;
CHECK_CONNECTION(self, NULL);
#if MYSQL_VERSION_ID >= 32321
s = mysql_character_set_name(&(self->connection));
#else
s = "latin1";
#endif
return PyString_FromString(s);
}
int test_client_character_set(MYSQL *mysql)
{
MY_CHARSET_INFO cs;
char *csname= (char*) "utf8";
char *csdefault= (char*)mysql_character_set_name(mysql);
FAIL_IF(mysql_set_character_set(mysql, csname), mysql_error(mysql));
mysql_get_character_set_info(mysql, &cs);
FAIL_IF(strcmp(cs.csname, "utf8") || strcmp(cs.name, "utf8_general_ci"), "Character set != UTF8");
FAIL_IF(mysql_set_character_set(mysql, csdefault), mysql_error(mysql));
return OK;
}
j_result_t CMySQLAccess::Connect(const j_char_t *pAddr, j_int16_t nPort, const j_char_t *pUa, const j_char_t *pPwd)
{
//连接数据库
if (!mysql_real_connect(m_mysql, pAddr, pUa, pPwd, "veh_data", 0, NULL, CLIENT_LOCAL_FILES))
{
J_OS::LOGINFO("Failed to connect to database: Error: %s", mysql_error(m_mysql));
return J_DB_ERROR;
}
mysql_set_character_set(m_mysql, "gbk");
J_OS::LOGINFO("character name: %s", mysql_character_set_name(m_mysql));
m_bConnected = true;
return J_OK;
}
static VALUE cConnection_character_set(VALUE self) {
VALUE connection_container = rb_iv_get(self, "@connection");
MYSQL *db;
const char *encoding;
if (Qnil == connection_container)
return Qfalse;
db = DATA_PTR(connection_container);
encoding = mysql_character_set_name(db);
return RUBY_STRING(encoding);
}
/**
escape : 'connection -> string -> string
<doc>Escape the string for inserting into a SQL request</doc>
**/
static value escape( value o, value s ) {
int len;
value sout;
val_check_kind(o,k_connection);
val_check(s,string);
len = val_strlen(s) * 2;
sout = alloc_empty_string(len);
len = mysql_real_escape_string(CNX(o)->m,val_string(sout),val_string(s),val_strlen(s));
if( len < 0 ) {
buffer b = alloc_buffer("Unsupported charset : ");
buffer_append(b,mysql_character_set_name(CNX(o)->m));
bfailure(b);
}
val_set_length(sout,len);
val_string(sout)[len] = 0;
return sout;
}
static int bug30472_retrieve_charset_info(MYSQL *con,
char *character_set_name,
char *character_set_client,
char *character_set_results,
char *collation_connection)
{
MYSQL_RES *rs;
MYSQL_ROW row;
int rc;
/* Get the cached client character set name. */
strcpy(character_set_name, mysql_character_set_name(con));
/* Retrieve server character set information. */
rc= mysql_query(con, "SHOW VARIABLES LIKE 'character_set_client'");
check_mysql_rc(rc, con);
rs= mysql_store_result(con);
FAIL_IF(!rs, "Invalid result set");
row= mysql_fetch_row(rs);
FAIL_IF(!row, "Couldn't fetch row");
strcpy(character_set_client, row[1]);
diag("cs: %s", row[1]);
mysql_free_result(rs);
rc= mysql_query(con, "SHOW VARIABLES LIKE 'character_set_results'");
check_mysql_rc(rc, con);
rs= mysql_store_result(con);
FAIL_IF(!rs, "Invalid result set");
row= mysql_fetch_row(rs);
FAIL_IF(!row, "Couldn't fetch row");
strcpy(character_set_results, row[1]);
mysql_free_result(rs);
rc= mysql_query(con, "SHOW VARIABLES LIKE 'collation_connection'");
check_mysql_rc(rc, con);
rs= mysql_store_result(con);
FAIL_IF(!rs, "Invalid result set");
row= mysql_fetch_row(rs);
FAIL_IF(!row, "Couldn't fetch row");
strcpy(collation_connection, row[1]);
mysql_free_result(rs);
return OK;
}
/***********************************************************************************************************
* 程序创建：刘进朝 程序修改:赵进军
* 函数功能：往TransMessage数据表里面新增一条数据 还差一个设备是否在线？
* 参数说明：
* lUserID：用户ID
* pTitle：消息标题
* pContext：消息内容
* vecHost：设备ID
* 注意事项：null
* 修改日期：2015/10/12 14:37:00
***********************************************************************************************************/
j_result_t CMySQLAccess::AddContextInfo(long lUserID, const char *pTitle, const char *pContext, std::vector<j_string_t> &vecHost)
{
if (!m_bConnected)
return J_DB_ERROR;
try
{
char strCmd[256] = { 0 };
char timeInterval[arr_size] = { 0 };
CXlHelper::GetTimestamp(true, timeInterval); // 获取系统时间戳
J_OS::LOGINFO("CMySQLAccess::AddContextInfo character name: %s", mysql_character_set_name(m_mysql));
sprintf(strCmd, "INSERT INTO TransMessage(UserID,Title,Content,SendTime)VALUES(%d,'%s','%s','%s');",
lUserID, pTitle, pContext, timeInterval);
int ret = mysql_real_query(m_mysql, strCmd, strlen(strCmd));
if (ret == 0)
{
int nMessageID = mysql_insert_id(m_mysql); //获取当前插入数据后的MessageID值
if (nMessageID >= 0)
{
std::vector<j_string_t>::iterator it = vecHost.begin();
for (; it != vecHost.end(); it++)
{
memset(strCmd, 0, sizeof(strCmd));
sprintf(strCmd, // 阅读时间和接收时间默认一样，状态默认车载端已经接收
"INSERT INTO TransMessageDetail(MessageID,EquipmentID,State,ReceTime,ReadTime)VALUES(%d,'%s','%d','%s','%s');",
nMessageID, it->c_str(), 0, &timeInterval, &timeInterval);
ret = mysql_real_query(m_mysql, strCmd, strlen(strCmd));
}
}
else
{
return J_DB_ERROR;
}
}
}
catch (...)
{
return J_DB_ERROR;
}
return J_OK;
}
void CDataBaseThread::__run()
{
while ( true )
{
if ( ProcessRequest() )
break ;
if ( !m_bRunning )
{
ProcessRequest();
break;
}
Sleep(1);
if ( time(NULL) >= m_tNextMysqlPingTime )
{
unsigned long id = mysql_thread_id(m_pMySql);
mysql_ping(m_pMySql);
if ( id != mysql_thread_id(m_pMySql) )
{
// reconnected ;
if (!mysql_set_character_set(m_pMySql, "utf8"))
{
printf("Reconnect !!! New client character set: %s\n",
mysql_character_set_name(m_pMySql));
}
}
m_tNextMysqlPingTime = time(NULL) + MYSQL_PING_TIME;
}
}
if ( m_pMySql )
{
mysql_close(m_pMySql) ;
}
}
Variant f_mysql_client_encoding(CVarRef link_identifier /* = uninit_null() */) {
MYSQL *conn = MySQL::GetConn(link_identifier);
if (!conn) return false;
return String(mysql_character_set_name(conn), CopyString);
}
static Variant HHVM_FUNCTION(mysql_client_encoding,
const Variant& link_identifier /* = uninit_null() */) {
MYSQL *conn = MySQL::GetConn(link_identifier);
if (!conn) return false;
return String(mysql_character_set_name(conn), CopyString);
}
string CDatabase_Connection::character_set_name()
{
return mysql_character_set_name(&my);
}
int main(int argc, char** argv)
{
DB_CFG cfg;
char szSql[BUFSIZ] = "";
char szFileName[BUFSIZ] = "";
char szHeader[BUFSIZ] = "";
memset( &cfg, 0, sizeof(DB_CFG) );
if ( rd_conf(&cfg) )
{
printf("rd_conf error\n");
return -1;
}
MYSQL *pDbm = mysql_init(NULL);
if (NULL == pDbm)
{
printf("mysql_init error\n");
return -1;
}
pDbm = mysql_real_connect(pDbm, cfg.szHostIP, cfg.szUser, cfg.szPasswd,
cfg.szDatabase, cfg.unPort, NULL, 0);
if (NULL == pDbm)
{
printf("mysql_real_connect error\n");
return -1;
}
// Create cvs file.
time_t time_now = time(0);
struct tm* ptm_now = localtime( &time_now );
sprintf(szFileName, "《问鼎》统计%02d月%02d日.csv", ptm_now->tm_mon + 1, ptm_now->tm_mday);
FILE* fp = fopen(szFileName, "w");
if (NULL == fp)
{
printf("open file error\n");
return -1;
}
sprintf(szSql, "set names 'latin1'");
if ( mysql_query(pDbm, szSql) )
{
printf("mysql_query error\n");
return -1;
}
printf("New client character set: %s\n", mysql_character_set_name(pDbm));
sprintf(szHeader, "激活帐号数,总角色数\n");
sprintf(szSql,
"select count(distinct(account)), count(*) from hddb.user \
where (account between 'test001001' and 'test005000') \
or (account between 'test002001' and 'test002434') \
or (account between 'test003703' and 'test003811')");
if ( mysql2csv(fp, szHeader, szSql, pDbm) )
{
printf("data query or write error\n");
return -1;
}
sprintf(szHeader, "\n\n新增角色数\n");
sprintf(szSql,
"select count(*) from hddb.user where create_time \
between (curdate()-1)*1000000 and curdate()*1000000");
if ( mysql2csv(fp, szHeader, szSql, pDbm) )
{
printf("data query or write error\n");
return -1;
}
sprintf(szHeader, "\n\n登录角色数,登录帐号数\n");
sprintf(szSql,
"select count(*), count(distinct(account)) from hddb.user where \
last_login_time between (curdate()-1)*1000000 and curdate()*1000000");
if ( mysql2csv(fp, szHeader, szSql, pDbm) )
{
printf("data query or write error\n");
return -1;
}
sprintf(szHeader, "\n\n等级段分布\n人数,等级段\n");
sprintf(szSql,
"select count(*), 5*floor(level/5) as lv_stage from hddb.user \
where (account between 'test001001' and 'test005000') \
or (account between 'test002001' and 'test002434') \
or (account between 'test003703' and 'test003811') \
group by lv_stage order by lv_stage desc");
if ( mysql2csv(fp, szHeader, szSql, pDbm) )
{
printf("data query or write error\n");
return -1;
}
sprintf(szHeader, "\n\n等级排名（前50）\n角色,帐号,等级\n");
sprintf(szSql,
"select name, account, level from hddb.user \
where (account between 'test001001' and 'test005000') \
or (account between 'test002001' and 'test002434') \
or (account between 'test003703' and 'test003811') \
order by level desc limit 50");
if ( mysql2csv(fp, szHeader, szSql, pDbm) )
{
printf("data query or write error\n");
return -1;
}
sprintf(szHeader, "\n\n金钱排名（前50）\n角色,帐号,金钱\n");
sprintf(szSql,
"select name, account, money from hddb.user \
where (account between 'test001001' and 'test005000') \
or (account between 'test002001' and 'test002434') \
or (account between 'test003703' and 'test003811') \
order by money desc limit 50;");
if ( mysql2csv(fp, szHeader, szSql, pDbm) )
{
printf("data query or write error\n");
return -1;
}
fclose(fp);
fp = NULL;
mysql_close(pDbm);
//getchar();
return 0;
}
static JSVAL character_set_name(JSARGS args) {
HandleScope scope;
MYSQL *handle = (MYSQL *) args[0]->IntegerValue();
return scope.Close(String::New(mysql_character_set_name(handle)));
}
/* character_set_name() */
static VALUE character_set_name(VALUE obj)
{
return rb_tainted_str_new2(mysql_character_set_name(GetHandler(obj)));
}
/*主要功能:得到 当前连接的默认字符集*/
const char * CppMySQL3DB::getCharacterSetName()
{
return mysql_character_set_name(_db_ptr);
}
int MysqlDatabase::connect(bool create_new) {
if (host.empty() || db.empty())
return DB_CONNECTION_NONE;
//CLog::Log(LOGDEBUG, "Connecting to mysql:%s:%s", host.c_str(), db.c_str());
try
{
disconnect();
if (conn == NULL) {
conn = mysql_init(conn);
mysql_ssl_set(
conn,
key.empty() ? NULL : key.c_str(),
cert.empty() ? NULL : cert.c_str(),
ca.empty() ? NULL : ca.c_str(),
capath.empty() ? NULL : capath.c_str(),
ciphers.empty() ? NULL : ciphers.c_str());
}
if (!CWakeOnAccess::GetInstance().WakeUpHost(host, "MySQL : " + db))
return DB_CONNECTION_NONE;
// establish connection with just user credentials
if (mysql_real_connect(conn, host.c_str(),
login.c_str(),
passwd.c_str(),
NULL,
atoi(port.c_str()),
NULL,
compression ? CLIENT_COMPRESS : 0) != NULL)
{
// disable mysql autocommit since we handle it
//mysql_autocommit(conn, false);
// enforce utf8 charset usage
default_charset = mysql_character_set_name(conn);
if(mysql_set_character_set(conn, "utf8")) // returns 0 on success
{
CLog::Log(LOGERROR, "Unable to set utf8 charset: %s [%d](%s)",
db.c_str(), mysql_errno(conn), mysql_error(conn));
}
// check existence
if (exists())
{
// nothing to see here
}
else if (create_new)
{
char sqlcmd[512];
int ret;
sprintf(sqlcmd, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", db.c_str());
if ( (ret=query_with_reconnect(sqlcmd)) != MYSQL_OK )
{
throw DbErrors("Can't create new database: '%s' (%d)", db.c_str(), ret);
}
}
if (mysql_select_db(conn, db.c_str()) == 0)
{
active = true;
return DB_CONNECTION_OK;
}
}
// if we failed above, either credentials were incorrect or the database didn't exist
if (mysql_errno(conn) == ER_BAD_DB_ERROR && create_new)
{
if (create() == MYSQL_OK)
{
active = true;
return DB_CONNECTION_OK;
}
}
CLog::Log(LOGERROR, "Unable to open database: %s [%d](%s)",
db.c_str(), mysql_errno(conn), mysql_error(conn));
return DB_CONNECTION_NONE;
}
catch(...)
{
CLog::Log(LOGERROR, "Unable to open database: %s (%u)",
db.c_str(), GetLastError());
}
return DB_CONNECTION_NONE;
}
