Hi Vadim, I think the best is a consistent API. I do not know how far SOCI offers a way to retrieve values from an insert statement? For me this use case is pretty similar, when I need the assigned UID from a fresh inserted row. (But I did that the last time with raw ODBC and INSERT ... OUTPUT INSERTED.key ) Otherwise it's just optimization and should be a decision by the backend I think.
Mit freundlichen Grüßen / Best regards Markus Klemm (Superlokkus / mar...@markusklemm.net ) -----Ursprüngliche Nachricht----- Von: Vadim Zeitlin [mailto:vz-s...@zeitlins.org] Gesendet: Mittwoch, 10. Juni 2015 14:41 An: soci-users@lists.sourceforge.net Betreff: [soci-users] RFD: Wrapping UPDATE ... RETURNING support in SOCI? Hello, Several RDBMS provide a way to update a row[0] and retrieve the values from the same row in a single query. This is much better than issuing one UPDATE and one SELECT if only because you save a round-trip to the database server, which can carry a pretty significant cost when using a remote database. The trouble is that the way they do it is not the same, I've tried summarizing what I could find in the respective manuals at https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Write_queries/Update_returning and, as you can see, while all rows in this table are similar, no two of them are identical (DB2 and Oracle use the same syntax but I'm not sure about Oracle semantics, I'll have to test whether it returns the old or new values). So I'm thinking about wrapping support for this functionality in SOCI to make it simpler to use. The questions are: 0. Do you agree that this would be useful? Or, IOW, does anybody object to including this in SOCI? 1. What form should the API take? I am thinking of adding statement::add_returning_clause(expressions, parameters) but I'm not sure if I like it very much. Any better suggestions? 2. What to do for the backends that don't support this (MySQL, SQLite, ODBC)? I'm tempted to just return false from add_returning_clause() to let people handle fallback in their own code but this feels a little like a cop out. OTOH silently implementing this as UPDATE+SELECT doesn't seem like a good idea neither. 3. Last but not least: does anybody here have any experience using this SQL construct? Any hints/things to look out for? Thanks in advance, VZ [0] In some of them this works even for multiple rows, but let's keep things simple for now. ------------------------------------------------------------------------------ _______________________________________________ soci-users mailing list soci-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/soci-users
