datausermanual.page

很好用的网络封装库,不熟悉网络编程的人也可以使用。使用风格良好的标准c++编写。

    std::vector<std::string> names;
    ses << "SELECT NAME FROM FORENAME", into(names), limit(50), now;
----

The above example will retrieve up to 50 rows from the database (note that returning nothing is valid!) and <*append*> 
it to the names collection, i.e. the collection is not cleared!
If one wants to make sure that <!exactly!> 50 rows are returned one must set the 2nd limit parameter 
(which per default is set to  <*false*>) to <*true*>:

    std::vector<std::string> names;
    ses << "SELECT NAME FROM FORENAME", into(names), limit(50, true), now;
----

Iterating over a complete result collection is done via the Statement object until statement.done() returns true.
For the next example, we assume that our system knows about 101 forenames:

    std::vector<std::string> names;
    Statement stmt = (ses << "SELECT NAME FROM FORENAME", into(names), limit(50)); 
    stmt.execute(); //names.size() == 50
    poco_assert (!stmt.done());
    stmt.execute(); //names.size() == 100
    poco_assert (!stmt.done());
    stmt.execute(); //names.size() == 101
    poco_assert (stmt.done()); 
----

We previously stated that if no data is returned this is valid too. Thus, executing the following statement on an
empty database table will work:

    std::string aName;
    ses << "SELECT NAME FROM FORENAME", into(aName), now;
----

To guarantee that at least one valid result row is returned use the <!lowerLimit!> clause:

    std::string aName;
    ses << "SELECT NAME FROM FORENAME", into(aName), lowerLimit(1), now;
----
If the table is now empty, an exception will be thrown. If the query succeeds, aName is guaranteed to be initialized.
Note that <!limit!> is only the short name for <!upperLimit!>. To iterate over a result set step-by-step, e.g. one wants to avoid 
using a collection class, one would write:

    std::string aName;
    Statement stmt = (ses << "SELECT NAME FROM FORENAME", into(aName), lowerLimit(1), upperLimit(1));
    while (!stmt.done())
        stmt.execute();
----

And for the lazy ones, there is the <!range!> command:

    std::string aName;
    Statement stmt = (ses << "SELECT NAME FROM FORENAME", into(aName), range(1,1));
    while (!stmt.done())
        stmt.execute();
----
The third parameter to range is an optional boolean value which specifies if the upper limit is a hard limit, ie. 
if the amount of rows returned by the query must match exactly. Per default exact matching is off.

!!!Complex Data Type Mapping
All the previous examples were contented to work with only the most basic data types: integer, string, ...
a situation, unlikely to occur in real-world scenarios.
Assume you have a class Person:

    class Person
    {
    public:
        // default constructor+destr.
        // getter and setter methods for all members
        [...] 
        
        bool operator <(const Person& p) const
            /// we need this for set and multiset support
        {
            return _socialSecNr < p._socialSecNr;
        }
        
        Poco::UInt64 operator()() const
            /// we need this operator to return the key for the map and multimap
        {
            return _socialSecNr;
        }
        
    private:
        std::string _firstName;
        std::string _lastName;
        Poco::UInt64 _socialSecNr;
    }
----

Ideally, one would like to use a Person as simple as one used a string. All that is needed is a template specialization of the <*TypeHandler*> 
template. Note that template specializations must be declared in the <!same namespace!> as the original template, i.e. <*Poco::Data*>. 
The template specialization must implement the following methods:

    namespace Poco {
    namespace Data {
    
    template <>
    class TypeHandler<class Person>
    {
    public:
        static std::size_t size()
        {
            return 3; // we handle three columns of the Table!
        }
    
       static void bind(std::size_t pos, const Person& obj, AbstractBinder* pBinder)
        {
            poco_assert_dbg (pBinder != 0);
            // the table is defined as Person (FirstName VARCHAR(30), lastName VARCHAR, SocialSecNr INTEGER(3))
            // Note that we advance pos by the number of columns the datatype uses! For string/int this is one.
            TypeHandler<std::string>::bind(pos++, obj.getFirstName(), pBinder);
            TypeHandler<std::string>::bind(pos++, obj.getLastName(), pBinder);
            TypeHandler<Poco::UInt64>::bind(pos++, obj.getSocialSecNr(), pBinder);
        }
    
        static void prepare(std::size_t pos, const Person& obj, AbstractPreparation* pPrepare)
        {
            poco_assert_dbg (pBinder != 0);
            // the table is defined as Person (FirstName VARCHAR(30), lastName VARCHAR, SocialSecNr INTEGER(3))
            // Note that we advance pos by the number of columns the datatype uses! For string/int this is one.
            TypeHandler<std::string>::prepare(pos++, obj.getFirstName(), pPrepare);
            TypeHandler<std::string>::prepare(pos++, obj.getLastName(), pPrepare);
            TypeHandler<Poco::UInt64>::prepare(pos++, obj.getSocialSecNr(), pPrepare);
        }
    
        static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor* pExt)
            /// obj will contain the result, defVal contains values we should use when one column is NULL
        {
            poco_assert_dbg (pExt != 0);
            std::string firstName;
            std::string lastName;
            Poco::UInt64 socialSecNr = 0;
            TypeHandler<std::string>::extract(pos++, firstName, defVal.getFirstName(), pExt);
            TypeHandler<std::string>::extract(pos++, lastName, defVal.getLastName(), pExt);
            TypeHandler<Poco::UInt64>::extract(pos++, socialSecNr, defVal.getSocialSecNr(), pExt);
            obj.setFirstName(firstName);
            obj.setLastName(lastName);
            obj.setSocialSecNr(socialSecNr);
        }
    };
    
    } } // namespace Poco::Data
----

And that's all you have to do. Working with Person is now as simple as working with a string:

    std::map<Poco::UInt64, Person> people;
    ses << "SELECT * FROM Person", into(people), now;
----

!!!RecordSet
The Poco::Data::RecordSet class provides a generic way to work with database tables.
Using a <[RecordSet]>, one can:
   - iterate over all columns and rows in a table
   - obtain meta information about columns (such as name, type, length, etc.)
   
To work with a RecordSet, first create a Statement, execute it, and
create the RecordSet from the Statement, as follows:

    Statement select(session);
    select << "SELECT * FROM Person";
    select.execute();
    RecordSet rs(select);
----
   
The number of rows in the RecordSet can be limited by specifying
a limit for the Statement.

Following example demonstrates how to iterate over all rows and columns
in a RecordSet:

    bool more = rs.moveFirst();
    while (more)
    {
        for (std::size_t col = 0; col < cols; ++col)
        {
            std::cout << rs[col].convert<std::string>() << " ";
        }
        std::cout << std::endl;
        more = rs.moveNext();
    }
----

As mentioned above, the number of rows retrieved into a RecordSet at a
time can be limited using the <[limit]> or <[range]> clause. Iterating
over all rows in a table a bunch of rows at a time can thus be done as
follows:

    Statement select(session);
    select << "SELECT * FROM Person", range(0, 10);
    RecordSet rs(select);
    while (!select.done())
    {
        select.execute();
        bool more = rs.moveFirst();
        while (more)
        {
            for (std::size_t col = 0; col < cols; ++col)
            {
                std::cout << rs[col].convert<std::string>() << " ";
            }
            std::cout << std::endl;
            more = rs.moveNext();
        }
    }
----


!!!Tuples
Poco::Tuple and vectors of Poco::Tuple provide a convenient way to work with rows when
column types are known, because TypeHandlers for them are readily available.

Consider the following example:

    typedef Poco::Tuple<std::string, std::string, int> Person;
    typedef std::vector<Person> People;

    People people;
    people.push_back(Person("Bart Simpson", "Springfield", 12));
    people.push_back(Person("Lisa Simpson", "Springfield", 10));
    
    Statement insert(session);
    insert << "INSERT INTO Person VALUES(:name, :address, :age)",
        use(people), now;
----
    
Of course, tuples can also be used in queries:

    Statement select(session);
    select << "SELECT Name, Address, Age FROM Person",
        into(people),
        now;
    
    for (People::const_iterator it = people.begin(); it != people.end(); ++it)
    {
        std::cout << "Name: " << it->get<0>() << 
            ", Address: " << it->get<1>() << 
            ", Age: " << it->get<2>() <<std::endl;
    }
----

!!!Session Pooling
Creating a connection to a database is often a time consuming
operation. Therefore it makes sense to save a session object for 
later reuse once it is no longer needed.

A Poco::Data::SessionPool manages a collection of sessions.
When a session is requested, the SessionPool first
looks in its set of already initialized sessions for an
available object. If one is found, it is returned to the
client and marked as "in-use". If no session is available,
the SessionPool attempts to create a new one for the client.
To avoid excessive creation of sessions, a limit
can be set on the maximum number of objects.

The following code fragment shows how to use the SessionPool:

    SessionPool pool("ODBC", "...");
    // ...
    Session sess(pool.get());
----

Pooled sessions are automatically returned to the pool when the
Session variable holding them is destroyed.