dave boland | 25 Aug 22:46 2011

Base List Tables Questions

I'm creating a database that has a main table and a few list tables to
supply data for list boxes.  I have been following the User Guide and
created three list tables.  When I go to link them I get errors about
the keys.  The errors make no sense to me, so what I need is a complete
procedure on how to do this (would also suggest updating the
documentation).

tMainTable:
--------------------------
ID, Integer                   // Key
Date, Date
EmployeeName, text VARCHAR
EmployeeAddr, text VARCHAR
EmployeeType, text VARCHAR
EmployeeDept, text VARCHAR
EmployeePos,  text VARCHAR

tEmployeeType:
---------------------------
EmployeeType, text VARCHAR
EmployeeTypeID, INTEGER        // Autovalue = yes, key

tEmployeeDept:
---------------------------
EmployeeDept, text VARCHAR
EmployeeDeptID, INTEGER        // autovalue = yes, Key

tEmployeePos:
---------------------------
EmployeePos, text VARCHAR
(Continue reading)

Andreas Säger | 25 Aug 23:14 2011
Picon

Re: Base List Tables Questions

Base is NOT a database program. Assuming that you are writing about a HSQLDB
of version 1.8 I suggest the following query to be run in direct SQL mode.
Dump the results of that query into a report, spreadsheet or whatever type
of office document.
HSQLDB 1.8 is documented fully at http://hsqldb.org/doc/guide/

#######################################
SELECT
   A.table_type,  -- TABLE or VIEW
   A.hsqldb_type,   -- CACHED or TEXT
   A.table_name,
   B.column_name,
   B.type_name,
   B.column_size

FROM information_schema.system_tables as A,
information_schema.system_columns as B
--  information_schema.system_indexinfo  is index information

WHERE A.table_schem = 'PUBLIC'
   AND A.table_name = B.table_name

ORDER by A.TABLE_TYPE, A.table_name, B.ordinal_position
###############################

This one lists all indices, foreign keys included:
-- Index information from all tables in database

SELECT
   A.table_type, 
(Continue reading)

dave boland | 25 Aug 23:35 2011

Re: Re: Base List Tables Questions

Andreas,

Wow!  Where to start.  First, Base (part of LibreOffice 3.3) is
described as a database program, and that is what I want.

As for the db engine, I have no clue, and could care less.

As for the query -- why???  I'm trying to follow along with the Getting
Started with Base guide, but it has left out a step or two.  So I need
someone to fill in the missing information based on what I have, not
what you have.

Don't mean to be fussy, but I don't see how this helps me in any way,
and I don't fully understand it.  So I would appreciate it if someone
could explain how I make the links so I can creat a form and some
reports.

Thanks again,
Dave

On Thu, 25 Aug 2011 14:14 -0700, "Andreas Säger" <villeroy <at> t-online.de>
wrote:
> Base is NOT a database program. Assuming that you are writing about a
> HSQLDB
> of version 1.8 I suggest the following query to be run in direct SQL
> mode.
> Dump the results of that query into a report, spreadsheet or whatever
> type
> of office document.
> HSQLDB 1.8 is documented fully at http://hsqldb.org/doc/guide/
(Continue reading)

planas | 25 Aug 23:42 2011
Picon

Re: Base List Tables Questions

Dave

On Thu, 2011-08-25 at 16:46 -0400, dave boland wrote: 

> I'm creating a database that has a main table and a few list tables to
> supply data for list boxes.  I have been following the User Guide and
> created three list tables.  When I go to link them I get errors about
> the keys.  The errors make no sense to me, so what I need is a complete
> procedure on how to do this (would also suggest updating the
> documentation).

What is the error message, you may need a series of one-to-one links
between the list tables and the main table. I suspect you will need a
foreign key for each list table in the main table. The main table will
look like this:

ID, Integer                   // Key
Date, Date
EmployeeName, text VARCHAR
EmployeeAddr, text VARCHAR
EmployeeType, text VARCHAR
EmployeeDept, text VARCHAR
EmployeePos,  text VARCHAR
EmployeeTypeID, Integer (Foreign Key to EmployeeType table)
EmployeeDeptID, Integer (Foreign Key to EmployeeDepartment table)
EmployeePosID, Integer (Foreign Key to EmployeePos) 

> tMainTable:
> --------------------------
> ID, Integer                   // Key
(Continue reading)

dave boland | 26 Aug 00:25 2011

Re: Base List Tables Questions

planas,

I suspect you are on to something here.  The error (detailed version)
is:

SQL Status: S0011
Error code: -170

Primary or unique constraint required on main table: "tEmployeeType" in
statement [ALTER TABLE "tMainTable" ADD  FOREIGN KEY ("EmployeeType")
REFERENCES "tEmployeeType" ("EmployeeType")]

It looks like the relationship system is trying to add a key to
tMainTable, but can't for some reason.

Dave,

-- 
  dave boland
  dboland9 <at> fastmail.fm

--

-- 
http://www.fastmail.fm - Does exactly what it says on the tin

planas | 26 Aug 02:57 2011
Picon

Re: Base List Tables Questions

Dave

On Thu, 2011-08-25 at 18:25 -0400, dave boland wrote: 

> planas,
> 
> I suspect you are on to something here.  The error (detailed version)
> is:
> 
> SQL Status: S0011
> Error code: -170
> 
> Primary or unique constraint required on main table: "tEmployeeType" in
> statement [ALTER TABLE "tMainTable" ADD  FOREIGN KEY ("EmployeeType")
> REFERENCES "tEmployeeType" ("EmployeeType")]
> 
> It looks like the relationship system is trying to add a key to
> tMainTable, but can't for some reason.
> 
> Dave,
> 

You can manually insert the each column into main. Under TOOLS select
RELATIONSHIPS. On the selection menu add the tables. Then under TOOLS (I
believe) select ADD Relationship. You then select the two keys that are
related Main.EmployeesID and Employees.EmployeeID (the correct foreign
key in Main with the key field of the table you are linking) 

> -- 
>   dave boland
(Continue reading)

Alexander Thurgood | 26 Aug 07:44 2011
Picon

Re: Base List Tables Questions

Le 25/08/11 22:46, dave boland a écrit :

Hi Dave,

> One other question -- how can I print out the table design (Edit view)?  

That is not currently possible from within the Base module itself, other
than via a screenshot (in fact, it was never possible, even with
StarOffice/OpenOffice.org).

I remember a macro written by Laurent Godard quite a long time ago that
sought to achieve a tree-like representation of the database by using
basic to query the field definitions and then use the acquired text to
fill text boxes in a Draw document, but that probably wouldn't exactly
be what you require.

Alex

--

-- 
For unsubscribe instructions e-mail to: users+help <at> global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

dave boland | 26 Aug 14:32 2011

Re: Re: Base List Tables Questions

Alex,

This would be a great feature to add as it is necessary to be able to
document the structure of all of the tables.

Dave,

On Fri, 26 Aug 2011 07:44 +0200, "Alexander Thurgood"
<alex.thurgood <at> gmail.com> wrote:
> Le 25/08/11 22:46, dave boland a écrit :
> 
> Hi Dave,
> 
> > One other question -- how can I print out the table design (Edit view)?  
> 
> That is not currently possible from within the Base module itself, other
> than via a screenshot (in fact, it was never possible, even with
> StarOffice/OpenOffice.org).
> 
> I remember a macro written by Laurent Godard quite a long time ago that
> sought to achieve a tree-like representation of the database by using
> basic to query the field definitions and then use the acquired text to
> fill text boxes in a Draw document, but that probably wouldn't exactly
> be what you require.
> 
> 
> Alex
> 
> 
> 
(Continue reading)

Andreas Säger | 26 Aug 20:52 2011
Picon

Re: Base List Tables Questions

Am 26.08.2011 14:32, dave boland wrote:
> Alex,
>
> This would be a great feature to add as it is necessary to be able to
> document the structure of all of the tables.
>
> Dave,

As I already outlined for the built-in HSQLDB, the feature is already 
there.
You are a copy&paste away from it followed by a right-click>Report 
Wizard... and 2 or 3 more clicks.

--

-- 
For unsubscribe instructions e-mail to: users+help <at> global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


Gmane