Hi Andrus, I was just testing the schema migration script so why not testing ?
After editing the transfer line as pg.tableSaver(table).mergeByPk().save(data); to avoid duplication as explained in your doc... It works really great 👍 The only one problem (there is always a problem) occured when conserting BLOB (FrontBaseSQL) to TEXT (PostgresSQL). It outputs the error above. Any simple idea ? Jérémy java.lang.RuntimeException: java.lang.RuntimeException: Error updating data in DB: Impossible de convertir une instance de type com.frontbase.jdbc.FBJClob vers le type String at com.nhl.dflib.jdbc.connector.tx.Tx.call(Tx.java:63) at com.nhl.dflib.jdbc.connector.saver.TableSaveStrategy.save(TableSaveStrategy.java:36) at com.nhl.dflib.jdbc.connector.TableSaver.save(TableSaver.java:89) at your.app.SellAndPepperDatabaseMigration.main(SellAndPepperDatabaseMigration.java:57) Caused by: java.lang.RuntimeException: Error updating data in DB: Impossible de convertir une instance de type com.frontbase.jdbc.FBJClob vers le type String at com.nhl.dflib.jdbc.connector.StatementBuilder.update(StatementBuilder.java:110) at com.nhl.dflib.jdbc.connector.saver.SaveViaInsert.doSave(SaveViaInsert.java:39) at com.nhl.dflib.jdbc.connector.saver.SaveViaUpsert.insert(SaveViaUpsert.java:105) at com.nhl.dflib.jdbc.connector.saver.SaveViaUpsert.doSave(SaveViaUpsert.java:52) at com.nhl.dflib.jdbc.connector.saver.SaveViaUpsert.doSave(SaveViaUpsert.java:37) at com.nhl.dflib.jdbc.connector.saver.TableSaveStrategy.lambda$save$1(TableSaveStrategy.java:36) at com.nhl.dflib.jdbc.connector.tx.Tx.call(Tx.java:56) ... 3 more Caused by: org.postgresql.util.PSQLException: Impossible de convertir une instance de type com.frontbase.jdbc.FBJClob vers le type String at org.postgresql.jdbc.PgPreparedStatement.cannotCastException(PgPreparedStatement.java:929) at org.postgresql.jdbc.PgPreparedStatement.castToString(PgPreparedStatement.java:918) at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:574) at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:935) at com.nhl.dflib.jdbc.connector.statement.DefaultColumnBinder.bind(DefaultColumnBinder.java:37) at com.nhl.dflib.jdbc.connector.statement.StatementBinder.bind(StatementBinder.java:38) at com.nhl.dflib.jdbc.connector.statement.UpdateStatementBatch.update(UpdateStatementBatch.java:40) at com.nhl.dflib.jdbc.connector.StatementBuilder.update(StatementBuilder.java:108) ... 9 more Caused by: java.sql.SQLException at com.frontbase.jdbc.FBJConnection.readLOB(Unknown Source) at com.frontbase.jdbc.FBJClob.getSubString(Unknown Source) at org.postgresql.jdbc.PgPreparedStatement.asString(PgPreparedStatement.java:738) at org.postgresql.jdbc.PgPreparedStatement.castToString(PgPreparedStatement.java:912) ... 15 more Le 19 avr. 2021 à 20:20, Andrus Adamchik <aadamc...@gmail.com<mailto:aadamc...@gmail.com>> a écrit : LinkMove will definitely work, but is targeting ongoing DB synchronization instead of a one-off migration. I would personally deal with it in a more low-tech way. Since you only need to do it once, the simplest tool is the best. I would use DFLib library [1] to read data from one DB and write to another. It was also presented in Frankfurt, and has evolved a lot since then. Here is an example: JdbcConnector fb = Jdbc.connector("frontbase_url").username(..).password(..).build(); JdbcConnector pg = Jdbc.connector("pg_url").username(..).password(..).build(); for(String table : tables) { DataFrame data = fb.tableLoader(table).load(); pg.tableSaver(table).save(data); } As you see it can literally be done in a few lines of code and requires no model. Andrus [1] https://nhl.github.io/dflib-docs/#jdbc On Apr 16, 2021, at 6:00 AM, Michael Gentry <blackn...@gmail.com<mailto:blackn...@gmail.com>> wrote: Hi Jérémy, I've never used it, but Andrus (and others) have a project called LinkMove which may work: https://github.com/nhl/link-move I don't see a way to re-use a Cayenne model as the source AND the target, but perhaps that is also an option (instead of the XML source), too. On Fri, Apr 16, 2021 at 4:14 AM Jérémy DE ROYER <jeremy.dero...@ingencys.net<mailto:jeremy.dero...@ingencys.net>> wrote: Sorry, please read « Migrate from FrontbaseSQL to PostgreSQL » 😄 Jérémy Le 16 avr. 2021 à 10:04, Jérémy DE ROYER <jeremy.dero...@ingencys.net<mailto:jeremy.dero...@ingencys.net>> a écrit : Hi Hugi, Unfortunately, I think you’re right. I wanted to leave FrontbaseSQL too but as Andrus say, the meeting in Frankfurt help me to resolve some cases and finally we keep our softwares using FrontbaseSQL. According to your experience, is there an easy way to migrate/transfert from PostgreSQL to FrontBaseSQL ? It could respond to the request from developers to have access to programming tools with the database, not offered by FrontbaseSQL. Thank’s for this highlight, Jérémy Le 16 avr. 2021 à 09:53, Hugi Thordarson <h...@karlmenn.is<mailto:h...@karlmenn.is>> a écrit : Hi Jérémy, I believe you hit a bug in the FrontBase JDBC driver: https://issues.apache.org/jira/browse/CAY-2574 < https://issues.apache.org/jira/browse/CAY-2574> Andrus wrote to FrontBase about this after our Frankfurt meetup: https://mail-archives.apache.org/mod_mbox/cayenne-dev/201905.mbox/%3C52EC2486-4736-4854-AE49-A7CF77904E52%40objectstyle.org%3E <https://mail-archives.apache.org/mod_mbox/cayenne-dev/201905.mbox/browser I haven't seen anything from FB about this since this thread, perhaps you should give their support a check? I also wrote to them last year about their JDBC driver not working on more recent Java versions, but don't think they've fixed it. I ended up migrating that one last FrontBase app to Postgres. Cheers, - hugi On 15 Apr 2021, at 20:59, Jérémy DE ROYER <jeremy.dero...@ingencys.net> wrote: Hi John, I know that FrontbaseSQL is not commonly used but our all apps are based on. I’ve just : - created the model explained on the doc https://cayenne.apache.org/docs/3.0/modeling-single-table-inheritance.html - wrote the code below inside the ‘Application’ class of a simple woapp But yes, maybe Cayenne is not done to work with FrontbaseSQL… as it works well with PostregSQL. Thank’s for your answer and have a nice day, Jérémy Le 15 avr. 2021 à 22:50, John Huss <johnth...@gmail.com<mailto: johnth...@gmail.com>> a écrit : I don't think FrontBase is very commonly used. I would recommend submitting a minimal example of the problem and someone should be able to fix the issue then. On Thu, Apr 15, 2021 at 3:44 PM Jérémy DE ROYER < jeremy.dero...@ingencys.net<mailto:jeremy.dero...@ingencys.net>> wrote: Hello, I switched my model from FrontBase to PostgreSQL and it works great. Does someone use Cayenne 4.1 with FrontbaseSQL successfully ? Is there something specific to know ? Thank’s for any tip, Jérémy Le 14 avr. 2021 à 23:05, Jérémy DE ROYER <jeremy.dero...@ingencys.net <mailto:jeremy.dero...@ingencys.net>> a écrit : Hi all, I’m trying to find a solution for the (temporary ?) lack of horizontal inheritance so I did model vertical inheritance following : https://cayenne.apache.org/docs/3.0/modeling-vertical-inheritance.html using a Frontbase database. I did : - create the model, - generate the database, - fix lack of jars and other things like username, password, jdbc driver The demo code below starts well and connects to database... try { ServerRuntime cayenneRuntime = ServerRuntime.builder() .addConfig("cayenne-CayenneTest.xml") .build(); ObjectContext context = cayenneRuntime.newContext(); Book _newBook = context.newObject(Book.class); _newBook.setTitle("Nouveau Book"); _newBook.setCreated(new Date()); context.commitChanges(); EBook _newEBook = context.newObject(EBook.class); _newEBook.setTitle("Nouveau EBook"); _newEBook.setCreated(new Date()); _newEBook.setDownloadUrl("https://www.google.fr/";); context.commitChanges(); PaperBook _newPaperBook = context.newObject(PaperBook.class); _newPaperBook.setTitle("Nouveau PaperBook"); _newPaperBook.setCreated(new Date()); _newPaperBook.setShippingWeight(1000); context.commitChanges(); List<Book> _books = ObjectSelect.query(Book.class).select(context); System.out.println("** ALL **"); for (Book currentBook : _books) { System.out.println(currentBook.getTitle() + " created on " + currentBook.getCreated()); } } catch (Exception e) { System.out.println("An error occured : " + e.getMessage()); e.printStackTrace(); } …but throws this exception An error occured : [v.4.1 Jul 14 2020 10:40:45] Commit Exception org.apache.cayenne.CayenneRuntimeException: [v.4.1 Jul 14 2020 10:40:45] Commit Exception at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:774) at org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:691) at your.app.Application.<init>(Application.java:50) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at java.lang.Class.newInstance(Class.java:442) at com.webobjects.appserver.WOApplication.main(WOApplication.java:547) at your.app.Application.main(Application.java:25) Caused by: java.lang.NullPointerException at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:242) at org.apache.cayenne.access.jdbc.SQLTemplateAction.runWithNamedParametersBatch(SQLTemplateAction.java:179) at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:111) at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:273) at org.apache.cayenne.dba.frontbase.FrontBasePkGenerator.longPkFromDatabase(FrontBasePkGenerator.java:143) at org.apache.cayenne.dba.JdbcPkGenerator.generatePk(JdbcPkGenerator.java:220) at org.apache.cayenne.access.DataDomainInsertBucket.createPermIds(DataDomainInsertBucket.java:168) at org.apache.cayenne.access.DataDomainInsertBucket.appendQueriesInternal(DataDomainInsertBucket.java:76) at org.apache.cayenne.access.DataDomainSyncBucket.appendQueries(DataDomainSyncBucket.java:78) at org.apache.cayenne.access.DataDomainFlushAction.preprocess(DataDomainFlushAction.java:185) at org.apache.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:143) at org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:624) at org.apache.cayenne.access.DataDomain.onSyncNoFilters(DataDomain.java:594) at org.apache.cayenne.access.DataDomain$DataDomainSyncFilterChain.onSync(DataDomain.java:822) at org.apache.cayenne.tx.TransactionFilter.lambda$onSync$0(TransactionFilter.java:61) at org.apache.cayenne.tx.DefaultTransactionManager$BaseTransactionHandler.performInTransaction(DefaultTransactionManager.java:183) at org.apache.cayenne.tx.DefaultTransactionManager$BaseTransactionHandler.performInNewTransaction(DefaultTransactionManager.java:155) at org.apache.cayenne.tx.DefaultTransactionManager$NestedTransactionHandler.handle(DefaultTransactionManager.java:98) at org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:65) at org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:43) at org.apache.cayenne.tx.TransactionFilter.onSync(TransactionFilter.java:61) at org.apache.cayenne.access.DataDomain$DataDomainSyncFilterChain.onSync(DataDomain.java:821) at org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:581) at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:742) ... 9 more It seem’s to be related to pk generation so I try a select unqiue from Book in FrontBase and it worked lije a charm. Any idea ? Many thank’s, Jérémy