Do-SQL
Description
Executes one or more general SQL statements either individually or as a batch.
Do-SQL is a general-purpose webject. It can be used both as a query and an action webject.
Syntax
<ie:webject name="Do-SQL" type="ACT">
<ie:param name="BLOB_COUNT" data="number_of_BLOBs"/>
<ie:param name="CONNECTION_ATTEMPT_INTERVAL" data="interval"/>
<ie:param name="CONNECTION_ATTEMPTS" data="attempts"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="GROUP_OUT" data="group_out"/>
<ie:param name="INSTANCE" data="instance"/>
<ie:param name="MAX_QUERY_SIZE" data="max_query_size"/>
<ie:param name="MODE" data="mode_of_execution"/>
<ie:param name="PASSWD" data="dbpassword"/>
<ie:param name="SQL" data="an_SQL_statement"/>
<ie:param name="QUERY_TIMEOUT" data="query_timeout"/>
</ie:webject>
<ie:param name="BLOB_COUNT" data="number_of_BLOBs"/>
<ie:param name="CONNECTION_ATTEMPT_INTERVAL" data="interval"/>
<ie:param name="CONNECTION_ATTEMPTS" data="attempts"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="GROUP_OUT" data="group_out"/>
<ie:param name="INSTANCE" data="instance"/>
<ie:param name="MAX_QUERY_SIZE" data="max_query_size"/>
<ie:param name="MODE" data="mode_of_execution"/>
<ie:param name="PASSWD" data="dbpassword"/>
<ie:param name="SQL" data="an_SQL_statement"/>
<ie:param name="QUERY_TIMEOUT" data="query_timeout"/>
</ie:webject>
Parameters
|
Required
|
Select
|
Optional
|
|---|---|---|
|
INSTANCE
|
BLOB_COUNT
|
|
|
SQL
|
CONNECTION_ATTEMPTS
|
|
|
CONNECTION_ATTEMPT_INTERVAL
|
||
|
DBUSER
|
||
|
GROUP_OUT
|
||
|
MAX_QUERY_SIZE
|
||
|
MODE
|
||
|
PASSWD
|
||
|
QUERY_TIMEOUT
|
BLOB_COUNT
Specifies how many BLOBs to deliver to the webject. Specifying a value of 0 results in no BLOBs being delivered. Specifying a value of more than 0 results in up to that specified number of BLOBs being delivered. For example, if this parameter is specified with a value of 5, then no more than five BLOBs are delivered to the webject.
The default behavior for this parameter is that all remaining BLOBs are delivered to the webject. This parameter is optional.
CONNECTION_ATTEMPTS
Defines the maximum number of times to attempt establishing a connection to an adapter before returning an error. The default value is 1. This parameter is optional.
If multiple INSTANCE parameter values are specified, the value of CONNECTION_ATTEMPTS defines the maximum number of times to iterate through the list of adapter instances.
CONNECTION_ATTEMPT_INTERVAL
Defines the amount of time, in seconds, to delay between connection attempts. The default value is 60 seconds. This parameter is optional.
If multiple INSTANCE parameter values are specified, the value of CONNECTION_ATTEMPT_INTERVAL defines the number of seconds to wait between the attempts to iterate through the entire list of adapter instances.
DBUSER
Specifies the name to use when logging in to the data repository. If this parameter is specified in this webject, the webject value takes precedence over any value specified in the credentials mapping settings or in the adapter LDAP entry. If this parameter is not specified here, it must be specified in the credentials mapping settings or in the adapter LDAP entry. For more information about credentials mapping, see the section Credentials Mapping.
GROUP_OUT
Identifies the group returned by the webject. This parameter is optional.
INSTANCE
Specifies the name of the adapter that executes the webject. Adapter names are defined when the adapter is configured for use in your Info*Engine environment. This parameter is required.
In order to provide the ability to connect to other adapters if a specific adapter is not available, this parameter can be multi-valued. Info*Engine attempts to connect to the adapters in the order given. If the first adapter specified is not available, the next adapter listed is tried, and so on, until a connection is made. If a connection cannot be established with any listed adapter, an error is returned.
In conjunction with this parameter, you can include two other parameters: CONNECTION_ATTEMPTS and CONNECTION_ATTEMPT_INTERVAL.
MAX_QUERY_SIZE
Specifies maximum number of objects returned from a database query. The default for this parameter is 2000. If there are more than the maximum number of objects, the JDBC driver silently drops the extra objects. MAX_QUERY_SIZE is unused when MODE is set for batch execution. This parameter is optional.
MODE
Specifies the mode of execution, which is relevant only when executing multiple SQL commands. To run in batch mode, set this parameter to BATCH. If the parameter is not provided or if it is set to any value other than BATCH, the SQL commands (provided as values for the SQL webject parameter) are executed individually rather than as a batch. This parameter is optional.
 |
The case associated with the value provided for the webject parameter MODE is unimportant. Thus, the value can be BATCH, Batch, batch, and so on.
When the adapter connects to the SQL server, certain CREATE statements (such as CREATE VIEW) cannot be combined with other statements in a batch.
|
PASSWD
Specifies the password to use when logging in to the data repository. If this parameter is specified in this webject, the webject value takes precedence over any value specified in the credentials mapping settings or in the adapter LDAP entry. If this parameter is not specified here, it must be specified in the credentials mapping settings or in the adapter LDAP entry. For more information about credentials mapping, see the section Credentials Mapping.
SQL
Specifies the SQL statement to be executed. There can be multiple entries of this webject parameter. When MODE is set for batch execution, this parameter can only take an SQL update command (a command that returns a simple update count) as its value. If this condition is violated, an exception is thrown upon executing the webject. However, there is no such restriction for the normal mode of execution. This parameter is required.
QUERY_TIMEOUT
Specifies the minimum amount of time (in seconds) the driver waits before it cancels a running statement (or times out). This parameter is optional.
Example
The following example documents DoSql.jsp located in the Windchill installation directory:
codebase/infoengine/jsp/examples/JDBCAdapter/examples
This example illustrates the Do-Sql webject for the normal mode of execution. In the example, a record including the ENAME attribute of ADAMS is retrieved from the EMP table. The record is then displayed using the Display-Table webject.
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Do-SQL Webject</title>
<BASE>
</head>
<body>
<h1>Do-SQL webject: </h1>
<h3>Performs a general SQL statement</h3>
<ie:webject name="Do-SQL" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="SQL" data="SELECT * FROM EMP WHERE
(ENAME='ADAMS')"/>
<ie:param name="GROUP_OUT" data="DoSql"/>
</ie:webject>
<ie:webject name="Display-Table" type="DSP"/>
</body>
</html>
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Do-SQL Webject</title>
<BASE>
</head>
<body>
<h1>Do-SQL webject: </h1>
<h3>Performs a general SQL statement</h3>
<ie:webject name="Do-SQL" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="SQL" data="SELECT * FROM EMP WHERE
(ENAME='ADAMS')"/>
<ie:param name="GROUP_OUT" data="DoSql"/>
</ie:webject>
<ie:webject name="Display-Table" type="DSP"/>
</body>
</html>
The following example documents BatchUpdate.jsp, which illustrates the Do-Sql webject for the batch mode of execution. Running this example inserts three additional rows to the table EMP by executing the SQL update commands (provided as values for the SQL webject parameter) as a batch.
<%@page language="java" session="false"
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Do-SQL Webject</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Do-SQL webject: </h1>
<h3>Executes two or more SQL update commands as a batch</h3>
<ie:webject name="Do-SQL" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8000,'John','CLERK',7902,'11-JAN-1999',800,NULL,20)"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8001,'Jim','CLERK',7902,'21-JUN-1999',850,NULL,20)"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8002,'Jack','CLERK',7902,'27-DEC-1999',900,NULL,20)"/>
<ie:param name="MODE" data="BATCH"/>
<ie:param name="GROUP_OUT" data="BatchUpdate"/>
</ie:webject>
</body>
</html>
errorPage="IEError.jsp"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core"
prefix="ie"%>
<html>
<head><title>Do-SQL Webject</title>
<BASE>
</head>
<body bgcolor="#AABBCC">
<h1>Do-SQL webject: </h1>
<h3>Executes two or more SQL update commands as a batch</h3>
<ie:webject name="Do-SQL" type="ACT">
<ie:param name="INSTANCE" data="${@FORM[]instance[]}"
default="jdbcAdapter"/>
<ie:param name="DBUSER" data="dbuser_name"/>
<ie:param name="PASSWD" data="dbuser_passwd"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8000,'John','CLERK',7902,'11-JAN-1999',800,NULL,20)"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8001,'Jim','CLERK',7902,'21-JUN-1999',850,NULL,20)"/>
<ie:param name="SQL" data="INSERT INTO EMP VALUES
(8002,'Jack','CLERK',7902,'27-DEC-1999',900,NULL,20)"/>
<ie:param name="MODE" data="BATCH"/>
<ie:param name="GROUP_OUT" data="BatchUpdate"/>
</ie:webject>
</body>
</html>