I can insert a new record through the SMP but not through code

Hi,

The following sql statement is working through the SMP but couldn't make it work through the code.

The table is a linked table to oracle db.

Tried to change the date property in the linked table class from %Date to %String and also tried using %SQL.Statement and %Library.ResultSet

this work through SMP:

INSERT INTO PACKAGE.SOME_TABLE_NAME

(EFFECTIVE_START_DATE,MORE,DATA,HERE)

VALUES (to_date ('2018-04-01','yyyy-mm-dd'),123,456,789)

In the code i'm using '?' in the statement for the parameters.

So in the code it's go something like this (where the to_date is in the sql statement):

Set statement="INSERT INTO PACKAGE.SOME_TABLE_NAME (EFFECTIVE_START_DATE,MORE,DATA,HERE) VALUES (to_date (?,'yyyy-mm-dd'),?,?,?)"

s obj=##class(%SQL.Statement).%New()

s status=obj.%Prepare(statement)

i status s result=obj.%Execute($zd($zdh(somedateYYYYMMDD,8),3),par2,par3,par4)

or this one (where the to_date is in the execute)

Set statement="INSERT INTO PACKAGE.SOME_TABLE_NAME (EFFECTIVE_START_DATE,MORE,DATA,HERE) VALUES (?,?,?,?)"

s obj=##class(%SQL.Statement).%New()

s status=obj.%Prepare(statement)

i status s result=obj.%Execute("to_date ('"_$zd($zdh(somedateYYYYMMDD,8),3)_"','yyyy-mm-dd')",par2,par3,par4)

As you can see i tried putting the to_date in the Prepare statement and it didn't work and also in the execute (in the parameters) it didn't work.