Copying data from one resultset to another fails

[ztminhas]

Good Afternoon,
I have made two tables of the same structure, TBL_TEST_SRC and TBL_TEST_DEST.

The structure of both tables are the same and in both tables column f1 is the primary key. f1 is of type INTEGER, while the rest are of type VARCHAR.

I am trying to make an attempt at mimicking what I would do in VBA in a similar situation.

1. open two recordsets
2. copy data from the source recordset which would normally be Oracle to the destination recordset which would normally be Access.
3. This would be done by cycling through each row of the source recordset and then from each row, cycling through each column and then assigning the value of each data point to a new row in destination.

As long as the name and the data type for each column is exactly the same in both recordsets, this process is seemless.

The problem I have is that values don’t seem to properly save in the destination table. Here is a screenshot of the error I get:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: firebird_sdbc error:
*violation of PRIMARY or UNIQUE KEY constraint "INTEG_4" on table "TBL_TEST_DEST"
*Problematic key value is ("f1" = 0)
caused by
'isc_dsql_execute'
.

Here is the code:

Sub Main1
	con = getDbConnection(ThisDatabaseDocument)
	set fp =con.prepareStatement("delete from TBL_TEST_DEST")
	fp.execute()
	set rowset1= getResultSetForward(con,  "select f1,f2,f3,f4,f5 from TBL_TEST_SRC")
	set rowset2= getResultSet(con,  "select f1,f2,f3,f4,f5 from TBL_TEST_DEST")
	While rowset1.Next
		rowset2.moveToInsertRow()
	   	For Each fld In rowset1.columns
	   		colName= fld.Name
	   		set col = rowset2.columns(colName)
   			col.value = fld.value
	   	Next fld
	rowset2.insertrow()
	wend
End Sub


sub getDbConnection(derDb As object) As object 
Dim db As object
	derDb.CurrentController.connect()
	db = derDb.DataSource
	getDbConnection= db.getConnection("","")
End Sub

Function getResultSetForward(con As object, sql As String) As Object
	set getResultSetForward = CreateUNOService("com.sun.star.sdb.RowSet") 	
	With getResultSetForward
		.activeconnection = con
	    .CommandType = com.sun.star.sdb.CommandType.COMMAND
	    .Command =sql
	    .ResultSetType= com.sun.star.sdbc.ResultSetType.FORWARD_ONLY
		.ResultSetConcurrency=com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY
	    .execute()
	 End with
End Function
Function getResultSet(con As object, sql As String) As Object
	set getResultSet = CreateUNOService("com.sun.star.sdb.RowSet") 	
	With getResultSet
		.activeconnection = con
	    .CommandType = com.sun.star.sdb.CommandType.COMMAND
	    .Command =sql
	    .ResultSetType= com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
		.ResultSetConcurrency=com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE
	    .execute()
	 End with
End Function

[RobertG]

Why don’t yo start with

INSERT INTO TBL_TEST_DEST SELECT * FROM TBL_TEST_SRC

The error, which has been shown, says: Duplicate value for f1.

[ztminhas]

Good Afternoon,

I am pretty competent with SQL. I know insert from select. I am trying to mimic getting data from a different source (as mentioned could be oracle) and copy that data to the destination.

[ztminhas]

Hello,

I have found a solution. Here is the modified code:

Sub Main1
	con = getDbConnection(ThisDatabaseDocument)
	set fp =con.prepareStatement("delete from TBL_TEST_DEST")
	fp.execute()
	set rowset1= getResultSetForward(con,  "select F1,F2,F3,F4,F5 from TBL_TEST_SRC")
	set rowset2= getResultSet(con,  "select F1,F2,F3,F4,F5 from TBL_TEST_DEST")
	While rowset1.Next
		rowset2.moveToInsertRow()
	   	For Each colSrc In rowset1.columns
	   		rowset2.columns(rowset2.findColumn(colSrc.name)-1).value = colSrc.value
		Next colSrc
		rowset2.insertrow()
	wend
End Sub

As you can see this is not exactly like VBA however it is close enough. With this I can take data from different sources, and copy them into Base. Hope this information is helpful to others. Thank you.

[Wanderer]

@ztminhas

I can take data from different sources

I remember also an example in the Guide for Base to copy (sync?) data between two different registered databases. I don’t know, if it is in the english version. I remember some unexpected issues concerning charsets (my language has special letters…).
.
How do you avoid the problem with a double Id ? If I read the initial error right this seemed the reason to fail for the code. So I’d guessed you’d need either some UPSERT or fixing Ids. I see you cleaned the destination in this case, but this limits the code to “single run only”

Edit: I obviously missed the delete in your first code. So there should not be a problem with double Id’s