By Kevin Marshall on Aug 20 2008
Awhile back I wrote about the Perl DBI and MS SQL text fields and according to google analytics, it was a fairly popular post.
Today I was reminded just why that is - dealing with LOB type fields is a pain in the butt!
This time around, I just needed to get some XML back out of an Oracle table for additional processing. For whatever reason, Oracle makes this (in my opinion) much harder than it really needs to be. And to make things worse, a quick google search did not reveal any simple, or complete, solutions!
So, after searching around for awhlie without finding a solid, simple solution I decided I should just bite the bullet and update my MS SQL example to also work with Oracle.
The biggest advantage to this approach is that it's (more or less) driver independant. Meaning, that you don't really have to mess around with all kinds of driver specific features or options like ora_lob_read!
So without further ado, here's some working code as an example:
$dbh->{'LongTruncOk'} = 1;
$dbh->{'LongReadLen'} = 255;
my $sth = $dbh->prepare('SELECT id, length(xml_string) dlength, xml_string FROM mytable') or die;
$sth->execute() or die;
my $xml = "";
while (my @data = $sth->fetchrow_array()) {
my $xml = "";
if ($data[1] > 255) {
my $offset = 1;
while ($offset < $data[1]) {
my $newoffset;
if ($offset + 255 < $data[1]) { $newoffset = 255; } else { $newoffset = ($data[1] - $offset) + 1; }
my $query = 'SELECT SUBSTR(xml_string, ' . $offset . ', ' . $newoffset . ') AS txt FROM mytable where id = ' . $data[0];
my $st1 = $dbh->prepare($query) or die "problems";
$st1->execute() or die;
while (my @d1 = $st1->fetchrow_array()) {
$xml .= $d1[0];
}
$offset += 255;
}
} else {
$xml .= $data[2];
}
print $xml . "nn";
}
Don't forget you still need your db connection stuff before all of this (and you will need to require whatever database library you are using -- when testing this I was using DBD::Oracle). Also don't forget that this solution is not paticularly effecient (so try to limit it in production situations).
Still, it works (at least for what I needed) and I think it's simple enough. Hopefully it can help save a few other people some time (and headaches) too.
Comments: 0
By Kevin Marshall on Aug 19 2008
As I write this I'm 34 and I've been getting paid to program since I was 19. In the grand scheme of things, that hasn't been that long...but I've been lucky enough to be around and on the scene as the internet more or less exploded onto the scene (hence keeping most of my bills paid over the years).
Anyway over even this short period of time, I've seen a few cycles come and go...for a little while it's all about centralized networks, then it's all about distributed networks...it just keep flipping and flopping. The latest being cloud computing (centralized) vs. peer to peer computing (distributed)...
But the cycles aren't just about centralized vs. distributed...they seem to be in just about everything...and the one that's been of special interest to me lately is features/freedom vs. constraints.
There was a time, not too long ago, that the race was all about adding more and more features...doing more than the other guys...giving the users more freedom to do whatever they wanted, however they wanted, whenever they wanted...that time seems to have passed.
Today's world seems to be all about intelligent constraints...limiting your features to just the core things that should be important to your users. Doing less, but doing it much much better.
Now I know that people will argue that the freedom and options to limitless features are not gone, they have just been shifted to third party developers via application programming interfaces (APIs)...and while I agree that providing an API allows for near limitless things to be done, associated, and derived from your service, I don't think it's quite the same thing.
For starters, only developers really have any way of actually doing anything with your API (so the general public doesn't really get those options or features to with as they please). Second, even if millions of people build zillions of services on top of your APIs, they are not (usually) part of your service...and so they are not (usually) directly integrated into your offering. This means to really use all the zillions of features, a user has to visit those millions of sites/services built on top of your API (I know, I know facebook is different, but they are sort of the exception here).
Anyway, my point is that the current trend is away from limitless features and options and towards more controlled and constrained offerings.
And this cycle seems to be expanding beyond our end-user applications...we are seeing the same cycle taking place in our frameworks and design patterns behind the scenes. Thanks in part to the popularity of things like Rails, Hibernate, Django, and the likes.
On the one hand, I love this trend, because it really does make it easier for people to get started and actually 'do something'...but at the same time I sort of hate it because there are so many people getting into things that they really have no knowledge (or interest) about.
So anyone off the street can build a pretty cool app in about a week (maybe less depending on the framework they pick)...but it's only going to live for so long and be stretched so far before they are going to need to understand just what's really under the hood (and what can be yanked out and customized). I'm not completely convinced someone off the street is really going to deal with that stuff well when it's time (at least not most of them)...
But I guess that's another argument for another day...and I've already been all over the place on this post, so I'll leave it at that for now!
Comments: 1
By Kevin Marshall on Aug 18 2008
Today has been one of those days when the clock just seems to refuse to move. I think it's probably related to my lack of motivation or interest in working today.
Whatever the case, it seems like I'm frozen in time today...sort of bored out of my mind and just waiting for something interesting to do or to distract me...but it didn't really come and so I just ended up mostly watching the clock tick tick tick.
This is a problem I have from time to time...even with all the various projects I'm actively involved in, I hit these periods of boredom...it's not that I don't have anything to do, it's that I don't want to do anything I have on my list at that moment.
While I was watching the clock tick away, I did manage to get some work in for myidentifiers.com (the project I've been working on for Bowker during the days), some small Draftwizard clean up, and some reviews.com stuff...I also poured through the usual blogs and NFL preseason news...and I even managed to get on the treadmill for a little while tonight...
Yet the clock continues to move slow...I'm winding down the night watching some UFC footage on Spike...this usually gets my adrenaline flowing, but tonight they happen to be replaying a fight I just watched a few nights ago (and so while I'm still entertained, it's not quite as exciting as it usually would be for me).
Anyway - it is what it is today.
Comments: 0
By Kevin Marshall on Aug 14 2008
Last nights tangent back into the Ruby world got me motivated to look a little deeper into my Ruby closet. One of the tidbits I found was this little script I hacked together last fall.
It's also somewhat related to my tiny little script from last night, but in this case I'm using the mechanize gem instead of simple screen scraping with the net/http library I mentioned last night...why you ask? Well in this particular case, the data I want to get at is behind a login, and I need to keep that session alive to get at the data I want...so mechanize just makes that simple.
OK so what does this script actually do and why did I build it? To answer that I have to first give you a little background.
Last season I just missed the playoffs in one of my fantasy football leagues, and as is usually the case when that happens, I wanted to gripe and complain. But being the 'fantasy football expert' of the league (simply because I'm in a magazine or two), I knew it wouldn't be good enough to just say "If I had your schedule or if I was in your division, I would have walked into the playoffs backwards and blindfolded"...I needed to have some hard data before I shared my gripes with the league.
So this script was built to swap my team with any other specified team in the league to see how I would have fared...the idea being that we would all have had the same rosters and started the same people for a given week regardless of what our actual schedules were (this isn't really the case, but I think it's a fair enough assumption for making my argument).
With all of that in mind, here's the simple script I threw together (with username/passwords changed to protect the guilty of course):
# program to compare schedules on commissioneronline (if I played another team's schedule)
# 1. log into commissioneronline.com
# 2. get the details for each team
# 3. change around to see results
require 'rubygems'
require 'mechanize'
agent = WWW::Mechanize.new
page = agent.get('http://www.commissioneronline.com')
form = page.form('frmLogonFF')
form.fields.find {|f| f.name == 'logon'}.value = 'mycommissionerusername'
form.fields.find {|f| f.name == 'password'}.value = 'mycommissionerpassword'
page = agent.submit(form)
forteam, againstteam = {}, {}
12.times do |t|
team = t + 1
puts "checking team #{team}"
forteam["#{team}"] = []
againstteam["#{team}"] = []
followed = false
page.links.each do |link|
if link.href == "../League/tmHome.asp?tid=#{team}" && followed == false
followed = true
teamdet = agent.click link
show = false
teamdet.search("//td").each do |td|
if td.to_s.downcase.include?("@home") && !td.to_s.downcase.include?("bodytext")
show = true
end
if show
if td.to_s.downcase.include?(" ") && td.to_s.downcase.include?(" - ")
line = td.to_s
startspot = line.index(" ") + 6
endspot = line.index(" ", startspot)
temp = line[startspot, endspot - startspot]
if temp != nil
dashspot = temp.index(" - ")
yours = temp[0, dashspot]
theirs = temp[dashspot + 3, temp.length - dashspot]
forteam["#{team}"].push(yours.to_f)
againstteam["#{team}"].push(theirs.to_f)
end
end
end
end
end
end
end
# now I have all the for and against values for every team; so I can do the swap out
comp1 = ARGV[0]
comp2 = ARGV[1]
team1 = []
count, wins, loss = 0, 0, 0
forteam[comp1].each do |points|
# for each score they got, see how they would have matched against other team
if points == againstteam[comp2][count]
# this is the week they played each other; so keep result
if points > forteam[comp2][count]
team1.push("win #{points} - #{forteam[comp2][count]}")
wins += 1
else
team1.push("loss #{points} - #{forteam[comp2][count]}")
loss += 1
end
elsif points > againstteam[comp2][count]
# they would have won this game
team1.push("win #{points} - #{againstteam[comp2][count]}")
wins += 1
elsif points < againstteam[comp2][count]
# they would have lost this game
team1.push("loss #{points} - #{againstteam[comp2][count]}")
loss += 1
end
# see how you would have matched against their opp
end
puts "team #{comp1} would have #{wins} wins and #{loss} losses if they played team #{comp2} schedule."
Since it was purely for supporting (or debunking) my own little gripes I didn't want to spend a lot of time putting this together -- I wrote and tested it all in about an hour last year. And of course I didn't comment it or get as fancy with the design or features as I might if I thought I would be reusing or expanding it (or even sharing it like this -- guess that's a lesson learned!).
In the end, it worked well enough for what I wanted...and in case you are curious, it did support my thought that had I played most other schedules in the league, I would have made the playoffs...but at the same time, it also showed me that had I played the same schedule as the guy who actually won my division played, I wouldn't have been anywhere near the playoffs -- and had he played my schedule he would have been undefeated! Yikes. Of course I've kept that bit of information to myself until now (I guess time does heal some wounds...well that and it's just about time to draft again this year and so I can finally start forgetting about last year and looking towards this one to avenge myself).
Comments: 0
By Kevin Marshall on Aug 13 2008
I've got a few post ideas that have been floating around in my head for the past few weeks. But by the time I get around to posting for the night, my motivation has been all but wiped out and my thoughts are pretty much a jumble...so once again I'm going to delay posting about any of those thoughts and dump out something a little different.
With my work at Bowker I've been spending most of my development time in PHP, with a little here and there in Perl. My development work with reviews.com keeps me mostly in Coldfusion and Java. And my own projects like fubnub, botfu, and draftwizard are primarily being done in PHP and Perl right now too. What all of this means is that I really haven't been doing much of anything in Ruby as of late.
That's a bit of shame really because I love working in Ruby (and it happens to be the only language I've had material published for so far - so you would think I would be doing tons in Ruby)...anyway, since I've been missing Ruby tonight I thought I would dump out a quick and easy Ruby code sample to get at least a little flavor around here tonight:
require "net/http"
feedurl = 'http://blog.botfu.com/?feed=rss2'
pagedata = Net::HTTP.get_response(URI.parse(feedurl))
puts pagedata.body
This little bit of code simply grabs the RSS feed of this very blog and dumps it out to the screen (so yes, it's a bit of generic screen scrape code). Nothing too fancy, but it's actually a VERY powerful bit of code that I use in a lot of quick hacks...and best of all it's only a line or two of actual code.
Anyway, I just thought I would share that little tidbit tonight. I'm going to be cleaning up some of my back end processing code for the Draftwizard challenges (and other things), and so I'll probably dip back into Ruby then (some of it's already in Ruby, and some is in Java -- I think I would prefer to move it all to Ruby over time).
So what's your favorite back end processing language of de' jour?
Comments: 0
|
Search All Posts »
Latest Comments »
Kyle Brady wrote » "I sort of hate it because there are so many people getting into things that they really have no knowledge ... read more »
Kevin Marshall wrote » I get them from drawshop.com ... the funny thing is that you are ... read more »
Kyle Brady wrote » 1) Ok. Just a thought.
2) I was just giving you a hard time! haha I'm not a designer either, but ... read more »
Kevin Marshall wrote » Hey thanks - couple of quick feedback points:
1. Traditional, mass market magazines are dying, but ... read more »
Kyle Brady wrote » I checked out your magazine thing. I think you're dabbling in a dying medium, and it'd be better to ... read more »
Blog Details »
This blog now includes 189 wonderfully exciting posts from 1 unique and very special writer!
Archives by Category »
(22) Code »
(4) ColdFusion »
(11) Database »
(8) Factor »
(190) General »
(9) JavaScript »
(11) Perl »
(12) PHP »
(15) Ruby »
Archives by Month »
December 2007 »
January 2008 »
February 2008 »
March 2008 »
April 2008 »
May 2008 »
June 2008 »
July 2008 »
August 2008 »
Sites you gotta visit »
bar.ackoba.ma
BotFu.com
Draftwizard.com
Fubnub.com
Reviews.com
StoryRank.com
Kevin Marshall - Who's that?
I'm just your basic programmer. I can't spell to save my life, I'm not the greatest story teller, and I often ramble on about nothing. This blog showcases all of that!
Believe it or not I wrote a book (Pro Active Record) for APress and a PDF (Web Services with Rails) for O'Reilly.
If you're bored drop me an email at info at botfu.com or view my outdated resume.
|
