Andy Lester

Technology, careers, life and being happy

Solr’s DataImportHandler can’t handle line-based SQL comments

| 0 comments

At least twice now I’ve run into this problem where I try to comment my SQL code, but doing so makes my Solr data importer blow up.  I post it here for posterity.

Part of your DIH configuration will be at least one entity, probably with SQL code like this:

<entity name="nodes" dataSource="jdbc""
    query="
        SELECT
            foo,
            bar
        FROM blah_blah
    ">

And maybe part of the SQL query isn’t obvious, so you want to add a comment like

<entity name="nodes" dataSource="jdbc""
    query="
        SELECT
            foo, -- We need the foo so we can fribble the wibbitz
            bar
        FROM blah_blah
    ">

But that blows up because the DIH strips linefeeds from your SQL code before passing it to the server.  This means that the SQL code you’re passing looks like this:

SELECT foo, -- We need the foo so we can fribble the wibbitz bar FROM blah_blah

Your line-based comment has wiped out the rest of your SQL query.  So what you have to do is use C-style comments

<entity name="nodes" dataSource="jdbc""
    query="
        SELECT
            foo, /* We need the foo so we can fribble the wibbitz */
            bar
        FROM blah_blah
    ">

Chances are your database supports C-style comments, according to this post on StackOverflow:

C style comments are standard in SQL 2003 and SQL 2008 (but not in SQL 1999 or before). The following DBMS all support C style comments:

  • Informix
  • PostgreSQL
  • MySQL
  • Oracle
  • DB2
  • Sybase
  • Ingres
  • Microsoft SQL Server
  • SQLite (3.7.2 and later)

That is not every possible DBMS, but it is more or less every major SQL DBMS.

Leave a Reply

Required fields are marked *.