Insert data from one database into another
guido_enexis Apr 11, 2017 8:36 AMHi,
I would like to use squirrel or another sql client to insert data from one database into another database using a virtual database.
I have created a demo for testing purposes.
The demo consists of two azure sql databases (SVT and ENEXIS). These are connected to a vdb using JDBC Datasources (jdbc sql server driver - sqljdbc4.jar)
I have created a table 'TEST_DV_INSERT' in the database SVT. I want to insert data from another database ENEXIS. This fails:
Insert into B_VIRTUAL_BASE_LAYER.TEST_DV_INSERT (waarde) select CONVERT(T1.BOUWJAAR,INTEGER) from A_SRC_ENEXIS.DWH_ASSET_ESTATION T1 FETCH FIRST 1 ROWS ONLY
;
-- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 A_SRC_SVT_DATALAKE_SQL:
-- TEIID11009 java.sql.SQLException: javax.resource.ResourceException:
-- IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@60be9ed5[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@434716ab connection handles=0 lastUse=1491904370206 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@4aea5a21 pool internal context=SemaphoreArrayListManagedConnectionPool@e20e092[pool=SVT_DATALAKE_SQL_DS] xaResource=LocalXAResourceImpl@229226d8[connectionListener=60be9ed5 connectionManager=197dcfd warned=false currentXid=null productName=Microsoft SQL Server productVersion=12.00.304 jndiName=java:/SVT_DATALAKE_SQL_DS] txSync=null]
-- SQLState: 50000
-- ErrorCode: 30504
However if i try the same thing inserting data from a table in the SVT database into the TEST_DV_INSERT in table which resides also in SVT database everything is fine:
Insert into B_VIRTUAL_BASE_LAYER.TEST_DV_INSERT (waarde)
select convert(substring(ean,1,2),integer) from "A_SRC_SVT_DATALAKE_SQL".frdet_eans WHERE EAN <> '' FETCH FIRST 1 ROWS ONLY
-- Works fine, insert data from one table in an Azure SQL database into another table in that same database.
I am quite new to the subject so i have a couple of questions....
While searching the internet it seems that i have a to use XA Datasources instead of regular datasources. Is this correct?
And if so, do i have to create XA Datasources for all source databases to the vdb?
And do i have to create an XA datasource for the vdb (for squirrel to be able to connect) as well?
I am using JBOSS Datavirtualization 6.3.0.
Thanks in advance!
Regards,
Guido