1

I'm using SOCI to access a PostgreSQL database. One particular table that I'm inserting into and selecting from has (at present) 72 columns. My question is how does one best deal with so many columns?

I've determined that for selecting, using the SOCI dynamic result set is probably best. In this way I can loop over the columns.

However, for inserting I'm having difficulty. What I want to achieve is something as follows:

int vals[NUM_VALS];
statement st = s.prepare << "INSERT INTO table (c0, c1, c2, ...) VALUES (";
for(int i = 0; i < NUM_VALS; ++i)
    st << vals[i];
st << ")";
st.execute();

Is anything like this possible? I've had no luck finding any way of dealing with large numbers of columns in an easy way.

JMW
  • 31
  • 4

1 Answers1

2

The SOCI-users mailing list provided me with the answer. Deferred construction of the statement object is required. For example, to make the above work, change it to:

int vals[NUM_VALS];
auto temp = (s.prepare << "INSERT INTO table (c0, c1, c2, ...) VALUES (:c1, :c2, ...)");
for(int i = 0; i < NUM_VALS; ++i)
    temp , into(vals[i]);
statement st(temp).execute();
JMW
  • 31
  • 4
  • The link to the mailing list post would be appreciated. – Fabian Sep 29 '16 at 06:13
  • Also, you want `use` instead of `into`. Your SQL command requires values to feed into the database while `into` tries to obtain values from the database. Another tip: Feed the temp object into a rowset to easily iterate through the results `rowset rs(temp);` The statement `st` is then hidden within the rowset and is executed automatically. – Fabian Sep 29 '16 at 08:28