Using @DbCommand to query and manipulate simple rich text
I am updating a short series of posts I started last February showing how to think of @Midas Formulas as taking what you already know and extending it. In the past couple of days, I have posted about Using @DbColumn to access Rich Text Tables
, followed by Using @DbLookup to access Rich Text Tables
. Those posts should be read before this article, which builds on the lessons learned there.Querying a Rich Text construct
You already know how to use @DbColumn and @DbLookup to query Notes view data, and with the past two articles, you also know how to use @DbColumn and @DbLookup to query Notes rich text table data. Querying other rich text constructs is not much different or more difficult. The @DbCommand syntax is used for all other queries and manipulation, so you had better get used to it.Getting Started
As a simple example, let's get the Section title from the first section in a rich text field. We need to query the rich text, but instead of columns of data, we need to get the title from a section. So, let's see how we get from what we already know to what we want to know. As a reminder, here is the syntax from the @DbLookup used with @Midas:@DbColumn(class : "NoCache" ; server : database ; doc-selection ; field ; key; columnNumber [; properties] )
The @DbCommand adds a new parameter before the server : database
pair, which is the thing we want to query (or, later, to do):@DbCommand(class : "NoCache" ; function-name; server : database ; doc-selection ; field)
or frequently:@DbCommand(class : "NoCache" ; function-name; server : database ; doc-selection ; field : chunkdef)
"Now, hold on a second!", I can practically hear you say. "What is that chunkdef
thing stuck on to the field?"
That, my dear friend, is a chunk definition, and it defines the essence of how the Midas Rich Text engine works. But unfortunately, I don't have time to describe it right now, see if you are not familiar with the concept , stop and take a quick peek at my earlier post, Midas 101 - Chunk Definitions
. I'll wait.
(sound of tapping fingers)
OK, now that you are back, let's go on...
We want the section title, so the function name is "SectionTitle". (We'll rate that a 10 for clarity, and a 0 for originality). A simple example would be:title := @DbCommand("Midas":"NoCache"; "SectionTitle"; ""; @DocumentUniqueID; "Body":"Section 1");
We added the "SectionTitle" in to tell what we were looking for, and the chunkdef "Section 1" to tell it we wanted to query the first section. Same database parameter, same note-selection and same field-selection as in the @DbColumn and @DbLookup. In fact, you could use the chunkdef argument with both @DbColumn or @DbLookup, which otherwise default to "Table 1".
So, this is cool. By using what we knew before, we can now access a whole slew of new "property-functions", as they are known in the Help. To name a very few: "ActionFormula" for the formula part of a formula hotspot, "LinkURL" for the URL part of a URL link hotspot, "CellColor" for a cell's background color, "GraphicHeight" and "GraphicWidth" for the dimensions of a graphic, and many more.
"What do you mean, many more?", you say. "How will I know what they are if you don't list them?"
Read the Help file! Yup, these are all listed in the on-line Midas Help file, even though the @Midas Formulas toolkit is still in beta. Take a look for yourself.Beyond queries
This is all cool, but what if you don't like the section title you get back? What if the person who created it is one of those annoying sorts who LEAVE THE CAPS KEY PRESSED ALL THE TIME. Ouch! Let's say we would prefer it propercased. Here's all we do:title := @DbCommand("Midas":"NoCache"; "SectionTitle"; ""; @DocumentUniqueID; "Body":"Section 1")
;@DbCommand("Midas":"NoCache"; "SectionTitle"; ""; @DocumentUniqueID; "Body":"Section 1"; @ProperCase(title))
That's right. To set a value, you just use the same property-function, but give it a new value. Not too difficult, even for you newbies!Conclusion
If you were able to understand anything I said in the first two articles in this series, you shouldn't have much difficulty moving on to querying and setting values other than table values. Just add a property-function name and query or set the value. That's all there is.Coming Soon
Tomorrow, or when I get to it,, I'll talk about how you can go one step forward, and query and modify more complex rich text, generate HTML from your rich text, and other things liable to make you very popular with your IT director.
Copyright © 2004 Genii Software Ltd.