Feedback
Did this article resolve your question/issue?

   

Article

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

« Go Back

Information

 
TitleTable x not found in any JDBC connection or Table x found in multiple JDBC connections
URL Name3074
Article Number000143387
Environment
Question/Problem Description

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 such as:

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

 

Steps to Reproduce
Clarifying Information
Error MessageTable x not found in any JDBC connection or Table x found in multiple JDBC connections.
Defect/Enhancement Number
Cause
Resolution

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:

  • Qualify table names in fn:collection() arguments 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.
  • Escape special characters in catalog, schema, and table names.
  • Verify connections associated with the query.
  • When querying XML files in a directory, make sure you specify the directory URL correctly. One typical mistake is that the file:/// URL prefix was not specified as part of the directory URL.


1. 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')

 

2. 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')


3. 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:

... 
    connection to stocks1 data
    jdbc:xquery:sqlserver://localhost:1433;DatabaseName=financial
    myuserid
    mypswd 
...

    connection to stocks2 data
    jdbc:xquery:oracle://localhost:1521;SID=ORCL
    myuserid
    mypswd 
...

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.


4. 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/> 


 

 

5. 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:

  • Type of database to which the application is connecting.
  • TCP/IP address or host name of the database server to which the application is connecting.
  • Number of the TCP/IP port.
  • User name used to connect to the database.
  • Password used to connect to the database.
  • Database-specific connection properties that provide additional connection information. DatabaseName (for DB2 and Microsoft SQL Server) and SID (for Oracle) are commonly used properties. For a list of available database-specific connection properties, see the tables in "Specifying Connection URLs".

6. 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:

  • Qualify table names in fn:collection() arguments 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.

Make sure that you know the correct catalog name, schema name, and table name (including case).

If you are accessing Microsoft SQL Server or Sybase and the database table is owned by dbo, make sure that you qualify the table name with the schema name dbo. For example:

collection('financial.dbo.holdings')

If you qualify the table name with a catalog name or schema name (or both) and DataDirect XQuery returns an error indicating that multiple collections are found, you may want to qualify the table name with a JDBC connection name.

  • Escape special characters in catalog, schema, and table names. See "Case Sensitivity" for details about how to avoid escaping characters.
  • Verify connections associated with the query.

Make sure that the information specified in your connection URL is correct including: database type, server name, port, user, password, and any database-specific connection properties.

If using a DataDirect XQuery source configuration file, make sure that it validates against the source_config.xsd schema shipped with DataDirect XQuery. This schema is located in the examples/config subdirectory of your DataDirect XQuery installation directory. You can validate the configuration file using a tool such as XML Editor for Eclipse (DataDirect XQuery Edition) or Stylus Studio.

If using a DataDirect XQuery source configuration file, make sure that the values of the catalog, schema, and table elements are correct.

 


7. 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.

  • The specified URL is missing the file:/// URL prefix. For example, collection(c:/myDir) must be collection(file:///c:/myDir).
  • A forward slash (/) is missing in the directory URL. For example, collection(file://c:/myDir) must be collection(file:///c:/myDir).
  • The specified URL does not reference a directory.

    When querying XML files in a directory, make sure you specify the directory URL correctly. One typical mistake is that the file:/// URL prefix was not specified as part of the directory URL.
    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.

     

Workaround
Notes
Last Modified Date9/14/2015 1:25 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.