Import from SQL to NSF: It's So Easy!

Apr 6, 2012, 7:57 PM

Tags: domino sql

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!

Commenter Photo

Dan SIckles - Apr 6, 2012, 12:59 PM

Only four craps! Last time I tried something like that it was at least five craps.

Commenter Photo

Patrick Sawyer - Apr 8, 2012, 2:01 PM

Why didn't you try to use TDI for that? Curious if there as a limitation that prevented you from using it.

New Comment