insert array (binary data) into a postgres DB with qsql (c++ and qt)

Question

Consider the following interraction with the postgres database:

QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("acidalia");
db.setDatabaseName("customdb");
db.setUserName("mojito");
db.setPassword("J0a1m8");
bool ok = db.open();
QSqlQuery query(db);

QSqlQuery query(db);

QVector<int> byteArray(2); 
byteArray[0] = 0;
byteArray[1] = 7;

QVariant v = QVariant::fromValue(byteArray); 

cout << "dropping a table: " << query.exec("drop table aaa;") << endl; //gives 1
cout << "creating a table: " << query.exec("create table aaa (gid integer, pos integer[])") << endl; // gives 0
query.prepare("INSERT INTO aaa (gid) VALUES (:gid, :pos)");
query.bindValue(0, 1);
query.bindValue(1, v);
cout << "inserting: " << query.exec() << endl; // gives 0 :-(

Of course, one way to do that would be to send the data with a manually built sql statement, and execute the query as a normal query on the server (where the byte array would be inserted as a string), but I am looking for a somewhat nicer solution..

Daniel Vérité · Accepted Answer · 2012-10-25 13:25:48Z

1

The INSERT has 3 destination columns declared but 4 bind values.

query.prepare("INSERT INTO geo (gid, bboxx, bboxy) " "VALUES (:gid, :bboxx, :bboxy, :pos)");

Once the bytea column is added after bboxy, this should work.

answered Oct 25, 2012 at 13:25

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

arthur Over a year ago

thanks for your effort and help. Unfortunately, I was not able to send the data to the server :-( I have updated the code, sot it is easier to reproduce the error, in case you could have a closer look at the problem..

Daniel Vérité Over a year ago

But there is still the problem of mismatched columns/bind values with the current version: INSERT INTO aaa (gid)... should be INSERT INTO aaa (gid,pos) ...

Daniel Vérité Over a year ago

Also the fact that the database type is now int[] and the host type is QVector<int> changes signficantly the problem. I'm not sure that bindValue() can be directly used on a array. If it can't, the typical fallback is to pass the text representation of the array (according to postgresql array syntax) in a text bind value.

arthur Over a year ago

Great. Thanks for letting me know. I'll try to play with the byte array for now. In principal I am saving the spatial information in postgres, which in my case makes my life a lot easier as I know how the data is represented in bytearray :-) Thanks!

Collectives™ on Stack Overflow

insert array (binary data) into a postgres DB with qsql (c++ and qt)

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related