Re^3: Access won´t allow ENUM, DEFAULT etc in CREATE

I´ve written a little test-script for working with ODBC. The real one still got ODBC but will be connected to a proper database then Acces 97. Everything works great with DBI::ODBC (win32 is not an option for me). When I give this query to the ODBC, MS Access returns a 'not a valid query'-error.

my $stt = $db->prepare("CREATE TABLE ".$row[0]."(id smallint, datum da
+te, mach smallint, van_afd smallint, naar_afd smallint, bewerking int
+, hoev_gepland int, gepland enum('true','false')");
[download]

(I also tried the escape-tags for the ' of the enum) Also

("CREATE TABLE ".$row[0]."(id smallint NOT NULL)");
[download]

won´t work. While

("CREATE TABLE ".$row[0]."(id smallint)");
[download]

works perfectly. Am I missing something? Greetings, Joris

There's your complete question. I did read it. Here's my understanding of the steps you took:

You have a CREATE TABLE command with several fields that fails;
You have a simple CREATE TABLE that works;
You've tried escaping the ' characters in the enum field, but that made no difference; and
Now you're stuck and would like our help and suggestions.

So, what I suggested was that you go back and figure out what was broken by taking 'baby steps' from the simple command that works, to the complicated command that doesn't work. Once you find out what breaks the command, you know what to fix.

Anyway, it seems you got the answer that ENUM is not a real SQL command, so you're on your way, and good luck with that.

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Comment on Re^3: Access won´t allow ENUM, DEFAULT etc in CREATE Select or Download Code

Replies are listed 'Best First'.
Re^4: Access won´t allow ENUM, DEFAULT etc in CREATE by Lytse Flap (Initiate) on Mar 15, 2005 at 08:43 UTC
I took far more steps befor I got at this point and knew that the problem was with ENUM ;) I´ve solved this problem a bit ugly. I made in the query a variable wich will take the 'shape' of the database flavour. `"CREATE TABLE ".$row[0]."(id $db_ind PRIMARY KEY,..` So in MSAcces 97 it wil be: `$db_ind ="COUNTER"` And in MSAcces 2000+: `$db_ind ="COUNTER NOT NULL"` In MySQL: `$db_ind ="AUTO_INCREMENT NOT NULL"` Well, this is the basic thought. For now I have to find out how many databases support this and which one might this company purchase.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: Access won´t allow ENUM, DEFAULT etc in CREATE
by Lytse Flap (Initiate) on Mar 15, 2005 at 08:43 UTC

"CREATE TABLE ".$row[0]."(id $db_ind PRIMARY KEY,..

$db_ind ="COUNTER"

$db_ind ="COUNTER NOT NULL"

$db_ind ="AUTO_INCREMENT NOT NULL"

[reply]
[d/l]
[select]