John English <[email protected]> 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
