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.
The change from %Date to %String influences image of your target table
and may affect implicit ODBC Data transmission that converts eg. +$h to YYY-MM-DD format !
once your data is a %Date again this should do it:
...... VALUES (?,?,?,?)
%Execute($zdh(somedateYYYYMMDD,8),par2,par3,par4)
If you test in SMP you have to check if you are in ODBC, Logical or Display mode.
with TO_DATE() you just overrule this as string are the same in any mode.
For ODBC the date is always 2018-04-25
For Display it's 04/23/2018 or 23.04.2018 depending on your local setting
For Logial it is always 64761 (+$h)
Still not working in the code, at the SMP it worked in every mode Display/Logical/ODBC. isn't the mode affect only on select statements? At the code i set the property back to %Date, and the sql statement and execute without the to_date, at the execute just converted to $h as you mentioned and i'm getting error [Oracle][ODBC][Ora]ORA-01861: literal does not match format string.
ok
SELECT top 3 EFFECTIVE_START_DATE PACKAGE.SOME_TABLE_NAME
did you try '2018-04-23' instead of $h ?
may show the expected default.
No problem in selecting from the table
Problems only when trying to insert new data to the table.
I tried yyyy-mm-dd and $h (i tried $h only when it was defined as %Date letting the Cache auto translate using %Date.LogicalToOdbc method)
At the Caché side it was generated as %Date using the link table wizard, through the SMP it worked with to_date when it was %Date and worked also after i changed it to %String.
Through terminal couldn't make it work no mater what i tried, not when it is %Date and using $h without to_date nor when it is %String and translating the date to yyyy-mm-dd with or without to_date (at prepare or at execute).
I also tried using ##class(Package.SomeClassName).%New() and %Save() and it also return ORA-01861 error ($h when %Date and yyyy-mm-dd when %String).
Also %String with %New/Save with "to_string ('"_FormattedDate_"','yyyy-mm-dd')" as the field value returned ORA-01858 error.
Don't know the table/field definition/format at the oracle db - hope to get this info soon.
At the oracle db - the field type is DATE and no special format is specified for the field.
SQL shell is working (using to_date no matter if it's %Date or %String)
So what the sql shell and SMP are doing that i'm not doing in the code?
Oops. we overlapped. pls. take a look to status
I'm checking the status of prepare and there's no problem during the prepare, it's the execute that return the error.
copy+paste from the example:
status=obj.%Prepare(statement)
i status s result=obj.%Execute($zd($zdh(somedateYYYYMMDD,8),3),par2,par3,par4)
Using TO_DATE (?,'YYYYMMDD') in prepare
Executing as you said - working in the terminal, and also in the code, finally
Thank you very much
WOW! good luck.
and let me have your Accepted
Through terminal couldn't make it work no mater what i tried, not when it is %Date and using $h without to_date nor when it is %String and translating the date to yyyy-mm-dd with or without to_date (at prepare or at execute).
Did you also try SQL shell from terminal ? (it should act like SMP)
SAMPLES>Do $system.SQL.Shell()
SAMPLES>>INSERT INTO .......
Also: have you ever checked status ? to see if %Prepare was OK ?
after:
do $system.OBJ.DisplayError(status)
It's hidden in your example
doc for ORA-01861
describes the TO_Date issue very detailed
https://www.techonthenet.com/oracle/errors/ora01861.php
Your problem seems to be strictly related that the value you use and its description in TO_DATE don't match.
So TO_DATE(?,'YYYYMMDD') in Prepare
should work with
s result=obj.%Execute(20180425,par2,par3,par4)
It's a little bit less transformations
What is a definition of oracle table, what format is EFFECTIVE_START_DATE?
I understand that on Caché side it was generated as %Date but you changed it to %String? It then worked in SMP and not in Terminal?
And if you changed it back to %Date, it doesn't work in SMP?