Results 1 to 13 of 13

Thread: .Net and DB2

  1. #1
    Join Date
    Dec 2012
    Posts
    7

    Question Unanswered: .Net and DB2

    Hi everybody,

    I'm new in using DB2, I'm trying to use it in .Net but without success.
    First, I was trying to use it with the Entity Framework, but in several tryies i kept receiving the message "ERROR [42704] [IBM][DB2/NT64] SQL0204N "NULLID.<table name>" is an undefined name.", then I thought it was an compatibility issue with the EF, so I tryied to make it in the traditional way, so i created a DB2Connection, DB2Command and tryied to execute a query but for my surprise I got the same message, so i think this may be a DB problem, and as I'm not a DBA neither a DB2 Developer I don't know what to do.

    Does anyone knows how to solve my problem?

    Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not familiar with .Net, but my guess would be that you either need to explicitly provide the table schemas when issuing SQL statement or define the correct default schema.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2012
    Posts
    7
    In my query I'm explicitly providing the schema, as can be seen:

    "SELECT UsuarioId, Nome, Login FROM NULLID.Usuario";

    When I created my tables in my database I created them in the NULLID table schema, that was the default schema provided by the DB2 Data Studio, should I create another schema and recreate all the tables?

    Is there a way of changing the tables schema without recreating them?

    Thank you.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by VOliverio View Post
    When I created my tables in my database I created them in the NULLID table schema.

    This is obviously not the case, as indicated by the error message.

    Quote Originally Posted by VOliverio View Post
    should I create another schema and recreate all the tables?
    Probably

    Quote Originally Posted by VOliverio View Post
    Is there a way of changing the tables schema without recreating them?
    No
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2012
    Posts
    7
    Quote Originally Posted by n_i View Post

    Quote Originally Posted by VOliverio View Post
    When I created my tables in my database I created them in the NULLID table schema.
    This is obviously not the case, as indicated by the error message.
    As you can see in the attachment, yes it is the case.

    Quote Originally Posted by n_i View Post

    Quote Originally Posted by VOliverio View Post
    should I create another schema and recreate all the tables?.
    Probably
    I'll try to do that, thank you.
    Attached Thumbnails Attached Thumbnails database.png  

  6. #6
    Join Date
    Dec 2012
    Posts
    5
    the same query run ok in an editor?

  7. #7
    Join Date
    Dec 2012
    Posts
    7
    Quote Originally Posted by gangster View Post
    the same query run ok in an editor?
    When I right click the table in the "Server Explorer" of the VS2010 and click "Show Data", it shows all the data in the table, but I cannot see the query it is doing to retrieve the data.

    When I build the query in the VS2010 query editor and try to run I get the same error.

    I tryied the suggestion above (create another schema) but I didn't work also, same error but with the other schema. If I omit the schema in the query it shows the error using the user as schema, e.g.:

    "SELECT * FROM Usuario;"

    The error I get is:

    ... "DB2ADMIN.Usuario" is an undefined name.

    Any suggestion?

    Thank you.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Apparently, you specified the mixed case for the table names when creating them. By default DB2 (and most other databases I know) will convert object names to upper case unless they are quoted. For example
    Code:
    SELECT * FROM Usuario
    will be looking for the table called <current schema>."USUARIO", while your table seems to be called "Usuario". To query it you will have to quote the name:
    Code:
    SELECT * FROM "Usuario"
    Generally it is not recommended to use mixed case for database objects.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Dec 2012
    Posts
    7
    Quote Originally Posted by n_i View Post
    Apparently, you specified the mixed case for the table names when creating them. By default DB2 (and most other databases I know) will convert object names to upper case unless they are quoted. For example
    Code:
    SELECT * FROM Usuario
    will be looking for the table called <current schema>."USUARIO", while your table seems to be called "Usuario". To query it you will have to quote the name:
    Code:
    SELECT * FROM "Usuario"
    Generally it is not recommended to use mixed case for database objects.
    Thank you very much n_i, it worked, but the DB2 does the same to columns, I had to quote them also, but i will recreate them in the DB2 pattern.

    But what you said about all the databases you know work this way I disagree, I work daily with Oracle, MySql and MS Sql Server and I never had to do such thing.

    Thank you.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by VOliverio View Post
    But what you said about all the databases you know work this way I disagree, I work daily with Oracle, MySql and MS Sql Server and I never had to do such thing.
    Ok, let's agree to disagree.

    "Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive." Schema Object Names and Qualifiers

    "Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation." Database Identifiers

    MySQL cannot be a good example, because the table identifier behaviour in it depends on the case-sensitivity of the operating system, which in my opinion does not make good sense.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Dec 2012
    Posts
    7
    Quote Originally Posted by n_i View Post
    Ok, let's agree to disagree.

    "Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive." Schema Object Names and Qualifiers

    "Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation." Database Identifiers

    MySQL cannot be a good example, because the table identifier behaviour in it depends on the case-sensitivity of the operating system, which in my opinion does not make good sense.
    Ok, you won, but unlike DB2 the other databases are intelligent enough to treat it the way they should, so Developers like me don't bother with it .

    Thank you!

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by VOliverio View Post
    Ok, you won
    I don't think one can win or lose an argument based on a fact; it's not a hockey game.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by VOliverio View Post
    so Developers like me don't bother with it .
    Exacltly, that's why there are DBA's like us. You can hire us, you know
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Tags for this Thread

Posting Permissions

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