JDBC DATE conversion Issue

[Former Member]

Hi All,

I am working on a Proxy to JDBC (Async to Sync) scenario in this I am passing a sender field to target and retrieving the data from data base as response

Created the Request structure as below

Statement

   Action

     Aceess

      f1

      f2

      f3

     f4

    Key

     f1

      compareOperation

     f2

      compareOperation

Mapped the source field to  f1 under key node and maintained the compare Operation as EQ and where the f2 field is the date,I have mapped it with constant( Value is GET DATE ()-90) and maintained the compare operation as GTEQ.

during run time getting issue at mapping as unable to convert the date,Could any one please help me with this.

Thanks & Regards,

AK

[Former Member · Accepted solution]

Hello AK,

You need to define of "hasQuot" attribute with value "NO" for f2 under key. GETDATE() is database function and it should not be treated as string during the execution.

You can refer the blog below for more information on this:

Alternatively, you can use following SQL_QUERY structure with creating the entire pl/sql statement and passing it into access element.

<root>

  <StatementName>

  <anyName action=” SQL_QUERY”>

  <access>select f1, f2, f3, f4 from xyz where f1 = getdate() - 90</access>

  </anyName >

  </StatementName>

</root>

More information on this second approach is available at below link:

Defining an SQL_QUERY Statement - Advanced Adapter Engine - SAP Library

[Former Member · Accepted solution]

Hi Shah,

I have tried the alternate one I have passed the sql query to access to get the data but getting error at where clause as the values in '$' are not processing.

Getting below exception

Unable to execute statement for table or stored procedure. 'xyz' (Structure 'Statement') due to com.microsoft.sqlserver.jdbc.SQLServerException: Invalid pseudocolumn "$f1$'

<root>

  <StatementName>

  <anyName action=” SQL_QUERY”>

  <access>select f1, f2, f3 and f4 from xyz where f1 = '$f1$' and f2 = getdate() - 90</access>

  </anyName >

  </StatementName>

</root>

[Former Member · Accepted solution]

Hello AK,

Two Things:

1. Need to update the query with proper table name instead of xyz which was given as an example.

2. Need to use string concatenation function to incorporate the value of f1 from the source. '$f1$' won't work.

[Former Member · Accepted solution]

Hi Shah,

I have changed the table name while posting and passing the correct table in the query and i didn't understand your second point,could you please tell me it more clearly.

Please correct me if i am passing incorrect query select f1, f2, f3 ,f4 from xyz where f1 = '$f1$' and f2 = getdate() - 90.

[Former Member · Accepted solution]

Hi AK,

Second point is about doing string concatenation like below:

String1 = "SELECT f1,f2,f3,f4 from xyz where f1 = '"

f1 is from source field

String2 = "' and f2 = getdate() - 90'"

With this the access field will have entire select statement with source field value. The use of single quote (') in String1 and String2 will depend on value of f1. Single quote is required if the value of f1 is string and it's not required if it's numeric.

Better you can execute the query in database first and then place it in mapping.

[Former Member · Accepted solution]

Hi Shah,

Thanks i will try it and let you know the result, thanks a lot for your inputs

[iaki_vila]

Hi Ak,

You should share the exact exception and the data with your are trying to do it.

You should use the standard functions currentDate and dateTrans. Check a few examples:

Regards.