asked on

executing query in sql server but not in java program

when i execute below query in sql server 2008 console its working fine.but when execute it through java program
its not giving any error or else its not proceeding further.

what this query does is based on the select query condition(if copies and number_copies not equal) it will be inserted into the JOBS table and return the primary key value.

previously i have written select query and insert statement in different transactions.
based on the conditions used to execute INSERT statement.

but now want to execute both (SELECT and INSERT) in the same transaction to avoid race conditions.

INSERT INTO JOBS(TYPE,DOC_ID,JOB_DATE)
OUTPUT Inserted.job_id
SELECT 'sample','22222',GETDATE() 
FROM
(
SELECT COUNT(*) COPIES,
          I.NUMBER_COPIES FROM 
   JOBS J,
   DOCINSTANCES I
  WHERE 
   ID =646464 AND
   J.INSID=I.ID  AND   
   J.TYPE IN('T2') AND
   J.STATUS IN('NEW') AND 
   
   ) T 
          GROUP BY T.COPIES ,T.NUMBER_COPIES
          HAVING T.COPIES <> T.NUMBER_COPIES


we are using JPA and also Hibernate for the back end layer.
		
		Query query = em.createNativeQuery(prop.getJobQuery());//getting above from properties file.
				
				List query= query.getResultList();

Open in new window

PortletPaul

There is one too many AND's in the sql above, try this:

INSERT INTO JOBS (TYPE, DOC_ID, JOB_DATE)
OUTPUT Inserted.job_id
SELECT 'sample', '22222', GETDATE()
FROM (
     SELECT COUNT(*) COPIES, I.NUMBER_COPIES
     FROM JOBS J
     INNER JOIN DOCINSTANCES I ON J.INSID = I.ID
     WHERE I.ID = 646464
              AND J.TYPE IN ('T2')
              AND J.STATUS IN ('NEW')
     GROUP BY I.NUMBER_COPIES
     HAVING COUNT(*) <> I.NUMBER_COPIES
     ) T

Open in new window

If the subquery result is null then no insert is performed.

ASKER CERTIFIED SOLUTION

mrcoffee365

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial