Topic
  • 10 replies
  • Latest Post - ‏2006-11-23T11:15:58Z by SystemAdmin
SystemAdmin
SystemAdmin
1731 Posts

Pinned topic SqlException pls help

‏2006-11-21T07:11:37Z |
hi,
i just migrated from mysql to db2

but when i excecute java code iam getting this exception
com.ibmdb2.jcc.b.SqlException:Invalid argument :unknown column name user
thanks
arvin
Updated on 2006-11-23T11:15:58Z at 2006-11-23T11:15:58Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-21T09:11:33Z  
    Sounds like there isn't a column called user in the table that you're selecting, inserting or updating into.

    > hi,
    > i just migrated from mysql to db2
    >
    > but when i excecute java code iam getting this
    > exception
    > com.ibmdb2.jcc.b.SqlException:Invalid argument
    > :unknown column name user
    > thanks
    > arvin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-21T09:42:57Z  
    There is a field 'user' in the table .
    but when i try to get the record using resultset its coming as 'Administrator 'instead of data in the field
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-21T10:19:27Z  
    There is a field 'user' in the table .
    but when i try to get the record using resultset its coming as 'Administrator 'instead of data in the field
    It might help if you posted your java code and the table schema from the table you're working with.
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-22T06:45:45Z  
    It might help if you posted your java code and the table schema from the table you're working with.
    iam using a field name 'user' in a table,
    when i retrieve records in that field am getting 'Administrator'

    name=user
    datatype=varchar
    length=20
    nullable=no

    actually 'user' is a keyword.
    can user used as a fieldname
    pls reply
    thanks arvin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-22T09:09:35Z  
    iam using a field name 'user' in a table,
    when i retrieve records in that field am getting 'Administrator'

    name=user
    datatype=varchar
    length=20
    nullable=no

    actually 'user' is a keyword.
    can user used as a fieldname
    pls reply
    thanks arvin
    You should be able to use double quotes around reserved words in your select statement, generally it is not recommended that reserved words are used as field names.
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-22T12:47:40Z  
    You should be able to use double quotes around reserved words in your select statement, generally it is not recommended that reserved words are used as field names.
    my query is like this
    select distinct(user) from tablename .
    how should i modify the query to get output
    pls help
    thanks
    arvin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-22T12:50:51Z  
    my query is like this
    select distinct(user) from tablename .
    how should i modify the query to get output
    pls help
    thanks
    arvin
    select distinct("user") from tablename
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-23T03:59:00Z  
    select distinct("user") from tablename
    when i try
    select distict("user" )from usertable;
    iam getting the following error.
    A JDBC connection to the target has succeeded.

    Commands Entered
    select distict("user" )from usertable;

    select distict("user" )from usertable
    SQL0206N "user" is not valid in the context where it is used. SQLSTATE=42703

    SQL0206N "user " is not valid in the context where it is used.

    Explanation:

    This error can occur in the following cases:

    o For an INSERT or UPDATE statement, the specified column is
    not a column of the table, or view that was specified as the
    object of the insert or update.

    o For a SELECT or DELETE statement, the specified column is not
    a column of any of the tables or views identified in a FROM
    clause in the statement.

    o For an ORDER BY clause, the specified column is a correlated
    column reference in a subselect, which is not allowed.

    o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION
    statement:

    - The reference "<name>" does not resolve to the name of a
    column, local variable or transition variable.

    - The condition name "<name>" specified in the SIGNAL statement
    has not been declared.

    o For a CREATE TRIGGER statement:

    - A reference is made to a column of the subject table without
    using an OLD or NEW correlation name.

    - The left hand side of an assignment in the SET
    transition-variable statement in the triggered action
    specifies an old transition variable where only a new
    transition variable is supported.

    o For a CREATE FUNCTION statement with a PREDICATES clause:

    - The RETURN statement of the SQL function references a
    variable that is not a parameter or other variable that
    is in the scope of the RETURN statement.

    - The FILTER USING clause references a variable that is not a
    parameter name or an expression name in the WHEN
    clause.

    - The search target in an index exploitation rule does not
    match some parameter name of the function that is being
    created.

    - A search argument in an index exploitation rule does not
    match either an expression name in the EXPRESSION AS
    clause or a parameter name of the function being
    created.

    o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH
    clause or the FILTER USING clause references a variable that
    is not a parameter name that can be used in the clause.

    The statement cannot be processed.

    User Response:

    Verify that the names are specified correctly in the SQL
    statement. For a SELECT statement, ensure that all the required
    tables are named in the FROM clause. For a subselect in an ORDER
    BY clause, ensure that there are no correlated column references.
    If a correlation name is used for a table, verify that subsequent
    references use the correlation name and not the table name.

    For a CREATE TRIGGER statement, ensure that only new transition
    variables are specified on the left hand side of assignments in
    the SET transition-variable statement and that any reference to
    columns of the subject table have a correlation name specified.

    sqlcode : -206

    sqlstate : 42703
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-23T09:04:28Z  
    when i try
    select distict("user" )from usertable;
    iam getting the following error.
    A JDBC connection to the target has succeeded.

    Commands Entered
    select distict("user" )from usertable;

    select distict("user" )from usertable
    SQL0206N "user" is not valid in the context where it is used. SQLSTATE=42703

    SQL0206N "user " is not valid in the context where it is used.

    Explanation:

    This error can occur in the following cases:

    o For an INSERT or UPDATE statement, the specified column is
    not a column of the table, or view that was specified as the
    object of the insert or update.

    o For a SELECT or DELETE statement, the specified column is not
    a column of any of the tables or views identified in a FROM
    clause in the statement.

    o For an ORDER BY clause, the specified column is a correlated
    column reference in a subselect, which is not allowed.

    o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION
    statement:

    - The reference "<name>" does not resolve to the name of a
    column, local variable or transition variable.

    - The condition name "<name>" specified in the SIGNAL statement
    has not been declared.

    o For a CREATE TRIGGER statement:

    - A reference is made to a column of the subject table without
    using an OLD or NEW correlation name.

    - The left hand side of an assignment in the SET
    transition-variable statement in the triggered action
    specifies an old transition variable where only a new
    transition variable is supported.

    o For a CREATE FUNCTION statement with a PREDICATES clause:

    - The RETURN statement of the SQL function references a
    variable that is not a parameter or other variable that
    is in the scope of the RETURN statement.

    - The FILTER USING clause references a variable that is not a
    parameter name or an expression name in the WHEN
    clause.

    - The search target in an index exploitation rule does not
    match some parameter name of the function that is being
    created.

    - A search argument in an index exploitation rule does not
    match either an expression name in the EXPRESSION AS
    clause or a parameter name of the function being
    created.

    o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH
    clause or the FILTER USING clause references a variable that
    is not a parameter name that can be used in the clause.

    The statement cannot be processed.

    User Response:

    Verify that the names are specified correctly in the SQL
    statement. For a SELECT statement, ensure that all the required
    tables are named in the FROM clause. For a subselect in an ORDER
    BY clause, ensure that there are no correlated column references.
    If a correlation name is used for a table, verify that subsequent
    references use the correlation name and not the table name.

    For a CREATE TRIGGER statement, ensure that only new transition
    variables are specified on the left hand side of assignments in
    the SET transition-variable statement and that any reference to
    columns of the subject table have a correlation name specified.

    sqlcode : -206

    sqlstate : 42703
    Your best bet would be to change the field name to something other than user.
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: SqlException pls help

    ‏2006-11-23T11:15:58Z  
    Your best bet would be to change the field name to something other than user.
    its solved
    the query should be
    select distinct("USER") from tablename

    thanks
    arvin