On 04/06/2012 16:35, Kristian Waagan wrote:
On 04.06.12 14:52, Rick Hillegas wrote:
On 6/2/12 3:18 AM, John English wrote:
On 01/06/2012 15:19, John English wrote:
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 also get the same error from this, which looks fairly innocuous to me:
INSERT INTO resource_usage (resid,itemid,itemtype)
(SELECT resid,20,'final' FROM resource_usage WHERE itemid=? AND
itemtype=?);
Is it perhaps because I'm inserting into the same table I'm selecting
from?
Hi John,

Do you have a stack trace for this error?

My extremely simple attempt at reproducing this didn't succeed on trunk (10.10), and I could "duplicate" the matching rows (only changing the itemtype column).
The itemid also changes; only resid stays the same.
Do you also have the schema and example of data that triggers the problem? Are there many matching rows for the select?
I'm on 10.8, and I only have about a dozen matching rows at the moment. No stack trace that goes further than my own code; all I have is stuff like this:
javax.servlet.ServletException: Conglomerate could not be created.
Query parameters: 1='2' 2='final' 3='9' 4='temp' 5='2' 6='tests'
	at servlets.BaseServlet.doGet(BaseServlet.java:282)
	at servlets.BaseServlet.doPost(BaseServlet.java:304)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        ... (and so on)
Caused by: java.sql.SQLException: Conglomerate could not be created.
Query parameters: 1='2' 2='final' 3='9' 4='temp' 5='2' 6='tests'
	at utils.DataManager.dbInsert(DataManager.java:1067)
	at actions.EditAction.copyResources(EditAction.java:1617)
	at actions.EditAction.doRelease(EditAction.java:1171)
	at actions.EditAction.processUpdates(EditAction.java:537)
	at servlets.BaseServlet.doGet(BaseServlet.java:190)
	... 31 more

which is from my own exception handler that catches the exception, appends the query parameters to the message and then rethrows it. The table is defined like so:

  CREATE TABLE resource_usage (
    resid       INTEGER       NOT NULL,
    itemid      INTEGER       NOT NULL,
    itemtype    VARCHAR(16)   NOT NULL,
    CONSTRAINT resusage_1     FOREIGN KEY (resid)
                              REFERENCES resources(id)
                              ON DELETE CASCADE,
    CONSTRAINT resusage_2     UNIQUE(resid,itemid,itemtype)
  );

There is more detail (including example data) in my original post.

Meanwhile, in desperation, I have broken it down into code to select the resource IDs followed by a loop to insert them one by one, which works but leaves me feeling deeply offended!

I'll try and boil it down to a simple example as soon as I have time and post more soon. Thanks for your help meanwhile!
--
John English
If we were to define a religion to be a system of thought which contains unprovable statements, so it contains an element of faith, then Gödel has taught us that not only is mathematics a religion but it is the only religion able to prove itself to be one.
John Barrow, Pi in the Sky