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

Question

Question

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	Stream	xDBC Type	Version Column	Selectivity
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

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

Vitaliy Serdtsev · Jan 24

Take a look at %SelectMode Property

1 0

score 0 · Answer 1 · 2024-01-23T17:36:42-05:00

"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

score 0 · Answer 2 · 2024-01-23T17:43:36-05:00

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.

score 0 · Answer 3 · 2024-01-23T17:55:26-05:00

find the expected format in test
where do get the date from ? if it's from IRIS functions $ZDATE() or $ZDATEH() convert it in the required format

score 0 · Answer 4 · 2024-01-24T08:51:01-05:00

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.