I decided I should probably finally get around to moving this blog from WordPress to Domino, if for no other reason than to have a perfect testbed for the weird stuff I've been doing lately. The first task is to write an importer, so I decided to just do a straight SQL rows -> Domino documents import. This couldn't be easier if you follow this simple guide:

1. Write a Java agent that uses the appropriate JDBC connector for your database. In my case, it's MySQL, so I had it do a "show tables" to get a list of tables, then loop over those to do "select * from whatever" statements to get all documents.
2. Since it's Domino, you can just do doc.replaceItemValue(column, rs.getObject(column))!
3. Oh wait, you can't all the time. Make sure to handle a couple cases, like converting BigIntegers to longs and Timestamps to DateTimes.
4. Wait, Domino doesn't even handle Boolean? For frack's sake, FINE: row.replaceItemValue(column, cell.equals(Boolean.TRUE) ? 1 : 0);
5. Oh hey, that import went really smoothly! Now I'll just make some views based on the table name and... crap. Forgot to include the table name! Maybe I shouldn't have written this importer at four in the morning.
6. Better delete all the documents and start over.
7. What's this, an error when I went to delete all the documents in the All view? "Field is too large (32k) or..." oh no.
8. Oh crap.
9. Crap!
10. Ah, I know - I'll just write an agent to do session.CurrentDatabase.AllDocuments.RemoveAll(True) and that'll fix that.
11. Hmm, nope, that didn't work. Alright, based on the documents created around the same time, I can guess that it's the "option_value" field that's too big. Why did it even cram that data into summary if it completely breaks the document? Well, no point in dealing with that now. It's time for StampAll to take care of that!
12. Nope? Okay, how about session.CurrentDatabase.GetView("All").AllEntries.StampAll?
13. Not all categorized? What kind of error message is THAT?
14. Time to delete the database and start over!
15. Alright, THIS TIME, check to see if the String value is over, let's say, 16000 bytes and, if so, store it in a RichTextItem instead.
16. Oh nice, it worked.
17. Oh crap, I forgot to include the table name again.

And that's all there is to it!

If you spend enough time working with XPages, you're eventually going to want to access some SQL data, either because of an integration project or because you just want to. It's pretty well-trodden ground, but, in one of my recent projects, I came up with an approach I rather like.

Now, before I get into it, fair warning: this is not scalable, efficient, or good programming practice. This is for when you just want to do a quick query and get back data in a usable fashion. It doesn't handle paging properly and it can easily stomp all over separation of concerns. But if your needs are simple, it may do the job perfectly.

Basically, the problem I had was this: I want to do a couple quick SQL "select" queries on an XPage, but I got tired of writing out similar "stmt = conn.prepareStatement(...); stmt.setString(...)" stuff over and over. Ideally, the solution would be to abstract it all away, but it was a small page deserving of a small fix, so I wrote myself a function:

SQLTools = {}
SQLTools.fetchQuery = function(query, args) {
args = args == null ? [] : args


var stmt = SQL.getConnection().prepareStatement(query)
for(var i = 0; i < args.length; i++) {
    stmt.setObject(i+1, args[i])
}
var rs = stmt.executeQuery()

var cols = []
var meta = rs.getMetaData()
for(var i = 0; i < meta.getColumnCount(); i++) {
    cols.push({
        name: meta.getColumnName(i+1),
        type: meta.getColumnType(i+1)
    })
}

var result = []
while(rs.next()) {
    var row = {}
    for(var i = 0; i < cols.length; i++) {
        row[cols[i].name] = rs.getObject(cols[i].name)

    }
    result.push(row)
}
return result

}

The actual job of connecting to the SQL DB with the right driver is handled by a Java bean called SQL - the aforementioned link covers that part pretty well. What makes this code different is how easy it makes a quick query with some interleaved parameters and how useful the resultant value is for standard XPage controls. For example, you could do a search like this...

SQLTools.fetchQuery("select firstname,lastname,degree from people where age > ? and lastname like ?", [30, "%son"])

...and you'd get back an array containing column-keyed hashes. Your XPage doesn't have to care about ResultSets or ".getString()"s or anything. You could end up with relatively clean code like:

<xp:repeat var="person">
<xp:this.value><![CDATA[#{javascript:
SQLTools.fetchQuery("select firstname,lastname,degree from people where age > ? and lastname like ?", [30, "%son"])
}]]></xp:this.value>

<xp:div><xp:text value="#{person.firstname}"/> <xp:text value="#{person.lastname}"/>, <xp:text value="#{person.degree}"/></xp:div>
</xp:repeat>

That's probably not a final product for displaying the information, but it gets the point across - it's a pretty clean traversal from "I want to query the database" to having XPages-friendly Server JavaScript objects.