Re: [SQL] how un-suscribe from this list

2007-01-08 Thread Richard Broersma Jr 
--- "Manso Gomez, Ramon" <[EMAIL PROTECTED]> wrote:
> I would like to unsuscribe from this list. I don´t know how to do it.
>  
> Thanks


If you are able to view the full headers for any email you get from one of the 
postgresql mailing
lists you would see the following line:
List-Unsubscribe: [EMAIL PROTECTED] 

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Help with Array

2007-01-08 Thread Jeremiah Elliott 

I have a java application that moves data from our erp platform
(progress) into a Postgres database. One of the fields in progress is
a character array. I haven't had any luck getting this field to
transfer. What I would like to do is something like:


preparedStatement.setArray(i,resultSet.getArray(i));


however this throws java.lang.UnsupportedOperationException
I have tried several other ways, with no luck.

...
preparedStatement.setObject(i,resultSet.getObject(i));
...
throws
java.sql.SQLException: ERROR: column "so_slspsn" is of type character
varying[] but expression is of type character varying


and

...
preparedStatement.setObject(i,resultSet.getObject(i),2003);
...
throws
org.postgresql.util.PSQLException: Unsupported Types value: 2,003


What am i doing wrong?

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Help with Array

2007-01-08 Thread Aaron Bono 

On 1/8/07, Jeremiah Elliott <[EMAIL PROTECTED]> wrote:


I have a java application that moves data from our erp platform
(progress) into a Postgres database. One of the fields in progress is
a character array. I haven't had any luck getting this field to
transfer. What I would like to do is something like:


preparedStatement.setArray(i,resultSet.getArray(i));


however this throws java.lang.UnsupportedOperationException
I have tried several other ways, with no luck.

...
preparedStatement.setObject(i,resultSet.getObject(i));
...
throws
java.sql.SQLException: ERROR: column "so_slspsn" is of type character
varying[] but expression is of type character varying


and

...
preparedStatement.setObject(i,resultSet.getObject(i),2003);
...
throws
org.postgresql.util.PSQLException: Unsupported Types value: 2,003


What am i doing wrong?




For your first attempt, it looks to me like one of the JDBC drivers you are
using doesn't support Arrays.  I would start by separating out the setArray
and getArray onto separate lines of code to see which is the offender.

The second one using get/setObject shows that the data types don't match.
Regardless of how you approach it you are probably going to need to do some
type conversion in your Java application itself.  Do a query on both tables
and then dig through the Meta Data on your result set to see what the JDBC
data types are so you know what you are getting with the get/setObject
methods.

It may be better to ask a JDBC group.

--
==
  Aaron Bono
  Aranya Software Technologies, Inc.
  http://www.aranya.com
  http://codeelixir.com
==

[SQL] Table relationships

2007-01-08 Thread Curtis Scheer 
I'm having trouble determining the best way to implement the following
scenario for a customer database.

 

Given the following tables what is the best way to link an address table to
both the "Master" and the "Detail" tables. Basically there can be many
addresses for each customermaster record and also many address for each
customerdetail record. Will this require two many-to-many tables? Or is
there a better solution I am missing? Is there any easy way to build in a
"Default" address or would that be something to do logically in client code?

 

Thanks,

Curtis

 

CREATE TABLE testing.customermaster

(

  customermasterid int4 NOT NULL DEFAULT
nextval('testing.customermatser_customermasterid_seq'::regclass),

  name varchar NOT NULL,

  description varchar,

)

 

CREATE TABLE testing.customerdetail

(

  customerdetailid int4 NOT NULL,

  customermasterid int4 NOT NULL,

  notes varchar,

  closed bool,

  customerdepartment varchar(3),

) 

 

CREATE TABLE address

(

  addressid int4,

  name varchar(40),

  addr varchar,

  city varchar(42),

  st varchar(2),

  zip varchar(30)

) 

 

 

Possible many-to-many tables



CREATE TABLE customermaster_address

(

  addressid int4,

 customermasterid int4

) 

 

CREATE TABLE customerdetail _address

(

  addressid int4,

 customerdetailid int4

)

Fwd: [SQL] Table relationships

2007-01-08 Thread Aaron Bono 

-- Forwarded message --
From: Aaron Bono <[EMAIL PROTECTED]>
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <[EMAIL PROTECTED]>

On 1/8/07, Curtis Scheer <[EMAIL PROTECTED]> wrote:


 I'm having trouble determining the best way to implement the following
scenario for a customer database.



Given the following tables what is the best way to link an address table
to both the "Master" and the "Detail" tables. Basically there can be many
addresses for each customermaster record and also many address for each
customerdetail record. Will this require two many-to-many tables? Or is
there a better solution I am missing? Is there any easy way to build in a
"Default" address or would that be something to do logically in client code?



Thanks,

Curtis



CREATE TABLE testing.customermaster

(

  customermasterid int4 NOT NULL DEFAULT nextval('
testing.customermatser_customermasterid_seq'::regclass),

  name varchar NOT NULL,

  description varchar,

)



CREATE TABLE testing.customerdetail

(

  customerdetailid int4 NOT NULL,

  customermasterid int4 NOT NULL,

  notes varchar,

  closed bool,

  customerdepartment varchar(3),

)



CREATE TABLE address

(

  addressid int4,

  name varchar(40),

  addr varchar,

  city varchar(42),

  st varchar(2),

  zip varchar(30)

)





Possible many-to-many tables

 CREATE TABLE customermaster_address

(

  addressid int4,

 customermasterid int4

)



CREATE TABLE customerdetail _address

(

  addressid int4,

 customerdetailid int4

)





I hope you left out the foreign keys for simplicity.  Make sure they get
into your database.

To answer your questions, I think it prudent to ask a few to get a better
understanding of the meaning of your tables:

Is there a difference between an address for the customer detail and an
address for the customer?

Is there some kind of significance to attaching an address to the customer
detail as opposed to the customer?  Attaching the address to the detail
gives it a customer by referencing through the detail.

Could you add a specific address to multiple customer and/or customer detail
records or is the address only assigned to one?

What it gets down to is that you must start with the LOGICAL data model and
ask yourself what are the meaning of the relationships and what
relationships make sense before you get down to creating the PHYSICAL
database.

--
==
  Aaron Bono
  Aranya Software Technologies, Inc.
  http://www.aranya.com
  http://codeelixir.com
==

Re: [SQL] Table relationships

2007-01-08 Thread Curtis Scheer 
->I hope you left out the foreign keys for simplicity.  Make sure they get
into your database.
Yes I left out the foreign keys for simplicity

-> Is there a difference between an address for the customer detail and an
address for the customer?

Not really an address is an address, it's a matter of specify an address for
the customer master record which basically represents an entire customer
while the customerdetail represents departments within that company that
might be at a different address then the company's main office for instance.


-> Could you add a specific address to multiple customer and/or customer
detail records or is the address only assigned to one?

Many addresses can belong to many customer detail records. The
customermaster table should only contain one address so that should be a one
to many relationship. I guess the real problem is defining which address is
the "Main Office" for a given company. So maybe making different "Types" of
addresses and referencing them to the customerdetail table is the best way
to go?  For instance

 

CREATE TABLE customerdetail _address

(

addressid int4, 
customerdetailid int4
addresstypeid varchar 

) 

CREATE TABLE testing.addresstype

(

  addresstypeid serial NOT NULL,

  shortdescription varchar(15) NOT NULL,

  description varchar(100),

  CONSTRAINT pk_addresstype_shortdescription PRIMARY KEY (shortdescription)

) 

 

The only other problem I see is if a particular customer has the same
address for all the departments in the company, then I guess the addressed
would exist multiple times but in the customerdetail_address table but the
user would only have to select that particular record rather then input the
same address again.

  _  

From: Aaron Bono [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 08, 2007 4:43 PM
To: [email protected]
Subject: Fwd: [SQL] Table relationships

 

-- Forwarded message --
From: Aaron Bono <[EMAIL PROTECTED]  >
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <[EMAIL PROTECTED]  >

On 1/8/07, Curtis Scheer <[EMAIL PROTECTED]  >
wrote:


I hope you left out the foreign keys for simplicity.  Make sure they get
into your database.

To answer your questions, I think it prudent to ask a few to get a better
understanding of the meaning of your tables: 

Is there a difference between an address for the customer detail and an
address for the customer?

Is there some kind of significance to attaching an address to the customer
detail as opposed to the customer?  Attaching the address to the detail
gives it a customer by referencing through the detail. 

Could you add a specific address to multiple customer and/or customer detail
records or is the address only assigned to one?

What it gets down to is that you must start with the LOGICAL data model and
ask yourself what are the meaning of the relationships and what
relationships make sense before you get down to creating the PHYSICAL
database. 

-- 
==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com  
   http://codeelixir.com  
==