1

I've been triyng to create a query using a dblink with some parameters. I'm using Hibernate 4 and the database is a MSSQL.

But I either get:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

if my query is 

@Query(nativeQuery=true, value="SELECT * FROM OPENQUERY([linked_server], 'SELECT * FROM TABLE WHERE COLUM1 = ''0145A'' AND COLUMN2 LIKE ':prefix'' );")
    public List<CPNT023_PART> GET_PART_AND_ANALYST(@Param(value="prefix") String prefix);

Or I get 

java.lang.IllegalArgumentException: Parameter with that position [1] did not exist

if the query is

@Query(nativeQuery=true, value="SELECT * FROM OPENQUERY([linked_server], 'SELECT * FROM TABLE WHERE COLUM1 = ''0145A'' AND COLUMN2 LIKE '':prefix'' ' );")
public List<CPNT023_PART> GET_PART_AND_ANALYST(@Param(value="prefix") String prefix);

(Note the difference in the single quotes after LIKE).

If I don't use any parameters I get a correct answer. So this query:

@Query(nativeQuery=true, value="SELECT * FROM OPENQUERY([linked_server], 'SELECT * FROM TABLE WHERE COLUM1 = ''0145A'' AND COLUMN2 LIKE ''%ABC%'' ' );")

Actually works.

I'm kinda lost with this problem, I'v been triyng to search for an answer but got nowhere near a valid one.

I have to make use of OPENQUERY as the linked server is a huge Oracle database, as I need it to make the actual processing.

Thanks in advance.

Batousay
  • 21
  • 4

1 Answers1

1

Finally I didn't manage to make the querys work properly.

To solve the problem I created some stored procedures in the database with the querys and that solved everything, as the @Query annotation doen't have to deal with nested apostrophes.

Life now is easy, and keeps going on.

Bye.

Batousay
  • 21
  • 4