· Jan 23

%Executing UPDATE/INSERT SQL. Statement with Parameters - How to send Dates/Timestamps with TO_DATE/TO_TIMESTAMP

Tring to Performing following script in IRIS but do not know how to format Date. Normally, I would use TO_DATE or TO_TIMESTAMP in the actual queue statement below, but since I'm using Prepared Statements, I do not know how to format date in 'values' that is being used in the %Execute. It generates errors. I'm updating an Oracle Database via a SQL JDBC Gateway Connection. Does anyone know how to pass in Dates? 

Books Table definition

    Field Name  Datatype  Column #  Required  Unique  Collation  Hidden  MaxLen  MaxVal  MinVal  Stream  Container  xDBC Type  ReferenceTo  Version Column  Selectivity  Outlier Selectivity  Outlier Value  Average Field Size  HISTOGRAM 
  ID %Library.BigInt 1 Yes Yes   Yes       No   BIGINT   No 1        
  BOOK_NUMBER %Library.Numeric 2 Yes Yes   No       No   NUMERIC   No 1        
  BOOK_NAME %Library.String 3 No No EXACT No 32     No   VARCHAR   No          
  BOOK_TITLE %Library.String 4 No No EXACT No 64     No   VARCHAR   No          
  BOOK_AUTHOR %Library.String 5 No No EXACT No 32     No   VARCHAR   No          
  BOOK_DATE %Library.Date 6 No No   No       No   DATE   No          

r values

"1023","The Truth Hurts Rome","The Truth Hurts Rome","David H Smithly","TO_DATE('12/15/2023','MM/DD/YYYY')"

s Book="TableBooks"
s Book=##class(%SQL.Statement).%New()
s qStatus=Book.%Prepare(queue)
s rset=Book.%Execute(values)
w rset.%SQLCODE


zw rset
rset=6@%SQL.StatementResult  ; <OREF>
+----------------- general information ---------------
|      oref value: 6
|      class name: %SQL.StatementResult
| reference count: 2
+----------------- attribute values ------------------
|     %CurrentResult = ""
|           %Message = "Invalid Dynamic Statement Parameter"
| %OutputColumnCount = 0
|          %ROWCOUNT = ""
|             %ROWID = ""
| %ResultColumnCount = 0
|           %SQLCODE = -400
|     %StatementType = 2
|           %routine = ""
+----------------- swizzled references ---------------
|    (i%%ProcCursor) = ""
|    (r%%ProcCursor) = ""
+--------------- calculated references ---------------
| %StatementTypeName   <Get>

Product version: IRIS 2022.1
$ZV: IRIS for Windows (x86-64) 2022.1.2 (Build 574U) Fri Jan 13 2023 15:00:26 EST
Discussion (5)1
Log in or sign up to continue

Hello Robert - The use of TO_DATE and TO_TIMESTAMP is to not have to know what the specific date format is on the Source Database. If TO_DATE & TO_TIMESTAMP cannot be used, is there another method I could use that does the same thing? I have no issues getting the date; just how to format it and send it so it is not a specific date format that could change.