Re: PK Generation and FrontBase

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 <[email protected]> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]><mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]><mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>>
> wrote:
> 
> Sorry, please read
> 
> « Migrate from FrontbaseSQL to PostgreSQL »  😄
> 
> Jérémy
> 
> Le 16 avr. 2021 à 10:04, Jérémy DE ROYER <[email protected]<mailto:[email protected]>>
> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]>
> 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 <[email protected]<mailto:
> [email protected]>> 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 <
> [email protected]<mailto:[email protected]>>
> 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 <[email protected]
> <mailto:[email protected]>>
> 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
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>