Open query in hibernate

Question

I am using struts and hibernate in my problem. I tried the following query

String hql ="insert into  "+
        "OPENQUERY(OracleLinkedServer, \'SELECT * FROM     report_access_log\') "+
        "(CALLINGHOST, ACCESSTIMESTAMP, HTTPREQUESTMETHOD, ACCESSURL,"+ 
        "HTTPRESPONSECODE, HTTPRESPONSETIMEMILLI, USERNAME, REPORTNAME, ID)"+
        " values "+
         "(:CALLINGHOST,:ACCESSTIMESTAMP,:HTTPREQUESTMETHOD,:ACCESSURL,:HTTPRESPONSECODE," +
         ":HTTPRESPONSETIMEMILLI,:USERNAME,:REPORTNAME,"+ 
         "(select * from OPENQUERY(OracleLinkedServer,"+ 
                 "\'select SQ_RPT_ACC_LOG_ID.nextval from dual\')))";
        Query query=session.createQuery(hql);
        query.setString("CALLINGHOST", userLogReport.get(0).toString());
        query.setDate("ACCESSTIMESTAMP", (Date)userLogReport.get(1));
        query.setString("HTTPREQUESTMETHOD", userLogReport.get(2).toString());
        query.setString("ACCESSURL", userLogReport.get(3).toString());
        query.setString("HTTPRESPONSECODE", userLogReport.get(4).toString());
        query.setInteger("HTTPRESPONSETIMEMILLI", (Integer)userLogReport.get(5));
        query.setString("USERNAME", userLogReport.get(6).toString());
        query.setString("REPORTNAME", userLogReport.get(7).toString());

The query printed on the console is as follows

insert into  OPENQUERY(OracleLinkedServer, 'SELECT * FROM report_access_log')     
(CALLINGHOST, ACCESSTIMESTAMP, HTTPREQUESTMETHOD, ACCESSURL,HTTPRESPONSECODE, 
HTTPRESPONSETIMEMILLI, USERNAME, REPORTNAME, ID) values 
(:CALLINGHOST,:ACCESSTIMESTAMP,:HTTPREQUESTMETHOD,:ACCESSURL,:HTTPRESPONSECODE:HTTPRESPONSE
TIMEMILLI,:USERNAME,:REPORTNAME,(select * from OPENQUERY(OracleLinkedServer,'select 
SQ_RPT_ACC_LOG_ID.nextval from dual')))

i get a query syntax exception at column no 79 which is (CALLINGHOST,...

But when i ran the query in SQL it is getting executed. The query is as follows

insert into  OPENQUERY(OracleLinkedServer, 'SELECT * FROM report_access_log') 
(CALLINGHOST, ACCESSTIMESTAMP, HTTPREQUESTMETHOD, ACCESSURL,HTTPRESPONSECODE, 
HTTPRESPONSETIMEMILLI, USERNAME, REPORTNAME, ID) values 
('10.87.192.246','GET','/cci/bby/ImageViewer/viewImages.action','200',6,'su','Insert 
Review',(select * from OPENQUERY(OracleLinkedServer,'select SQ_RPT_ACC_LOG_ID.nextval 
from dual')))

Please explain the problem and provide me a solution for executing it from Java. Thanks in advance.

score 1 · Accepted Answer · answered Sep 13 '11 at 10:02

1

HQL and SQL are two different languqges. HQL works on Hibernate entities, their properties, and associations between them. SQL works on database tables and columns.

Use

Query query = session.createSQLQuery(sql);

rather than

Query query = session.createQuery(hql);

answered Sep 13 '11 at 10:02

JB Nizet

678,734
91
1,224
1,255

Thanks JB... Oversight on my side... :-) – rozar Sep 13 '11 at 10:18
Oops.. I got one another problem. I used session.createSQLQuery() and now getting the following org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query I googled and came to know that we have to use session.createQuery(). Any possible solution ??? First of all i really appreciate your effort JB. – rozar Sep 13 '11 at 12:11
Post your query, the Java code used to execute it, and the exception stack trace in another question. – JB Nizet Sep 13 '11 at 12:14
the query and the Java code is already there in my question. The following is the stack trace – rozar Sep 13 '11 at 12:18
Yes, but this question has already been answered and accepted. You have another question, post it in another question. – JB Nizet Sep 13 '11 at 12:20
Yeah i ll do that JB. Meanwhile the exception i get is org.hibernate.util.JDBCExceptionReporter - MSDTC on server 'BLRSAL132772D\INTAPP2_5' is unavailable. I guess the exception is due to some service stopped in the server. U r take on this ??? – rozar Sep 13 '11 at 12:24
No idea at all. Posting it in another question, with the appropriate tags, the name of your database, etc. will certianly lead to an answer. – JB Nizet Sep 13 '11 at 12:27
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3406/discussion-between-rozar-and-jb-nizet) – rozar Sep 13 '11 at 12:42

Open query in hibernate

1 Answers1