[Base] Store location and opened time in a Base table

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

[Base] Store location and opened time in a Base table

Postby misitu » Wed Nov 23, 2016 5:00 am

The purpose of this code is to set a timestamp and an identifier for whichever instance (LIVE, TEST, or UPGRADES) is in use. To run on opening an instance of the database.

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* 
. A separate attachment includes the same code together with some images and other explanations.

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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


The item 'sysInstance' is replaced by the run time value of sysInstance in Basic, i.e. in my case either LIVE or TEST or UPGRADES. It needs to be quoted by apostrophes in order to appear to SQL as a literal. In the Basic text string the preceding and trailing apostrophes form part of the preceding and following literals in quotation marks.

I trust this is clear enough :?: :!: 
Attachments
Code Snippet of Sub Start.ods
Supporting information including Code
(85.47 KiB) Downloaded 50 times
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 85
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 2 guests