Issue 108303 - [DBF]: Primkey can not be set for existing columns via GUI table editor
Summary: [DBF]: Primkey can not be set for existing columns via GUI table editor
Status: REOPENED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.2 RC2
Hardware: Unknown Windows, all
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
: 108302 (view as issue list)
Depends on:
Blocks:
 
Reported: 2010-01-14 00:10 UTC by mhatheoo
Modified: 2013-01-29 21:39 UTC (History)
5 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
message to the non functioning process (77.78 KB, text/plain)
2010-01-14 00:11 UTC, mhatheoo
no flags Details
screencapture with problems on label- and type-detection (161.45 KB, text/plain)
2010-03-08 00:31 UTC, mhatheoo
no flags Details
screencapture with problems on label- and type-detection (161.45 KB, image/jpeg)
2010-03-08 00:31 UTC, mhatheoo
no flags Details
zip-file with two DBF - one wellformed and one 000-desaster (524 bytes, application/x-gzip)
2010-04-26 18:11 UTC, mhatheoo
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description mhatheoo 2010-01-14 00:10:02 UTC
supposed you import a DBF as new to an ODB-file and refuse to build the primkey
on import, it is lateron impossibe to set the primary key.
adding a field as integer will work
setting this to primkey will fail, as a smart process to set the primary key is
broken.

should work - so a Stopper 

Martin
Comment 1 mhatheoo 2010-01-14 00:11:51 UTC
Created attachment 67179 [details]
message to the non functioning process
Comment 2 Frank Schönheit 2010-01-14 07:18:03 UTC
*** Issue 108302 has been marked as a duplicate of this issue. ***
Comment 3 drewjensen.inbox 2010-03-07 17:52:20 UTC
Yes it fails - as it should.

If you add a column of type INT and then table is empty then you can set it to
the primary key.

If the table has existing records then this new field will have all NULL values
in it for every current record. The primary key designation requires that the
field be REQUIRED and NOT NULL.

Add data to the column for existing records (unique values of course) and then
the change to primary key will work.

Setting the new field type to AUTO_VALUE will not help you here either, as this
only fires when a NEW record is inserted. 

For help with the steps required to perform the AUTO_VALUE field addition please
ask on the dba users mailing list or at the user.services forum

Setting issue to INVALID
Comment 4 Mechtilde 2010-03-07 18:28:15 UTC
invalid -> closed
Comment 5 mhatheoo 2010-03-07 23:36:34 UTC
Well Drew, I guess you already know, that something has broken from the RC2,
whereto this issue was mailed, to the actual RC5 or 3.2. final.
Adding primary key is broken aswell as copying DBF-files to a ODB.

Note: in principle you are absolutly right about the solution for how-to add a
primary key. 
But since it is impossible to add a primary key to a base-table manually or set
a given field as primary key overruling to assistent, this is - in all versions
of base  - a glitch. 

So actually the only way to import DBF-File is, to use the assistent. 
But that is broken due to the label-import-issue.
And by that the reuse of self-constructed auto-increment fields as primary key
is impossible due to to problems with the problems by move a field to the and,
as the constraints are not functioning due to label-import-issue, except you
manually edit each fieldlabel (which is a lousy but the only way to import, as
the field-type-detection is not functioning to)

However, in the result it is not possible to edit the settings for the primary
key in a sufficient manner.
Re-opend, and I guess you will leave it open until some of the basic are solved,
and until it is even possible to judge whether this issue is solved or not . Thanks.

Martin     
Comment 6 drewjensen.inbox 2010-03-08 00:02:03 UTC
nope didn't know any of that...
Comment 7 drewjensen.inbox 2010-03-08 00:16:08 UTC
Ok - I can import dBase files to an embedded database and include a PK when
doing so and have no problem with labels at all? Do you have an issue number on
that one.

I can also add a Primary Key IF I add a new field.

I can not change an existing field to a primary key, it seems.




Comment 8 drewjensen.inbox 2010-03-08 00:29:39 UTC
oh and one correction - adding an auto_increment integer field to an existing
table does indeed, fill all existing records with proper values. So you can do
both steps in the table editor without a problem.

Again - I am using XP and OOo 3.2 

Changed summary for PK problem
Confirmed and assign to developer

Finally the problem is that a drop primary key is issued without If EXISTS.

However you can assign Primary Key to an existing table column using the SQL DDL
in the SQL window.

so given a table with an column id you can make that the PK with

ALTER TABLE "Table1" ADD PRIMARY KEY ("id" )

Comment 9 mhatheoo 2010-03-08 00:31:13 UTC
Created attachment 68201 [details]
screencapture with problems on label- and type-detection
Comment 10 mhatheoo 2010-03-08 00:31:35 UTC
Created attachment 68202 [details]
screencapture with problems on label- and type-detection
Comment 11 drewjensen.inbox 2010-03-08 00:36:45 UTC
@mhatheoo Please one problem per issue - as you can see it can be hard enough
keeping up with just one at times :>)

Just looked at the screen shots and I have more questions then answers.

Would you mind if we move this to the mailing list for now and if need be I'll
open another issue for the labels - but I want to ask about how you are trying
to import that dBase table first. OK?
Comment 12 mhatheoo 2010-03-08 00:37:01 UTC
this attachement shows my problem with importing DBF: 
as shown in CALC and 
as shown in Base-import-assistent
all labels and fields needed manual fixing

rgds
Martin

P.S.:
sorry for posting attachement double 
Comment 13 mhatheoo 2010-03-08 17:36:03 UTC
Yes of course, one problem only.
However, you need to get these info to see, that these problems are connected -
somehow. Nevertheless it would be kind if open the "label-issue" as new.

Idea: Bring data from DBase to OOO/HSQL

the way is:
open DBF in CALC with codepage 437
select all and paste it to an ODB-file.
normal dialog starts, but the labels fieldproperty-detection failed.
The result you can see, the assistent is not functioning. 

(and I just found, that in my manual label-edit I made such much mistakes, that
the resulting table is useless)

Martin


Comment 14 mhatheoo 2010-03-08 17:51:15 UTC
some additional words:
SUPPOSED you have 
-- ONE.odb, using the dbase-driver (routed to the appropriate path) and a 
-- TWO.odb with ordinary HSQL-tables, 
the COPY-dialog will fail too:

on ONE select table and copy
on TWO paste

the diaolg will start, but BOTH procedures (NEW with data or APPEND) will fail
with exception:
SQL-Status: 37000
Fehler-Code: -16

Wrong data type: java.lang.NumberFormatException

martin
Comment 15 drewjensen.inbox 2010-03-08 18:54:24 UTC
well - then we need to find out why your system is failing on this - because I
can import using the two ODB file solution (one of type dBase and the other HSQL)
without problem. Not once but I tried against half a dozen different dbf files
no problems.
Comment 16 mhatheoo 2010-03-09 16:58:36 UTC
tja, I do not know
(W2k with Java 6_18 - System is setup for DE)

martin
Comment 17 Mechtilde 2010-04-05 15:56:58 UTC
as Drew sid in his last comment -> worksforme
Comment 18 Mechtilde 2010-04-05 15:59:34 UTC
-> closed
Comment 19 mhatheoo 2010-04-05 20:30:49 UTC
Mechtilde
I do not know for what reason you closed this, at actual nothing works with
importing from DBF, esp. the reuse of a field that had been used as a counter
within the external application can set and reused to primary key within OOO.
(here OOO320M14)
(okay, the index-glitch is only one item, the glitch with wrong field-detection
another, the too-long-string for HSQL for building the table are problems too,
but as said, we leave it here with one problem) 

Give it a try for your own, and let me see the screencaptures and I will give it
a try too, and than ... maybe

Martin
Comment 20 mhatheoo 2010-04-06 17:43:28 UTC
Mechtilde - I guess you already found, where the small word "not" is missing


Just to put the things together:
The question was, whether it is possible, to make use of DBF in OOO
esp. make reuse of a DBF-field in OOO as prim.Key-field.

The test-scenario is quite simple: save the - in OOO altered - DBF and reopen it
in the application, where the original DBF is derived from.
if it works, all is fine (which is not so!)

a)
open DBF in in an ODB-Container: works, but ...
- put in data in OOO: works
- reopen in OOO: works
- reopen in DBF-appl.: broken 
broken because: OOO does not know how to handle "numeric" fields - adds always
.00 and kills counter-fields

b)
- open in ODB and copy to CALC and save as DBF:
totally broken (no fixed length, no numeric, none functioning header)

c)
copy from any DBF from within any OOO-source (ODB or CALC) and paste it to
HSQL-File:
-copy: works
- paste: works partly (see below)
- reexport to DBF: broken

paste: is working, BUT ...
but-1.)
No "numeric" field is treated right: 
DBF-Numeric-N0 == INT
DBF-Numeric-N4 ==  decimal 4 decimal places
in NO CASE a dbf-numeric is a OOO-"number(numeric)"-type!!
Note: if the DBF-N0 is used as a counter or Autoincr.Prim.Key, OOO actually
breaks it in any case, when set to INT aswell as when left as numeric

but-2.)
a counter-field can be set to be the prime-key, but that's strange
- copy DBF
- paste DBF to new and correct in the dialog-boxes:
--- new ID? No
--- add all fields
--- change fieldtype of manually selected field to INT 
--- change that field with right-click to PRIM-KEY
-- close and reopen table
--- change THAT field to AUTO-INCR
--- close table
--- open SQL-Window from menue
--- put in statement "alter table "tabelle1" alter column "id" restart with 12000"
the counter is working 
(note: it is now(m14) working as it was in m2 - the double-issue from m5
regarding the glitch from changing fields in table-definitions had been partly
repaired)
 

d)
same as c) - with more than 10 filelds in original DBF-File:
- copy: works
- paste: broken terminates with "parameter too long"
the behaviour to put all requested parameters for to build the new HSQL-table in
one string will not work, as it exceeds the allowed length of such parameters. 

By that, the "workaround-with-special-advise" can not be tested for version d)
and is treated as broken.


All in all: No good idea to close this issue until some homework is done.

Martin


Comment 21 mhatheoo 2010-04-26 02:16:30 UTC
Hello everybody

itn't it time to set this issue to state "new" ?

Martin
Comment 22 ocke.janssen 2010-04-26 07:38:49 UTC
@Martin: Could you please add a sample dbf file which I could use to reproduce
it. Thanks.
Comment 23 mhatheoo 2010-04-26 18:11:18 UTC
Created attachment 69133 [details]
zip-file with two DBF - one wellformed and one 000-desaster