Hibernate cannot execute native query SqlExceptionHelper : Invalid value "7" for parameter "parameterIndex"

Question

I have native SQL request and it consist of two

insert - select (with subrequest)
select

The parameter is list of three String elements and passed to:

@Modifying
@Query(value = " "
//close
+ "INSERT INTO status (is_open,advertisement, updated) "
+ "SELECT "
+ " false, to_close.id, NOW() "
+ "FROM( "
+ " SELECT " 
+ "             ad.id as id, " 
+ "             ad.native_id as native_id, " 
+ "             sc_newest.updated as updated, " 
+ "             status.is_open as is_open " 
+ " FROM advertisement AS ad " 
+ " LEFT JOIN " 
+ "         (SELECT "
+ "             MAX(sc.UPDATED) AS updated, "
+ "             sc.ADVERTISEMENT "
+ "         FROM ADVERTISEMENT AS ad "
+ "         LEFT JOIN STATUS AS sc "
+ "             ON  ad.ID = sc.ADVERTISEMENT "
+ "         WHERE   ad.NATIVE_ID NOT IN  :shown_ads "
+ "         GROUP BY sc.advertisement) AS sc_newest "
+ "     ON ad.id = sc_newest.advertisement "
+ " LEFT JOIN status AS status "
+ "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
+ " WHERE "
+ "     status.is_open = true " 
+ "     AND ad.native_id NOT IN :shown_ads) AS to_close ; "
+ ""
// get stored previously 
+ " SELECT "
+ " ADVERTISEMENT.NATIVE_ID "
+ "FROM ADVERTISEMENT "
+ " WHERE "
+ " ADVERTISEMENT.NATIVE_ID IN :shown_ads "
        , nativeQuery = true)
public Set<String> closeReopenFindStored(@Param("shown_ads") List<String> shownNaturalIDs);

Here the log

2017-08-03 07:44:12.353 DEBUG 16162 --- [ main] org.hibernate.SQL :

INSERT INTO status (is_open,advertisement, updated) SELECT false, to_close.id, NOW() FROM( SELECT ad.id as id, ad.native_id as native_id, sc_newest.updated as updated, status.is_open as is_open FROM advertisement AS ad LEFT JOIN (SELECT MAX(sc.UPDATED) AS updated, sc.ADVERTISEMENT FROM ADVERTISEMENT AS ad LEFT JOIN STATUS AS sc ON ad.ID = sc.ADVERTISEMENT WHERE ad.NATIVE_ID NOT IN (?, ?, ?) GROUP BY sc.advertisement) AS sc_newest ON ad.id = sc_newest.advertisement LEFT JOIN status AS status ON ad.id = status.advertisement AND status.updated = sc_newest.updated WHERE status.is_open = true AND ad.native_id NOT IN (?, ?, ?)) AS to_close ; SELECT ADVERTISEMENT.NATIVE_ID FROM ADVERTISEMENT WHERE ADVERTISEMENT.NATIVE_ID IN (?, ?, ?)

2017-08-03 07:44:12.429 TRACE 16162 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [ad1 number] 2017-08-03 07:44:12.430 TRACE 16162 --- [
main] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [VARCHAR] - [ad1 number] 2017-08-03 07:44:12.431 TRACE 16162 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [VARCHAR] - [ad1 number]

2017-08-03 07:44:12.432 WARN 16162 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90008, SQLState: 90008 2017-08-03 07:44:12.433 ERROR 16162 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Invalid value "7" for parameter "parameterIndex" [90008-195]

Why this not works?

Update - SQL I run from SQL client fine:

INSERT INTO status (is_open,advertisement, updated) 
SELECT 
    false, to_close.id, NOW() 
FROM( 
    SELECT 
                ad.id as id, 
                ad.native_id as native_id, 
                sc_newest.updated as updated, 
                status.is_open as is_open 
    FROM advertisement AS ad 
    LEFT JOIN 
            (SELECT 
                MAX(sc.UPDATED) AS updated, 
                sc.ADVERTISEMENT 
            FROM ADVERTISEMENT AS ad 
            LEFT JOIN STATUS AS sc 
                ON  ad.ID = sc.ADVERTISEMENT 
            WHERE   ad.NATIVE_ID NOT IN  ('ad1 number','ad2 number', 'new ad') 
            GROUP BY sc.advertisement) AS sc_newest 
        ON ad.id = sc_newest.advertisement 
    LEFT JOIN status AS status 
        ON ad.id = status.advertisement AND status.updated = sc_newest.updated 
    WHERE 
        status.is_open = true 
        AND ad.native_id NOT IN ('ad1 number','ad2 number', 'new ad')) AS to_close ; 


SELECT 
    ADVERTISEMENT.ID AS ad_id,
    ADVERTISEMENT.NATIVE_ID
FROM ADVERTISEMENT
WHERE
    ADVERTISEMENT.NATIVE_ID IN ('ad1 number','ad2 number', 'new ad')

Confused - how can it have two parts? an `insert` followed by a separate `select` ? — Scary Wombat, Aug 03 '17 at 05:19
or do you mean not have have the semicolon at `AS to_close ; "` — Scary Wombat, Aug 03 '17 at 05:19
Well, as Hibernate can execute SQL queries, I think it should be able to do same what I doing in SQL client. I not found any limitations about this in docs yet. In SQL client I can run few SQL commands and separate them with ";" sign. — P_M, Aug 03 '17 at 05:22
I think you are passing value in String at the place of integer — CIPHER007, Aug 03 '17 at 05:26
native_id is varchar. The Advertisement.nativeId is a String property generated by oter system. I just store it. You can see this in test class closeReopenFindStoredTest method — P_M, Aug 03 '17 at 05:39
OK see https://stackoverflow.com/a/10804730/2310289 `String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true"; ` — Scary Wombat, Aug 03 '17 at 05:49
I updated with SQL command I tested before move this to Java code — P_M, Aug 03 '17 at 05:49
@Scary Wombat the link you gived about mySQL while I use H2 database. In H2 multiple queries should work by default http://h2-database.66688.n3.nabble.com/dose-h2database-support-multiple-queries-td4031455.html You can try to clone my repository (it contains just couple of tests) so you will see any details there. — P_M, Aug 03 '17 at 05:57
Pavlo, the link that you gave gives the same remedy as both my comment and the links I gave you - **that is the connection url needs** `allowMultiQueries=true` - can not see where in you link it says it works by default — Scary Wombat, Aug 03 '17 at 06:01
You right I messed with links. Though when I tried to update connection like spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;allowMultiQueries=true I got an error states about dialect not set. But with dialect set to MySQLDialect and mode MODE=MySQL it not works either. — P_M, Aug 03 '17 at 06:22
maybe *org.hibernate.dialect.H2Dialect* see http://www.h2database.com/html/cheatSheet.html — Scary Wombat, Aug 03 '17 at 06:32

score 1 · Accepted Answer · edited Aug 03 '17 at 10:47

I have gone through your code and I have figured out your problem. The problem is that you can not run 2 queries i.e. insert and select query at using the same method because your insert query and select query return type are not same so you have to create 2 different methods 1st for saving data and 2nd for selecting data from the database. You have to modify your code like below

AdvertisementRepository.java

//  @Modifying
    @Query(value = " "
    //close
//  + "INSERT INTO status (is_open,advertisement, updated) "
//  + "SELECT "
//  + " false, to_close.id, NOW() "
//  + "FROM( "
//  + " SELECT " 
//  + "             ad.id as id, " 
//  + "             ad.native_id as native_id, " 
//  + "             sc_newest.updated as updated, " 
//  + "             status.is_open as is_open " 
//  + " FROM advertisement AS ad " 
//  + " LEFT JOIN " 
//  + "         (SELECT "
//  + "             MAX(sc.UPDATED) AS updated, "
//  + "             sc.ADVERTISEMENT "
//  + "         FROM ADVERTISEMENT AS ad "
//  + "         LEFT JOIN STATUS AS sc "
//  + "             ON  ad.ID = sc.ADVERTISEMENT "
//  + "         WHERE   ad.NATIVE_ID NOT IN  :shown_ads "
//  + "         GROUP BY sc.advertisement) AS sc_newest "
//  + "     ON ad.id = sc_newest.advertisement "
//  + " LEFT JOIN status AS status "
//  + "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
//  + " WHERE "
//  + "     status.is_open = true " 
//  + "     AND ad.native_id NOT IN :shown_ads) AS to_close ; "
//  + ""
    //reopen
//  + "INSERT INTO status (is_open,advertisement, updated) "
//  + "SELECT "
//  + " true, to_reopen.id, NOW() "
//  + "FROM( "
//  + " SELECT " 
//  + "             ad.id as id, " 
//  + " FROM advertisement AS ad " 
//  + " LEFT JOIN " 
//  + "             (SELECT "
//  + "                 MAX(sc.updated) AS updated, " 
//  + "                 sc.advertisement " 
//  + "             FROM status AS sc "
//  + "             GROUP BY sc.advertisement) AS sc_newest "
//  + "     ON ad.id = sc_newest.advertisement "
//  + " LEFT JOIN status AS status "
//  + "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
//  + " WHERE "
//  + "     status.is_open = false " //reopen closed 
//  + "     AND ad.native_id NOT IN :shown_ads) AS to_reopen ; "
//  + ""
    // get stored previously 
    + " SELECT "
    + " advertisement.native_id "
    + "FROM advertisement "
    + " WHERE "
    + " advertisement.native_id IN :shown_ads "
            , nativeQuery = true)

    public List<String> closeReopenFindStored(@Param("shown_ads") List<String> shownNaturalIDs);


    @Modifying
    @Query(value = "INSERT INTO status (is_open,advertisement, updated) "
    + "SELECT "
    + " false, to_close.id, NOW() "
    + "FROM( "
    + " SELECT " 
    + "             ad.id as id, " 
    + "             ad.native_id as native_id, " 
    + "             sc_newest.updated as updated, " 
    + "             status.is_open as is_open " 
    + " FROM advertisement AS ad " 
    + " LEFT JOIN " 
    + "         (SELECT "
    + "             MAX(sc.UPDATED) AS updated, "
    + "             sc.ADVERTISEMENT "
    + "         FROM ADVERTISEMENT AS ad "
    + "         LEFT JOIN STATUS AS sc "
    + "             ON  ad.ID = sc.ADVERTISEMENT "
    + "         WHERE   ad.NATIVE_ID NOT IN  :shown_ads "
    + "         GROUP BY sc.advertisement) AS sc_newest "
    + "     ON ad.id = sc_newest.advertisement "
    + " LEFT JOIN status AS status "
    + "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
    + " WHERE "
    + "     status.is_open = true " 
    + "     AND ad.native_id NOT IN :shown_ads) AS to_close ; "
    + "", nativeQuery = true)
    public void saveCloseReopenFindStored(@Param("shown_ads") List<String> shownNaturalIDs);

AdvertisementRepositoryTestService.java

@Transactional
    public List<String> closeReopenFindStored(List<String> shownAdNumbers){
        advertisementRepository.saveCloseReopenFindStored(shownAdNumbers);
        return advertisementRepository.closeReopenFindStored(shownAdNumbers);
    }

AdvertisementRepositoryTest.java

List<String>  storedPreviously =     
                    advertisementRepositoryTestService.closeReopenFindStored(shownAdNaturalIds);
            assertEquals(2, storedPreviously.size());

No change in AdvertisementRepositoryTest.java

Note: I have changed your return type Set<String> to List<String> you can change it according to your requirement.

I tried to use separate methods for each SQL statement. This works fine. Thank you. — P_M, Aug 03 '17 at 09:36

Hibernate cannot execute native query SqlExceptionHelper : Invalid value "7" for parameter "parameterIndex"

1 Answers1