Example #1
0
	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;
}
Example #3
0
/* 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;
}