DB2 - Problembeschreibung
Problem IC69963 | Status: Geschlossen |
DB2COMMANDBUILDER STATEMENTS RESULT IN TABLE NOT FOUND ERROR WHEN EXECUTING AGAINST IDS ANSI MODE DATABASES. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
The IDS ANSI-mode database has some specific rules when it comes to resolving the owner name of tables: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp? topic=/com.ibm.sqls.doc/ids_sqs_1704.htm (ommitted owner name) Reads or stores owner exactly as the login name is stored in the system, but returns an error if the user is not the owner. The result is that if SQL statements are to have the owner name included with the table name, care must be taken to ensure that the use is consistent... the code below demonstrates an inconsistency where the DB2DataAdapter is used. In the example code below, the DB2CommandBuilder is going to generate a command that includes the owner name without the use of quotes. When that statement is used, an error is thrown indicating that the table NEWTON.ownerprob is not found in the database. This occurs even though the user did not use a owner in the original SQL statement provided to the command builder. This is not a problem when connected to either IDS buffered or unbuffered logged databases. static void IDS_owner_problem() { string connStr = "database=mydb;server=mysystem.ibm.com:9089;uid=*****;pwd=****** ;"; string tstSQL = "select col1 from ownerprob"; try { using (DB2Connection tstConn = new DB2Connection(connStr)) { tstConn.Open(); DB2Command tstCmd = tstConn.CreateCommand(); try { tstCmd.CommandText = "drop table ownerprob"; tstCmd.ExecuteNonQuery(); } catch { /* don't care */ } tstCmd.CommandText = "create table ownerprob (col1 integer primary key constraint col1)"; tstCmd.ExecuteNonQuery(); tstCmd.CommandText = "insert into ownerprob (col1) values (1)"; tstCmd.ExecuteNonQuery(); Console.WriteLine("Reading using DB2DataReader"); tstCmd.CommandText = tstSQL; using (DB2DataReader tstRdr = tstCmd.ExecuteReader()) { while (tstRdr.Read()) { Console.WriteLine("\tcol1: {0}", tstRdr.GetInt32(0)); } } Console.WriteLine("add row using DataAdapter"); DB2DataAdapter tstDA = new DB2DataAdapter(tstSQL, tstConn); DataTable dt = new DataTable("ownerprob"); tstDA.Fill(dt); Console.WriteLine("\tgot rows:"); foreach (DataRow dr in dt.Rows) { Console.WriteLine("\t\tcol1: {0}", dr[0].ToString()); } dt.Rows.Add(new object[] { 2 }); DB2CommandBuilder tstBldr = new DB2CommandBuilder(tstDA); tstDA.InsertCommand = tstBldr.GetInsertCommand(); tstDA.UpdateCommand = tstBldr.GetUpdateCommand(); int rowsAffected = tstDA.Update(dt); // throws exception HERE!! Console.WriteLine("\trows affected: {0}", rowsAffected); foreach (DataRow dr in dt.Rows) { Console.WriteLine("\t\tcol1: {0}", dr[0].ToString()); } } } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { using (DB2Connection tstConn = new DB2Connection(connStr)) { tstConn.Open(); DB2Command tstCmd = tstConn.CreateCommand(); tstCmd.CommandText = "drop table ownerprob"; try { tstCmd.ExecuteNonQuery(); } catch { /* don't care */ } } } } | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * .NET provider users on the Windows platform * **************************************************************** * PROBLEM DESCRIPTION: * * The IDS ANSI-mode database has some specific rules when it * * comes * * to resolving the owner name of tables: * * * * * * * * http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.js * topic=/com.ibm.sqls.doc/ids_sqs_1704.htm * * * * (ommitted owner name) Reads or stores owner exactly as the * * * * login name is stored in the system, but returns an error if * * the * * user is not the owner. * * * * * * * * The result is that if SQL statements are to have the owner * * name * * included with the table name, care must be taken to ensure * * that * * the use is consistent... the code below demonstrates an * * * * inconsistency where the DB2DataAdapter is used. In the * * example * * code below, the DB2CommandBuilder is going to generate a * * command * * that includes the owner name without the use of quotes. * * When * * that statement is used, an error is thrown indicating that * * the * * table NEWTON.ownerprob is not found in the database. This * * * * occurs even though the user did not use a owner in the * * original * * SQL statement provided to the command builder. * * * * * * * * This is not a problem when connected to either IDS buffered * * or * * unbuffered logged databases. * * * * * * * * static void IDS_owner_problem() * * * * { * * * * string connStr = * * * * "database=mydb;server=mysystem.ibm.com:9089;uid=*****;pwd=**** * ;"; * * * * string tstSQL = "select col1 from ownerprob"; * * * * * * * * try * * * * { * * * * using (DB2Connection tstConn = new * * * * DB2Connection(connStr)) * * * * { * * * * tstConn.Open(); * * * * DB2Command tstCmd = tstConn.CreateCommand(); * * * * try * * * * { * * * * tstCmd.CommandText = "drop table * * ownerprob"; * * tstCmd.ExecuteNonQuery(); * * * * } * * * * catch { /* don't care */ } * * * * tstCmd.CommandText = "create table ownerprob * * * * (col1 integer primary key constraint col1)"; * * * * tstCmd.ExecuteNonQuery(); * * * * tstCmd.CommandText = "insert into ownerprob * * * * (col1) values (1)"; * * * * tstCmd.ExecuteNonQuery(); * * * * * * * * Console.WriteLine("Reading using * * DB2DataReader"); * * tstCmd.CommandText = tstSQL; * * * * using (DB2DataReader tstRdr = * * * * tstCmd.ExecuteReader()) * * * * { * * * * while (tstRdr.Read()) * * * * { * * * * Console.WriteLine("\tcol1: {0}", * * * * tstRdr.GetInt32(0)); * * * * } * * * * } * * * * * * * * Console.WriteLine("add row using * * DataAdapter"); * * DB2DataAdapter tstDA = new * * DB2DataAdapter(tstSQL, * * tstConn); * * * * DataTable dt = new DataTable("ownerprob"); * * * * tstDA.Fill(dt); * * * * Console.WriteLine("\tgot rows:"); * * * * foreach (DataRow dr in dt.Rows) * * * * { * * * * Console.WriteLine("\t\tcol1: {0}", * * * * dr[0].ToString()); * * * * } * * * * dt.Rows.Add(new object[] { 2 }); * * * * DB2CommandBuilder tstBldr = new * * * * DB2CommandBuilder(tstDA); * * * * tstDA.InsertCommand = * * tstBldr.GetInsertCommand(); * * tstDA.UpdateCommand = * * tstBldr.GetUpdateCommand(); * * int rowsAffected = tstDA.Update(dt); // * * throws * * exception HERE!! * * * * Console.WriteLine("\trows affected: {0}", * * * * rowsAffected); * * * * foreach (DataRow dr in dt.Rows) * * * * { * * * * Console.WriteLine("\t\tcol1: {0}", * * * * dr[0].ToString()); * * * * } * * * * } * * * * } * * * * catch (Exception e) * * * * { * * * * Console.WriteLine(e.ToString()); * * * * } * * * * finally * * * * { * * * * using (DB2Connection tstConn = new * * * * DB2Connection(connStr)) * * * * { * * * * tstConn.Open(); * * * * DB2Command tstCmd = tstConn.CreateCommand(); * * * * tstCmd.CommandText = "drop table ownerprob"; * * * * try * * * * { * * * * tstCmd.ExecuteNonQuery(); * * * * } * * * * catch { /* don't care */ } * * * * } * * * * } * * * * } * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 LUW Version 9.7 Fix Pack 3 * **************************************************************** | |
Local-Fix: | |
One solution to resolve the problem is to specify DB2CommandBuilder.QuotePrefix and DB2CommandBuilder.QuoteSuffix, setting both to a string containing a single quote ( QuotePrefix = "\""; ). Such an action will result in all database objects (table owner, table names, column names, etc.) being enclosed in quotes and preserving the case of the table owner. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in Version 9.7 Fix Pack 3 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC69970 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 15.07.2010 16.09.2010 16.09.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP3 | |
Problem behoben lt. FixList in der Version | |
9.7.0.3 | |
9.7.0.3 |