Re: Problems generating sql with the SqlGenerator

Hi Armin,

yes, I have the problem only when adding my extension. Generally, all 
works fine.
We have tried a other solution like this:
-------------------------------------------------
  public String getPreparedSelectStatement(Query query, ClassDescriptor 
cld) {
      try {
      if ((cld.getClassOfObject().newInstance() instanceof 
LogableAbstractPoIfc)) {
              logQuery.setCriteria(new Criteria());
              logQuery.getCriteria().addEqualTo( "log_table", 
OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
              logQuery.getCriteria().addEqualTo("log_funktion", "D");
              logQuery.getCriteria().addEqualToColumn(log_table_obj_id, 
"A0.obj_id");
              query.getCriteria().addNotExists(logQuery);
-------------------------------------------------

Except of one Query, all works fine. In this one query, ojb works with 
two outer joins instead of in join and i can not set in join as 
preffered like query.setPathOutterJoin() :o/

In my opinion, the old solution in my generator must also work, any idea??

Thanks Armin for time you spend
Josef


Armin Waibel schrieb:
> Hi Josef,
>
> could you clarify your problem: The generated sql is only invalid when 
> you add your extension or is the generated sql invalid in general?
>
> regards,
> Armin
>
> Josef Wagner wrote:
>> Hi List,
>>
>> i have written my one SqlGenerator, because i have to extend each 
>> query width the criteria, that the status of the entry, which is 
>> described in a seperate log-table is not 'DELETED'.
>> ------------------
>> Problem
>> ------------------
>> Now, my problem is, that the generated statemts are incorecct.
>> I extend from the SqlGeneratorDefaultImpl and override the Method 
>> getPreparedSelectStatement as followed:
>>
>>   public String getPreparedSelectStatement(Query query, 
>> ClassDescriptor cld) {
>>       try {
>>       if ((cld.getClassOfObject().newInstance() instanceof 
>> LogableAbstractPoIfc)) {
>>           query.getCriteria().addEqualTo("log.log_table", 
>> OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
>>           query.getCriteria().addNotEqualTo("log.log_status", "D");
>>       }
>>       }              catch(Exception iaex) {
>>           logger.warn("Statement wurde evtl. nicht korrekt 
>> aufbereitet");                      
>> logger.warn(iaex);                  }
>>             SqlCacheKey key = new SqlCacheKey(query, cld, 
>> SqlCacheKey.TYPE_SELECT);
>>       String result = (String) m_sqlCacheMap.get(key);
>>       if (result == null) {
>>           SqlStatement sql = new SqlSelectStatement(getPlatform(), 
>> cld, query, logger);
>>           result = sql.getStatement();
>>           if (logger.isDebugEnabled()) {
>>               logger.debug("SQL:" + result);
>>           }
>>           m_sqlCacheMap.put(key, result);
>>       }
>>       return result;
>>   }
>>
>> The following incorrect statment will be generated, wenn calling
>> new User(1).getGroups().size();
>>
>> -------------
>> Incorrect generated sql-statement
>> -------------
>> SELECT count(*) FROM groups A0,users_groups INNER JOIN log 
>> A1,users_groups ON A0.obj_id=A1.log_table_obj_id WHERE 
>> (((users_groups.users_obj_id = 1) AND users_groups.groups_obj_id = 
>> A0.obj_id) AND log_table = 'GrOUPS') AND log_funktion <> 'D'
>>
>> ------------
>> statement i would expect
>> ------------
>> SELECT count(*) FROM groups A0 INNER JOIN log A1 ON 
>> A0.obj_id=A1.log_table_obj_id INNER JOIN users_groups ON A0.obj_id= 
>> users_groups.groups_obj_id WHERE (((users_groups.users_obj_id = 1) 
>> AND users_groups.groups_obj_id = A0.obj_id) AND log_table = 'GROUPS') 
>> AND log_funktion <> 'D'
>>
>> ---------------------------
>> Description to the tables
>> ---------------------------
>>
>>
>> ##############            #####################           ##############
>> # groups     #            # groups_users      #           # users      #
>> ##############            #####################           ##############
>> # int obj_id #            # int groups_obj_id #           # int obj_id #
>> ##############            # int users_obj_id  #           ##############
>>
>>
>> #########################
>> # log                   #
>> #########################
>> # int obj_id            #
>> # int log_table_obj_id  #
>> # string log_table      #
>> # string log_status     #
>> #########################
>>
>> - table groups and table users are joined width the m-n-table 
>> groups_users
>> - for each data-entry, there is a entry in the log-table, which 
>> describes the status of the data-entry (deleted, inserted...) the 
>> repository.xml of groups and users have an collection-descrpitor to 
>> the log-table with a query-optimizer vor verifing the source-table 
>> with string log_table
>>       <!-- 1-n to Log -->
>>   <collection-descriptor
>>      auto-retrieve="true"
>>      auto-delete="object"
>>      auto-update="object"
>>      proxy="true"
>>      name="log"
>>      element-class-ref="de.on_ergy.lakon.data.model.Log"
>>   >
>>      <inverse-foreignkey field-ref="logTableObjId"/>
>>      <query-customizer
>>           class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>>           <attribute
>>               attribute-name="logTable"
>>               attribute-value="GROUPS"
>>           />
>>       </query-customizer>
>>        </collection-descriptor>
>>
>> the query-customizer sets an and-criteria width log_table equals for 
>> example "GROUPS"
>>
>>
>> --------------------------------------
>> groups_repostory.xml, users_repository is equal, only width other 
>> parameters
>> -----------------------------------------
>> <class-descriptor class="de.on_ergy.lakon.data.model.Groups" 
>> table="groups">
>>   <field-descriptor name="objId"         column="obj_id"          
>> jdbc-type="INTEGER"                primarykey="true" 
>> autoincrement="true"></field-descriptor>
>>
>>   <!-- m - n  users_groups to users -->
>>   <collection-descriptor
>>    name="users"
>>    
>> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 
>>
>>    element-class-ref="de.on_ergy.lakon.data.model.Users"
>>    auto-retrieve="true"
>>    auto-update="false"
>>    auto-delete="link"
>>      proxy="true"
>>    indirection-table="users_groups"
>>  >
>>    <fk-pointing-to-this-class column="groups_obj_id"/>
>>    <fk-pointing-to-element-class column="users_obj_id"/>
>> </collection-descriptor>  <!-- 1-n to log -->
>>   <collection-descriptor
>>      auto-retrieve="true"
>>      auto-delete="object"
>>      auto-update="object"
>>      proxy="true"
>>      name="log"
>>      element-class-ref="de.on_ergy.lakon.data.model.Log"
>>   >
>>      <inverse-foreignkey field-ref="logTableObjId"/>
>>      <query-customizer
>>           class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>>           <attribute
>>               attribute-name="logTable"
>>               attribute-value="GROUPS"
>>           />
>>       </query-customizer>
>>        </collection-descriptor>
>>  </class-descriptor>
>>
>>
>> ---------------------
>> that complete repository.xml data are in attachment, but in german...
>>
>> Thanks a lot for your help
>>
>> Josef Wagner/ Germany
>>
>>
>> ------------------------------------------------------------------------
>>
>> <class-descriptor class="de.on_ergy.lakon.data.model.Benutzer" 
>> table="benutzer">
>>     <field-descriptor name="objId"       column="obj_id"         
>> jdbc-type="INTEGER"                primarykey="true" 
>> autoincrement="true"></field-descriptor>
>>     <field-descriptor name="benutzerKz"   column="benutzer_kz"      
>> jdbc-type="VARCHAR"  length="6"   ></field-descriptor>
>>     <field-descriptor name="vorName"  column="vorname"    
>> jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
>>     <field-descriptor name="nachName"   column="nachname"      
>> jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
>>     <field-descriptor name="passwort"   column="passwort"      
>> jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
>>     <field-descriptor name="memoObjId"   column="memo_obj_id"    
>> jdbc-type="INTEGER"                ></field-descriptor>
>>     <field-descriptor name="spracheObjId"   
>> column="sprache_obj_id"    jdbc-type="INTEGER"                
>> ></field-descriptor>
>>     <!-- Referenz auf den Memotexts -->
>>     <reference-descriptor
>>        auto-delete="object"
>>        auto-update="object"
>>        proxy="true"
>>        name="memotext"
>>        class-ref="de.on_ergy.lakon.data.model.Memotexte"
>>     >
>>     <foreignkey field-ref="memoObjId"/>           
>> </reference-descriptor>            <!-- Referenz auf die Sprache -->
>>     <reference-descriptor
>>        proxy="true"
>>        name="sprache"
>>        class-ref="de.on_ergy.lakon.data.model.Sprache"
>>     >
>>     <foreignkey field-ref="spracheObjId"/>           
>> </reference-descriptor>            <!-- m - n ÃŒber benutzer_gruppen 
>> zu gruppen -->
>>     <collection-descriptor
>>      name="gruppen"
>>      
>> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 
>>
>>      element-class-ref="de.on_ergy.lakon.data.model.Gruppen"
>>      auto-retrieve="true"
>>      auto-update="false"
>>      auto-delete="link"
>>      proxy="true"
>>      indirection-table="benutzer_gruppen"
>>   >
>>      <fk-pointing-to-this-class column="benutzer_obj_id"/>
>>      <fk-pointing-to-element-class column="gruppen_obj_id"/>
>>   </collection-descriptor>   
>>   <!-- m - n ÃŒber benutzer_teams zu den Teams -->
>>   <collection-descriptor
>>      name="teams"
>>      
>> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 
>>
>>      element-class-ref="de.on_ergy.lakon.data.model.Teams"
>>      auto-retrieve="true"
>>      auto-update="false"
>>      auto-delete="link"
>>      proxy="true"
>>      indirection-table="benutzer_teams"
>>   >
>>      <fk-pointing-to-this-class column="benutzer_obj_id"/>
>>      <fk-pointing-to-element-class column="team_obj_id"/>
>>   </collection-descriptor>           
>>                 <!-- 1-n-Beziehung zu dem Log -->
>>     <collection-descriptor
>>        auto-retrieve="true"
>>        auto-delete="object"
>>        auto-update="object"
>>        proxy="true"
>>        name="log"
>>        element-class-ref="de.on_ergy.lakon.data.model.Log"
>>     >
>>        <inverse-foreignkey field-ref="logKlasseObjId"/>
>>        <query-customizer
>>             class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>>        <!-- Achtung, diesen Werte bitte aus Klasse Tabellentyp 
>> entnehmen -->
>>             <attribute
>>                 attribute-name="logTable"
>>                 attribute-value="BENUTZER"
>>             />
>>         </query-customizer>
>>            </collection-descriptor>
>>        
>> </class-descriptor>
>>
>>
>> ------------------------------------------------------------------------
>>
>> <class-descriptor class="de.on_ergy.lakon.data.model.Gruppen" 
>> table="gruppen">
>>     <field-descriptor name="objId"         column="obj_id"          
>> jdbc-type="INTEGER"                primarykey="true" 
>> autoincrement="true"></field-descriptor>
>>     <field-descriptor name="gruppeKz"  column="gruppe_kz"  
>> jdbc-type="CHAR"     length="12"   ></field-descriptor>
>>     <field-descriptor name="standard"  column="standard"  
>> jdbc-type="BIT"  ></field-descriptor>
>>     <field-descriptor name="memoObjId"         
>> column="memo_obj_id"          jdbc-type="INTEGER"                
>> ></field-descriptor>
>>     <!-- 1-n Referenz auf die Bezeichnungen -->
>>     <collection-descriptor
>>        auto-delete="object"
>>        auto-update="object"
>>        proxy="true"
>>        name="bezeichnungen"
>>        element-class-ref="de.on_ergy.lakon.data.model.Bezeichnungen"
>>        orderby="sprach_obj_id"
>>        sort="ASC"
>>     >
>>        <inverse-foreignkey field-ref="parentObjId"/>
>>        <query-customizer
>>             class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>>        <!-- Achtung, diesen Werte bitte der Schnittstelle 
>> TabellentypAttributeIfc entnehmen -->
>>             <attribute
>>                 attribute-name="parentTabKz"
>>                 attribute-value="GRUPPEN"
>>             />
>>         </query-customizer>
>>            </collection-descriptor>
>>     <!-- Referenz auf den Memotexts -->
>>     <reference-descriptor
>>        auto-delete="object"
>>        auto-update="object"
>>        proxy="true"
>>        name="memotext"
>>        class-ref="de.on_ergy.lakon.data.model.Memotexte"
>>     >
>>        <foreignkey field-ref="memoObjId"/>           
>> </reference-descriptor>
>>         <!-- m - n ÃŒber benutzer_gruppen zu Benutzer -->
>>     <collection-descriptor
>>      name="benutzer"
>>      
>> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 
>>
>>      element-class-ref="de.on_ergy.lakon.data.model.Benutzer"
>>      auto-retrieve="true"
>>      auto-update="false"
>>      auto-delete="link"
>>        proxy="true"
>>      indirection-table="benutzer_gruppen"
>>   >
>>      <fk-pointing-to-this-class column="gruppen_obj_id"/>
>>      <fk-pointing-to-element-class column="benutzer_obj_id"/>
>>   </collection-descriptor>   
>>
>>     <!-- m - n ÃŒber gruppen_rollen zu rollen -->
>>     <collection-descriptor
>>      name="rollen"
>>      
>> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 
>>
>>      element-class-ref="de.on_ergy.lakon.data.model.Rollen"
>>      auto-retrieve="true"
>>      auto-update="false"
>>      auto-delete="link"
>>      proxy="true"
>>      indirection-table="gruppen_rollen"
>>   >
>>      <fk-pointing-to-this-class column="gruppen_obj_id"/>
>>      <fk-pointing-to-element-class column="rollen_obj_id"/>
>>   </collection-descriptor>        
>>         <!-- 1-n-Beziehung zu dem Log -->
>>     <collection-descriptor
>>        auto-retrieve="true"
>>        auto-delete="object"
>>        auto-update="object"
>>        proxy="true"
>>        name="log"
>>        element-class-ref="de.on_ergy.lakon.data.model.Log"
>>     >
>>        <inverse-foreignkey field-ref="logKlasseObjId"/>
>>        <query-customizer
>>             class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>>        <!-- Achtung, diesen Werte bitte aus Klasse Tabellentyp 
>> entnehmen -->
>>             <attribute
>>                 attribute-name="logTable"
>>                 attribute-value="GRUPPEN"
>>             />
>>         </query-customizer>
>>            </collection-descriptor>
>>     
>> </class-descriptor>
>>
>>
>> ------------------------------------------------------------------------
>>
>> <class-descriptor class="de.on_ergy.lakon.data.model.Log" table="log">
>>     <field-descriptor name="objId"         column="obj_id"         
>> jdbc-type="INTEGER"                primarykey="true" 
>> autoincrement="true"></field-descriptor>
>>     <field-descriptor name="logBenutzerObjId"         
>> column="log_benutzer_obj_id"         
>> jdbc-type="INTEGER"></field-descriptor>
>>     <field-descriptor name="logKlasseObjId"     
>> column="log_klasse_obj_id"     jdbc-type="INTEGER"></field-descriptor>
>>     <field-descriptor name="mnObjId"         
>> column="m_n_obj_id"         jdbc-type="INTEGER"          
>> ></field-descriptor>
>>     <field-descriptor name="logTable"     column="log_table"     
>> jdbc-type="VARCHAR"     length="8"></field-descriptor>
>>     <field-descriptor name="logFunktion"   column="log_funktion"   
>> jdbc-type="CHAR"     length="1"   ></field-descriptor>
>>     <field-descriptor name="logDataDefault"     
>> column="log_data_default"     jdbc-type="VARCHAR"  length="65535"  
>> ></field-descriptor>   
>>     <field-descriptor name="logDataExport"     
>> column="log_data_export"     jdbc-type="VARCHAR"  length="65535"  
>> ></field-descriptor>   
>>     <field-descriptor name="erstelltTimestamp"  
>> column="erstellt_timestamp"  jdbc-type="TIMESTAMP"               
>> ></field-descriptor>
>>     <field-descriptor name="letzteAenderungTimestamp"  
>> column="letzte_aenderung_timestamp"  
>> jdbc-type="TIMESTAMP"               ></field-descriptor>
>> </class-descriptor>
>>
>>
>> ------------------------------------------------------------------------
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>


-- 
Josef Wagner

On Ergy Systemberatung GmbH
FÃŒssener Str. 31
87459 Pfronten

Tel.: 	08363 92898 - 57
Fax.: 	08363 92898 - 51
E-Mail:	[email protected]