how to use the libchdb.so in c/c++ code?

[l1t1]

there is only a chdb.h file in the gz file , could you add some example?

I read the https://github.com/chdb-io/chdb/blob/main/programs/local/LocalChdb.cpp, it seems need other source code?

[wudidapaopao]

Hi @l1t1, to use libchdb.so, you only need the chdb.h header file and the compiled shared library libchdb.so. You don't need the rest of the source code if you're just using the library.

Below is a simple example in C that demonstrates how to use chdb.h together with libchdb.so:

#include <stdio.h>
#include <stdlib.h>
#include "chdb.h"

int main(void) {
    // 1. Prepare connection arguments
    // Session will keep the state of query.
    // If path is None, it will create a temporary directory and use it as the database path
    // and the temporary directory will be removed when the session is closed.
    // You can also pass in a path to create a database at that path where will keep your data.

    // You can also use a connection string to pass in the path and other parameters.
    // Examples:
    //     - "test.db" (for relative path)
    //     - "/path/to/test.db" (for absolute path)

    // Important:
    //     - There can be only one session at a time. If you want to create a new session, you need to close the existing one.
    //     - Creating a new session will close the existing one.
    char *argv[] = {
        "chdb_example",      // Program name
        "--path=test.db"    // Optional
    };
    int argc = 2;

    // 2. Create database connection
    chdb_connection *conn = chdb_connect(argc, argv);
    if (!conn) {
        fprintf(stderr, "Failed to create database connection\n");
        return EXIT_FAILURE;
    }
    printf("Connected to in-memory database successfully\n");

    // 3. Execute SQL query
    const char *query = "SELECT 'Hello, chDB!' AS message, 42 AS number";
    // The output supports various other rich formats, such as CSV, JSON, etc.
    // For more information, please see https://clickhouse.com/docs/interfaces/formats#formats-overview
    chdb_result *result = chdb_query(*conn, query, "CSV");
    if (!result) {
        fprintf(stderr, "Query execution failed\n");
        chdb_close_conn(conn);
        return EXIT_FAILURE;
    }

    // 4. Check for query errors
    const char *error = chdb_result_error(result);
    if (error) {
        fprintf(stderr, "Query error: %s\n", error);
        chdb_destroy_query_result(result);
        chdb_close_conn(conn);
        return EXIT_FAILURE;
    }

    // 5. Process query results
    printf("Query executed successfully. Retrieved %lu rows\n", chdb_result_rows_read(result));

    char *data = chdb_result_buffer(result);
    size_t data_len = chdb_result_length(result);

    printf("Result (%zu bytes):\n%.*s\n", data_len, (int)data_len, data);

    // 6. Clean up resources
    chdb_destroy_query_result(result);
    chdb_close_conn(conn);

    printf("Resources released. Program completed.\n");
    return EXIT_SUCCESS;
}

[l1t1]

thanks. it works, I wonder how to let the c program run faster. I already used -O3

export LD_LIBRARY_PATH=/par:/usr/local/lib
export LIBRARY_PATH=/par:/usr/local/lib
gcc chsql.c -O3 -o chsql -I . -lchdb
time ./chsql
Connected to in-memory database successfully
Query executed successfully. Retrieved 1 rows
Result (18 bytes):
"Hello, chDB!",42

Resources released. Program completed.

real    0m6.770s
user    0m0.108s
sys     0m0.712s

[wudidapaopao]

Under normal circumstances, the total execution time of this demo should be less than 1 second. The query "SELECT 'Hello, chDB!' AS message, 42 AS number" itself should be executed very quickly.

The demo includes three main steps: establishing a connection (chdb_connect), executing the query (chdb_query), and closing the connection (chdb_close_conn). To better understand where the time is being spent, you can add timing measurements before and after each of these function calls.

It’s worth noting that both chdb_connect and chdb_close_conn involve creating and deleting temporary files in the local machine's temporary directory. Additionally, during the first load of libchdb.so, there may be some I/O overhead related to loading the shared library itself. It’s possible that disk I/O performance on your system is contributing to the observed latency.

In real-world usage, it's recommended to reuse the connection returned by chdb_connect for multiple queries rather than creating and destroying a new connection for each query. This will significantly reduce overhead and improve overall performance.

[auxten]

I think the most time cost is on cold loading libchdb.so which will much faster on next run

[l1t1]

thank you, with your help, I ask deepseek to write a c++ program as following, add the timer .

#include <iostream>
#include <string>
#include <vector>
#include <chrono>
#include <algorithm>
#include <iomanip>
#include <map>
#include "chdb.h"

class ChDBClient {
private:
    chdb_connection* conn;
    bool timer_enabled;
    std::vector<std::string> history;
    std::string current_format;
    std::map<std::string, std::string> format_aliases;

    void initialize_format_aliases() {
        format_aliases = {
            {"csv", "CSVWithNames"},
            {"json", "JSONEachRow"},
            {"pretty", "Pretty"},
            {"vertical", "Vertical"},
            {"markdown", "Markdown"},
            {"tsv", "TabSeparatedWithNames"},
            {"table", "Pretty"}  // Alias for Pretty
        };
    }

    chdb_result* execute_query(const std::string& query) {
        auto start = std::chrono::high_resolution_clock::now();
        chdb_result* result = chdb_query(*conn, query.c_str(), current_format.c_str());
        auto end = std::chrono::high_resolution_clock::now();

        if (timer_enabled) {
            auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start);
            std::cout << "Query executed in " << duration.count() << " ms\n";
        }

        return result;
    }

    void print_result(chdb_result* result) {
        // Check for errors
        const char* error = chdb_result_error(result);
        if (error) {
            std::cerr << "Query error: " << error << "\n";
            return;
        }

        // Process results
        char* data = chdb_result_buffer(result);
        size_t data_len = chdb_result_length(result);
        size_t rows_read = chdb_result_rows_read(result);
        double elapsed = chdb_result_elapsed(result);

        if (data_len > 0) {
            std::string result_data(data, data_len);
            std::cout << result_data;

            if (timer_enabled) {
                std::cout << "\n(" << rows_read << " rows, " << elapsed << " sec)\n";
            } else if (rows_read > 0) {
                std::cout << "\n(" << rows_read << " rows)\n";
            }
        } else {
            std::cout << "Query executed successfully. No results returned.\n";
        }
    }

    void print_table(const std::vector<std::vector<std::string>>& rows, const std::vector<std::string>& headers) {
        if (rows.empty()) return;

        // Calculate column widths based on both headers and data
        std::vector<size_t> col_widths(headers.size(), 0);
        for (size_t i = 0; i < headers.size(); ++i) {
            col_widths[i] = headers[i].size();
        }
        for (const auto& row : rows) {
            for (size_t i = 0; i < row.size() && i < col_widths.size(); ++i) {
                col_widths[i] = std::max(col_widths[i], row[i].size());
            }
        }

        // Print header separator
        std::string header_sep = "+";
        for (size_t width : col_widths) {
            header_sep += std::string(width + 2, '-') + "+";
        }
        std::cout << header_sep << "\n";

        // Print header
        std::cout << "|";
        for (size_t i = 0; i < headers.size(); ++i) {
            std::cout << " " << std::left << std::setw(col_widths[i]) << headers[i] << " |";
        }
        std::cout << "\n" << header_sep << "\n";

        // Print rows (with potential truncation)
        size_t total_rows = rows.size();
        bool truncated = false;
        size_t print_count = total_rows;

        if (total_rows > 10) {
            print_count = 10;
            truncated = true;
        }

        // Print first 5 rows
        for (size_t i = 0; i < std::min(print_count / 2, total_rows); ++i) {
            print_row(rows[i], col_widths);
        }

        // Print ellipsis if truncated
        if (truncated) {
            std::cout << "|";
            for (size_t i = 0; i < col_widths.size(); ++i) {
                std::cout << " " << std::left << std::setw(col_widths[i]) << "..." << " |";
            }
            std::cout << "\n";
        }

        // Print last 5 rows if truncated
        if (truncated) {
            for (size_t i = std::max(total_rows - print_count / 2, print_count / 2); i < total_rows; ++i) {
                print_row(rows[i], col_widths);
            }
        } else {
            // Print remaining rows if not truncated
            for (size_t i = print_count / 2; i < total_rows; ++i) {
                print_row(rows[i], col_widths);
            }
        }

        std::cout << header_sep << "\n";

        if (truncated) {
            std::cout << "(" << total_rows << " rows total, showing first and last 5 rows)\n";
        } else if (total_rows > 0) {
            std::cout << "(" << total_rows << " rows)\n";
        }
    }

    void print_row(const std::vector<std::string>& row, const std::vector<size_t>& col_widths) {
        std::cout << "|";
        for (size_t i = 0; i < row.size() && i < col_widths.size(); ++i) {
            std::cout << " " << std::left << std::setw(col_widths[i]) << row[i] << " |";
        }
        std::cout << "\n";
    }

    // Parse CSV results and extract headers from column names
    void parse_csv(const std::string& csv_data, std::vector<std::string>& headers, std::vector<std::vector<std::string>>& rows) {
        headers.clear();
        rows.clear();
        
        std::vector<std::string> current_row;
        std::string current_field;
        bool in_quotes = false;
        bool is_first_row = true;

        for (char c : csv_data) {
            if (c == '"') {
                in_quotes = !in_quotes;
            } else if (c == ',' && !in_quotes) {
                current_row.push_back(current_field);
                current_field.clear();
            } else if (c == '\n' && !in_quotes) {
                current_row.push_back(current_field);
                
                if (is_first_row) {
                    headers = current_row;
                    is_first_row = false;
                } else {
                    rows.push_back(current_row);
                }
                
                current_row.clear();
                current_field.clear();
            } else {
                current_field += c;
            }
        }

        // Add the last field if not empty
        if (!current_field.empty()) {
            current_row.push_back(current_field);
        }
        if (!current_row.empty()) {
            if (is_first_row) {
                headers = current_row;
            } else {
                rows.push_back(current_row);
            }
        }
    }

public:
    ChDBClient() : conn(nullptr), timer_enabled(false), current_format("CSVWithNames") {
        initialize_format_aliases();
    }

    ~ChDBClient() {
        if (conn) {
            chdb_close_conn(conn);
        }
    }

    bool connect() {
        char *argv[] = {
            "chdb_client",
            "--path=:memory:"
        };
        int argc = 2;

        conn = chdb_connect(argc, argv);
        if (!conn) {
            std::cerr << "Failed to create database connection\n";
            return false;
        }
        std::cout << "Connected to in-memory database successfully\n";
        return true;
    }

    void run() {
        std::cout << "chDB client (type '.exit' to quit, '.help' for help)\n";

        while (true) {
            std::string input;
            std::cout << "chdb> ";
            std::getline(std::cin, input);

            // Trim whitespace
            input.erase(0, input.find_first_not_of(" \t\n\r\f\v"));
            input.erase(input.find_last_not_of(" \t\n\r\f\v") + 1);

            if (input.empty()) continue;

            // Add to history
            history.push_back(input);

            // Handle meta commands
            if (input[0] == '.') {
                if (input == ".exit") {
                    break;
                } else if (input == ".timer on") {
                    timer_enabled = true;
                    std::cout << "Query timer enabled\n";
                    continue;
                } else if (input == ".timer off") {
                    timer_enabled = false;
                    std::cout << "Query timer disabled\n";
                    continue;
                } else if (input.substr(0, 8) == ".format ") {
                    set_format(input.substr(8));
                    continue;
                } else if (input == ".help") {
                    print_help();
                    continue;
                } else if (input == ".history") {
                    print_history();
                    continue;
                } else {
                    std::cerr << "Unknown command: " << input << "\n";
                    continue;
                }
            }

            // Execute SQL query
            chdb_result* result = execute_query(input);
            if (!result) {
                std::cerr << "Query execution failed\n";
                continue;
            }

            // For Pretty format, just print the result directly
            if (current_format.substr(0, 6) == "Pretty" || current_format == "Vertical" || current_format == "Markdown") {
                print_result(result);
            } else {
                // For other formats, parse and display as table
                char* data = chdb_result_buffer(result);
                size_t data_len = chdb_result_length(result);
                
                if (data_len > 0) {
                    std::string result_data(data, data_len);
                    std::vector<std::string> headers;
                    std::vector<std::vector<std::string>> rows;
                    parse_csv(result_data, headers, rows);
                    
                    if (!headers.empty()) {
                        print_table(rows, headers);
                    } else {
                        std::cout << result_data;
                    }
                    
                    size_t rows_read = chdb_result_rows_read(result);
                    double elapsed = chdb_result_elapsed(result);
                    
                    if (timer_enabled) {
                        std::cout << "(" << rows_read << " rows, " << elapsed << " sec)\n";
                    } else if (rows_read > 0) {
                        std::cout << "(" << rows_read << " rows)\n";
                    }
                } else {
                    std::cout << "Query executed successfully. No results returned.\n";
                }
            }

            chdb_destroy_query_result(result);
        }
    }

    void set_format(const std::string& format) {
        std::string fmt_lower = format;
        std::transform(fmt_lower.begin(), fmt_lower.end(), fmt_lower.begin(), ::tolower);
        
        if (format_aliases.find(fmt_lower) != format_aliases.end()) {
            current_format = format_aliases[fmt_lower];
            std::cout << "Output format set to: " << current_format << "\n";
        } else {
            // Try to use the format directly if it's not an alias
            current_format = format;
            std::cout << "Output format set to: " << current_format << "\n";
        }
    }

    void print_help() {
        std::cout << "Available commands:\n"
                  << "  .exit          - Exit the client\n"
                  << "  .timer on/off  - Enable/disable query timing\n"
                  << "  .format <fmt>  - Set output format (csv, json, pretty, vertical, markdown, tsv)\n"
                  << "  .history       - Show command history\n"
                  << "  .help          - Show this help message\n"
                  << "  SQL queries    - Execute SQL queries\n";
    }

    void print_history() {
        std::cout << "Command history:\n";
        for (size_t i = 0; i < history.size(); ++i) {
            std::cout << "  " << i + 1 << ": " << history[i] << "\n";
        }
    }
};

int main() {
    ChDBClient client;
    if (!client.connect()) {
        return EXIT_FAILURE;
    }

    client.run();
    std::cout << "Goodbye!\n";
    return EXIT_SUCCESS;
}

it run a sql is fast(some ms)

chdb> .timer on
Query timer enabled
chdb> .format CSVWithNames
Output format set to: CSVWithNames
chdb> select * from file('foods.csv');
Query executed in 24 ms
+------------+----------+--------+----------+
| category   | calories | fats_g | sugars_g |
+------------+----------+--------+----------+
| vegetables | 45       | 0.5    | 2        |
| seafood    | 150      | 5      | 0        |
| meat       | 100      | 5      | 0        |
| fruit      | 60       | 0      | 11       |
| seafood    | 140      | 5      | 1        |
| ...        | ...      | ...    | ...      |
| seafood    | 130      | 1.5    | 0        |
| fruit      | 130      | 0      | 25       |
| meat       | 100      | 7      | 0        |
| vegetables | 30       | 0      | 5        |
| fruit      | 50       | 0      | 11       |
+------------+----------+--------+----------+
(27 rows total, showing first and last 5 rows)
(27 rows, 0.0232782 sec)