libpqxx v12 - 将向量值动态绑定到准备好的语句(替代调用) [英] libpqxx v12 - dynamically bind vector-values to prepared statement (alternative to invocation)
问题描述
我想使用准备好的语句一次将数千行插入到我的 postgres 数据库中.要插入的数据存储在结构向量中.
通过阅读 How to在 Postgresql for C++ 中准备语句和绑定参数 我以为我找到了方法.
不幸的是,我使用的当前版本的 libpqxx 不再支持 pqxx::prepare::invocation,我无法在文档/互联网中找到任何替代方案.>
我为我的目的尝试的代码如下:
//pqxx 头#include "pqxx/connection.hxx";#include "pqxx/transaction.hxx";#include "pqxx/nontransaction.hxx";#include "pqxx/compiler-public.hxx";#include "pqxx/result.hxx";#include "pqxx/prepared_statement.hxx";#include "pqxx/transaction_base.hxx";#include "pqxx/internal/statement_parameters.hxx";...int main(){结构测试数据{字符串 uuid;浮点数;字符串时间戳;};向量数据缓冲区;测试数据数据库数据;字符串 queryStr;const char* 查询;stringstream connStream;字符串流查询流;字符串 DB_NAME = "dbname";字符串 DB_USER = "postgres";字符串 DB_PASSWORD = 密码";字符串 DB_HOSTADDR = "127.0.0.1";字符串 DB_PORT = "1234";字符串 DB_SCHEMA = "test_schema";string tableName = "test_table";//创建随机数据for (int i = 0; i <100000; i++) {dbdata.uuid =fe2b22ba-6ce7-4bbc-8226-d7696fe7a047";dbdata.rndNo = i/3.123;dbdata.timestamp = systemDateTimeSQLFormat(0);dataBuffer.push_back(dbdata);}stringstream connStream;connStream<<"dbname = "<<DB_NAME<<"用户 = "<<DB_USER<<"密码 = "<<DB_PASSWORD <<"主机地址 = "<<DB_HOSTADDR <<"端口 = "<<DB_PORT;连接 dbConn(connStream.str());如果(dbConn.is_open()){cout<<"成功打开数据库:"<<dbConn.dbname() <<结束;}别的 {cout<<无法打开数据库"<<结束;返回;}pqxx::nontransaction W(dbConn);std::string m_insertCommand = "INSERT INTO test_schema.test_table (column1, column2, column3) VALUES";int buffSize = dataBuffer.size();for (size_t i = 0; i < buffSize; i++){unsigned int countOf$ = i * 3;for (unsigned int i = 0; i <3; ++i){如果(我== 0){m_insertCommand += "(";}别的{m_insertCommand += ", ";}m_insertCommand += "$";std::stringstream ss;ss<
pqxx::prepare::make_dynamic_params
可能会解决您的问题.它解决了我的问题.使用这种方式:
for (size_t i = 0; i 向量;vect.reserve(3);vect.push_back(pqxx::to_string(element.rndNo));vect.push_back(element.timestamp);vect.push_back(element.uuid);work.exec_params(m_insertCommand, pqxx::prepare::make_dynamic_params(vect));}
I would like to use prepared statements to insert thousands of rows at once into my postgres database. The data to insert is stored in a vector of structs.
By reading the answers to How to prepare statements and bind parameters in Postgresql for C++ I thought I found the way how to do it.
Unfortunately, the current version of libpqxx I am using doesn't support pqxx::prepare::invocation anymore and I wasn't able to find any alternative in the docs/internet.
The code I tried out for my purpose is the following:
//pqxx header
#include "pqxx/connection.hxx"
#include "pqxx/transaction.hxx"
#include "pqxx/nontransaction.hxx"
#include "pqxx/compiler-public.hxx"
#include "pqxx/result.hxx"
#include "pqxx/prepared_statement.hxx"
#include "pqxx/transaction_base.hxx"
#include "pqxx/internal/statement_parameters.hxx"
...
int main(){
struct testData {
string uuid;
float rndNo;
string timestamp;
};
vector<testData> dataBuffer;
testData dbdata;
string queryStr;
const char* query;
stringstream connStream;
stringstream queryStream;
string DB_NAME = "dbname";
string DB_USER = "postgres";
string DB_PASSWORD = "password";
string DB_HOSTADDR = "127.0.0.1";
string DB_PORT = "1234";
string DB_SCHEMA = "test_schema";
string tableName = "test_table";
//Create Random data
for (int i = 0; i < 100000; i++) {
dbdata.uuid = "fe2b22ba-6ce7-4bbc-8226-d7696fe7a047";
dbdata.rndNo = i / 3.123;
dbdata.timestamp = systemDateTimeSQLFormat(0);
dataBuffer.push_back(dbdata);
}
stringstream connStream;
connStream << "dbname = " << DB_NAME << " user = " << DB_USER << " password = " << DB_PASSWORD << " hostaddr = " << DB_HOSTADDR << " port = " << DB_PORT;
connection dbConn(connStream.str());
if (dbConn.is_open()) {
cout << "Opened database successfully: " << dbConn.dbname() << endl;
}
else {
cout << "Can't open database" << endl;
return;
}
pqxx::nontransaction W(dbConn);
std::string m_insertCommand = "INSERT INTO test_schema.test_table (column1, column2, column3) VALUES";
int buffSize = dataBuffer.size();
for (size_t i = 0; i < buffSize; i++)
{
unsigned int countOf$ = i * 3;
for (unsigned int i = 0; i < 3; ++i)
{
if (i == 0)
{
m_insertCommand += "(";
}
else
{
m_insertCommand += ", ";
}
m_insertCommand += "$";
std::stringstream ss;
ss << countOf$ + i + 1;
m_insertCommand += ss.str();
}
if (i < buffSize - 1)
m_insertCommand += ") ,";
}
m_insertCommand += ")";
//FOLLOWING CODE NOT POSSIBLE WITH libpqxx v12
dbConn.prepare("insert_into_db", m_insertCommand);
pqxx::prepare::invocation = W.exec_prepared("insert_into_db"); //prepare doesn't have a member "invocation"
for (size_t i = 0; i < buffSize; i++)
{
inv(dataBuffer.at(i).rndNo)(dataBuffer.at(i).timestamp)(dataBuffer.at(i).uuid); //inv not defined
}
inv.exec();
return 1;
}
pqxx::prepare::make_dynamic_params
will probably solve your problem. It's solved my problem. Use this way:
for (size_t i = 0; i < buffSize; ++i)
{
auto element = dataBuffer.at(i);
vector<string> vect;
vect.reserve(3);
vect.push_back(pqxx::to_string(element.rndNo));
vect.push_back(element.timestamp);
vect.push_back(element.uuid);
work.exec_params(m_insertCommand, pqxx::prepare::make_dynamic_params(vect));
}
这篇关于libpqxx v12 - 将向量值动态绑定到准备好的语句(替代调用)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!