Sorry Andrus but it does not work. I haven chosen the famous : - « alter column "INDEXYS"."INDEXYS_PARAMETRE_AVEC_CONTENU"."CONTENT" to varchar(8000); » (from CLOB to VARCHAR)
less expensive for a single use / migration Many thank’s for help, DFLib rocks ! Jérémy Le 20 avr. 2021 à 14:02, Andrus Adamchik <aadamc...@gmail.com<mailto:aadamc...@gmail.com>> a écrit : I suspected something like this, though wasn't sure how it would behave of FB. Different DBs handle it differently, but the issue is that a CLOB can not be "materialized" without access to the Connection that read it. There's a rudimentary transaction support in DFLib that should address this. You can modify your "load" code as follows: DataFrame data = Tx.newTransaction(fb).call(c -> { DataFrame data = c.tableLoader(currentTable).load(); return /* convert clob */ ); Here we are wrapping FB connector invocation and Clob resolving in a transaction. So hopefully the CLOB can be extracted error-free. Andrus On Apr 20, 2021, at 7:40 AM, Jérémy DE ROYER <jeremy.dero...@ingencys.net<mailto:jeremy.dero...@ingencys.net>> wrote: Hi, I update my code to use data.convertColumn but an IOException occures as soon as I try to read a Frontbase CLOB (like if the connection was closed ? Even if it’s not). Any idea ? I posted the code below if it may help Except that, that’s awesome and so simple. I remember this library at Frankfurt but I though it was mainly for statistics purposes. Jérémy Java Code == main.java == JdbcConnector fb = Jdbc.connector("jdbc:FrontBase://localhost/TestIndeXys_v30").driver(FBJDriver.class.getName()).userName("frontbase").build(); JdbcConnector pg = Jdbc.connector("jdbc:postgresql://localhost:5432/TestIndeXys_v30").driver(Driver.class.getName()).userName("postgres").password("postgres").build(); for (String currentTable : _tables) { DataFrame data = fb.tableLoader(currentTable).load(); if (Arrays.asList(data.getColumnsIndex().getLabels()).contains("CONTENT")) { data = data.convertColumn("CONTENT", (Clob c) -> _convertClob(fb.getConnection(), c) ); } pg.tableSaver(currentTable).mergeByPk().save(data); System.out.println("La table " + currentTable + " a été recopiée"); } additional méthod == private static String _convertClob(Connection connectionIn, Clob clobIn) { StringBuffer targetStringBuffer = new StringBuffer(); try { System.out.println("clobIn >" + clobIn.length()); System.out.println("connectionIn.isClosed() >" + connectionIn.isClosed()); // System.out.println("connectionIn.getSubString() >" + clobIn.getSubString(1L, (int) clobIn.length())); // // FBJInputStream // FBJClob final Reader reader = clobIn.getCharacterStream(); final BufferedReader bufferedReader = new BufferedReader(reader); int intValueOfChar; while(-1 != (intValueOfChar = bufferedReader.read())) { targetStringBuffer.append((char)intValueOfChar); } bufferedReader.close(); } catch (Exception e) { e.printStackTrace(); } return targetStringBuffer.toString(); } Exception == clobIn >0 connectionIn.isClosed() >false clobIn >170 connectionIn.isClosed() >false java.io.IOException at com.frontbase.jdbc.FBJInputStream.read(Unknown Source) at sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:284) at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:326) at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178) at java.io.InputStreamReader.read(InputStreamReader.java:184) at java.io.BufferedReader.fill(BufferedReader.java:161) at java.io.BufferedReader.read(BufferedReader.java:182) at your.app.SellAndPepperDatabaseMigration._convertClob(SellAndPepperDatabaseMigration.java:106) at your.app.SellAndPepperDatabaseMigration.lambda$0(SellAndPepperDatabaseMigration.java:75) at com.nhl.dflib.series.ColumnMappedSeries.doMaterialize(ColumnMappedSeries.java:50) at com.nhl.dflib.series.ColumnMappedSeries.materialize(ColumnMappedSeries.java:38) at com.nhl.dflib.series.ColumnMappedSeries.get(ColumnMappedSeries.java:25) at com.nhl.dflib.series.IndexedSeries.doMaterialize(IndexedSeries.java:62) at com.nhl.dflib.series.IndexedSeries.materialize(IndexedSeries.java:44) at com.nhl.dflib.series.IndexedSeries.get(IndexedSeries.java:31) at com.nhl.dflib.series.ObjectSeries.eq(ObjectSeries.java:260) at com.nhl.dflib.ColumnDataFrame.eq(ColumnDataFrame.java:592) at com.nhl.dflib.jdbc.connector.saver.SaveViaUpsert.update(SaveViaUpsert.java:122) at com.nhl.dflib.jdbc.connector.saver.SaveViaUpsert.doSave(SaveViaUpsert.java:98) 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) 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:78) FBJInputStream.read() == public int read() throws IOException { if (this.available <= 0L) { return -1; } else { if (this.buffer == null) { this.initBuffer(); } if (this.read == 0) { try { this.loadBuffer(this.nextOffset); } catch (Exception var2) { throw new IOException(var2.getMessage()); } } --this.read; --this.available; return this.buffer[this.position++] & 255; } } Le 19 avr. 2021 à 22:57, Andrus Adamchik <aadamc...@gmail.com<mailto:aadamc...@gmail.com><mailto:aadamc...@gmail.com>> a écrit : 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<mailto:jeremy.dero...@ingencys.net><mailto: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><mailto: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><mailto: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
