[Solved] How to copy a form between databases?

[nathany]

I'm wondering if there is any way to copy a form from one database to another. My desire and need is exactly the same as that described in this post from a few years ago:

http://forum.openoffice.org/en/forum/vi ... es#p117086

I'm wondering/hoping that since then, perhaps some new approach is possible...??

Thanks --

Nathan

[RPG]

Hello

Copy form =Ctrl-c
paste form=Ctrl-v
where you see the names of the forms

Romke

[nathany]

Thanks for the quick reply. I was just thinking about this approach which of course would require me to be transferring a full copy of the database containing the new form to someone who needs to ONLY incorporate the new form into the "production" version of the database in use. I was hoping that there was a way to ONLY transfer the form-content/dataset itself rather than the entire database, noting that the database approach you suggest DOES seem to work OK.

To clarify, I am supporting an end-user of a database being used in production, and will be periodically creating/modifying forms and queries for that matter, which will need to be incorporated into the "production" database. I was planning to perform this process via email, sending updates of whatever form/format are needed to enable this ongoing update process.

Thanks again -

Nathan

[DACM]

...I was hoping that there was a way to ONLY transfer the form-content/dataset itself rather than the entire database...

Nathan, as a forum reader I'm sure you're aware that the default, all-in-one Base (.odb) file is unstable and highly susceptible to data corruption. It should never be used in production database applications. You can separate the database files from the Base (.odb) front-end file for stability. This also solves your issue with ongoing Form and Query development, because in this 'split database' configuration the front-end (.odb) file contains only your Queries, Forms, Reports and embedded Macros. So you can send a new copy of the Base (.odb) file by email at any time without affecting the production database.

See my signature links below for details...

[nathany]

DACM-

Actually I had not yet read up on this interesting issue/danger
regarding "embedded" database operations. The "split" mode sounds
exactly what I should be pursuing.

Is there an easy and "safe" way to convert my existing embedded database into
such a "split" mode scenario?

Thanks for this key comment/advice!

[Villeroy]

http://forum.openoffice.org/en/forum/vi ... me#p236734

[nathany]

Villeroy--

Thanks for this link! I will investigate and
report on my results once I'm able.
This all seems to be a great approach, also noting
that we are only dealing with a single-user
scenario.

Thanks again for the quick reply.

And thanks again to DACM for responding as we'll.

Nathan

[DACM]

See also: Migrating 'Embedded databases'

BTW, HSQLDB 2.2.9+ is no longer compatible with Base. The last version of HSQLDB that's compatible with Base is version 2.2.8. And when upgrading to HSQLDB 2.x there may be some data migration issues in some acute cases. You can start by simply connecting to the built-in version (HSQLDB 1.8.0.10). Links and instructions for both versions are here: http://wiki.openoffice.org/wiki/FAQ_%28Base%29#HSQLDB
...

[Villeroy]

See also: Migrating 'Embedded databases'

BTW, HSQLDB 2.2.9+ is no longer compatible with Base. The last version of HSQLDB that's compatible with Base is version 2.2.8. And when upgrading to HSQLDB 2.x there may be some data migration issues in some acute cases. You can start by simply connecting to the built-in version (HSQLDB 1.8.0.10). Links and instructions for both versions are here: http://wiki.openoffice.org/wiki/FAQ_%28Base%29#HSQLDB
...

I did not know that and in fact I do run HSQLDB 2.2.9 with Base on production systems with no problem. The only problem I noticed recently is that an embedded 1.8 database gets spoiled when I open it with an office version that is prepared to use 2.2.9. The embedded database seems to work but next time I get a message like "this database has been created with a newer version and can not be opened".

[nathany]

DACM -
I've finally managed to investigate migrating my embedded database to a split-database. I *think* I've done things properly. In my new (split) database I *do* see the JDBC URL reference in the bottom left, currently pointing to my "mydb" folder in the users/public folder structure. I've got all my tables transferred and I manually drag-n-dropped my forms and queries.
QUESTION: How do I proceed to manage my forms and queries EXTERNAL to the database, now that I am in this "split" mode? Can you clarify where these files are now being managed in such an "exploded" fashion? or, perhaps there is some additional step required to explode these forms and queries items as discrete files which can be managed external to the database?

Thanks -

Nathan

[Villeroy]

[Tutorial] Standalone Forms / Switchboard

[nathany]

Villeroy -

THANKS for this link/tutorial. I have done an initial test and can see that I CAN manipulate the contents of a table using such a standalone form, created as an ODT document, saving from the database. Cool.
I've read the info about Switchboard... can you confirm, is Switchboard the preferred best practice for how
to organize/present a set of (non-embedded) forms such as these externalized ODT files, for the end user?

ie, once I am into this split-database mode, and wanting the ability to be able to periodically update forms, is this the way to go?

Also, is there any similar approach to manage Queries EXTERNAL to the main ODB file?

Thanks -

Nathan

[DACM]

ie, once I am into this split-database mode, and wanting the ability to be able to periodically update forms, is this the way to go?

Also, is there any similar approach to manage Queries EXTERNAL to the main ODB file?

Sounds like a successful setup and migration.

I think we're all on the same page but lets make sure:

• 1. The reason to 'split' the database is to achieve reliability for the database data in production environments. This is critical, but it does not alleviate the responsibility to maintain regular database backups -- especially in this age of FREE automated backup software and cloud-sync folders complete with version history as provided (up to ~5GB or more) by SugarSync, Dropbox, and standalone software titles.
  
  2. A by-product of a 'split database' is the ability to distribute the "front-end" components (Queries, Forms, Reports and embedded Macros) separately, without affecting the "back-end" database. This can be as simple as distributing a new copy of the Base (.odb) file containing embedded front-end components.
  
  3. A nice touch is to hide the Base user-interface/development-environment using standalone Forms. As long as the Forms are contained within an identical directory structure (relative to the switchboard document), you can distribute Forms separately without issue. Thus, I would recommend saving everything to your "users\public\databases\mydb" folder for ease of management.
  
  4. Standalone Reports are not as cut-and-dry. You may end-up with a mix of embedded and standalone Reports, leveraging the power of embedded List-Reports and standalone Calc-reporting tools.
  
  5. Queries are locked into the proprietary Base (.odb) file format. So you can simply distribute a new copy of your Base (.odb) file when updating queries. But you have some options with Queries:
  • (a) Queries may be accessed from standalone Forms using the 'F4' function key if the 'data source' is registered in *Office.
    (b) Forms allow embedded 'SQL commands' (queries) so when combined with Form 'Table Controls' (grids) there's little reason to rely on centrally stored (.odb) queries, whether for query display or Form function. This embedded-query technique applies to both embedded and standalone Forms.
    (c) You can store Queries as 'Views' in the back-end database for universal access through the database engine (HSQL) rather than through the Base (.odb) file.

[DACM]

BTW, HSQLDB 2.2.9+ is no longer compatible with Base. The last version of HSQLDB that's compatible with Base is version 2.2.8...[/url]
...

I did not know that and in fact I do run HSQLDB 2.2.9 with Base on production systems with no problem...

That's good news, but it appears that HSQLDB 2.2.9 causes problems in some cases/configurations. Sliderule, r4zoli and fredt (the lead HSQL developer) have collaborated on the issues in brief. I've simply confirmed Sliderule's findings, although the issues with my own databases are less severe (no data loss) and simply pressing the Table refresh button seems to clear-up the table/query display issue with my own data files. I quickly reverted back to HSQLDB 2.2.8 and have never looked "back" (or is it "forward"...?), but this will complicate our community support for Base with HSQLDB 2.x.

Some of the problems with Base + HSQLDB 2.2.9 include:

• 1. Table/Query display is truncated at one or none lines (at least until pressing the 'refresh' button in my case)
  2. Tables without an auto-incremented primary key exhibit the truncated display issue
  3. Queries that utilize the Base query-parser exhibit the truncated display issue
  4. Sometimes the entire database is corrupted or reports errors upon restart
  5. Both 'file' (in-process) and 'server' modes have exhibited the issue based on r4zoli's findings using Sliderule's database file

Lastly, you can downgrade an HSQLDB 2.2.9 database to 2.2.8 by removing the following line from the *.script file:

• Code: Select all

  SET DATABASE SQL REGULAR NAMES TRUE

Edit: HSQLDB 2.3x has been specifically patched to accommodate the legacy Base code which caused this issue. So it appears that Base users will again enjoy the benefits of new releases of HSQLDB.

[nathany]

Villeroy -

Thanks for your summary info... so YES, I think the migration is fine and I understand all the steps involved. Do you think I have any reason why NOT to simply stick with the default HSQLDB v1.8.0.10 ? The current use case if pretty simple... ie, a SINGLE user/computer being used in production with the database.

Regarding the question on QUERIES, this is GREAT news that I can embed them within a standalone form. ie, as a methodology, is there any reason why not to
potentially create a "queries" form which can contain all the desired queries? OR for that matter, to potentially add a popular query to a form via a pushbutton if it
makes sense for the context of the form?

Thanks again --

Nathan

[Villeroy]

There is no reason not to use HSQL 2.8. It comes with more useful functions (e.g. DATEDIFF lets you calculate with dates).
There is no difference between query definitions stored in the form and queries saved in the database. I use to store the frequently used ones in the database so I can reference them by query name (e.g. one query to fill the same items into a list box that occurs in many forms).
Forms are for manual data input, reports for data output and printing. I prefer small forms to edit/insert/remove one record at a time. The amount of data on one form depends on the relations and it is a matter of work flow and screen size.

[DACM]

...Regarding the question on QUERIES...is there any reason why not to potentially create a "queries" form which can contain all the desired queries? OR for that matter, to potentially add a popular query to a form via a push-button...

Just to be clear, Queries stored within a 'registered' Base (.odb) file are accessible without exposing the Base interface using the 'F4' key from a standalone Form. So in effect, you can already access all of your Queries from a standalone Form without getting fancy. So it follows that Query storage and distribution is best accomplished using your Base (.odb) file.

Having said that, you do have the flexibility to store Queries in a standalone Form for use in the context of the Form. This option would not normally exceed the normal use of 'SQL commands' in support of Form elements (like the MainForm, SubForms, List/Combo Boxes). Anything more is uncommon.

If you just don't like the 'F4' table/query display format, or simply want to build a standalone Form/switchboard for Query distribution and/or display purposes (via Table Controls) then I think a Calc document would be best. Perhaps use the first sheet/tab to host the switchboard using Push Buttons, while additional sheets/tabs are used to host Table Controls reflecting Query results. The Queries themselves could be stored separately in a registered Base (.odb) file, or within the individual "Forms" on the Spreadsheet as SQL Commands. Your push buttons will need to be setup with Action: Open document/web page and the following URL syntax reflecting the name of each tab/sheet (#Sheet2, #Sheet3, etc.). Of course, you can place a "Return to Menu" (#Sheet1) button on each sheet as well:

Calc push button switchboard setup to jump between sheets/tabs

Otherwise, you could use separate Writer (.odt) Form documents to host the Queries/Table-Contols as you've outlined. In that case, I would recommend specifying 'relative' links as outlined in the [Tutorial] link Villeroy posted above.

[DACM]

...And I agree with Villeroy on the upgrade to HSQLDB 2.2.8. You may well get by with HSQLDB 1.8 because it is relatively full-featured, but once you've adopted a 'split database' configuration, it's [normally] a simple matter to upgrade the database and engine to HSQLDB 2.2.8 for even more features. The upgrade does disable support for the all-in-one file "embedded database" configuration, but you can always re-enable support through Base 'Class Path' settings, or simply install a sister *Office (AOO or LibO) suite for the all-in-one file support as necessary.

But of course it's up to you.

[nathany]

Villeroy & DACM,

THANKS for all this info - lot's of great ideas and multiple options. I now have some more tasks to dig into. (and BTW, the 'F4' behavior in the standalone form is pretty cool behavior "for free").

Thanks again for all of your responsiveness - MUCH appreciated --


Nathan