OraFAQ Forum: Windows » Using OO40 and DateTime stamps

Home » Infrastructure » Windows » Using OO40 and DateTime stamps (Oracle 10g, Windows Server 2000 , VB 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using OO40 and DateTime stamps [message #305868]

Wed, 12 March 2008 04:45

Vifferrider
Messages: 2
Registered: March 2008
Location: Systems Analyst

Junior Member

Hi, First post Smile

We are porting many of our systems away from MS SQL server to Oracle 10g, and I have been tasked with recording our VB6 programs to work with the new system

We are using the OIP10.TLB

To pass the information from the programs, which stores events as they happen we are using stored procedures on Oracle.

One of the procedure fields store the date and time of the event, down to the millisecond. This is so the integrity of the sequence of events is maintained.

Trouble is that Microsoft's DATE variable only allows DD/MM/YY HH:MM:SS to be stored, no facility for milliseconds.

The procedure works perfectly if I use DATE, but is missing the vital milliseconds, so the sequence goes to pot.

With OraDatabase.Parameters
.Add "d_DateTime", Now(), ORAPARM_INPUT, ORATYPE_DATE
.Add "v_System", sApplication, ORAPARM_INPUT, ORATYPE_VARCHAR2
.Add "v_Events", EV_Event, ORAPARM_INPUT, ORATYPE_VARCHAR2
.Add "v_Status", EV_Status, ORAPARM_INPUT, ORATYPE_VARCHAR2
End With

OraDatabase.ExecuteSQL ("begin JCMS_TRANS_PKG.INS_ENV_EVENTS_PR(:d_DateTime,:v_System,:v_Events,:v_Status);end;")

If I try to create a string and pass this to the procedure

With OraDatabase.Parameters
.Add "d_DateTime", MyTime, ORAPARM_INPUT, ORATYPE_DATE
.Add "v_System", sApplication, ORAPARM_INPUT, ORATYPE_VARCHAR2
.Add "v_Events", EV_Event, ORAPARM_INPUT, ORATYPE_VARCHAR2
.Add "v_Status", EV_Status, ORAPARM_INPUT, ORATYPE_VARCHAR2
End With

OraDatabase.ExecuteSQL ("begin JCMS_TRANS_PKG.INS_ENV_EVENTS_PR(:d_DateTime,:v_System,:v_Events,:v_Status);end;")

it will error " Variable is not a valid date"

Please can you advise how I can pass a valid date time to this procedure using milliseconds?

Thanks in advance

Rich

Report message to a moderator

Re: Using OO40 and DateTime stamps [message #305878 is a reply to message #305868]

Wed, 12 March 2008 05:18

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use TIMESTAMP datatypes and not DATE one.

Regards
Michel

Report message to a moderator

Re: Using OO40 and DateTime stamps [message #305890 is a reply to message #305868]

Wed, 12 March 2008 05:27

Vifferrider
Messages: 2
Registered: March 2008
Location: Systems Analyst

Junior Member

Thanks for the reply

I have tried that [using timestamp] with the variable type being passed as a string and a varient

I get this error

/forum/fa/3998/0/

if I try using DATE it truncates the value back down to dd/mm/yy hh:mm:ss

Rich

Attachment: ora_err.jpg
(Size: 16.81KB, Downloaded 2505 times)

Report message to a moderator

Re: Using OO40 and DateTime stamps [message #305907 is a reply to message #305890]

Wed, 12 March 2008 05:52

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Without code I can't comment an error.

By the way, -20003 error and associated message are custom and Oracle ones.
So search in the code of your procedure MESSAGE_PKG or JCMS_TRANS_PKG

Regards
Michel

Report message to a moderator

Previous Topic:	AS and DB instance on a same Windows Machine
Next Topic:	converting text file data to a table on oracle 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 06:33:28 CST 2024