db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: "Conglomerate could not be created"?
Date Fri, 01 Jun 2012 13:36:47 GMT
John English <checkpoint.je@gmail.com> writes:

> I'm having trouble with the following error: "Conglomerate could not be
> created". It happens when I do this:
>
>   INSERT INTO resource_usage (resid,itemid,itemtype)
>     (SELECT resid,?,? FROM resource_usage
>      WHERE itemid=?
>      AND   itemtype=?
>      AND   NOT EXISTS (SELECT resid FROM resource_usage
>                        WHERE itemid=? AND itemtype=?));

I think Derby doesn't allow untyped parameters in the select list. For
example, this fails:

ij> prepare ps as 'select ? from sysibm.sysdummy1';
ERROR 42X34: There is a ? parameter in the select list.  This is not allowed.

Whereas this works:

ij> prepare ps as 'select cast(? as int) from sysibm.sysdummy1';
ij> execute ps using 'values 1';
1          
-----------
1          

1 row selected

Does the insert statement work if you rewrite it to
  INSERT INTO resource_usage (resid,itemid,itemtype)
    (SELECT resid, CAST(? AS INT), CAST(? AS VARCHAR(32672))
     FROM resource_usage
     WHERE itemid=?
     AND   itemtype=?
     AND   NOT EXISTS (SELECT resid FROM resource_usage
                       WHERE itemid=? AND itemtype=?));
?

Of course, it's a bug that the compiler doesn't catch this and report a
syntax error. It would be worth filing a bug report to get this
improved.

-- 
Knut Anders

Mime
View raw message