I have been working with databases for nearly 30 years (wow, should I admit that?).  I’m a longtime user and developer for the DotNetNuke (DNN) framework. One of the most common problems I’m asked to solve for people, is how to access data in databases nearly always outside of DotNetNuke. Early on I experimented with a variety of DNN add-ons, and even wrote my own custom modules that achieve that functionality. Eventually however, I came across a tool called ListX from r2i, and more recently, their next-generation product called Open Web Studio (OWS).

These tools seem to be able to do nearly anything I’d need to do with DNN.  I want to use them.  They claim OWS is straightforward and easy to use.  I didn’t always agree, but I did find it powerful and useful.  I prefer to think of OWS as a wonderful monster.

I tried OWS when it first came out without much luck.  Recently however, I came across another project that seemed appropriate for a web-based tool, and OWS seemed like the best tool for the job.  So off I was to take another run at reconciliation in my love/hate relationship with r2i products.

I needed to make a simple set of maintenance screens for a small database table. The table only has two columns and I need to make a way for a user here in-house to insert edit, delete and view records in the table.  The company I have worked for, for the last 6 months, has used Clarion for all programming ever since Clarion was the latest and greatest piece of software.  Needless to say it is now old and outdated.  It is hard to find help.  It is quite powerful at building simple data maintenance apps like the one I needed now.  I have been an advocate for moving forward and trying some of the new technology.   Management is open to technologies other than Clarion if it is easy to accomplish tasks and maintain them.  I have created a DNN-based intranet site with some data reporting and analysis tools using ListX.  I even created my own handler to provide inline sparklines right in the datat table.  For this new project, I needed to allow a user to edit a table in our production database, while the DNN database is already set in the intranet SQL server.

Since it has always been a challenging task for me to develop using ListX, I decided I would document the process of using OWS and all the twists and turns it takes.  I thought it would be easy to do that on Twitter and maybe someone would be interested to see the process.  Once I started, a few people said they were indeed interested.  In the past I have encouraged people to use ListX but that the “learning cliff” was quite steep.  I heard the “learning cliff” in reference to OWS and it seems to fit.  I had watched the first of r2i’s OWS training videos when they had first come out, and also the DNN Creative tutorial videos using the OWS QuickBuilder that made it seem…. (oh boy, I have to say it) straightforward and easy.

With the DNN Creative video’s on one of my two screens and IE connected to my DNN intranet site on the other and TweetDeck in the background I attacked my problem and tweeted at each step as I paused the video.  See my tweet stream (or may I suggest stweam).  The project ended unsuccessfully.  I am still attempting to make that DNN OWS module work for my application.  But I may have to take an hour off and make a useful tool using (ugh) Clarion in the mean time.

The thing that prompted this blog entry was the response I got from the guys at r2i via Twitter in my stweam.  When I started having issues I looked to see if the guys from r2i might be following.  I remembered Kevin Schreiner’s name, so I searched for it and found him and invited him to follow #owsfirtstry.  (Nice spelling huh?  Yet another example of why my company is named Brillnat)

My first real issue came when I tried to put in a connection string to my database and then create the simplest of all queries to the db as shown in the video.  The video did not show an example using the connection string to a remote database, but it only used data in the DNN database.  Since I had used a custom connection string before in ListX and the connection string input box was present in OWS, it didn’t seem like it should be a problem. 

Unfortunately, it became a BIG problem because OWS gave me a completely unhelpful error message, something like “query failed try again”.  The error message was misleading.  Kevin from r2i eventually chimed in.  

He said:  “the #ows interface and capability differs from the ListX one in the ability to execute sql directly.”

I was not sure how that related to what I was doing.  I didn’t know what his point was. 

Next he said:  “You cannot use a connstr in the admin because it wouldn’t be very secure. You can do that as a saved action, just not from the adm” 

I got from that, that I could not do what I was trying to do.  But in classic r2i fashion he used some terms that I wasn’t quite sure what they were.  “Admin” or “adm” and “saved action”.  Part of my confusion was probably due to the fact that in twitter it is difficult to see exactly what tweet he was responding to, and the need to cram info into 140 chars.  I had to make some assumptions.  He also suggested a solution but it wouldn’t work for me since my db was on a completely different server.  But even before Kevin offered some help I had devised a plan of attack to get around the obfuscated and un-useful connection string box in QuickBuilder. 

Kevin did admit: “now reading this thread makes me realize how pointless that is in your current scenario”

He also said it was in the works to fix it.  That was somewhat encouraging.  My app was generated using what I felt was a clever trick to create my table structure in the dnn db.  I would let QuickBuilder generate the app then go back into OWS and change the queries to use my remote db.  I was pretty sure that ListX and OWS both would be able to access the remote db.  I felt it was probably just a QuickBuilder problem. 

I had some real work to do at that point and when I came back to the OWS project I made changes to the queries but I couldn’t publish the changes.  It gave another obfuscated and un-useful error message.  I figured out, as Kevin pointed out, later that my login had timed out.  Not a big problem, and partly my fault, but the error message kept me trying to fix a problem that did NOT exist.  Not straightforward and easy.  I got my queries changed and it was reading my remote DB.  Yippee!!!!.   But it appeared QuickBuilder didn’t give me a workable app at that point.  There were issues.  Buttons didn’t work or were non existent.  So now I was into the guts of ListX and OWS programming.  They say it is straightforward and easy.  IT IS NOT.  The system is complex and powerful but NOT straightforward and easy.  The inmates are definitely running that asylum.  I went home for the night tired and beaten. 

That night I went online and saw that this time Chris Chodnicki from r2i had now tweeted in…

He said “have you gone tru the training videos? this is one of the most basic things OWS performs”

This really pissed me off.

I read that as more “straightforward and easy to use” talk.  It felt like he was saying “are you stupid or just plain dumb?”.  At this point I was feeling a little of both.  But then I have issues. To be fair though, I should make sure there wasn’t something obvious I had missed.  Remembermy OWS stweam was based on videos on DNN Creative.  Those talked about using QuickBuilder.  I had seen some videos on the OWS site when they first came out. Was he talking about those?  I wandered thru the OWS site's maze of info.  I found some intro training videos.  I think they called them the newbie series.  I watched them again.  They showed how straightforward and easy to use” the QuickBuilder was.  However, they did not use a remote db connection.  In the video it appeared to be using a slightly different version than mine.  I did not quite get the same results.  Their app included an add button and while mine did seem to have an add action I did not get a button and could not find the code for a button.  

Chris also said “are you also using the OWS forums / ticket area? There are examples/lot of other users there that can also assist.” 

I went there.  I found a post that was very closely related to problems I was experiencing that was posted on 3/5/2009.  Over a 100 people had viewed it. But there were no replies to it.  No answer.  That gave me no confidence that there were in fact “users there that can also assist”. ( I can't find that post now.)

Chris later sent me an email with info from a tech guy that explained how to add a connection string to my web.config and use a variable that points back to the web.config in the connection string boxes.  I don’t believe that to be necessary as I had already achieved getting info from the remote DB. Could it really be that it would only work using a connection string in some places and not others?

I decided to try and follow the OWS video to use QuickBuilder since it was slightly different from the DNN Creative one.  The OWS way enabled jQuery transitions.  I thought it might be cool to try those anyway.  I followed, exactly, the video making an app that worked against my table in the dnn database.  I then went and changed all the connection string boxes to have my connection string.  Now it talked to my remote DB.  But there were still issues.  Some buttons appeared to do nothing.  Add didn’t quite work.  Similar stuff with cancel, edit…  Nearly every function had some issue with it.  But it did seem close.

I thought maybe debugging would help explain the strange behavior.  I had enabled debugging for super users in the general tab. I was logged in as host so that shouldn’t be a problem.  I was expecting the debugging to appear in the module like ListX.   It didn’t in OWS.  I went to the OWS wiki.  I could not find anything useful that told me about debugging.  I did find a video example about debugging.  Shouldn’t the wiki explain debugging?  I feel that video examples are nice and helpful but a full explanation in the documentation is required for it to be straightforward and easy to use.  Videos that show how to do something are helpful.  I also need explanation about all the possibilities, and how things work.  I can’t decide if the OWS and ListX documentation is:

a. Crap (it doesn’t sound so bad when you say it with a Scottish accent, “Crrrap”)
b. Confusing
c. Totally useless
d. Incomplete
e. All of the above

I will be gracious and choose D.  I have hope that it will get better.  The negativity I feel towards all things r2i is the blind insistence that their products are “Straightforward and easy to use” when they are obviously not.  I may have to vent more about that later.  Maybe be I'll even blog about my spakline handler and my stweam caching.  For now I have work to do.