#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep Power
    0

    newbie: can't select *


    I've migrated a db over from Access; i can log in with isql and "show tables;" lists all tables including "foo"; when I look at the db from an admin tool I can see data in the tables, but when I try "select * from foo;" it tells me:

    Statement failed, SQLCODE = -104

    Dynamic SQL Error
    -SQL error code = -104
    -Token unknown - line 1 char xx
    -foo

    Where can I start to troubleshoot this?

    Thanks for any help.

    -Bill
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Bucharest
    Posts
    72
    Rep Power
    12

    Exclamation


    You must have SELECT privilege granted:
    GRANT SELECT ON foo TO <user>,
    or you have to login as SYSDBA user.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep Power
    0

    .An answer and one further question:


    This db was created by a script generated by InterBase DataPump; because the table names were specified in quotes during the creation, it turns out that they must be referred to with quotes:

    SELECT * from "foo";

    BTW:

    How would I do this on IB/Firebird:
    GRANT SELECT ON * TO <user>; ? (grant privileges to all tables in a db)

    atb
    -s
    Last edited by sporb; September 24th, 2003 at 12:10 PM.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Bucharest
    Posts
    72
    Rep Power
    12

    Lightbulb Re: .An answer and one further question:


    How would I do this on IB/Firebird:
    GRANT SELECT ON * TO <user>; ? (grant privileges to all tables in a db)
    1). you can create a stored procedure to grant all privileges (SIUDR) to an user on all tables/views in database:
    CREATE PROCEDURE SP_GRANT_ALL (
    M_USER CHAR(31))
    AS
    DECLARE VARIABLE M_TABLE CHAR(31);
    begin
    for select RDB$RELATION_NAME as myTable from rdb$relations WHERE SUBSTRING(RDB$RELATION_NAME from 1 for 4) <> 'RDB$' INTO :m_table
    do begin execute statement 'grant all on ' || :m_table || ' to ' || :m_user; end
    suspend;
    end


    (to execute SP_GRANT_ALL(<cUser>) you must have EXECUTE ON PROCEDURE privilege)

    2).or GRANT ALL on <table> to PUBLIC;

    * ALL = select + insert + update + delete + reference

IMN logo majestic logo threadwatch logo seochat tools logo