Troubleshooting Database Service Runtime Errors

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 WorkSpace Database Development, Sybase Central, or Interactive SQL (ISQL), to create SQL statement operations. Create and test the statement, copy and paste the statement into the Properties section, and 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)

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 define it as inout. There will be two parameters named reason, one for input, and one for output. If you delete the wrong one, for example, the reason parameter reason for output, there will only be an input parameter named reason, causing a runtime failure. The installation directory\EAServer \logs\MyHostName.log file has an error when building the response message indicating that parameter reason 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 to show this messages.

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:

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 (EAServer\lib\ext) as stated in the Installation Guide.
Search the installation directory\EAServer\logs\MyHostName.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\EAServer \logs\MyHostName.log .

Table 1. Service Tester Fault and Error Information
Section	Description
SQL Fault	`<SqlFaultType>` `<sqlError>` `<reason>JZ006: Caught IOException: java.net.ConnectException: Connection refused: connect</reason>` `<errorCode>0</errorCode>` `<sqlState>` `</sqlState>` `</sqlError>` `</SqlFaultType>`
EAServer log or Service Container 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`

If you receive a connection error when testing an operation, 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 this Service Container log file: installation directory\SC\log\SC55.X.log

Reset the log levels in the Package Profile Runtime Configuration to FINE, FINER, or FINEST. Then repackage and redeploy 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 ( that is, select one of FINE, FINER, or FINEST), and review boththe Service Container log (which does not have any EAServer specific errors) and the EAServer log which shows the Service Container errors in context with EAServer messages.

Example log entries:

Table 2. Log files
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 directoryinstallation directory\EAServer\lib\ext .

If a business process contains a database service that accesses SQL Anywhere fails, verify that Autocommit is set to TRUE.

Use result set discovery. Click Discover in the Result Set pane.

Note: Exercise caution when using Autocommit. If the procedure modifies tables, enabling Autocommit will not allow you to back out changes.

Note: 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 log files to verify the service operation setup: installation directory\EAServer \logs\MyHostName.log 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.

Table 3. Example log entry
Section	Description
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 .`

Shut down the server and study the stack trace. For each trace, check to see if the server shutdown shows a PropertiesNotFoundException. This error maybe generated by artifacts that were not correctly removed from a previous server session. Artifacts that are not correctly removed will continue to trigger these errors every time you shut down the server.

In this case, call the Sybase Support team for assistance in helping you remove any artifacts that remain.