Marc Santhoff wrote:
Executing CHECKPOINT DEFRAG from the SQL window will indeed shrink the
size of the odb file,
Doing so with in a script with
oStatement.ExecuteUpdate( "CHECKPOINT DEFRAG" )
does not. It runs to completion, but the file size is not changed.
Not wanting to insult anybody, but:
Did you mke sure you "commit"ed the command explicitly or the
"AutoCommit"-property is set to true?
Good question.
If you open a Base file on the desktop,
or open a connection in a script such as
createConnection( <USERNAME>,.<PASSWORD> )
then AUTO_COMMIT = TRUE is the only thing you can have
You must use an IsolatedConnection in order to turn AUTO_COMMIT off.
( unless things have changed in the last few releases)
Surely I would not be so silly as to have created an isolatedConnection -
What do you know I did..
OK another test.
Size of the database before I started
10,850KB
Opened and deleted 9,500 records
Closed that database ./ Close OOo completely
size of file 10,850KB
Started OOo, without opening the Base file and ran this script
sub Main
defrag_database("baseball_embedded_fielding")
end sub
Sub defrag_database( sRegName as string )
dim ctx, db, con, stmt
ctx = createUnoService("com.sun.star.sdb.DatabaseContext")
db = ctx.getByName(sRegName)
con = db.getConnection( "", "" )
stmt = con.CreateStatement
stmt.ExecuteUpdate( "CHECKPOINT DEFRAG" )
End Sub
Closed OOo, completely, and size on disk 10,850KB
BUT - ah ha I found the bug..it was my code - well, maybe not.all my code.
I did not dispose of the connection within the script.
When I add the dispose with
Sub defrag_database( sRegName as string )
dim ctx, db, con, stmt
ctx = createUnoService("com.sun.star.sdb.DatabaseContext")
db = ctx.getByName(sRegName)
con = db.getConnection( "", "" )
stmt = con.CreateStatement
stmt.ExecuteUpdate( "CHECKPOINT DEFRAG" )
con.dispose
End Sub
Now the file immediately shrank to 9,557KB - without having to exit OOo
I noticed that after the first run the .lck file was not removed until
after OOo was fully closed, and that is what tipped me off.
What is insulting is that I really know enough from esxperience to
alwasy explicilty dispose of those [EMAIL PROTECTED] connection variables..
I'll leave it up to some else to determine if AUTO_COMMIT also plays a
role in this.
For now I'll just put the defrag_database procedure up as a code snippet.
Thanks Marc,
Drew
ps. I am hard to insult, at least when it comes to this type of thing,
so don't ever worry 'bout it.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
