store NULL date in Oracle DB using CAPI.EXEC 1

[omarben]

Hi,

I would like to know if it's possible to store a NULL date into a table (Oracle database) using CAPI.EXEC because I have tried doing these instructions but none of them worked for me knowing that it's allowed to store null date in the table.

if(myJsonData.eventDate.value != ')
[indent]myAssoc.eventDate = Date.StringToDate(myJsonData.eventDate.value, "%d/%m/%Y")[/indent]
else
[indent]myAssoc.eventDate = 'NULL'[/indent]
end

if(myJsonData.eventDate.value != ')
[indent]myAssoc.eventDate = Date.StringToDate(myJsonData.eventDate.value, "%d/%m/%Y")[/indent]
else
[indent]myAssoc.eventDate = '(NULL)'[/indent]
end

if(myJsonData.eventDate.value != ')
[indent]myAssoc.eventDate = Date.StringToDate(myJsonData.eventDate.value, "%d/%m/%Y")[/indent]
else
[indent]myAssoc.eventDate = '[/indent]
end

I'm wondernig if anyone faced this problem before and if there is any workaround

Omar.

 

[appnair]

I think when you want to nullify a date your sql statement will be something like this

 sql=Str.Format("update D set DD=(%1)",'NULL')
CAPI.Exec(conn,sql)
the difference is the keyword null is being passed to oracle literally and not as a bind

whereas when you are doing a bind for date it may be

 updSql=Str.Format("update D set DD=(:A1)")
CAPI.Exec(conn,sql,date.Now())
the difference is the date is being passed to oracle  as a bind param

also by the way the new CSIDE based oscript debugger has a better SQL string formatting command soit may be much more elegant than using Str.Format



Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[omarben]

First i would like to thank you for your fast answer,

Actually I am passing my date as a bind parameter but instead of using date.Now() I would like to pass a the date that the user types it in the UI if he puts a date it works fine but if he leave the date field empty i want to store a null value in my DB which not working for me right now.

My SQL statement is

String sqlStatement = 'INSERT INTO MY_TABLE (FIRST_PARAM, SECOND_PARAM, EVENT_DATE) '
sqlStatement += 'VALUES(:A1, :A2, :A3)'
CAPI.Exec(dbConnect,sqlStatement ,[indent]myAssoc.firstParam,\[/indent]
[indent]myAssoc.secondParam,\[/indent]
[indent]myAssoc.eventDate)[/indent]

and myAssoc.eventDate contains 'NULL'


Omar.

 

[appnair]

I doubt I know the answer.However since you know oscript I will say look in this script in your livelink indexobject:indexobjcet root:verifypkg.AddEntry you will see OT programmers
adding a NULL like

			stmt = "INSERT INTO DTreeExtractorVerify ( DataID, VersionNum, New, CorrectionAttempted, Retries, LastRetry, SubType, Status, ModifyDate, CreateDate, PartitionName, PartitionMode, OwnerID, Name, IntegrityError, ContentStatus, MetadataErrors, OTObject )" +\ 
						" VALUES ( :A1, :A2, 1, 0, 0, NULL, :A3, :A4, :A5, :A6, :A7, :A8, :A9, :A10, :A11, :A12, :A13, :A14 )"

Once you specify there are three parameter replacements Oracle will expect 3 bind params and null is not a keyword in oscript.If you look in the dsbscripts that create tables etc for oracle and sql you will see a lot of sql handling.

in your case all one would do is build a smart sql

 if myAssoc.eventDate != null
 sqlStatement = 'INSERT INTO MY_TABLE (FIRST_PARAM, SECOND_PARAM, EVENT_DATE) '
sqlStatement += 'VALUES(:A1, :A2, :A3)'
else
sqlStatement = 'INSERT INTO MY_TABLE (FIRST_PARAM, SECOND_PARAM, NULL) '
sqlStatement += 'VALUES(:A1, :A2, )'
end

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[omarben]

Thank you for your help I built a smart SQL as you said and it's working just the way I was expecting it to work Thank you for your help

Omar.

 

[appnair]

great it helped you like I said I didnt know how to skin this easily so I gave you the suggestion

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in