Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Question Unanswered: Question about timestamp

    Guys,

    We are upgrading our Informix databases to Oracle 9i(v2). 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 varchar2(20),
    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 call 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:

    'goLocalDb is the class
    'newid is the new sequence
    'rsConthist is the recordset

    sSQL = "select conthist_sequence.nextval as newid from dual"
    Set rsConthist = goLocalDB.ExecuteLocalSQL(sSQL)
    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
    rsConthist.Close

    The result is I can insert this new row into the table with all data
    updated correctly except the timestamp in column "createtime". I just
    couldn't set the data in the recordset and the data passed from the provider to Oracle for this column is always Null. I am still trying to find the
    solution, in the meantime, does anybody have some experience on the
    this?

    Any input is appreciated.

    Regards,

    Gary

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Gary,

    Sorry to sound so negative, but I think you're well off the track if you believe this should be done in any interface layer.

    Allow the developer to do "new" - it's what they're used to and what their record set components expect. Why? Because it's what's found to be the best trade off between DB side and client side functionality.

    Your ID column should be set in a trigger on the table. Otherwise save yourself ten thousand pounds and run it on MySql.

    Again, sorry to sound so negative, I think your intention is good, but your execution is little off the track.

    Hth
    Bill

  3. #3
    Join Date
    Jul 2003
    Posts
    5

    Exclamation

    Bill,

    Thanks for this.

    Technically I agree with you. It should be done on the database using some PL/SQL procedure.

    Unfortunately, in this real world, I have got 3 weeks to finish the upgrade and the development team has more than 30 applications to modify and a heavy middle tier to replace. We are just running out of time. Certainly I will ask them to put this kind of business logic on database itself for new development in the future.

    I probably will use default on that column to work around. The problem remains on some other tables in which I have a few timestamp columns need to be updated in different time. It's just how the legacy system worked and I was asked to move it not change it within this short period of time, you can tell from the name of one of the columns "sequence", as I told them I would change the name to be something I am more comfortable with, they boss replied " You created that talbe, didn't you? It is not a reversed word then, don't change it at this stage 'cause too many code changes will be involved!".

    Thank you again.

    Regards,

    Gary

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Gary,

    Oh I see the problem.

    It is diffcult when doing a migrate to determine what is migration work, what are absolutely necessary changes, and what are "nice to have's". Your boss is probably right to try to limit how much additional work is done.

    However, there were some other posts on this forum where someone simply could not for love nor money create a trigger on a table with a reserved word column name. Even though the syntax they were using should have worked it simply wouldn't. I don't know if you might find other similar issues.

    I don't know if you know already and sorry if I'm covering stuff you do, but you can create the trigger for your table very quickly as follows...

    CREATE OR REPLACE TRIGGER ContHistInsTrg
    BEFORE INSERT ON ContHist
    FOR EACH ROW
    DECLARE
    tmpVar ContHist.Sequence%type;
    BEGIN
    Select ContHist_Sequence.NextVal into tmpVar from dual;
    :NEW.SEQUENCE := tmpVar;
    /* for timestamps you could also do.... *.
    :NEW.CREATEDATE := SYSDATE;
    EXCEPTION WHEN OTHERS THEN
    -- log to error table
    RAISE;
    END ContHistInsTrg;
    /

    I'm not sure whether it will like the column called sequence though.

    I would also strongly urge you to change the createdate/createtime to a date type field. Again you're probably aware of this. Using a date field you will have no formatting issues (us/uk formats) and date arithmetic is much more efficient in selects etc.

    Personally I would consider these changes to be imperative as part of the migration.

    Once you are all done and settled, I would then recommend you consider changing you client side SQL to use bind variables. They can have quite a large performance effect on Oracle. I don't know the VB syntax but this gives the picture...

    not "insert into table (column1, column2) values ('hello','world')"

    but rather "insert into table (column1,column2) values (:b1,:b2)"
    Sql.SetVariable('b1','hello')
    Sql.SetVariable('b2','world')

    This will improve Oracle performance and also tidies up all of the Sql building and concatenation which occurs throughout your source. I imagine that's one for a rainy day though.

    Regarding the problem with the createdate/createtime - I suspect this is a formatting problem (NLS under Oracle). Use of a bind var will almost certainly correct it.

    Anyway good luck with the migration, knowing a little more about your time constraints etc maybe I can be a little more positive in future :-)

    Cheers
    Bill

  5. #5
    Join Date
    Jul 2003
    Posts
    5

    Question

    Bill,

    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 hope I can get more good suggestions from you in this thread in the future.

    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 as you suggested, but I am not sure whether I can do it with all those beloved methods in ADO or OLEDB. Any suggestions?

    Regards,

    Gary

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Gary,

    Originally posted by Rooty Hill
    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?
    I believe it is do with some of the external Oracle interfaces you can use (external tables). Because they could contain reserved words, Oracle is somewhat flexible with it. Separate to this, not all of the Oracle internals are as flexible as others - hence the trigger compilation problem.
    Originally posted by Rooty Hill
    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...?
    Timestamp is a new Oracle 9 type which complements the DATE type. Date only gives accurracy to one second, timestamp has a higher accuracy (I don't know which, hundredths of a second or possibly more).
    Originally posted by Rooty Hill
    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 as you suggested, but I am not sure whether I can do it with all those beloved methods in ADO or OLEDB. Any suggestions?
    On that I'm afraid I can't help. One way to check though would be to look in V$SQLAREA for two identical queries with different parameters. If there is only one version of it in V$SQLAREA, then it's using bind variables. These can make a big difference to performance, so it's worth trying to find/check on them.

    Hth
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •