[Solved] Backup and SQL Script file location

Creating and using forms

[Solved] Backup and SQL Script file location

Postby gkick » Wed Sep 11, 2019 4:49 pm

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
Last edited by robleyd on Fri Sep 13, 2019 3:57 am, edited 2 times in total.
Reason: Add green tick
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 58
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Backup and SQL Script file location

Postby Sliderule » Thu Sep 12, 2019 4:02 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1190
Joined: Thu Nov 29, 2007 9:46 am

Re: Backup and SQL Script file location

Postby gkick » Thu Sep 12, 2019 5:48 pm

Thank you Sliderule, will give it a shot :super:
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 58
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Backup and SQL Script file location

Postby UnklDonald418 » Fri Sep 13, 2019 12:23 am

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   Expand viewCollapse view
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   Expand viewCollapse view
  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   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1213
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved]Backup and SQL Script file location

Postby gkick » Fri Sep 13, 2019 3:55 am

Thank you all!
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 58
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests