Alpha Video Training
Results 1 to 3 of 3

Thread: PostgreSQL Problems

  1. #1
    Member
    Real Name
    Tom Krieg
    Join Date
    Jul 2012
    Posts
    2

    Default PostgreSQL Problems

    I'm new to Alpha5 but an experienced database administrator. I've just started to build my first "learning" project but have already come across a major problem which could turn out to be a show-stopper. I have a PostgreSQL 9.0 database that has all field names in tables in uppercase (this is a non-negotiable standard). That means these tables have to be referenced explicitly with quotation marks (e.g. select dbledger."ACCOUNT_CODE" from dbledger ... "). I can build the connection string and connect to the database just fine, however my first attempt at looking at table data using the database explorer fails with the error message shown below.

    Database API specific error. Your database has returned the following error code and description to Alpha5. Consult your database documentation for further information.

    7 - Error. Column "account_code" does not exist. Error while executing the query.

    The error is correct, the column "account_code" does not exist, however the column "ACCOUNT_CODE" does exist. It seems that when building a query, Alpha5 ignores case in field names, even though field names are shown in upper case in the database explorer. PostgreSQL field names are case sensitive. Is there anywhere in Alpha5 where I can set an "Options" parameter to tell Alpha5 NOT to ignore case in database field names?

  2. #2
    Member
    Real Name
    Tom Krieg
    Join Date
    Jul 2012
    Posts
    2

    Default Re: PostgreSQL Problems

    Here's an extract from the PostgreSQL documentation:

    "Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

    Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote A particular name or never quote it.) "

    So it seems Alpha5 does not handle quoted names when generating a SQL statement for a PostgreSQL connection. I'll go through the tutorials and see if I can generate pass-through SQL in native PostgreSQL syntax to overcome the problem, although this will be more cumbersome than it may be worth.

  3. #3
    "Certified" Alphaholic
    Real Name
    eric
    Join Date
    Mar 2009
    Location
    Amsterdam
    Posts
    1,284

    Default Re: PostgreSQL Problems

    Quote Originally Posted by tomk@hepburn View Post
    Here's an extract from the PostgreSQL documentation:

    "Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

    Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote A particular name or never quote it.) "

    So it seems Alpha5 does not handle quoted names when generating a SQL statement for a PostgreSQL connection. I'll go through the tutorials and see if I can generate pass-through SQL in native PostgreSQL syntax to overcome the problem, although this will be more cumbersome than it may be worth.
    I don't see this as a bug at present but i understand your situation, send a request to Selwyn@alphasoftware.com perhaps he can help to solve and fix your issue.

Similar Threads

  1. PostgreSQL DAO
    By badmood in forum Archived Wishlist
    Replies: 5
    Last Post: 01-30-2011, 07:12 AM
  2. Web apps and PostgreSQL
    By badmood in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 01-20-2011, 07:48 PM
  3. PostgresQL 8.3 Connection
    By Mike Thomson in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 05-20-2009, 05:24 AM
  4. More PostgreSQL Madness
    By Occam49 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 12
    Last Post: 04-02-2009, 08:43 AM
  5. SQL connecting PostgreSQL
    By coleman in forum Alpha Five Version 5
    Replies: 0
    Last Post: 08-05-2003, 09:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •