[soci-users] RFD: Wrapping UPDATE ... RETURNING support in SOCI?

[Vadim Zeitlin]

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.

[Klemm, Markus]

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 / ***@markusklemm.net )

-----Ursprüngliche Nachricht-----
Von: Vadim Zeitlin [mailto:vz-***@zeitlins.org]
Gesendet: Mittwoch, 10. Juni 2015 14:41
An: soci-***@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.

------------------------------------------------------------------------------

[Asif Lodhi]

Dear Vadim,

IMHO, it should go in the implementation and should not be visible to the
SOCI user. Whether you implement it using templates or virtuals, the
optimization should be automatically selected at compile-time or run-time.
I mean, when DB is DB2 then compiler selects template specialization
related to DB2 and when DB is Oracle then Oracle-specific specialization
gets selected resulting in generation of code doing both steps in a single
trip. If this cannot be done then, IMHO, the feature should be incorporated
as a back-end-specific feature only and should also be documented in those
back-ends' documentation so that the user can take advantage of it when
using those back-ends.

BR / Asif

Post by Vadim Zeitlin
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
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
https://lists.sourceforge.net/lists/listinfo/soci-users