Drew Jensen wrote:
As I said in the issue.
Using latest developers build
Derby 10.3.1.4
Can create tables, but not set PK via GUI
Can add fields to a table.
Can not edit existing fields.
Adding data via SQL window - no problem.
After closing OpenOffice.org and then reopening database, when
connecting I get this warning:
Scroll sensitive result sets are not supported by server; remapping to
forward-only cursor
Added the auto_increment statement IDENTITY to the advanced properties
of the connection manager, attempting to create an auto_increment
field now generates a syntax error.
Drew
I suppose the first question is - Am I going to Purgatory because I
follow up on my own posts here? - Hope not.
Anyway, some follow up. Had a chance to curl up with Derby for another 2
hours yesterday.
Solved the IDENTITY problem. ( I'm sure most know this already but it
was a find for me )
In the Advanced Properties dialog for Auto-increment statement don't
simply add IDENTITY, you must add the full syntax GENERATED BY DEFAULT
AS IDENTITY
Then in the Query of generated values slot add IDENTITY_VAL_LOCAL
In fact using OO.org 2.3 adding GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY to Auto-increment statement in the odb properties dialog.
After this creating a table with an auto increment integer works just
great, just as with the embedded HSQL engine and this is the only way to
set the primary key for the table via the GUI. Which is another way of
saying that one still can't right click and set the primary key from the
context menu, as it isn't there.
Foreign keys - well, Base insists that Derby does not support them. OK,
must be a driver issue. This means of course that you can not use the
relation manager in Base. But there is more oddities. For example. After
getting the IDENTITY issue working and making primary keys I went ahead
and used the table wizard to create the tables Employees and
EmployeesTasks. Then created the FK relation by entering the DDL in the
SQL window:
ALTER TABLE "EmployeesTasks" ADD CONSTRAINT emp_tsks_frgn_key
FOREIGN KEY ("EmployeeID")
REFERENCES "Employees" ("EmployeeID")
That reports that it executes no problem.
Now with the Employees table empty I was able to open and insert 3
records in the EmployeesTasks table, all with NULL for the EmployeeID
field? Kind of a Derby feature that, however I don't like that. I really
meant to make that a FK that would not allow this..so using the GUI
table editor on EmployeesTasks I checked the index created for the FK
constraint and sure enough it is not unique. Trying to set it unique
will render a syntax error. ( Trick, just add a new index on the
EmployeeID column and set that unique, gets the behavior I wanted )
Alright..so I'm feeling pretty good..I got tables, I got relations, they
are enforced now.
The Query designer - just like the relation window the FK is not picked
up and therefore if you add the tables to a query the JOIN is not
automatically created for you. The same for the Form Wizard not
suggesting the Sub-form of EmployeesTasks for Employees Master. But
still not bad.
Now I have a couple of forms - they seem to work right. Got a couple of
queries..no problem I can find.
Time to test the reporting tools.
For this I want to have a little more data. Having a embedded Base file
with employee data, why not just import it via the Copy Table wizard.
Big problem here...won't work -can't figure out how to make it work
either not directly that is.
Let me elaborate. The embedded Base database also has the Departments
table with some data. I used the copy table wizard to import that first.
There are some issues, but you can do it. The problems are these. When
you start the wizard and add the two columns ID and Department it gives
a warning. That it can find a matching data type for Departments. The
column is a varchar(50) so it creates a MEMO with length of 50? No
problem, easy fix. The ID field comes over as an Integer, NOT NULL - but
no way to set it as Auto Increment as it is in the source database, and
no way to set it as a PK during the copy table function. Still, once you
fix up the memo to varchar it does import the data without a problem.
The rest can be fixed with DDL statements.
Now time to move over the data in the Employees table using the Copy
Table Wizard and Append data. Here the wzard acts differently. When it
sees the first VARCHAR field it gives the same warning that the data
type can not be matched and it stops. You can not proceed to the next
step, which I suppose makes sense as there is no place to correct the
data type and it will not try to write a MEMO field to a VARCHAR field.
In other words they only way to move the data is to copy it to a dummy
table in the target database, then use a INSERT statement in the SQL
window to get it to the correct Employees table...arrrgh...but that works.
Reports then. Short and sweet. Didn't have any problems with either the
report wizard or the report builder.
Now the fun part - performance and memory usage...gets a little dicey here.
Last weekend I had Derby running as a server on my XP machine ( as
Localhost ). Yesterday I switched and used Derby in embedded mode right
in the same JVM as from Base. This was my plan from the start... ( yes I
noticed the todo item about embedding Derby in a Base file..so I wondered )
No Derby server running anywhere, instead when I created the Base file I
used the driver class 'org.apache.derby.jdbc.EmbeddedDriver' and a
connection string of jdbc:derby:mynewDB;create=true
First time the file is opened then it creates the directory
<OfficeInstallPath>\programs\mynewDB and all the necessary directories
and files. Comes up without even asking for a username or password. It
is pretty nice this way then.
As long as the tables didn't have much data it is also fast, really
fast. ( well Duh! )..The memory usage is about 37 Megs just to open a
database - which is a little less then a embedded HSQL Base file on my
machine. ( I think - next weekend I'll do this all again with much more
detail on the memory usage)
OK - time to bring out the Baseball statistics database -
I simple test copy the table Master from the HSQL Embedded data Base
file to the new Derby EmbeddedDriver, disk storage data, Base file. The
file is 16,361 records and
Alright - just ran out of time...LOL..meaning I have to stop typing not
that it didn't finish. I'll finish this in a little bit. The results of
working with Derby versus HSQL embedded on this table are rather
interesting.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
