Wednesday, April 25, 2007

Unicode error with TurboGears and Oracle

Thanks to kap_ravi's comment, I tracked down a surprisingly big and poorly-documented stumbling block for folks using TG with Oracle.

I've updated the original TG+Oracle blog post to reflect it, but the gist of it is that you're likely to get
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
type unicode
errors unless you include
sqlalchemy.convert_unicode = True
in your .cfg files.

Configuring Oracle for proper Unicode use may avoid the need for this; I'm not sure. My dreadfully mononational working environment has prevented me from developing any Unicode experience.

Monday, April 23, 2007

good errors vs. bad errors

One of the things I love most about PostgreSQL is its beautiful error messages. For instance, let's say I've inexplicably forgotten to add my home state to a table of states, thus violating a foreign key constraint when I try to insert a Minnesotan city. Here it is in Oracle:

SQL> insert into city values ('International Falls','MN');
insert into city values ('International Falls','MN')
ERROR at line 1:
ORA-02291: integrity constraint (MYSCHEMA.FK_CITY_STATE) violated - parent key not found

OK, that doesn't suck. In this case, it's pretty easy to figure out the problem, especially since I gave my constraint a meaningful name. However, there are cases where the foreign key is not so obvious - maybe it involves multiple columns - and I'm sometimes sent off to look up details on what FK_CITY_STATE involves. Since DESCRIBE won't work on a constraint, that means either an intricate query joining several V$ tables, or a graphical tool like Toad or SQL Developer.

Now I'll make the same mistake in psql.

mydb=# insert into city values (2,'International Falls','MN');
ERROR: insert or update on table "city" violates foreign key constraint "fk_city_state"
DETAIL: Key (state)=(MN) is not present in table "state".

Now that's clarity! It's one of the things I love about PostgreSQL. After all, the database has all this detail easily accessible to it; why shouldn't it deliver it to me along with the error message? It's even more important when an error like this is thrown from somewhere deep inside some code, where it may not be obvious to me which statement or which row is responsible for it - having the offending value displayed is incredibly convenient. It's considerate programming - no more, no less.

I'll say this for Oracle, though - Oracle's standardized ABC-12345 error codes are really handy for looking them up, for searches on Metalink or Google.

In any case, either of these SQL environments have good error reporting. But what about Oracle Enterprise Manager?

Where am I supposed to go with this? Shall we go search on Metalink for "An error has occurred"? Does someone at Oracle imagine that this message is, in some mysterious way, useful? Maybe there's some logfile somewhere on the server that will tell me more... maybe... somewhere. Maybe. Somewhere.

Seriously, folks. OEM has been around for a long time now. It still throws lots of errors, often for no apparent fault of the DBA (I got this one simply by trying to access the "Administration" tab.) But I would forgive throwing lots of errors, if the errors were clear, and if they included information to facilitate finding out more. This, however, I cannot forgive, and it is all too typical of my experiences with OEM. (Well, a few years back, the errors tended to be screenfuls of Java error-stack vomit; now most of those seem to have been "cleaned up" into these poker-faced non-messages. That's progress? It's like Windows 3.1 all over again.)

So, if you see me rolling my eyes next time an Oracle rep shows a slide telling us how wonderful lovely OEM makes DBAs' jobs so much easier... this is why.

Friday, April 20, 2007

Hello Penguicon

I'm having a fantastic time at Penguicon so far...

except, of course, for being unable to connect my laptop to the projector for my talk. Aaaaaaaaaah!

If you were in my audience, 1) thank you for bearing with me, 2) I hope you managed to get something out of it, and 3) I'm so glad you came here!

The full tarball of talk material is available here. You can use it to look through the plain-text version of the talk (independently available here), or install vpython on your system and run the demos (I recommend running 'python', then looking at the code), or actually install Bruce on your system so you can run ' pyIntro.soc' and see the presentation the way you should have seen it at the con.

Thanks for being a great, involved - not to mention forgiving - audience!

Thursday, April 12, 2007

Excel reports from TurboGears

Last week, I had the chance to design, build, and deploy a TurboGears application to outside customers for the first time ever. It was awesome! It was a simple survey form - well, simple in concept, but various questions were dependent in various ways on the answers to other questions, so I did have to get into the JavaScript, praise be to MochiKit.

The application owner wanted to see his survey results in (hmm, can you guess?) MS Excel. "No problem", I said, and then was surprised at how much trouble I had figuring out how to do that straight from TG. AFAIK, TurboGears doesn't have a handy way to output CSV. I could certainly produce a webpage with CSV data, and even use the default method in my controller to serve it with a .CSV extension ("http://myserver/report.csv"), but it still had webpage-type headers and thus Excel still didn't know what to do with it. And no, I wasn't about to tell him to cut-and-paste.

What I eventually hit on was to provide the results as a simple HTML table, then take advantage of the fact that Excel can open a webpage that contains (only) a table rather nicely. Then I gave him a batchfile that simply said

"c:\Program Files\Microsoft Office\Office11\excel.exe" http://myserver/report

... double-clicking it gets a live view of the data.

If he'd been cooler, the batchfile could just as well have said
"c:\Program Files\ 2.2\program\scalc.exe" http://myserver/report
- I tried that, too, it works.

Actually, I didn't serve it under http://myserver/report, I served it under https://myserver/reportWithHideousGUIDblahblah4242rtfm22hike. That was my quick-and-dirty way to provide some basic security - the report URL is unguessable, so unless he shares the link, it should be safe. On the other hand, anybody who can sniff his request can extract the magic URL and use it. Can anybody comment on how much of a risk that is? There's no real sensitivity to the data in this case, but it would be nice to know if this the-URL-is-the-password scheme is a worthwhile shortcut or is terribly dumb.

Tuesday, April 03, 2007

Presenting at Penguicon

Well, two weeks from Friday, I'll be presenting on Python in Michigan... wearing a Starfleet uniform, if the vendor area opens in time.

Yes, I'll be at Penguicon. It's an open-source convention - it's an SF convention - it's a dessert topping! Troy, MI, April 20-22. I've never been there before, and I expect to enjoy it. The schedule included two higher-level Python talks, so it seemed to me like somebody should introduce Python so newbies could get the background they need to absorb the higher-level stuff. Wish me luck!
Introduction to Python, April 20 7:30 - 8:30 PM, Catherine Devlin

Django the Python framework for rapid web app development, April 21 9:30 - 10:30 AM, Dan Scott

Patterns of Data Access implemented in SQLAlchemy, April 21 6:00 - 7:00 PM, Mark Ramm
I also hope to make it to
PostgreSQL & Explaining Explain, April 20 10:00 - 11:00 PM, Aaron Thul
and to sit on the Constructed Languages panel (I'm an Esperantist)... and not to sleep, apparently.

My presentation will be more or less a repeat of the one I gave at Dayton Perl Mongers - it went well there, and the planetary theme should be even more appropriate for Penguicon. In fact, now that I've got a good talk to give, I'm starting to look around for any old place, any old audience in the area I can introduce Python to. Suggestions are welcome! is gaining membership pretty nicely, but we're still going to need more to turn it into a really thriving in-person group. Hopefully I can lure a bunch of new people into the joys of Python.

Monday, April 02, 2007

Feeling the love from Oracle

The Oracle Technology Network has rolled out two new features deeply gratifying to the PyOraGeek...Thank you, OTN! This attention is important for more than just the benefits to those of us already hooked on Python. Oracle's interest carries a lot of weight; when Oracle embraced Linux several years ago, it was a big step forward in establishing Linux's credibility in the corporate world. I think we can expect a boost, too.