AOL Webserver - Retrieving oid from INSERT

Author

Retrieving oid from INSERT

William Scott Jordan

2006-03-30, 11:55 pm

Hi all!

Is there any way to get the oid or any other row identifier from a
database insert with ns_db on Postgres? Say for example I have the
following table:

CREATE TABLE test (
test_column int
) ;

And then I do an insert with aolserver, along the lines of:

ns_db dml $db "INSERT INTO test (test_column) SELECT
COALESCE(MAX(test_column),0) + 1 FROM test"

Because I don't know the value of test_column that I just entered, I
don't have any way to continue working with that entry. Is there
some trick to getting either the entry's oid or the value of
test_column back into the current TCL workspace?

Any suggestions would be appreciated.

-Scott

Janine Sisk

2006-03-30, 11:55 pm

The way we do this in OpenACS is to have a primary key in each table
that is populated from a sequence. So in your code you get the next
number from the sequence, do the insert (storing the number in the
primary key column), and then you have that number already in your
possession to use to reference the row.

janine

On Mar 28, 2006, at 11:58 AM, William Scott Jordan wrote:

> Hi all!
>
> Is there any way to get the oid or any other row identifier from a
> database insert with ns_db on Postgres? Say for example I have the
> following table:
>
> CREATE TABLE test (
> test_column int
> ) ;
>
> And then I do an insert with aolserver, along the lines of:
>
> ns_db dml $db "INSERT INTO test (test_column) SELECT COALESCE(MAX
> (test_column),0) + 1 FROM test"
>
> Because I don't know the value of test_column that I just entered,
> I don't have any way to continue working with that entry. Is there
> some trick to getting either the entry's oid or the value of
> test_column back into the current TCL workspace?
>
> Any suggestions would be appreciated.
>
> -Scott
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to
> <listserv@listserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the
> Subject: field of your email blank.

Bas Scheffers

2006-03-30, 11:55 pm

William Scott Jordan said:
> ns_db dml $db "INSERT INTO test (test_column) SELECT
> COALESCE(MAX(test_column),0) + 1 FROM test"
Ehrm, that is a very, very bad way of creating keys. It is no unlikely
that on a very loaded site two people might insert at the same time and
get the same key!

The correct way of doing this is using a sequence:

create sequence test_id;

You can either first select an ID from this and use it manually in your
query:

select nextval('test_id') as test_id;

Or use it in a default:

create table test (
test_id primary key default nextval('test_id')
);

If using that method, you can use "curval('test_id')" to get the last
assigned id and use it for further processing after you do the insert.
curval gives back the last sequence handed out in the current session, so
it is perfectly safe to use as long as you stick with the same database
handle.

Cheers,
Bas.

Mark Aufflick

2006-03-30, 11:55 pm

How depresssing. I'm doing a lot of work with Sybase these days.
That's right, the "enterprise" database. It seems that sequences are
not "enterprise" enough and you end up seeing developers resorting to
stuff like this instead.

Not that it's relevant in this case, but it's nice to blowoff steam
occasionally - especially in an environment where people get it!

On 3/29/06, Bas Scheffers <bas@scheffers.net> wrote:
> William Scott Jordan said:
> Ehrm, that is a very, very bad way of creating keys. It is no unlikely
> that on a very loaded site two people might insert at the same time and
> get the same key!
>
> The correct way of doing this is using a sequence:

Bas Scheffers

2006-03-30, 11:55 pm

People that think this is a good solution aren't "enterprise" developers
anyway!

Though I sympathize with your pain, Sybase has identity columns, which
work quite well.

The other option is to simply have a table to like (sequence varchar, id
int) and then:

update sequences set id = id + 1 where sequence = 'mytable';
-- that also locked the row for the next updater, so now it is perfectly
safe to do:
select id from sequences where sequence = 'mytable'

All you need to do is spent half an hour at the start of a project setting
up this, a stored proc and a Tcl procedure named "get_next_id" and forget
about it from then on...

Other than this quirk, I have found Sybase to be much more "enterprise"
than other database I have worked with.

Cheers,
Bas.

Mark Aufflick said:
> How depresssing. I'm doing a lot of work with Sybase these days.
> That's right, the "enterprise" database. It seems that sequences are
> not "enterprise" enough and you end up seeing developers resorting to
> stuff like this instead.
>
> Not that it's relevant in this case, but it's nice to blowoff steam
> occasionally - especially in an environment where people get it!
>
> On 3/29/06, Bas Scheffers <bas@scheffers.net> wrote:
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to
> <listserv@listserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the
> Subject: field of your email blank.
>

Mark Aufflick

2006-04-08, 6:57 pm

Doesn't that still serialize all updates requiring access to that sequence?

If you used it within a transaction it would also have implications
with other transactions using that "sequence" especially in a rollback
situation.

In the context of AOLServer you could minimise the impact of these
problems by initialising a totally seperate pool of db connections
just for this "sequence" manipulation proc. This could decrease
performance though - especially if there is network latency between
aolserver and the database.

I'd prefer my enterprise database not to decide that it would be a
good idea to allow NULL = NULL to evaluate as true by default, but now
I'm getting off topic ;)

Bas Scheffers

2006-04-08, 6:57 pm

Mark Aufflick said:
> Doesn't that still serialize all updates requiring access to that
> sequence?
Sequences are used for inserts, not updates. Well, I haven't seen them
being used in updates anyway.

> If you used it within a transaction it would also have implications
> with other transactions using that "sequence" especially in a rollback
> situation.
Why? Yes, they would be serialized, but again, this will really only be
used for inserts into the same table, which are serialized by the database
anyway, as far as I know.

> In the context of AOLServer you could minimise the impact of these
> problems by initialising a totally seperate pool of db connections
That is not needed. Simply by first getting a new id in a different
transaction, pretty much all of the problems are gone. In Oracle and
postgres, getting a sequence value is serialized too; it has to be. My
solution would be less efficient if used within a transaction as the
updates to actual data tables would also stop other other transactions to
get a sequence. So just get an ID in a different transaction. Postgres
sequences don't roll back either when the transaction fails, even if used
in line:

postgres=# create sequence foo_id;
CREATE SEQUENCE
postgres=# create table foo (foo_id int primary key default
nextval('foo_id'), name varchar(10));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
CREATE TABLE
postgres=# insert into foo (name) values ('bas');
INSERT 0 1
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# insert into foo (name) values ('bas1234567890');
ERROR: value too long for type character varying(10)
postgres=# select currval('foo_id');
currval
---------
7

I suspect Oracle (which I don't have handy right now) to be the same. So
there is no reason in this comparison to require my Sybase solution to
roll back the sequence. (which it would if used in the same tran as the
update, and some would like that behaviour as to not get gaps, but I
couldn't care less)

> I'd prefer my enterprise database not to decide that it would be a
> good idea to allow NULL = NULL to evaluate as true by default, but now
You mean you'd like it not acting like any other programming language out
there?

Personally, I find the whole "is not null" in SQL annoying,
making me have to write extra code to deal with NULL comparisons.

I also would prefer my enterprise database to distinguish between an empty
string and NULL! (but I'd also like Sybase to allow NULL in a boolean
field to distinguish between an explicit false and a value left empty by
the user...)

All Systems have their merrits. As (commercial) enterprise database go, I
have _much_ better experiences with Sybase than Oracle for ease of
maintainance (best backups in the business), optimization (it just uses
indexes that make sense without needing hints, no matter how complex I
make the query), performance and realiability (don't get me started). Your
mileage may vary...

Cheers,
Bas.

Andrew Piskorski

2006-04-08, 6:57 pm

On Tue, Apr 04, 2006 at 03:02:21PM +1000, Mark Aufflick wrote:
> Doesn't that still serialize all updates requiring access to that sequence?

No. Sequences in an RDBMS are designed to scale gracefully under
heavy concurrent load. (This is basic stuff, grab any good
intro. book on databases and read up on it.)

They basically do this by guaranteeing ONLY that the integer you get
from the sequence is unique and generally increasing over time -
that's it. The sequence can skip numbers, and the sequence integers
are NOT necessarily in any particular order by either commit time or
sequence request time.

> If you used it within a transaction it would also have implications
> with other transactions using that "sequence" especially in a rollback
> situation.

No, it doesn't, not in any database with real built-in sequence
support, like Oracle or PostgreSQL. If you rollback a transaction
which used a sequence, that sequence integer is simply discarded,
never to be used again.

Of course, if you are implementing sequences yourself with a helper
table, then yes, you get to worry about all the above issues. (They
are only hard to solve *IF* you also need concurrency.) This is why
it's nice to have real sequences in the RDBMS...

> In the context of AOLServer you could minimise the impact of these
> problems by initialising a totally seperate pool of db connections
> just for this "sequence" manipulation proc. This could decrease

AFAICT, you're speculating about solving a problem that never actually
exists.

There are real RDBMSs, like MS SQL Server, which don't support the
sort of sequences I described above, but those all tend to have some
different but similar mechanism intended to solve the same problems.
I think SQL Server and Sybase use auto-incrementing column values, for
example.

I bet SQLite, for instance, doesn't provide any such sequence-like
support at all, so you'd emulate it yourself by simply taking an
exclusive lock on a my_sequence table or whatever. But, SQLite is a
lightweight embedded database designed for low or no concurrency, so
by definition, you don't have a concurrency problem anyway! (SQLite
takes an exlusive lock on the whole database for every single write
transaction, so if you DO have a concurrency problem, it's unlikely to
be solely because of your ad-hoc table-based sequence support!)

But actually I'm wrong, SQLite does support auto-incrementing columns,
and provides a helper function to return the the integer key that just
got automatically generated by your insert:

http://www.sqlite.org/faq.html#q1

And of course, serializing all writes (or even all transactions
period) works just fine if your load is moderate and your application
is carefully designed to insure that ALL your transactions are very
fast. That seems to be exactly the approach that D. Richard Hipp,
creator of SQLite, takes in building his own SQLite-backed websites.

--
Andrew Piskorski <atp@piskorski.com>
http://www.piskorski.com/

Dossy Shiobara

2006-04-08, 6:57 pm

On 2006.04.04, Bas Scheffers <bas@SCHEFFERS.NET> wrote:
> All Systems have their merrits. As (commercial) enterprise database go, I
> have _much_ better experiences with Sybase than Oracle for ease of
> maintainance (best backups in the business), optimization (it just uses
> indexes that make sense without needing hints, no matter how complex I
> make the query), performance and realiability (don't get me started). Your
> mileage may vary...

I just have to ask: what version of Sybase and Oracle are you comparing?
Oracle 7 to Sybase ASE 11? Since Oracle 8.0 and 8i, in my experience,
Oracle has always been better, more reliable, etc. than Sybase ASE 10
and 11. Oracle 10g today ... there's no comparison -- Sybase is closer
to MySQL/PostgreSQL than it is to Oracle, in terms of capability and
stability.

Of course, I find that it always ends up being a people problem: if you
have DBAs that don't know what they're doing or don't do their jobs
properly, you can end up with an Oracle instance that's broken and a
Sybase setup that works ... which leaves developers to learn that
"Sybase is better than Oracle" ...

The lesson here: Regardless of software, if your people can't make it
work, it won't satisfy you.

But, I still assert that, given equally competent people, modern Oracle
is better than modern Sybase. Hands down.

-- Dossy

--
Dossy Shiobara | dossy@panoptic.com | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

Bas Scheffers

2006-04-08, 6:57 pm

Andrew Piskorski said:
> No. Sequences in an RDBMS are designed to scale gracefully under
> heavy concurrent load. (This is basic stuff, grab any good
I think Mark was talking about my Sybase solution (sequence table) for if
you don't want to use "identity" columns.

> I think SQL Server and Sybase use auto-incrementing column values, for
> example.
Yeah they do. They have a bit of a bad rep, though. Do a google on "sybase
identity gap" to see what I mean. That and they require extra work if you
want to transfer a table from one DB to another, but not much.

> And of course, serializing all writes (or even all transactions
> period) works just fine if your load is moderate and your application
The load can even be pretty high. Unless you are doing an amazing amount
of inserts, it's not going to matter, especially if you first get the
sequence in a different transaction.

Cheers,
Bas.

Bas Scheffers

2006-04-08, 6:57 pm

Dossy Shiobara said:
> I just have to ask: what version of Sybase and Oracle are you comparing?
9i and 11.92, 12, 12.5.

The worst Oracle instance was maintained by a couple of very good DBAs, at
least according to the guy who actually worked for Oracle and did an audit
of our system. They were doing a release on a weekend which included some
complex SQL query of mine to update a lot of data. The DBAs phoned me late
in the afternoon saying my query had been running for the past 4 hours.
WTF!? Of course I got the blame and they ended up rolling back the
release. Knowing i wasn't dumb I pleaded for them to give me access to the
live system on Monday so I could inspect the thing. A query plan showed it
decided to do a carthesian join on a couple of 100K row tables for no
reason other than it being bored. No amount of updating statistics and
index hints could convince it otherwise. On the UAT system it ran fine. I
ended up just messing around with it (knowing it must have been some
obscure bug) and after changing a join to a sub-query (or vice versa,
can't recall) Oracle suddenly behaved and ran the update in a couple of
minutes as I suspected it would in the first place.

That is probably the worst thing I came acros, but there have been other
instances.

> to MySQL/PostgreSQL than it is to Oracle, in terms of capability and
> stability.
Not sure about that, I have never actually seen a Sybase instance go down
or mess up my data. To be fair, I can say the same about Oracle. On the
capabilities side, Oracle Analytics in 10g are very good, but other than
that I don't see Oracle offers anything more. And don't get me started on
the performance of Intermedia!

The whole financial world seems to run on Sybase, and so far it's been
doing OK...

> The lesson here: Regardless of software, if your people can't make it
> work, it won't satisfy you.
Absolutely. But I have experienced three Oracle production enviroments,
all maintained by competent DBAs, all of whom spent quite a bit of time on
the phone with gold-pressed-latinum support only to be told: "yeah, sorry,
that's a bug", "We are working on a patch" or "It will be fixed in the
next version". Were they so good they only their funky pushing the system
to the max could reveal these bugs, or are there just so many bugs in the
system?

They also _still_ have a bug in either their connection library or only
JDBC that has been around since version 7. I forgot the error string, but
everyone and their brother is compaining about it on the internet. It
seems to mostly happen after an SQL error that the connection becomes
stale. Yet it is almost completely unreproducable and so still not fixed.
Very poor if you ask me. We have a client running a heavily loaded system
that is very affected by this. Their EJB server (Orion) tends to crap out
when this happens in a transaction and leaves entity beans locked, meaning
they have to restart the damn thing anything from a couple of times a week
to several times a day.

> But, I still assert that, given equally competent people, modern Oracle
> is better than modern Sybase. Hands down.
I'd call it a draw, except that you need more experienced people to keep
Oracle ticking while someone like me can install and maintain a
high-concurrency heavily loaded Sybase enviroment with ease.

I don't feel like rubishing Oracle just for the fun if it or because I
don't like working with it, that's not the kinda guy I am. But these are
my very real life experiences...

Cheers,
Bas.

Mark Aufflick

2006-04-08, 6:57 pm

Hi Andrew,

I might have missed the context in my reply - we were discussing
Sybase which is one of those databases missing sequence support.

We are a little off topic, but Bas was suggesting a reasonable way
around the missing sequences (very reasonable compared with many other
attempts to work around it) and I was basically bemoaning the fact
that I have to work with such a lame database!

Sybase, like SQLlite, does (now) support an auto-incrementing column
type, but AFAICT there is no way to find out which id you just
inserted.

On 4/4/06, Andrew Piskorski <atp@piskorski.com> wrote:
> On Tue, Apr 04, 2006 at 03:02:21PM +1000, Mark Aufflick wrote:
>
> No. Sequences in an RDBMS are designed to scale gracefully under
> heavy concurrent load. (This is basic stuff, grab any good
> intro. book on databases and read up on it.)
>
> They basically do this by guaranteeing ONLY that the integer you get
> from the sequence is unique and generally increasing over time -
> that's it. The sequence can skip numbers, and the sequence integers
> are NOT necessarily in any particular order by either commit time or
> sequence request time.
>
>
> No, it doesn't, not in any database with real built-in sequence
> support, like Oracle or PostgreSQL. If you rollback a transaction
> which used a sequence, that sequence integer is simply discarded,
> never to be used again.
>
> Of course, if you are implementing sequences yourself with a helper
> table, then yes, you get to worry about all the above issues. (They
> are only hard to solve *IF* you also need concurrency.) This is why
> it's nice to have real sequences in the RDBMS...
>
>
> AFAICT, you're speculating about solving a problem that never actually
> exists.
>
> There are real RDBMSs, like MS SQL Server, which don't support the
> sort of sequences I described above, but those all tend to have some
> different but similar mechanism intended to solve the same problems.
> I think SQL Server and Sybase use auto-incrementing column values, for
> example.
>
> I bet SQLite, for instance, doesn't provide any such sequence-like
> support at all, so you'd emulate it yourself by simply taking an
> exclusive lock on a my_sequence table or whatever. But, SQLite is a
> lightweight embedded database designed for low or no concurrency, so
> by definition, you don't have a concurrency problem anyway! (SQLite
> takes an exlusive lock on the whole database for every single write
> transaction, so if you DO have a concurrency problem, it's unlikely to
> be solely because of your ad-hoc table-based sequence support!)
>
> But actually I'm wrong, SQLite does support auto-incrementing columns,
> and provides a helper function to return the the integer key that just
> got automatically generated by your insert:
>
> http://www.sqlite.org/faq.html#q1
>
> And of course, serializing all writes (or even all transactions
> period) works just fine if your load is moderate and your application
> is carefully designed to insure that ALL your transactions are very
> fast. That seems to be exactly the approach that D. Richard Hipp,
> creator of SQLite, takes in building his own SQLite-backed websites.
>
> --
> Andrew Piskorski <atp@piskorski.com>
> http://www.piskorski.com/
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to <listserv@listserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
>

Mark Aufflick

2006-04-08, 6:57 pm

I've added so many 2c this thread is nearly tax deductable!

> .. I have never actually seen a Sybase instance go down
> or mess up my data.

I have seen some large Sybase databases go down quite spectacularly.

> The whole financial world seems to run on Sybase, and so far it's been
> doing OK...

Most of the web world runs on mysql and does ok - just like CDBaby who
used to have constraint and corruption issues until they switched to
Postgres.

> Absolutely. But I have experienced three Oracle production enviroments,
> all maintained by competent DBAs, all of whom spent quite a bit of time on
> the phone with gold-pressed-latinum support only to be told: "yeah, sorry,
> that's a bug", "We are working on a patch" or "It will be fixed in the
> next version". Were they so good they only their funky pushing the system
> to the max could reveal these bugs, or are there just so many bugs in the
> system?

Seems a common scenario in much of the "enterprise" software I have
come across. CA is partucularly good at this sort of response.

> I'd call it a draw, except that you need more experienced people to keep
> Oracle ticking while someone like me can install and maintain a
> high-concurrency heavily loaded Sybase enviroment with ease.

You probably do need more people to manage Oracle in a big
environment, but my opinion is that you end up needing more developers
with Sybase because you end up needing more lines of code. That is a
purely gut feel however.

Further, my experience with large-ish (some millions of rows and many
tables) databases is that Postgres scales to that size easily and
needs even less support than Sybase. The support people probably need
to be smarter though, especially if you use replication.

Bas Scheffers

2006-04-08, 6:57 pm

"select @@identity" for the last identity value assigned in the
current session. This is acros all tables with id columns, so if you
are inserting multiple in one transaction and need all of them, be
sure to assign them to another variable after each insert.

Cheers,
Bas.

On 5 Apr 2006, at 05:14, Mark Aufflick wrote:

> Hi Andrew,
>
> I might have missed the context in my reply - we were discussing
> Sybase which is one of those databases missing sequence support.
>
> We are a little off topic, but Bas was suggesting a reasonable way
> around the missing sequences (very reasonable compared with many other
> attempts to work around it) and I was basically bemoaning the fact
> that I have to work with such a lame database!
>
> Sybase, like SQLlite, does (now) support an auto-incrementing column
> type, but AFAICT there is no way to find out which id you just
> inserted.
>
> On 4/4/06, Andrew Piskorski <atp@piskorski.com> wrote:
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to
> <listserv@listserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the
> Subject: field of your email blank.

Olaf Mersmann

2006-04-08, 6:57 pm

Hi Mark,
* Mark Aufflick <mark-aolserver@AUFFLICK.COM> [060405 06:31]:
> Sybase, like SQLlite, does (now) support an auto-incrementing column
> type, but AFAICT there is no way to find out which id you just
> inserted.

Sybase provides the @@identity variable which holds the last
auto-generated identity value in the transaction. I can also say for
sure, that Sybase has had identity columns for quite some time
(started using them sometime around 1998 - not sure which version of
Sybase though). The big problem with identity columns as you've said
is that Sybase at times will produce large gaps if the server goes
down unexpectedly or is shutdown with nowait. But there are
workarounds (and since 12.5 official fixes) for that problem.

-- Olaf

Bas Scheffers

2006-04-08, 6:57 pm

Mark Aufflick said:
> I have seen some large Sybase databases go down quite spectacularly.
Any details on how or why? One common cause for downtime is filling up the
log space and locking up the DB, not knowing how to fix it. But they
should have read the manual!

> Most of the web world runs on mysql and does ok - just like CDBaby who
You can't possibly liken Sybase to MySQL!

> with Sybase because you end up needing more lines of code. That is a
> purely gut feel however.
I don't see why, what do you think Oracle offers that Sybase doesn't and
gives it an advantage? 99% of applications I see are nothing more than
straight SQL jobs. And even when stored procs are involved, I find T-SQL a
much nicer and language than PL/SQL and uses less code to achieve the same
thing.

> Further, my experience with large-ish (some millions of rows and many
> tables) databases is that Postgres scales to that size easily and
> needs even less support than Sybase. The support people probably need
> to be smarter though, especially if you use replication.
Definitely, Postgres is my first choice. It took me a while to get back to
it after being severely burned by 6.5, but now it seems great. But if a
client wants a commercial DB, I will always push for Sybase. Luckily, most
clients already have big Sybase production enviroments, so using that is
usually a given.

"Enterprise" features like replication is one of the things that lets it
down. While miles ahead of "the other free database", backup isn't great
either. The version 8 option of "stop checkpoints, copy data files" and
the automatic archiving of log files is nice, but they need to go one more
step to the equivalent of Sybase's "dump database" and "dump tran"
commands.

Cheers,
Bas.

Andrew Piskorski

2006-04-08, 6:57 pm

On Wed, Apr 05, 2006 at 02:14:38PM +1000, Mark Aufflick wrote:
> Hi Andrew,
>
> I might have missed the context in my reply - we were discussing
> Sybase which is one of those databases missing sequence support.

Yes, you're quite correct. My apologies for jumping in while paying
no attention to those earlier posts.

> We are a little off topic, but Bas was suggesting a reasonable way
> around the missing sequences (very reasonable compared with many other
> attempts to work around it) and I was basically bemoaning the fact
> that I have to work with such a lame database!

Looking back at Bas's original post, yeah, that is a nice way to
convert a Sybase-style identity (auto-increment) column to an
Oracle-style sequence, should you want to do that.

Btw, I don't see why Sybase "identity gaps" should be much of an
issue. Yes, it would be mildly annoying for an identity column value
to suddenly skip from 10031 to 5000002 or something, as sometimes
those values are human visible, but if an application breaks because
of that it's a serious bug in the application, not in the RDBMS.

http://www.sypron.nl/idgaps.html

Oracle and PostgreSQL can in principle show the exact same sort of
huge gaps in their sequence numbers, they just don't usually.

--
Andrew Piskorski <atp@piskorski.com>
http://www.piskorski.com/

Tom Jackson

2006-04-08, 6:57 pm

Can't you write a function to do the insert and return the value of the oid?

If you are contemplating adding a new query, seems like it would be just as
easy to replace the insert statement with a function call.

Another possibility is to grab a number of oids and cache them in your
application. I think OpenACS does this for certain applications.

tom jackson

On Tuesday 04 April 2006 23:25, Bas Scheffers wrote:
> "select @@identity" for the last identity value assigned in the
> current session. This is acros all tables with id columns, so if you
> are inserting multiple in one transaction and need all of them, be
> sure to assign them to another variable after each insert.

Mark Aufflick

2006-04-08, 6:57 pm

That's great - I'm a bit of a Sybase newby and this tip makes life a lot easier.

Given that this is so easy and reasonable I don't understand why so
many Sybase developers employ other unreliable methods to emulate
triggers.

On 4/5/06, Bas Scheffers <bas@scheffers.net> wrote:
> "select @@identity" for the last identity value assigned in the
> current session. This is acros all tables with id columns, so if you
> are inserting multiple in one transaction and need all of them, be
> sure to assign them to another variable after each insert.
>
> Cheers,
> Bas.
>

Mark Aufflick

2006-04-08, 6:57 pm

> > Most of the web world runs on mysql and does ok - just like CDBaby who
> You can't possibly liken Sybase to MySQL!

I know that they are not even in the same league technically, but they
do show disturbingly similar philosophies - like making NULL = NULL by
default because many clients with poorly trained developers asked for
it.

> I don't see why, what do you think Oracle offers that Sybase doesn't and
> gives it an advantage? 99% of applications I see are nothing more than
> straight SQL jobs. And even when stored procs are involved, I find T-SQL a
> much nicer and language than PL/SQL and uses less code to achieve the same
> thing.

I was really commenting on the sql rather than stored procs. Simple
things like trying to format a date (that's not one of the fixed and
oddly numbered formats) can be quite trying in Sybase.

I do find the T-SQL syntax quite cumbersome too, but I suspect that is
more personal preference than anything else.

> "Enterprise" features like replication is one of the things that lets [Postgres]
> down. While miles ahead of "the other free database", backup isn't great
> either. The version 8 option of "stop checkpoints, copy data files" and
> the automatic archiving of log files is nice, but they need to go one more
> step to the equivalent of Sybase's "dump database" and "dump tran"
> commands.

Agreed. Backup has always been weak although it's a lot better than it
was. Same with replication although I am very interested to try out
Slony and also the replication solution from commandprompt.com.

Bas Scheffers

2006-04-08, 6:57 pm

Mark Aufflick said:
> do show disturbingly similar philosophies - like making NULL = NULL by
> default because many clients with poorly trained developers asked for
I recon that's the only one! (and I still don't see why this is such a bad
thing) My problem with mysql isn't this "simplification", it's the
inconsistencies. (apart from other "known issues") Sybase is very
consistent in what it does, even if you find it's not the way _you_ would
like it to do things.

> things like trying to format a date (that's not one of the fixed and
> oddly numbered formats) can be quite trying in Sybase.
Absolutely right; it's stupid the default output isn't ANSI format. All
sybase projects I have been on in the past few years were Java, so I just
got a Date object back just like with any other database and formatting
wasn't an issue.

That said: I don't tend to use any formatting features of the database in
my web apps on AOLserver. (or Vignette, which also uses Tcl) Instead, I
take the default format and simply create a "date_format" Tcl procedure I
pass everything through. If needed, I first use a regexp (or split/lindex)
to put the date from the db into something "clock scan" can take in and
then format it using the clock format command.

I don't even see this as a workaround for the database's limitations; it
makes perfect sense to have one date format proc to be used throughout the
website as dates never have more than one or two formats on any given
site.

For Sybase I would simply select all dates as "convert(varchar(20),
date_col, 23)" to get back the "2005-12-23T15:33:32" format. "clock scan"
parses this fine if you just take out the "T". (it parses it with the "T"
too, but gets the time wrong and thinks it is 7 hours earlier for me!)

Sybase does also take the ANSI "2005-12-23 15:33:32" format as input, so
that is one less thing to worry about.

Another shortcomming of dates in Sybase is a complete lack of timezones.
If needed, I get around this by storing everything as UTC. Java's
PreparedStatement makes this quite easy - as long as you use jconn3 as
version 2 didn't due to a bug - you can use the "-gmt 1" switch in clock
scan/format for Tcl. But that does make using getdate() as defaults
impossible.

> I do find the T-SQL syntax quite cumbersome too, but I suspect that is
> more personal preference than anything else.
It could very well be what you grew up on; I used T-SQL before anything
else and just hate PL/SQL!

Cheers,
Bas.

Dossy Shiobara

2006-04-08, 6:57 pm

On 2006.04.06, Mark Aufflick <mark-aolserver@AUFFLICK.COM> wrote:
> That's great - I'm a bit of a Sybase newby and this tip makes life a
> lot easier.
>
> Given that this is so easy and reasonable I don't understand why so
> many Sybase developers employ other unreliable methods to emulate
> triggers.

For the exact same reason you almost did: new developers don't know any
better, but most of them don't bother to ask anyone. Instead, they go
ahead and implement some crazy brokenness because they have to "just
make it work" ... I find most crap code ends up written this way.

On 2006.04.06, Mark Aufflick <mark-aolserver@AUFFLICK.COM> wrote:
>
> I know that they are not even in the same league technically, but they
> do show disturbingly similar philosophies - like making NULL = NULL by
> default because many clients with poorly trained developers asked for
> it.

You're right; mysql is orders of magnitude better than Sybase. I'm dead
serious. (I've been doing work in Sybase lately and I have to tell you:
T-SQL is so damn awful, it would probably be better off without any
stored procedure capability than to have it.)

And, where are you getting your misinformation about mysql about?
Here's a test I did just now (MySQL 4.1.11):

mysql> create table dossy (x int, y int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into dossy (x, y) values (1, 1), (2, 2), (3, NULL), (4, NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from dossy where y = NULL;
Empty set (0.00 sec)

mysql> select * from dossy where y IS NULL;
+------+------+
| x | y |
+------+------+
| 3 | NULL |
| 4 | NULL |
+------+------+
2 rows in set (0.00 sec)

MySQL is far more standards-compliant than Sybase.

Did you know that Sybase silently promotes an empty string to a string
of 1 character? You can't actually store an empty string in a Sybase
database. That is absolutely ridiculous. I'd expect that kind of
behavior from some college-level pidgin databases class project, not
from something people are expected to pay real money for.

Sybase's TEXT/IMAGE support is ridiculous. You can't create stored
procedures which take parameters that are typed TEXT or IMAGE? You have
to jump through ridculous hoops with VARBINARY pointers and
READTEXT/WRITETEXT? I suppose Oracle's not much better in this regard,
with all their DBMS_LOB package shenanigans. mysql claims any data type
can be used as a stored procedure parameter -- I'll have to test to see
how it handles BLOB and CLOB types.

-- Dossy

--
Dossy Shiobara | dossy@panoptic.com | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

Bas Scheffers

2006-04-08, 6:57 pm

Dossy Shiobara said:
> You're right; mysql is orders of magnitude better than Sybase. I'm dead
Yeah, when it doesn't blow up in weird and wonderul ways... And you don't
mind not being able to do online backups.... And if you think '0000-00-00'
or '2006-02-30' is a date... (oh no, just tested mysql 5 silently turns
the latter into '0000-00-00'!) And expect 2 ints added together to form a
value more than 32 bits to be silently cast to a long... The list goes on
and on.

> T-SQL is so damn awful, it would probably be better off without any
> stored procedure capability than to have it.)
Some people feel the same about PL/SQL... Or that basterdazation of PL/SQL
used in Postgres. T-SQL isn't perfect, but it has always gotten the job
done for me quite easily and efficiently. Different strokes for differen
folks.

> Did you know that Sybase silently promotes an empty string to a string
> of 1 character?
Most database can't, they just silently make it NULL. Not sure which is
worse... In fact, mysql does *very* weird things with empty and semi-empty
strings:

mysql> select concat('"',foo,'"') from test2 where foo = ' ';
+---------------------+
| concat('"',foo,'"') |
+---------------------+
| "" |
| " " |
+---------------------+
2 rows in set (0.00 sec)

Yes, those are 3 spaces in the where clause and mysql returns rows with
both empty strings and one space!

Oh, and did you notice how it trims the values of char() columns?

mysql> create table test3 (foo char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test3 values ('a ');
Query OK, 1 row affected (0.00 sec)

mysql> select concat('"',foo,'"') from test3;
+---------------------+
| concat('"',foo,'"') |
+---------------------+
| "a" |
+---------------------+
1 row in set (0.00 sec)

Just to make sure I have a recent version:

mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.18, for redhat-linux-gnu (i386)

> Sybase's TEXT/IMAGE support is ridiculous. You can't create stored
> procedures which take parameters that are typed TEXT or IMAGE? You have
That is silly, yes. But like you say, it's not better in any of the other
commercial databases.

> to jump through ridculous hoops with VARBINARY pointers and
> READTEXT/WRITETEXT? I suppose Oracle's not much better in this regard,
A tiny hoop, really. Plus it seems to be a problem with the C driver more
than anything else. In JDBC, you can send as much data as you like for an
insert or update of a TEXT/IMAGE column.

> can be used as a stored procedure parameter -- I'll have to test to see
> how it handles BLOB and CLOB types.
Does mysql still use/support actual LOBs? (as in data stored elsewhere
from the row) I thought everything was stored in-line and clob/blob was
just a synonym for varchar(2B), just like in Postgres?

I don't mind anyone who prefers Oracle over Sybase. But to say mysql is
much better and more standards compliant just because you don't like a few
things in Sybase (which, as it turns out mysql isn't very good at either)
is, well, a little strange.

Just my $0.02...

Bas.

Dossy Shiobara

2006-04-08, 6:57 pm

On 2006.04.06, Bas Scheffers <bas@SCHEFFERS.NET> wrote:
> Dossy Shiobara said:
>
> Yeah, when it doesn't blow up in weird and wonderul ways... [...]

I'll bet a nickel it's user error (whether that user is a developer or
the DBA).

> [...] And you don't mind not being able to do online backups.... [...]

Oh man, who is feeding you this pack of lies? I mean, mysql has been
able to do hot online backups since May 2002! Well, you've been able to
do them for MyISAM table types much earlier than that, but the InnoDB
Hot Backup product reached version 1.00 in May 2002.

> [...] And if you think '0000-00-00' or '2006-02-30' is a date... (oh
> no, just tested mysql 5 silently turns the latter into '0000-00-00'!)
> [...]

There's a very good reason for this, AND if you find it objectionable,
you can even turn it off:

http://dev.mysql.com/doc/refman/5.0...time-types.html

"MySQL also allows you to store '0000-00-00' as a "dummy date" (if
you are not using the NO_ZERO_DATE SQL mode). This is in some cases
is more convenient (and uses less space in data and index) than
using NULL values."

> [...] And expect 2 ints added together to form a value more than 32
> bits to be silently cast to a long... The list goes on and on.

My understanding is that the data type determines the number of
bits/bytes used at the storage layer, but all mathematics and aggregate
functions should operate at the highest precision and largest word size.
MySQL documents this:

http://dev.mysql.com/doc/refman/5.0...-functions.html

"MySQL supports arithmetic with both signed and unsigned 64-bit
values. If you are using numeric operators (such as +) and one of
the operands is an unsigned integer, the result is unsigned. You can
override this by using the SIGNED and UNSIGNED cast operators to
cast the operation to a signed or unsigned 64-bit integer,
respectively."

If you want to truncate a value to 32-bits, your database should offer
you a way to do so. mysql does, albeit a bit awkward:

mysql> select (pow(2, 32) + 8675309) << 32 >> 32;
+------------------------------------+
| (pow(2, 32) + 8675309) << 32 >> 32 |
+------------------------------------+
| 8675309 |
+------------------------------------+

> Some people feel the same about PL/SQL... Or that basterdazation of PL/SQL
> used in Postgres. T-SQL isn't perfect, but it has always gotten the job
> done for me quite easily and efficiently. Different strokes for differen
> folks.

I've done plenty of PL/SQL in Oracle from 7.3 through 9i, and T-SQL in
Sybase 11.5 through 12.5 -- both modern versions -- and given the
choice, I'd choose Oracle and PL/SQL over Sybase and T-SQL. That's my
personal preference, naturally, but one based on first-hand experience
with both.

>
> Most database can't, they just silently make it NULL. Not sure which is
> worse...

Not being able to store a string of length 0 is worse.

> In fact, mysql does *very* weird things with empty and semi-empty
> strings:
[...]
> Yes, those are 3 spaces in the where clause and mysql returns rows with
> both empty strings and one space!

The row where you got one space back: I'd like to see how you inserted
the data. In general, this isn't weird at all, because ...

> Oh, and did you notice how it trims the values of char() columns?

Absolutely. mysql trims trailing whitespace on CHAR values:

http://dev.mysql.com/doc/refman/5.0...e-overview.html

"Note: Trailing spaces are removed when CHAR values are retrieved."

...

"Note: Before 5.0.3, trailing spaces were removed when VARCHAR
values were stored, which differs from the standard SQL
specification."

> Does mysql still use/support actual LOBs? (as in data stored elsewhere
> from the row) I thought everything was stored in-line and clob/blob was
> just a synonym for varchar(2B), just like in Postgres?

http://dev.mysql.com/doc/refman/5.0...strictions.html

"[...] InnoDB stores the first 768 bytes of a VARCHAR, BLOB, or TEXT
column in the row, and the rest into separate pages."

If you want to use MyISAM and store the LOB data in a separate place,
you could manually do something similar to what Postgres does: store the
LOBs in the filesystem and generate a unique ID and just store that in
the row, or store the LOBs in a separate table and again, just store the
unique ID in the row.

> I don't mind anyone who prefers Oracle over Sybase. But to say MySQL
> is much better and more standards compliant just because you don't
> like a few things in Sybase (which, as it turns out mysql isn't very
> good at either) is, well, a little strange.

The difference? If I *had* to, I could extend mysql to do exactly what
I need. (Beware: Tcl as a supported UDF language for mysql stored
procs! Muwahaha.)

-- Dossy

--
Dossy Shiobara | dossy@panoptic.com | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

Tom Jackson

2006-04-08, 6:57 pm

It sounds like you guys are comparing rotten oranges and rotten apples.

tom jackson

On Thursday 06 April 2006 05:37, Bas Scheffers wrote:
> Dossy Shiobara said:
>
> Yeah, when it doesn't blow up in weird and wonderul ways... And you don't
> mind not being able to do online backups.... And if you think '0000-00-00'
> or '2006-02-30' is a date... (oh no, just tested mysql 5 silently turns
> the latter into '0000-00-00'!) And expect 2 ints added together to form a
> value more than 32 bits to be silently cast to a long... The list goes on
> and on.
>
>
> Some people feel the same about PL/SQL... Or that basterdazation of PL/SQL
> used in Postgres. T-SQL isn't perfect, but it has always gotten the job
> done for me quite easily and efficiently. Different strokes for differen
> folks.
>
>
> Most database can't, they just silently make it NULL. Not sure which is
> worse... In fact, mysql does *very* weird things with empty and semi-empty
> strings:
>
> mysql> select concat('"',foo,'"') from test2 where foo = ' ';
> +---------------------+
>
> | concat('"',foo,'"') |
>
> +---------------------+
>
> | "" |
> | " " |
>
> +---------------------+
> 2 rows in set (0.00 sec)
>
> Yes, those are 3 spaces in the where clause and mysql returns rows with
> both empty strings and one space!
>
> Oh, and did you notice how it trims the values of char() columns?
>
> mysql> create table test3 (foo char(10));
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> insert into test3 values ('a ');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select concat('"',foo,'"') from test3;
> +---------------------+
>
> | concat('"',foo,'"') |
>
> +---------------------+
>
> | "a" |
>
> +---------------------+
> 1 row in set (0.00 sec)
>
> Just to make sure I have a recent version:
>
> mysql> \s
> --------------
> mysql Ver 14.12 Distrib 5.0.18, for redhat-linux-gnu (i386)
>
>
> That is silly, yes. But like you say, it's not better in any of the other
> commercial databases.
>
>
> A tiny hoop, really. Plus it seems to be a problem with the C driver more
> than anything else. In JDBC, you can send as much data as you like for an
> insert or update of a TEXT/IMAGE column.
>
>
> Does mysql still use/support actual LOBs? (as in data stored elsewhere
> from the row) I thought everything was stored in-line and clob/blob was
> just a synonym for varchar(2B), just like in Postgres?
>
> I don't mind anyone who prefers Oracle over Sybase. But to say mysql is
> much better and more standards compliant just because you don't like a few
> things in Sybase (which, as it turns out mysql isn't very good at either)
> is, well, a little strange.
>
> Just my $0.02...
>
> Bas.
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to
> <listserv@listserv.aol.com> with the body of "SIGNOFF AOLSERVER" in the
> email message. You can leave the Subject: field of your email blank.

Bas Scheffers

2006-04-08, 6:57 pm

Tom Jackson said:
> It sounds like you guys are comparing rotten oranges and rotten apples.
Are there any apples that aren't considered rotten by someone, somewhere?
Like I said before, every RDBMS has it's issues...

....MySQL just takes them to a whole new level and gets even otherwise
sensible people like Dossy (sorry to be speaking in the 3rd person here)
describing bugs as features. Oh well.

Cheers,
Bas.

Bas Scheffers

2006-04-08, 6:57 pm

Dossy Shiobara said:
> Oh man, who is feeding you this pack of lies? I mean, mysql has been
> able to do hot online backups since May 2002! Well, you've been able to
How? All I can find is this "mysqldump" and "mysqlhotcopy", which lock
tables while they are being dumped. Hardly "online backup" if you ask me.

> do them for MyISAM table types much earlier than that, but the InnoDB
> Hot Backup product reached version 1.00 in May 2002.
That is true, in fact I knew about it. Usualy when the pgsql/mysql debate
comes up I use this to point out another reason why mysql is "free" in
beer nor speech. But compared to Sybase of course this argument is lost.
(unless you run a small(ish) database on (free, as in beer) Sybase
Express, of course!)

> There's a very good reason for this, AND if you find it objectionable,
> you can even turn it off:
I found the page it is documented on, but I don't find "This is in some
cases is more convenient (and uses less space in data and index) than
using NULL values." good enough reason. Besides, if you turn it off many
3rd party apps will fail as they rely on the functionality being there.

> My understanding is that the data type determines the number of
> bits/bytes used at the storage layer, but all mathematics and aggregate
> functions should operate at the highest precision and largest word size.
That doesn't fly when you expect a .NET or JDBC .getInt() to do just that.
So unless you specifically ask for it to be casted up, I believe the
database should throw the error, not your client program. Just because it
is documented, doesn't make it right!

> The row where you got one space back: I'd like to see how you inserted
> the data. In general, this isn't weird at all, because ...
insert into test4 values (null), (''), ('a '), (' ');

Actually, I stand corrected on that one, Sybase is a messed up in this
case as MySQL. Postgres gets it right, though.

> Absolutely. mysql trims trailing whitespace on CHAR values:
Doesn't that go against standards? Then what is the point of having "char"
fields to begin with?

> The difference? If I *had* to, I could extend mysql to do exactly what
> I need. (Beware: Tcl as a supported UDF language for mysql stored
> procs! Muwahaha.)
Why waste your time, Postgres already has that!

(and loads of other
features mysql doesn't have, plus it is truly Free and as ANSI compliant
as they get)

Cheers,
Bas.

Dossy Shiobara

2006-04-08, 6:57 pm

On 2006.04.06, Bas Scheffers <bas@SCHEFFERS.NET> wrote:
>
> Why waste your time, Postgres already has that!

(and loads of other
> features mysql doesn't have, plus it is truly Free and as ANSI compliant
> as they get)

Can you embed Postgres? Before I got turned on to SQLite, mysql served
as a great embedded database inside the real applications. Can Postgres
do the same? (Moot point, really, since I now just use SQLite for
this, but it's good trivia to know, anyway.)

PostgreSQL 8.0's support of Win32 will help things, too.

As much as people criticize MySQL's replication and clustering, where's
Postgres's? I don't see any mention of it in Postgres 8.1 docs:

http://www.postgresql.org/docs/8.1/...tive/index.html

The docs also don't mention any GIS support.

I agree, Postgres is a fine database. I would be happy using it if I
were working in an environment that already had it deployed. But, I
don't see a compelling reason to try and convert a shop over to
Postgres. Just the same, I don't feel a big need to migrate folks onto
MySQL, either.

.... except, if they're using Sybase. Then, I'd advocate migrating onto
any other RDBMS, just because having to work in Sybase would be more
effort. Seriously. 16K max page size? Not being able to create stored
procedures with TEXT or IMAGE parameters? How do you build a content
management system on top of Sybase? A whole lot of dynamic SQL in the
application? I guess ... in which case, if you're not leveraging stored
procedures, why not just use mysql or Postgres? Sigh.

I'm glad you like Sybase. Someone has to; better you than me, for sure.
:-)

-- Dossy

--
Dossy Shiobara | dossy@panoptic.com | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

Bas Scheffers

2006-04-08, 6:57 pm

On 6 Apr 2006, at 17:42, Dossy Shiobara wrote:
> Can you embed Postgres? Before I got turned on to SQLite, MySQL
> served
I don't see that as a downside, it's not what I use it for. Like you
say, there's SQLite for that. Try embedding mysql into a non-GPL
project and see how free it is then!

> PostgreSQL 8.0's support of Win32 will help things, too.
For some, I guess. I wouldn't want to run a production app on a
Windows box anyway! But yeah, it's there and supposedly even simpler
to get going than mysql is on Windows! (though I have never tried
MySQL on windows, but Postgres's installer is very good)

> As much as people criticize MySQL's replication and clustering,
> where's
> Postgres's? I don't see any mention of it in Postgres 8.1 docs:
Nope, just 3rd party products. Supposedly good, but I have never
tried them.

> The docs also don't mention any GIS support.
The comunity does, though. PostGIS is quite mature. And I am making
good use of the point datatype and it's related operators to search
for stuff in a radius around a postcode, very efficient.

> don't see a compelling reason to try and convert a shop over to
> Postgres. Just the same, I don't feel a big need to migrate folks
> onto
Maybe not with mysql 5 and InnoDB anymore. I would run a mile from
previous versions, though. And as those didn't have stored procs,
porting would be easy.

> ... except, if they're using Sybase. Then, I'd advocate migrating
> onto
> any other RDBMS, just because having to work in Sybase would be more
What did the kind folks at Sybase do to you as a child!

> effort. Seriously. 16K max page size? Not being able to create
> stored
How is that a bad limit? The 2K pages and varchar(255) were terrible,
but 16K isn't a problem. Yes, you still can't do 2GB varchars, but
neither can Oracle or any of the "big 4" databases. (though not sure
about DB2) You have to go to a free one for that.

> procedures with TEXT or IMAGE parameters? How do you build a content
> management system on top of Sybase? A whole lot of dynamic SQL in the
Personally, I have never in any CMS system had more than 16K in any
CLOB. 16K is a freakin' big amount of text for a website! In any case
it seems to be better than Oracle's 4000.

> I'm glad you like Sybase. Someone has to; better you than me, for
> sure.
> :-)
Hehe, ditto, and also for your love of Oracle!

Isn't AOL still a
big Sybase shop anyways?

Cheers,
Bas.

dhogaza@PACIFIER.COM

2006-04-08, 6:57 pm

> On 2006.04.06, Bas Scheffers <bas@SCHEFFERS.NET> wrote:

> As much as people criticize MySQL's replication and clustering, where's
> Postgres's? I don't see any mention of it in Postgres 8.1 docs:
>
> http://www.postgresql.org/docs/8.1/...tive/index.html

That's because replication isn't part of basic PG support. You can choose
a couple of options, though:

PGCluster - Multi-master no delay synchronus replication for load sharing
or HA. Large objects are now supported.

Slony-I - Master to multi-slave cascading and almost-failover.

> The docs also don't mention any GIS support.

PostgreSQL has had operators and index support for polygons and the like
forever. Some of the earliest users of PG with large databases were in
the GIS realm.

> Just the same, I don't feel a big need to migrate folks onto
> MySQL, either.

The people who wrote mysql have the ethical standards of the George W.
Bush administration.

Bas Scheffers

2006-04-08, 6:57 pm

On 6 Apr 2006, at 19:13, dhogaza@PACIFIER.COM wrote:
> The people who wrote mysql have the ethical standards of the George W.
> Bush administration.
What, it's made by the same folks as those behind JBoss!? ;-)

Bas.

Mark Aufflick

2006-04-08, 6:57 pm

On 4/6/06, Bas Scheffers <bas@scheffers.net> wrote:
> Mark Aufflick said:
> I recon that's the only one! (and I still don't see why this is such a bad
> thing) My problem with mysql isn't this "simplification", it's the
> inconsistencies. (apart from other "known issues") Sybase is very
> consistent in what it does, even if you find it's not the way _you_ would
> like it to do things.

I feel a good old fashioned usenet style flame war coming on =)

In Sybase, NULL matches NULL in a search clause (by default - as
established), but NULL will never match anything (not even itself) in
a join ( http://manuals.sybase.com/onlineboo...kTextView/13175
).

That doesn't sound very consistent!

> It could very well be what you grew up on; I used T-SQL before anything
> else and just hate PL/SQL!

You are probably correct. (Although I grew up on Applesoft basic and I
don't like VB!)

Should we call a truce? ;)

Mark Aufflick

2006-04-08, 6:57 pm

On 4/6/06, Dossy Shiobara <dossy@panoptic.com> wrote:
> Did you know that Sybase silently promotes an empty string to a string
> of 1 character? You can't actually store an empty string in a Sybase
> database. That is absolutely ridiculous. I'd expect that kind of
> behavior from some college-level pidgin databases class project, not
> from something people are expected to pay real money for.

Although that's not a dissimilar problem to the NULL as empty string
issue in the Oracle C libraries (which really really bites).

Mark Aufflick

2006-04-08, 6:57 pm

On 4/7/06, Mark Aufflick <mark-aolserver@aufflick.com> wrote:
> On 4/6/06, Dossy Shiobara <dossy@panoptic.com> wrote:
>
> Although that's not a dissimilar problem to the NULL as empty string
> issue in the Oracle C libraries (which really really bites).
>
And it also reminds me - Sybase silently truncates strings if you try
to insert a string that is longer than the char/varchar field. If
that's not mysql-esque I don't know what is.

Dossy Shiobara

2006-04-08, 6:57 pm

On 2006.04.07, Mark Aufflick <mark-aolserver@AUFFLICK.COM> wrote:
>
> Although that's not a dissimilar problem to the NULL as empty string
> issue in the Oracle C libraries (which really really bites).

Um, where are you getting this information from? Oracle's OCI libraries
definitely expose whether a column in a resultset is an empty string or
a NULL through an "is null" kind of boolean. So, if you ignore that
boolean, then yes: a NULL appears to be an empty string. But, if you
care to tell the difference apart, you can.

See: (Requires Oracle technet login.)

http://download-west.oracle.com/doc...5rel.htm#449815
http://download-west.oracle.com/doc...2bas.htm#422173

The "indicator variable" parameter (indp) -- look at the second URL,
Table 2-8 "Output Indicator Values":

-2 The length of the item is greater than the length of the output
variable; the item has been truncated. Additionally, the
original length is longer than the maximum data length that can
be returned in the sb2 indicator variable.

-1 The selected value is null, and the value of the output variable
is unchanged.

0 Oracle assigned an intact value to the host variable.

>0 The length of the item is greater than the length of the output
variable; the item has been truncated. The positive value
returned in the indicator variable is the actual length before
truncation.

So, if the indicator variable is set to -1, the value is NULL, even if
the output variable (the column value) is an empty string.

Now, whether the AOLserver ns_db interface bubbles up that NULL
information back to the application code ... that's a different story,
but this isn't a limitation of Oracle's OCI library, but rather
AOLserver's ns_db interface.

-- Dossy

--
Dossy Shiobara | dossy@panoptic.com | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)

Bas Scheffers

2006-04-08, 6:57 pm

Mark Aufflick said:
> I feel a good old fashioned usenet style flame war coming on =)
Slashdot has flame wars, all we have are friendly argument with as outcome
learning more about our favourite and other products.

> In Sybase, NULL matches NULL in a search clause (by default - as
Yes. I personaly think it is a good extention to be able to just compare
to NULL directly instead of having to detect in code that the input value
is null and change your query string accordingly. It may not be standard
practice, but it sure as hell makes a lot of sense. Postgres and MySQL
allow you to do "where foo = null" without warning or error but simply
don't return rows.

> established), but NULL will never match anything (not even itself) in
> a join (
You mean just like in Postgres and MySQL? (not tried in Oracle)

I don't really see how the two are related. One is good practice done by
other databases too and one is, well, nothing to write home about and
probably good behaviour the ANSI SQL standard should prescribe.

> You are probably correct. (Although I grew up on Applesoft basic and I
> don't like VB!)
There is one thing I dislike more than VB itself: VB programmers. I mean
people who ONLY know hot to program VB. The kind that make their function
return "-999" on error instead of telling you what the f*** is actually
wrong. (a stacktrace would be nice, oh wait, VB doesn't do those!)

> Should we call a truce? ;)
Don't be silly!

Bas.