Re: SQL7-> Oracle Help

Thanks for the very helpful comments. 3 follow up questions if I may....

1. The varbinary datatype in SQL7 just holds arbitrary/unformatted binary data of variable length. You can store values by specify the column value in hex. SQL7 does allow you to convert 4 bytes into an integer and also to do comparison operations on a single byte value extracted from a varbinary data column. This is what I was looking for under ora.
2. Re: timestamps and your "select for update ..." suggestion, I do not see how this would work in our environment. We have multiple users connected via ODBC to the database. Users select stuff from the database into forms on their workstations. At some point in the future they may a) disconnect w/o warning b) change one or more values on the form and send it back c) request some other data. Neither the database or the remote application "know" in advance when an update will be attempted. The problem is when
3. User A selects a record
4. User B selects the same record
5. User B updates the record
6. User A updates the record based on values read at step 1.

The SQL7 timestamp method allows one to easily fail the 2nd update bacause the record timestamp which user A submits will no longer be valid due to the update by user B. If user A were to do a " select for update" in step 1, I assume that B's update in step 3 would fail (or would B fail at step 2 when he attempted a select for update on the same (or overlapping ) record set).

3. Yes an SQL batch is like a PL/SQL block. More reading in the PL/SQL reference leads me to think that I could handle exceptions in an inner block which should allow the enclosing block to proceed.

Thanks again....
Jim

Jerry Gitomer wrote:

> Hi James,
>
> Welcome to the big leagues ;-)
>
> I have inserted my response/comments inline.
>
> James A. Littlefield wrote in message <375670C5.21985016_at_alum.mit.edu>...
> >Moving from NT/SQL7.0 to Linux/Oracle I have encountered a few issues.
> >Perhaps someone can point an Oracle newbie to right solution(s).
> >
> >
> >1. My database has some MS/SQL7 varbinary columns. This seems to map
> >to the raw datatype under Oracle? I would like to apply some
> >contraints to ensure that the values are only within a certain range.
> >SQL7 lets me define contraints which compare the values of a
> >varbinary(1) to constants such as 0x01. I have been able to compare
> >for equality under Oracle using RAWTOHEX() to get a string and compare
> >the string to a constant '01' but this does not work for ranges. What
> >is the best way to do this?
>
> Sorry, I don't know what a MS/SQL7 varbinary is so I can't comment.
>
> >2. SQL7 has an identity datatype which causes each inserted row to get
> >assigned a unique value. The closest thing I have found under Ora is
> >to define a sequence and use values from the sequence. Is this the
> >correct approach.
>
> Use the Oracle sequence. Do bear in mind that anytime a rollback occurs
> there can be a gap in your sequence numbers. In other words they will
> provide unique identifiers , but don't use them for things like check
> numbers (your auditors will be very unhappy).
>
> >3. To guard against concurrent read/update errors MS/SQL7 suggests
> >using adding a column type of timestamp. The timestamp column gets
> >updated automatically whenever anyone does a modification to the
> >column. Applications can tell if update collisions have occurred by
> >adding a "where timestamp = " clause to the table update statements.
> >How does this work under Oracle
>
> You can do SELECT ... FOR UPDATE. This will lock the record and prevent
> any other program from either deleting or updating the record until either a
> commit or a rollback is performed. Note that this will not stop another
> program from reading the record.
>
> >4. MS/SQL7 has a database option (xact_abort) which controls behavior
> >during execution of a batch. If xact_abort is on, the entire batch is
> >rolled back if any of its statements fail. If xact_abort is off, only
> >the offending statement is rolled back but execution of the rest of the
> >batch continues. What option(s) control this behavior under Ora.
>
> OK, what is an MS/SQL7 batch? If it is equivalent to an Oracle stored
> procedure or SQL script consisting of more than one SQL statement it is
> possible to insert explicit commits anyplace you want. The default in
> Oracle is 1) Commit anytime the session issues a DDL statement. 2) commit
> anytime the session issues an explicit commit statement. 3) commit when the
> session terminates in an orderly manner. 4) rollback in the event of an
> error. 5) rollback if an explicit rollback is issued by the session 6)
> rollback if the session terminates in a disorderly manner. Your best bet is
> to build a small test instance and do some experimentation since the scope
> of a transaction (the commands issued by a session between two commits or
> between a commit and a rollback) is sometimes obscure and best learned by
> making stupid mistakes before your developers and users find out you are not
> omnipotent (I do assume you are going to be the DBA).
>
> regards
>
> Jerry Gitomer
>
> >
> >Thanks for any suggestions/pointers.
> >
> >Jim
> >
> >
Received on Fri Jun 04 1999 - 06:06:29 CDT