bool Database::Initialize(const char* infoString, int nConns /*= 1*/)
{
// Enable logging of SQL commands (usually only GM commands)
// (See method: PExecuteLog)
m_logSQL = sConfig.GetBoolDefault("LogSQL", false);
m_logsDir = sConfig.GetStringDefault("LogsDir","");
if (!m_logsDir.empty())
{
if ((m_logsDir.at(m_logsDir.length()-1)!='/') && (m_logsDir.at(m_logsDir.length()-1)!='\\'))
m_logsDir.append("/");
}
m_pingIntervallms = sConfig.GetIntDefault("MaxPingTime", 30) * (MINUTE * 1000);
//create DB connections
//setup connection pool size
if (nConns < MIN_CONNECTION_POOL_SIZE)
m_nQueryConnPoolSize = MIN_CONNECTION_POOL_SIZE;
else if (nConns > MAX_CONNECTION_POOL_SIZE)
m_nQueryConnPoolSize = MAX_CONNECTION_POOL_SIZE;
else
m_nQueryConnPoolSize = nConns;
//create connection pool for sync requests
for (int i = 0; i < m_nQueryConnPoolSize; ++i)
{
SqlConnection* pConn = CreateConnection();
if (!pConn->Initialize(infoString))
{
delete pConn;
return false;
}
m_pQueryConnections.push_back(pConn);
}
//create and initialize connection for async requests
m_pAsyncConn = CreateConnection();
if (!m_pAsyncConn->Initialize(infoString))
return false;
m_pResultQueue = new SqlResultQueue;
InitDelayThread();
return true;
}
void test_insert(SqlConnection& connection)
{
for (int i = 0; i < 1; i++)
{
std::string inser_sql(
"INSERT INTO favorite.test_table(user_name) VALUES (?)");
std::auto_ptr<SqlPreparedStatement> stmt(
connection.preparedStatement(inser_sql));
std::ifstream in("test/main.cpp");
stmt->setBlob(1, in);
stmt->executeUpdate();
std::cout << "Insert Id: " << connection.getInsertId() << std::endl;
}
}
Strings
OracleDialect::get_tables(SqlConnection &conn)
{
Strings table;
auto_ptr<SqlCursor> cursor = conn.new_cursor();
String query = _T("SELECT table_name FROM all_tables where owner = (SELECT USER FROM DUAL)");
cursor->prepare(query);
Values params;
SqlResultSet rs = cursor->exec(params);
for (SqlResultSet::iterator i = rs.begin(); i != rs.end(); ++i)
{
table.push_back((*i)[0].second.as_string());
}
return table;
}
static void setup_log(SqlConnection &c)
{
init_log();
c.init_logger(root_logger.get());
c.set_echo(true);
}
int main(int argc, char** argv)
{
SqlConnection con;
bool bok = con.ConnectToServer("192.168.1.192", 3306, "webgame", "letmego", "mysql");
if (!bok)
{
return 0;
}
SqlBaseCmd basecmd;
basecmd.Init(con.get_mysql_handle());
//printf("%s", con.get_mysql_handle()->db);
basecmd.ExecuteSqlCmd("use mysql");
basecmd.ExecuteSqlCmd("show tables");
SqlUseResults useresult;
SqlStoreResults storeresult;
useresult.Init(basecmd.GetExecuteResult());
std::string results;
int nrow = 0;
char sztemp[32] = { 0 };
useresult._FirstRow();
while (!useresult._IsEnd())
{
results.clear();
for (int i = 0; i < useresult.GetCol(); i ++)
{
memset(sztemp, 0, 32);
useresult.GetString(useresult._GetCurRow(), i, sztemp, 32);
results += sztemp;
results += '\t';
}
printf("%s\n", results.c_str());
useresult._MoveNextRow();
}
useresult.Uninit();
//basecmd.ExecuteSqlCmd("show databases");
printf("%%%%%%%%%%%%%%%%\n");
storeresult.Init(mysql_list_processes(con.get_mysql_handle()));
for (int i = 0; i < storeresult.GetRow(); i ++)
{
results.clear();
for (int j = 0; j < storeresult.GetCol(); j ++)
{
memset(sztemp, 0, 32);
storeresult.GetString(i, j, sztemp, 32);
results += sztemp;
results += '\t';
}
printf("%s\n", results.c_str());
}
storeresult.Uninit();
con.DisConnectServer();
#if 0
lihj::CSql_Helper sqlhelper;
lihj::Sql_Paramer sqlparamer;
sqlparamer.host = "127.0.0.1";
sqlparamer.dbname = "testlihj";
sqlparamer.passwd = "letmego";
sqlparamer.port = 3306;
sqlparamer.user = "******";
#endif
//sqlhelper.Startup(sqlparamer);
#if 0
printf("input sql \n");
char szsql[256] = {0};
while (1)
{
std::cin.clear();
std::cin.ignore(256, '\n');
std::cin.getline(szsql,512);
if (strlen(szsql) > 0)
{
szsql[strlen(szsql)] = '\0';
sqlhelper.PushBack(szsql);
}
}
#endif
#if 0
lihj::CMemPool mempools;
mempools.init();
char* psz1 = (char*)mempools.alloc(1000);
char* psz2 = (char*)mempools.alloc(1000);
char* psz3 = (char*)mempools.alloc(1000);
char* psz4 = (char*)mempools.alloc(1000);
char* psz5 = (char*)mempools.alloc(1000);
mempools.free(psz1);
mempools.free(psz3);
mempools.free(psz2);
mempools.free(psz5);
char* psz6 = (char*)mempools.alloc(23);
char* psz7 = (char*)mempools.alloc(23);
char* psz8 = (char*)mempools.alloc(23);
char* psz9 = (char*)mempools.alloc(23);
char* psz10 = (char*)mempools.alloc(23);
// mempools.free(psz1);
#endif
system("pause");
return 0;
}
ColumnsInfo
OracleDialect::get_columns(SqlConnection &conn, const String &table)
{
ColumnsInfo ci;
auto_ptr<SqlCursor> cursor = conn.new_cursor();
String query = _T("SELECT col.column_name, col.data_type, col.data_length, col.nullable, col.data_default FROM ALL_TAB_COLUMNS col WHERE col.TABLE_NAME = '") +table
+ _T("' AND col.OWNER = (SELECT USER FROM DUAL)");
cursor->prepare(query);
Values params;
SqlResultSet rs = cursor->exec(params);
for (SqlResultSet::iterator i = rs.begin(); i != rs.end(); ++i)
{
ColumnInfo x;
for (Row::const_iterator j = i->begin(); j != i->end(); ++j)
{
if (_T("COLUMN_NAME") == j->first)
{
x.name = str_to_upper(j->second.as_string());
}
else if (_T("DATA_TYPE") == j->first)
{
x.type = str_to_upper(j->second.as_string());
if (_T("VARCHAR2") == x.type)
{
++j;
if (_T("DATA_LENGTH") == j->first)
{
x.size = j->second.as_integer();
}
}
}
else if (_T("NULLABLE") == j->first)
{
x.notnull = _T("N") == j->second.as_string();
}
else if (_T("DATA_DEFAULT") == j->first)
{
if (!j->second.is_null())
x.default_value = j->second.as_string();
}
}
ci.push_back(x);
}
String querypk = _T("SELECT cols.position FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '") +table+ _T("' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner");
cursor->prepare(querypk);
Values paramspk;
SqlResultSet rspk = cursor->exec(paramspk);
for (SqlResultSet::iterator i = rspk.begin(); i != rspk.end(); ++i)
{
Row::const_iterator j = i->begin();
if (j != i-> end())
{
ci[j->second.as_integer() - 1].pk = true;
}
}
String q2 =_T("SELECT substr(c_src.COLUMN_NAME, 1, 20) as SRC_COLUMN, c_dest.TABLE_NAME as DEST_TABLE, substr(c_dest.COLUMN_NAME, 1, 20) as DEST_COLUMN FROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME AND c_list.CONSTRAINT_TYPE = 'R' AND c_src.TABLE_NAME = '")
+ table + _T("'");
cursor->prepare(q2);
Values params2;
SqlResultSet rs2 = cursor->exec(params2);
for (SqlResultSet::iterator i = rs2.begin(); i != rs2.end(); ++i)
{
String fk_column, fk_table, fk_table_key;
for (Row::const_iterator j = i->begin(); j != i->end(); ++j)
{
if (_T("DEST_TABLE") == j->first)
{
if (!j->second.is_null())
{
fk_table = j->second.as_string();
}
}
else if (_T("DEST_COLUMN") == j->first)
{
if (!j->second.is_null())
{
fk_table_key = j->second.as_string();
}
}
else if (_T("SRC_COLUMN") == j->first)
{
if (!j->second.is_null())
{
fk_column = j->second.as_string();
}
}
}
for (ColumnsInfo::iterator k = ci.begin(); k != ci.end(); ++k)
{
if (k->name == fk_column)
{
k->fk_table = fk_table;
k->fk_table_key = fk_table_key;
break;
}
}
}
return ci;
}
SqlConnection *Engine::get_from_pool()
{
if (!pool_.get())
throw PoolError(_T("Engine with no connection"));
SqlConnection *conn = pool_->get(source_id_, timeout_);
if (!conn)
throw PoolError(_T("Can't get connection"));
dialect_ = conn->get_dialect();
conn->set_echo(echo_);
conn->init_logger(logger_.get());
return conn;
}
