Yep, me again,
First of all I would like to express my gratitude to all volunteers for their helpfulness in incredible speed of responses.
Now to backups...
The split database folder is just wonderful as it makes the whole lot portable. I think the magic is done via some url construct which has forward and backwards slashes, sort of double dutch for me.
As part of system maintenance options I have a button to generate a timestamped backup and another button to generate an sql script. And with that there are other backups associated with running checkpoint and backups associated with system crashes.
The crash backup is located in the User directory unless specified otherwise in tools options
The auto checkpoint places the backup inside the database folder and is recycled
My button for versioning SQL = "BACKUP DATABASE TO 'C:/MyPNSA/Backups/' BLOCKING NOT COMPRESSED" puts it there and creates the directory if not existent
The Script button SQL = "SCRIPT 'C:\MyPNSA\sFiles\SQLscript.txt'" puts it there provided the directory exists, if not it throws up an error.
The problem with the buttons is that the path is hardcoded unlike the splitdb folder.
Is there a way for the path to be always inside the db folder in the event the folder is moved to drive e f g z etc?
Thanks
[Solved] Backup and SQL Script file location
[Solved] Backup and SQL Script file location
Last edited by robleyd on Fri Sep 13, 2019 3:57 am, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Backup and SQL Script file location
Included is a SQL statement, which, I think will give you what you are asking for.
Perhaps, you will have to modify it a bit, but, if not a final answer, it is a good place to start.
Query below is SAME AS ABOVE withOUT line breaks for readability!
Explanation:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Perhaps, you will have to modify it a bit, but, if not a final answer, it is a good place to start.
Code: Select all
Select
'"SCRIPT ''' || SUBSTRING( REPLACE(INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."VALUE", 'file:', SPACE(0)), 1, LENGTH(REPLACE(INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."VALUE", 'file:', SPACE(0)) ) - LOCATE('\', REVERSE(REPLACE(INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."VALUE", 'file:', SPACE(0)) )) ) || '\SQLscript.txt''"'
From INFORMATION_SCHEMA.SYSTEM_SESSIONINFO
Where INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."KEY" = 'DATABASE'
Query below is SAME AS ABOVE withOUT line breaks for readability!
Code: Select all
Select '"SCRIPT ''' || SUBSTRING( REPLACE(INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."VALUE", 'file:', SPACE(0)), 1, LENGTH(REPLACE(INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."VALUE", 'file:', SPACE(0)) ) - LOCATE('\', REVERSE(REPLACE(INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."VALUE", 'file:', SPACE(0)) )) ) || '\SQLscript.txt''"' From INFORMATION_SCHEMA.SYSTEM_SESSIONINFO Where INFORMATION_SCHEMA.SYSTEM_SESSIONINFO."KEY" = 'DATABASE'
- Information is taken from HSQL table INFORMATION_SCHEMA.SYSTEM_SESSIONINFO
Bringing back the data of "VALUE" Where "KEY" is 'DATABASE' contains the directory and file name you are logged in on. - HSQL Version 2 ( 2.5.0 in your case ) functions include:
- SUBSTRING
- REPLACE
- SPACE
- REVERSE
- LOCATE
- LENGTH
- || for concatenation
- Manipulating the result of this Query, to remove file: at the start, and, to remove the file name at the end, AND, adding the other text you want, will result in the SQL command you are looking for ( or to investigate ).
- I strongly encourage you to TEST this out by running the SQL statement as a Query, to see what you get, and, then adding it to your macro when appropriate.
- Your macro, will probably have to include code to delete ( remove ) any file that might already exists with that name, for the SCRIPT command to execute successfully.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Backup and SQL Script file location
Thank you Sliderule, will give it a shot
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Backup and SQL Script file location
An alternative approach
Each time a database with a JDBC (Split) connection opens it automatically runs a macro found at
Tools>Macros>Organize Macros>OpenOffice Basic> Your Database Name >Standard>Embedded>Setup() runs to connect the Base front end to the tables.
That macro has 4 lines of Basic code that extract the path from the URL of the database so I used that approach in the following macro
A similar macro for saving the script file using
would also generate an error if there is no sFiles subdirectory
To resolve that problem use the following
Each time a database with a JDBC (Split) connection opens it automatically runs a macro found at
Tools>Macros>Organize Macros>OpenOffice Basic> Your Database Name >Standard>Embedded>Setup() runs to connect the Base front end to the tables.
That macro has 4 lines of Basic code that extract the path from the URL of the database so I used that approach in the following macro
Code: Select all
REM ***** BASIC *****
Rem Run macro from an Approve Action Event on a Push Button cointrol
Sub SaveDatabase (oEv as Object)
Dim sPathURL as String
Dim sPath as String
Dim sName as String
Dim strSQL as String
Dim iLen as Integer
DIM oDoc as Object
Dim oConnection as Object
Dim oStmt as Object
REM Since this is invoked by a button, use the button event to find the database document
oDoc = oEv.Source.Model
While oDoc.ImplementationName <> "com.sun.star.comp.dba.ODatabaseDocument"
oDoc = oDoc.Parent
Wend
REM got the document now extract sPath from the URL
sPathURL = oDoc.URL
sPath = ConvertFromURL(sPathURL)
sName = ThisDatabaseDocument.Title
iLen = InStr(sPath, sName)
sPath = Left(sPath, iLen-1)
REM using sPath build SQL statement
strSQL = "BACKUP DATABASE TO " & "'" & sPath & "" & "Backups\' BLOCKING NOT COMPRESSED"
REM now get active connection so a Base Statement service can be used to execute the SQL command
oConnection = oDoc.CurrentController.ActiveConnection
oStmt = oConnection.createStatement()
oStmt.execute(strSQL)
End Sub
A similar macro for saving the script file using
Code: Select all
strSQL = "SCRIPT " & "'" & sPath & "" & "sFiles\SQLscript.txt'"
To resolve that problem use the following
Code: Select all
strSQL = "SCRIPT " & "'" & sPath & "" & "sFiles\SQLscript.txt'"
If NOT FileExists(sPath & "sFiles\") Then
MkDir sPath & "sFiles\"
End If
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: [Solved]Backup and SQL Script file location
Thank you all!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend