use mysql in centos machine by c++ ，实现定时建表，取数等功能

确定centos 版本，用命令

cat /etc/centos-release

1	cat /etc/centos-release

结果是

CentOS release 6.10 (Final)

1	CentOS release 6.10 (Final)

也就是说，是 centos6

看是32位还是 64位

uname -m

uname -m

结果为

i686

i686

所以是 32位的

看 mysql 版本

mysql --version

1	mysql --version

显示

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i386) using readline 5.1

1	mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i386) using readline 5.1

所以，是 mysql 5.1.73

先写一个最简单的显示mysql版本的程序

#include <iostream>

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>


using namespace std;

MYSQL *connection, mysql;

MYSQL_RES *result;

MYSQL_ROW row;

int query_state;

int main() {

        printf("MySQL client version: %s\n", mysql_get_client_info());
        return 0;

}

#include <iostream>

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

using namespace std;

MYSQL *connection, mysql;

MYSQL_RES *result;

MYSQL_ROW row;

int query_state;

int main() {

printf("MySQL client version: %s\n", mysql_get_client_info());

return 0;

}

如何编译

mysql 安装后，会自带1个命令，有2个参数一个是：

mysql_config --cflags

1	mysql_config --cflags

它的输出结果是：

-I/usr/include/mysql  -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC   -DUNIV_LINUX -DUNIV_LINUX

1	-I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC -DUNIV_LINUX -DUNIV_LINUX

第二个是

mysql_config --libs

1	mysql_config --libs

输出：

-rdynamic -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lssl -lcrypto

1	-rdynamic -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lssl -lcrypto

所以，刚才的程序就可以这样编译：

g++ -o main $(mysql_config --cflags) main.cpp $(mysql_config --libs)

1	g++ -o main $(mysql_config --cflags) main.cpp $(mysql_config --libs)

来源： https://www.codingfriends.com/index.php/2010/02/17/mysql-connection-example/comment-page-1/

然后再做一个显示数据库所有表的程序，来源同上

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
 
// just going to input the general details and not the port numbers
struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};
 
MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}
 
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}
 
int main()
{
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;
  mysqlD.server = "localhost";  // where the mysql database is
  mysqlD.user = "mysqlusername";		// the root user of mysql	
  mysqlD.password = "mysqlpassword"; // the password of the root user in mysql
  mysqlD.database = "mysql";	// the databse to pick
 
  // connect to the mysql database
  conn = mysql_connection_setup(mysqlD);
 
  // assign the results return to the MYSQL_RES pointer
  res = mysql_perform_query(conn, "show tables");
 
  printf("MySQL Tables in mysql database:\n");
  while ((row = mysql_fetch_row(res)) !=NULL)
      printf("%s\n", row[0]);
 
  /* clean up the database result set */
  mysql_free_result(res);
  /* clean up the database link */
  mysql_close(conn);
 
  return 0;
}

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

// just going to input the general details and not the port numbers

struct connection_details

{

char *server;

char *user;

char *password;

char *database;

};

MYSQL* mysql_connection_setup(struct connection_details mysql_details)

{

// first of all create a mysql instance and initialize the variables within

MYSQL *connection = mysql_init(NULL);

// connect to the database with the details attached.

if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {

printf("Conection error : %s\n", mysql_error(connection));

exit(1);

}

return connection;

}

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)

{

// send the query to the database

if (mysql_query(connection, sql_query))

{

printf("MySQL query error : %s\n", mysql_error(connection));

exit(1);

}

return mysql_use_result(connection);

}

int main()

{

MYSQL *conn; // the connection

MYSQL_RES *res; // the results

MYSQL_ROW row; // the results row (line by line)

struct connection_details mysqlD;

mysqlD.server = "localhost"; // where the mysql database is

mysqlD.user = "mysqlusername"; // the root user of mysql

mysqlD.password = "mysqlpassword"; // the password of the root user in mysql

mysqlD.database = "mysql"; // the databse to pick

// connect to the mysql database

conn = mysql_connection_setup(mysqlD);

// assign the results return to the MYSQL_RES pointer

res = mysql_perform_query(conn, "show tables");

printf("MySQL Tables in mysql database:\n");

while ((row = mysql_fetch_row(res)) !=NULL)

printf("%s\n", row[0]);

/* clean up the database result set */

mysql_free_result(res);

/* clean up the database link */

mysql_close(conn);

return 0;

}

编译方法与之前的一样，如果把这句

res = mysql_perform_query(conn, "show tables");

1	res = mysql_perform_query(conn, "show tables");

换为：

res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

1	res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

即可实现建表

以上只是 Mysql相关，如果与 ctp 合并，参见：

http://www.notesoflyang.com/?p=2152

编译方法将改为

g++ -g -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs) /home/cplusExer/useMysql/thostmduserapi.so

1	g++ -g -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs) /home/cplusExer/useMysql/thostmduserapi.so

以下的程序实现了当把日期作为参数传进去之后（20190805）

能自动建表并插数

#include "ThostFtdcMdApi.h"
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h> 
#include <iostream>
#include <string.h>
#include <sstream>

char dateParam[30];   // global param of date


// just going to input the general details and not the port numbers
struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};
 
MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}
 
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}


class CMduserHandler : public CThostFtdcMdSpi
{
private:
  CThostFtdcMdApi *m_mdApi;
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;

public:
  CMduserHandler()
  {

	  struct connection_details mysqlD;
  	mysqlD.server = "localhost";  // where the mysql database is
  	mysqlD.user = "root";		// the root user of mysql	
 	 mysqlD.password = "Piercing77#"; // the password of the root user in mysql
 	 mysqlD.database = "shfuture";	// the databse to pick
 

	  // connect to the mysql database
  	conn = mysql_connection_setup(mysqlD);

  }

 ~CMduserHandler()
 {
   mysql_free_result(res);
   /* clean up the database link */
   mysql_close(conn);

  }
  
  void connect()
  {

//创建并初始化API

      m_mdApi = CThostFtdcMdApi::CreateFtdcMdApi("", true, true);

      m_mdApi->RegisterSpi(this);

     m_mdApi->RegisterFront("tcp://218.28.130.102:41413");    //real
     //m_mdApi->RegisterFront("tcp://210.5.151.249:51205");    // Pan Hou

      m_mdApi->Init();
  }


//登陆

  void login()

  {

      CThostFtdcReqUserLoginField t = {0};

      while (m_mdApi->ReqUserLogin(&t, 1)!=0) sleep(1);

  }


// 订阅行情

  void subscribe()

  {

      char **ppInstrument=new char * [50];

      ppInstrument[0] = "rb1910";
      //ppInstrument[1] = "jm1901";

	 // ppInstrument[1] = "IF1812";

	  ppInstrument[1] = "IF1909";

	  ppInstrument[2] = "IC1909";

	  ppInstrument[3] = "ni1909";

      while (m_mdApi->SubscribeMarketData(ppInstrument, 4)!=0) sleep(1);
  }


  //接收行情

  void OnRtnDepthMarketData(CThostFtdcDepthMarketDataField *pDepthMarketData)
  {
		  //printf("%d,%d,%d",(int)pDepthMarketData->LastPrice,(int)pDepthMarketData->BidPrice1,(int)pDepthMarketData->BidVolume1);
    //      printf("\n");
		  //printf("%d,%d",(int)pDepthMarketData->AskPrice1,(int)pDepthMarketData->AskVolume1);
    //      printf("\n");

		  //printf("%s %s",(char*)pDepthMarketData->TradingDay,(char*)pDepthMarketData->UpdateTime);



 // MYSQL *conn;		// the connection
 // MYSQL_RES *res;	// the results
 // MYSQL_ROW row;	// the results row (line by line)
 
  //struct connection_details mysqlD;
  //mysqlD.server = "localhost";  // where the mysql database is
  //mysqlD.user = "root";		// the root user of mysql	
  //mysqlD.password = "Piercing77#"; // the password of the root user in mysql
  //mysqlD.database = "shfuture";	// the databse to pick
 

  // connect to the mysql database
  //conn = mysql_connection_setup(mysqlD);
		

	     if((pDepthMarketData->BidVolume1>1000000)||(pDepthMarketData->BidVolume1<-1000000))
			 return;

	     if((pDepthMarketData->BidPrice1>1000000)||(pDepthMarketData->BidPrice1<-1000000))
			 return;

		  std::string str((char*)pDepthMarketData->ActionDay);
		  std::string strtime((char*)pDepthMarketData->UpdateTime);
		  std::string strDayTime = str.substr(0,4) + '-' + str.substr(4,2) + '-' +  str.substr(6,2) + ' ' + strtime ;


		  //combine update char*    example : "INSERT INTO rb1 VALUES('1950-11-12 00:00:00', 100,200,300,400)"
		  char updatestring[256];
		  memset(updatestring, '\0', sizeof(updatestring));


		  if(strcmp (pDepthMarketData->InstrumentID,"rb1910")==0)
		  {
		     strcpy(updatestring,"INSERT INTO rb1910_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 
			 
		  }
		  else if(strcmp (pDepthMarketData->InstrumentID,"IF1909")==0) 
		  {
			 strcpy(updatestring,"INSERT INTO if1909_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 

		  }
		  else if(strcmp (pDepthMarketData->InstrumentID,"IC1909")==0)
		  {
			 strcpy(updatestring,"INSERT INTO ic1909_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 

		  }
		  else if(strcmp (pDepthMarketData->InstrumentID,"ni1909")==0)
		  {
			 strcpy(updatestring,"INSERT INTO ni1909_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 

		  }

		  else
		  {
			  //strcpy(updatestring,"INSERT INTO if20181221 VALUES('");
		  }
		  
		  strncat(updatestring, strDayTime.c_str(), sizeof(updatestring));
		  strncat(updatestring, "',", sizeof(updatestring));


		  std::ostringstream ostr;
		  ostr << pDepthMarketData->LastPrice;
		  std::string sLastPrice = ostr.str();
		  strncat(updatestring, sLastPrice.c_str(), sizeof(updatestring));
		  
		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr1;
		  ostr1 << pDepthMarketData->BidPrice1;
		  std::string sBidPrice1 = ostr1.str();
		  strncat(updatestring, sBidPrice1.c_str(), sizeof(updatestring));	  
		  

		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr2;
		  ostr2 << (int)pDepthMarketData->BidVolume1;
		  std::string sBidVolume1 = ostr2.str();
		  strncat(updatestring, sBidVolume1.c_str(), sizeof(updatestring));	  


		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr3;
		  ostr3 << pDepthMarketData->AskPrice1;
		  std::string sAskPrice1 = ostr3.str();
		  strncat(updatestring, sAskPrice1.c_str(), sizeof(updatestring));	  


		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr4;
		  ostr4 << (int)pDepthMarketData->AskVolume1;
		  std::string sAskVolume1 = ostr4.str();
		  strncat(updatestring, sAskVolume1.c_str(), sizeof(updatestring));	  

		 		  
		  strncat(updatestring, ")", sizeof(updatestring));
		  
		  std::cout<<updatestring<<std::endl;

		  res = mysql_perform_query(conn,updatestring);
		  mysql_free_result(res);
		  /* clean up the database link */
 		 //mysql_close(conn);

		  
		 return;
  }

}; 
int main(int argc, char* argv[])
{
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;
  mysqlD.server = "localhost";  // where the mysql database is
  mysqlD.user = "root";		// the root user of mysql	
  mysqlD.password = "Piercing77#"; // the password of the root user in mysql
  mysqlD.database = "shfuture";	// the databse to pick
 
   int i;
   printf("%d\n",argc);
   for(i=0;i<=argc-1;i++)
   {
        printf("%s\n",argv[i]);
   }

  memset(dateParam, '\0', sizeof(dateParam));
  strcpy(dateParam,argv[1]);


  // connect to the mysql database
  conn = mysql_connection_setup(mysqlD);
 
  // assign the results return to the MYSQL_RES pointer
 
  // res = mysql_perform_query(conn, "show tables");

 // res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

  char CreateTableString[256];
   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists rb1910_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);


//
   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists if1909_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);


   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists ni1909_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);

//
   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists ic1909_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);



 
 // printf("MySQL Tables in mysql database:\n");
// while ((row = mysql_fetch_row(res)) !=NULL)
	 //printf("%s\n", row[0]);
 
  /* clean up the database result set */
  mysql_free_result(res);
  /* clean up the database link */
  mysql_close(conn);
  
  
   CMduserHandler *mduser = new CMduserHandler;

  mduser->connect();

  mduser->login();

  mduser->subscribe();

 // while(1)
  //{}

  sleep(1000);
 

  return 0;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

#include "ThostFtdcMdApi.h"

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

#include <iostream>

#include <string.h>

#include <sstream>

char dateParam[30]; // global param of date

// just going to input the general details and not the port numbers

struct connection_details

{

char *server;

char *user;

char *password;

char *database;

};

MYSQL* mysql_connection_setup(struct connection_details mysql_details)

{

// first of all create a mysql instance and initialize the variables within

MYSQL *connection = mysql_init(NULL);

// connect to the database with the details attached.

if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {

printf("Conection error : %s\n", mysql_error(connection));

exit(1);

}

return connection;

}

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)

{

// send the query to the database

if (mysql_query(connection, sql_query))

{

printf("MySQL query error : %s\n", mysql_error(connection));

exit(1);

}

return mysql_use_result(connection);

}

class CMduserHandler : public CThostFtdcMdSpi

{

private:

CThostFtdcMdApi *m_mdApi;

MYSQL *conn; // the connection

MYSQL_RES *res; // the results

MYSQL_ROW row; // the results row (line by line)

struct connection_details mysqlD;

public:

CMduserHandler()

{

struct connection_details mysqlD;

mysqlD.server = "localhost"; // where the mysql database is

mysqlD.user = "root"; // the root user of mysql

mysqlD.password = "Piercing77#"; // the password of the root user in mysql

mysqlD.database = "shfuture"; // the databse to pick

// connect to the mysql database

conn = mysql_connection_setup(mysqlD);

}

~CMduserHandler()

{

mysql_free_result(res);

/* clean up the database link */

mysql_close(conn);

}

void connect()

{

//创建并初始化API

m_mdApi = CThostFtdcMdApi::CreateFtdcMdApi("", true, true);

m_mdApi->RegisterSpi(this);

m_mdApi->RegisterFront("tcp://218.28.130.102:41413"); //real

//m_mdApi->RegisterFront("tcp://210.5.151.249:51205"); // Pan Hou

m_mdApi->Init();

}

//登陆

void login()

{

CThostFtdcReqUserLoginField t = {0};

while (m_mdApi->ReqUserLogin(&t, 1)!=0) sleep(1);

}

// 订阅行情

void subscribe()

{

char **ppInstrument=new char * [50];

ppInstrument[0] = "rb1910";

//ppInstrument[1] = "jm1901";

// ppInstrument[1] = "IF1812";

ppInstrument[1] = "IF1909";

ppInstrument[2] = "IC1909";

ppInstrument[3] = "ni1909";

while (m_mdApi->SubscribeMarketData(ppInstrument, 4)!=0) sleep(1);

}

//接收行情

void OnRtnDepthMarketData(CThostFtdcDepthMarketDataField *pDepthMarketData)

{

//printf("%d,%d,%d",(int)pDepthMarketData->LastPrice,(int)pDepthMarketData->BidPrice1,(int)pDepthMarketData->BidVolume1);

// printf("\n");

//printf("%d,%d",(int)pDepthMarketData->AskPrice1,(int)pDepthMarketData->AskVolume1);

// printf("\n");

//printf("%s %s",(char*)pDepthMarketData->TradingDay,(char*)pDepthMarketData->UpdateTime);

// MYSQL *conn; // the connection

// MYSQL_RES *res; // the results

// MYSQL_ROW row; // the results row (line by line)

//struct connection_details mysqlD;

//mysqlD.server = "localhost"; // where the mysql database is

//mysqlD.user = "root"; // the root user of mysql

//mysqlD.password = "Piercing77#"; // the password of the root user in mysql

//mysqlD.database = "shfuture"; // the databse to pick

// connect to the mysql database

//conn = mysql_connection_setup(mysqlD);

if((pDepthMarketData->BidVolume1>1000000)||(pDepthMarketData->BidVolume1<-1000000))

return;

if((pDepthMarketData->BidPrice1>1000000)||(pDepthMarketData->BidPrice1<-1000000))

return;

std::string str((char*)pDepthMarketData->ActionDay);

std::string strtime((char*)pDepthMarketData->UpdateTime);

std::string strDayTime = str.substr(0,4) + '-' + str.substr(4,2) + '-' + str.substr(6,2) + ' ' + strtime ;

//combine update char* example : "INSERT INTO rb1 VALUES('1950-11-12 00:00:00', 100,200,300,400)"

char updatestring[256];

memset(updatestring, '\0', sizeof(updatestring));

if(strcmp (pDepthMarketData->InstrumentID,"rb1910")==0)

{

strcpy(updatestring,"INSERT INTO rb1910_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else if(strcmp (pDepthMarketData->InstrumentID,"IF1909")==0)

{

strcpy(updatestring,"INSERT INTO if1909_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else if(strcmp (pDepthMarketData->InstrumentID,"IC1909")==0)

{

strcpy(updatestring,"INSERT INTO ic1909_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else if(strcmp (pDepthMarketData->InstrumentID,"ni1909")==0)

{

strcpy(updatestring,"INSERT INTO ni1909_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else

{

//strcpy(updatestring,"INSERT INTO if20181221 VALUES('");

}

strncat(updatestring, strDayTime.c_str(), sizeof(updatestring));

strncat(updatestring, "',", sizeof(updatestring));

std::ostringstream ostr;

ostr << pDepthMarketData->LastPrice;

std::string sLastPrice = ostr.str();

strncat(updatestring, sLastPrice.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr1;

ostr1 << pDepthMarketData->BidPrice1;

std::string sBidPrice1 = ostr1.str();

strncat(updatestring, sBidPrice1.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr2;

ostr2 << (int)pDepthMarketData->BidVolume1;

std::string sBidVolume1 = ostr2.str();

strncat(updatestring, sBidVolume1.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr3;

ostr3 << pDepthMarketData->AskPrice1;

std::string sAskPrice1 = ostr3.str();

strncat(updatestring, sAskPrice1.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr4;

ostr4 << (int)pDepthMarketData->AskVolume1;

std::string sAskVolume1 = ostr4.str();

strncat(updatestring, sAskVolume1.c_str(), sizeof(updatestring));

strncat(updatestring, ")", sizeof(updatestring));

std::cout<<updatestring<<std::endl;

res = mysql_perform_query(conn,updatestring);

mysql_free_result(res);

/* clean up the database link */

//mysql_close(conn);

return;

}

};

int main(int argc, char* argv[])

{

MYSQL *conn; // the connection

MYSQL_RES *res; // the results

MYSQL_ROW row; // the results row (line by line)

struct connection_details mysqlD;

mysqlD.server = "localhost"; // where the mysql database is

mysqlD.user = "root"; // the root user of mysql

mysqlD.password = "Piercing77#"; // the password of the root user in mysql

mysqlD.database = "shfuture"; // the databse to pick

int i;

printf("%d\n",argc);

for(i=0;i<=argc-1;i++)

{

printf("%s\n",argv[i]);

}

memset(dateParam, '\0', sizeof(dateParam));

strcpy(dateParam,argv[1]);

// connect to the mysql database

conn = mysql_connection_setup(mysqlD);

// assign the results return to the MYSQL_RES pointer

// res = mysql_perform_query(conn, "show tables");

// res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

char CreateTableString[256];

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists rb1910_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists if1909_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists ni1909_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists ic1909_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

// printf("MySQL Tables in mysql database:\n");

// while ((row = mysql_fetch_row(res)) !=NULL)

//printf("%s\n", row[0]);

/* clean up the database result set */

mysql_free_result(res);

/* clean up the database link */

mysql_close(conn);

CMduserHandler *mduser = new CMduserHandler;

mduser->connect();

mduser->login();

mduser->subscribe();

// while(1)

//{}

sleep(1000);

return 0;

}

centos 7 用 sleep 函数加 #include <unistd.h>

此程序的问题是 cpu 占用过高，在centos 执行：

ps -aux

ps -aux

发现占用率是百分100 ，

因为原来的写法是：

while(1) {}

改为 sleep(大整数)即可

看进程的方法来自：

https://www.cyberciti.biz/faq/how-to-check-running-process-in-linux-using-command-line/

此外，还存在的问题是内存泄漏，随着程序的运行，内存会很快耗尽 (经过观察，不存在这个问题，是程序一开始执行内存占用急增，过一阵就会恢复正常 )

检查内存泄漏的工具是 valgrind

安装方法：

wget https://sourceware.org/pub/valgrind/valgrind-3.15.0.tar.bz2  //download valgrind
tar xvjf valgrind-3.15.0.tar.bz2                            //now extract
cd valgrind-3.15.0                                          //move to the corresponding dir
./configure                                                //prepare everything
make
make install                                               //now install

wget https://sourceware.org/pub/valgrind/valgrind-3.15.0.tar.bz2 //download valgrind

tar xvjf valgrind-3.15.0.tar.bz2 //now extract

cd valgrind-3.15.0 //move to the corresponding dir

./configure //prepare everything

make

make install //now install

参拷： https://stackoverflow.com/questions/22495744/how-to-install-valgrind-3-9-0-in-centos-5-5

安装后：

valgrind --tool=memcheck --leak-check=yes ./ConnAndShowTable 20190806

1	valgrind --tool=memcheck --leak-check=yes ./ConnAndShowTable 20190806

执行

发现程序执行一段时间后会自己结束

是因为sleep过短的原因，改为 sleep(26000) , 约7个小时即可

如何定时执行此程序并定时关闭

~~a) 因为程序运行在美国虚拟机，首先要知道当前的中国时间~~

<del>/* gmtime example */
#include <stdio.h>      /* puts, printf */
#include <time.h>       /* time_t, struct tm, time, gmtime */

#define MST (-7)
#define UTC (0)
#define CCT (+8)

int main ()
{
  time_t rawtime;
  struct tm * ptm;

  time ( &rawtime );

  ptm = gmtime ( &rawtime );

  puts ("Current time around the World:");
  printf ("Phoenix, AZ (U.S.) :  %2d:%02d\n", (ptm->tm_hour+MST)%24, ptm->tm_min);
  printf ("Reykjavik (Iceland) : %2d:%02d\n", (ptm->tm_hour+UTC)%24, ptm->tm_min);
  printf ("Beijing (China) :     %2d:%02d\n", (ptm->tm_hour+CCT)%24, ptm->tm_min);

  return 0;
}</del>

<del>/* gmtime example */

#include <stdio.h> /* puts, printf */

#include <time.h> /* time_t, struct tm, time, gmtime */

#define MST (-7)

#define UTC (0)

#define CCT (+8)

int main ()

{

time_t rawtime;

struct tm * ptm;

time ( &rawtime );

ptm = gmtime ( &rawtime );

puts ("Current time around the World:");

printf ("Phoenix, AZ (U.S.) : %2d:%02d\n", (ptm->tm_hour+MST)%24, ptm->tm_min);

printf ("Reykjavik (Iceland) : %2d:%02d\n", (ptm->tm_hour+UTC)%24, ptm->tm_min);

printf ("Beijing (China) : %2d:%02d\n", (ptm->tm_hour+CCT)%24, ptm->tm_min);

return 0;

}</del>

~~来源 : http://www.cplusplus.com/reference/ctime/gmtime/~~

~~编译方法：~~

<del>g++ -o showChinaTime showChinaTime.cpp</del>

1	<del>g++ -o showChinaTime showChinaTime.cpp</del>

b) 要有一个能实现cron trigger 的库，要能在 centos 运行的非.net版本

一开始想用这个： https://stackoverflow.com/questions/3618499/c-c-api-to-decode-cron-style-timings

是c++程序可以调用的lib ，但是下载已不可用，是 2013年的

或者可以用centos系统自带的crontab , 定时执行 c++ 程序

https://blog.csdn.net/capecape/article/details/78515558

思路是：先写一个脚本判断时间，该执行还是该关闭程序

然后用 crontab 每小时执行脚本

如何在 bash 脚本判断时间： https://unix.stackexchange.com/questions/395933/how-to-check-if-the-current-time-is-between-2300-and-0630

自动启动的脚本是这样的：

#!/bin/bash

#printf "Memory information\n"
#free -m
#printf "Disk information\n"
#df -h

echo "Today is $(date)"
echo "In UTC format Today is $(date -u)"
printf "Now will judge time\n"


#change date from edt to china date
dateStr=""
case $(date -d " + 12 hour" +'%H:%M') in    # us east time add 12 hour is china time
    (8:*)        echo morningOpen;;
    (15:*)       echo morningClose;;
    (20:*)        echo nightOpen;
                  echo doOpen;;
    (17:*)        echo nightClose;
                 dateStr=$(date -d " + 24 hour"  '+%Y%m%d');
                echo $dateStr;
                #exec /home/cplusExer/useMysql/ConnAndShowTable  $dateStr;;
                nohup /home/cplusExer/useMysql/ConnAndShowTable  $dateStr > /dev/null &;;
esac

#!/bin/bash

#printf "Memory information\n"

#free -m

#printf "Disk information\n"

#df -h

echo "Today is $(date)"

echo "In UTC format Today is $(date -u)"

printf "Now will judge time\n"

#change date from edt to china date

dateStr=""

case $(date -d " + 12 hour" +'%H:%M') in # us east time add 12 hour is china time

(8:*) echo morningOpen;;

(15:*) echo morningClose;;

(20:*) echo nightOpen;

echo doOpen;;

(17:*) echo nightClose;

dateStr=$(date -d " + 24 hour" '+%Y%m%d');

echo $dateStr;

#exec /home/cplusExer/useMysql/ConnAndShowTable $dateStr;;

nohup /home/cplusExer/useMysql/ConnAndShowTable $dateStr > /dev/null &;;

esac

需要注意的是这句： dateStr=$(date -d ” + 24 hour” ‘+%Y%m%d’);

等号2测不能有空格，来源：

https://stackoverflow.com/questions/9556517/variable-assignment-inside-a-case-statement-bash

此外，用 nohup 来后台执行

让脚本可执行的方法是：

chmod +x bashTest02.sh

1	chmod +x bashTest02.sh

如何自动停止

#!/bin/bash

#echo "Today is $(date)"

#pidof ConnAndShowTable>/dev/null && echo "Service is running" || echo "Service NOT running"

pid=0
pid=$(pidof ConnAndShowTable)
#echo $pid
if [ -z "$pid" ]
then
      echo "\$var is empty"
      echo $pid
else
      kill -9 $pid
fi

#!/bin/bash

#echo "Today is $(date)"

#pidof ConnAndShowTable>/dev/null && echo "Service is running" || echo "Service NOT running"

pid=0

pid=$(pidof ConnAndShowTable)

#echo $pid

if [ -z "$pid" ]

then

echo "\$var is empty"

echo $pid

else

kill -9 $pid

所以，最终的脚本是这样的：

#!/bin/bash

#printf "Memory information\n"
#free -m
#printf "Disk information\n"
#df -h

echo "Today is $(date)"
echo "In UTC format Today is $(date -u)"
printf "Now will judge time\n"
#echo $(date -d " + 12 hour" +'%H:%M')

#change date from edt to china date
dateStr=""
case $(date -d " + 12 hour" +'%H:%M') in    # us east time add 12 hour is china time
    (02:*)        echo nightClose;
                 pid=0;
                 pid=$(pidof ConnAndShowTable);
                 if [[ -z "$pid" ]];
                 then
                    echo $pid;
                 else
                    kill -9 $pid;
                 fi;;
    (16:*)       echo morningClose;;
    (20:*)       echo nightOpen;
                 dateStr=$(date -d " + 24 hour"  '+%Y%m%d');
                 nohup /home/cplusExer/useMysql/ConnAndShowTable  $dateStr > /dev/null &;;
    (08:*)        echo morningOpen;
                 dateStr=$(date -d " + 24 hour"  '+%Y%m%d');
                echo $dateStr;
                #exec /home/cplusExer/useMysql/ConnAndShowTable  $dateStr;;
                nohup /home/cplusExer/useMysql/ConnAndShowTable  $dateStr > /dev/null &;;
esac

#!/bin/bash

#printf "Memory information\n"

#free -m

#printf "Disk information\n"

#df -h

echo "Today is $(date)"

echo "In UTC format Today is $(date -u)"

printf "Now will judge time\n"

#echo $(date -d " + 12 hour" +'%H:%M')

#change date from edt to china date

dateStr=""

case $(date -d " + 12 hour" +'%H:%M') in # us east time add 12 hour is china time

(02:*) echo nightClose;

pid=0;

pid=$(pidof ConnAndShowTable);

if [[ -z "$pid" ]];

then

echo $pid;

else

kill -9 $pid;

fi;;

(16:*) echo morningClose;;

(20:*) echo nightOpen;

dateStr=$(date -d " + 24 hour" '+%Y%m%d');

nohup /home/cplusExer/useMysql/ConnAndShowTable $dateStr > /dev/null &;;

(08:*) echo morningOpen;

dateStr=$(date -d " + 24 hour" '+%Y%m%d');

echo $dateStr;

#exec /home/cplusExer/useMysql/ConnAndShowTable $dateStr;;

nohup /home/cplusExer/useMysql/ConnAndShowTable $dateStr > /dev/null &;;

esac

centos crontab

如何查看现有定时任务，新建任务，见：

https://tecadmin.net/crontab-in-linux-with-20-examples-of-cron-schedule/

我加了一个这样的任务：

* * * * *  /home/cplusExer/useMysql/bashScripts/bashTest02.sh

1	* * * * * /home/cplusExer/useMysql/bashScripts/bashTest02.sh

每分钟执行此脚本，但是，执行没有，或者执行的结果在哪里看？

答案是 ; https://stackoverflow.com/questions/28856326/crontab-simple-echo-not-running

如果要每小时，就

50 * * * *  /home/cplusExer/useMysql/bashScripts/bashTest.sh

1	50 * * * * /home/cplusExer/useMysql/bashScripts/bashTest.sh

调试用到的命令行

./ConnAndShowTable "20190802"

1	./ConnAndShowTable "20190802"

g++ -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs)

1	g++ -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs)

date -d " + 24 hour"  '+%Y%m%d'

1	date -d " + 24 hour" '+%Y%m%d'

sudo nano ConnAndShowTable.cpp

1	sudo nano ConnAndShowTable.cpp

待解决的问题：

a) 找一个读 config 文件的库

b) 自动建表时避开周末及休市日，这个要用到config

参考文档

https://sandeepghai.wordpress.com/2011/08/07/linking-of-mysql-database-with-c-on-linux-machine/

Linking of MYSQL Database with C++ on Linux Machine

https://stackoverflow.com/questions/8896963/connecting-to-mysql-through-c

关于 how to link

http://zetcode.com/db/mysqlc/

代码编译不通过，原因未知