如何在C ++的Postgresql中准备语句和绑定参数 [英] How to prepare statements and bind parameters in Postgresql for C++

问题描述

我对 C ++ 还是很陌生，并且对 pqxx 库有点了解。我要实现的是准备语句和绑定参数。在 PHP 中，我习惯以一种简洁明了的方式执行此操作：

I'm quite new to C++ and know a little bit about pqxx library. What I want to implement is to prepare statements and bind parameters. In PHP I'm used to doing this in such a nice and concise manner:

$s = $db->prepare("SELECT id FROM mytable WHERE id = :id");
$s->bindParam(':id', $id);
$s->execute();

或使用令牌：

$data = array();
$data[] = 1;
$data[] = 2;
$s = $db->prepare("SELECT id FROM mytable WHERE id = ? or id = ?");
$s->execute($data);

我试图从pqxx中删除文档如何实现此功能，但对我而言，文档看起来像是一团糟，缺少简短的示例（如我上面提供的示例） ）。我希望在处理 C ++中的 Postgresql 时，也能提供这样的简单示例（或相当简单-无需编写庞然大物的代码）。

I tried to fugure out from pqxx documentation how to implement this, but to me documentation looks like a mess and lacks short and simple examples (like I provided above). I hope someone can also provide such simple examples (or of comparable simplicity - without having to write some behemoth code) when dealing with Postgresql in C++.

推荐答案

一个简单的示例。

#include<pqxx/pqxx>
#include<iostream>

int main()
{
    std::string name = "name";
    int id = 0;
    try {
        //established connection to data base
        pqxx::connection c("dbname=mydb user=keutoi");
        pqxx::work w(c);
        //statement template
        c.prepare("example", "SELECT id  FROM mytable WHERE id = $1");
        //invocation as in varible binding
        pqxx::result r = w.prepared("example")(id).exec();

        w.commit();
        //result handling for accessing arrays and conversions look at docs
        std::cout << r.size() << std::endl;
    }
    catch(const std::exception &e)
    {
        std::cerr << e.what() << std::endl;
        return 1;
    }
    return 0;
}

函数w.prepared（）有点复杂。它类似于haskell中的curried（curry）函数，因为它需要一个参数并返回另一个函数，而后者又需要另一个参数。

The function w.prepared() is a bit convoluted. It's similar to a curried(curry) function in haskell, as in it takes a parameter and returns another function which in turn takes another parameter. That kind of thing.

文档说：

您如何通过这些？参数？ C ++没有好的方法可以让您将无数可变的参数传递给函数调用，并且编译器也不知道您将要传递多少个参数。这样做有一个窍门：您可以将准备好的函数返回的值当作一个函数，调用该函数以传递参数。从该调用返回的内容是相同的，因此您可以再次调用它以传递另一个参数，依此类推。

How do you pass those parameters? C++ has no good way to let you pass an unlimited, variable number of arguments to a function call, and the compiler does not know how many you are going to pass. There's a trick for that: you can treat the value you get back from prepared as a function, which you call to pass a parameter. What you get back from that call is the same again, so you can call it again to pass another parameter and so on.

在此传递所有参数之后这样，您可以通过在调用时调用exec来调用带有参数的语句

Once you've passed all parameters in this way, you invoke the statement with the parameters by calling exec on the invocation

如果有更多参数，请使用$ 1 $ 2，依此类推 prepare 函数。

If there are more parameters use $1 $2 and so on in the prepare function.

c.prepare("SELECT id name FROM mytable WHERE id = $1 AND name = $2")

并给出变量

w.prepared("example")(dollar1_var)(dollar2_var).exec()

动态准备的示例

#include<pqxx/pqxx>
#include<iostream>
#include<vector>

//Just give a vector of data you can change the template<int> to any data type
pqxx::prepare::invocation& prep_dynamic(std::vector<int> data, pqxx::prepare::invocation& inv)
{
    for(auto data_val : data)
        inv(data_val);
    return inv;
}

int main()
{
    std::string name = "name";

    //a data array to be used.
    std::vector<int> ids;
    ids.push_back(0);
    ids.push_back(1);

    try {
        pqxx::connection c("dbname=mydb user=keutoi");
        pqxx::work w(c);

        c.prepare("example", "SELECT id  FROM mytable WHERE id = $1 or id = $2");
        pqxx::prepare::invocation w_invocation = w.prepared("example");

        //dynamic array preparation
        prep_dynamic(ids, w_invocation);
        //executing prepared invocation.
        pqxx::result r = w_invocation.exec();

        w.commit();

        std::cout << r.size() << std::endl;
    }
    catch(const std::exception &e)
    {
        std::cerr << e.what() << std::endl;
        return 1;
    }
    return 0;
}

如果要处理其他数据类型，请使用此函数定义

if you want to handle other data types use this function definition

template<class T> pqxx::prepare::invocation& prep_dynamic(std::vector<T> data, pqxx::prepare::invocation& inv)
{
    for(auto data_val : data)
        inv(data_val);
    return inv;
}

这篇关于如何在C ++的Postgresql中准备语句和绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！