Obstacle 1

Where is the data located?

With a lot of help from Mohave County’s Systems & Database Admin, Gary Waters, this step wasn’t too bad. I realized what I needed was the DataMart schema… but I didn’t quite understand how I was supposed to populate it. Gary kept telling me about this job they have that populates it nightly, but I couldn’t even find (in Oracle Enterprise) where Oracle keeps it’s jobs. I eventually found a query that would list them for me, but we had no such job.

The next day, or thereabouts, Jeff, one of the other Navajo County programmers, noted that he had found a DataMart application and had populated the schema. As he was explaining how to install it (it’s included in the advanced RealWare install), I realized I had seen it there when I was installing RealWare on my machine, duh. Oh well, all in a day’s work, I guess.

With the help of the SQL scripts that Gary sent along, I started to piece together how the data was arranged in the database. On to…

Obstacle 2

How do I get the data from the internal Oracle server to the external SQL server?

After some extensive Google searches, a lot of reading of the (very boring) Microsoft SQL Server Books Online, as well as a healthy helping of the (very complex) Oracle Database Documentation Library, mostly the part about Oracle Streams, I started thinking this wasn’t going to be a walk in the park.

Well, I took a walk through Oracle Enterprise Manager, right clicking some things in database links, trying to see what was necessary to send data directly from there to the MS SQL Server sitting in the DMZ. Well, it turns out, it’s not that easy. I installed Oracle XE on the SQL server (read somewhere that it was the thing to do). Altered some .ora files and tried to replicate the data… and failed to connect. Well, I googled, tweaked, retried… googled, tweaked, retried, tweaked, retried, googled, tweaked, retried… No go.

After beating my head against that rock that is Oracle for a day and cursing CCI for using Oracle (which I do love, btw, it is what i learned SQL on, but replication is so easy in SQL server) the rest of the night, I had an brilliant idea. You see, I did read that you could use a SQL Server as a distributor for a publishing Oracle database. The whole reason I didn’t go that route in the first place is because the SQL Server is in the DMZ, therefore, while the Oracle server should be able to get to it just fine (hah), It wouldn’t be able to get to the Oracle server, at all (without opening holes in the firewall, which my boss is very against). However, we have about a million (okay, closer to 10 or 20) SQL Servers inside the network (Non-DMZ).

Well, I was stoked. I read some more of the Books Online, with renewed vigor and got started on the plan that would should bring data from the inside Oracle server to the DMZ’d SQL Server. Well, I read all the papers and went through all of the necessary steps, installing all of the .NET crap into the Oracle database. Finally, the moment of truth… I setup the distributor, made sure all of the settings were just right… I had successfully connected to the Oracle database and browsed the list of tables from SQL Server Management Studio. It’s go time… So I click Finish and I instantly get the error saying you have to buy the insanely expensive SQL Server Enterprise to use Non-SQL Server publishers, well, it didn’t say that, but that’s what it boiled down to after a quick google search to decipher the greek that it gave me. However, I was on the right trail…

Now, at the beginning of this foray into Obstacle 2, I had talked to Gary Waters about how he got data from their CCI database to the DMZ’d SQL server. They just punched a hole in the firewall and have SQL Server import the data every night. I told you how my boss feels about holes in the firewall… and he really like real-time data, so that was immediately out of the question. I told him that we could either buy SQL Server Enterprise, which was pretty much dismissed out of hand, due to the current financial state that the county is in… or I could do a nightly import (without punching holes in the firewall, mind). I just needed some space on one of our existing internal servers to accomplish this task…

Well, he gave me the space and I went to work. I built an Integration Services package to import the data from the Oracle server to the internal SQL server. Then I setup replication to send the data to the DMZ. Other than a few hiccups with jobs failing and needing to be setup with different logins, it worked perfectly… Next came making the views for the search…

Obstacle 3

How do I convert the parts of Gary’s populate mcit_webdata that I need to SQL server?

Well, most of the views were very straight forward… I just took the scema off of the table names and they worked. However, there were a few functions that caused problems, the wannabe case statement comes to mind, but I can’t remember what the actual clause (function, procedure, command, whatever) was. Anyway, it wasn’t in Books Online (I had found the command reference and was using that extensively by this time), but a quick google search turned up some useful results. That, paired with the command reference, gave me everything I needed to make the views work properly. Only real problem was that the search time didn’t really improve as much as I hoped it would. A problem that could wait, I decided, and wait it did…

Obstacle 4

How do I incorporate personal property into this search?

Until just this week, the property search was just a parcel search and the treasurer has been riding me on getting the personal property (as opposed to real, parcels and improvements) into the search. Well, I can proudly say that I have finally done just that, as well as reducing the search time from 5 - 10 seconds to pull up the data for a parcel property to <1 to 4 seconds, depending on what data is available for said property.

I decided to try to speed up the search by creating a table that will hold all of the data that will be served to the web, statically, as well as a stored procedure to populate this table nightly after the import from the Oracle database. I actually got this idea from Gary’s scripts, too. Well, that did speed things up, but more importantly, it gave me the idea of how to include the personal property in the parcel search.

Every time this was mentioned to me, it seemed like everyone was suggesting I use a radio button type switch to switch between a search for personal and real property. Then I’d likely need to build a couple of new functions or subs into my code behind to handle the personal property search. However, it struck me that there’s not much difference between the data needed on personal property than that of the real property, like I said before, it’s really just a subset of the data needed for the real property. That said, the only real difference I needed to overcome was that personal property didn’t have parcel numbers…

Well, what I ended up doing was using the tax ID number on the personal property in place of the parcel number. The field that I intended to be for the parcel number was altered to accomodate the tax ID number, as well. The search application had to be altered some, I think I added about 40 or 50 lines of code to various functions and sub routines.

While I was altering the code, I made a few changes that I had been meaning to make, using SQL parameters where I’m getting input directly from the user. It provides some protection against SQL injection attacks, but also makes the error messages from mistyped entries more friendly (I already use an account that only has read access to pull the data to protect from SQL injection attacks).

I made a maintenance plan to run the job that imports data from the Oracle database, then runs the stored procedure to repopulate the table for the web search. After that, it rebuilds the indexes and updates the statistics on that table, then replicates everything out to the DMZ. The time it takes to pull property data in the best cases been reduced to less than a second and worst cases was still halved from the best times before the changes.

Now, if we can just make it through this tax season, I think everything will be moving along smoothly again, although it is looking like my workload is going to double… again. I’m thinking it might be time to revamp my resume, now.

Not Found

Error 404

http://www.google.com/chrome

Update: Woohoo, it’s released, and so far, so good. I like it.

UGH!?! Give me Chrome… I need Chrome… If you haven’t heard already (Lord knows how you got here), Google is releasing a browser tomorrow (Tuesday). Google Chrome is set to be a major improvement over current browsers (even Firefox, although it likely won’t take Firefox long to build in the best features).

This is huge, why else would Arrington talk about it not once or even twice, but three times in less than four hours… did I mention it hasn’t even been made available, yet. It’s on just about every other tech blog I read, as well. Anyway, here’s the comic book Google released about it, if you’re interested (it’s kind of long, but a great read if you’re like me). You can also read about it on The Official Google Blog or on Google Blogoscoped.

Now, I know this can’t be intentional, but it’s very annoying, and I can’t see any easy way to make it go away. It looks like this is being caused by some Microsoft style CSS, as the page loads fine in Internet Explorer 7. Firefox 3, however, renders the ad right over the top of other content. I sure hope they get this fixed over at lifehacker. I do like a lot of their articles.

UPDATE: It looks like this is happening all over the web with these annoying “Mojave Experiment” ads. A refresh usually fixes it. I haven’t really looked into the CSS that is creating this issue, but I’m sure it something Microsoft designed for IE and threw in compatibility with Firefox as an afterthought.

Well, I got to work investigating the offending code, and it seems there was a name attribute missing from the input tag for email in comments.php for the finestripes theme. I simply added the attribute name="email" to the necessary input tag and it started working. Now, I guess it’s time to get Akismet up to date.

Sorry, Frank, for misspelling your blog’s name in my last post.

PHP Parse error: syntax error, unexpected T_SL in wp-includes/widgets.php on line 464

After googling, I found a post on futtta’s blog that described the same error. I fixed it the same way as he describes, by downloading the tarball and copying the a fresh copy of widgets.php over my offending copy. Viola, everything works as it should now, cept my search box seems a bit tweaked. I’ll have to check that out after I feed the kids.

My only real peeve about it is that Verizon locked the GPS. Currently, in order to use it, you have to pay $10/month for VZNavigator. However, there is a forum, http://www.everythingq.com/forum/moto-q-hacks/verizon-q9c-gps-hack-21606.html, full of people hard at work trying to open it up for Google Maps and Live Search.

One more difficulty that I just noticed… creating links. Maybe there is a good blog writing tool for Windows Mobile 6 out there somewhere. We’ll see.

We are raffling tickets for this beautiful blanket. Honestly, it is the most beautiful crocheted blanket I have seen (and I’ve seen a lot of crocheted blankets). This is to raise money for my daughter to go to the Cinderella pageant in Laughlin, NV, this June. We are selling the tickets for $2 each or 3 for $5. Please stop me when you see me or send me a message if using the contact page if you would like to buy some. Northeast AZ locals only, please.

The drawing will be held May 4th. Thank you for your support.

In walks DD-WRT… To be completely honest, I’ve been looking at DD-WRT for a while now, and wanted a router to put it on. I was really just watching ebay, but ended up charging a new one to our Dell account. I wasn’t completely surprised that WDS wasn’t on my WRT when it got here, it wasn’t in the feature list, but the latest version of the WRT54G will take the DD-WRT custom firmware, and WDS is on DD-WRT’s feature list. Anyway, I setup a NAT pass-through for my router, got all of my port-forwards, and reserved addresses configured, and even got the built in Dynamic DNS client configured to update my domain names. Unfortunately, the Speedstream only supports WEP when you are using WDS, so I MAC locked the wireless as a second layer of protection.

Psi is a free instant messaging application designed for the Jabber IM network including Google Talk. Fast and lightweight, Psi is fully open-source and compatible with Windows, Linux, and Mac OS X.

I was browsing the site and noticed a lot of features that looked great to me (I’m sort of a feature hound… even for features I’ll never use). Encryption, multi-user chats, and remote client control… could it really be, the exact feature I’m looking for?

Always-on security

Using the same SSL technology that makes it possible for you to safely shop on the web, Psi automatically encrypts its communication with compatible IM servers to provide a secure connection over untrusted networks like public WiFi access points. For advanced security needs, Psi can also encrypt messages end-to-end with OpenPGP.

Leading the way

Psi’s developers are actively involved in the Jabber community, working hard to ensure that Psi fully supports the standards for features like avatars, multi-user chats (with full admin control), remote client control, and more. It’s standards like these that allow people to communicate smoothly with each other on the Jabber network, and Psi ensures that you’ll never miss a beat.

There’s a couple of things I miss from Google Talk, I like the chat display more, being able to search contacts, and having the email button right in the chat window. However, the remote client control makes it worth the switch by itself, not to mention all of the other features built into Psi (adjustable auto away, auto offline, and connect through proxy). To enable remote client control, you’ll need to check the little box in the advanced page of the options.

You can also make it portable (not a huge deal, since all you need to do is log into your inbox in GTalk to do the same thing, but it will give you remote client control).

When searching for the remote sign out for the Google Talk client, I saw quite a few forum posts from people who wanted this feature and were switching to other messaging platforms because of the missed messages and such. I wanted to let everybody know about this great product.

Way to go Psi Team.

I had too much trouble with Microsoft Virtual Server to keep it around after my hard drive change. Almost everytime my computer had an unscheduled reboot (my power went out), it dropped the virtual network card and created a new one which drove opensuse crazy.  I’ve had no such problems with VMware server. It was also a lot easier getting the VMware tools running than it was getting Virtual Machine Additions to work properly. So now my time is syncing and all (my clock was running a little slow before I installed it). Getting the mail setup correctly threw me a little. Opensuse 10.3 uses Postfix by default, and I had only setup sendmail, and my ISP requires authentication for outgoing mail now. I eventually found the necessary instructions. It was actually a lot easier to set up than I thought it would be. I ended up using my ISP’s SMTP server as a relay and SMTP AUTH to get me logged into it.

I finally got enough money to get some upgrades for my computer. I upgraded my memory with two 1 GB sticks of RAM. I also tossed a 200 GB hard drive that I had laying around… bad idea, it crashed two weeks ago, so I convinced my wife to let me get a 500 GB hard drive to replace it. We really do need it since we just got a digital camcorder, and my little 80 GB was filling up with pictures alone. Anyway, all of the updates are installed, I’ve dedicated 256 MB of memory to the virtual server, and everything is running a lot better.

I’ve converted my Wordpress instance to a subversion install, it’s now a lot easier to upgrade when a new version is released. After browsing themes for a few hours I found one, PDawn, that I thought was okay, but it wasn’t widget ready, like it said it was. It’s not a huge issue, in fact, I could make it widget ready myself it I wanted to. I searched some more and found Coulorful Wall, this one was worse than PDawn.

1. It wasn’t widget ready (didn’t claim to be).
2. When I went to a different page, the link background changed on mouse over, but was white again when the page loaded.
3. Used h3’s on the sidebar instead of h2’s (made my widget enabling it less effective).
4. There are some stats on the header image… not live stats, just part of the image… annoying.

After searching a little more, I came across Finestripes… and I loved it the second I saw it. I even liked the default picture in the header, but replaced it with my children, anyway. It wasn’t widget ready, but it wasn’t hard to make it widget ready. It also made the search widget look a little off, but a little hacking fixed it right up. The tag widget was also a little tweaked,  but a couple more hacks in style.css took care of that.

All in all, I’m quite happy with my blog now. I think I’ll leave it alone, and I’ll even try to get blogging a little more often.