ROWID in Sybase ?
Sunil Gaur
Hi everyone, I wonder if you could help me on the following -
I am currently writing a SQL driver for our product
which will be a generic driver for the following databases :
INFORMIX, ORACLE and INGRESS and SYBASE.
Now I know that you can use the "ROWID" in your select statement to select
a particular row in INFORMIX /ORACLE and TID in INGRES but I am not sure
about SYBASE.
Do you know if there is such an row ID exists for SYBASE ?
I have looked at that manuals but no joy.
Thanks in advance.
------------------------------------------------------------------------------
Sunil Gaur at McDonnell Information Services,
Tele : +44 (0)442 272093 (Direct line), Fax. +44 (0)442 234443
Internet : sg...@uk.mdis.com
------------------------------------------------------------------------------
j...@trwlasd.com
>
> Hi everyone, I wonder if you could help me on the following -
>
> I am currently writing a SQL driver for our product
> which will be a generic driver for the following databases :
> INFORMIX, ORACLE and INGRESS and SYBASE.
>
> Now I know that you can use the "ROWID" in your select statement
> to select a particular row in INFORMIX /ORACLE and TID in INGRES
> but I am not sure about SYBASE.
>
> Do you know if there is such an row ID exists for SYBASE ?
> I have looked at that manuals but no joy.
GAG ME WITH A PREDICATE! I still can't believe that people write
products depending on something as nonstandard as ROWID!!!!!!!!!!
Can you say "primary key"????
Jon Rosen
christop...@mccaw.com
> products depending on something as nonstandard as ROWID!!!!!!!!!!
> Can you say "primary key"????
>
> Jon Rosen
>
Have a valium latte, Ron. Since when is a primary key (or any
other key for that matter) required to be unique? Why do you
think there's all the discussion for how to generate unique,
surrogate id's for rows in sybase?
Can *you* say "There's a real world out there! Wow! All my
formal relational training makes my apps slower than molasses
going uphill in January!!!"
Topher
---
Christopher "Topher" White |"If life is just a highway
McCaw Cellular Communications| then the soul is just a car
Kirkland, Washington | And objects in the rearview mirror
<Standard disclaimers apply> | may appear closer than they are" - Steinman
Klaas Hemstra
>
>Hi everyone, I wonder if you could help me on the following -
>
>I am currently writing a SQL driver for our product
>which will be a generic driver for the following databases :
>INFORMIX, ORACLE and INGRESS and SYBASE.
Just what i did recently.
We use that library to provide access to the same databases (Informix
not yet operational) from batch processes. It provides a embedded sql-like
cursor-mechanism, with C subroutines for all normal Embedded SQL
'statements'.
Beware with sybase.
There are problems with locking and cursor's.
If you can use sybase system 10 Embedded SQL than do that.
>
>Now I know that you can use the "ROWID" in your select statement to select
>a particular row in INFORMIX /ORACLE and TID in INGRES but I am not sure
>about SYBASE.
>
>Do you know if there is such an row ID exists for SYBASE ?
>I have looked at that manuals but no joy.
I also needed something like a ROW-ID for providing update_cursor_row
en delete_cursor_row functionality.
I used the dbqual() subroutine to obtain the where clause for selecting
the 'current' row. It works, but you must add a 'timestamp' column to
all tables.
Klaas
--
Klaas Hemstra (h...@mh.nl) | / / ,~~~ ~~/~~
uucp: ..{uunet!}sun4nl!mh.nl!hst | /--/ `-, / ___ |_/ |__|
Multihouse Automatisering B.V. Gouda,Netherlands| / / ___/ / --- | \ | |
---- Incompatible: relation between two computer(software-)products. -----
j...@trwlasd.com
> > GAG ME WITH A PREDICATE! I still can't believe that people write
> > products depending on something as nonstandard as ROWID!!!!!!!!!!
> > Can you say "primary key"????
> >
> > Jon Rosen
> >
>
> Have a valium latte, Ron. Since when is a primary key (or any
> other key for that matter) required to be unique? Why do you
> think there's all the discussion for how to generate unique,
> surrogate id's for rows in sybase?
Duh, excuse me? Since when is a primary key required to be
unique? A primary key, by definition, IS unique. That isn't
fancy academic poop, that's just definitional truth.
To quote from the Oracle System 7 SQL manual:
"A PRIMARY KEY constraint designates a column or combination
of columns as the table's primary key. To satisfy a PRIMARY
KEY constraint, both of these conditions must be true:
* No primary key value can appear in more than one row
in the table.
* No column that is part of the primary key can contain
a null."
This is STANDARD ANSI SQL and I will bet that if you look in Sybase
System 10 manuals (where Sybase finally adopted the REST OF THE
WORLD approach to SQL instead of their own and added declarative
referential integrity instead of making you do it yourself with
triggers and stored procedures), you will find that a PRIMARY KEY
is defined the same way in Sybase.
It is true that non-primary keys can have duplicates. But, it
should be required that EVERY TABLE have a primary key. If this
means that you create a special unique integer for this purpose,
that's fine, but it is still a primary key and it is still unique.
> Can *you* say "There's a real world out there! Wow! All my
> formal relational training makes my apps slower than molasses
> going uphill in January!!!"
Well, that's arguable. Maybe you should get a faster database.
Denormalization is just fine, but even a denormalized table has
a primary key (although it may be a composite key made up of the
keys from all tables that have been denormalized).
Sorry, you are just plain wrong on this one.
Oh, and the name is:
Jon (not Ron, thank you) Rosen
Mischa Sandberg
>
> GAG ME WITH A PREDICATE! I still can't believe that people write
> products depending on something as nonstandard as ROWID!!!!!!!!!!
> Can you say "primary key"????
Well, if you can lift your finger off the "!" key, for a moment ...
there are some problems which have to be solved on a row-by-row basis,
either inside SQL or outside it. For example, many apps import data
from sources that have an implied sequence, but no natural sequence key.
One of
our apps receives large sets of transactions as data, where there is
an implied order to processing these transactions --- but nothing
so handy as a differentiable timestamp associated with each row.
So, what do you do? Either you make an external process strap on
an artificial sequence number as a primary key, or you do it within
SQL.
Another problem arises from "next unique number generators" --- where
incoming transactions require new artificial keys to be assigned simply
to distinguish them. Typically, a "select max(seqno)+1 from tab" is
an expensive operation; at very least, it produces a hot spot in the index
on "seqno". It's sometimes not good enough to say, get a DB engine or
platform that's fast enough to do this for your given inbound transaction
rate. Having a system-generated identity that is (say) guaranteed to
be in ascending order, with atomic generation and no locking, is a
valuable asset.
My .02
--
Mischa Sandberg ... Mischa_...@mindlink.bc.ca
or uunet!van-bc!rsoft!mindlink!Mischa_Sandberg
*-*-*-*-*-*-*-*-*-*-*
Engineers think equations are an approximation of reality.
Physicists think reality is an approximation of the equations.
Mathematicians never make the connection.
j...@trwlasd.com
> In article <Cp24E...@trwlasd.com> j...@trwlasd.com writes:
> >Duh, excuse me? Since when is a primary key required to be
> >unique? A primary key, by definition, IS unique. That isn't
> >fancy academic poop, that's just definitional truth.
> >
> >To quote from the Oracle System 7 SQL manual:
> >
> > "A PRIMARY KEY constraint designates a column or combination
> > of columns as the table's primary key. To satisfy a PRIMARY
> > KEY constraint, both of these conditions must be true:
> >
> > * No primary key value can appear in more than one row
> > in the table.
> >
> > * No column that is part of the primary key can contain
> > a null."
>
Okay Chris, you've made your point. I don't understand the need
to keep up the insulting name change over and over again. Is
this the way you get your jollies?
> But look at what comes next:
>
> >It is true that non-primary keys can have duplicates. But, it
> >should be required that EVERY TABLE have a primary key. If this
> >means that you create a special unique integer for this purpose,
> >that's fine, but it is still a primary key and it is still unique.
>
> Every table has to have a primary key? Yeh, right. Even if it
> doesn't make sense, even if it is superfluous to the problem,
> and even if it is as expensive as all hell to implement?
Well, if you don't have a primary key, you can't identify the rows
without regard to sequence. Pure and simple. And sequence is not
valid in a relational database. Unless you add stuff to provide a
navigational way to access rows. In which case, it is a relational
database - NOT. You can manufacture a primary key if you desire,
even if it means creating an artificial SEQUENCE or TIMESTAMP or...
or... or... ROWID, yeah, that's the ticket :-) Or you can use all
the columns in the table to create a primary key. Doesn't matter
as long as you can identify each row. Any table without a primary
key can not be accurately updated or deleted using non-cursor SQL.
Any attempt to use any UPDATE or DELETE on a table without a primary
key runs the risk of deleting more rows than you want to delete or
updating multiple rows when you only want to update one.
Primary keys are NOT "expensive as hell" to implement. Early RDBMSes
had problems because their indexing techniques were lousy. That is
simply not true any more. There is no performance reason to NOT use
primary keys.
True, there are legacy tables where there are duplicate rows and no
candidate keys. Often, these tables need redesign. If not, you can
always add a unique id to each row. However, they should have a
primary key once they are in a relation.
> Maybe it's me, but I've always considered solving the problem a
> little higher priority than sticking ith the letter of some
> disinterested party's attempts to force every problem in the
> universe into their own predisposed view of the world.
I don't get into religious wars. I understand relational theory.
Its not perfect, and many of the things it is faulted for are valid.
The insistence on a primary key, however, IMHO, is not one of them.
This is a kneejerk reaction by people who simply don't want to look
at the inadequacies of their ability to carefully and correctly
design information systems. Its like using GOTOs. The reason to
NOT use GOTOs isn't because Dijsktra SAID you shouldn't (that's just
dumb) but simply because there really isn't any good reason to use
them once you understand good programming practices.
> These are rules of thumb to make yourself ask questions and
> evaluate your situation, not the holy scriptures. This is SQL,
> not the cure for cancer.
Agreed. But what does that mean? Here is a good comparison. You
can use a double precision floating point number to represent a
character in C. If you really want to do so, you can. Does that
mean you should? No one in their right mind would really suggest
that is a good thing or correct thing to do. But, hell, C isn't
the hold scriptures, now, is it? Its just a dumb language. Why
the heck shouldn't I be able to do anything I want to do?
Well, there is no crime committed here if you do. But it isn't
going to win you any prizes, and more importantly, it won't get
you very many jobs. I realize the analogy is less than perfect
but I think it points up what I am trying to say. Just because
you CAN do something that is dumb, doesn't mean you should. Having
a table without a primary key is dumb. There is NEVER any need for
it. IMHO, things would be a lot easier if it was mandated, but many
systems don't mandate it, so we have to live with the differences of
opinion that this conflict generates.
> Personally, I don't give a rat's whisker about rowid's, or
> primary keys, or sombody's religious f***ing war with
> relational theroy. ANSI ain't the mother church, folks --
> it's an organization that's gotten to where it is by politiking
> and settling for the LCD (ok, maybe it's a lot *like* the
> holy mother church, but ....). And if my option's are a
> unique primary key required for all tables or I'm gonna get
> 5 hail Boyce's and 3 Our Codd's put on me, I think we have
> a problem.
Well, wait a second here. You are WILLING (obviously because
you use Sybase) to accept the notion of FLAT tables. WHY????
Why not argue that FLAT tables are a curse, also. That NO ONE
should FORCE me to have flat tables. What if I want hierarchies?
What about networks??? Pointers?!!!??? Hooray!!!! Bring on the
node pointers that we all knew (and hated) in the old DBMSes.
Why do you pick on a "requirement" like a primary key and don't
complain about all the other limits of SQL and relational databases.
The fact is that relations are a well-founded basis on which to
build many (but not all) information systems and databases. To
do so accurately requires a primary key on the tables. If you
try to use relational databases without primary keys, the entire
house of cards comes down (i.e., many of the operations that are
mathematically provable and correct become faulty when primary
key constraints are not enforced). The very fact that ALL of the
recent versions of the major database products (Oracle 7, Sybase 10,
etc.) support CONSTRAINT clauses and primary keys is testimony to
the fact that this is very necessary. The major reason that these
databases still support non-primary key tables is purely historical.
> Religious wars don't do anyone any good, and standardization
> is only useful if it solves the problem *cheaper* than some
> other way.
Cheaper is only a valid measure if it covers ALL issues, including
design, implementation, maintenance and extensibility. Every
system I have ever worked on (and I have worked on a LOT of them
in over 11 years of working with SQL and 25 years of working with
databases in general) had MORE problems when primary key constraints
were not or could not be enforced. So, after many many years of
experience, I have simply decided that it is the better way. Not
because of religious beliefs (heaven knows I don't have many of
those :-).
>
> And the bottom line is that Ron's reply to the original post
> made him and all the rest of us look like asses.
Huh? If I made myself look like an ass, how did that rub off on
you (and all the rest of youse :-)? Not that I think for a minute
that it did :-)
> And Jon, you can quote me every manual you want to, but the
> solutions are only in the back of the book in college, babe.
Uh, let's not get REAL personal here, okay, sweety? :-)
> This is the real world and we're spending real money for
> real solutions to real problems. Sometimes you gotta break
> the rules to do what's right.
There are no RULES. There are only good results. I have simply
found that following the guideline of always enforcing PK integrity
gives me the best results. I concede if you believe differently
that you have the right to do it your way, but I will bet my results
over the past 10+ years without hesitation.
> Sorry to waste y'all's bandwith,
No problem :-)
> Topher
Don, er, Ron, er Jon (Mommy! Daddy! I'm so confused!)
Gunther Birznieks
know that you will never ever care about updates or deletes on the
table for that reason (it wouldnt make logical sense to the
application). And you only want to do inserts.
But say you have a field that is a date of the transaction in the
historical table... Its NOT Unique (most of the time it is) but you
really do not care if you delete a whole days worth of data if you
ever have to delete (deletions would only occur if you are reloading
transactional data anyway in this case).
In such a table, to me, it would not make sense to use a primary key
because thats not the model the table is designed off of. And it's
not necessary for deletes or updates.
I know that you can have a primary key by forcing an incremental
number for each transaction in the historical database...But if it is
not necessary for the model, why should I be forced to do so?
Later,
Gunther
sid kitchel
>Well, if you don't have a primary key, you can't identify the rows
>without regard to sequence. Pure and simple. And sequence is not
>valid in a relational database. Unless you add stuff to provide a
>navigational way to access rows. In which case, it is a relational
>database - NOT. You can manufacture a primary key if you desire,
>even if it means creating an artificial SEQUENCE or TIMESTAMP or...
>or... or... ROWID, yeah, that's the ticket :-) Or you can use all
>the columns in the table to create a primary key.
This thread has been focusing on the primary key aspect of
ROWID. Of course, Oracle's ROWID has this property, but it is also the
physical disk address of the row it points to. Most people know the
trade-offs between a primary key and a physical pointer. There are
some developers who prefer having the direct pointer for efficiency
reasons. This makes Oracle be more attractive than Sybase just because
the ROWID is very useful. It is also standardized and always there.
The end-user or DBA does not have to do anything. Primary keys
invented ad-hoc cannot be so regular and dependable.
--Sid
--
Sidney W. Kitchel kit...@cs.indiana.edu, kit...@dpsi.com
Data Parallel Systems, Inc. ============|| DPSI ||===============
4617 E. Morningside Drive (812) 334-8100
Bloomington, Indiana, 47408 USA FAX: (812) 334-8121
Sergey Fradkov
Uf-f-f, you're really getting too personal, guys!
It's a scientific discussion, isn't it?
As about enforcing PK, I'd rather see a possibility to define a
special type, called ROWID, that will have all the features of
regular ROWID, but will be optional. Also, type COUNTER, like
in MS Access (forgive me), will help people, who struggle to
assign a constanly incrementing number to an insert. But, it it
very important, that it will not be mandatory, because our
world is so different, and there is no good universal solution
for anything. You just can give people good tools. And exactly
this made C so popular - you can use GOTO's and LONGJUMP's,
when you desperate, and also give a pretty structured code.
There is no sacred things, what can you do...
Sergey Fradkov
dag zeinetz Cgua
Using IDENTITY in a column definition seems to be the same as type COUNTER.
At least it is very useful since SYBASE generates unique rowid's for columns
with that attribute.
// Dag
Erland Sommarskog
>Well, if you don't have a primary key, you can't identify the rows
>without regard to sequence. Pure and simple. And sequence is not
>valid in a relational database. Unless you add stuff to provide a
>navigational way to access rows. In which case, it is a relational
>database - NOT. You can manufacture a primary key if you desire,
>even if it means creating an artificial SEQUENCE or TIMESTAMP or...
>or... or... ROWID, yeah, that's the ticket :-) Or you can use all
>the columns in the table to create a primary key.
Just to give my testimony. I have a table in the application I maintain
whose clustered index allows duplicats on rows, that is two rows in
this table may contain exactly the same data. This because I load this
table from files. Since these files are taken from another relational
system, each row in the file are probably unique. However, when I format
the file for bcp, I delete the three fields which constitutes the unique
key. You see, in our application these fields relates to nothing at all.
So how do I handle this? Well, this is only a transient table. In a
stored procedure I find out which rows which really interests me, and
then I stuff them into the real application tables. And, yes, part of
the procedure is to assign a code number to make each interesting row
unique. It's not a sequence number for the entire table, but a sub-
number of a combination person and date.
This approach may not comply with the relational model, but it does
comply with the problem I have to solve.
Another reason to use ROWID is of course to use it as the possible
access to a record, which I know that at least Rdb supports. This
may not be pretty, but sometimes the customer is more interested
in response time than compliance to any model.
--
Erland Sommarskog, som...@enea.se, Stockholm
Io che parlo di niente, lo faccio soltanto in paura del silenzio
pcoll@gandalf_ld
In the real world where you need performance you dont write "generic"
products - Sybase Prof Services does very nicely out of people who do -
trying to dig them out of the mire afterwards!
In the real world if you want performance just use SQL Server and forget
O***** and A** etc
************************************************************
** Peter Collard - Sybase UK Professional Services **
** pcoll @ sybase.com (44) 457-861435
**
** These are my views and could well be different **
** to those of my employer. **
************************************************************
Craig Denson
>It is one of sybases great weaknesses that they dont support either
>cursors (until system 10) or unique sequential keys. I have spent
>many hours getting around their limitations.
yeah, i too, but...
>PS. denormalized tables do NOT necessarily have a unique key
>
>PPS. here is a real case where a unique key is not possible:
>
>an instruction (shares,security,limit,datestamp) to a trader on the
>floor of the exchange can have multiple executions
>(shares,security,price,datestamp). Instructions are allocated to
>orders (shares,security,account,limit,datestamp) with a N:N
>relationship. Figure a key for the resulting
>(shares,security,account,price,datestamp) allocated execution that
>will allow maintenance (correction of prices, update to the execution
>time...). You cant do it without unique sequential keys because it is
>quite likely that there will be multiple identical allocated
>executions (every field is the same). It just cant be done in SQL.
my thoughts about your problem:
1- if the relation is correct, in that all the required data is
available, then, why do updates need to distinguish one record from
another record? if an update does need to distinguish between records
then your data is underconstrained.
seems to me, that this is the real issue - there are implicit
distinctions between records that are not represented in the data.
2- it may be that unique keys are required, but i don't see the need
for sequential keys - given that you already have a datestamp. why
couldn't you use an additional datestamp field for each record? and
hey - that's monotone increasing: what more could you want?
on the other hand, if your relation needs a unique sequential key -
then it would be nice if the rdbm would generate one for you easily.
it just isn't the point, though, that Jon was arguing, i think.
craig - being as unique, sequential and referentially intact as i can
given the tools i've got.
Edward Barlow,The Ed,16/30B,59241,5551212
I think Mischa wrote a good rational explaination - and I will try too.
In addition to the fact that row by row operations are sometimes needed,
and the fact that outside sources of data often are unkeyed but require a
sequence, there is the performance considerations of most normal applications. You
pooh-pooh performance by saying that we should get a faster database, but most
real life developers cant do that. Are
you saying that performance is not going to be slowed by a n column key that might
contain variable length fields, dates and could run into the hundreds of bytes? Why
cant you use a nice 4 byte int? There is also the coding and maintenance issues of
multi byte keys. I think any real world systems designers prefer a nice
simple key structure. It is one of sybases great weaknesses that they dont support
either cursors (until system 10) or unique sequential keys. I have spent many
hours getting around their limitations.
Ed
PS. denormalized tables do NOT necessarily have a unique key
PPS. here is a real case where a unique key is not possible:
an instruction (shares,security,limit,datestamp) to a trader on the floor of the
exchange can have multiple executions (shares,security,price,datestamp). Instructions
are allocated to orders (shares,security,account,limit,datestamp) with a N:N
relationship. Figure a key for the resulting (shares,security,account,price,datestamp)
allocated execution that will allow maintenance (correction of prices, update to
the execution time...). You cant do it without unique sequential keys
because it is quite likely that there will be multiple identical allocated
executions (every field is the same). It just cant be done in SQL.