[Solved] Backup and SQL Script file location

[gkick]

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

[Sliderule]

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.

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'

Explanation:

1. 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.
2. HSQL Version 2 ( 2.5.0 in your case ) functions include:
   1. SUBSTRING
   2. REPLACE
   3. SPACE
   4. REVERSE
   5. LOCATE
   6. LENGTH
   7. || for concatenation
3. 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 ).
4. 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.
5. 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.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[gkick]

Thank you Sliderule, will give it a shot

[UnklDonald418]

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

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'" 

would also generate an error if there is no sFiles subdirectory
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

[gkick]

Thank you all!