' Example script for reading data from Commence using the API ' should work for Cmc 4.1x and higher (but see note about category/fieldnames) ' This is intended to be a real-world example of working with the API ' remove the .txt extension from the filename to make it as a working stand-alone Visual Basic script file ' The best introduction/tutorial on the API to date is the "Of Cursors and Rowsets" document ' which is available from Commence Corp (www.commence.com) or Michael Gauthier's ' Freedomcomputing site, www.freedomcomputing.com ' Notes ' - this assumes a perfect world scenario, that is: no error-trapping is included! ' - this assumes basic understanding of VBScript. ' - In this example, I will use the Commence RM 3.0 'Tutorial' database. ' If you want to test this on another database, you should adjust category- and fieldnames accordingly! ' our goal in this example: ' -read data from a category called "Person/Company" ' -we want to read only 3 fields: "File As*", "Phone" and "E-mail" ' -read the data as fast as we can, trying to minimize system strain '++++++++++ On to the real script! ++++++++++++ Option Explicit 'force explicit variable declaration. Optional but good practice 'First, we declare some constants representing some basic elements of the Commence API Const CMC_CURSOR_CATEGORY = 0 'tell cmc that we want to use a category, not a view, as datasource. Use 1 for a view. Const CMC_FLAG_DEFAULT = 0 'most API functions include a flag, that is usually unused but always required if present Const CMC_MAX_ROWS = 5 'maximum number of rows we want to retrieve each time round. 5 is very few, but be sure to keep this value below say 100 max. Const CMC_DELIM = "<<%\%||%/%>>" 'string to delimit the retrieved data. API will allow 20 chars max, but DDE only will take 8. ' you can use any string you want, but make sure it is unique (i.e., not equal to -say- a fieldname or fieldvalue(!)) 'Next, we will declare some variables Dim objCmc 'we will use this to represent the Commence.DB object ' this is our reference to the database Dim objCursor 'we will use this to represent the Commence Cursor object ' a cursor is like a pointer to the data Dim objRowset 'we will use this to represent the Commence Rowset object ' a rowset holds the actual data represented in the cursor, and is usually a smaller subset of it. Dim strCategory 'category name we are going to read data from Dim arrFieldNames(2) 'array that will hold the fieldnames from which we want to retrieve values. ' Note that a size of 2 allows for 3 fieldnames to be stored in the array since arrays are 0-based unless otherwise specified. Dim nRows 'number of rows in Rowset Dim i 'just a counter Dim j 'just a counter Dim MyRow 'used to put our returned field values in. Dim msg 'store messages in here, that we can bleep out to the user 'populate strCategory, i.e. specify the desired category strCategory = "Person/Company" 'populate our array of fieldnames, i.e. specify the fields we are interested in 'you could just as well use separate variables or constants to contain our fieldnames, but I just happen to like arrays arrFieldNames(0) = "File As*" arrFieldNames(1) = "Phone" arrFieldNames(2) = "E-mail" 'side note: an easier way would be not to declare the array bounds of arrFieldNames in advance, 'and just create a variable, assigning an array to it later. 'another way to do this could be: 'Dim arrFieldNames 'dont even declare it as array. To VBS, anything is a variant anyway, unlike in VBA or VB 'arrFieldNames = Array("fieldname1", "fieldname2", "fieldnameN") 'make it an array and populate it ' --- done with the preparations, on with the works --- 'tell our script we want to talk to Commence 'note that VBS isn't case sensitive, but (for instance) JavaScript and PHP are! 'unconfirmed: Commence may enforce case too. I am not 100% certain of this, I just always use same casing as in Commence to be sure. Set objCmc = CreateObject("Commence.DB") ' --- background information --- ' if u were doing this from within a Commence detail form script, better use this syntax: 'Set objCmc = Application.Database 'It means the same, but there is an advantage: 'Commence will use the current database, even if u have more than 1 open. CreateObject("Commence.DB") will assume last instance opened. 'You'll have to know how Commence detail forms work however. That's not hard at all, but beyond the scope of this example. ' --- end background information --- 'tell Commence what datasource we want to use. In our case, that was the category "Person/Company" stored in strCategory. 'notice that we have to include the flag, even tho it does not do anything at present Set objCursor = objCmc.GetCursor(CMC_CURSOR_CATEGORY, strCategory, CMC_FLAG_DEFAULT) 'we are about to ask the Cursor for a Rowset, which will contain the actual item values 'there are a few points worth mentioning now: '-you will likely want to filter the category (cursor) so as to only work with the items we actually want. ' Now is the time for it (Cursor.SetFilter), but it is beyond the scope of this example. ' SetFilter is an exceedingly hard and counter-intuitive method of the Cursor object, see helpfile for examples. ' Much easier would be to use not a category but a view as datasource. '-one approach now would be to just point to the complete set of items contained in the cursor, and pick from that only those fields we want. ' There is a major problem with that though: ' ALL items in the Cursor will be read into memory, and your computer will probably explode. Kind of. ' the first step therefore will be to limit our request to the fields we are actually interested in. ' You do this by specifying a set of columns (fields). Doing so makes Commence ignore all others ' There are several other ways of accomplishing this, I just like to do it this way. Matter of taste. ' now, the usefulness of putting all fieldnames in array surfaces: you can just loop through it! ' if we would have used separate variables or constants, we would have had to specify our desired columns one by one, ' which would have been especially cumbersome when you intended to also include error trapping ' the assigning of an index (a number representing the column) is done manually, we use variable i for it here. For i = 0 To UBound(arrFieldNames) objCursor.SetColumn i, arrFieldNames(i), CMC_FLAG_DEFAULT Next 'Okay, we have told Commence what fields we want to get data from 'Now, lets get to the data! Erm..wrong! Even though we have specified only a subset of fields to retrieve, 'if there are something like 100.000 items in the category, the resulting set of values will likely be huge still, and your computer will still explode. Kind of. 'We need to retrieve them in small portions at a time, so as to keep memory load small, and speed high. 'What we will do is this: we are going to check how many items (rows) there are in total in our Cursor, and retrieve the rows in small sets of size CMC_MAX_ROWS 'loop through all items in cursor 'note the minus 1 since indexing is 0-based, but counting is not, of course 'the 'Step' makes the 'row-pointer' advance to the next starting point in our cursor (see background info below). We want to read rows 0-4 first, then 5-9, etc. For i = 0 To objCursor.RowCount - 1 Step CMC_MAX_ROWS 'for every loop, get a small subset of items Set objRowset = objCursor.GetQueryRowSet(CMC_MAX_ROWS, CMC_FLAG_DEFAULT) 'get only CMC_MAX_ROWS at a time 'okay, so now we have a rowset. Check it's size. nRows = objRowset.RowCount 'the returned rowset may be smaller than CMC_MAX_ROWS(!) 'a speed issue is up next. 'we have our (small) subset of items by now 'up next is reading actual fieldvalues from it 'we could use GetRowValue for that. 'however, GetRowValue(row, column, flag) requires that we specify what column we want to read the value from 'this is time consuming, since Commence has to search for the specific column in the columnset for each row 'a MUCH faster way is to just grab the whole row, and deal with the returned values ourselves. 'to make life even easier, we will put the returned fieldvalues into an array using VBS's Split function For j = 0 to nRows -1 MyRow = Split(objRowset.GetRow(j, CMC_DELIM, CMC_FLAG_DEFAULT), CMC_DELIM) 'the heart of the entire script, really. See how the delimiter is of use now? 'what we will have ended up with now, is an array MyRow(): 'MyRow(0) will hold the fieldvalue of arrFieldnames(0) ("File As*) 'MyRow(1) will hold the fieldvalue of arrFieldnames(1) ("Phone") 'MyRow(2) will hold the fieldvalue of arrFieldnames(2) ("E-mail") 'ANYTHING YOU WANT TO DO WITH THE DATA GOES HERE (well, not in this example, it only reads data:) ) 'in this example, a QueryRowSet is used. See dbapi.hlp in the (-ProgramDir-) for additional info on AddRowSet, EditRowSet and DeleteRowSet types of rowsets 'fwiw, if u edit items, ModifyRow is a method to look into there. Also note that nothing happens until invoke the Commit method of a rowset. Useful for debugging! 'what I will do here is just display results of each item in a messagebox - be careful when u have lots of items or you will click yourself silly! :-> msg = "Information on category '" & strCategory & "'" & vbCrLf & vbCrLf msg = msg & "The field ''" & arrFieldNames(0) & "' contains " & MyRow(0) & vbCrLf msg = msg & "The field '" & arrFieldNames(1) & "' contains " & MyRow(1) & vbCrLf msg = msg & "The field '" & arrFieldNames(2) & "' contains " & MyRow(2) MsgBox msg, vbInformation, "Results of the small API example" Next 'loop through rowset ' --- some background information --- ' there is something you should know that is not obvious from this code ' a cursor has a 'row pointer'. Think of it as a way of Commence knowing at which 'line', which item, you are. ' when you create a rowset, Commence will automatically move this 'row pointer' forward for you ' to illustrate: remember we get only a few items at a time, right? But the cursor may well contain much more items... ' So how does Commence know which ones to give to us? Enter the row pointer: whenever you create a rowset, ' Commence will advance this internal counter forward. By an amount equal to the size of the requested rowset. ' This way it knows at which row in the cursor we are at, and this is what makes us users get the right rows. ' We ask for a rowset of say 5 rows, so we get rows 0, 1, 2, 3 and 4 that we just processed in the above code. ' When we ask for the next subset of rows, Commence has automatically moved the row pointer forward to row 5, ' and gives us the rows from that point on. Etc etc. ' Why is this important to know? It is important to know because when you want to re-use the cursor later on, ' you will have to move back the row pointer to the desired row, which will most often be the first. ' Syntax for this is .SeekRow ' here, this does not apply, since we will use the cursor only once. ' --- end background information --- Next 'loop through cursor, Commence will 'know' we want the next set of items. See above comment for details. 'clean up any object variables we created. This is optional but good practice Set objCmc = Nothing Set objCursor = Nothing Set objRowset = nothing 'that's it! I hope it was useful WScript.Quit 0 'get me out of here!