9.5 webMethods Select JDBC Adapter Issue with the TIMESTAMP

Shyam_Kukunuri · June 23, 2014, 7:08am

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 · June 23, 2014, 8:18am

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 · June 23, 2014, 8:38am

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 · June 23, 2014, 8:49am

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

Shyam_Kukunuri · June 23, 2014, 8:59am

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 · June 23, 2014, 10:39am

Thats strange

Meanwhile raise a ticket with SAG global support…

rmg · June 23, 2014, 3:59pm

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 · June 23, 2014, 4:17pm

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 · June 23, 2014, 5:52pm

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 · June 24, 2014, 6:37am

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 · June 24, 2014, 7:41am

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 · June 26, 2014, 4:31am

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 · June 26, 2014, 4:41am

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 · July 29, 2015, 11:42am

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 · November 23, 2015, 6:21pm

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 · November 23, 2015, 6:46pm

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 · November 24, 2015, 11:21am

Hi,

can you share the configuration of the JDBC-Connection?

Latest Fix available is JDBC_6.5_Fix51.

Regards,
Holger

Krishna_Gokaraju1 · November 24, 2015, 7:28pm

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

MR_as173d · November 24, 2015, 8:25pm

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 · November 24, 2015, 10:49pm

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