This function of DfLib is pretty awesome… I'm going to have to try this out for
migrating my remaining MySQL DBs to Postgres :).
- hugi
> On 19 Apr 2021, at 20:57, Andrus Adamchik <aadamc...@gmail.com> wrote:
>
> The "power of DFLib" should help with this too :) Its main focus is
> manipulating data in DataFrame columns after all, so you can adapt the data
> you got from FB into something that can be saved to PG. E.g. [1]:
>
> data = data.convertColumn("col_name", (Clob c) -> convertClob(c));
>
> // implement this function
> String convertClob(Clob c) { ... }
>
> Andrus
>
> [1] https://nhl.github.io/dflib-docs/#changing-column-type
>
>
>
>> On Apr 19, 2021, at 4:37 PM, Jérémy DE ROYER <jeremy.dero...@ingencys.net>
>> wrote:
>>
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
