Re: Using database Views
The SQL is not liked by HSQLDB, works fine with MySQL as you say, what happens if you just remove 'AS'? I've not used mine against HSQLDB . On Thu, Feb 15, 2018 at 10:49 PM, Smitha N wrote: > Hello Steve, > > Thanks for the input. I tried creating viewModel in similar fashion. > > But I face issues when I try to run the integration tests for this class. > > I get issues creating the view with the below mentioned errors: > > > Error thrown executing CREATE VIEW `usersforportal` AS SELECT `users`.id AS > id,`users`.helenaam AS helenaam,`users`.email AS email FROM `users` WHERE > `users`.actief = 1 AND `users`.bedrijf_id = 1 AND `users`.email is not null > : unexpected token: required: AS > java.sql.SQLSyntaxErrorException: unexpected token: required: AS > at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) > at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) > at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) > at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) > > > When I try the same syntax on MYSQL workbench , it works fine and there are > no errors. > > Somehow these issues occur while running the integration tests with > in-memory database. > > Any help is much appreciated. > > Regards > Smitha > > On Wed, Feb 14, 2018 at 10:17 PM, Stephen Cameron < > steve.cameron...@gmail.com> wrote: > > > Hi, > > > > Using a DataNucleus capability rather than an Apache Isis one. > > > > http://www.datanucleus.org/products/accessplatform_3_1/rdbms/views.html > > > > You can use annotations in your class instead of a jdo config file, I > > copied examples from Estatio to get started. > > > > But here is one I've created: > > https://github.com/Stephen-Cameron-Data-Services/isis- > > chats/blob/master/reports/src/main/java/au/com/scds/chats/ > dom/report/view/ > > ActivityAttendanceSummary.java > > > > The class can be seen by making your view class a ViewModel. > > > > I don't think you can use persistable views, may be wrong on that. > > > > I've had a problem with views, that DN tries to create them before the > > underlying tables are created, never resolved that but I should. > > > > If I were to start afresh I'd not use them unless absolutely necessary > for > > performance, instead use queries on my domain objects and write code, > then > > you have easier refactoring. > > > > Steve > > > > > > > > On Thu, Feb 15, 2018 at 2:26 AM, Smitha N > wrote: > > > > > Hi, > > > > > > In our application, there is a need to use database views and I was > > > wondering what is the best way to implement it using the framework. > > > > > > How do we map the database view fields to the Object? Can we use > > ViewModels > > > with persistanceCapability and refer to the view? > > > > > > If above scenario is valid, how do we make such scenarios work for > > > integration testing? > > > If not please advise on how to use database views. > > > > > > Regards > > > Smitha > > > > > >
Re: Using database Views
Hello Steve, Thanks for the input. I tried creating viewModel in similar fashion. But I face issues when I try to run the integration tests for this class. I get issues creating the view with the below mentioned errors: Error thrown executing CREATE VIEW `usersforportal` AS SELECT `users`.id AS id,`users`.helenaam AS helenaam,`users`.email AS email FROM `users` WHERE `users`.actief = 1 AND `users`.bedrijf_id = 1 AND `users`.email is not null : unexpected token: required: AS java.sql.SQLSyntaxErrorException: unexpected token: required: AS at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) When I try the same syntax on MYSQL workbench , it works fine and there are no errors. Somehow these issues occur while running the integration tests with in-memory database. Any help is much appreciated. Regards Smitha On Wed, Feb 14, 2018 at 10:17 PM, Stephen Cameron < steve.cameron...@gmail.com> wrote: > Hi, > > Using a DataNucleus capability rather than an Apache Isis one. > > http://www.datanucleus.org/products/accessplatform_3_1/rdbms/views.html > > You can use annotations in your class instead of a jdo config file, I > copied examples from Estatio to get started. > > But here is one I've created: > https://github.com/Stephen-Cameron-Data-Services/isis- > chats/blob/master/reports/src/main/java/au/com/scds/chats/dom/report/view/ > ActivityAttendanceSummary.java > > The class can be seen by making your view class a ViewModel. > > I don't think you can use persistable views, may be wrong on that. > > I've had a problem with views, that DN tries to create them before the > underlying tables are created, never resolved that but I should. > > If I were to start afresh I'd not use them unless absolutely necessary for > performance, instead use queries on my domain objects and write code, then > you have easier refactoring. > > Steve > > > > On Thu, Feb 15, 2018 at 2:26 AM, Smitha N wrote: > > > Hi, > > > > In our application, there is a need to use database views and I was > > wondering what is the best way to implement it using the framework. > > > > How do we map the database view fields to the Object? Can we use > ViewModels > > with persistanceCapability and refer to the view? > > > > If above scenario is valid, how do we make such scenarios work for > > integration testing? > > If not please advise on how to use database views. > > > > Regards > > Smitha > > >
Re: Using database Views
Hi, Using a DataNucleus capability rather than an Apache Isis one. http://www.datanucleus.org/products/accessplatform_3_1/rdbms/views.html You can use annotations in your class instead of a jdo config file, I copied examples from Estatio to get started. But here is one I've created: https://github.com/Stephen-Cameron-Data-Services/isis-chats/blob/master/reports/src/main/java/au/com/scds/chats/dom/report/view/ActivityAttendanceSummary.java The class can be seen by making your view class a ViewModel. I don't think you can use persistable views, may be wrong on that. I've had a problem with views, that DN tries to create them before the underlying tables are created, never resolved that but I should. If I were to start afresh I'd not use them unless absolutely necessary for performance, instead use queries on my domain objects and write code, then you have easier refactoring. Steve On Thu, Feb 15, 2018 at 2:26 AM, Smitha N wrote: > Hi, > > In our application, there is a need to use database views and I was > wondering what is the best way to implement it using the framework. > > How do we map the database view fields to the Object? Can we use ViewModels > with persistanceCapability and refer to the view? > > If above scenario is valid, how do we make such scenarios work for > integration testing? > If not please advise on how to use database views. > > Regards > Smitha >