Hi, We are getting the below error when data is tried to be pushed from xml to table.

[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service com…
[ADA.1.316] Cannot execute the SQL statement “INSERT INTO schema.table(Col1,Col2,Col3,Col4,Col5,Col6,Col7) VALUES (?, to_timestamp(?,‘YYYY-MM-DD’), ?, ?, ?, ?, ?)”. "
-3(0) -3(1) -3(2) -3(3) -3(4) -3(5) -3(6) -3(7) -3(8) -3(9) -3(10) -3(11) -3(12) -3(13) -3(14)
(08000/17410) No more data to read from socket
(08000/17410) No more data to read from socket"
No more data to read from socket

Please suggest what could be the reason.

Need below details:

wM version, JDBC version with fix levels

DB used?

Check you are able to ping or telnet your database (this is to check you do not have any network connectivity issues)

What is your minimum connection pool setting in JDBC connection?

Sweta – Try setting the minimum connection pool size to 0 for the pool that this query is executing against - sounds to me like a stale connection. Also see does it affect performance.

Thanks,

Hi Sweta,

additionally provide the signature of the adapter service.

There might be an issue with the number of parameters passed to the adapter service.
It is expecting 7 parameters (indicated by ‘?’) and there are 15 entries in the row with the ‘-3()’-list.

This ‘-3()’-list looks a little bit weird to anyway.

Regards,
Holger

Thanks All for all the replies. This issue happened only once and got resolved without any changes made. next day onwards data was inserted to the table without error.I will try to get the required details from webMethods team asked here.

Glad to hear issue got resolved. Thanks for updating the forum.

Thanks,

The issue is resolved. But we have no clue about the root cause. Web Methods is upstream system for us. So i wanted to know if this issue can be assumed as network issue or issue with Web methods or issue with SQL Developer.

Hi Sweta,

As you said this issue happened only once and got resolved without any changes made. So it seems only network issue because there could be possible that break a connection for a while for some reason. However the server logs provide the more and precise insights.

Please update forum if any other issue identify.

Thanks,
Arun

Sweta – As it happend only once, we can see it might be due to n/w glitch. Can you check with N/w team any issues occurred during that window, also kindly update us if you come across this again.

Thanks,

Hi everyone, I’m getting the below error

[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service workmgmt_oktauserprofile.updateWMSUserProfile.adapters:updateWMSUserProfile.
[ADA.1.316] Cannot execute the SQL statement “call NUWMSWEB.OKTA_WMS.USP_UPDATE_USER( ?, ?, ?, ?, ?)”. "
(17004) Invalid column type"
Invalid column type

What could be this issue friends?

Hi Karthika,

please provide more details:

wM Version, JDBC Adapter Version (both with FixLevels)

Was this working in the past?

Were the any changes to the server and it has been overseen that an update of database tables is needed?

Regards,
Holger

Just another question:

which service template was used for the service?

Can you share a screenshot of the Input/Output-Tab of your service

Hi all,

I also have a problem on this one having the error below:

[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service test.util.jdbc:insertService.\n[ADA.1.316] Cannot execute the SQL statement "INSERT INTO [dbo].[Table1] ([Field1],[Field2],[Field3],[Field4],[Field5],[Field6],[Field7]) VALUES(?,?,?,?,?,?,?)". "\n(0) The connection is closed."\nThe connection is closed.

The strange thing is, it only happens when inserting a record for the first time. Once the error occurred, when you try inserting again, the insert succeed.

After awhile when the Integration Server is on idle for 10mins or so, the Error throws again saying " The connection is closed".

I’m currently accessing to AZURE Database with these jdbc adapter connection settings

Connection Type:webMethods Adapter for JDBC Connection
Package Name:testPackage
Connection Properties
Transaction Type:LOCAL_TRANSACTION
DataSource Class:com.microsoft.sqlserver.jdbc.SQLServerDataSource
serverName:
user:
password:
databaseName:
portNumber:
networkProtocol:tcp
Other Properties
Connection Management Properties
Enable Connection Pooling:TRUE
Minimum Pool Size:1
Maximum Pool Size:10
Pool Increment Size:1
Block Timeout (msec):1000
Expire Timeout (msec):1000
Startup Retry Count:0
Startup Backoff Timeout (sec):10

What am I missing here

Hoping for your replies

Thanks,
Jay

Hi Jay,

Connection properties looks fine. As per your description , it seems failure is happening at the time of getting connection from the connection manager or establishing initial connection. You can try with increasing Retry-count and backOff-Timeout.

Are you trying to connect wM server in local system to connect to Azure?

Thanks
Baharul Islam

Hi Baharul,

Thanks for the reply.

I’m not sure what do you mean by wM server.

But the set up is,
I’m trying to connect to the Azure Database using the SQL Adapter Custom SQL in my Integration Server locally (but the error is also experienced in production).

I’ll try the Retry-Count and backOff-Timeout that you suggested and reply back here for the results.

Thanks.
Jay

Hi,

can you try with MinPoolSize=0?
This will avoid stale connections in the pool.
When hitting a stale connection from you might get the error message mentioned above.
When this happens the stale connection gets discarded and new one will be created.

With MinPoolSize=0 stale connections do not remain in the pool, but get discarded when they time out.

Regards,
Holger

Hi Holger,

Thanks for the reply. I will try setting the Minimum Pool Size to zero and monitor it.

I will post the results here.

Thanks,
Jay

Hi All,

The issue is not occurring on my end. I think the minimum pool size set to 0 fixed it.
Thanks everyone for the reply.

Thanks,
Jay

Hi Everyone,

I have a follow up question. Does JDBC Connectors automatically disables/closes the connection to the database it calling to? Currently, We have set up a scheduler that calls a database table to get data every 60s. We are curious if the connections created by the JDBC automatically closes.

Are there any flow service that we can invoke to close the connections?

Hoping for your replies

Thanks everyone

By

I think you want to mean connection pool at establishes with database . If yes, then yes , it’s being managed by connetion manager and connection removing depends on “Expirary Timeout” that you have mentioned on connection creation.

Thanks!

Recommend you to create a new thread/post.

Yes, after the service execution, connection to the database will be closed by WmART. Can you share the screen-shot of the JDBC adapter connection and the flow service?

If you doubt that connections are not getting closed, you can query the current db connection from wM on your database server.

Let me know if you have any questions

Hi,

please provide the settings for the Connection Pooling for your connection definition from the lower part of connection settings page.

To avoid creation/closure of connections every a connection is needed, the connections should be pooled.
If there is an idle connection in the pool the service which requests will get this one assigned.
When there is no idle connection in the pool and max pool size has not been reached the DataSourceManager creates a new one and assigns it to the service.
After the service has copleted, the connection will be returned to the pool for further requests.
When the connection is idle for a certain time without being used it will timeout and after that will be closed by DataSourceManager.

Check IS Built-In-Services Guide for WmART services for checking the statistics of the connection pool.

Regards,
Holger

Hi @Holger von Thomsen

Thanks for the response. My settings are as shown below

Connection Type:webMethods Adapter for JDBC Connection
Package Name:testPackage
Connection Properties
Transaction Type:LOCAL_TRANSACTION
DataSource Class:com.microsoft.sqlserver.jdbc.SQLServerDataSource
serverName:
user:
password:
databaseName:
portNumber:
networkProtocol:tcp
Other Properties
Connection Management Properties
Enable Connection Pooling:TRUE
Minimum Pool Size: 0
Maximum Pool Size:10
Pool Increment Size:1
Block Timeout (msec):1000
Expire Timeout (msec):1000
Startup Retry Count:0
Startup Backoff Timeout (sec):10

I think I get it now. When the Expire Timeout reaches the idled connection will be closed.

@Mahesh K. Sreenivas Thanks for your response. I’ll keep that in mind

To everyone who responded, Thanks! it made everything clearer now