Ah, thanks. But it seems to me that NOT NULL is a common SQL tag.

NOT NULL is not the same as DEFAULT. DEFAULT means that if a value is not supplied in insertion, the database gives one by, well, default. NOT NULL means this field must not be omitted in the INSERT (unless there's a default!), nor can it be set to NULL (which is not the same thing as an empty string in SQL) later with an UPDATE.

...some perl code here
DBI->trace(1,"/path/to/logfile");
... DBI code that gives errors here ...
DBI->trace(0);
...some perl code here
[download]

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.

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.

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