[Solved] Checkpoint defrag and shutdown compact

[MikeytheMagnificent]

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

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

similar for shutdown [compact]

[RPG]

Hello

I think you should only close the connection and / or the database when you really want close the connection.

Code: Select all

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

[Greengiant224]

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

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

Greengiant224

[RPG]

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

[MikeytheMagnificent]

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

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

[RPG]

Hello

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.

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