Java DB2 SQL 错误：SQLCODE=-204，SQLSTATE=42704
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20919298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704
提问by Yasmin
I created local database in DB2 called "TestDB" then I created table called "TestTable".
I found that the table is put under schema name is "yasmin".
I am trying to connect to the DB2database using JDBCbut I got this exception
我在 DB2 中创建了名为“ TestDB”的本地数据库，然后创建了名为“ TestTable”的表。
我发现该表放在架构名称下是“ yasmin”。
我正在尝试使用连接到DB2数据库，JDBC但出现此异常
R SQLException information
[1/4/14 11:32:59:289 EST] 0000004d SystemErr R Error msg: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TESTTABLE, DRIVER=3.61.86
[1/4/14 11:32:59:290 EST] 0000004d SystemErr R SQLSTATE: 42704
[1/4/14 11:32:59:290 EST] 0000004d SystemErr R Error code: -204
[1/4/14 11:32:59:290 EST] 0000004d SystemErr R com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TESTTABLE, DRIVER=3.61.86
I tried many solutions on the internet Like set schema but unfortunately doesn't work.
我在互联网上尝试了许多解决方案，例如 set schema，但不幸的是不起作用。
This is the JDBC code I used
这是我使用的JDBC代码
String urlPrefix = "jdbc:db2:";
String url;
String user;
String password;
String empNo;
Connection con;
Statement stmt;
ResultSet rs;
url = urlPrefix + "//127.0.0.1:50000/TestDB";
user = "db2admin";
password = "db2admin";
try
{
// Load the driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
System.out.println("**** Loaded the JDBC driver");
// Create the connection using the IBM Data Server Driver for JDBC and SQLJ
con = DriverManager.getConnection (url, user, password);
// Commit changes manually
con.setAutoCommit(false);
System.out.println("**** Created a JDBC connection to the data source");
stmt = con.createStatement(); con.createStatement();
System.out.println("**** Created JDBC Statement object");
// Execute a query and generate a ResultSet instance
rs = stmt.executeQuery("select *from TestTable");
System.out.println("**** Created JDBC ResultSet object");
}
catch (ClassNotFoundException e)
{
System.err.println("Could not load JDBC driver");
System.out.println("Exception: " + e);
e.printStackTrace();
}
catch(SQLException ex)
{
System.err.println("SQLException information");
while(ex!=null) {
System.err.println ("Error msg: " + ex.getMessage());
System.err.println ("SQLSTATE: " + ex.getSQLState());
System.err.println ("Error code: " + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException(); // For drivers that support chained exceptions
}
}
回答by Mark Rotteveel
The error SQLERROR -204, SQLSTATE 42704is a missing/unknown objectname and it is most likely caused by a missing space in:
错误SQLERROR -204, SQLSTATE 42704是一个丢失/未知的对象名，它很可能是由以下位置的空格丢失引起的：
rs = stmt.executeQuery("select *from TestTable");
Which should be:
应该是：
rs = stmt.executeQuery("select * from TestTable");
回答by bhamby
As @Mark Rotteveel said, the -204error is from a missing object, but it's missing for a reason other than what he said.
正如@Mark Rotteveel 所说，-204错误来自丢失的对象，但由于他所说的以外的原因而丢失。
It's not found because you did not prefix it with the schema name. You said above that it's in schema yasmin, but you're connecting with db2admin, so it's trying to look for db2admin.TestTable.
找不到它，因为您没有在它前面加上架构名称。您在上面说过它在 schema 中yasmin，但是您正在与 连接db2admin，因此它正在尝试查找db2admin.TestTable.
SELECT * FROM yasmin.TestTable
should be what you're looking for.
应该是你要找的。
By default, the search path for schemas is the name of the currently connecting user. You can see what it is using
默认情况下，模式的搜索路径是当前连接用户的名称。你可以看到它在使用什么
SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1
If you want to change it, you can use the SET SCHEMAcommand to change the search path, but usually it's easier just to include the schema name in your query.
如果您想更改它，您可以使用SET SCHEMA命令来更改搜索路径，但通常在您的查询中包含架构名称会更容易。
回答by Yasmin
The problem was The table I created in db2 has schema name "yasmin"
I used username and password "db2admin/db2admin"In JDBC connection
so it search for the table in this schema "db2admin"
so I need to set the schema to be "yasmin"
so I added the following lines of code after creating
the connection
问题是我在 db2 中创建的表有模式名称"yasmin"
我"db2admin/db2admin"在 JDBC 连接中使用了用户名和密码
所以它在这个模式“db2admin”中搜索表
所以我需要将模式设置为"yasmin"
所以我添加了以下代码行创建连接
String schemaName="yasmin";
if (schemaName != null && schemaName.length() != 0) {
try {
statement = connection.createStatement();
statement.executeUpdate("set current sqlid = " + schemaName);
System.out.println("The schema is set successfully.");
} catch (SQLException exception) {
exception.printStackTrace();
}
and grant db2admin all privilages on this table in db2
并授予 db2admin 在 db2 中该表的所有权限
1-Right click on the table choose privilages
2-Click on "Add user" button
3-then write the username "db2admin" and click "apply" button
4-Then select the user you just add and click "gtant all" button
1-右键单击表格选择权限
2-单击“添加用户”按钮
3-然后输入用户名“db2admin”并单击“应用”按钮
4-然后选择您刚刚添加的用户并单击“gtant all”按钮
Now he can see the table.
现在他可以看到桌子了。
Many Thanks @Mark Rotteveel and @bhamby for your help.
非常感谢@Mark Rotteveel 和@bhamby 的帮助。
回答by punkck
If you are connecting in Spring using Hibernate use following in the property line
如果您在 Spring 中使用 Hibernate 进行连接，请在属性行中使用以下内容
<property name="url" value="jdbc:db2://<ip>:<port>/<database>:currentSchema=<currentSchema>;" />
回答by Thang Vinh Tran
Other way is just create an ALIAS, likes this:
另一种方法是创建一个别名，就像这样：
CREATE ALIAS USER1.YOURTABLE FOR TABLE USER2.YOURTABLE;
