Can I use with any SQL database that supports JDBC?

[lcmuniz]

I'm trying to use ReplicaDB with the Caché database. I included the JDBC jar drive in the lib folder but it still doesn't work. The message "The database with scheme jdbc:Cache: was not found. Trying with standard JDBC manager" appears.

Do I need to do anything else?

[osalvador]

Hi @lcmuniz

I have been working on a "standard JDBC manager" with some limitations, but compatible with any JDBC database. Could you share the full log with verbose mode?

Thank you!

[lcmuniz]

Hey. Thanks for the answer. The problem seems to be with the SQL language that the Caché database uses, which does not have the OFFSET keyword.
See the run log:

2022-09-19 08:46:40,477 DEBUG ReplicaDB:68 ToolOptions{
        sourceConnect='jdbc:Cache://172.16.0.1:1972/db',
        sourceUser='user',
        sourcePassword='****',
        sourceTable='SQLUser.RHTabTipoServidor',
        sourceColumns='*',
        sourceWhere='',
        sourceQuery='',
        sinkConnect='jdbc:postgresql://172.16.0.2:5432/db',
        sinkUser='user',
        sinkPassword='****',
        sinkTable='cachedb.rhtabtiposervidor',
        sinkStagingTable='null',
        sinkStagingSchema='null',
        sinkStagingTableAlias='null',
        sinkColumns='*',
        sinkDisableEscape=false,
        sinkDisableIndex=false,
        sinkDisableTruncate=false,
        sinkAnalyze=false,
        jobs=1,
        BandwidthThrottling=0,
        quotedIdentifiers=false,
        fetchSize=100,
        help=false,
        version=false,
        verbose=true,
        optionsFile='.\conf\replicadb.conf',
        mode='complete',
        sentryDsn='null',
        sourceConnectionParams={driver=com.intersys.jdbc.CacheDriver},
        sinkConnectionParams={},
        sourceFileFormat='null',
        sinkFileformat='null'}
2022-09-19 08:46:42,657 WARN  ManagerFactory:79 The database with scheme jdbc:Cache: was not found. Trying  with standard JDBC manager
2022-09-19 08:46:42,661 INFO  SqlManager:370 Truncating sink table with this command: TRUNCATE TABLE cachedb.rhtabtiposervidor2
2022-09-19 08:46:43,254 INFO  ReplicaTask:35 Starting  TaskId-0
2022-09-19 08:46:43,254 WARN  ManagerFactory:79 The database with scheme jdbc:Cache: was not found. Trying  with standard JDBC manager
2022-09-19 08:46:43,444 INFO  SqlManager:128 TaskId-0: Executing SQL statement: SELECT * FROM SQLUser.RHTabTipoServidor OFFSET ?
2022-09-19 08:46:43,524 ERROR ReplicaTask:60 ERROR in TaskId-0 reading source table: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (:) encountered after end of query^SELECT * FROM SQLUser . RHTabTipoServidor OFFSET :%qpar>]
2022-09-19 08:46:43,525 ERROR ReplicaDB:136 Got exception running ReplicaDB:
java.util.concurrent.ExecutionException: java.sql.SQLException: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (:) encountered after end of query^SELECT * FROM SQLUser . RHTabTipoServidor OFFSET :%qpar>]
        at java.util.concurrent.FutureTask.report(FutureTask.java:122) ~[?:?]
        at java.util.concurrent.FutureTask.get(FutureTask.java:191) ~[?:?]
        at org.replicadb.ReplicaDB.processReplica(ReplicaDB.java:117) [ReplicaDB-0.12.3.jar:0.12.3]
        at org.replicadb.ReplicaDB.main(ReplicaDB.java:47) [ReplicaDB-0.12.3.jar:0.12.3]
Caused by: java.sql.SQLException: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (:) encountered after end of query^SELECT * FROM SQLUser . RHTabTipoServidor OFFSET :%qpar>]
        at com.intersys.jdbc.CacheConnection.getServerError(CacheConnection.java:1485) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.CacheConnection.processError(CacheConnection.java:1752) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.InStream.readMessage(InStream.java:320) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.InStream.readMessage(InStream.java:204) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.CachePreparedStatement.prepareInternal(CachePreparedStatement.java:700) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.CachePreparedStatement.prepare(CachePreparedStatement.java:661) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.CachePreparedStatement.<init>(CachePreparedStatement.java:147) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.CacheConnection.getOrCreatePossiblyShardedCachePreparedStatement(CacheConnection.java:2953) ~[cache-jdbc-2.0.0.jar:?]
        at com.intersys.jdbc.CacheConnection.prepareStatement(CacheConnection.java:2052) ~[cache-jdbc-2.0.0.jar:?]
        at org.replicadb.manager.SqlManager.execute(SqlManager.java:130) ~[ReplicaDB-0.12.3.jar:0.12.3]
        at org.replicadb.manager.SqlManager.execute(SqlManager.java:160) ~[ReplicaDB-0.12.3.jar:0.12.3]
        at org.replicadb.manager.StandardJDBCManager.readTable(StandardJDBCManager.java:69) ~[ReplicaDB-0.12.3.jar:0.12.3]
        at org.replicadb.ReplicaTask.call(ReplicaTask.java:58) ~[ReplicaDB-0.12.3.jar:0.12.3]
        at org.replicadb.ReplicaTask.call(ReplicaTask.java:15) ~[ReplicaDB-0.12.3.jar:0.12.3]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
        at java.lang.Thread.run(Thread.java:833) ~[?:?]
2022-09-19 08:46:43,543 INFO  ReplicaDB:54 Total process time: 3094ms

When I run SELECT * FROM SQLUser.RHTabTipoServidor OFFSET 1 on the databse, it returns the message

[%msg: < Input (:) encountered after end of query^SELECT * FROM SQLUser . RHTabTipoServidor OFFSET :%qpar>]

Caché SQL does not support LIMIT and OFFSET.
%VID and TOP can be used in Caché SQL instead of LIMIT / OFFSET:
https://cedocs.intersystems.com/ens20152/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_views#GSQL_C13474

[osalvador]

Hi @lcmuniz,

LIMIT and OFFSET are not supported by a large number of databases. ReplicaDB uses the LIMIT and OFFSET to replicate de data in parallel.

I think it is best for now to disable parallel processing for the standard JDBC and try to use only the most common instructions that most databases have.

Therefore, only the complete mode without parallel processing (aka jobs=1) will be supported. Is this valid for you now?

Regards