9.5 webMethods Select JDBC Adapter Issue with the TIMESTAMP

[Shyam_Kukunuri]

Hi All,

I am facing a issue with the Select JDBC adapter in 9.5 webMethods version.
While in 8.x version i am able to do a select along with the TimeStamp however, i deployed the same code in the 9 version i am trying to do a select on the same table and the result is only the date. See the results below:

8.x Result: 2014-06-07 22:30:21.0
9.x Result: 2014-06-07

But i need the TimeStamp as 8.x, Please advise and help me to resolve this issue…

9.x version fix Updates: IS_9.5_SP1_Core_Fix3

Jar Files in : ojdbc14.jar, ojdbc6.jar and ojdbc7.jar

Thanks in advance…

[Amit_Gahlot2 (Amit Gahlot)]

Hi Shyam,

Kindly check in adapter service regarding the “JDBC Type”, it should be timestamp in order to meet your requirement.

Why it was working in 8.x: Kindly check at which JDBC fix level you were. as it used to fetch timestamp information as well even when “JDBC Type” was “Date”. It was fixed in JDBC fix 42, extracts of the same :

JDB-1003 (JDBC_6.5_Fix42)
webMethods JDBC Adapter services erroneously show the timestamp
when retrieving rows from a database table with a DATE Column, if
the Output Field Type is set to java.lang.String.

Regards,
Amit Gahlot

[Shyam_Kukunuri]

Hi Amit,

Thanks for the reply.

We applied JDBC_6.5_Fix47 fix to JDBC.

The outPut Field type is java.lang.string still i am not able to get the data type as TimeStamp in the select adapter.

Data Base table desc.

Name Null Type

---

SERIAL_DATE NOT NULL DATE

[Mahesh_K_Sreenivas]

Try to reload the values from the adapter manually on the adapter service.

[Shyam_Kukunuri]

Hi Mahesh,

Thanks for the response. Reloaded still no luck…

Is this is a product bug ?
Is any other fix required for this issue…

Thanks in advance…

Regards,
Shyam

[Mahesh_K_Sreenivas]

Thats strange

Meanwhile raise a ticket with SAG global support…

[rmg]

Can you rollback the fix47 and try to prove it’s not a fix issue or new issue?

Are you having this issue since after applying the fix…if yes then raise a ticket ASAP and share the updates in the thread.

HTH,
RMG

[Amit_Gahlot2 (Amit Gahlot)]

Hi Shyam,

Kindly change the data type of “SERIAL_DATE” to timestamp in database and then re-load the adapter service & try.

Regards,
Amit Gahlot

[rmg]

If it’s working before the fix does t make sense to change the type in the DB or new FIX not equivalent to the existing SERIAL_DATE ? But to me Timestamp make sense as long it works as expected.

HTH,
RMG

[Shyam_Kukunuri]

Hi rmg,

The issue with the timestamp is after and before the JDBC fix.
I raised a request to the SAG, will post the updates once this issue is addressed by the provider.

Aside to Amit,

I strongly believe that, there shouldn’t be any changes for the other teams for our product upgrade.
However, i tried changing the data type to TimeStamp in the DB, and it is working fine.

I will keep posted the updates. Thanks to every one.

Regards,
Shyam

[Amit_Gahlot2 (Amit Gahlot)]

Hi Shyam,

I do agree there should not be impact on your partner DB due to webMethods upgrade.

But to fetch the TimeStamp information , you need to have the data type as time-stamp in DB. Before JDBC Fix 42 eventhough it is DATE type in DB , but it used to reflect timestamp information as well while fetching the data using adapter service and that was wrong. And the same has been fixed as part of FIX 42.

So while designing itself , you needed to have SERIAL_DATE as time-stamp type in DB. It is just correcting something which was working fine till date due to bug in JDBC adapter .

Still lets wait for SAG Support also to put their comments. Kindly share their findings as well in this forum.

Regards,
Amit Gahlot

[Shyam_Kukunuri]

Hi All,

Finally the issue is resolved. The SAG team has given us a extended setting property, please find below.

watt.adapter.JDBC.DateWithTimestampAndMilliseconds
watt.adapter.JDBC.DateWithTimestamp

HTH,

Thanks ,
Shyam

[Mahesh_K_Sreenivas]

Happy to know Thanks for updating the resolution…

I am just wondering why this setting is not documented in JDBC adapter guide… Are you able to find the same in PDF?

Can you share the key value pair of the below setting: (is this the same you provided)

watt.adapter.JDBC.DateWithTimestampAndMilliseconds=true
watt.adapter.JDBC.DateWithTimestamp=true

[Shyam_Kukunuri]

Hi Experts,

I have a requirement, map the values to a document from a Struct data type from a JDBC adapter.
It is a store procedure from where the output will be a Struct data type.
Can some one help me how can i proceed with coding.
Do i need to use any java service ?

webMethods version 9.5

[Sachin_Hejaji]

We are having a output JDBC type as Struct. I have created a stored procedure when im running it is failing with below exception.

The latest fix is JDBC_6.5_Fix50 fix. can you please suggest the fix available or any work around for a similar issue

Launch started: 2015-11-19 13:15:53.831
Configuration name: StoredProc_Main_Customer
Configuration location: C:/Users/Krishna Gokaraju/workspace97/.metadata/.plugins/org.eclipse.debug.core/.launches/StoredProc_Main_Customer.launch

Could not run ‘StoredProc_Main_Customer’
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service Default.Krishna:StoredProc_Main_Customer.
[ADA.1.316] Cannot execute the SQL statement "call XX_CI_PUB_ITO_074A_PKG.MAIN_CUSTOMER (?, ?, ?, ?, ?, ?, ?, ?) ". "
(63000/3115) ORA-03115: unsupported network datatype or representation
"
ORA-03115: unsupported network datatype or representation

[Mahesh_K_Sreenivas]

@sachin hejaji

We are having a output JDBC type as Struct. I have created a stored procedure when im running it is failing with below exception.

The latest fix is JDBC_6.5_Fix50 fix. can you please suggest the fix available or any work around for a similar issue

Launch started: 2015-11-19 13:15:53.831
Configuration name: StoredProc_Main_Customer
Configuration location: C:/Users/Krishna Gokaraju/workspace97/.metadata/.plugins/org.eclipse.debug.core/.launches/StoredProc_Main_Customer.launch

Could not run ‘StoredProc_Main_Customer’
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service Default.Krishna:StoredProc_Main_Customer.
[ADA.1.316] Cannot execute the SQL statement "call XX_CI_PUB_ITO_074A_PKG.MAIN_CUSTOMER (?, ?, ?, ?, ?, ?, ?, ?) ". "
(63000/3115) ORA-03115: unsupported network datatype or representation
"
ORA-03115: unsupported network datatype or representation

Check the jar and have the right one.

[Holger_von_Thomsen]

Hi,

can you share the configuration of the JDBC-Connection?

Latest Fix available is JDBC_6.5_Fix51.

Regards,
Holger

[Krishna_Gokaraju1 (Krishna Gokaraju)]

I have attached the adapter service screenshot.
We are using ojdbc6.jar.

[MR_as173d]

Krishna --Please verify your database version use

select * from v$version;

Also, try to directly call the SP from DB and see how is the response.

Because sometimes if the DB doesn’t support the specific datatype then you will encounter this issue.

Thanks,

[rmg]

Yes these were the things you can try and check with your DBA help too:

try to directly call the SP from DB and see how is the response.

if the DB doesn’t support the specific datatype then you will encounter this issue.

If possible try to change the data type to strut compatible one and see if that make’s a difference.

HTH,
RMG