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

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F215B79.8A602C3C_at_exxesolutions.com>...
> 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,

Thanks for this.

What I have done is blindly running the converted scripts in Oracle and then fix the problem in my development environment. I fixed all those naming problems except this one. I should have carefully read the doc about reserved words which I had already printed out. No excuse, it is a shame.

When I told the developers that I would change it to "seq", I got strong resistance from them. One of the young guns even complained that this change would get too much development involved. Luckily, I have been developers more than 10 years and when I started to use those RAD stuff like VB, the boy probably was still working for some KFC shop to earn his uni fee. I talked to the development manager and got it through.

The timestamp columns are changed to be dates. They decided to go on to let the front end to update the dates in the database. Technically nothing wrong with it whilst I would rather let the server do it with defaults.

I do have very little time to finish the upgrade from Informix to Oracle 9i2 for our CRM databases. I was explicitly told to make it work first then to think about the tuning things later on. On the other hand, as a fact, I am still learning Oracle stuff. The good thing is I enjoy it. I am also appreciated your kind input, I found your posts are valuable to the others and I hope I can get more good suggestions and instructions from you in this thread.

Here are some subsequential questions I got: 1. Why Oracle lists a word as reserved but still lets users to use it as an object name as normal?
2. I know the difference between a timestamp and a date, according to you experience, what kind of reasons do drive users to use a timestamp when they can handle the data with a date? Time zone, geograhic migration of database,some time-sensitive-industry...? 3. Our developers are used to use ADO or OLEDB to handle the data access. They loved (me too when I was an everyday developer) all those methods they can use in a COM way. You can tell from the code I presented in the first post. I do have talked to the boss about moving business logics into some PL/SQL packages on the server gradually but I don't think it will happen in near future. In the meantime, I still want to do some tuning in the client part (as far as I know, the client part is the starting point of tuning). One thing I can do is to ask them to put bind vaiables in their SQL statements. I am still trying to find out how to do it via Oracle OLEDB Provider. I think I can do it by passing in a completed SQL statement but I am not sure whether I can do it with all those beloved methods in ADO or OLEDB. Any suggestions?

Regards,

Gary Received on Mon Jul 28 2003 - 20:22:28 CDT