PlayerInfo * db_findPlayerInfo(Connection *conn, const std::string &sql)
{
PlayerInfo *info = NULL;
Statement *stmt = conn->createStatement();
ResultSet *res = stmt->executeQuery(sql.c_str());
if (res->next())
{
info = new PlayerInfo;
info->user_id = res->getUInt("user_id");
std::string user_name = res->getString("user_name");
size_t size = user_name.size() < MAX_NAME ? user_name.size() : MAX_NAME;
memcpy(info->user_name, user_name.c_str(), size);
info->level = res->getInt("level");
info->gender = res->getInt("gender");
info->role_id = res->getInt("role_id");
info->scene_id = res->getInt("scene_id");
info->x = res->getInt("x");
info->y = res->getInt("y");
std::string user_pwd = res->getString("user_pwd");
size = user_pwd.size() < MAX_PWD ? user_pwd.size() : MAX_PWD;
memcpy(info->user_pwd, user_pwd.c_str(), size);
}
delete stmt;
delete res;
return info;
}
/**
*
* Executes a query and returns the result
*
* @param VariantVector arguments The query arguments. The first argument should
* be the query and any subsequent arguments are the bind parameters
*
* @return The results from the query
*/
QueryResult DatabaseConnection::execute(VariantVector arguments)
{
int i;
int count;
QueryResult result;
ResultSet *sqlResult;
ResultSetMetaData *sqlMetadata;
result = connect();
if (result.error.isError) {
// There was an error connecting. Return the result.
return result;
}
PreparedStatement *sqlStatement = nullptr;
try {
// Prepare query
sqlStatement = connection->prepareStatement(
arguments.front().toString());
if (arguments.size() > 1) {
// Bind arguments
int i = 1;
for (auto it = arguments.begin() + 1; it != arguments.end(); ++it) {
sqlStatement->setString(i, it->toString());
i++;
}
}
// Execute query
sqlStatement->execute();
// Fetch results
sqlResult = sqlStatement->getResultSet();
} catch (SQLException &e) {
if (sqlStatement != nullptr) {
// Free memory
delete sqlStatement;
}
result.error.isError = true;
result.error.code = e.getErrorCode();
result.error.string = e.getSQLState() + ": " + e.what();
return result;
}
sqlMetadata = sqlResult->getMetaData();
count = sqlMetadata->getColumnCount();
for (i = 1; i <= count; i++) {
// Add to collection
result.columns.push_back(sqlMetadata->getColumnName(i).asStdString());
}
// Read rows
while (sqlResult->next()) {
VariantVector row;
for (i = 1; i <= count; i++) {
Variant column;
switch (sqlMetadata->getColumnType(i)) {
default:
case ::DataType::UNKNOWN:
case ::DataType::CHAR:
case ::DataType::VARCHAR:
case ::DataType::LONGVARCHAR:
case ::DataType::BINARY:
case ::DataType::VARBINARY:
case ::DataType::LONGVARBINARY:
case ::DataType::TIMESTAMP:
case ::DataType::DATE:
case ::DataType::GEOMETRY:
case ::DataType::ENUM:
case ::DataType::SET:
case ::DataType::BIGINT:
case ::DataType::REAL:
case ::DataType::DOUBLE:
case ::DataType::DECIMAL:
// @TODO: store binary, timestamp, date, & geometry differently
// Also numeric types (need to be added to Variant class)
column = sqlResult->getString(i).asStdString();
break;
case ::DataType::SQLNULL:
column = Variant();
break;
case ::DataType::BIT:
case ::DataType::TINYINT:
case ::DataType::SMALLINT:
case ::DataType::MEDIUMINT:
case ::DataType::INTEGER:
case ::DataType::NUMERIC:
column = sqlResult->getInt(i);
break;
case ::DataType::YEAR:
column = static_cast<unsigned short>(sqlResult->getUInt(i));
break;
}
// Add column to collection
row.push_back(column);
}
// Add row to collection
result.rows.push_back(row);
}
// Free memory
delete sqlResult;
delete sqlStatement;
return result;
}
/* dosql_content_insert()
Inserts a new content item into database */
bool dosql_content_insert(list<string>& body, int user_id, string& filename,
unsigned& content_id)
{
if( !errlog || !sqlconn ) { throw -1; } /* SCREW THAT JAZZ!! */
/* extract individual strings from body */
list<string>::iterator it=body.begin();
filename = *it;
it++; /* title of content */
static PreparedStatement* pstmt_content_insert=NULL;
static PreparedStatement* pstmt_get_id=NULL;
if( !pstmt_content_insert ) {
/* has not been prepared yet--give it a shot */
try {
pstmt_content_insert = sqlconn->prepareStatement(
"insert into content (user_id,folder_id,add_date,status,title) "
"values ((?),(?),(?),'A',(?))");
pstmt_get_id = sqlconn->prepareStatement("select last_insert_id()");
}
catch( SQLException err ) {
errlog->writef("failed to generate a prepared SQL statement: what: %s, "
"code: %d, state: %s", LOG_FATAL, err.what(), err.getErrorCode(),
err.getSQLState().c_str());
throw -1;
}
}
/* format date added */
time_t t = time(NULL);
tm* timeptr = localtime(&t);
char sz[32];
memset(sz,'\0',32);
strftime(sz,32,"%Y-%m-%d %H:%M:%S",timeptr);
string datetime(sz);
/* set parameters for SQL */
try {
pstmt_content_insert->setInt(1, user_id);
pstmt_content_insert->setInt(2, 1);
pstmt_content_insert->setDateTime(3, datetime);
pstmt_content_insert->setString(4, (*it).c_str());
int ret;
if( (ret=pstmt_content_insert->executeUpdate()) != 1 ) {
errlog->writef("insert into content values (%d,%d,%s,%s) returned %d "
"when 1 was expected", LOG_WARNING, user_id, 1, datetime.c_str(),
(*it).c_str(), ret);
}
/* get ID of content just inserted */
ResultSet* rs = pstmt_get_id->executeQuery();
if( !rs->next() ) {
errlog->write("failed to get ID of inserted content",LOG_ERROR);
return false;
}
content_id = rs->getUInt(1);
}
catch( SQLException err ) {
errlog->writef("failed to execute an SQL statement to insert content: "
"what: %s, code: %d, state: %s", LOG_FATAL, err.what(),
err.getErrorCode(), err.getSQLState().c_str());
}
return true;
}
