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

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 section is not for asking questions about writing your own macros.
Post Reply
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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

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

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 280 times
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
Post Reply