Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    22

    Unanswered: DBAccess cannot identify table when I prefix the owner name in select statement

    Hi All!

    I have Informix Universal Server 9.14.TC8 installed.

    Using DBAccess, I log into my database using the informix user account details. The owner of all tables in my database is called user1.

    However, when I issue the following SQL statement to retrieve employee information:

    select * from user1.employees

    DBAccess reports the following error message:

    206: The specified table (user1.stock) is not in the database.
    111: ISAM error: no record found.

    If I omit the owner prefix everything works fine i.e.

    select * from employees

    I have verified that the user1 user exists by interrogating the sysusers table.

    Any idea why this is not working for me?

    thanks in advance
    - Garry

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Are you sure about the owner.

    SELECT owner FROM systables WHERE tabname = "user1"

    AND have you tried

    SELECT * FROM "user1".tablename

    Maybe this will help,

    Rob Prop

  3. #3
    Join Date
    Apr 2002
    Posts
    22
    Hi Rob!

    As per your suggestion, executing the following statement works:

    select * from "user1".stock


    Perhaps you can tell me the significance of this. Although I used the name user1 in the example, the real name is Administ (note the capitalised A).

    The problem is that I have a Java program that retrieves all the owner and table names automatically from the sysusers & systables tables and builds up the qualified name at runtime.

    Is my solution to always enclose the owner name in double quotes? The reason I ask this is that my program works unchanged when connecting to other Informix servers, so it begs the question, what configuration differences exist between the different Informix servers.

    Any further help would be appreciated.

    thanks
    - Garry

  4. #4
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Garry,

    I'm not sure but I think the proble is the capital character. I noticed earlier that Informix itself is always default specifying quotes around the user name. As far as I know this always works. So you can just implemented it in scripts etc.


    Hope this helps,

    Rob Prop

  5. #5
    Join Date
    Apr 2002
    Posts
    22
    Cheers Rob!

Posting Permissions

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