[Solved] Checkpoint defrag and shutdown compact

Creating and using forms

[Solved] Checkpoint defrag and shutdown compact

Postby MikeytheMagnificent » Wed Feb 19, 2014 7:21 am

What's the difference?
Both work at the tools>sql command line until a shutdown [compact] command is issued
in no more than , which of the following are affected ie optimized by the commands 1 through 6

1. updated table data and or definitions (a [y|n], b [y|n] , c [y|n] , d [y|n] , e [y|n]
2. updated queries (sql statements) (a [y|n], b [y|n] , c [y|n] , d [y|n] , e [y|n]
3. updated embedded database graphics IMAGE LVB BLOB CLOB ditto
4. updated form graphics, backgrounds etc ditto
5. updating file relationships if any are set ditto
6. updating linked database graphics ditto

are affected / optimized by each of these 5 SQL and Base commands (a to e)
a. checkpoint defrag
b. shutdown compact
c. file>save
d. file>close
e. connection close

Once a shutdown command has been issued what is necessary to restart safely other than closing and reopening Base?

If I choose to <Record> these to a readymade <button> on a database form without going thro' the the tools > sql thing every time
afaik I will first need to perform the make new connection lines as follows ?
and end with connection.close
Because executing <as a macro> does not offer a Success/Failure dialog.
perhaps as an extra someone could suggest how to create such a Messagebox


Code: Select all   Expand viewCollapse view
sub checkpoint
   dbName = "NDBEMB"   ' registered datasource...
   oDatabaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext")
   oDBSource       = oDatabaseContext.GetByName(dbName)
   oConnection       = oDBSource.GetConnection("", "")
   oStatement       = oConnection.executeStatement(mystatement)    this is wrong can u correct it for me

       mystatement       = checkpoint defrag   
       oconnection.close()
       msgbox  "success"                                          this needs a tweak and an iif()
       
end sub

Code: Select all   Expand viewCollapse view
similar for shutdown [compact]
Last edited by MikeytheMagnificent on Fri Feb 21, 2014 12:18 am, edited 1 time in total.
Many opportunities to add short Tutorials or faq's are missed

1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
User avatar
MikeytheMagnificent
 
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

Re: Checkpoint defrag and shutdown compact

Postby RPG » Wed Feb 19, 2014 10:30 am

Hello

I think you should only close the connection and / or the database when you really want close the connection.
Code: Select all   Expand viewCollapse view
sub subdefrag
thisdatabasedocument.currentcontroller.activeconnection.createstatement.execute("checkpoint defrag")
end sub


A connection to the database is always exported by a data-form and your databasedocument.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Checkpoint defrag and shutdown compact

Postby Greengiant224 » Wed Feb 19, 2014 11:47 am

MikeyTheMagnificent:

To checkpoint/defrag and or shutdown/compact a database/connection you need to enter the default username/password pair.
This is usually "sa" as root user and password is "" 'empty string' unless you have used something different.

The following macro can be assigned to a button on your dataform and will give you a message if completed or not.

Code: Select all   Expand viewCollapse view
Sub Defrag_Database()

' this macro can be assigned to a button on your data form

dim conn, stmt, dbName, DBContext, Datasource As Object

dbName = "NDBEMD" '<<- change to suit, must be a registered datasource

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")

    If DBContext.hasByName(dbName) then
          DataSource = DBContext.getByName(dbName)
          conn = DataSource.getConnection( "sa", "" ) '<<- default username/password pair
          stmt = conn.CreateStatement
          stmt.ExecuteUpdate( "CHECKPOINT DEFRAG" )
          conn.dispose '<<- your database will still be open to enter/edit the data
          MsgBox " The defragmentation of: " & chr(13) & dbName & chr(13) & "has now been completed....", ," --AOO Database Defrag-- "
    else
         MsgBox "The connection has failed....." & chr(13) & "Sorry... Can Not Defragment" & chr(13) & dbName,  ," --AOO Database Defrag--"
    End If
End Sub


Once a connection has been closed by using shutdown or shutdown compact you cannot access your data
from the *.odb file. You need to re-start your *.odb to instantiate a 'new' connection.

Good luck :bravo:

Greengiant224

Win 7, AOO 4.1.6, LibreOffice 6.1.* (Java 1.8.0_181) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
Greengiant224
 
Posts: 276
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Checkpoint defrag and shutdown compact

Postby RPG » Wed Feb 19, 2014 10:18 pm

Hello

Answering this question makes also I must good think about those thing I knew so I can tell it in a good way. I'm not sure if it is real correct.

Shutdown
I have the idea when you work with OOo then you have never to shut down the (database)engine. When you shutdown the (database-)engine then you can not restart it from OOo. When you do not use the embedded database then with a good database design it is possible can not shutdown the (database-)engine.

Defrag
It is good to defrag the database sometime how often: I do not know.

Connection
From some test it seems possible there is a connection to the database without a running (database-)engine.

The next part is more common

Services
When you work with macros most of the time you want use a service which is already exported by the document you work with.The question is how to use the service in this case we mean the service for the database connection.

When you work with a databasedocument then you can use the controller of the database. This is the normal use.
http://www.openoffice.org/api/docs/comm ... entUI.html
This controller does export several method, properties and interfaces you can use. Which you have to use depends of several things but in this thread you can use Activeconnection.

When you are working with a form even if it is a standalone form then you can get this connection to the database from the data-form what is in your form-document. As example you have a button then the next code is enough.

oEvent.source.model.parent.Activeconnection

Connection
http://www.openoffice.org/api/docs/comm ... ction.html
When you have found the methode Activeconnection then it is easy to go to the connection interface and use the properties or methods of that interfaces.

Why use exported services
I think it is better, use macro code what is close to the document. When you use code what is close to the document then OOo takes care of the more difficult parts who the most of us ,like me, do not understand.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Checkpoint defrag and shutdown compact

Postby MikeytheMagnificent » Fri Feb 21, 2014 12:16 am

Sincere thanks to you both, absolutely sterling work.

Of course I always knew that sql commands (checkpoint defrag and shutdown compact) must always be expressed as strings in Basicotherwise they'll be regarded as BASIC variables and variables are not allowed spaces, I knew that too. What a shame that I remembered to forget.

We must just have a little laugh together
Schoolboy error Greengiant you read and retyped the registered name incorrectly instead of c&p'ing it. The last letter should be “B”

Other schoolboy error Mr. Romke: No-one can execute a string, you know that, even I know that. In our case we must execute either a sql update or sql query depending whether we are altering data or just extracting a copy for use elsewhere. So in our case Romke's one liner should be
Code: Select all   Expand viewCollapse view
thisdatabasedocument.currentcontroller.activeconnection.createstatement.ExecuteUpdate("checkpoint defrag")

It still doesn't quite work because there is still an “object variable not set” error. What it is I'm not quite sure. Perhaps if you've got 5 minutes..............please

I now know on your advice that Shutdown is terminal and we may as well just close Base and reopen it whenever we need it again after a shutdown [compact]. Unless we just happen to have other Databases ( .odb's) open which should still be working. AFAIK, We can connect and disconnect connections as often as we like in the correct order so connection.close or connection.dispose should be good, what the difference is I'm not sure, only that RobertoB used .close in his similar work..

I note that the messagebox does not check anything but simply confirms that the code passed by this way and the sql reported no error to stop the macro.
Terrific stuff both of you. Thanx
Many opportunities to add short Tutorials or faq's are missed

1. Must have obvious title to be found easily
2. Keep to objectives
3. Use very clear language
4. Difficult to get right
5. Rewards are few
User avatar
MikeytheMagnificent
 
Posts: 137
Joined: Fri Apr 11, 2008 12:06 am
Location: W. Yorks UK

Re: [Solved] Checkpoint defrag and shutdown compact

Postby RPG » Fri Feb 21, 2014 1:00 am

Hello

MikeytheMagnificent wrote:Other schoolboy error Mr. Romke: No-one can execute a string, you know that, even I know that. In our case we must execute either a sql update or sql query depending whether we are altering data or just extracting a copy for use elsewhere. So in our case Romke's one liner should be

Code: Select all Expand view
thisdatabasedocument.currentcontroller.activeconnection.createstatement.ExecuteUpdate("checkpoint defrag")
My original code is working code.

MikeytheMagnificent wrote:It still doesn't quite work because there is still an “object variable not set” error. What it is I'm not quite sure. Perhaps if you've got 5 minutes..............please
No I cannot give you code what is working in your case for that reason there was the explanation you maybe do not understand.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands


Return to Forms

Who is online

Users browsing this forum: No registered users and 7 guests