Problem: Runtime errors in a database service.
Solution: Use the following sequence to troubleshoot database service errors during runtime:
Verify that the database server is up and the stored procedure or SQL query used in the database service operation works as a stand-alone.
Use a client tool such as Sybase Central Interactive SQL or ISQL to create SQL statement operations. Create and test the statement, copy and paste the statement into the Properties Panel, and then edit parameters as needed.
Verify that the parameter modes match the stored procedure in the database service.
Sometimes parameters will be inout for output only, and other times they will be in or out for inout.
For example, the following procedure definition defines “returnval”, and “reason” as output parameters.
ALTER PROCEDURE "DBA"."sybstore_save_salesdata" (@salesid integer, @item_num char(20), @qty integer, @price decimal(10, 2), @dateofsale date, @returnval integer output, @reason char(200) output)
as
begin
insert into salesdata(sales_id, item_num, qty, price, dateofsale)
values(@salesid, @item_num, @qty, @price, @dateofsale)
select @returnval = 1,@reason = 'Saved successfully.'
end
Exercise caution when deleting parameters.
If a stored procedure requires parameters, and one or more are deleted, a fatal error will occur, making it difficult to debug.
If a parameter is an inout, two parameters appear in the Operation Parameters pane with the same name, one for In and another for Out. The tooling allows you to delete one or both of them.
For example, if a procedure has an output parameter named “reason”, the JDBC driver may defines it as inout. There will be two parameters named reason, one for input, and one for output. If you delete the wrong one i.e. parameter reason for output, there will only be an input parameter named reason, causing a failure during runtime. The Unwired.log and Jaguar.log files will both have an error when building the response message indicating that part response cannot be found. It sometime requires reading more than one log entyry in order to determine the actual problem.
The following log entries indicate reason was being looked for in salesdataResponse. It is not found because the output parameter was deleted in order to show this message.
Example Log Entries
Log Entry | Jul 25 11:17:07 2005: 2005-07-25 11:17:07 ,12 FINER 12 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.csb.wsif.sybsuper.WSIFOperationSybSuper] executeRequestResponseOperation:433 - _tXnKtv0nEdmT-YxIqQU9pA - Looking up ContentFormat key [sybstore_save_salesdata|out|sybstore_save_salesdataResponse|reason] |
Log Entry | Jul 25 11:17:07 2005: 2005-07-25 11:17:07,12 FINER 12 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.csb.wsif.sybsuper.WSIFOperationSybSuper] executeRequestResponseOperation:456 - _tXnKtv0nEdmT-YxIqQU9pA - IGNORING: contentFormat not found for part [reason], using raw data content. At: com.sybase.soa.services.csb.wsif.sybsuper.WSIFOperationSybSuper.executeRequestResponseOperation(WSIFOperationSybSuper.java:456) |
Log Entry | Jul 25 11:17:07 2005: 2005-07-25 11:17:07,22 SEVERE 12 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.csb.wsif.sybsuper.WSIFOperationSybSuper] handleWSIFException:884 - _tXnKtv0nEdmT-YxIqQU9pA - Cannot get part 'reason'. Part was not found in message. |
Test the database connection profile using the Test connection button when creating the profile.
Test all database service operations using the Service Tester before incorporating the database service in business processes.
If you receive a connection error when testing an operation, do the following:
Verify that the database is running.
Review your setup.If you are using not using a Sybase database, verify that the database jars are copied to the appropriate location in EAServer (EAS\java\lib) as stated in the Installation Guide.
Search the <installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\bin\Unwired.log file for jdbc:sybase:Tds. This is a substring of the actual URL used to connect to the database. The full URL is http://<jdbc:sybase:Tds:name-xp2:2648>.
Ensure that connection parameters are correct.
Verify that the user name and password are correct in the connection profile.
Verify that the package definition has a log level of FINE, FINER, or FINEST; otherwise, the URL will not be logged.
The following table shows the SQL Fault returned by the service tester, and the error logged in the <installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\bin\Jaguar.log.
Service Tester Fault and Error Information
SQL Fault | <SqlFaultType> <sqlError> <reason>JZ006: Caught IOException: java.net.ConnectException: Connection refused: connect</reason> <errorCode>0</errorCode> <sqlState> </sqlState> </sqlError> </SqlFaultType> |
Jaguar Log | Jul 25 10:14:33 2005: 010052-Error: Cannot get connection for cache: SybStoreDBService_cedarSybStore, driver: com.sybase.jdbc2.jdbc.SybDriver, conn url: jdbc:sybase:Tds:cedar:2648/SybStore?ServiceName=SybStore |
Review any fault messages thrown by the JDBC driver for SQLException information indicating a problem.
See the specific JDBC driver or database product documentation for more information reviewing error messages.
Review the following:
Reset the log levels in the Package Profile Runtime Configuration to FINE, FINER, or FINEST. Then re-package and re-deploy the service package. Reviews the log files for debug information.
If you manually created result sets, carefully review column properties for value errors. Verify that the mapped name corresponds to the column name in the database and the data type corresponds to the database type.
If you detect a problem with input or output message content, enable debug logging (FINE, FINER, or FINEST), and review the following:
Log File
| File Name | Description |
|---|---|
<installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\bin\Jaguar.log | Displays EAServer runtime information. |
<installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\bin\Unwired.log | Displays Unwired Orchestrator runtime information. |
Example Log Entries
Log Entry | Jul 25 11:16:08 2005: 2005-07-25 11:16:08,897 FINER 11 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.wshf.endpoint.soap.handler.SOAPHandler] onMessage:97 - _tXnKs_0nEdmT-YxIqQU9pA - On message called in SOAPHandler |
Log Entry | Jul 25 11:16:08 2005: 2005-07-25 11:16:08,897 FINER 11 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.wshf.endpoint.soap.handler.SOAPHandler] logEnvelope:613 - _tXnKs_0nEdmT-YxIqQU9pA - Request SOAPEnvelope follows... |
Log Entry | Jul 25 11:16:08 2005: 2005-07-25 11:16:08,897 FINER 11 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.wshf.endpoint.soap.handler.SOAPHandler] logEnvelope:618 - _tXnKs_0nEdmT-YxIqQU9pA - <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <soapenv:Body> <sybstore_save_salesdata xmlns="urn:mycompany:/SybStore/SybStoreDBService"> <salesid>3 </salesid> <item_num>A6500 </item_num> <qty>1 </qty> <price>10.00 </price> <dateofsale>2005-07-25 </dateofsale> <returnval>1 </returnval> <reason>error </reason> </sybstore_save_salesdata> </soapenv:Body> </soapenv:Envelope> |
If you are using a third party database, such as Oracle, and receive a java.lang.NoClassDefFoundError, the database is looking for a JDBC class such as java.sql.DriverManager or some a java.sql class. Verify that the driver classes have been extracted into the following directory:
<installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\java\classes
If a business process contains a database service that accesses ASA fails, verify that Autocommit is set to TRUE.
Use result set discovery. Click Discover in the Result Set pane.
Exercise caution when using Autocommit.If the procedure modifies tables, enabling Autocommit will not allow you to back out changes.
Result set discovery is automatic except when the using ASE stored procedures. See Chained Mode in the ASE product documentation in the online bookshelf for more information.
Review log information on moperation in the following files to verify the service operation setup:
Log File
| File Name | Description |
|---|---|
<installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\bin\Jaguar.log | Displays EAServer runtime information. |
<installation directory>\Sybase\WorkSpace\DevRuntimes\EAServer\bin\Unwired.log | Displays Unwired Orchestrator runtime information. |
The “Stored Procedure Template” will show the syntax used to create either a JDBC Statement, or PreparedStatement. It will be in the proper JDBC format such as: “select * from salesdata where sales_id=?”, where the question mark denotes a parameter. The “Stored Procedure Type” will indicate if a JDBC Statement, PreparedStatement, or CallableStatement is created for execution. If a JDBC Statement is created, there will be no parameters in the “Stored Procedure Template” (No question marks). Parameter and column types are numerical values that map to JDBC types.
Example Log Entry
Log Entry | Jul 25 13:17:09 2005: 2005-07-25 13:17:09,782 FINER 10 [SybStoreDBService.HTTPTransport.com.sybase.soa.services.wsif.providers.jdbc.util.AIDebugHandler] println:36 - _tXnKuP0nEdmT-YxIqQU9pA - (Thread[Thread-20,5,main]): SPRequest.Create(), method info: SPMethodInfo: Method Name: getSales Data Catalog Name: Schema Name: Stored Procedure Name: Catalog Separator: . Stored Procedure Template: select * from salesdata where sales_id=? Stored Procedure Type: java.sql.PreparedStatement Quoted String Delimiter: " Return Value Index: -1 Auto Commit: false 1 parameters: [0]SPParamInfo: Parameter Name: salesId Parameter Mapped Name: salesId Parameter Type: 4 Parameter Mode: 1 Parameter is nullable: false Parameter is fragment: false Parameter will be substitued for the following marker positions: 0 1 result sets: [0]SPResultSetInfo: Result Set Name: Sales Result Set Number: 1 Result Row Name: order 5 columns: [0]SPColumnInfo: Column Name: sales_id Column Mapped Name: sales_id Jul 25 13:17:09 2005: Column Type: 4 Column is nullable: false [1]SPColumnInfo: Column Name: item_num Column Mapped Name: item_num Column Type: 1 Column is nullable: false [2]SPColumnInfo: Column Name: qty Column Mapped Name: qty Column Type: 4 Column is nullable: false [3]SPColumnInfo: Column Name: price Column Mapped Name: price Column Type: 3 Column Scale: 2 Column is nullable: false [4]SPColumnInfo: Column Name: dateofsale Column Mapped Name: dateofsale Column Type: 93 Column is nullable: false . |
Send your feedback on this help topic to Sybase Tech Pubs: pubs@sybase.com