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

LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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.
0
mrcoffee365Commented:
Hibernate does not support the full set of sql which standard relational dbms's can support.  It's hard to tell from the fragment you have posted, but probably the sql cannot be executed through hibernate.

If you are using a stored procedure or variable (again, hard to tell from the posted fragment) then the syntax for executing from java is different from executing in the UI provided with your dbms.

If it is just sql (not a stored procedure), then you can try executing the sql with a normal jdbc call from java rather than using hibernate.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.