And the fact of being able to handle in a simple and effective manner this class in a form's code or in a program, allows us to control with precision the whole behaviour of an updatable cursor and carry that code, from one place to the other, with no need to "meddle" with the database, something we cannot avoid if we are dealing with views.
When I realized the above, I began to use cursor adapters extensively in my applications, especially in the creation of relatively complex data entry screens, such as is the case with invoice making.
And when something becomes repetitive and its use is frequent, the ideal thing is to have an automatic means of creation and reuse.
For this reason I decided to create a class for generating an object that would allow me to build cursor adapters on the fly, sending but a few parameters and letting the object handle all necessary tasks for the generation of the cursor adapter I wanted.
This object is built from a class, which I called "dataobj", residing inside a class library called "myclasses.vcx".
What is needed to create a cursor adapter?
Even though it is necessary to initialize several properties to model the behaviour of a cursor generated by this class, we have to send at least the following data as a minimum, as parameters, so that the cursor adapter works.
With the above elements, the class should have the necessary intelligence as to return the cursor we want.
Example of use of the data handling object which generates a cursor adapter
** we instantiate the class and the oData object is generated ** we save the class in our class library, which should be within scope of the form ** the class is called "dataobj" ** we put the class in the general scope of our application, so it is available all the ** time, when we need it. We do this in the main program of our applicationset classlib ** to myclasses.vcx additive set classlib to myclasses.vcx additive
if vartype(thisform.oData) = "O" removeproperty(thisform,"oData") endif thisform.AddProperty("oData") thisform.oData = NewObject("dataobj") ** and when we need to generate the cursor, we use the following code in an adequate ** method of the form Local oHeader oHeader = CreateObject("custom") use in Select("theheader") Try ** the adapter method in our class generates the cursor this.oData.adapter( @oHeader,; 'header',; 'theheader',; .t.,; && updatable cursor .t.) && empty cursor (no records returned) Catch To oException messagebox("Error message: " + oException.message) Endtry
The class
We need to instantiate the cursor adapter class and the object thus obtained should have its own separate name. This object is the "container" of the cursor, built from the properties and methods contained in the "CursorAdapter" class.
And we have to bear in mind that for each updatable cursor adapter we have to create a different object. In the invoice making with cursor adapters example published in this magazine in the March 2006 issue, I mentioned this. An object was built for the header of the invoice, another one for the details table, another one for handling the money tendered by the customer, yet another one for the stock movements, etc.
This is so because each cursor adapter is the result of one unique instantiation of the "CursorAdapter" class. This class generates one single cursor in each one of its instances. Therefore, if we need more than one updatable cursor, originated from different SQL select statements, we must instantiate a similar quantity of objects from the class.
It is necessary to pass to the object the names of the tables involved in the select SQL statement and, in addition, the name of the cursor to build.
If we are only generating a cursor from a single table and we want the cursor to be empty or to contain all records. it is not necessary to pass the SQL statement. We must put a method inside our object, so it builds the SQL statement by itself.
In the same way and because it is absolutely mandatory that the "cursorAdapter" class knows what is the primary key of the table from which the cursor will be generated, we shall have to create a method to obtain such primary key.
Let us therefore see the fundamental methods making up this class. (As every class should have builder and destroyer methods and a data handling method, I am including such methods without explanation, because they should be self explanatory to the reader. In addition, the class should have methods to open and close tables, also not explained here because they are very elementary)
DEFINE CLASS DATAOBJ AS CUSTOM cSch = "" cUnl = "" cUfl = "" cKeyField = "" cTheAlias = "" cTable = "" lOK = .T. cSavingTable = "" PROCEDURE INIT Set Multilocks On this.opendatabase() && include a method to open the database ENDPROC PROCEDURE DESTROY this.closealltables() && method for closing tables this.CloseDataBase() && method for closing the database ENDPROC PROCEDURE ERROR (place your own error handling code here) ENDPROC PROCEDURE ADAPTER Lparameters toObject,tcTable,tcAlias,tlUpdatable,tlEmpty,tcSelectCmd,tcTableList Local cSchema,cUfl,cUnl,lOK,cSelectCmd lOK = .T. this.cKeyField = This.getkeyfield( tcTable,tcAlias ) && gets the name of the key field * try to generate the cursor schema, analyzing the tables as per the SQL statement Try If Vartype(tcSelectCmd) = "C" && use the Select statement if passed as a parameter cSelectCmd = tcSelectCmd This.MakeSchema(tcTable,cSelectCmd,tcAlias) else This.OpenTable(tcTable,tcAlias) && open the table and use an autogenerated select This.MakeSchema(tcTable,'',tcAlias) endif Catch To oException lOK = .F. messagebox("Cursor "+tcAlias+" could not be generated"+Chr(13)+oException.Message) Endtry If lOK = .F. Return Endif cUfl = This.cUfl && updatable field list cUnl = This.cUnl && update name list cSchema = This.cSch && table schema This.CloseTable(tcAlias) && close the cursor before building the CA lOK = .T. Try If Vartype( This.toObject ) = "O" Removeproperty(This,"toObject") Endif This.AddProperty("toObject") This.toObject = Createobject("cursoradapter") Catch To oErrors Messagebox("CursorAdapter object not generated"+; Chr(13)+oErrors.Message,16,"Attention",2000) lOK = .F. Endtry If lOK = .F. Return Endif If lOK = .T. Try If Vartype(tcSelectCmd) <> "C" && if we do not pass our own select statement If tlEmpty = .T. ** build an empty cursor cSelectCmd = [select * from ] + tcTable + [ where 1 = 0 ] Else ** bring all records cSelectCmd = [select * from ] + tcTable Endif Else cSelectCmd = tcSelectCmd && use the select statement passed as a parameter endif With This.toObject .DataSourceType = "NATIVE" .Alias = tcAlias If Vartype(tcListaTablas) = "C" and Len(Alltrim(tcTableList)) > 0 .Tables = tcTableList else .Tables = tcTable endif .BufferModeOverride = 5 .KeyFieldList = this.cKeyField .SendUpdates = tlUpdatable .SelectCmd = cSelectCmd .UpdatableFieldList = cUfl .UpdateNameList = cUnl .CursorSchema = cSchema .CursorFill() Endwith Select ( tcAlias ) Catch To oErrors Messagebox("Cursor "+tcAlias+" was not generated"+Chr(13)+oErrors.Message; ,16,"Attention",3000) lOK = .F. Endtry Endif Return lOK ENDPROC PROCEDURE MAKESCHEMA Lparameters tcTable,tcSelectCmd,tcAlias ** builds the schema, the updatablefieldlist and the updatenamelist Local cUnl,cSch,cUfl,cCmd Local array aNC(1,1) cSch = "" && schema cUnl = "" && updatenamelist cUfl = "" && updatablefieldlist If Len(Alltrim(tcSelectCmd)) > 0 && if Select-SQL is passed cCmd = tcSelectCmd + [ into cursor ] + tcAlias try &cCmd Catch to oError this.mensaje("Incorrect SQL statement") Endtry ** build the list of updatable fields from a temporary cursor ** generated with the SQL sentence we passed to it Select * from (tcTable) into cursor temporario where 1=0 Select temporario AFields( aTemp,'temporario') Use in Select("temporario") For i = 1 to Alen(aTemp)/18 cUnl = cUnl + aTemp(i,1)+" "+JustStem(tcTable)+"."+aTemp(i,1)+"," cUfl = cUfl + aTemp(i,1)+"," EndFor ** the schema is built from the CURSOR, NOT FROM THE TABLE Select (tcAlias) =Afields(aNC,tcAlias) && build an array with all the fields from the CURSOR For i = 1 to Alen(aNC)/18 cSch = cSch + aNC(i,1)+" "+aNC(i,2) Do case Case InList(aNC(i,2),"C","D","L","M","I","T") cSch = cSch + "(" +Transform(aNC(i,3)) +")" Case aNC(i,2) = "N" cSch = cSch + "(" +Transform(aNC(i,3))+","+Transform(aNC(i,4)) +")" EndCase cSch = cSch + "," EndFor else Select (tcAlias) =Afields(aNC,tcAlias) && build an array with all fields from the table For i = 1 to Alen(aNC)/18 cSch = cSch + aNC(i,1)+" "+aNC(i,2) cUnl = cUnl + aNC(i,1)+" "+JustStem(tcTable)+"."+aNC(i,1)+"," cUfl = cUfl + aNC(i,1)+"," Do case Case InList(aNC(i,2),"C","D","L","M","I","T") cSch = cSch + "(" +Transform(aNC(i,3)) +")" Case aNC(i,2) = "N" cSch = cSch + "(" +Transform(aNC(i,3))+","+Transform(aNC(i,4)) +")" EndCase cSch = cSch + "," EndFor endif ** remove the comma remaining at the end this.cSch = Substr(cSch,1,Len(Alltrim(cSch))-1) && schema this.cUnl = Substr(cUnl,1,Len(Alltrim(cUnl))-1) && updatenamelist this.cUfl = Substr(cUfl,1,Len(Alltrim(cUfl))-1) && updatablefieldlist ENDPROC PROCEDURE GETKEYFIELD Lparameters tcTable,tcAlias Local cKeyField this.opentable (tcTable,tcAlias) Select (tcAlias) For i = 1 to Tagcount() If Primary(i) or Candidate(i) cKeyField = Tag(i) exit endif EndFor this.closetable (tcAlias) Return cKeyField ENDPROC PROCEDURE COMMIT Lparameters tcAlias ** uses tableupdate to commit the cursor adapters Local cAlias dimension aErrorArray(1) cAlias = tcAlias Select (cAlias) this.lOK = TableUpdate(1,.t.,(cAlias)) If this.lOK = .f. =aError(aErrorArray) && Data from most recent error messagebox(aErrorArray(2)+Chr(13)+; "Error Nbr: "+ Transform(aErrorArray(1))+Chr(13)+; "Table: "+this.cAlias +Chr(13)+Chr(13)+; "Not saved" ) EndIf Return this.lOK ENDPROC ENDDEFINE
Analyze the code shown
The first thing to notice is that a series of parameters is being passed, (some of which are optional), that are fundamental for the functioning of the class. The optional parameters are the SQL statement and the list of tables within that statement.
If a SQL statement is not passed, the method generates its own, but only using the main table. This is very convenient when we want to build cursor adapters from a single table, to use in data entry forms for that table only.
Then we see the intention to obtain the key field, by means of a call to the GetKeyField method.The table is opened briefly, a scanning is made through all the tags and the primary key is found out, returning its name and closing the table immediately.
The next step is to generate the schema, by calling the MakeSchema method.
The end result is that this method will generate three properties which are necessary to obtain an updatable cursor:
UpdateNameList, UpdatableFieldList and Schema
If a SQL statement is not passed as a parameter, this method uses the table passed as tcTable and the alias, coming in through the tcAlias parameter. It builds a SQL statement containing all the fields from the table and, by a series of iterations it builds the strings with the appropriate data, which end up in the cUNL, cUFL and cSchema properties.
If, on the contrary, a special SQL statement is passed, adding fields not existing in the main table, as for instance in the following code snippet:
local cSelectCmd cSelectCmd = [select customers.*, provinces.provname from customers ] cSelectCmd = cSelectCmd + [ left outer join provinces on customers.provcode = provinces.code]
The buffermodeoverride property is set to 5 by default (optimistic table buffering). But the reader can choose to change this value with a special parameter. In my own experience, and following the opinion of several authors, there are no noticeable benefits from using some other buffering setting. And the value of 5 allows for the updating of many records with only one tableupdate command.
Let us now update the base table data
So far all the code analyzed allows us to build a cursor adapter. In order to end this article, we shall see how data is saved to disk. Please remember that one of the advantages of using cursor adapters is that they generate updatable cursors.This means that any change we make to the cursor will be reflected in the original table, after we issue the tableupdate() function.
The code in the Commit() method shows how to do that. We simply issue the tableupdate function on the cursor and, if the update is successful, the data are committed to disk. However, if the update fails, we catch the error in the aErrorArray array and we'll know what went wrong.
Conclusion
With this class we can build cursor adapters on the fly, with no need to write special code every time we need one. In situations in which we have to update several tables this becomes particularly important. With this class, we only send a few parameters and the cursors are built almost by themselves. I hope this small contribution makes life easier to the VFP community.
