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

Gary wrote:

> 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

A few comments based on your posting:

1. In Oracle you must SELECT INTO
2. You are overcomplicating your insert ... just recreate the column CREATETIME as follows:

CREATE TABLE test (
createtime DATE DEFAULT SYSDATE);

All Oracle date fields include the time, there is no alternative. With DEFAULT you don't submit anything as part of your insert ... the database automatically fills in the value.

Also I'd strongly urge you to aquaint yourself with the following query:

SELECT keyword
FROM v_$reserved_words;

A column named SEQUENCE is a disaster waiting to happen.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)