Access on OpenLCA DB via Java API

[lando84]

Hi all, I was trying to access to OpenLCA DB by means of Java API. In particular I was not able to retrieve product systems by means of ProductSystemDao object. I wrote this following simple code:

File db_dir = new File("/Users/lando/openLCA-data-1.4/databases/ecoinvent_38_cutoff_lci_bck_for_java");
Derby db = new Derby(db_dir);
ProductSystemDao psDao = new ProductSystemDao(db);
List<ProductSystem> pss = psDao.getForName("Water bottle production");

And I got a the following db error:

Error Code: 30000
Call: SELECT id, default_allocation_method, description, dq_entry, infrastructure_process, last_change, last_internal_id, library, name, process_type, ref_id, tags, version, f_category, f_process_doc, f_dq_system, f_exchange_dq_system, f_location, f_quantitative_reference, f_social_dq_system FROM tbl_processes WHERE (name = ?)
    bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Process sql="SELECT id, default_allocation_method, description, dq_entry, infrastructure_process, last_change, last_internal_id, library, name, process_type, ref_id, tags, version, f_category, f_process_doc, f_dq_system, f_exchange_dq_system, f_location, f_quantitative_reference, f_social_dq_system FROM tbl_processes WHERE (name = ?)")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:391)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:264)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.2.v202107160933): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Column 'LIBRARY' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'LIBRARY' is not a column in the target table.

The error seems to be quite clear, in fact if I try to execute the same query (removing "library" and "tags" fields) directly with NativeSQL, it works properly but it is not what I would like to do because I would pass through ProductSystemDao.

File db_dir = new File("/Users/lando/openLCA-data-1.4/databases/ecoinvent_38_cutoff_lci_bck_for_java");
Derby db = new Derby(db_dir);
String query = "SELECT id, CUTOFF, description, last_change, name, ref_id, target_amount, version, f_category, f_reference_exchange, f_reference_process, f_target_flow_property_factor, f_target_unit FROM tbl_product_systems WHERE (name = 'Water bottle production' )";

List<String> productSystems = new ArrayList<>();
NativeSql.on(db).query(query, result -> {
      productSystems.add(result.getString("REF_ID"));
      return true;
});

If I try to retrive processes by means of ProcessDao...

File db_dir = new File("/Users/lando/openLCA-data-1.4/databases/ecoinvent_38_cutoff_lci_bck_for_java");
Derby db = new Derby(db_dir);
ProcessDao pDao = new ProcessDao(db);
List<Process> pss = pDao.getForName("Water bottle production");

I got a similar error.

Error Code: 30000
Call: SELECT id, default_allocation_method, description, dq_entry, infrastructure_process, last_change, last_internal_id, library, name, process_type, ref_id, tags, version, f_category, f_process_doc, f_dq_system, f_exchange_dq_system, f_location, f_quantitative_reference, f_social_dq_system FROM tbl_processes WHERE (name = ?)
    bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Process sql="SELECT id, default_allocation_method, description, dq_entry, infrastructure_process, last_change, last_internal_id, library, name, process_type, ref_id, tags, version, f_category, f_process_doc, f_dq_system, f_exchange_dq_system, f_location, f_quantitative_reference, f_social_dq_system FROM tbl_processes WHERE (name = ?)")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:391)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:264)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
    at org.openlca.core.database.Query.getAll(Query.java:68)
    at org.openlca.core.database.Query.getAllForName(Query.java:51)
    at org.openlca.core.database.RootEntityDao.getForName(RootEntityDao.java:239)
    ... 1 more
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.2.v202107160933): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Column 'LIBRARY' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'LIBRARY' is not a column in the target table.

Thank you in advance.

[msrocka]

You need to upgrade the database so that the structure fits to the version of the Java API you are using:

Upgrades.on(db);

[lando84]

Is this a function available on OpenLCA Java API?

[msrocka]

yes: https://github.com/GreenDelta/olca-modules/blob/master/olca-core/src/main/java/org/openlca/core/database/upgrades/Upgrades.java#L30

[lando84]

Thank you very much! It works!!!!!!

[lando84]

I got a new related issue: once I upgraded the DB by means of Upgrades.on(db), the db becomes not openable via OpenLCA app with the following error: "The given database is newer than this openLCA version". If I reload a db from scratch, I update it by means of OpenLCA and I try to get the db version by means of getVersion() method I see 9 instead 11 --> so this implies I should execute Upgrades.on(db) making the db not openable by openLCA.

[msrocka]

yes, if you run Upgrades.on from the API, you need to use a matching openLCA version if you want to use this database in the user interface then. You can check the version via IDatabase.CURRENT_VERSION, e.g. in the Python editor of openLCA:

from org.openlca.core.database import IDatabase

print(IDatabase.CURRENT_VERSION)