It is well known that Valentina offers to developer a very reach ADK API (about 1000 methods) in contrast to poor API of most dbs (e.g. near to 25 in mySQL).
One of nice feature is the ability to use VCursor class methods to MODIFY records of ServerSide & ReadWrite cursor. But for years we did have limit here: cursor can be ReadWrite only for single table SELECT. In the release 4.5 we remove this limitation! Now you can do UpdateRecord() and DropRecord() for cursors built over INNER JOIN of N tables!
WHY this is so great feature?
Lets compare work with Valentina’s cursor and mySQL (the same Postgre, SqlLight, …).
Let you want do some SELECT, show records to user in window, user want modify some fields of a record and UPDATE that, but still see records in the window.
mySQL steps are:
* recordset = db.query( “SELECT * FROM T WHERE …” ) // this is read only recordset.
* display records of recordset.
* when user edit fields and click UPDATE button, your application must produce a new SQL command
“UPDATE TABLE T SET f1 = x1 WHERE id = k1”
and execute this command to mySQL server. Note, that to produce this UPDATE command, your application should do string concatenations, DB SERVER later should do string parsing to understand command.
* Okay db is updated, but your recordset not yet. So you must destroy existed recordset and execute the same SELECT command as on the firs step above to see changes.
Valentina steps are:
* curs = db.query( “SELECT * FROM T WHERE …”, kServerSide, kReadWrite )
* display records of recordset.
* when user edit fields and click UPDATE button, your application just do
curs.Field(f1) = EditField_X.String
curs.UpdateRecord()
Thats all. No need to spend CPU time to build string on the client side and no need to spend the server CPU time to parse that SQL command string, because Valentina just send by protocol a small command [kUpdate, f1, x1]. And no need destroy cursor and spend time to execute SELECT again.
Really, smarter, faster, smaller! Cool? 🙂
And now in 4.5 release we enable this feature for cursors built on INNER JOINs! Although with some limits, but they are natural:
- VCursor.AddRecord() cannot be used, because result of this action is not obvious. And fits to rule: “not change existed links”.
- Vcursor.DeleteRecord() cannot be used, because e.g. for 1:M table, how we can delete record One? It can have yet child records. And again this operation fits to rule: “not change existed links”.
You can use VCursor.UpdateRecord() and VCursor.DropRecord() methods.
Note, that cursor cannot be ReadWrite for the following cases:
- OUTER JOIN adds NULLs into rows of cursor, so it is not so easy do Update.
- SELECTs that produce temporary tables, e.g. DISTINCT, GROUP BY, …
