Stored procedure adapter and dates

, ,
1

I’m trying to call a PL/SQL procedure which takes a Date for input. I’ve failed to get passing dates into my adapter to work.

I’ve used a map step with pub.date:getCurrentDate to get the current date as a java.util.Date. Passing this into the adapter resulted in a ClassCastException, so I changed the adapter input type from java.sql.Date to java.util.Date.

This however results in an ORA-01008: Not all variables bound. The Oracle documentation states that this involves substitution variables, and the adapter shouldn’t be using those at all.

Just to test, I modified the PL/SQL to not take in the date parameter but use sysdate instead. When there was no dates involved everything went fine. Unfortunately, the date is needed…

Does anyone know what I might be doing wrong?

2

Maybe using pub.date:currentDate would work. This gets the date as string, not a Java object. This service accepts a pattern for the date string. Use a pattern that is right for your Oracle instance.

3

Hmm… As my organization has a bunch of Oracle databases, and I don’t think they all use the same date formats, that’s not necessarily the best option. It’s a workaround though.

But I thought the adapter would HAVE to support date parameters, and that I’m using it the wrong way… Would the deprecated pub.db:call or pub.db:execSQL perhaps be better? I’ve not tried these, but I’ve seen there are a lot of posts about them.

4

Oracle is very tricky on what dates it will accept and what format the date is in. The easiest way around this is to use the Oracle to_date function as part of the SQL call. For example the SQL Server query:

Select * from table where dateField > ‘2/13/2003 15:12:00’

In Oracle would be written

Select * from table where dateField > to_date(‘2/13/2003 15:12:00’, ‘DD-Mon-YYYY hh24:mi:ss’)

Hope this helps in some way.

Thomas

5

Actually I think that would be ‘dd/mm/yyyy’ and onwards… ‘dd-mon-yyyy’ expects dashes instead of slashes and textly month (like jan or feb).

That’s what I’d be doing, programming in java. And I expected the adapter to do the same thing, but obviously it’s not. I unfortunately don’t have control over the SQL generated by the adapter. Sending a string to the adapter, I suspect it would put my TO_DATE inside 's and thus avoid the conversion inside the DBMS and fail.

I can make the JDBC call in a java service created myself, if I only knew how to get the connection information stored. If I have a connection org.personell.conn:personell, how do I go about extracting this?

6

I’ve tried all the different ways of putting dates in the adapter now, including putting java.sql.Date, java.util.Date, String into a Date input, and putting java.sql.Timestamp, java.util.Date, String into a Timestamp input, and String into a Varchar input. Nothing works.

7

Hi, Brigt.

Can we take a step back and look at the services you are using and what variables you are passing to the services?

Use F7 to step through your Flow and keep an eye on the “Results” tab.

Be sure to note if the Pipeline objects are object or String types.

Thanks.

8

The kinds of pipeline objects I’ve tried to pass are both kinds. The java.util.Date, java.sql.Date and java.sql.Timestamp are objects (and depicted with a nice watch picture in the results tab), while the string is, naturally, a string.

The services I use are as follows:

  • pub.date:getCurrentDate - returns a java.util.Date. This will be replaced with a date input coming from a published document later.

  • util:utilDateToSqlDate - A very simple java service I made to convert java.util.Date to java.sql.Date.

  • util:utilDateToTimestamp - Ditto, to java.sql.Timestamp.

  • The stored procedure adapter I created, which takes in 5 varchar parameters and 1 date parameter. As mentioned in my previous post, I’ve changed the expected input type for the date parameter in the adapter as I’ve changed the objects I try to pass in.

9

You might be interested in knowing webMethods has admitted the stored procedure adapter does not work with date input parameters.

However, the Custom SQL adapter is a workaround, so creating java services for this purpose is unnecessary.

10

Hey Brigt,

I am also having issues with a web service which inturn calls a stored procedure written for SQL Server. This Stored Proc takes and returns date.
Do you have a JAVA code handy for util:utilDateToSqlDate and util:utilDateToTimestamp which you wrote? Can you upload it here ?
i wanna try something real quick 2morrow morning.

Thanks !