Insert a new row into a talbe using Addnew method via Oracle OLEDB Provider

From: Gary <rooty_hill2002_at_yahoo.com.au>
Date: 22 Jul 2003 19:39:28 -0700
Message-ID: <171bd226.0307221826.10e2d774@posting.google.com>

Guys,

We are upgrading our Informix and MSSQL databases to Oracle 9i (HP-UX 9.2.0.1.0). I was the DBA (Informix/MSSQL) and I have been "Oracle DBA" for 3 months.

I am writing a Oracle database connection layer (a VB class using Oracle OLEDB provider) for my vb developers. I have a little problem here, see whether you have some idea about it.

Say I have table called "conthist", it has columns createdate Date,
createtime timestamp(6),
createop varchar2(20),
contresult varchar2(40,
contpurpos varchar2(40),

contchan varchar2(40),
personid varchar2(10),
contprod varchar2(40),

sequence not null number

I have a sequence called conthist_sequence used for column "sequence".

Now, the developers want to insert a new row into this table, I have asked them to use this statement:

    sSQL = "insert into conthist (createdate,createtime,createop,"     sSQL = sSQL & "contresult,contpurpos,contchan,personid,contprod,sequence)
"

    sSQL = sSQL & "values(to_date('" & Date & "', 'dd/mm/yyyy'), "     sSQL = sSQL & "to_date('" & Format(Now, "dd/mm/yyyy hh:mm:ss") &
"', 'dd/mm/yyyy hh24.mi.ss'), "

    sSQL = sSQL & "'GZ', 'result for oracle','purpose for oracle', "     sSQL = sSQL & "'chan for oracle','0101785451', 'prod for oracle',
"

    sSQL = sSQL & "conthist_sequence.nextval)"

and they confirm it works fine.

The developers used to use "addnew" method of recordset object to insert a new row into a table and ask whether they can keep doing that. Before I can say yes to them, I want to make sure I can do it by myself. So I use .Nextval to get the new sequence number and then want to insert the new row by doing the following:

sSQL = "select conthist_sequence.nextval as newid from dual" Set rsConthist = goLocalDB.ExecuteLocalSQL(sSQL)

'goLocalDb is the class
'rsConthist is the recordset

newid = rsConthist!newid
sSQL = "select * from conthist where sequence=" & newid Set rsConthist = goLocalDB.ExecuteLocalSQL(sSQL)

    rsConthist.AddNew

    rsConthist("createdate") = Format(Date, "dd/mm/yyyy")
    rsConthist("createtime") = Format(Now, "dd/mm/yyyy "hh:mm:ss")
    rsConthist("createop") = "GZ"
    rsConthist("contresult") = "result for Oracle"
    rsConthist("contpurpos") = "purpose for Oracle"
    rsConthist("contchan") = "chan for Oracle"
    rsConthist("personid") = "0101785450"
    rsConthist("contprod") = "prod for Oracle"
    rsConthist("sequence") = newid

    rsConthist.Update

The result is I can insert a new row into the table with all data updated correctly except the timestamp in "createtime". I just couldn't set the data for this column in the recordset and the data passed to Oracle is always Null. Looks like the provider can implicitly convert a string to a date ( if it is a date_like_string ,:-) ) but it will reject any string for a timestemp column. Just curious, any solution for this?

I can ask them to use "insert into" only anyway, I don't have to say yes. BTW, the reason they ask to keep the existing code is, you know, they are just lazzzzzy.

Any input is appreciated.

Regards,

Gary Received on Tue Jul 22 2003 - 21:39:28 CDT