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

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.
  • Comment on Re^2: Access won´t allow ENUM, DEFAULT etc in CREATE

Replies are listed 'Best First'.
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]
      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]

In Section Seekers of Perl Wisdom