Home » Infrastructure » Windows » Using OO40 and DateTime stamps (Oracle 10g, Windows Server 2000 , VB 6)
Using OO40 and DateTime stamps [message #305868] Wed, 12 March 2008 04:45 Go to next message
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
Re: Using OO40 and DateTime stamps [message #305878 is a reply to message #305868] Wed, 12 March 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TIMESTAMP datatypes and not DATE one.

Regards
Michel
Re: Using OO40 and DateTime stamps [message #305890 is a reply to message #305868] Wed, 12 March 2008 05:27 Go to previous messageGo to next message
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 1841 times)
Re: Using OO40 and DateTime stamps [message #305907 is a reply to message #305890] Wed, 12 March 2008 05:52 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
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:
  


Current Time: Thu Mar 28 15:27:54 CDT 2024