#106 column named 'change' does not get accepted by mysql

closed-wont-fix
nobody
None
5
2005-10-04
2005-08-19
Marek Baczynski
No

Solution is simple: make it `change` in the query and
the problem goes away; unfortunately, I'm afraid
several similar changes will have to be made accross
selects/alters/drops/etc. An example patch for creates
attached.

Discussion

  • an example patch, by no means complete (against rev 915)

     
    Attachments
  • Oleg Broytman
    Oleg Broytman
    2005-08-22

    Logged In: YES
    user_id=4799

    I would very much like to recommend you to avaoid reserved
    words as table/column names. To make thing worse, SQLObject
    supports a number of different DBMS (MySQL, PostgreSQL and
    SQLite being the primary ones) and they all have different
    rules and lists of reserved keywords.

     
  • Logged In: YES
    user_id=838849

    Yes, I know. The problem is, how do I know if a keyword is
    reserved? Using backticks is the best way to avoid such
    problems, and I believe portable across different databases.
    Currently, what you're saying implies that SQLObject is
    _not_ portable (a table valid in one DBMS may throw
    exceptions in another.)

    BTW, I have used a column named 'grant' in the past without
    problems, a little bit of care was needed and that's all :)
    Besides, look at PHPMyAdmin. It puts backticks everywhere,
    just like I suggest to do. I am willing to submit a complete
    patch to do this for mysql (or even other databases if
    they're similar) if you could tell me which functions need
    to be changed.

     
  • Oleg Broytman
    Oleg Broytman
    2005-09-13

    Logged In: YES
    user_id=4799

    Yes, SQLObject is not completely portable, but tis is a
    problem of DBMSes, not SQLObject. For example, in SQL92 and
    SQL99 the word "user" is reserved, and PostgresSQL does not
    allow to create a table named "user"; MySQL and SQLite
    allow. But what if in the next version of MySQL the name
    will be reserved too? BTW, backticks are not portable
    solution AFAIK. PostgreSQL and SQL standards use double quotes.

     
  • Oleg Broytman
    Oleg Broytman
    2005-10-04

    • status: open --> closed
     
  • Oleg Broytman
    Oleg Broytman
    2005-10-04

    Logged In: YES
    user_id=4799

    When you create a column you can provide a dbName:

    change = StringCol(dbname="_change")

    or

    change = StringCol(dbname="`change`")

    or

    change = StringCol(dbname='"change"')

    for example...

     
  • Oleg Broytman
    Oleg Broytman
    2005-10-04

    • status: closed --> closed-wont-fix