C++ Query::exec_direct方法代码示例

/**
 * Create the table "phone_number", which lists all known telephone numbers.
 */
int PhoneBook::create_table_phone_number()
{
    int     rc;
    char    buffer[256];
    Query q;

    //-------------------------------------------------------------------
    // Create the phone_number table
    //   uint64         id
    //   nvarchar       name(MAX_CONTACT_NAME)
    //   uint64         ring_id
    //   varchar        picture_name(FILENAME_MAX)
    //   blob           picture
    //-------------------------------------------------------------------
    sprintf( buffer,
        "create table phone_number ("
        "  contact_id uint64 not null,"
        "  number ansistr(%d) not null,"
        "  type uint64 not null,"
        "  speed_dial sint64,"
        "  constraint contact_ref foreign key (contact_id) references contact(id)"
        ")",
        MAX_PHONE_NUMBER);

    if  (DB_SUCCESS(rc = q.exec_direct(db, buffer))) {
        //---------------------------------------------------------------
        // Create contact_id index on PHONE_NUMBER table
        //---------------------------------------------------------------
        rc = q.exec_direct(db,
            "create index by_contact_id on phone_number(contact_id)" );
    }

    return print_error(rc, q);
}

/**
 * Briefly list all contacts in the database.
 */
void PhoneBook::list_contacts_brief()
{
    Query       q;
    const char  *cmd;

    cmd = "select id, name "
          "  from contact "
          "  order by name ";

    if  (DB_SUCCESS(print_error(q.exec_direct(db, cmd), q))) {
        //---------------------------------------------------------------
        // Bind local data fields to the data retrieved by the SQL call.
        // The field number is determined by the order of the fields
        // in the select statement.
        //
        // Field bindings can be created before or after the query is executed.
        //---------------------------------------------------------------
        IntegerField  id(q, "id");
        WStringField  name(q, "name");

        for (q.seek_first(); !q.is_eof(); q.seek_next()) {
            char name_mbs[50];
            wcstombs(name_mbs, WString(name).c_str(), sizeof name_mbs/sizeof name_mbs[0]);

            cout << (long) id << '\t';
            cout << name_mbs << endl;
        }
    }
    return;
}

/**
 * Create sequences. Sequences are used to generate unique identifiers.
 *
 * Demonstrates:
 * - defining sequences
 */
int PhoneBook::create_sequences()
{
    Query q;
    //-------------------------------------------------------------------
    // Create CONTACT_ID sequence
    //-------------------------------------------------------------------
    return print_error(q.exec_direct(db, "create sequence contact_id start with 1"), q);
}

/**
 * Create the table "contact", which lists contacts in the phone book.
 *
 * Demonstrates:
 * - defining table schema: fields and indexes
 * - unique and non-unique indexes
 * - add_datatype() functions return FieldDesc so they can be chained together
 * - add_index() functions return IndexDesc so they can be chained together
 */
int PhoneBook::create_table_contact(bool with_picture)
{
    int     rc;
    char    buffer[256];
    Query q;

    //-------------------------------------------------------------------
    // Create the CONTACT table
    //   uint64         id
    //   nvarchar       name(MAX_CONTACT_NAME)
    //   uint64         ring_id
    //   varchar        picture_name(FILENAME_MAX)
    //   blob           picture
    //-------------------------------------------------------------------
    if (with_picture)
        sprintf(buffer, 
            "create table contact ("
            "  id uint64 not null,"
            "  name utf16str(%d) not null,"
            "  ring_id uint64,"
            "  picture_name varchar(%d),"
            "  picture blob,"
            "  constraint by_id primary key (id)"
            ")",
            MAX_CONTACT_NAME, MAX_FILE_NAME);
    else
        sprintf(buffer, 
            "create table contact ("
            "  id uint64 not null,"
            "  name utf16str(%d) not null,"
            "  ring_id uint64,"
            "  picture_name varchar(%d),"
            "  constraint by_id primary key (id)"
            ")",
            MAX_CONTACT_NAME, MAX_FILE_NAME);

    if  (DB_SUCCESS(rc = q.exec_direct(db, buffer))) {
        //-----------------------------------------------------------
        // Create name index on CONTACT table
        //-----------------------------------------------------------
        rc = q.exec_direct(db, "create index by_name on contact(name)");
    }

    return print_error(rc, q);
}

/**
 * Commit transaction
 */
void PhoneBook::tx_commit()
{
    Query q;
    // Equivalent to: db.tx_commit();
    print_error(q.exec_direct(db, "commit"), q);
}

/**
 * Start transaction
 */
void PhoneBook::tx_start()
{
    Query q;
    // Equivalent to: db.tx_begin();
    print_error(q.exec_direct(db, "start transaction"), q);
}

/**
 * List all contacts in the database with full phone numbers
 *
 * Demonstrates:
 * - parent/child relationships
 */
void PhoneBook::list_contacts(int sort)
{
    Query       q;
    const char  *cmd;
    uint64_t    prev_id = 0;

    const char* query_by_name = 
        "select A.id, A.name, A.ring_id, A.picture_name, B.number, B.type, B.speed_dial"
        "  from contact A, phone_number B"
        "  where A.id = B.contact_id"
        "  order by A.name, B.type";
    const char* query_by_id =
        "select A.id, A.name, A.ring_id, A.picture_name, B.number, B.type, B.speed_dial"
        "  from contact A, phone_number B"
        "  where A.id = B.contact_id"
        "  order by A.id, B.type";
    const char* query_by_ring_id_name =
        "select A.id, A.name, A.ring_id, A.picture_name, B.number, B.type, B.speed_dial"
        "  from contact A, phone_number B"
        "  where A.id = B.contact_id"
        "  order by A.ring_id, A.name, B.type";

    /* Choose the query for the selected sort order. */
    switch (sort) {
        case 0:
            cmd = query_by_id;
            break;
        case 1:
            cmd = query_by_name;
            break;
        case 2:
            cmd = query_by_ring_id_name;
            break;
        default:
            return;
    }

    if  (DB_SUCCESS(print_error(q.exec_direct(db, cmd), q))) {
        //---------------------------------------------------------------
        // Bind local data fields to the data retrieved by the SQL call.
        // The field number is determined by the order of the fields
        // in the select statement.
        //
        // Field bindings can be created before or after the query is executed.
        //---------------------------------------------------------------
        IntegerField    id          (q, "id");
        WStringField    name        (q, "name");
        IntegerField    ring_id     (q, "ring_id");
        StringField     picture_name(q, "picture_name");
        StringField     number      (q, "number");
        IntegerField    type        (q, "type");
        IntegerField    speed_dial  (q, "speed_dial");

        for (q.seek_first(); !q.is_eof(); q.seek_next()) {
            //-----------------------------------------------------------
            // Otherwise, if brief is NOT set we want to display the
            // entire row from the selected data.
            //
            // For contacts with numerous phone numbers, only display
            //   the ID, NAME, RING_TONE, and PICTURE_NAME once.
            //-----------------------------------------------------------
            if  ( (uint64_t)id != prev_id ) {
                char name_mbs[50];
                wcstombs(name_mbs, WString(name).c_str(), sizeof name_mbs/sizeof name_mbs[0]);
                
                prev_id = id;
                cout << "Id: " << (long) id << endl;
                cout << "Name: " << name_mbs << endl;

                if (!ring_id.is_null())
                    cout << "Ring tone id: " << (int) ring_id << endl;

                if (!picture_name.is_null())
                    cout << "Picture name: " << String(picture_name).c_str() << endl;
            }

            cout << "Phone number: " << String(number).c_str() << " (";
            switch ((long)type) {
                case HOME:   cout << "Home"; break;
                case MOBILE: cout << "Mobile"; break;
                case WORK:   cout << "Work"; break;
                case FAX:    cout << "Fax"; break;
                case PAGER:  cout << "Pager"; break;
            }
            if (((long)speed_dial) >= 0)
                cout << ", speed dial " << (long) speed_dial;

            cout << ")" << endl;
        }
    }
    return;
}