On Mon, 2011-09-26 at 14:46 +0200, Benoît Minisini wrote: > > On Mon, 2011-09-26 at 14:03 +0200, Benoît Minisini wrote: > > > This feature is missing, mainly because there is no standard way to do > > > that in SQL, and I don't know if there is a function for that in all SQL > > > DBMS. > > > > I was afraid of that. I'll do some research. I know it is possible in > > postgresql and I think it can be made generic across all inserts. As much > > as I hate delving in to the twisted entrails of mysql documentation I will > > endeavour... I don't have much expertise with firebird but there is a > > learning opportunity, I suppose. With sqlite, a quick look seems there is > > some probability. I doubt that it is possible with ODBC. > > > > I'll get back to you. > > Bruce > > > > Firebird being not GPL, it has been removed from Gambas. So don't worry with > it. But I'd like to have the information for the others! >
Research results so far: 1) I expanded the frame of my original quest to see whether it is possible to implement a "with re-read" option for both INSERT and UPDATE queries. This would allow an automatic synchronisation of the gambas result with the true database row when the row has been updated with serial, default or computed values at the database level. The theory being that it would work regardless of whether the primary key columns were affected or not. Given the beaut way gambas works with updateable results, I think that the optimal approach would be to have an optional boolean parameter in Result.Update (i.e. Result.Update([WithReread=FALSE]) that would provide the feature but not break existing code. Alas, my C skills are not up to a point where I can try this out on my local trunk copy! Any guidance here would be appreciated. 2) Assuming the above, the internal workings of Result.Update would vary according to the dbms. Sometimes, returning the updated result is available directly from the dbms, other times it would have to be simulated via a subsequent SELECT. Further notes below. 2.1) The problem breaks down into two cases a) where the primary key columns are all provided (by the calling program) in the Result, and b) where some or all of the primary key fields are serial,default or computed. In the first case the solution is trivial, the subsequent SELECT has all it needs to query the updated row and thus refresh the Result object. The second case is more complex. However I believe it can be accomplished depending on the dbms. 2.2) For postgresql (since 8.x) the solution apears trivial as postgresql provides INSERT ... RETURNING and UPDATE ... RETURNING i.e. it can return some or all of the columns after the insert or update has completed. Further, this appears to be well protected from any concurrency or other side effects. 2.3) For MySQL (since ?.?) there is possibly a solution as there is a protected SELECT LAST_INSERT_ID(); that will return the last "autoincrement" for the most recently executed INSERT on a table containing an autoincrement column. By protected, I mean that it works on a per connection basis, thus avoiding concurrency side effects. Now since MySQL will only allow one autoincrement column per table, if the column is in the primary key then the above approach will work using a subsequent select on the resultant fully populated key. HOWEVER, there is still an unresolved issue if the db supplied key columns are not an autoincrement, say the key contains a db generated timestamp column. I am still looking into this. 2.4) For Sqlite the situation is similar to MySQL but simpler in some ways. It provides "SELECT last_insert_rowid()" to retrieve serial pkeys. I'm still looking at this as a) I'm not sure yet how far Sqlite goes in terms of the other computed column value types and b) it appears from some web pages that the version of this function in Sqlite2 has some bugs that may never have been fixed. 2.5) For ODBC, I have doubts. I'd have fewer doubts if I could actually get an ODBC driver to work at all on this machine. 2.6) Interestingly, Firebird (since 2.0) does support INSERT ... RETURNING and UPDATE ... RETURNING. Research contnues, but I'd appreciate any thoughts. Bruce ------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user