Resolving fn:collection Errors

DataDirect XQuery uses fn:collection to access a relational table or to access multiple XML files in a directory. For example, the following query accesses the holdings database table: 

collection('holdings')

When DataDirect XQuery cannot resolve the fn:collection argument to a specific database object or to a file system directory, it raises an error.

Guidelines for Resolving Errors

When DataDirect XQuery cannot resolve the fn:collection argument to a specific database object, it raises an error such as: 

Table x not found in any JDBC connection or Table x found 
in multiple JDBC connections.

If you encounter these types of errors when using DataDirect XQuery, the following guidelines will help you troubleshoot and correct the cause of the error:

Qualifying Table Names

If you have multiple database tables with the same name or the default catalog and schema associated with the connection do not provide access to the database table, you can qualify the database table name in the fn:collection argument to target the specific table. For example, if the default catalog (database) and schema (user) associated with the connection is financial and joseph, respectively, and the target table is owned by the schema mary, qualify the table name in the fn:collection argument: 

collection('financial.mary.holdings')

Using Catalog and Schema Names

To verify that you know the correct catalog name, schema name, and table name, start the SQL tool shipped with your database and connect to the database server. Once connected, execute the following SQL statement against the database:

DB2 and Microsoft SQL Server 

SELECT * FROM "catalog"."schema"."table" WHERE 1=0

Informix 

SELECT * FROM "catalog":"schema"."table" WHERE 1=0

MySQL 

SELECT * FROM 'catalog'.'table' WHERE 1=0

Oracle and PostgreSQL 

SELECT * FROM "schema"."table" WHERE 1=0

Sybase 

SELECT * FROM catalog.schema."table" WHERE 1=0

where catalog, schema, and table are the catalog name, schema name, and table name of the database object you are trying to access.

NOTE: Oracle and PostgreSQL databases do not have catalogs. MySQL databases do not have schemas.

If the SQL statement returns an empty result, the values you entered correspond to the correct catalog name, schema name, and table name. Use these values in the fn:collection argument to qualify the table name. If the SQL statement returns an error, the values you entered are incorrect.

IMPORTANT: The case of the values specified in the fn:collection argument must match the case of the database.

On Microsoft SQL Server and Sybase, a user can have the special status of database owner. For example, if the SQL name of the target table is "financial"."dbo"."holdings," qualify the table name in the fn:collection argument with the schema name dbo: 

collection('financial.dbo.holdings')

Using JDBC Connection Names

If the table name in the fn:collection argument is qualified with a catalog name or schema name (or both) and DataDirect XQuery returns an error indicating that multiple tables with the same name exist, you need to qualify the table name using a JDBC connection name. A JDBC connection name identifies a specific connection associated with the database table.

Suppose two tables of the same name, holdings, exist on different database servers with the same schema name, joseph. In this case, the following query does not provide enough information for DataDirect XQuery to locate the target table: 

collection('joseph.holdings')

To identify the correct table, you can qualify the table name in the fn:collection argument with a JDBC connection name. Here's an example that shows a connection made explicitly in the application to two different databases; each connection is assigned a unique JDBC connection name, stocks1 and stocks2, respectively: 

DDXQJDBCConnection jc1 = new DDXQJDBCConnection(); 
jc1.setUrl("jdbc:xquery:sqlserver://server1:1433;databaseName=financial"); 
jc1.setName("stocks1"); 
DDXQJDBCConnection jc2 = new DDXQJDBCConnection(); 
jc2.setUrl("jdbc:xquery:oracle://server2:1521;SID=ORCL"); 
jc2.setName("stocks2"); 
DDXQDataSource ds = new DDXQDataSource(); 
ds.setDdxqJdbcConnection(new DDXQJDBCConnection[] {jc1, jc2}); 
XQConnection conn = ds.getConnection("myuserid", "mypswd");

Here's an example showing the same connection information configured in a DataDirect XQuery source configuration file: 

... 
 <JDBCConnection name="stocks1"> 
    <description>connection to stocks1 data</description> 
    <url>jdbc:xquery:sqlserver://localhost:1433;DatabaseName=financial</url> 
    <user>myuserid</user> 
    <password>mypswd</password> 
    ... 
<JDBCConnection name="stocks2"> 
    <description>connection to stocks2 data</description> 
    <url>jdbc:xquery:oracle://localhost:1521;SID=ORCL</url> 
    <user>myuserid</user> 
    <password>mypswd</password> 
    ...

To target the holdings table on server1, qualify the table name with the JDBC connection name stocks1 in addition to the catalog name and schema name: 

collection('stocks1:joseph.holdings')

See Choosing a Connection Method for more information about connecting with DataDirect XQuery.

Escaping Special Characters

If the catalog name, schema name, or table name in the fn:collection argument contains a period (.), colon (:), or backslash (\), escape the character with a backslash (\) so that DataDirect XQuery can parse the argument into its different parts. For example, if the target table is named a.holdings and you specify the following query, DataDirect XQuery parses 'A' as the schema name, not as part of the table name: 

collection('a.holdings')

Escaping the period (.) in the fn:collection argument using the backslash character allows DataDirect XQuery to parse the argument correctly: 

collection('a\.holdings')

In addition, XQuery string literal syntax applies to the fn:collection argument. If a table name contains double quotes, for example, a"holdings, and the fn:collection argument uses double quote delimiters, you must repeat the double quotes: 

collection("a""holdings")

Or, you can use: 

collection('a"holdings')

See the next section Using Aliases for details about how to avoid escaping characters.

Using Aliases

To avoid escaping period (.), colon (:), and double quotes ("), and to avoid the SQL/XML escaping of non-supported XML characters, DataDirect XQuery supports an alias attribute for the catalog, schema, and table elements of the source configuration file.

For example, assume a table exists named tab"le that contains a single integer column named c with one row. In this case, the following query: 

collection("tab""le")

returns: 

<tab_x0022_le> 
   <c>1</c> 
</tab_x0022_le>

Using the alias attribute for the table element as shown in the following source configuration file example: 

<catalog name="catalog"> 
  <schema name="schema"> 
    <table name='tab"le' alias="tablealias"/> 
  </schema> 
</catalog>

you can specify tablealias as the table name in fn:collection: 

collection("tablealias")

which results in: 

<tablealias> 
   <c>1</c> 
</tablealias/>

Verifying Connections

Using DataDirect XQuery, an application establishes a connection to the database to execute a query. The application can establish a connection to the database in multiple ways: explicitly specifying connection information in the application, using a data source registered with JNDI, or using a DataDirect XQuery source configuration file. If DataDirect XQuery cannot access the database because connection information is specified incorrectly or because the structure of the configuration file is incorrect, it raises an error.

Verifying Connection URLs

Verify that the following information in your connection URL is correct:

Checklist

If you encounter an error when using fn:collection with DataDirect XQuery when accessing a relational table, examine the following checklist to resolve the problem:

Querying XML Files in a Directory

The fn:collection argument value is a URL referencing a directory. The URL must use the file:// scheme. See Querying Multiple Files in a Directory for complete details.

Some typical errors made when specifying the directory URL are:

In addition, you may receive an error when querying XML files in a directory if:

See Querying Multiple Files in a Directory for the collection function’s declaration for this feature.