pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC CREATE TABLE failures
6+ messages / 4 participants
[nested] [flat]
* ODBC CREATE TABLE failures
@ 1999-01-01 05:12 Michael J. Sheldon <msheldon@cbrcomm.com>
0 siblings, 2 replies; 6+ messages in thread
From: Michael J. Sheldon @ 1999-01-01 05:12 UTC (permalink / raw)
To: pgsql-interfaces
I've just started testing postgreSQL as a backend database for an
NT-based intranet application running on Cold Fusion.
The application will automatically create the database structure if it
does not already exist, by executing a series of CREATE TABLE statements
through the ODBC driver.
However, only the first table is being created.
Each statement is in a separate request, and any one statement will
execute, but only if it is the first. Even more puzzling is the fact
that subsequent calls do not throw an error. The first error I see is
when an attempt is made to insert data into a non-existent table.
The application has tested out fine on both MS Access and MS SQL Server.
The ODBC driver version is 6.40.0002
Code follows:
(For those not familiar with Cold Fusion, the <CFQUERY> tags specify a
distinct database call. #attributes.datasource# is a variable name
containing the DSN)
<CFQUERY DATASOURCE="#attributes.datasource#">
CREATE TABLE FMGroups
(
GroupID INTEGER NOT NULL,
GroupName VARCHAR(64) NOT NULL,
Description VARCHAR(100),
CONSTRAINT ID_pk PRIMARY KEY (GroupID),
CONSTRAINT check_directory UNIQUE (GroupName)
)
</CFQUERY>
<CFQUERY DATASOURCE="#attributes.datasource#">
CREATE TABLE FMDirectories
(
DirectoryID INTEGER NOT NULL,
DirectoryName VARCHAR(64) NOT NULL,
Description VARCHAR(100),
ParentID INTEGER NOT NULL,
CONSTRAINT ID_pk PRIMARY KEY (DirectoryID),
CONSTRAINT check_directory UNIQUE (DirectoryName)
)
</CFQUERY>
<CFQUERY DATASOURCE="#attributes.datasource#">
CREATE TABLE FMFiles
(
FileID INTEGER NOT NULL,
DirectoryID INTEGER,
FilePath VARCHAR(255) NOT NULL,
DisplayName VARCHAR(100) NOT NULL,
Description VARCHAR(100),
AddedBy VARCHAR(64) NOT NULL,
DateModified datetime NOT NULL,
CONSTRAINT ID_pk PRIMARY KEY (FileID),
CONSTRAINT check_path UNIQUE (FilePath),
CONSTRAINT check_directory FOREIGN KEY (DirectoryID) REFERENCES
FMDirectories (DirectoryID)
)
</CFQUERY>
<CFQUERY DATASOURCE="#attributes.datasource#">
CREATE TABLE FMPermissions
(
GroupID INTEGER NOT NULL,
DirectoryID INTEGER NOT NULL,
Permissions INTEGER NOT NULL,
CONSTRAINT ID_pk PRIMARY KEY (GroupID, DirectoryID),
CONSTRAINT check_group FOREIGN KEY (GroupID) REFERENCES FMGroups
(GroupID),
CONSTRAINT check_directoryID FOREIGN KEY (DirectoryID)
REFERENCES FMDirectories (DirectoryID)
)
</CFQUERY>
<CFQUERY DATASOURCE="#attributes.datasource#">
CREATE TABLE FMUserGroups
(
USERNAME VARCHAR(64) NOT NULL,
GroupID INTEGER NOT NULL,
CONSTRAINT check_groupID FOREIGN KEY (GroupID) REFERENCES
FMGroups (GroupID),
CONSTRAINT ID_pk PRIMARY KEY (USERNAME)
)
</CFQUERY>
Michael J. Sheldon
CBR Communications
Graphic design and Internet services.
A division of Commercial Blueprint Co., Inc.
http://www.cbrcomm.com
(602)707-1545
PGP Key available on request
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] ODBC CREATE TABLE failures
@ 1999-01-01 05:40 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: Michael J. Sheldon <msheldon@cbrcomm.com>
1 sibling, 0 replies; 6+ messages in thread
From: Thomas G. Lockhart @ 1999-01-01 05:40 UTC (permalink / raw)
To: Michael J. Sheldon <msheldon@cbrcomm.com>; +Cc: pgsql-interfaces
> The application will automatically create the database structure if it
> does not already exist
> However, only the first table is being created.
> Code follows:
<snip>
You can test this code by running it through psql, and it fails there
too. Each CREATE TABLE statement is trying to use the same label for the
primary key constraint, but since Postgres enforces the primary key by
generating an implicit index this fails. Try using unique names for
constraints!
If for some reason these constraint names *must* be identical, you could
consider modifying the code in the parser to generate a unique internal
name based on both the constraint name and the table name, for example.
There is already a routine there to help you do this.
Failure example follows...
- Tom
postgres=> CREATE TABLE FMGroups
postgres-> (
postgres-> GroupID INTEGER NOT NULL,
postgres-> GroupName VARCHAR(64) NOT NULL,
postgres-> Description VARCHAR(100),
postgres-> CONSTRAINT ID_pk PRIMARY KEY (GroupID),
postgres-> CONSTRAINT check_directory UNIQUE (GroupName)
postgres-> );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index id_pk for
table fmgroups
NOTICE: CREATE TABLE/UNIQUE will create implicit index check_directory
for table fmgroups
CREATE
postgres=> CREATE TABLE FMDirectories
postgres-> (
postgres-> DirectoryID INTEGER NOT NULL,
postgres-> DirectoryName VARCHAR(64) NOT NULL,
postgres-> Description VARCHAR(100),
postgres-> ParentID INTEGER NOT NULL,
postgres-> CONSTRAINT ID_pk PRIMARY KEY (DirectoryID),
postgres-> CONSTRAINT check_directory UNIQUE (DirectoryName)
postgres-> );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index id_pk for
table fmdirectories
NOTICE: CREATE TABLE/UNIQUE will create implicit index check_directory
for table fmdirectories
ERROR: Cannot create index: 'id_pk' already exists
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] ODBC CREATE TABLE failures
@ 1999-01-01 06:10 David Hartwig <daybee@bellatlantic.net>
0 siblings, 1 reply; 6+ messages in thread
From: David Hartwig @ 1999-01-01 06:10 UTC (permalink / raw)
To: Michael J. Sheldon <msheldon@cbrcomm.com>; pgsql-interfaces
What jumps out at me is those constraints. The PostgreSQL backend does
not currently support the forgien keys syntax nor the UNIQUE keyword.
However, they can be implemented using other methods.
For foreign keys, check out the contrib/spi directory. (refint.* stuff)
The full syntax will be supported at some time in the future.
For unique, create a unique index on the necessary fields.
You should inspect and send the CommLog, so that we can determine exectaly
where the failure is occuring. It can be activated in the DSN setup and
is located in D:\psqlodbc-*.log
----- Original Message -----
From: Michael J. Sheldon <msheldon@cbrcomm.com>
To: 'pgsql-interfaces@postgresql.org' <pgsql-interfaces@postgreSQL.org>
Sent: Friday, January 01, 1999 12:12 AM
Subject: [INTERFACES] ODBC CREATE TABLE failures
>I've just started testing postgreSQL as a backend database for an
>NT-based intranet application running on Cold Fusion.
>
>The application will automatically create the database structure if it
>does not already exist, by executing a series of CREATE TABLE statements
>through the ODBC driver.
>
>However, only the first table is being created.
>
>Each statement is in a separate request, and any one statement will
>execute, but only if it is the first. Even more puzzling is the fact
>that subsequent calls do not throw an error. The first error I see is
>when an attempt is made to insert data into a non-existent table.
>
>The application has tested out fine on both MS Access and MS SQL Server.
>
>The ODBC driver version is 6.40.0002
>
>Code follows:
>(For those not familiar with Cold Fusion, the <CFQUERY> tags specify a
>distinct database call. #attributes.datasource# is a variable name
>containing the DSN)
>
><CFQUERY DATASOURCE="#attributes.datasource#">
> CREATE TABLE FMGroups
>
> GroupID INTEGER NOT NULL,
> GroupName VARCHAR(64) NOT NULL,
> Description VARCHAR(100),
> CONSTRAINT ID_pk PRIMARY KEY (GroupID),
> CONSTRAINT check_directory UNIQUE (GroupName)
> )
></CFQUERY>
><CFQUERY DATASOURCE="#attributes.datasource#">
> CREATE TABLE FMDirectories
>
> DirectoryID INTEGER NOT NULL,
> DirectoryName VARCHAR(64) NOT NULL,
> Description VARCHAR(100),
> ParentID INTEGER NOT NULL,
> CONSTRAINT ID_pk PRIMARY KEY (DirectoryID),
> CONSTRAINT check_directory UNIQUE (DirectoryName)
> )
></CFQUERY>
><CFQUERY DATASOURCE="#attributes.datasource#">
> CREATE TABLE FMFiles
>
> FileID INTEGER NOT NULL,
> DirectoryID INTEGER,
> FilePath VARCHAR(255) NOT NULL,
> DisplayName VARCHAR(100) NOT NULL,
> Description VARCHAR(100),
> AddedBy VARCHAR(64) NOT NULL,
> DateModified datetime NOT NULL,
> CONSTRAINT ID_pk PRIMARY KEY (FileID),
> CONSTRAINT check_path UNIQUE (FilePath),
> CONSTRAINT check_directory FOREIGN KEY (DirectoryID) REFERENCES
>FMDirectories (DirectoryID)
> )
></CFQUERY>
><CFQUERY DATASOURCE="#attributes.datasource#">
> CREATE TABLE FMPermissions
>
> GroupID INTEGER NOT NULL,
> DirectoryID INTEGER NOT NULL,
> Permissions INTEGER NOT NULL,
> CONSTRAINT ID_pk PRIMARY KEY (GroupID, DirectoryID),
> CONSTRAINT check_group FOREIGN KEY (GroupID) REFERENCES FMGroups
>(GroupID),
> CONSTRAINT check_directoryID FOREIGN KEY (DirectoryID)
>REFERENCES FMDirectories (DirectoryID)
> )
></CFQUERY>
><CFQUERY DATASOURCE="#attributes.datasource#">
> CREATE TABLE FMUserGroups
>
> USERNAME VARCHAR(64) NOT NULL,
> GroupID INTEGER NOT NULL,
> CONSTRAINT check_groupID FOREIGN KEY (GroupID) REFERENCES
>FMGroups (GroupID),
> CONSTRAINT ID_pk PRIMARY KEY (USERNAME)
> )
></CFQUERY>
>
>Michael J. Sheldon
>CBR Communications
>Graphic design and Internet services.
>A division of Commercial Blueprint Co., Inc.
>http://www.cbrcomm.com
>(602)707-1545
>
>PGP Key available on request
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] ODBC CREATE TABLE failures
@ 1999-01-01 14:36 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: David Hartwig <daybee@bellatlantic.net>
0 siblings, 0 replies; 6+ messages in thread
From: Thomas G. Lockhart @ 1999-01-01 14:36 UTC (permalink / raw)
To: David Hartwig <daybee@bellatlantic.net>; +Cc: Michael J. Sheldon <msheldon@cbrcomm.com>; pgsql-interfaces
> The PostgreSQL backend does
> not currently support the foreign keys syntax nor the UNIQUE keyword.
> However, they can be implemented using other methods.
> For unique, create a unique index on the necessary fields.
NOTICE: CREATE TABLE/UNIQUE will create implicit index
check_directory for table fmgroups
The UNIQUE keyword is supported, unless there is some other feature or
behavior that you are expecting?
And, to split hairs, Postgres does support the foreign key *syntax*, but
throws it away and does nothing with it in the backend. So, I'd say it
supports foreign key syntax but does not support foreign keys ;)
Regards.
- Tom
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] ODBC CREATE TABLE failures
@ 1999-01-01 16:56 David Hartwig <daybee@bellatlantic.net>
0 siblings, 0 replies; 6+ messages in thread
From: David Hartwig @ 1999-01-01 16:56 UTC (permalink / raw)
To: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; +Cc: pgsql-interfaces
----- Original Message -----
From: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
To: David Hartwig <daybee@bellatlantic.net>
Cc: Michael J. Sheldon <msheldon@cbrcomm.com>;
'pgsql-interfaces@postgresql.org' <pgsql-interfaces@postgreSQL.org>
Sent: Friday, January 01, 1999 9:36 AM
Subject: Re: [INTERFACES] ODBC CREATE TABLE failures
>> The PostgreSQL backend does
>> not currently support the foreign keys syntax nor the UNIQUE keyword.
>> However, they can be implemented using other methods.
>> For unique, create a unique index on the necessary fields.
>
>NOTICE: CREATE TABLE/UNIQUE will create implicit index
> check_directory for table fmgroups
>
>The UNIQUE keyword is supported, unless there is some other feature or
>behavior that you are expecting?
>
>And, to split hairs, Postgres does support the foreign key *syntax*, but
>throws it away and does nothing with it in the backend. So, I'd say it
>supports foreign key syntax but does not support foreign keys ;)
That's what I get for answering e-mail at half past 1999.
Happy New Year
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] ODBC CREATE TABLE failures
@ 1999-01-04 16:37 Byron Nikolaidis <byronn@insightdist.com>
parent: Michael J. Sheldon <msheldon@cbrcomm.com>
1 sibling, 0 replies; 6+ messages in thread
From: Byron Nikolaidis @ 1999-01-04 16:37 UTC (permalink / raw)
To: Michael J. Sheldon <msheldon@cbrcomm.com>; +Cc: pgsql-interfaces
Michael J. Sheldon wrote:
> I've just started testing postgreSQL as a backend database for an
> NT-based intranet application running on Cold Fusion.
>
> The application will automatically create the database structure if it
> does not already exist, by executing a series of CREATE TABLE statements
> through the ODBC driver.
>
> However, only the first table is being created.
>
> Each statement is in a separate request, and any one statement will
> execute, but only if it is the first. Even more puzzling is the fact
> that subsequent calls do not throw an error. The first error I see is
> when an attempt is made to insert data into a non-existent table.
>
> The application has tested out fine on both MS Access and MS SQL Server.
>
I would like to rule out any odbc driver problems. Can you please send me a
copy of the "psqlodbc.log" file of the session in question? That would be a
minimum, and if you want, you could also send an odbc trace log of the
session which, together with the other log, would give me all the
information I need.
Byron
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-01-04 16:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-01 05:12 ODBC CREATE TABLE failures Michael J. Sheldon <msheldon@cbrcomm.com>
1999-01-01 05:40 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-01-04 16:37 ` Byron Nikolaidis <byronn@insightdist.com>
1999-01-01 06:10 Re: [INTERFACES] ODBC CREATE TABLE failures David Hartwig <daybee@bellatlantic.net>
1999-01-01 14:36 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-01-01 16:56 Re: [INTERFACES] ODBC CREATE TABLE failures David Hartwig <daybee@bellatlantic.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox