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

Am I missing something?

Yes .. a quote from a character in The Big Chill comes to mind: "I'm not hung up on this completion thing."

Think about what you've done. You have a complicated query that fails, and a simple one that succeeds. So you stop there? No, you keep going, adding fields to the simple query until you get all the way to the complicated one that fails. Somewhere along the line, whatever you add that causes the query to fail, is most likely the problem.

I'd wager that if you tried the query again, excluding the enum field, as suggested above, the query would work.

Conclusion? Take it further than this, the next time you run into a problem.

Alex / talexb / Toronto

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

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

Replies are listed 'Best First'.
Re^2: Access won´t allow ENUM, DEFAULT etc in CREATE by Lytse Flap (Initiate) on Mar 14, 2005 at 22:48 UTC
Just a quetion. Have you red my complete question? I tried the simple codes. I did not write that this long queue was my first attempt with ODBC in Perl. I started with a dbm built in Acces. First with select * then with some rows, then with conditions. Then I built a table through ODBC. After succeeding that I came at this point that I couldn't add ENUM or NOT NULL. I've planned this traject carefully. I've got the answer. ENUM is not a real SQL-tag.	[reply]
Re^3: Access won´t allow ENUM, DEFAULT etc in CREATE by talexb (Chancellor) on Mar 15, 2005 at 05:21 UTC
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	[reply] [d/l] [select]
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]