1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
|
#include "stdafx.h"
#include "database_con.h"
////////////////////////////////////////////////////////////////////////
// Show errors from the SQLHANDLE
void database_con::show_error(unsigned int handletype, const SQLHANDLE& handle)
{
SQLWCHAR sqlstate[1024];
SQLWCHAR message[1024];
if (SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL))
wcout << "Message: " << message << "\nSQLSTATE: " << sqlstate << endl;
}
std::wstring database_con::StringToWString(const std::string& s)
{
std::wstring temp(s.length(), L' ');
std::copy(s.begin(), s.end(), temp.begin());
return temp;
}
////////////////////////////////////////////////////////////////////////
// Builds the stored procedure query.
std::wstring database_con::buildQuery(vector<std::wstring> input, string symbol)
{
std::wstringstream builder;
builder << L"EXEC sp_addHistorical " << "@Symbol='" << L"" << StringToWString(symbol) << "'," <<
"@Date='" << (wstring)L"" << input.at(0) << "'," <<
"@Open=" << (wstring)L"" << input.at(1) << "," <<
"@Close=" << (wstring)L"" << input.at(2) << "," <<
"@MaxPrice=" << (wstring)L"" << input.at(3) << "," <<
"@MinPrice=" << (wstring)L"" << input.at(4) << "," <<
"@Volume=" << (wstring)L"" << input.at(5) << ";";
return builder.str();
}
void database_con::executeQuery(wstring query) {
if (SQL_SUCCESS != SQLExecDirectW(stmt, const_cast<SQLWCHAR*>(query.c_str()), SQL_NTS)) {
std::cout << "Execute error " << std::endl;
show_error(SQL_HANDLE_STMT, stmt);
std::wcout << L"Unsuccessful Query: " << query << std::endl;
}
// Close Cursor before next iteration starts:
SQLRETURN closeCursRet = SQLFreeStmt(stmt, SQL_CLOSE);
if (!SQL_SUCCEEDED(closeCursRet))
{
show_error(SQL_HANDLE_STMT, stmt);
// maybe add some handling for the case that closing failed.
}
}
void database_con::assignThread(std::vector<vector<std::wstring>> historical, std::string symbol) {
int nThreads = 5;
std::thread threads[5];
if (historical.size() < 5) {
threads[historical.size()];
nThreads = historical.size();
}
for (int i = 0; i < nThreads; i++) {
threads[i] = std::thread(&database_con::executeQuery, this, buildQuery(historical.at(historical.size()), symbol));
historical.pop_back();
}
for (auto& th : threads) th.join();
}
////////////////////////////////////////////////////////////////////////
// Constructs a database connector object with the historical data and its symbol
database_con::database_con(std::vector<std::vector<std::wstring>> historical, string symbol){
/*
Set up the handlers
*/
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN */
SQLDriverConnectW(dbc, NULL, L"DRIVER={SQL Server};SERVER=DESKTOP-L5OT4OH\\SQLEXPRESS;DATABASE=stocks;UID=geo;PWD=geostocks;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Check for success */
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt))
{
show_error(SQL_HANDLE_DBC, dbc);
std::cout << "Failed to connect";
}
std::cout << "Building and executing the query" << std::endl;
while (!historical.empty()) {
assignThread(historical, symbol);
}
/*for (_mVecHistIter = historical.begin();
_mVecHistIter != historical.end();
_mVecHistIter++) {
assignThread(*_mVecHistIter, symbol);
//std::thread t(&database_con::executeQuery, this, buildQuery(*_mVecHistIter, symbol));
//std::thread t2(&database_con::executeQuery, this, buildQuery(historical.at(_mVecHistIter - historical.begin()+1), symbol));
std::thread t3(&database_con::executeQuery, this, buildQuery(*_mVecHistIter, symbol));
std::thread t4(&database_con::executeQuery, this, buildQuery(*_mVecHistIter, symbol));
std::thread t5(&database_con::executeQuery, this, buildQuery(*_mVecHistIter, symbol));
*/
//t.join();
//t2.join();
/*
t3.join();
t4.join();
t5.join();
*/
//executeQuery(buildQuery(*_mVecHistIter, symbol));
/*_mSymbol = symbol;
std::wstringstream stream(StringToWString(historical));
std::wstring line;
int row = 0;
while (std::getline(stream, line)) {
if (row > 0) {
vector<wstring> vHistorical = parseData(L"" + line, ',');
std::wstring SQL = buildQuery(vHistorical, _mSymbol);
if (SQL_SUCCESS != SQLExecDirectW(stmt, const_cast<SQLWCHAR*>(SQL.c_str()), SQL_NTS)) {
std::cout << "Execute error " << std::endl;
show_error(SQL_HANDLE_STMT, stmt);
std::wcout << L"Unsuccessful Query: " << SQL << std::endl;
}
// Close Cursor before next iteration starts:
SQLRETURN closeCursRet = SQLFreeStmt(stmt, SQL_CLOSE);
if (!SQL_SUCCEEDED(closeCursRet))
{
show_error(SQL_HANDLE_STMT, stmt);
// maybe add some handling for the case that closing failed.
}
}
row++;
}*/
std::cout << "Query " << _mSymbol << " ready" << std::endl;
}
database_con::~database_con() {
std::cout << "The database object has been deleted" << std::endl;
}
|
