BIG problem (bug?) with JDBC and maxdb 7.7

[Former Member]

As described in a previous post I'm migrating a set of databases from a server with maxdb 7.6 to a new server with maxdb 7.7.

The databases are used by a java app server (JBoss 4.2.3) so it was easy for me to migrate one db at a time and check the result.

Tonight I was migrating the last database but I found a big problem.

With version 7.6 our apps on the app server are ok.

With version 7.7 the apps get an JDBC Exception (see below).

Nothing changes but the database server. The app server is using the 7.7 jdbc driver so it's the same driver for both db servers.

It's very strange that this exception is raised unless something changed on the supported SQL.

The exception talks about order column that must be output column.

But I double checked the sql query and the order column (one) IS an output column.

Can someone help on this problem ?

Thank you !!

Caused by: com.sap.dbtech.jdbc.exceptions.DatabaseException: 
[-8013] (at 3115): Order column must be output column
at com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:72) at
com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:946) at
com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:584) at
com.sap.dbtech.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1797) at
com.sap.dbtech.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:866) at
com.sap.dbtech.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:242) at
com.sap.dbtech.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:195) at
com.sap.dbtech.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.java:105) at
com.sap.dbtech.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:852) at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperMan
agedConnection.java:449) at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:444) at
org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:243) at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505) at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423) at
org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139) at
org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547) at
org.hibernate.loader.Loader.doQuery(Loader.java:673) at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at
org.hibernate.loader.Loader.doList(Loader.java:2220) ... 104 more

[Former Member · Accepted solution]

Hallo,

nice to hear that you found the problem by yourself.

You asked:

Why did this statement succeed in 7.6?

I have to say: there were some bugs/misbehaviours concerning output-columns with normal (substr, for example) and aggregate (like SUM, for example) functions around and this column or the reference name of the output column as order by column.

Statements, which should have done, did not work, and vice-versa. Now several corrections have been implemented.

Your statement should never have worked and (I do not know hybernate) should never have been constructed. I hope, you are able to provide some info to this tool to prepare a better/correct statement.

Elke

[Former Member · Accepted solution]

Dear Elke,

I've fixed the query and now it works. In hibernate you provide a sql with a syntax that is similar to sql so it was my fault.

The strange thing was that it worked in 7.6 so I wrote that here for future reference.

Thank you !!

[Former Member]

I am loading data with java loader and a column contains question mark ( ?) value has been replaced with blank ( empty ) value

mLoaderSession.cmd ("IMPORT TABLE <table name> IGNORE DUPLICATES DATA INSTREAM FILE '<file name>' CSV");

RDBMS version: 7.7.07.16

OS RHEL 5 64 bit

[Former Member]

Hallo,

would you please provide the SQL statement causing the trouble, the info, if the table(s) in the from-clause are base-tables, one-table-views, join-views(more than 1 table in the from-clause of the view), the setting of installation parameter EnableQueryRewrite and the exact version of the server-software(7.7.??.??)?

It is hard to believe that the same server-software-version causes different behaviour with the same statement on different databases. Did you try the exact same statement on other databases? If it succeeds there, please check the differences in installation parameters and definitions in used views(if there are any).

Elke

[Former Member]

Dear Elke,

I've found the problem but I think it need some attention as it may break some other apps.

I have two database servers, the old with 7.6.00.34 64 bit and the new with 7.7.06.09 64 bit .

The query works on the old server but not on the new server.

The database is the same on both servers, I exported the db from the old server with the loader and imported in the new server. Then I switched the datasource on the app server from the old to the new server so my apps are now pointing to the new server. With this config the query fails. Then I switched back to the old server and now it's all ok.

EnableQueryRewrite is YES.

So the problem is that the new server seems to not like a query that the old does.

The query is constructed by Hibernate. Here is the SQL generated from Hibernate, it's a bit difficult to read, sorry. All are simple tables :

select articolo3_.ID as col_0_0_, articolo3_.CODICE as col_1_0_, articolo3_.codiceRepository as 
col_2_0_, articolo3_.DESCRIZIONE as col_3_0_, articolo3_.marchio as col_4_0_, articolo3_.MODELLO as
col_5_0_, articolo3_.CATEGORIA as col_6_0_, unitamisur6_.DESCRIZIONE as col_7_0_, articoloco2_.ID
as col_8_0_, articoloco2_.CODICE as col_9_0_, articoloco2_.STATOCOMMERCIALE as col_10_0_,
articoloco2_.DATA_STATOCOMMERCIALE as col_11_0_, articolo3_.ALTRO1 as col_12_0_,
articolo3_.ALTRO2 as col_13_0_, articolo3_.ALTRO3 as col_14_0_, articolo3_.ALTRO4 as col_15_0_,
articolo3_.ALTRO5 as col_16_0_, articoloco2_.ATTIVO as col_17_0_, articoloco2_.LOTTOORDINE as
col_18_0_, articoloco2_.MINIMOORDINE as col_19_0_, articoloco2_.SCORTAMINIMA as col_20_0_,

MIN(prezzo4_.PREZZO) as col_21_0_,

SUM(dispmagazz5_.DISPONIBILITA) as col_22_0_,
SUM(dispmagazz5_.DISPFUTURA) as col_23_0_, iva7_.CODICE as col_24_0_, iva7_.PERC as
col_25_0_, articolo3_.STIMAPESO as col_26_0_, MAX(prezzo4_.FLAG) as col_27_0_,
articolo3_.COMPOSTO as col_28_0_, articoloco2_.ELEMENTO_ARTICOLOCOMPOSTO as col_29_0_,
articoloco2_.DATA_CREAZIONE as col_30_0_, articoloco2_.DATA_ULTIMAMODIFICA as col_31_0_,
MIN(prezzo4_.PREZZOPREC) as col_32_0_, MIN(prezzo4_.DATAPREZZOPREC) as col_33_0_,
MIN(prezzo4_.PREZZOPRECNOFLAG) as col_34_0_, articolo3_.ALTEZ as col_35_0_, articolo3_.LARG
as col_36_0_, articolo3_.PROF as col_37_0_, articolo3_.TAGLIA as col_38_0_, articolo3_.COLORE as
col_39_0_ from ECF3.ALBEROGENERATO alberogene0_, ALBEROGENERATO_ARTICOLICOMM
articolico1_, ECF3.ARTICOLO_COMMERCIALE articoloco2_, ECF3.ARTICOLO articolo3_,
ECF3.UNITAMISURA unitamisur6_, ECF3.IVA iva7_, ECF3.PREZZO prezzo4_, ECF3.DISPMAGAZZINO
dispmagazz5_ where alberogene0_.ID_ALBEROARTICOLI=articolico1_.ID_ALBEROARTICOLI and
alberogene0_.CODICE=articolico1_.CODICE and alberogene0_.PATH=articolico1_.PATH and
articolico1_.ID_ARTICOLOCOMM=articoloco2_.ID and articoloco2_.ID_ARTICOLO=articolo3_.ID and
articolo3_.ID_UM=unitamisur6_.ID and articolo3_.ID_IVA=iva7_.ID and
prezzo4_.ID_ARTICOLOCOMM=articoloco2_.ID and dispmagazz5_.ID_ARTICOLOCOMM=articoloco2_.ID
and articoloco2_.ID_AZIENDA=? and articoloco2_.ID_CANALE=? and articoloco2_.ATTIVO=? and
prezzo4_.ID_LISTINO=? and alberogene0_.ID_ALBEROARTICOLI=? and
(alberogene0_.PATH||alberogene0_.CODICE)=? and prezzo4_.PREZZO>0 group by articolo3_.ID ,
articolo3_.CODICE , articolo3_.codiceRepository , articolo3_.DESCRIZIONE , articolo3_.marchio ,
articolo3_.MODELLO , articolo3_.CATEGORIA , unitamisur6_.DESCRIZIONE , articoloco2_.ID ,
articoloco2_.CODICE , articoloco2_.STATOCOMMERCIALE , articoloco2_.DATA_STATOCOMMERCIALE
, articolo3_.ALTRO1 , articolo3_.ALTRO2 , articolo3_.ALTRO3 , articolo3_.ALTRO4 , articolo3_.ALTRO5 ,
articoloco2_.ATTIVO , articoloco2_.LOTTOORDINE , articoloco2_.MINIMOORDINE ,
articoloco2_.SCORTAMINIMA , iva7_.CODICE , iva7_.PERC , articolo3_.STIMAPESO ,
articolo3_.COMPOSTO , articoloco2_.ELEMENTO_ARTICOLOCOMPOSTO ,
articoloco2_.DATA_CREAZIONE , articoloco2_.DATA_ULTIMAMODIFICA , articolo3_.ALTEZ ,
articolo3_.LARG , articolo3_.PROF , articolo3_.TAGLIA , articolo3_.COLORE

order by prezzo4_.PREZZO ASC

The statement sort on "PREZZO" and this is the problem as PREZZO appears as MIN(PREZZO) in the select values part.

If I change and sort on MIN(PREZZO) it works in 7.7.

But why it worked on version 7.6 ??

Thank you !!