I have three instances. LIVE is the real life set of accounts and analyses. TEST is whatever copy I take of the former in order to develop or amend code, alter the database structures, and so on. UPGRADES is a location where I can evaluate new versions of the database driver and the AOO software without impacting the foregoing.
The above disclaimers accepted, it's understood that the structure of all three Instances is identical and the means of populating TEST and UPGRADES is by the magic of the HSQL “backup database” command and the 7-Zip File Manager.
Clearly, copying from LIVE to TEST leaves us no internal evidence of the location in which we are working.
What I do is to extract the location from the AOO database (“XXXX.odb”) path and store it in a column of the table USEFUL.
To confirm this, I also take a timestamp (formatting it for readability) and store that in a column of the USEFUL table.
The first is identified by its primary key column value “-sysInstance” and the second by “-timestamp”.
The two columns of the USEFUL table are called SHORTNAME (PK) and DETAILS
The code follows
Edit: *FIXED* |
Code: Select all
REM ***** BASIC *****
global sysInstance as string ' (rem) I am not sure if I need this but it may be needed for a later development
SUB Start
dim oDatasource as object
dim oSettings as object
dim sURL as string
DocURL = ThisComponent.getURL()
GlobalScope.BasicLibraries.loadLibrary("Tools")
DocDir=DirectoryNameoutofPath(DocURL, "/")
FileName=Dir(DocURL, 0)
sysInstance = GetFileNameWithoutExtension(DocURL, "/")
cPath = "C:\Users\Accounts\Bookkeeping\" & sysInstance & "\driver/hsqldb.jar"
sURL = ConvertToURL(cPath)
oDatasource = ThisComponent.Datasource
oSettings = oDatasource.Settings
oSettings.JavaDriverClassPath = sURL
dim databaseContext
dim databaseConnection
dim dataSource as object
dim Statement
dim statementText as string
dim execTimeStamp as string
dim ShortDayName as string
dim xxx as integer
databaseContext=CreateUnoService("com.sun.star.sdb.DatabaseContext")
dataSource = databaseContext.getByName(sysInstance)
databaseConnection = dataSource.GetConnection("user name", "user password")
Statement = databaseConnection.createStatement()
CompatibilityMode(True)
ShortDayName = WeekDayName(WeekDay(now),true)
execTimeStamp = "Opened at " & FormatDateTime(now,4) & " on " & ShortDayName & " " & FormatDateTime(now,2)
statementText = _
" merge into USEFUL using (values('-sysInstance', '" & sysInstance & _
"')) as THIS_SYSTEM_IS(SHORTNAME, DETAILS)" & _
" on USEFUL.SHORTNAME = THIS_SYSTEM_IS.SHORTNAME" & _
" when matched then update set USEFUL.DETAILS = THIS_SYSTEM_IS.DETAILS" & _
" when not matched then insert values THIS_SYSTEM_IS.SHORTNAME, THIS_SYSTEM_IS.DETAILS"
xxx = Statement.executeUpdate (statementText)
statementText = _
" merge into USEFUL using (values('-timestamp', '" & execTimeStamp & _
"')) as THIS_SYSTEM_IS(SHORTNAME, DETAILS)" & _
" on USEFUL.SHORTNAME = THIS_SYSTEM_IS.SHORTNAME" & _
" when matched then update set USEFUL.DETAILS = '" & execTimeStamp & "'" & _
" when not matched then insert values THIS_SYSTEM_IS.SHORTNAME, '" & execTimeStamp & "'"
xxx = Statement.executeUpdate (statementText)
end sub
Edit: A note on how the merge statement parses into SQL from Basic:
Code: Select all
I trust this is clear enough |