Moving a Base (HSQL) database

[gladtobegrey]

I created an Base (HSQL) database, and it's been working fine (apart from a known bug with StarDesktop.Terminate, which I've posted about elsewhere).

I decided that I needed to relocate the database into a different folder, so I simply moved it and updated the shortcut I was using to access it. I then realised that it was still registered in its old location, so I deleted the old registration and added a new one in the new location.

But now my macros appear unable to connect to the database. Or, more accurately

Code: Select all

oConnection = ThisDataBaseDocument.CurrentController.ActiveConnection

... seems to work OK, but ..

Code: Select all

oStatement = ThisDataBaseDocument.CurrentController.ActiveConnection.CreateStatement

... fails with "Object variable not set".

I see from this thread http://user.services.openoffice.org/en/ ... t+register Drew Jensen suggests that moving a database can be problematical, but what do I need to do to clean this up? Although this is a one-off database, it does use Sun Report Builder to create Writer reports, so presumably I do need the database registered?

Thanks

[Villeroy]

Learn debugging.

Code: Select all

db=ThisDatabaseDocument
ctrl=db.getCurrentController()
con=ctrl.ActiveConnection

Which one fails?

[gladtobegrey]

Code: Select all

   oDB = ThisDatabaseDocument
   oCC = oDB.getCurrentController()
   oAC = oCC.ActiveConnection
   oStatement = oAC.CreateStatement          [ or .CreateStatement() ]

... fails on the last line ... i.e the .CreateStatement ... as the OP suggested.

[Villeroy]

I can not reproduce the problem. I tried with a HSQLDB as well as a file based database (a spreadsheet connection).

Code: Select all

Sub Main
Snippet ThisDatabaseDocument
End Sub

Sub Snippet( Optional oInitialTarget )
  Dim oCurrentController As Object
  Dim oActiveConnection As Object
  Dim oObj1 As Object

  oCurrentController = oInitialTarget.CurrentController
  
  oActiveConnection = oCurrentController.ActiveConnection
  oObj1 = oActiveConnection.createStatement()
End Sub

Sub Snippet has been generated by the MRI tool. http://extensions.services.openoffice.org/project/MRI

[gladtobegrey]

Was that after moving (copy/paste) the database to a different location?

[Villeroy]

Was that after moving (copy/paste) the database to a different location?

ThisDatabaseDocument always refers to the database document where the code is stored in.

[gladtobegrey]

Thanks, I realise that. My problem arose initially after I had moved the database physically to a new folder on disk. So I wanted to know if the test you did was after physically moving your test database (i.e. were you fully reproducing the problem I have)?

In desperation, I have tried completely rebuilding my (fortunately small) database - but it STILL exhibits the same problem I originally posted.

I have done some more digging, and found an alternative method of connecting to the database that doesn't use ThisDatabaseDocument in Andrew Pitonyak's documentation ...

Code: Select all

oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oBaseContext.getByName("MyDatabase")
oCon = oDB.getConnection("", "")
oStmt = oCon.createStatement()

And that seems to work OK, but I have to name the database URL of the database I'm already in.

This is driving me nuts, and I'm pretty well ready to give up on Base as a bad job.

[Villeroy]

I do not use a single line of macro code. Since 8 months I actually use a fairly complicated database (embedded HSQL) day by day, sometimes several hours per day. Of course I run daily incremental backups with an additional copy of the HSQLDB on a stick.
Apart from that, I keep a flat dBase list with data pilots for our private expenses since the days of OOo1.1 (2002? it was an Excel sheet before).

Base is the perfect bridge between the database world and this document oriented office suite. No more and no less than that.

No Basic, no cry. All that macro coding is a waste of time if all you need is mapping a few thousand items in several levels of categories to another thousand items.
Are you trying to sell a database application in Basic/Base? Come on, no profit without investment. Get some real software for the job. You will find Base still useful when you got a real database up and running.

[gladtobegrey]

No Basic, no cry. All that macro coding is a waste of time if all you need is mapping a few thousand items in several levels of categories to another thousand items.

All well and good, but that's not what I want to do. My database is pretty simple - the issue is that it's for a totally non-technical user to use, so I cannot simply leave them with the 'raw' Base interface of Forms and Reports - I need to tie it together as a coherent application.

Are you trying to sell a database application in Basic/Base? Come on, no profit without investment.

No, I'm building it as a favour for a friend - for free - who doesn't have the cash to invest in MS Office right now.

Get some real software for the job.

Quite so.

Incidentally, I have found that if I code:

Code: Select all

If not ThisDatabaseDocument.CurrentController.IsConnected then
   ThisDatabaseDocument.CurrentController.Connect
   MsgBox "It was not connected"
End if
oConnection = ThisDatabaseDocument.CurrentController.ActiveConnection
oStatement = oConnection.createStatement()

then sometimes it runs through OK, and sometimes it displays "the "It was not connected" message ...

[Villeroy]

What could be the reason why a computer user should be unable to full out Base forms?

No, I'm building it as a favour for a friend - for free - who doesn't have the cash to invest in MS Office right now.

My daughter had some difficulties with my forms because she is so used to web-applications. My wife can fill out my forms slowly. Quite often she reads my input data from paper while I try to follow her, so she got the concept of adding items before referencing them from refreshed list boxes, switching windows, sub-windows (form beamer), and subforms (Ctrl+Tab).
I'm conviced that everybody who's able to use a keyboard can adjust him/herself to a Base form within one or two hours. The other way round is close to impossible (never-good-enough problem).

It really helps a lot when people realize that changing a name really changes the name of the selected person rather than selecting another person by name.
A few colors help a lot to give a subliminal clue on forms, subforms and list boxes.
Many-to-many relation: This "green" is one part of the above selected "blue", Ctrl+Tab back to blue and arrow down to select the next "blue", Ctrl+Tab to green and pick the next greens, typing their well known short names into the list boxes.
Yes, sometimes you have to move forward(store) and back(reload) to refresh some detail. Where's the problem?
Using a form's grid view in a beamer window while working with a form can be really important, even if you have to use the mouse to get in and out of that window.
Of course, it is important to know Alt+Tab (switch windows under Windows) regardless of the app you are using.

Edit: I forgot: print ThisDatabaseDocument.getURL() prints the path of the containing odb-document whereever I move that thing.

[gladtobegrey]

We'll have to agree to disagree on this one. My view is that the application should be designed and built to make the user's life easier, not require or retain unnecessary actions that they can all too easily forget or get wrong, or have to work around just to make the application builder's life easier. If I'm building for me, I can accept and avoid those compromises because I understand them. For a non-technical user, they cannot, nor should they have to.

Anyway, this is going off-topic.

[Villeroy]

My view is that the application should be designed and built to make the user's life easier,

I collect hundreds of records (mostly associations between existing items) day by day very rapidly.
From the collected records I get really good aggregations, partially with the help of a spreadsheet.
Our banker was very impressed by my detailed credit application made by Sun Report Builder.
It's like using a shell. A shell is an extremely user friendly interface since it lets you "talk to your computer", but it can be quite picky about which users are its friends.