title: h2: "data conversion error" on array returned from stored procedure
body: <p>This is a followup post to <a href="https://stackoverflow.com/questions/11658251/stored-procedure-returns-an-array-how-to-use-in-sql#comment15451220_11658251">this post</a>.  I am writing an accounting system backed by an h2 database. The tree of accounts is stored in the ACCOUNTS table, with the PARENT_ID column storing the links in the tree.</p>
  
  <p>To get the path to a given node in the tree, I have the following stored procedure:</p>
  
  <pre><code>public static Long[] getAncestorPKs(Long id)
  </code></pre>
  
  <p>whose job is to produce an array of integers, being the PARENT_ID values between the given node and the root of the tree.  Let's imagine it is defined like this (because I have tried this and I get the same error):</p>
  
  <pre><code>public static Long[] getAncestorPKs(Long id)
  {
    return new Long[]{new Long(1), new Long(2), new Long(3)};
  }
  </code></pre>
  
  <p>It is properly registered in the database and I can call it from within a SQL query.  My problem is that h2 seems to be unable to deal with the return value: if I use it like this:</p>
  
  <pre><code>SELECT ID FROM ACCOUNTS WHERE ID IN (ANCESTOR_PKS(5))
  </code></pre>
  
  <p>then I get the following error:</p>
  
  <pre><code>Data conversion error converting "(1, 2, 3)"; SQL statement:
  SELECT ID FROM ACCOUNTS WHERE ID IN (ANCESTOR_PKS(5)) [22018-167]
  </code></pre>
  
  <p>If, instead, I send the following to the database:</p>
  
  <pre><code>SELECT ID FROM ACCOUNTS WHERE ID IN (1, 2, 3)
  </code></pre>
  
  <p>I get back a result set with 3 rows, containing the three integers (exactly what I expect).</p>
  
  <p>I really can't see what is the problem here!  I am returning an array of Longs, which are to be used in comparing against a column which contains BIGINTS.  Why is h2 refusing to convert this array?  I have tried making the return value be Object[], because the h2 documentation is not entirely clear whether this is required on the return side as well as on the call side, but that makes no difference at all.  I'm just banging my head against a brick wall here.  This ain't rocket science!  Surely someone has written similar code before?</p>
  
  <p>Many thanks in advance, before I go mad!</p>