Received: from smtp-out1.bellatlantic.net (smtp-out1.bellatlantic.net
	[199.45.39.156]) by hub.org (8.9.1/8.9.1) with ESMTP id BAA13599
	for <pgsql-interfaces@postgreSQL.org>;
	Fri, 1 Jan 1999 01:10:45 -0500 (EST)
	(envelope-from daybee@bellatlantic.net)
Received: from daveh (client-151-196-125-99.bellatlantic.net [151.196.125.99])
	by smtp-out1.bellatlantic.net (8.9.1/8.9.1) with SMTP id BAA10967;
	Fri, 1 Jan 1999 01:10:39 -0500 (EST)
Message-ID: <01d601be354d$77b72dc0$637dc497@daveh>
From: "David Hartwig" <daybee@bellatlantic.net>
To: "Michael J. Sheldon" <msheldon@cbrcomm.com>,
	"'pgsql-interfaces@postgresql.org'" <pgsql-interfaces@postgreSQL.org>
Subject: Re: [INTERFACES] ODBC CREATE TABLE failures
Date: Fri, 1 Jan 1999 01:10:41 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.0810.800
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.0810.800

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
>