[Solved] Add Months Function

Creating tables and queries
Post Reply
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

[Solved] Add Months Function

Post by RobboSC »

This is my first post as a newbie Base user and am on a steep learning curve, so Pls feel free to advise if I am not following form in this forum.

I have created a table called Registrations using direct SQL input, which I am now trying to edit (by creating a new table) so that I can get an automated entry in the "Expiry Date" field

CREATE TABLE "Registrations" (
"Registration ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0) NOT NULL PRIMARY KEY,
"Registration Date" DATE DEFAULT CURRENT_DATE,
"Expiry Date" DATEADD( 'mm', 6, CURRENT_DATE) ,
"Status" CHAR (8) NOT NULL,

I would like the "Expiry Date" field to be automatically populated with a date 6 months into the future based on the "Registration Date" field when the "Registration Date" field becomes populated. Having searched through the forum I found the function DATEADD and have played around with it to try and create the table. I cant get it accept the line. Either the code is meant for Form input or I am not compiling it correctly. In fact in my search I have realised that the ADD_MONTHS function would probably be a more suitable function, I cant get that to work either. Would appreciate a pointer or two how to construct the code for use in my table...... thanks.
By the way that CURRENT_DATE insert was the last of my many trials...

Am using Apache 3.4.1 on Windows Vista
Last edited by RobboSC on Tue Aug 20, 2013 11:01 am, edited 1 time in total.
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Add Months Function

Post by Villeroy »

You do not store any formulas in tables. A table stores raw data. A query queries sub-sets of the stored data plus calculated fields.

Code: Select all

SELECT *,  DATEADD( 'mm', 6, "Registration Date") AS "Expiry Date" FROM "Registrations" 
queries everything plus the calculated expiry date from the "Registration" table.

P.S. DATEADD is not part of the built-in HSQLDB 1.8 shipped with this office suite. You need to connect your database document to HSQLDB 2.x or some other database engine having that function.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Add Months Function

Post by Sliderule »

I would like to agree with the post by Villeroy above, completely, concerning NOT storing 'calculated' values in a database.

But, just to be clear, the SYNTAX ( command - Select statement ) should be written as:

Code: Select all

SELECT 
   "Registrations".*, 
   DATEADD( 'mm', 6, "Registration Date") AS "Expiry Date" 
FROM "Registrations"
Explanation:
  1. When using HSQL as your database back-end, and, using an * to denote all of the fields/columns from the defined tables/views, AND, you are adding other columns as well, you MUST denote the table/view name prior to the * --- in this case as --- "Registrations".*
  2. Another 'technique' that could be used, with a database back-end, such as, HSQL 2.2.8, HSQL 2.3.0, other ( but, NOT HSQL 1.8.0.10 - embedded database with OpenOffice 3.4.1 and prior ), is, by defining a CREATE TRIGGER to populate such a field.
Sliderule
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

Thanks for the feedback and the clarifications guys.
I had seen the exclusion lists on line but had made a very basic error of assuming I was using the latest version because I had downloaded the latest Open Office. So this now obviously leads me to the next question if I want to use this function then :-
1. Can I download the latest HSQL version and seamlessly (not have to go through a complicated process) get it to work with base?
2. Will I have to redo my tables again

Just want to say thanks for the prompt response, appreciate it.
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Add Months Function

Post by Villeroy »

0) Make a backup of your database.
1) Close the office.
2) Open your .odb with a zip tool and extract the database directory.
3) Rename the extracted files script, properties, data to db.script, db.properties, db.data (db is just an arbitrary name prefix for the extracted files).
4) Download http://sourceforge.net/projects/hsqldb/ ... p/download (NOT the current 2.2.9!). This requires no installation. Just extract it somewhere. Basically, all you need is the contained lib/hsqldb.jar and the documentation.
 Edit: As of July 2013 HSQL 2.3 is ready to use. http://www.hsqldb.org/ 
5) Download and install this extension of mine: http://forum.openoffice.org/en/forum/do ... p?id=17008 (don't extract that zip, call menu:Tools>ExtensionManager...)
6) Open your .odb with OpenOffice, call Tools>Macros>Run>FreeHsqldb>FreeHsqldb>Main
7) Click the first button and point the file picker to the .script file you extracted and renamed at 2( & 3)
8) Click the second button and point the file picker to the hsqldb.jar you extracted at 4)

Close the tool, save the document and give a try. Now you have the full featured database backend separated from your Base document. You may move the backend on a server for multi-user access (requires more tweaking).
Last edited by Villeroy on Sat Jul 13, 2013 5:03 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

Hi Villeroy, thanks for the instructions and the files.
I got as far as trying to install your extension as in line 5. but it could not associate the .xba file with any application. I am assuming FreeHSQLDB.xba is the file to click.
I did do some research on the internet on the file type but am hesitant to download apps I do not know much about. Would appreciate it if you could advise what application to associate the file type with. ? Thanks
As a matter of interest - I am assuming that when you say that the "full featured database backend will be separated from the base document" will this be the case for any other new database I create or must I run it anew for each instance ?
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

OK after a bit of familiarising myself with the Base GUI I found the add extension button under Tools. Will try and run this on Monday and see what happens !!
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Add Months Function

Post by Villeroy »

You must not extract the zip archive.
You can rename the .zip to .oxt (which is not allowed on this server) or you can call Tools>Extensions... from the office menu.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

I followed the instructions as given, but was thrown a bit by the fact that as per instruction line 3 I had an extra file called backup, which i renamed however. Everything was completed otherwise as listed. I was able to open the database, but the tables were not editable as far as changing structure properties. Data I could edit. I ran a table drop and table add for my registrations table and the sql reports came back as successful. However when I tried to reopen the database it had crashed with an error message "The connection to the data source "XXXXXXXXXx-Copy" could not be established. Error in script file line 64 java.lang.OutOfMemoryError:Java heap space - Anybody got any ideas here on what may be the issue or maybe something I am doing wrong ?
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Add Months Function

Post by DACM »

Could you post your .script file?

Something is causing your Java Runtime Environment (JRE) to run out of memory. Since the Java 'heap space" defaults to 64MB of RAM, it could be a number of things:

1. A query result-set returning/processing more than 50,000 normal size rows

2. Image files stored within the database without the proper table structure or data-type

3. Scrolling through a large database (50K+ rows) may require an increased Java heap space allocation:
  • Tools (*Office) > Options (Preferences) > *Office > Java (Advanced) > Parameters... > Java start parameter, add:

    Code: Select all

    -Xmx128m
    or -Xmx256m, or -Xmx512m, or -Xmx1024m, and so on in terms of megabytes (where 'm' = MB).

    Perhaps a rule-of-thumb would be 1MB of RAM for every 1,000 records possible in a query result-set plus a 25% overhead. For instance, the default 64MB should handle 50,000 records averaging 1KB each. Or to support 100,000 records, adjust the memory allocation to 128MB ...and so on. The use of 'outer-joins' will further multiply the size of a result-set, so careful memory considerations may be necessary. If this scheme would exceed your available RAM, then consider 'disk-based result sets' at the expense of performance on-the-margin.
Last edited by DACM on Sun Aug 25, 2013 10:29 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

HI DACM thanks for the further advice.
Before your post I tried to rework and rework the split by trying to change the table structure through SQL commands(after the split), even trying to reset read only for table properties. In the process of doing so I deleted a lot of the previous error files and the databases as well. I generally took a copy of the back up and reworked from anew each time. After your post I tried to replicate the error but I now get an error message "Java.lang.IllegalArgumentException" when trying to open the tables folder in the Base GUI -
Some extra information that may or may not be relevant :
1. I tried to split a new database with one simple table and received this same Illegal Argument message on trying to open the table
2. In my original database I had only one image file and I was using the LONGVARBINARY variable
3. When I go into the database file to change the names of the files, the script file and the properties file default to just db and then have an association with windows notepad. I then go back in and change the name to db.script etc ( this happens because i tried to view the files after the corruptions on my trial runs) not sure if this has an impact!!
4. Also on review I see that there are now 2 script files and a temp script folder in the database folder

I would like to get back to stage 1 of the original problem, which I suspect will be the case after this error can be sorted. Have attached the script file for the latest issue. I have noted your means for increasing memory, would prefer to implement this once back to the original error message.
Cant upload the files due to "The extension properties is not allowed" feature so have copied and pasted below

The first script file
#HSQL Database Engine 2.2.8
#Tue Jul 02 10:25:14 SGT 2013
version=2.2.8
modified=yes

The second script file
SET DATABASE COLLATION "Latin1_General"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE "Clients"("Client ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"First Name" VARCHAR(25) NOT NULL,"Alias" VARCHAR(25),"Middle Name" VARCHAR(25),"Surname" VARCHAR(25) NOT NULL,"Gender" CHAR(6) NOT NULL,"Birth Day" DATE,"Age Group" CHAR(8) NOT NULL,"Club Member" CHAR(3),"Membership No" VARCHAR(25),"Photo" LONGVARBINARY,"Spouse Name" VARCHAR(25),"Parent Name" VARCHAR(25),"Notes" VARCHAR(256),CONSTRAINT CHK_CLI_GNR CHECK("Clients"."Gender" IN ('Male','Female')),CONSTRAINT CHK_CLI_AGE CHECK("Clients"."Age Group" IN ('Jnr','Inter','Adult','Senior')),CONSTRAINT CHK_CLI_MEM CHECK("Clients"."Club Member" IN ('Yes','No')))
CREATE CACHED TABLE "Contacts"("Contact ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Phone Number" VARCHAR(15) NOT NULL,"Number Description" VARCHAR(15) NOT NULL,"Email" VARCHAR(50),"Email Description" VARCHAR(20),"Web App Ident" VARCHAR(30),"Web App Decsription" VARCHAR(30),"Client ID" INTEGER NOT NULL,CONSTRAINT FK_CON_CLI FOREIGN KEY("Client ID") REFERENCES "Clients"("Client ID"))
CREATE CACHED TABLE "Addresses"("Address ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Address" VARCHAR(100),"Suburb" VARCHAR(30),"Post Code" VARCHAR(10),"City" VARCHAR(30),"State" VARCHAR(30),"Country" VARCHAR(30),"Client ID" INTEGER NOT NULL,CONSTRAINT FK_ADD_CLI FOREIGN KEY("Client ID") REFERENCES "Clients"("Client ID"))
CREATE CACHED TABLE "Packages"("Package ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Units" CHAR(2) NOT NULL,"Description" VARCHAR(100) NOT NULL,"Pax" CHAR(2) NOT NULL,"Class1" DECIMAL(10) NOT NULL,"Class2" DECIMAL(10) NOT NULL,"Class3" DECIMAL(10) NOT NULL,"Class4" DECIMAL(10) NOT NULL,"Class5" DECIMAL(10) NOT NULL)
CREATE CACHED TABLE "Pros"("Pro ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Alias" VARCHAR(10) NOT NULL,"First Name" VARCHAR(25) NOT NULL,"Middle Name" VARCHAR(25),"Surname" VARCHAR(25) NOT NULL,"Mobile" VARCHAR(15),"Home" VARCHAR(15),"Spouse" VARCHAR(15),"Parents" VARCHAR(15),"Email" VARCHAR(50),"Class" CHAR(2) NOT NULL,"Tax ID" VARCHAR(30),"Address" VARCHAR(100),"Photo" LONGVARBINARY,"Qualifications" VARCHAR(100),"Notes" VARCHAR(256))
CREATE CACHED TABLE "Registrations"("Registration ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Registration Date" DATE DEFAULT CURRENT_DATE,"Expiry Date" DATE NOT NULL,"Status" CHAR(8) NOT NULL,"Student1" VARCHAR(50),"Student2" VARCHAR(50),"Student3" VARCHAR(50),"Student4" VARCHAR(50),"Student5" VARCHAR(50),"Package ID" INTEGER NOT NULL,"Pro ID" INTEGER NOT NULL,CONSTRAINT FK_PAC_REG FOREIGN KEY("Package ID") REFERENCES "Packages"("Package ID"),CONSTRAINT FK_PRO_REG FOREIGN KEY("Pro ID") REFERENCES "Pros"("Pro ID"))
CREATE CACHED TABLE "Schedule"("Schedule ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Lesson Date" DATE NOT NULL,"History" VARCHAR(20),"Registration ID" INTEGER NOT NULL,CONSTRAINT FK_SCH_REG FOREIGN KEY("Registration ID") REFERENCES "Registrations"("Registration ID"))
CREATE CACHED TABLE "Payments"("Pay ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Payers Name" VARCHAR(30) NOT NULL,"Payment" DECIMAL(10) NOT NULL,"Payment Date" TIMESTAMP(0) NOT NULL,"Type" CHAR(8) NOT NULL,"Reference" VARCHAR(30) NOT NULL,"Payment Status" CHAR(8) NOT NULL,"Registration ID" INTEGER,CONSTRAINT CHK_PAY_TYP CHECK("Payments"."Type" IN ('Card','Cheque','Cash','IBT','Discount','Voucher','Other')),CONSTRAINT CHK_PAY_STA CHECK("Payments"."Payment Status" IN ('Part Pd','Unpaid','Paid Up')),CONSTRAINT FK_PAY_REG FOREIGN KEY("Registration ID") REFERENCES "Registrations"("Registration ID"))
CREATE CACHED TABLE "Pax Matrix"("PaxMat ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Registration ID" INTEGER NOT NULL,"Client ID" INTEGER NOT NULL,"Concat Name" VARCHAR(70) NOT NULL,CONSTRAINT FK_PAX_CLI FOREIGN KEY("Client ID") REFERENCES "Clients"("Client ID"),CONSTRAINT FK_PAX_REG FOREIGN KEY("Registration ID") REFERENCES "Registrations"("Registration ID"))
SET TABLE "Clients" INDEX'6712 4'
SET TABLE "Contacts" INDEX'6504 6504 3'
SET TABLE "Addresses" INDEX'6616 6616 3'
SET TABLE "Packages" INDEX'9672 12'
SET TABLE "Pros" INDEX'32 1'
SET TABLE "Registrations" INDEX'7872 7976 7872 5'
SET TABLE "Payments" INDEX'7336 7336 3'
SET TABLE "Pax Matrix" INDEX'7256 8080 7256 3'
ALTER TABLE "Clients" ALTER COLUMN "Client ID" RESTART WITH 4
ALTER TABLE "Contacts" ALTER COLUMN "Contact ID" RESTART WITH 3
ALTER TABLE "Addresses" ALTER COLUMN "Address ID" RESTART WITH 3
ALTER TABLE "Packages" ALTER COLUMN "Package ID" RESTART WITH 12
ALTER TABLE "Pros" ALTER COLUMN "Pro ID" RESTART WITH 1
ALTER TABLE "Registrations" ALTER COLUMN "Registration ID" RESTART WITH 5
ALTER TABLE "Schedule" ALTER COLUMN "Schedule ID" RESTART WITH 0
ALTER TABLE "Payments" ALTER COLUMN "Pay ID" RESTART WITH 3
ALTER TABLE "Pax Matrix" ALTER COLUMN "PaxMat ID" RESTART WITH 3
CREATE VIEW "vwProNameNum" ("ProNameNum","Pro ID") AS SELECT "First Name" || ' ' || "Surname" || ' Tel: ' || "Mobile" AS "ProNameNum", "Pro ID" FROM "Pros"
CREATE VIEW "vwPackDescr" ("PackDescr","Package ID") AS SELECT "Units" || ' by ' || "Description" || ' with ' || "Pax" || 'pax' AS "PackDescr", "Package ID" FROM "Packages"
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
Attachments
ErrorIllegalArgument.jpg
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Add Months Function

Post by DACM »

Make sure all file extensions are shown by Windows. Then clean up the database folder, leaving only the original db.script, db.properties, db.data and db.backup files.

Your .properties file indicates that you're using HSQLDB 2.2.8, so you'll eventually want to eliminate the LOGVARBINARY field and replace it with BLOB data-type. If you need to extract the image file before deleting the LONGVARBINARY column, then see the macros at the "Image files" link in a previous post above.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

I deleted the photo by deleting the record, then I cleaned out all the old files, and copied back in the latest version of my base file.

When I extracted the database directory I noticed the following error reported (screenprint attached) . I can tell you now that when I checked the config accelerator file it showed 0KB.

I stopped at this juncture coz I want to know if it needs sorting out or if I can ignore and proceed.

Also want to thank you guys for hanging in there with your help, for me personally not at all frustrated with the process and grateful for the support- its an interesting learning curve.

Even though I am forging ahead with designing my database on the old embedded version, it is proving to be a rewarding experience and I already have something I can use- however I do eventually want to have this ADD MONTH feature.
Attachments
dataerrorconfig.jpg
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Add Months Function

Post by DACM »

You only need to zip-extract the files from the 'database' subfolder located within your legacy embedded-database file. The files are named:
script
data
properties
backup


After extraction, rename those files with a common prefix (show file extensions in Windows):
db.script
db.data
db.properties
db.backup
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

Have done it now and can access the database through base.

However all the Tables are read only in regard to changing the table structure, not in regard to the data. Is there a method of amending this?
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Add Months Function

Post by DACM »

Yes.
[Wizard] Create a new 'split' HSQL database wrote:
  • IMPORTANT: While this template attempts to overcome some glaring deficiencies with Base, it scarcely addresses the holes in the Base GUI with respect to DDL tasks (database schema development) when connected to external databases through standard ODBC/JDBC drivers. Specifically, when connected to HSQLDB through its native JDBC driver, the Base GUI does not fully support modification of table-field attributes such as setting-up an Auto-Value primary key or Default field values. In fact, these and other field attributes appear frozen in the Base GUI after initial creation.

    Workarounds exist:
    Base limitations as a database manager:
Perhaps see also this recent rant:
http://forum.openoffice.org/en/forum/vi ... 45#p276745
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

OK great, thanks for that, will study it before implementing.
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

I decided to finish developing my databse before tackling this issue, hence the delay.

I read about the trick for unfreezing the field properties for editing. Unfortunately they appeared not to workfor me. I copied and pasted a table, saved it, refreshed and was not able to edit anything. I tried copying a column in one of the tables and was able to do so, I was able to edit it as well, however after saving exiting , and saving I returned to find my self locked out even on the copied column. Any ideas on taking it further ?
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Add Months Function

Post by DACM »

RobboSC wrote:...the trick for unfreezing the field properties...they appeared not to work for me. I copied and pasted a table, saved it, refreshed and was not able to edit anything.
You may need to enable the Table Copy 'wizard' so you can step through the wizard while adjusting each column and its associated property upon pasting the new table. This all occurs before saving or refreshing the tables.
RobboSC wrote:...and [after] saving I returned to find myself locked out even on the copied column. Any ideas on taking it further ?
There's no way to unfreeze the column properties permanently when using a split database configuration. Hence the GUI workaround. But you shouldn't need to alter the columns very often as the table design phase of the project ends.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

Thanks for the response, only saw it a few days ago.
Have taken note of the restriction and can confirm that I can adjust field properties through teh wizard. I note also that I can change properties on any table when going through direct HSQL input. However have only tested this in a limited way.

In the earlier responses the use of a Trigger was suggested and having read up on it a bit it appeals to me, as from what I understand it is actioned from teh backend. The trick now is to get the correct syntax expression. Will give it a shot and if I cant hack something together will come back...

I have upgraded to Open Office 3.4 and was disappointed to see no development done on base, but after reflection and reading a few articles on this I can understand that noone wants to waste time developing a concept that is broken from the start. All said and done, I still am happy to use base and HSQL even in a broken split environment, as a free product it is still very useful and effective.

Thanks for the help to date
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

That upgrade should read Open Office 4.0
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Add Months Function

Post by Sliderule »

RobboSC:

You said asked:
RobboSC wrote:In the earlier responses the use of a Trigger was suggested and having read up on it a bit it appeals to me, as from what I understand it is actioned from teh backend. The trick now is to get the correct syntax expression. Will give it a shot and if I cant hack something together will come back...
There might be another, and, perhaps, easier technique, than using a TRIGGER to accomplish your task, while using the database back-end ( HSQL ). But, before I give you the technique, please confirm answers to the following questions, so, I will know, and, be able to tell you exactly how to do it.
  1. What version of HSQL ( database backend ) are you using . . . for example . . . HSQL 2.2.8, or, HSQL 2.3.0 ?
  2. What is the name of the TABLE the NEW FIELD will be added . . . please answer that question with the name surrounded by double quotes . . . for example: "Registrations"
  3. What is the name of the EXISTING field, ( column ) you want to add to your database . . . that is 6 months in the future . . . please answer that question with the name surrounded by double quotes . . . for example: "Registration Date" ?
  4. What is the name of the NEW field, ( column ) you want to add to your database . . . that is 6 months in the future . . . please answer that question with the name surrounded by double quotes . . . for example: "Expiry Date" ?

    Also, most important, tell me if this field already exists in your table.
  5. Please tell me . . . where in the table . . . you want the new field to be added . . . that is . . . as the LAST field ( column ) in the table, OR, if not as the last field, give the name of the field . . . surrounded by double quotes . . . of the field that will be AFTER the NEW field . . . for example . . . "Field After Expiry"
Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

Hi Sliderule
Okay first some relevant info:
1. None of my databases are in operation yet, but I am expecting to phase them in on the 1st Sept.
2. At the moment I am developing my databases in embedded base/HSQL 1.8 because of the complications of the frozen fields
3. I have a split database for experiment and trial purposes, this uses HSQL 2.2.8, however I would be happy to scrap that and attempt to split my latest developed version I have now. QUESTION: Would you recommend I use 2.3.0 ? and is the process of splitting exactly the same as per 2.2.8 ?
4. Here is a list of my table fields as per the latest developed version in the embedded 1.8. HSQL version
CREATE TABLE "Registrations" (
"Registration ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0) NOT NULL PRIMARY KEY,
"Registration Date" DATE DEFAULT CURRENT_DATE,
"Expiry Date" DATE NOT NULL ,
"Package ID" INTEGER NOT NULL,
"Pro ID" INTEGER NOT NULL,
CONSTRAINT FK_PAC_REG FOREIGN KEY ("Package ID") REFERENCES "Packages" ("Package ID") ,
CONSTRAINT FK_PRO_REG FOREIGN KEY ("Pro ID") REFERENCES "Pros" ("Pro ID")
) ;

ALTER TABLE "Registrations" ADD COLUMN "Sale Value" DECIMAL (10, 2);
ALTER TABLE "Registrations" ALTER COLUMN "Sale Value" SET NOT NULL ;

5. In answer to your question no 3. I want 6 months to be added to the “Registration Date” Value and for it to reflect in the field “Expiry Date”
6. I believe that the table above gives answers to all the questions you have asked, if I have missed anything please advise.
7. From your post it appears that the order of the field is important, if it were better for the field “Sale Value” to appear after the “Expiry Date” field ( it now being the last field in the table) and or for the "Expiry Date" to be the last, is there a method for doing this without making the table anew and manipulating constraint and view connections?
I look forward to hearing your advice and your new simpler method, thanks a bunch for the proactive support!
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Add Months Function

Post by Sliderule »

The following command, if issued from the OpenOffice Base Menu: Tools -> SQL..., AND, can be used with HSQL Version 2.2.8, or, HSQL Version 2.3.0 ( but NOT HSQL 1.8.0.10, that is, the 'Embedded HSQL' version . . . at least of the date I am writing this ).

NOTE: As always, if it were me, I would always make a back-up of the database files ( *.backup, *.data, *.properties, *.script ) and the OpenOffice Base file ( *.odb ) before making any changes to the database and/or Base file . . . to be safe. 8-)

Code: Select all

ALTER TABLE "Registrations" ADD COLUMN "Expiry Date 6" DATE GENERATED ALWAYS AS ( DATEADD('month', 6, "Registration Date")) BEFORE "Package ID";
After issuing the above command, you will be able to 'see' the new column in your table, if you issue the command from the OpenOffice Base Menu: View -> Refresh Tables

Explanation:
  1. This will create a NEW field in your database . . . by the name of . . . "Expiry Date 6" . . . and . . . the database back-end ( database engine . . . HSQL ) will automatically, calculate a date for that field . . . some 6 months AFTER the value stored in column "Registration Date" . In the event you UPDATE ( change ) the value of "Registration Date" after adding it to the table "Registrations" . . . the value will also be changed. :bravo:
  2. I elected to put the new field ( "Expiry Date 6" ), BEFORE field "Package ID", because of your CREATE statement . . . BUT . . . of course, you can change this as you so desire.
  3. I elected to give the column a different name ( "Expiry Date 6" ) so you could look at it ( the database contents ) comparing the values 'calculated' in this field and anything you may have had in column "Expiry Date" . If you like what you see, you can always DROP the old column ( "Expiry Date" ) and RENAME column "Expiry Date 6" to "Expiry Date" . . . with the following four commands issued from: Tools -> SQL... ( I strongly recommend you execute them, one at a time -- that is individually, to ensure no errors )

    Code: Select all

    ALTER TABLE "Registrations" DROP COLUMN "Expiry Date";
    
    COMMIT;
    
    ALTER TABLE "Registrations" ALTER COLUMN "Expiry Date 6" RENAME TO "Expiry Date";
    
    CHECKPOINT DEFRAG
  4. I do NOT know, if you have any VIEWs defined, that might effect the dropping of column "Expiry Date" before you RENAME "Expiry Date 6" back to "Expiry Date".
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has ben resolved.
RobboSC
Posts: 25
Joined: Mon Jun 24, 2013 4:07 am
Location: Malaysia

Re: Add Months Function

Post by RobboSC »

Hi Sliderule
I can report back that the split and the adding of the fields went very well and is a success. The date function is working perfectly so I guess I can say this issue is solved. It was not without hiccup as I noticed some of teh queries did not work and i had to redo them with slightly different variations. Some of teh forms had to be reconnected , but Base seems to be much more stable even when developing in this condition. I am experiencing some issues with the CHECK CONSTRAINT - I think it is tied in with the CHAR variable and must be a slight change from 1.8 to 2.3 in the way that it works- but this I can work on. By teh way I used HSQL 2.3

Any way I am very happy with the result and want to say thanks to all who contributed and the constructive support.
Apache OpenOffice 4.1.0 on Windows 7 Professional 68 bit
Post Reply