Ask Your Question
0

Copying Tables between databases

asked 2020-06-06 07:10:35 +0100

gkick gravatar image

updated 2020-06-06 07:12:39 +0100

Hi all,

The latest BaseGuide 6.2 pdf Chapter 9 page 389 has a description and sample code of how to import a table from database A to database B and how to append records from the source.

I am not clear from the documentation if this code is designed for embedded or external dbs or both.

I have created a quick mockup using HSQL2.5 backends, however the code copied from the original documentation throws up an error message. Tried to rem out the duplicate declaration to no success.

In the linked mockup DummyB is supposed to import from DummyA. Has anyone come across this feature? If that could be made workable, that would be fantastic because it would open up the door to synchronise tables between dbs( a la replication light) assuming no one touches the source db. Anyone able to spot the problem with the code ? Thanks for your thoughts and ideas. Please keep save.

https://www.dropbox.com/s/4p6mlkmhbbj...

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-06-06 21:30:02 +0100

Ratslinger gravatar image

updated 2020-06-06 23:54:20 +0100

Hello,

Did not bother with your attachments. Please just zip & post here changing the ext to .odb and note it must be changed to .zip

Typically don't deal with rar files.

The code has a number of naming errors. Even after correcting this nothing appears to happen. From the appearance of the code it is simply using SQL to read records in one table and write to another. May be a bit of code there for updating but to me not worth the effort.

This is not a new concept. Even if this worked (and the process seems backward) there is a matter of keeping things synced.

Edit:

Got the code to work. A number of errors in the documentation. Tested with HSQLDB embedded to HSQLDB embedded.

Here is the corrected code:

Sub DataCopy
Dim oDatabaseContext As Object
Dim oDatasource As Object
Dim oDatasourceZiel As Object
Dim oConnection As Object
Dim oConnectionZiel As Object
Dim oDB As Object
Dim oSQL_Command As Object
Dim oSQL_CommandTarget As Object
Dim oResult As Object
Dim oResultTarget As Object
Dim stSql As String
Dim stSqlTarget As String
Dim inID As Integer
Dim inIDTarget As Integer
Dim stName As String
Dim stTown As String
oDB = ThisComponent.Parent
stDir = Left(oDB.Location,Len(oDB.Location)-Len(oDB.Title))
          Rem Modify to your odb
stDir = ConvertToUrl(stDir & "TargetDB.odb")
oDatasource = ThisComponent.Parent.CurrentController
If Not (oDatasource.isConnected()) Then
oDatasource.connect()
End If
oConnection = oDatasource.ActiveConnection()
oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDatasourceTarget = oDatabaseContext.getByName(stDir)
oConnectionTarget = oDatasourceTarget.GetConnection("","")
oSQL_Command = oConnection.createStatement()
        Rem Modify to your table
stSql = "SELECT * FROM ""CUSTOMER"""
oResult = oSQL_Command.executeQuery(stSql)
While oResult.next
inID = oResult.getInt(1)
stName = oResult.getString(2)
stTown = oResult.getString(3)
oSQL_CommandTarget = oConnectionTarget.createStatement()
        Rem Modify to your table
stSqlTarget = "SELECT ""ID"" FROM ""CUSTOMER"" WHERE ""ID"" = '"+inID+"'"
'oResultTarget = oSQL_CommandZiel.executeQuery(stSqlTarget) '
oResultTarget = oSQL_CommandTarget.executeQuery(stSqlTarget)
inIDTarget = - 1
While oResultTarget.next
inIDTarget = oResultTarget.getInt(1)
Wend
If inIDTarget = - 1 Then
        Rem Modify to your table
stSqlZiel = "INSERT INTO ""CUSTOMER"" (""ID"",""NAME"",""ADDRESS"") VALUES ('"+inID+"','"+stName+"','"+stTown+"')"
oSQL_CommandTarget.executeUpdate(stSqlZiel)
End If
Wend
End Sub
edit flag offensive delete link more

Comments

Oh well, thanks anyway

gkick gravatar imagegkick ( 2020-06-06 22:43:23 +0100 )edit

Was just looking at the code again and it is missing some logic. Supposedly meant to copy records in one table missing in another table. Has no concern for existing records being up-to-date.

Seems to be better to simply empty the receiving table of records and then just copy all. Of course this could be a problem in very large tables.

Ratslinger gravatar imageRatslinger ( 2020-06-06 22:57:48 +0100 )edit

@gkick,

This is not new in the documentation. It is also in Chapter 9 of the v5.x documentation. It is wrong there too. Surprisingly, the 6.2 documentation was noted as being translated from German. I have not found this routine in that version. Also there are items in the German version not in the translated version.

Ratslinger gravatar imageRatslinger ( 2020-06-07 01:57:02 +0100 )edit

Yep, although stuff can get lost in translation and does, perhaps a little QC issue. Anyway, thanks for having a second look. Guess it could be adapted to work with back ends as well, only problem I can see is separate folder structure and a fight between 2 hsql jars.

gkick gravatar imagegkick ( 2020-06-07 04:15:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-06-06 07:10:35 +0100

Seen: 42 times

Last updated: Jun 06 '20