Re: Getting date/time fields from Oracle with ADO

From: Cesar Ronchese (ronchese_at_smlinfo.com.br)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 17:53:44 -0300

Yeah, when I 've used the SQL Plus Work***, the data was valid. The error
only occur when in ADO recordset.

"Douglas Marquardt" <no_spam@dummy.com> wrote in message
news:O$F7EahBEHA.3204@TK2MSFTNGP10.phx.gbl...
Hi Cesar:

Well... I can't see anything wrong with it -- looks to me like it
should work.

The only other thing I can think of is that the field actually contains
valid data?

In any case, I ran this code against my oracle database and it worked fine:

'Created the table
CREATE TABLE "GROUPDOC2" ("GROUPDOC_ID" NUMBER(38) NOT NULL,
"GROUPDOC_NAME" VARCHAR2(70 byte) NOT NULL,
"GROUPDOC_DETAILS" VARCHAR2(200 byte), "GROUPDOC_INSERTDATE"
DATE NOT NULL, "GROUPDOC_UPDATEDATE" DATE NOT NULL,
CONSTRAINT "PK_GROUPDOC2" PRIMARY KEY("GROUPDOC_ID")
USING INDEX)

'Added a record
INSERT INTO GROUPDOC2 VALUES(1,'Test 3',NULL,'2004-03-09 12:34','2004-03-09
12:36')

'Returned the record (and was able to access all fields no problem)
SELECT * FROM GROUPDOC2

'Deleted the table
DROP TABLE GROUPDOC2

Doug.

"Cesar Ronchese" <ronchese@smlinfo.com.br> wrote in message
news:%23bKdEsgBEHA.2404@TK2MSFTNGP11.phx.gbl...
> I've created using SQL. I've searched by the Timestamp datatype, and I
> cannot found it in Oracle Enterprise Manager.
>
> The DDL generated from my table (Oracle sintax) is below:
>
>
> CREATE TABLE "SA"."GROUPDOC" ("GROUPDOC_ID" NUMBER(38) NOT NULL,
> "GROUPDOC_NAME" VARCHAR2(70 byte) NOT NULL,
> "GROUPDOC_DETAILS" VARCHAR2(200 byte), "GROUPDOC_INSERTDATE"
> DATE NOT NULL, "GROUPDOC_UPDATEDATE" DATE NOT NULL,
> CONSTRAINT "PK_GROUPDOC" PRIMARY KEY("GROUPDOC_ID")
> USING INDEX
> TABLESPACE "TESTE_DBCONVERGE"
> STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
> 2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
>
> TABLESPACE "TESTE_DBCONVERGE" PCTFREE 10 PCTUSED 0 INITRANS 1
> MAXTRANS 255
> STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
> 2147483645 PCTINCREASE 0)
> NOLOGGING
>
>
>
> And the SQL generated from SQL Server (Visio sintax) is below:
>
> -- Create new table GROUPDOC.
> -- GROUPDOC : Table of GROUPDOC
> create table GROUPDOC (
> GROUPDOC_ID NUMBER(38,0) not null,
> GROUPDOC_NAME VARCHAR2(70) not null,
> GROUPDOC_DETAILS VARCHAR2(200) null,
> GROUPDOC_INSERTDATE DATE not null,
> GROUPDOC_UPDATEDATE DATE not null, constraint PK_GROUPDOC primary key
> (GROUPDOC_ID) );
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "Douglas Marquardt" <no_spam@dummy.com> wrote in message
> news:OaqqvngBEHA.624@TK2MSFTNGP10.phx.gbl...
> Hi Cesar:
>
> Yes, ado will report 135 for a date field in Oracle.
>
> The reason I asked, is because I was having the same problem
> and I discovered that my field was set to timestamp; when I
> changed it to data the error went away.
>
> Do you create the table using SQL (i.e. CREAT TABLE ..etc)
> or did you do it manually in the OEM?
>
>
> Doug.
>
> "Cesar Ronchese" <ronchese@smlinfo.com.br> wrote in message
> news:uyIx$ggBEHA.392@TK2MSFTNGP12.phx.gbl...
> > The Oracle Enterprise Manager is showing DATE, but ADO shows datatype
135
> > (TimeStamp).
> >
> >
> > "Douglas Marquardt" <no_spam@dummy.com> wrote in message
> > news:Oi8aNfgBEHA.2768@tk2msftngp13.phx.gbl...
> > Hi Cesar:
> >
> > Can you verify that the field in Oracle was created using the
> > "DATE" datatype and not "TIMESTAMP"?
> >
> >
> > Doug.
> >
> > "Cesar Ronchese" <ronchese@smlinfo.com.br> wrote in message
> > news:%23Qf2$ZgBEHA.1140@TK2MSFTNGP10.phx.gbl...
> > > Hello, guys!
> > >
> > > I'm migrating from SQLServer (7) to Oracle (9.i release 2), so my
> software
> > > can support multi-databases. In SQL Server, all works fine.
> > >
> > > However, testing my programs in Oracle, I got a problem with fiels in
> Date
> > > datatype, when that field use date/time togheter.
> > >
> > > See the following sample:
> > >
> > > Dim cnn As New Connection
> > > Dim rs As New Recordset
> > >
> > > 1 cnn.Open "Provider=MSDAORA.1;Password=PWD;User ID=USER;Data
> > > Source=NAME;Persist Security Info=True"
> > > 2 rs.Open "select doctype_insertdate from doctype", cnn
> > > 3 Debug.Print rs("doctype_insertdate").Value
> > >
> > >
> > >
> > > The error occurs when I try get the value in line 3, above. The
message
> > is:
> > >
> > > Run-time error '-2147217887 (80040e21)':
> > > Mulitple-step OLE DB operation generated errors. Check each OLE DB
> status
> > > value, if available. No work was done.
> > >
> > >
> > > If I remove the time from field (directly in database), the code above
> > work.
> > > But, I need to get the date and time from that field.
> > >
> > > Anyone have a solution for this problem?
> > >
> > > Tks in advance!
> > >
> > > []'s
> > > Cesar
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>