0
290

Written by

James Casazza
Solutions Architect at FIS Global
Follow
Question James Casazza · Jan 23, 2024

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

#InterSystems IRIS #JDBC #SQL

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          

s queue="INSERT INTO DWHGDS.BOOKS (BOOK_NUMBER, BOOK_NAME, BOOK_TITLE, BOOK_AUTHOR, BOOK_DATE) VALUES (?,?,?,?,?)"
 
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

-400

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)0
Log in or sign up to continue

Comments

Robert Cemper · Jan 23, 2024

"TO_DATE('12/15/2023','MM/DD/YYYY')"  is an SQL function call not a value
could you try straight ODBC format "2023-12-15"  ?

Converting and using $H date format might be a different option

0
James Casazza  Jan 23, 2024 to Robert Cemper

I could, but I'd rather have the date format work for any environment and not hardcoded. I understand TO_DATE & TO_TIMESTAMP is an SQL Function but thought there might be something else to perform this functionality here.

0
Robert Cemper  Jan 23, 2024 to James Casazza
  1. find the expected format in test
  2. where do get the date from ?
    if it's from IRIS functions $ZDATE() or $ZDATEH() convert it in the required format
0
James Casazza  Jan 24, 2024 to Robert Cemper

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. 

0