Wednesday, January 30, 2008

ARC: Did You Know #1

Inspired by Sara Ford’s “Did You Know” series on Visual Studio, I thought it would be fun do do the same with Advantage. The majority of these will focus on the Advantage Data Architect (ARC), but I will sometimes cover other parts of the product as well.

Did you know that ARC can automatically generate INSERT statements for all rows in a table? The functionality is hidden in the "Tables To Code” form right now. In the future I hope to expose it via the existing export functionality or the automatic SQL generation you can access by right-clicking on any object in the Connection Repository.

For now, if you want a batch of INSERT statements generated for a table click on the Tools -> “Export Table Structures as Code” menu item.

Click on “Add Table(s)” and select one or more tables.

Choose SQL as the export type. When you do this, the “Include Existing Data” checkbox will become active. Check this box to generate not only the CREATE TABLE statement, but also an INSERT statement for every row in the table.

If the table is large, you can specify an output file, which will be more efficient than just having ARC build the output in memory and display it on the screen.

Tuesday, January 29, 2008

A Database Trigger That Maintains an RSS Feed

Since my opening post was about how much I’m addicted to RSS, I thought it would be fitting if my first Advantage-specific post was related. In this post I will talk a bit about a trigger I wrote that updates an RSS feed automatically. At the end I will include a link to a zip file with the trigger source.


This trigger is used in a bug tracking database. R&D engineers and support engineers subscribe to the feed to stay up to date with known bugs in the product. Readers obviously won’t memorize every entry, but this helps facilitate that, “Hey, I think I remember a bug similar to this.” moment that can often be helpful. It can also be used to replace e-mail notifications when new bugs are entered into a tracking system. RSS is a much friendlier way to deliver these notifications, as it can be read at the consumer’s leisure, as opposed to interrupting them or littering their inbox as e-mail notifications would.


Triggers can be fun to write because they can often add useful functionality to an application with zero changes to the actual application itself. This particular trigger is defined as an AFTER INSERT trigger on the base bug table. It takes a few details from the new row, including the bug ID and description, and writes them to an xml file.


This particular example is a trigger DLL written in Delphi. The first order of business is to extract the information we want to include in the feed from the __new table.
oQuery.SQL.Text := 'SELECT * FROM __new';
oQuery.Open;
strID := oQuery.FieldByName( 'bugid' ).AsString;
strTitle := oQuery.FieldByName( 'title' ).AsString;
strDesc := 'Bug #' + strID + #13 + #10 + #13 + #10 +
      oQuery.FieldByName( 'Description' ).AsString +
      #13 + #10 + #13 + #10;
oQuery.Close;

The next block of code reads a few settings from a settings table in the database. The main purpose of the settings table is to avoid hard coding the values inside the trigger.

The settings table looks something like this:

PropertyValue
rss_feedc:\path\to\myfeed.xml
rss_feed_itemtemplatec:\path\to\myfeedtemplate.xml
rss_feed_linkhttp://myserver/somesite/buginfo.php?BugID=

The first two rows include paths to the feed file (myfeed.xml) and the feed template file (myfeedtemplate.xml). The feed template file contains the xml shell to generate a single new feed item. The final property, rss_feed_link, is used to embed a link readers can click on that will take them to a web page where they can view more details about the particular feed item.


And the code that reads the settings table looks like this:

oQuery.SQL.Text := 'select * from settings';
oQuery.Open;
if not oQuery.Locate( 'property', 'rss_feed', [loCaseInsensitive] ) then
// no feed configured, just exit
exit;
strFeedFile := oQuery.FieldbyName('value').AsString;
if not oQuery.Locate( 'property', 'rss_feed_link', [loCaseInsensitive] ) then
// no feed link configured, just exit
exit;
strFeedLink := oQuery.FieldbyName('value').AsString + strID;
if not oQuery.Locate( 'property', 'rss_feed_item_template', [loCaseInsensitive] ) then
// no feed link configured, just exit
exit;
strFeedItem := oQuery.FieldbyName('value').AsString;
oQuery.Close;


Finally, a utility function is called to actually add a new feed item to the file.

AddFeedItem( strFeedItem,
         strFeedFile,
         strTitle,
         strFeedLink,
         strDesc );

The base feed file includes an identifier that can easily be located, in this case it is the comment -- next item here --. The AddFeedItem function constructs a valid feed item and replaces -- next item here -- with the new feed item template, which also includes the comment at the end, facilitating the next insertion sometime in the future.


Verifying Your Feed

I use the following site to verify a feed after testing. This is particularly important when generating the xml in code, as we aren’t using any third party libraries, and there is certainly the potential of generating some malformed xml:

http://www.feedvalidator.org/


Performance Note

It should be noted that this trigger is not suited for a table that frequently undergoes a lot of concurrent insert operations, or batches of inserts. It streams the feed file in each time it is executed, and the reading and writing of the feed file has to be synchronous. The last thing you would want is 100 users waiting for synchronous access to the feed file.


Exercises Left to the Reader

This trigger was written in haste with a “good enough for government work” mentality. In other words, I was fairly lazy but this gets the job done. As such, there are certainly some issues that would need to be addressed if you wanted it to be bullet proof.

One that comes to mind off the top of my head is if absolutely every new row needs to be in the feed, you would need to address the code that bails out if it can’t get a deny write lock on the feed file. In its current state, the trigger just gives up after a few tries and exits.

You may also want to limit the number if items you put in the feed. When you reach a specific number of feeds you may want to delete an old item for every new item you add.


The Source Code

The trigger source code, a base feed file, and a feed item template can be found here.

Wednesday, January 23, 2008

Book Review – The Pirate’s Dilemma by Matt Mason

I attended the Business of Software Conference last year and had the pleasure of watching Matt Mason give a presentation. You can view it here. The presentation, while not directly related to software piracy (although he does his best to incorporate it), is thought provoking and very interesting. Matt is a talented individual.

When his book The Pirate’s Dilemma came out I bought a copy. The book is full of examples of piracy from punk rock to patent law, graffiti, the recording industry, pharmaceuticals and more.

I felt like there was more story telling going on than actually linking the stories together to make a point, but the truth is the stories were all very entertaining and most could stand on their own quite well. Still, I felt a bit lost as I jumped from story to story, wondering if things were going to come together in the final chapter.

The final chapter didn't really provide the "Ah Ha!" wrap-up I was hoping for, but the book is a quick read, has a lot of fun stories, and helps you view all forms of piracy from a different perspective.

On my book review scale (1=skip it, 2=bookmobile, 3=buy it) I give this book a 2.

The next book on my night stand is from two authors who obviously couldn’t agree on a book title: Hard Facts, Dangerous Half-Truths & Total Nonsense, Profiting from Evidence-Based Management

On Standby: The Illusions of Entrepreneurship

Tuesday, January 22, 2008

Why Blogs are not as Lame as they Sound

I’ll never forget the first time I heard the term blog. How lame. An online journal of some sort? Nerds pouring out their feelings and daily activities to strangers on the web; not my scene. I truly didn’t get it, and didn’t spend any time investigating why I would be interested in blogs.

That must have been some time around 2003. About a year later I was reading the newsgroup of a company we work closely with and was shocked to see one of the engineers mention an upcoming change that was going to require quite a bit of work on my part in order for Advantage to be compliant. I jumped into the conversation and it went something like this:

“How can you be changing this and you didn’t tell your partners until just now? No e-mail, no communication of any sort? This is ridiculous.”

“I’ve been talking about this change in my blog for months”, was the reply.

“I’m very busy. I don’t have time to go and check the blog or website of every vendor I interact with, that would be a full time job in and of itself.”

“Use an RSS reader”

This is where I had to stop and do some research. I knew syndication was a popular way to get various news items to web portals, but I had no idea what an RSS reader or an RSS aggregator was.

Of course, I would find out an RSS reader is the cornerstone to what makes blogs tick, and actually downright addictive. The technical blogs I now subscribe to via RSS feeds are my number one source of technical information. With printed magazines going the way of the buffalo, technical blogs are an excellent way to learn new technologies, stay up to date with companies and products you are interested in and discover tips and tricks you would otherwise never be exposed to. RSS feeds are not limited to blogs either. Practically any published content can benefit from having an RSS feed, and tons of web sites provide feeds for news content, updates to their downloads sections, etc.

If you already use an RSS reader, this post is of little interest. I wouldn’t be writing it except for the fact that I run into so many people that still don’t have a reader set up, and are completely missing out on what I think is an amazing wealth of knowledge and something that truly helps developers be more productive.

In fact, I wouldn’t have started my own blob if not for the fact that I have learned so much from others that I feel the need to try to contribute and start that same sort of knowledge sharing in the domain I work in and with the community I interface with (that’s you, whoever you are).

If you are new to technical blogs and don’t know where to start it is relatively easy. You subscribe to one or two feeds that interest you, and eventually those authors will reference someone who interests them. You follow that link and subscribe to the referenced author for a while and see if you like his/her posts. If so, you stay subscribed and eventually that referenced author will reference someone else. Rinse, lather, and repeat. Soon you will be monitoring 100 feeds, but the beauty is you will spend no time whatsoever hunting for content, it will all be brought to you and presented only when there are new items to read. Brilliant.

There are a ton of readers to choose from. I use Google Reader because I like to view my feeds from home, from work, and from my phone. I’ve used Omea Reader in the past, and many on my team use RSS Bandit. A web search for “RSS Reader” should turn up lots of choices.

In the future I’ll post a few of my favorite feeds to get you started.