如何使用 C++ libpqxx API 将二进制数据插入到 PostgreSQL BYTEA 列中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16047986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert binary data into a PostgreSQL BYTEA column using the C++ libpqxx API?
提问by Stéphane
I'd like to insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details, and http://pqxx.org/has been down for the past few days.
我想在 BYTEA 列中插入一些二进制数据，但我发现 Doxygen 输出缺乏细节，而且http://pqxx.org/过去几天一直关闭。
How would I go about inserting the contents of somefile.bin into a table with a BYTEA column?
我将如何将 somefile.bin 的内容插入带有 BYTEA 列的表中？
What I have is along these lines:
我所拥有的是沿着这些路线：
pqxx::work work( conn );
work.exec( "CREATE TABLE test ( name varchar(20), data BYTEA )" );
work.exec( "INSERT INTO test( name, data ) VALUES ( 'foo', <insert filename.bin here> )" );
work.commit();
If it makes a difference, I'd like to use the new hexformat for BYTEA available in PostgreSQL 9.1.
如果它hex有所作为，我想使用PostgreSQL 9.1 中可用的 BYTEA新格式。
采纳答案by Stéphane
Figured it out. Here is an example showing how to insert a bunch of binary objects into a table:
弄清楚了。这是一个示例，显示如何将一堆二进制对象插入表中：
pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES (, )" );
pqxx::work work( conn );
for ( ... )
{
std::string name = "foo";
void * bin_data = ...; // obviously do what you need to get the binary data...
size_t bin_size = 123; // ...and the size of the binary data
pqxx::binarystring blob( bin_data, bin_size );
pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
}
work.commit();
Here is how to get the binary data back out of the database:
以下是如何从数据库中取回二进制数据：
pqxx::result result = work.exec( "SELECT * FROM mytable" );
for ( const auto &row : result )
{
pqxx::binarystring blob( row["binfile"] );
void * ptr = blob.data();
size_t len = blob.size();
...
}
回答by Andrey
There is not pqxx::bynarystring in insertion. I used the following solution to do that:
插入中没有 pqxx::bynarystring。我使用以下解决方案来做到这一点：
=== STORING AN wxImage IN DATABASE ====
=== 在数据库中存储 wxImage ====
//Getting image string data
wxImage foto = (...);
wxMemoryOutputStream stream;
foto.SaveFile(stream,wxBITMAP_TYPE_PNG);
wxStreamBuffer* streamBuffer = stream.GetOutputStreamBuffer();
size_t tamanho = streamBuffer->GetBufferSize();
char* fotoData = reinterpret_cast<char*>(streamBuffer->GetBufferStart());
string dados(fotoData, tamanho);
//Performing the query
conn->prepare("InsertBinaryData", "INSERT INTO table1(bytea_field) VALUES (decode(encode(,'HEX'),'HEX'))") ("bytea",pqxx::prepare::treat_binary);
pqxx::work w = (...);
w.prepared(dados).exec();
=== RETRIEVING AN wxImage FROM DATABASE ====
=== 从数据库中检索 wxImage ====
pqxx::result r = w.exec("SELECT bytea_field FROM table1 WHERE (...)");
w.commit();
const result::tuple row = r[0];
const result::field tfoto = row[0];
pqxx::binarystring bs(tfoto);
const char* dadosImg = bs.get();
size_t size = bs.length();
wxMemoryInputStream stream(dadosImg,size);
wxImage imagem;
imagem.LoadFile(stream);
I hope it be helpfull.
我希望它有帮助。
回答by bobeff
Instead of using prepared SQL statement with conn.prepareas in Stéphane's answer if you want you can simply escape the binary data with one of overloads of pqxx::escape_binaryfunction. Hereis the documentation.
conn.prepare如果您愿意，可以简单地使用pqxx::escape_binary函数重载之一来转义二进制数据，而不是像在 Stéphane 的回答中那样使用准备好的 SQL 语句。这是文档。
回答by David Scarlett
Since this question is the top search result for inserting a BYTEA via libpqxx, here's another way to do it using a parameterized query, which is more appropriate if performing a single insert.
由于这个问题是通过 libpqxx 插入 BYTEA 的最高搜索结果，这里有另一种使用参数化查询的方法，如果执行单个插入，则更合适。
// Assuming pre-existing pqxx::connection c, void * bin_data, size_t bin_size...
pqxx::work txn(c);
pqxx::result res = txn.parameterized("INSERT INTO mytable(name, binfile) VALUES (, )")
(name)
(pqxx::binarystring(bin_data, bin_size))
.exec();
txn.commit();
Note that the parameters should notbe escaped with the quoteor escmethods.
请注意，参数应不与转义quote或esc方法。
Also note that the accepted answer of using pqxx::connection::preparecauses the prepared query to exist for the entire lifetime of the connection. If the connection will be long-lasting and the prepared query is no longer needed, it should probably be deleted by calling pqxx::connection::unprepare.
另请注意，接受的 using 答案pqxx::connection::prepare会导致准备好的查询在连接的整个生命周期内都存在。如果连接将是持久的并且不再需要准备好的查询，则可能应该通过调用 将其删除pqxx::connection::unprepare。
回答by LAL
I've come up with a flexible solution using binary data inside std::string.
我想出了一个使用二进制数据的灵活解决方案std::string。
I propose this new solution because current answers are old (2013) and I was looking for a multi insert query using pqxx 5.0.1.
我提出这个新解决方案是因为当前的答案是旧的（2013 年），我正在寻找使用 pqxx 5.0.1 的多插入查询。
With the solution bellow you have the flexibility to use a for loopto append multiple binary data in a single insert query.
使用下面的解决方案，您可以灵活地使用 afor loop在单个插入查询中附加多个二进制数据。
CustomStruct data = .... ; // have some binary data
// initialise connection and declare worker
pqxx::connection conn = new pqxx::connection(...);
pqxx::work w(conn);
// prepare query
string query += "INSERT INTO table (bytea_field) VALUES ("
// convert your data in a binary string.
pqxx::binarystring blob((void *)&(data), data.size());
// avoid null character to bug your query string.
query += "'"+w.esc_raw(blob.str())+"');";
//execute query
pqxx::result rows = w.exec(query);
When we want to retrieve data from the database you should have the scope of your datatype (CustomStructfor instance) and you should be able to cast it back in the binary format of your choice.
当我们想从数据库中检索数据时，您应该拥有数据类型的范围（CustomStruct例如），并且您应该能够将其转换为您选择的二进制格式。
// assuming worker and connection are declared and initialized.
string query = "SELECT bytea_field FROM table;";
pqxx::result rows = w.exec(query);
for(pqxx::result::iterator col = rows.begin(); col != rows.end(); ++col)
{
pqxx::binarystring blob(col[0]);
CustomStruct *data = (CustomStruct*) blob.data();
...
}
This was tested with pqxx 5.0.1, c++11and postgresSQL 9.5.8
这是用pqxx 5.0.1,c++11和postgresSQL 9.5.8
