0

I'm trying to execute this sql query:

SELECT TOP 150 'giac_img' AS font,
           im_notext1.nt1_codi_arxiu AS codi_arxiu,
           im_notext1.nt1_codi_fons AS codi_fons,
           im_notext1.nt1_unitat AS codi_unitat,
           im_notext1.nt1_id AS codi_intern,
           im_notext1.nt1_titol AS titol,
           im_notext1.nt1_desc AS descripcio,
           im_notext1.nt1_obser AS observacions,
           im_notext1.nt1_data_ini AS data_ini,
           im_notext1.nt1_data_fin AS data_fi,
           im_notext2.nt2_crono AS cronologia,
           im_notext1.codi_grup AS codi_grup,
           im_notext1.nclassif AS codi_classificacio,
           anc_fons.codi_quadre AS codi_quadre,
           anc_fons.nom_fons AS fons_nom,
           anc_fons.descripcio AS fons_descripcio,
           anc_fons.codi_tipus_fons AS fons_tipus_codi,
           anc_arxius.nom_arxiu AS arxiu_nom
FROM im_notext1
LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id
INNER JOIN anc_fons ON im_notext1.nt1_codi_arxiu = anc_fons.codi_arxiu
AND im_notext1.nt1_codi_fons = anc_fons.codi_fons
INNER JOIN anc_arxius ON im_notext1.nt1_codi_arxiu = anc_arxius.codi_arxiu
WHERE im_notext1.nt1_difusio = 'S'
ORDER BY im_notext1.nt1_id ASC

When I'm launching it using jdbc driver is telling me:

org.springframework.dao.TransientDataAccessResourceException: StatementCallback; SQL [SELECT TOP 150 'giac_img' AS font, im_notext1.nt1_codi_arxiu AS codi_arxiu, im_notext1.nt1_codi_fons AS codi_fons, im_notext1.nt1_unitat AS codi_unitat, im_notext1.nt1_id AS codi_intern, im_notext1.nt1_titol AS titol, im_notext1.nt1_desc AS descripcio, im_notext1.nt1_obser AS observacions, im_notext1.nt1_data_ini AS data_ini, im_notext1.nt1_data_fin AS data_fi, im_notext2.nt2_crono AS cronologia, im_notext1.codi_grup AS codi_grup, im_notext1.nclassif AS codi_classificacio, anc_fons.codi_quadre AS codi_quadre, anc_fons.nom_fons AS fons_nom, anc_fons.descripcio AS fons_descripcio, anc_fons.codi_tipus_fons AS fons_tipus_codi, anc_arxius.nom_arxiu AS arxiu_nom FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id INNER JOIN anc_fons ON im_notext1.nt1_codi_arxiu = anc_fons.codi_arxiu AND im_notext1.nt1_codi_fons = anc_fons.codi_fons INNER JOIN anc_arxius ON im_notext1.nt1_codi_arxiu = anc_arxius.codi_arxiu WHERE im_notext1.nt1_difusio = 'S' ORDER BY im_notext1.nt1_id ASC]; The column name im_notext1.nt1_id is not valid.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The column name im_notext1.nt1_id is not valid.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name im_notext1.nt1_id is not valid.

However, I've tried to perform it manually, outside of my service nd it's working well.

The problematic column appears on ORDER BY clause.

I'm building this query using a:

@Bean
public SqlPagingQueryProviderFactoryBean queryProvider() {
    SqlPagingQueryProviderFactoryBean provider = new SqlPagingQueryProviderFactoryBean();

    provider.setDataSource(this.dataSource);
    provider.setSelectClause("SELECT 'giac_img' AS font, im_notext1.nt1_codi_arxiu AS codi_arxiu, im_notext1.nt1_codi_fons AS codi_fons, im_notext1.nt1_unitat AS codi_unitat, im_notext1.nt1_id AS codi_intern, im_notext1.nt1_titol AS titol, im_notext1.nt1_desc AS descripcio, im_notext1.nt1_obser AS observacions, im_notext1.nt1_data_ini AS data_ini, im_notext1.nt1_data_fin AS data_fi, im_notext2.nt2_crono AS cronologia, im_notext1.codi_grup AS codi_grup, im_notext1.nclassif AS codi_classificacio, anc_fons.codi_quadre AS codi_quadre, anc_fons.nom_fons AS fons_nom, anc_fons.descripcio AS fons_descripcio, anc_fons.codi_tipus_fons AS fons_tipus_codi, anc_arxius.nom_arxiu AS arxiu_nom");
    provider.setFromClause("FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id INNER JOIN anc_fons ON im_notext1.nt1_codi_arxiu = anc_fons.codi_arxiu AND im_notext1.nt1_codi_fons = anc_fons.codi_fons INNER JOIN anc_arxius ON im_notext1.nt1_codi_arxiu = anc_arxius.codi_arxiu");
    provider.setWhereClause("WHERE im_notext1.nt1_difusio = 'S'");
    provider.setSortKey("im_notext1.nt1_id");

    return provider;
}

EDIT

These are logs:

2020-11-20 16:08:30.104  INFO 6943 --- [           main] o.s.batch.core.job.SimpleStepHandler     : Executing step: [unitat]
2020-11-20 16:08:30.106 DEBUG 6943 --- [           main] o.s.b.i.database.JdbcPagingItemReader    : Reading page 0
2020-11-20 16:08:30.106 DEBUG 6943 --- [           main] o.s.b.i.database.JdbcPagingItemReader    : SQL used for reading first page: [SELECT TOP 150 im_notext1.nt1_id, 'giac_img' AS font, im_notext1.nt1_codi_arxiu AS codiArxiu, im_notext1.nt1_codi_fons AS codiFons, im_notext1.nt1_unitat AS codiUnitat, im_notext1.nt1_id AS codiIntern, im_notext1.nt1_titol AS titol, im_notext1.nt1_desc AS descripcio, im_notext1.nt1_obser AS observacions, im_notext1.nt1_data_ini AS dataInici, im_notext1.nt1_data_fin AS dataFi, im_notext2.nt2_crono AS cronologia, im_notext1.codi_grup AS codiGrup, im_notext1.nclassif AS codiClassificacio, anc_fons.codi_quadre AS codiQuadre, anc_fons.nom_fons AS fonsNom, anc_fons.descripcio AS fonsDescripcio, anc_fons.codi_tipus_fons AS fonsTipusCodi, anc_arxius.nom_arxiu AS arxiuNom FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id INNER JOIN anc_fons ON im_notext1.nt1_codi_arxiu = anc_fons.codi_arxiu AND im_notext1.nt1_codi_fons = anc_fons.codi_fons INNER JOIN anc_arxius ON im_notext1.nt1_codi_arxiu = anc_arxius.codi_arxiu WHERE im_notext1.nt1_difusio = 'S' ORDER BY im_notext1.nt1_id ASC]
2020-11-20 16:08:30.249 ERROR 6943 --- [           main] o.s.batch.core.step.AbstractStep         : Encountered an error executing step unitat in job job1

org.springframework.dao.TransientDataAccessResourceException: StatementCallback; SQL [SELECT TOP 150 im_notext1.nt1_id, 'giac_img' AS font, im_notext1.nt1_codi_arxiu AS codiArxiu, im_notext1.nt1_codi_fons AS codiFons, im_notext1.nt1_unitat AS codiUnitat, im_notext1.nt1_id AS codiIntern, im_notext1.nt1_titol AS titol, im_notext1.nt1_desc AS descripcio, im_notext1.nt1_obser AS observacions, im_notext1.nt1_data_ini AS dataInici, im_notext1.nt1_data_fin AS dataFi, im_notext2.nt2_crono AS cronologia, im_notext1.codi_grup AS codiGrup, im_notext1.nclassif AS codiClassificacio, anc_fons.codi_quadre AS codiQuadre, anc_fons.nom_fons AS fonsNom, anc_fons.descripcio AS fonsDescripcio, anc_fons.codi_tipus_fons AS fonsTipusCodi, anc_arxius.nom_arxiu AS arxiuNom FROM im_notext1 LEFT OUTER JOIN im_notext2 ON im_notext1.nt1_id = im_notext2.nt1_id INNER JOIN anc_fons ON im_notext1.nt1_codi_arxiu = anc_fons.codi_arxiu AND im_notext1.nt1_codi_fons = anc_fons.codi_fons INNER JOIN anc_arxius ON im_notext1.nt1_codi_arxiu = anc_arxius.codi_arxiu WHERE im_notext1.nt1_difusio = 'S' ORDER BY im_notext1.nt1_id ASC]; The column name im_notext1.nt1_id is not valid.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The column name im_notext1.nt1_id is not valid.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110) ~[spring-jdbc-5.3.1.jar:5.3.1

You can see that first page os got, the second one gets me the issue.

Any ideas?

Jordi
  • 20,868
  • 39
  • 149
  • 333
  • The error would suggest that table `im_notext1` has no column `nt1_id`. Are you sure you're connecting to the same database when manually executing the query? – Mark Rotteveel Nov 20 '20 at 14:51
  • Sorry, I thought I deleted this question. Could you take a llok on [this other question](https://stackoverflow.com/questions/64928337/spring-batch-jdbcpagingitemreader-doesnt-obtain-page-1-onwards)? After that, I'm gonna delete it. – Jordi Nov 20 '20 at 15:12

0 Answers0