c++ - sqlite返回多行查询的第一行后返回SQLITE_DONE

Question

我正在开发一个客户端服务器应用程序，即使我对SQLITE3数据库进行了多行查询，服务器也只返回第一行。我正在运行ubuntu 20.04，sqlite 3.22.02018。我正在发布双方的代码，因此您可以更轻松地重现该代码。
客户:

#include <unistd.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <termios.h>
#include <unistd.h>
#include <iostream>
#include <string>
#include <sstream>
#include <sys/types.h>
#include <cstring>
#include <thread>
#include <chrono>

#define PORT     6200
#define MAXLINE  2048

class Server
{
    sockaddr_in servaddr;
    std::string hash; //Client's ID to identify itself for the server
    std::thread receiver; //Receives all messages from the server
    bool receiverGo; // Check whether the receiver is running or not
    int sockfd; // socket stuff
public:
    Server(sockaddr_in servaddr_, int sockfd_):servaddr(servaddr_),sockfd(sockfd_){
}
    void run(){
        receiverGo = true;
        receiver = std::thread([this] (){
            int n;
            char buffer[MAXLINE-1]; //buffer, where all messages will go through
            socklen_t addrlen = sizeof(sockaddr);
            while(this->receiverGo){ //receiver for all messages
                n = recvfrom(this->sockfd, (char *)buffer, MAXLINE,
                    MSG_DONTWAIT, (struct sockaddr *) &servaddr, &addrlen);
            if(n == -1) { // check if something was received. Sleeping for half a second if not.
                std::this_thread::sleep_for (std::chrono::milliseconds(500));
                continue;
            }
            buffer[n] = '\0';
            std::cout << buffer << std::endl; // printing the message
        }
    });
}

void stop(){ // stops the listener
    receiverGo = false;
    receiver.join();
}
void connect(){ // connect-to-server function
    send("hello, ");
    int n;
    char buffer[MAXLINE-1];
    socklen_t addrlen = sizeof(sockaddr);
    n = recvfrom(sockfd, (char *)buffer, MAXLINE, //temp receiver
        MSG_WAITALL, (struct sockaddr *) &servaddr, &addrlen);
    buffer[n] = '\0';
    std::stringstream buff;
    buff << buffer;
    std::string msgtype;
    buff >> msgtype;
    if(msgtype == "hello"){
        buff >> hash;
    }
}
void process(std::string input){
    if(input[0] == '/'){
        if(input[1] == 'j' && input[2] == 'o' && input[3] == 'i' && input[4] == 'n'){ //check if command is /join and ask for channel history
            send(hash + " " + input);
            send(hash + " /history");
        }
        else{
            send(hash + " " + input); //send a message to everyone in the channel
        }
    }
    else{
        send(hash + " /say " + input); //send a message to everyone in the channel
    }
}
void send(std::string message){ //easier to send stuff through a socket
    sendto(sockfd, (const char *)message.c_str(), message.length(),
    0, (const struct sockaddr *) &servaddr,
        sizeof(sockaddr));
    }
};
std::string getUserInput(){ //making my life harder
    std::string input;
    std::cin >> input;
    return input;
}

std::istream& getUserLine(std::string& in){ //making my life harder
    std::getline(std::cin, in);
    return std::cin;
}
// Driver code
int main() {
    int sockfd;
    std::string input;
    struct sockaddr_in     servaddr;

    // Creating socket file descriptor
    if ( (sockfd = socket(AF_INET, SOCK_DGRAM, 0)) < 0 ) {
        perror("socket creation failed");
        exit(EXIT_FAILURE);
    }

    memset(&servaddr, 0, sizeof(servaddr));

    // Filling server information
    servaddr.sin_family = AF_INET;
    servaddr.sin_port = htons(PORT);
    servaddr.sin_addr.s_addr = inet_addr("127.0.0.1");

    Server server(servaddr, sockfd);
    server.connect(); //connect to a server
    server.run(); // run the listener, etc.
    while(getUserLine(input)){ //wait for user's input
        server.process(input);
        if(input == "/quit"){
            server.stop();
            std::cout << "disconnected from the server, the program will exit in 3 seconds" << std::endl;
            sleep(3);
            close(sockfd);
            return 0;
        }
    }
    close(sockfd);
    return 0;
}

服务器:

#include <stdio.h>
#include <cstring>
#include <sstream>
#include <iostream>
#include <map>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <list>
#include <memory>
#include <algorithm>
#include <chrono>
#include <sqlite3.h>

#define PORT     6200
#define MAXLINE  2048
sqlite3 *db;
int sockfd; //socket
class Client;
std::map<std::string, std::list<std::weak_ptr<Client>>> rooms; //chat channels list
std::map<std::string, std::shared_ptr<Client>> users; // users dictionary, contains hashes (IDs of users that are generated for everyone individually) and nicknames
std::map<std::string, std::weak_ptr<Client>> usersByName; // unlike users dictionary, it doesn't have hashes, but does have Client class instances.
class Client{
    std::string nickname; //client's nickname
    sockaddr_in cliaddr;
    static long nextHash; //next hash to generate. 8 numbers.
    static const int required_pad = 8;
    std::string hash; // hash variable for client's hash
    std::string activeRoom; // active chat channel, /join command changes it
public:
    static std::tuple<std::string,std::string,std::string> separate(std::string input){ //separates buff variable to 2 strings: /command, arguments to the command
        std::string first, second, third;
        std::stringstream ss(input);
        ss >> first >> second;
        ss.ignore();
        std::getline(ss, third);
        return {first, second, third};
    }
    Client(){ //setting the default nickname to a client
        nickname = "guest";
    }
    Client(sockaddr_in cliaddr_):cliaddr(cliaddr_){};
    void send(std::string message){ //send a message to a client
        sendto(sockfd, (const char *)message.c_str(), message.length(),
        MSG_CONFIRM, (const struct sockaddr *) &cliaddr,
            sizeof(sockaddr));
    };
    std::string get_nickname(){ //get user's nickname
        return nickname;
    }
    std::string get_active(){ // get chat room memberrs
        return activeRoom;
    }
    std::string makeHash(){ //making "hash" to identify users, because everyone is "guest", and hash is generated for everyone
        std::string hash_ = std::to_string(nextHash++);
        int length = hash_.length();
        if(length < required_pad){
            hash_.insert(0, required_pad - length, '0');
        }
        hash = hash_;
        return hash;
    }
    void messageToRoom(std::string room, std::string msg){ //message to everyone in the chat
        for(auto &c:rooms.at(room)){
            if(auto c_real = c.lock()){
                c_real->send(msg);
            }
        }
    }
    std::string getHash(){ // get user's hash, returns 8 numbers.
        return hash;
    }
    void set_active(std::string room){ // set active chat channel for user
        activeRoom = room;
    }
    void process(std::shared_ptr<Client> client, std::string cmd, std::string msg){ //function that is processing all commands from users. cmd = command name - join, history, say. msg - arguments to the command.
        if(cmd == "/say"){
            if(!client->get_active().empty()){
                if(msg == ""){
                    client->send("message can't be empty");
                }
                else{
                    messageToRoom(client->get_active(), client->get_nickname() + ": " + msg);
                }
            }
        }
        else if(cmd == "/help"){
            client->send("1. Join #general channel (/join #general)");
            client->send("2. Do /history - it makes a request to the sqlite");
        }
        else if(cmd == "/history"){ //getting message history from the DB. Client, when joining a channel, requests a message history no longer than a day ago
            //That's where I have a problem!
            if(!client->get_active().empty()){
                sqlite3_stmt *stmt;
                sqlite3_prepare_v2(db, "select room, nick, message from message where dt > date('now','-1 day') and room = ?", -1, &stmt, NULL);
                int ret_code = 1;
                sqlite3_bind_text(stmt, 1, client->get_active().c_str(), -1, NULL);
                while((ret_code = sqlite3_step(stmt)) == SQLITE_ROW){
                    std::stringstream ss;
                    ss << sqlite3_column_text(stmt, 1) << ": " << sqlite3_column_text(stmt, 2);
                    client->send(ss.str());
                }
            }
            else{
                client->send("you should be in a channel");
            }
        }
        else if(cmd == "/join"){ //lets user join a chat channel. Makes a new one, if needed.
        if(msg[0] == '#'){
            if(msg.size() >= 60){
                client->send("room name couldn't be longer than 60 characters");
                bool newUser = false;
            }
            else if(rooms.find(msg) == rooms.end()){
                rooms.insert(std::make_pair(msg, std::list<std::weak_ptr<Client>>()));
            }
            std::list<std::weak_ptr<Client>> &roomMembers = rooms.at(msg);
            bool newUser = std::none_of(roomMembers.begin(), roomMembers.end(), [&client](std::weak_ptr<Client> &c){
                if(auto c_real = c.lock()){
                    return c_real->getHash() == client->getHash();
                }
                return false;
            });
            if(newUser){
                roomMembers.push_back(client);
                std::string result = msg;
                for(auto &c:rooms.at(msg)){
                    if(auto c_real = c.lock()){
                        result += " " + c_real->get_nickname();
                        if(c_real != client){
                            c_real->send(client->get_nickname() + " has joined");
                        }
                    }
                }
                client->send(result);
            }
            else{
                client->send("You've returned to the " + msg);
            }
            client->set_active(msg);

        }
        else
        {
            client->send("join error");
        }
    }
    }
};
long Client::nextHash = 0;
int main(){
    sqlite3_open("db.sqlite", &db); //opening db
    char buffer[MAXLINE]; // message buffer, where "hello client" will be
    struct sockaddr_in servaddr, cliaddr; //something from socket stuff, doesn't work without it

    // Creating socket file descriptor
    if ( (sockfd = socket(AF_INET, SOCK_DGRAM, 0)) < 0 ) {
        perror("socket creation failed");
        exit(EXIT_FAILURE);
    }

    memset(&servaddr, 0, sizeof(servaddr));
    memset(&cliaddr, 0, sizeof(cliaddr));

    // Filling server information
    servaddr.sin_family    = AF_INET; // IPv4
    servaddr.sin_addr.s_addr = INADDR_ANY;
    servaddr.sin_port = htons(PORT);

    // Bind the socket with the server address
    if ( bind(sockfd, (const struct sockaddr *)&servaddr,
            sizeof(servaddr)) < 0 )
    {
        perror("bind failed");
        exit(EXIT_FAILURE);
    }
    while(1){
        unsigned int len, n;

        len = sizeof(cliaddr);  //len is value/result

        n = recvfrom(sockfd, (char *)buffer, MAXLINE,
                    MSG_WAITALL, ( struct sockaddr *) &cliaddr,
                    &len);
        buffer[n] = '\0';
        std::string buff (buffer); //buffer's clone, and that's where all messages to clients will go through
        if(std::strncmp(buffer, "hello", 5) == 0){
            std::shared_ptr<Client> c = std::make_shared<Client>(cliaddr);
            std::string h = c->makeHash(); //making "hash" to identify users, because everyone is "guest", and hash is generated for everyone
            users.emplace(h,c); // add the client to the clients dictionary
            usersByName.emplace(c->get_nickname(), c);
            c->send("hello " + h); //sending welcome messages to the client
            c->send("connected to the server");
            c->send("welcome, stack overflow! for reproduce instructions, type /help");
        }
        else{
        auto parts = Client::separate(buff);
            try{
                std::shared_ptr<Client> c = users.at(std::get<0>(parts));
                c->process(c, std::get<1>(parts), std::get<2>(parts));
            }
            catch(const std::out_of_range& oor){
                std::cerr << "Out of Range error: " << oor.what() << '\n';
            }
        }
        printf("Client : %s\n", buffer);
    }
    return 0;
};

我检查了查询是否在sqlite的控制台中返回了几行，尽管我的代码仅返回结果的第一行。在while循环之后，ret_code为101，即SQLITE_DONE。为什么会这样呢？
表方案是

CREATE TABLE message(id integer primary key autoincrement, dt timestamp default current_timestamp, nick varchar(32) not null,room varchar(60) not null, message varchar(2048) not null);

。倾倒:

INSERT INTO message VALUES(96,'2020-09-15 10:19:18','guest','#general','a');
INSERT INTO message VALUES(97,'2020-09-15 10:19:18','guest','#general','b');
INSERT INTO message VALUES(98,'2020-09-15 10:19:18','guest','#general','c');
INSERT INTO message VALUES(99,'2020-09-15 10:19:19','guest','#general','d');

//将a更改为a，b，c，d
代码中的输出是

guest: a

命令行工具中的输出:

#general|guest|a
#general|guest|b
#general|guest|c
#general|guest|d

我提出的要求是:从消息中选择房间，昵称，消息，其中dt>日期(“现在”，“-1天”)

Answer 1

错误在以下行中:

sqlite3_bind_text(stmt, 1, client->get_active().c_str(), -1, NULL);

在这里，您传递的是c_str()函数的结果，该结果是瞬时结果，在执行查询之前，该结果可能会停止存在。您应该将其复制到某个缓冲区中(这是一种不好的方法)，或者更好的方法是将 SQLITE_TRANSIENT作为最后一个参数传递。上线:

sqlite3_bind_text(stmt, 1, client->get_active().c_str(), -1, SQLITE_TRANSIENT);