This chapter explains how to use SQL to access data services and how to set up and use the Data Services Platform JDBC driver. The chapter covers the following topics:
Many reporting tools, such as Crystal Reports, Business Objects, Microsoft Access, and Microsoft Excel, can access data using SQL. SQL can also be useful in other contexts. Java applications, for example, can access data using SQL. You can also run ad hoc SQL queries using development tools such as Data Tools Platform (DTP) or SQL Explorer.
The Data Services Platform JDBC driver enables JDBC and ODBC clients to access information from data services using SQL. The Data Services Platform JDBC driver thereby increases the flexibility of the ALDSP integration layer by enabling access from a range of database applications and reporting tools.
For the client, the ALDSP integration layer appears as a relational database, with each data service operation comprising a table or a stored procedure. Internally, ALDSP translates SQL queries into XQuery. Figure 5-1 illustrates SQL access to data using the Data Services Platform JDBC driver.
As Figure 5-1 shows, source data can be consolidated, integrated, and transformed using Data Services Platform data services. The source data itself can come from disparate sources throughout the enterprise, including relational databases and Web services, among others.
You can then, in turn, expose the data service operations as a relational data source accessible using SQL queries. This enables JDBC clients to access data consolidated through Data Services Platform.
Note that the Data Services Platform JDBC driver does impose the following constraints on data services:
The Data Services Platform JDBC driver implements the java.sql.* interface in JDK 1.5x to provide access to an Data Services Platform server through the JDBC interface. The driver has the following features:
Using the Data Services Platform JDBC Driver, you can control the metadata accessed through SQL based on the access rights set at the JDBC driver level. This access control ensures that users can view only those tables and procedures that they are authorized to access.
However, to use this feature, the Data Services Platform console configuration should be set to check access control. For more information, refer to the “ Securing Data Services Platform Resources” section in the Administration Guide.
The Data Services Platform views data retrieved from a database in the form of data sources and operations. Table 5-2 shows the equivalent terminology.
For example, if you have a project SQLHowTo
and a data service EmpInfo.ds
with an operation getAll()
, you can use SQL Mapper to expose it as JDBCdemo.empData.empinfo
. The JDBC driver would then see a table called empinfo
with schema empData
and catalog JDBCdemo
.
This section describes the JDBC and SQL support in the Data Services Platform JDBC driver
The Data Services Platform JDBC driver implements the following interfaces from the java.sql
package as specified in JDK 1.5x:
The Data Services Platform JDBC driver supports the following methods:
This section outlines SQL-92 support in the Data Services Platform JDBC driver, and contains the following sections:
The Data Services Platform JDBC driver provides support for the SQL-92 SELECT statement. The INSERT, UPDATE, and DELETE statements are not supported. Additionally, the driver does not support DDL (Data Definition Language) statements.
The Data Services Platform JDBC driver supports functions that you can use to access and process data. This section describes the following supported Data Services Platform SQL-92 query language functions:
The Data Services Platform JDBC driver supports the numeric functions described in Table 5-4.
CEIL returns the smallest integer greater than or equal to n. If n is NULL, the return value is NULL. |
||
The Data Services Platform JDBC driver supports the string functions described in Table 5-5.
The Data Services Platform JDBC driver supports the datetime functions described in Table 5-6.
The Data Services Platform JDBC driver supports the aggregation functions described in Table 5-7.
The Data Services Platform JDBC driver supports standard JDBC API search patterns, as shown in Table 5-8.
Note: | For more information about using the JDBC metadata API, refer to the Java documentation at: |
Assuming that the default_catalog is catalog1 and default_schema is schema1, Table 5-9 shows some common matching patterns.
The Data Services Platform JDBC driver extends the standard SQL-92 parameter model by providing the ability to add table parameters to SQL FROM clauses. For example, in SQL you might encounter a situation where it is necessary to specify a list of parameters (highlighted) in a query.
In the following query, JDBCdemo.empData.empinfo
is the entire customer table.
SELECT emp.empid, emp.name, emp.salary
FROM JDBCdemo.empData.empinfo emp
WHERE emp.empid in (?, ?, ?, ...)
or emp.name in (?, ?, ?, ...)
If the number of parameters can vary, you need to specify a query for each case. Table parameters provide an alternative by enabling you to specify that the query accept a list of values (the list can be of variable length). The following query uses table parameters (highlighted):
SELECT emp.empid, emp.name, emp.salary
FROM JDBCdemo.empData.empinfo emp
WHERE emp.empid in (SELECT * FROM ? as emp(empid))
or emp.name in (SELECT * FROM ? as emp(empname))
The table parameter is specified using the same mechanism as a parameter; a question mark ("?") is used in place of the appropriate table name.
Note: | You can only pass a table with a single column as a table parameter. If you specify more than one column, an exception is thrown. |
For more information about using table parameters, see Using Table Parameters.
When using the ALSDSP JDBC driver, each connection points to one Data Services Platform dataspace. Table 5-10 notes the Data Services Platform JDBC driver limitations that apply to SQL language features.
This section describes the tasks you need to perform prior to using SQL to access data services, and contains the following topics:
To access data services using SQL, you first need to publish the data service operations as SQL objects within the AquaLogic Data Services Platform-enabled project. These SQL objects include tables, stored procedures, and functions.
Note: | SQL objects published through Data Services Platform need to be enclosed in double quotes when used in an SQL query, if the object name contains a hyphen. For example SELECT “col-name” FROM “table-name” . |
To publish data service operations as SQL Objects, perform the following steps:
After the dataspace is deployed, the newly created SQL objects are available to the dataspace through the ALSDSP JDBC driver.
The Data Services Platform JDBC driver is located in the ldjdbc.jar
file, which is available in the <
ALDSP_HOME>/lib
directory after you install BEA AquaLogic Data Services Platform. To use the Data Services Platform JDBC driver on a client computer, you need to configure the classpath, class name, and the URL for the JDBC driver.
Note: | You will need gateway software to enable connectivity between the JDBC driver and DSP to configure the JDBC driver. For more information, refer to the section entitled Accessing Data Services Using SQL Explorer. |
To configure the driver on a client computer, perform the following steps:
ldjdbc.jar
and weblogic.jar
(in the <ALDSP_HOME>/lib
and <WL_HOME>/server/lib
directories respectively) to the client computer.ldjdbc.jar
and weblogic.jar
to the classpath on the client computer. %JAVA_HOME%/jre/bin
to the path on the client computer.com.bea.dsp.jdbc.driver.DSPJDBCDriver
jdbc:dsp@<DSPServerName>:<ALDSPServerPortNumber>/<DataspaceName>
For example the driver URL could be:
jdbc:dsp@localhost:7001/Test_DataSpace
Alternatively, set the default catalog name and schema name in the URL while connecting to the JDBC driver using the following syntax:
jdbc:dsp@<DSPServerName>:<ALDSPServerPortNumber>/<DataspaceName>/
<catalogname>/<schemaname>
Note: | If you do not specify the CatalogName and SchemaName in the JDBC driver URL, then you need to specify the three-part name for all queries. For example: |
select * from <catalogname>.<schemaname>.CUSTOMER
logFile
property. To log debugging information, use the following JDBC driver URL syntax:jdbc:dsp@localhost:7001/test;logFile=c:\output.txt
In this case, the log file is created in the c:\output.txt
file. You can also specify the debug property separately instead of specifying it with the URL.
Note: | If you build an SQL query using a reporting tool, the unqualified JDBC function name is used in the generated SQL. Consequently, to enable application developers to invoke an database function, the default catalog and schema name must be defined in the JDBC connection URL. It is also a requirement that any JDBC connection utilize those functions available from a single SQL catalog:schema pair location. |
The following is an example URL defining a default catalog and schema for a JDBC connection:
jdbc:dsp@localhost:7001/myDataspace/myCatalog/mySchema
Note: | You can specify the default schema and catalog name using the default_catalog and default_schema property fields in case you do not specify it in the properties. If dataspace , default_catalog , or default_schema appears in both the connection properties and the URL, the variable in the URL takes precedence. |
For more information, see Configuring the Connection Using the Properties Object or Configuring the Connection in the JDBC URL respectively.
You can have a Java application access information from data services using SQL through the Data Services Platform JDBC driver.
To access the data from a Java application, perform the following steps:
For more information, see Obtaining a Connection.
You can use either the PreparedStatement or CallableStatement interface to specify and submit the query to the datasource. For more information, see Using the PreparedStatement Interface and Using the CallableStatement Interface respectively.
A JDBC client application can connect to a deployed Data Services Platform dataspace by loading the Data Services Platform JDBC driver and then establishing a connection to the dataspace. In the database URL, use the Data Services Platform dataspace name as the database identifier with “dsp” as the sub-protocol, using the following form:
jdbc:dsp@
<WLServerAddress>:<WLServerPort>/<DataspaceName>
(/default catalog/default schema;
param(=value1; param2=value2;)?
jdbc:dsp@localhost:7001/Test_DataSpace
The name of the Data Services Platform JDBC driver class is:
com.bea.dsp.jdbc.driver.DSPJDBCDriver
You can establish a connection to an Data Services Platform dataspace using the Properties object as follows:
Properties props = new Properties();
props.put("user", "weblogic");
props.put("password", "weblogic");
props.put("application", "TestProjectDataSpace");
// Load the driver
Class.forName("com.bea.dsp.jdbc.driver.DSPJDBCDriver");
// Get the connection
Connection con = DriverManager.getConnection("jdbc:dsp@localhost:7001",
props);
Alternatively, you can specify the Data Services Platform dataspace name, TestProjectDataSpace
, in the connection object itself, as shown in the following segment:
Properties props = new Properties();
props.put("user", "weblogic");
props.put("password", "weblogic");
// Load the driver
Class.forName("com.bea.dsp.jdbc.driver.DSPJDBCDriver");
// Get the connection
Connection objConnection = DriverManager.getConnection(
"jdbc:dsp@localhost:7001/TestProjectDataSpace",props);
You can also configure the JDBC driver connection without creating a Properties object, as shown in the following segment:
// Load the driver
Class.forName("com.bea.dsp.jdbc.driver.DSPJDBCDriver");
// Get the connection
Connection objConnection = DriverManager.getConnection(
"jdbc:dsp@localhost:7001/TestProjectDataSpace;logFile=
c:\output.txt; ", <username>, <password>);
You can use the preparedQueryWithParameters
method to specify a query to the JDBC datasource using the connection object (conn
), obtained earlier. The connection object is obtained through the java.sql.Connection
interface to the WebLogic Server, which hosts Data Services Platform.
Note: | You can create a preparedStatement for a non-parametrized query as well. The statement is used in the same manner. |
In this query, the data service function getAll()
in the data service EmpInfo.ds
under the SQLHowTo
project is mapped using SQL Mapper to JDBCdemo.empData.empinfo
.
public ResultSet preparedQueryWithParameters(Connection conn) throws
java.sql.SQLException {
PreparedStatement ps = conn.prepareStatement("SELECT *
FROM JDBCdemo.empData.empinfo emp WHERE emp.salary >= ?");
ps.setInt(1,275000);
ResultSet rs = ps.executeQuery();
return rs;
}
In the SELECT query, JDBCdemo
is the catalog name, empData
is the schema name, and empinfo
is the table name.
Note: | For more information about how to map data service operations to SQL objects, refer to Publishing Data Service Operations on page 5-21. |
After you establish a connection to a server where Data Services Platform is deployed, you can call a data service operation to obtain data using a parameterized data service operation call.
The following example shows how to call a stored query with a parameter (where conn
is a connection to the Data Services Platform server obtained through the java.sql.Connection
interface). In the segment, a stored query named getBySalary
is called passing a parameter with a value of 275000
.
public ResultSet storedQueryWithParameters(Connection conn) throws
java.sql.SQLException {
CallableStatement ps =
conn.prepareCall("call JDBCdemo.empData.getBySalary(?)");
ps.setInt(1,275000);
ResultSet rs = ps.executeQuery();
return rs;
}
Note: | You can also use the prepareCall method as follows: |
conn.prepareCall("{call JDBCdemo.empData.getBySalary(?)}");
This section describes advances features and uses of the Data Services Platform JDBC driver and contains the following sections
This section describes how to use the Data Services Platform JDBC driver to pass table parameters to data services.
Consider the case in which a data service contains consolidated information of all employee contact information. A manager further has a consolidated list of all government employees in European countries. The goal is to use a data service to obtain contact information for that specific subset of employees.
The scenario is a common one involving the need for a join between the manager’s employee list and contact information. However, if the manager’s employee list is long and not already available through a database, it is convenient to pass a list of values as if it were a column in a table.
In the SQL cited above, a list of employees is passed in as a table with a single column. The clause
? as emp(empid)
provides a virtual table value (emp) and a virtual column name (empid).
Note: | You should alias all table parameters since the default table/column names are undefined and may produce unexpected name conflicts. |
The Data Services Platform JDBC driver passes table parameters to data services through its TableParameter class. The class (shown in its entirety in Listing 5-1) represents an entire table parameter and the rows it represents.
public class TableParameter implements Serializable
/**
* Constructor
*
* @schema the schema for the table parameter
*/
public TableParameter(ValueType[] schema);
/**
* Creates a new a row and adds it to the list of rows in this
* table parameter
*/
public Row createRow();
/**
* Gets the rows of this table parameter
*/
public List/*Row*/ getRows();
/**
* Gets the schema of this table parameter
*/
public ValueType[] getSchema();
/**
* Represents a row in the table parameter
*/
public class Row implements Serializable {
/**
* Sets a value to a particular column
* @param colIdx the index of the column to set, always 1
* @param val the value for the column
* @exception if index is out of bounds
*/
public void setObject(int colIdx,Object val) throws SQLException;
Object getObject(int colIdx);
}
The following steps show how to create a TableParameter instance and populate the instance with data:
Note: | At present only one column is supported for table parameters. |
createRow( )
method on TableParameter to create a new Row object representing a tuple in the table.setObject(1,val)
call to set the column on the Row object.createRow( )
again to create as many rows as the table requires.You can pass table parameters through JDBC just like any other parameter, using the PreparedStatement interface.
To pass table parameters using the PreparedStatement interface:
PreparedStatement ps = c.prepareStatement("SELECT * " +
"FROM ? as EMP(empid), JDBCdemo.empData.contact CONTACT " +
"WHERE CONTACT.empid = EMP.empid AND CONTACT.zip=?");
ps.setObject(2,"98765");
ValueType[] tableType = new ValueType[1];
tableType[0] = ValueType.REPEATING_INTEGER_TYPE;
TableParameter p = new TableParameter(tableType);
String empidlist = FileUtils.slurpFile("empidlist.txt");
StringTokenizer empids = new StringTokenizer(empidlist,"\n");
while(empids.hasMoreTokens()) {
TableParameter.Row r = p.createRow();
r.setObject(1,new Integer(empids.nextToken()));
}
ps.setObject(1,p);
ps.setObject(1,p);
The following simplified example illustrates the use of a table parameter. The supporting JDBC code is shown in Listing 5-2:
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.Driver;
import java.util.Properties;
import java.util.StringTokenizer;
import com.bea.ld.sql.types.ValueType;
import com.bea.ld.sql.data.TableParameter;
import weblogic.xml.query.util.FileUtils;
public class TableParameterTest {
/**
* Establish a connection to the ALDSP JDBC driver and return it
*/
protected static Connection connect() throws Exception {
// Attempt to locate the JDBC driver
Class.forName("com.bea.dsp.jdbc.driver.DSPJDBCDriver");
Driver driver =
DriverManager.getDriver("jdbc:dsp@localhost:7001");
if(driver == null)
throw new IllegalStateException("Unable to find driver.");
//Set the connection properties to the driver
Properties props = new Properties();
props.setProperty("user", "weblogic");
props.setProperty("password", "weblogic");
props.setProperty("application", "SQLHowTo");
// Try to connect to the driver using the properties set above
Connection c =
driver.connect("jdbc:dsp@localhost:7001", props);
if(c == null)
throw new IllegalStateException("Unable to establish a
connection.");
return c;
}
/**
* Prints a result set to system out
* @param rs the result set to print
*/
protected static void printResultSet(ResultSet rs)
throws Exception{
while(rs.next()) {
for(int i = 1; i < rs.getMetaData().getColumnCount()+1; i++) {
rs.getObject(i);
System.err.print(rs.getObject(i) + " ");
}
System.err.println();
}
rs.close();
}
public static void main(String args[]) throws Exception {
Connection c = connect();
// Create the query
PreparedStatement ps = c.prepareStatement("SELECT * " +
"FROM ? as EMP(empid), JDBCdemo.empData.contact CONTACT " +
"WHERE CONTACT.empid = EMP.empid AND CONTACT.zip=?");
// Set the normal parameter
ps.setObject(2,"98765");
// Create the table parameter
ValueType[] tableType = new ValueType[1];
tableType[0] = ValueType.REPEATING_INTEGER_TYPE;
TableParameter p = new TableParameter(tableType);
// Create the rows of the table parameter from values in a file
String empidlist = FileUtils.slurpFile("empidlist.txt");
StringTokenizer empids = new StringTokenizer(empidlist,"\n");
while(empids.hasMoreTokens()) {
TableParameter.Row r = p.createRow();
r.setObject(1,new Integer(empids.nextToken()));
}
ps.setObject(1,p);
// Run the query and print the results
ResultSet rs = ps.executeQuery();
printResultSet(rs);
}
}
Table 5-11 lists the table parameter ValueTypes supported by the Data Services Platform JDBC driver.
Several relational database management systems provide mechanisms to extend the library of built-in, standard SQL functions with user-defined, custom functions, defined using another language, such as PL/SQL, that can be directly embedded in SQL statements.
You can make these built-in or custom functions in your database available through data services by registering the function with Data Services Platform through a library. After registering the functions, you can use them in SQL statements submitted to the Data Services Platform JDBC driver. The following example shows the use of the custom function myLower()
in a SELECT statement:
select * from CUSTOMER where ? = myLower( LAST_NAME )
Note that the following conditions must be met to enable Data Services Platform to use database-specific or user-defined functions:
myFunction(arg1, arg2)
. This argument must be from the data source for which the function is defined. Remaining arguments, however, may be constants or arguments from another type of data service, such as a web service.TRIM( TRAILING ' ' FROM $column)
as custom database functions.SYSDATE
, ROWNUM
, or similar columns as parameters to custom database functions.
You can access data services using both SQL-based applications and applications that connect to the Data Services Platform JDBC driver through an ODBC-JDBC bridge. This section describes how to configure SQL and ODBC-based applications to access data services, and contains the following sections:
Note: | You can also use the Data Services Platform JDBC driver with the Eclipse Data Tools Platform (DTP) plug-in. To use DTP, download the DTP software using the following link: |
Note: | http://wiki.eclipse.org/index.php/Getting_Started_with_DTP |
You can use the Data Services Platform JDBC driver with Eclipse SQL Explorer to access data services. This section describes how to configure SQL Explorer to use the Data Services Platform JDBC driver and how to specify the connection settings. This section assumes that you have already defined your web server and dataspace project in Eclipse.
Note: | SQL Explorer does not support stored procedures and, therefore, data services exposed as stored procedures through the Data Services Platform JDBC driver do not appear in SQL Explorer. For more information, refer to the Eclipse SQL Explorer web site at: |
To use SQL Explorer, perform the following steps:
com.bea.dsp.jdbc.driver.DSPJDBCDriver
The data displays in the Database Structure and Database Detail window.
jdbc:dsp@<DSPServerName>:<DSPServerPortNumber>/<DSPDataspaceName>
/<Your_CatalogName>/<Your_SchemaName>
You can use an ODBC-JDBC bridge to connect to the Data Services Platform JDBC driver from non-Java applications. This section describes how to configure the OpenLink ODBC-JDBC bridge to connect to the Data Services Platform JDBC driver.
You can use the Openlink ODBC-JDBC driver to interface with the Data Services Platform JDBC driver to query ALDSP dataspaces with client applications such as Crystal Reports, Business Objects XI, Microsoft Access 2003, and Microsoft Excel 2003.
To use the OpenLink bridge, you need to install the bridge and create a system DSN using the bridge. The following describes the steps to complete these two tasks:
For information on installing OpenLink ODBC-JDBC-Lite, refer to the OpenLink Software download page for the Single-Tier (Lite Edition) ODBC to JDBC Bridge Driver (Release 6.0) for use on Windows systems. The page can be accessed at:
http://download.openlinksw.com/download/login.vsp?pform=2&pfam=1&pcat=1&prod=odbc-jdbc-bridge-st&os=i686-generic-win-32&os2=i686-generic-win-32&release-dbms=6.0-jdbc
WARNING: | For Windows platforms, be sure to save the value of your CLASSPATH before installation. |
ldjdbc.jar
and weblogic.jar
. A typical CLASSPATH might look as follows:<ALDSP_HOME>/lib/ldjdbc.jar;
<
WL_HOME>
/server/lib/weblogic.jar;
jvm.dll
file, which should be in the <ALDSP_HOME>/%javaroot%/jre/bin/server
directory. Note: | Do not include the file name jvm.dll in the system path. |
com.bea.dsp.jdbc.driver.DSPJDBCDriver.
jdbc:dsp@<machine_name>:<port>/<dataspace_name>/<catalog_name>/
<schema_name>
This section describes how to configure and use the following reporting tools with the Data Services Platform ODBC-JDBC driver:
Note: | Some reporting tools issue multiple SQL statement executions to emulate a scrollable cursor if the ODBC-JDBC bridge does not implement one. Some drivers do not implement a scrollable cursor, so the reporting tool issues multiple SQL statements, which can affect performance. |
This section describes the steps to connect Crystal Reports to the Data Services Platform JDBC driver along with information about standard configuration files that are available with Data Services Platform installation. It also describes the limitations of using Crystal Reports with Data Services Platform and includes the following topics:
Before you start using Crystal Reports with Data Services Platform, you must modify the default Crystal Reports configuration file, CRConfig.xml
to ensure that Crystal Reports is able to access data services through JDBC. The configuration file is located in the following directory:
<Drive>\Program Files\Business Objects\Common\3.5\java
A modified sample CRConfig.xml
file and an associated CR_Readme.txt file are available at:
<ALDSP_HOME>/samples/ReportingTools/config/Crystal Reports
The CR_Readme.txt file contains instructions on how to apply DSP-specific rules in an existing Crystal Reports installation and how to modify the CRConfig.xml
file.
Before you use Crystal Reports to access data services, consider the following information:
This section assumes that you have successfully completed the following:
http://diamond.businessobjects.com/node/432
CRConfig.xml
file (see Configuring Crystal Reports), To connect Crystal Reports to the JDBC driver and access data to generate reports, perform the following steps:
Business Objects enables you to create a Universe and generate reports based on the specified Universe. In addition, you can execute pass-through SQL queries against Business Objects that do not need the creation of a Universe.
This section provides information on configuring Business Objects to access the Data Services Platform JDBC driver. It includes the following topics:
There are two Business Objects configuration files, odbc.prm
and odbc.sbo
, available with the standard Business Objects installation.
When you install Business Objects, these files are copied to the following location:
<Business_Objects_Home>/BusinessObjects Enterprise 11.5/
win32_x86/dataAccess/connectionServer/odbc
An Data Services Platform installation includes samples of these configuration files along with an associated BO_Readme.txt file, available at the following location:
<ALDSP_HOME>/samples/ReportingTools/config/BusinessObjects
You can edit the Business Objects configuration files according to the instructions in the readme file.
Tip: | When first getting started using Business Objects with Data Services Platform, use the included configuration file to verify the ability to access data services through JDBC. |
Table 5-16 identifies some restrictions and specifies configuration changes you may want to make to your Business Objects configuration files when accessing data using the Data Services Platform JDBC driver.
Before you start using Business Objects to access data services, consider the following information:
com.bea.ld.sql.compiler.ast.SQLTypeCheckingException: Invalid table reference. No such table null.Xtreme.CUSTOMER found.
at
com.bea.ld.sql.compiler.ast.TableRSN.inferSchemaAndCheck(Lcom/bea/ld/
sql/context/SQLContext;Lcom/bea/ld/sql/types/SchemaIndex;)V(TableRSN.java:149)
For details about configuring OpenLink, Connecting to the Data Services Platform Client Using OpenLink ODBC-JDBC Bridge on page 5-38. Business Objects supports all XML types that are supported by AquaLogic Data Services Platform JDBC driver except the following:
To generate a report, perform the following steps:
For details about configuring the OpenLink ODBC-JDBC bridge, refer to Connecting to the Data Services Platform Client Using OpenLink ODBC-JDBC Bridge on page 5-38.
If you want to make the selected universe the default universe, check the box next to Set as my Default universe. Click Finish.
On the left pane, you should see the tables and their fields (columns) on expansion.
You can also run the pass-through queries using the Desktop Intelligence application.
To run pass-through queries, perform the following steps:
Note: | If you need to specify a four part name in a SELECT list (such as, <catalogname>.<schemaname>.<tablename>.<columnname> ), define a table alias using the FROM clause, and then use only two parts <tablealias>.<columnname> in the SELECT list. Data Services Platform JDBC driver extracts only the last two parts from the SELECT list item, and ignores the rest. |
SELECT E.Name FROM JDBCdemo.empData.empinfo E
whereJDBCdemo
is the catalog nameempData
is the schema nameempinfo
is the table nameName
is the column nameE
is the table alias for empinfo
This section describes the procedure to connect Microsoft Access 2003 to ALDSP through an ODBC-JDBC bridge. It includes the following topics:
To connect MS Access to the bridge, perform the following steps.
SQL_DECIMAL
and SQL_NUMERIC
fields to the closest Jet numeric data type, depending upon the precision and scale of the ODBC field. In certain cases, this mapping results in a map to a non-exact (floating point) numeric Jet data type, such as Double
or a Text field. For details, refer to the following Microsoft article:This implicit type conversion by MS Access causes some errors when retrieving data from Data Services Platform using MS Access.
This section describes the procedure for connecting Microsoft Excel 2003 to ALDSP through an ODJB-JDBC bridge using OpenLink.
To connect MS Excel to Data Services Platform, perform the following steps:
The Query Wizard - Choose Columns dialog box displays. For details on configuring the JDBC driver using OpenLink, refer to Connecting to the Data Services Platform Client Using OpenLink ODBC-JDBC Bridge on page 5-38.
When passing a generated SQL string to Excel, there are situations where Excel inserts single quotes around an alias, resulting in an exception from the Data Services Platform JDBC driver. Here is an example:
SELECT Sum(EMP.SALARY) AS 'Salary Cost' FROM JDBCdemo.empData.empinfo emp
Although you can edit your query post-generation, another option is to install a patch from Microsoft that is designed to address the problem. The current URL for accessing information on this problem and patch is listed below: