Selecting Multiple Records

As discussed above, DAME uses the same syntax to access a single row or multiple rows from a table. For example, Let us see how we can we print all the employees who are in the same department as given employee.

First the DAME source:

example2.dame
        Dame::ExampleDb::emp GetByDeptno () {
                select empno, ename, job, coalesce(mgr,0), sal, coalesce(comm, 0), deptno, to_char(hiredate, 'dd/mm/yyyy hh24:mi:ss')
                into :Empno, :Name, :Job, :Manager, :Salary, :Commission, :Deptno, :Hiredate
                from emp
                where empno <> :Empno and
                deptno = :Deptno
        } ;
        

Note the declaration of function GetByDeptno. We do not want to write GetByDeptno(void) since we want to pass the department number and employee number through an emp record.

The code for performing this function is shown below:

example3.cpp

            Dame::Example::SelectDB                 edb (&db) ;
            Dame::ExampleDb::emp*                   ep ;
            edb.GetByEmpno (7369, Dame::make_single(&ep)) ;
            edb.GetByDeptno (*ep, std::mem_fun(&Dame::ExampleDb::emp::Print)) ;
        

We made use of STLs std::mem_fun adapter to converter emp::Print to a function of type Print(emp*).

Selecting Records Into Standard Containers

Standard STL containers comes in multiple flavors. Some of the standard containers are vector, list, deque, set, map. For DAME to be useful we should have mechanisms wherein the selected rows are converted into objects and stored in different types of containers. The standard STL defines three types of insertors. A front insertor that uses push_front to add an element into a container, a back insertor that uses push_back to add an element into a container and a normal insertor that uses insert to add an element into a container.

Corresponding to each of the insertors, DAME utility.hpp provides three adapter functions. Any standard container can be used along with these adapters.

The following program shows how we can make use of the standard containers std::vector, std::list to make use of DAMEs capabilities. This program makes use of the same GetByDeptno defined in the ealier example.

example3.cpp
                std::vector<Dame::ExampleDb::emp*>      vep ;
                std::list<Dame::ExampleDb::emp*>        lep ;
                edb.GetByDeptno (*ep, Dame::make_back_insertor(vep)) ;
                std::cout << "Vector size = " << vep.size() << std::endl ;
                edb.GetByDeptno (*ep, Dame::make_front_insertor(lep)) ;
                std::cout << "List size = " << vep.size() << std::endl ;
            

Selecting Records Into Associative Containers

Where as selecting into containers like vector, list is straight forward, selecting into associative containers like std::map, std::set requires some efforts from the users of DAME. Suppose we want find department wise salary totals for all employees.

First the DAME source:

example2.dame

                Dame::ExampleDb::emp GetByDeptno () {
                    select empno, ename, job, coalesce(mgr,0), sal, coalesce(comm, 0), deptno, to_char(hiredate, 'dd/mm/yyyy hh24:mi:ss')
                    into :Empno, :Name, :Job, :Manager, :Salary, :Commission, :Deptno, :Hiredate
                    from emp
                    where empno <> :Empno and
                    deptno = :Deptno
                } ;
            

The Iterator class that is required to access to create the map and its usage is shown below:

example4.cpp

                class Mapper
                {
                    std::map<std::string, double>&      m ;
                public:
                    Mapper (std::map<std::string, double>& _m)
                        : m(_m) {
                    }
                    int operator() (Dame::ExampleDb::emp* e) {
                        m[e->GetDeptname()] = e->GetSalary() ;
                    }
                } ;
                ...
                ...
                Dame::Example::SelectDB                 edb (&db) ;
                std::map<std::string, double>     smap ;

                edb.GetSalByDept (Mapper(smap)) ;
            

Bad design!!!: In a real life project using the emp class to retrieve the grouped by salaries might be considered as bad design choice. I did so that I do not write another sample program. Do not do this in your regular code.

Selecting Records In Chunks

DAME provided adapter make_XXX_insertor accepts a second parameter. The parameter controls the number of records that are fetched each time. You can use the _next functions to retrieve the next set of records as shown below:

            std::vector<Dame::ExampleDb::emp*>      vep ;
            GetByDeptno (ep, Dame::make_back_insertor(vep, 5)) ;
            ..... Fetched 5 records and processed
            GetByDeptno (ep, Dame::make_back_insertor(vep, 5)) ;
            ..... Fetch next 5 records.
            

DAME does not provide any indication at the end of the record set. You need to check the size of the container or use some other mechanism to perform this check.