Topic
  • 6 replies
  • Latest Post - ‏2010-02-22T16:36:20Z by cscs010
cscs010
cscs010
8 Posts

Pinned topic sqlcode=-206 on xmlquery

‏2010-02-18T15:43:21Z |
I am running DB2 EXPRESS-C v9.7 on Windows and am
trying to invoke a XMLQUERY statement in a java program
and keep getting this SQLCODE=-206 error on the executeQuery().

DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CUSTOMER2.INFO, DRIVER=3.5
7.82
-206
com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CUS
TOMER2.INFO, DRIVER=3.57.82
at com.ibm.db2.jcc.am.bd.a(bd.java:676)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.km.c(km.java:2506)
at com.ibm.db2.jcc.am.km.d(km.java:2483)
at com.ibm.db2.jcc.am.km.a(km.java:1963)
at com.ibm.db2.jcc.t4.db.g(db.java:139)
at com.ibm.db2.jcc.t4.db.a(db.java:39)
at com.ibm.db2.jcc.t4.t.a(t.java:32)
at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
at com.ibm.db2.jcc.am.km.eb(km.java:1934)
at com.ibm.db2.jcc.am.lm.ic(lm.java:2798)
at com.ibm.db2.jcc.am.lm.b(lm.java:3517)
at com.ibm.db2.jcc.am.lm.Vb(lm.java:654)
at com.ibm.db2.jcc.am.lm.executeQuery(lm.java:628)
at Order2Process.isCustomer(Order2Process.java:71)
at Order2Process.main(Order2Process.java:23)
Now, i can run the same query from the DB2 command line in Windows.

C:\db2e>db2 select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS
"D" ) from CUSTOMER2

1



1 record(s) selected.
this is the relevant code - error is on executQuery():

public String isCustomer(int id)
{
String query=
"select xmlquery('$d/customerinfo/name' passing customer2.info as \"d\") from ebarkow.customer2";

try {
PreparedStatement stmt=con.prepareStatement(query);
ResultSet rs=stmt.executeQuery();
cid=id;
if(!rs.next())
return null;
else
return rs.getString(1);
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println(e.getErrorCode());
e.printStackTrace();
return null;
}
} // isCustomer
Updated on 2010-02-22T16:36:20Z at 2010-02-22T16:36:20Z by cscs010
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: sqlcode=-206 on xmlquery

    ‏2010-02-18T15:50:08Z  
    You may wish to post this on the DB2 pureXML forum: http://www.ibm.com/developerworks/forums/forum.jspa?forumID=1423
  • nivanov1
    nivanov1
    231 Posts

    Re: sqlcode=-206 on xmlquery

    ‏2010-02-18T16:27:27Z  
    >> Now, i can run the same query from the DB2 command line in Windows.
    >>
    >> C:\db2e>db2 select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS
    >> "D" ) from CUSTOMER2

    It's not the same query - unlike the one embedded in your java code, you don't specify the schema name here. If you connect as something different than "ebarkow" you obviously access a different table.
  • cscs010
    cscs010
    8 Posts

    Re: sqlcode=-206 on xmlquery

    ‏2010-02-19T15:23:45Z  
    • nivanov1
    • ‏2010-02-18T16:27:27Z
    >> Now, i can run the same query from the DB2 command line in Windows.
    >>
    >> C:\db2e>db2 select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS
    >> "D" ) from CUSTOMER2

    It's not the same query - unlike the one embedded in your java code, you don't specify the schema name here. If you connect as something different than "ebarkow" you obviously access a different table.
    it is the same query on the command line - i was signed in with my id in the previous listing so the schema name was not necessary.

    C:\db2e>db2 select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS "D"
    ) from ebarkow.CUSTOMER2

    1

    1 record(s) selected.
  • cscs010
    cscs010
    8 Posts

    Re: sqlcode=-206 on xmlquery

    ‏2010-02-19T15:37:29Z  
    You may wish to post this on the DB2 pureXML forum: http://www.ibm.com/developerworks/forums/forum.jspa?forumID=1423
    Thank you for letting me know about the pure xml forum - i took your advice and posted there.
  • nivanov1
    nivanov1
    231 Posts

    Re: sqlcode=-206 on xmlquery

    ‏2010-02-19T18:36:59Z  
    • cscs010
    • ‏2010-02-19T15:23:45Z
    it is the same query on the command line - i was signed in with my id in the previous listing so the schema name was not necessary.

    C:\db2e>db2 select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS "D"
    ) from ebarkow.CUSTOMER2

    1

    1 record(s) selected.
    $ db2 "? sql206"

    SQL0206N "<name>" is not valid in the context where it is
    used.

    The error obviously refers to CUSTOMER2.INFO, so there is something wrong with the reference to that column. You do know that XML is case-sensitive, don't you? From that point of view

    select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS "D" ) from ebarkow.CUSTOMER2

    and

    select xmlquery('$D/customerinfo/name' PASSING CUSTOMER2.INFO AS "D" ) from ebarkow.CUSTOMER2

    are still different queries...
  • cscs010
    cscs010
    8 Posts

    Re: sqlcode=-206 on xmlquery

    ‏2010-02-22T16:36:20Z  
    • nivanov1
    • ‏2010-02-19T18:36:59Z
    $ db2 "? sql206"

    SQL0206N "<name>" is not valid in the context where it is
    used.

    The error obviously refers to CUSTOMER2.INFO, so there is something wrong with the reference to that column. You do know that XML is case-sensitive, don't you? From that point of view

    select xmlquery('$D/CUSTOMERINFO/name' PASSING CUSTOMER2.INFO AS "D" ) from ebarkow.CUSTOMER2

    and

    select xmlquery('$D/customerinfo/name' PASSING CUSTOMER2.INFO AS "D" ) from ebarkow.CUSTOMER2

    are still different queries...
    I changed the query so that the case data looks like it does in the database (according to control center):
    database name is EBARKOW.CUSTOMER2 and xml field is INFO

    <customerinfo xmlns="http://posample.org" Cid="1000"><name>Kathy Smith</name><addr country="Canada"><street>5 Rosewood</street><city>Toronto</city> 282 DB2 Express-C: The Developer Handbook for XML, PHP, C/C++, Java, and .NET <prov-state>Ontario</prov-state><pcode-zip>M6W 1E6</pcode-zip></addr><phone type="work">416-555-1358</phone></customerinfo>

    query is:
    "Select Xmlquery('$d/customerinfo/name' passing CUSTOMER2.INFO as \"d\") from EBARKOW.CUSTOMER2";

    still get the error:

    DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CUSTOMER2.INFO, DRIVER=3.5
    7.82
    -206
    com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=CUS
    TOMER2.INFO, DRIVER=3.57.82
    at com.ibm.db2.jcc.am.bd.a(bd.java:676)
    at com.ibm.db2.jcc.am.bd.a(bd.java:60)
    at com.ibm.db2.jcc.am.bd.a(bd.java:127)
    at com.ibm.db2.jcc.am.km.c(km.java:2506)
    at com.ibm.db2.jcc.am.km.d(km.java:2483)
    at com.ibm.db2.jcc.am.km.a(km.java:1963)
    at com.ibm.db2.jcc.t4.db.g(db.java:139)
    at com.ibm.db2.jcc.t4.db.a(db.java:39)
    at com.ibm.db2.jcc.t4.t.a(t.java:32)
    at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
    at com.ibm.db2.jcc.am.km.eb(km.java:1934)
    at com.ibm.db2.jcc.am.lm.ic(lm.java:2798)
    at com.ibm.db2.jcc.am.lm.b(lm.java:3517)
    at com.ibm.db2.jcc.am.lm.Vb(lm.java:654)
    at com.ibm.db2.jcc.am.lm.executeQuery(lm.java:628)
    at Order2Process.isCustomer(Order2Process.java:78)
    at Order2Process.main(Order2Process.java:25)