Is there some auto-timestamp per record in SQL Anywhere?
Kevin Shrieve
database product and a product using SQL Anywhere v5.0. They want to
use the most efficient way to pull all the data entered since our last
connection.
I am new to SQL and SQL Anywhere. Is there by chance some kind of
automatically generated timestamp set per record to reflect datetime of
creation, and more importantly, datetime of last edit? Or would that
only be available for tables that had fields specifically created for
that purpose?
Thanks for any help.
Kevin Shrieve
ke...@NOSPAMlumiere.net
humans: remove NOSPAM for address
Reg Domaratzki
on each table that you want to monitor. This default will modify the column
to the last time that the row was modified.
--
Reg Domaratzki
Sybase iAnywhere Solutions
Please reply only to the newsgroup
TechWave 2000 July 30 - August 3 /2000
http://www.sybase.com/techwave2000
"Kevin Shrieve" <ke...@NOSPAMlumiere.net> wrote in message
news:39637DD4...@NOSPAMlumiere.net...
Mike Pasveer
TIMESTAMP'? When setting up defaults for columns or domains in PowerDesigner
for an ASA7 database, I have been entering CURRENT TIMESTAMP as the default.
If I enter TIMESTAMP in this field, it does not appear to recognize it as a
keyword, and treats it like a string literal; the word is enclosed in single
quotes in the SQL code that is generated. If there is a difference between
the two, how do I get PowerDesigner to recognize TIMESTAMP as a keyword for
the default?
"Reg Domaratzki" <nospam_...@sybase.com> wrote in message
news:CHWGbLr5$GA....@forums.sybase.com...
Mike Packard
DEFAULT TIMESTAMP - record will be stamped on INSERT, but NOT changed on UPDATE
DEFAULT CURRENT TIMESTAMP - record will be stamped on INSERT and re-stamped on
every UPDATE (with current date/time)
See "Constants in expressions" in the Help file.
Mike
Mike Packard
default?
You may not like this answer (and it may not be the best one for you, but it
will get the job done):
1. Export the table create script to ascii text.
2. Edit the script and change the default.
3. Run the script in ISQL to create your table.
I create all tables in script and run them in ISQL and find you get greater
control and far easier maintenance (my opinion) this way.
My .02 cents worth
Mike
Paul Horan[TeamSybase]
Both PD6 and PD7 allow for the creation of default values and clauses on a
column.
In the column properties dialog, click on the Standard Checks tabpage. In
the Default column, enter the string CURRENT TIMESTAMP. When the table
create script is generated, the column will have the clause DEFAULT CURRENT
TIMESTAMP generated for it.
Paul Horan [TeamSybase]
VCI www.twoplus.com
Springfield, MA
"Mike Packard" <mpac...@mail.sandi.net> wrote in message
news:3963BF0A...@mail.sandi.net...
David Fishburn
DEFAULT TIMESTAMP does both insert and update
DEFAULT CURRENT TIMESTAMP only does INSERT.
--
David Fishburn
Sybase - iAnywhere Solutions
Please only post to the newsgroup
TechWave 2000 July 30 - August 3 /2000
http://www.sybase.com/techwave2000
Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
http://sdn.sybase.com/sdn/mec/mec_down_ebf.stm
"Mike Packard" <mpac...@mail.sandi.net> wrote in message
news:3963BE44...@mail.sandi.net...
Breck Carter
http://www.bcarter.com/tip088_default_values.htm
Breck
Mike Pasveer
Mike Pasveer
"Breck Carter" <NOSPAM_...@bcarter.com> wrote in message
news:3964839a...@forums.sybase.com...
> You can tell PowerDesigner about the missing defaults. In PD6 you just
> add TIMESTAMP to the sqlany5.def or sqlany55.def file (I'm 99% sure of
> that).
>
> In PD7 you add TIMESTAMP to the list in Database - Edit Current DBMS -
> General tab - Script - Sql - Keywords - ReservedDefault (I'm 100% sure
> of that).
>
> If you don't do that then if you put TIMESTAMP as a column default it
> will show up with quotes as DEFAULT 'TIMESTAMP'.
>
> Breck
>
Mike Pasveer
the one I'm looking for, since I need it for both inserts and updates
"David Fishburn" <spamoff_...@sybase.com> wrote in message
news:SD$Nnh05$GA....@forums.sybase.com...
Michael F. Nicewarner [TeamSybase]
figured out that DEFAULT TIMESTAMP is a valid statement. I'll put in my 2
cents and see what happens. :-)
--
Michael F. Nicewarner [TeamSybase]
mailto:mike.ni...@ibpinc.com
http://www.datamodel.org
"David Fishburn" <spamoff_...@sybase.com> wrote in message
news:U#tSVm05$GA....@forums.sybase.com...
> > You may not like this answer (and it may not be the best one for you,
but
> it
> > will get the job done):
>
> I certainly don't like it.
> So this is what I do....
>
> In your PD directory there is a DBMS directory.
> In there you have different files for each of the different RDBMS'.
>
> I edit sqlany6.def and do a search for CURRENT TIMESTAMP
> When I find it, I copy the line and remove the CURRENT.
> I have attached a copy of it.
> Please make a backup copy of your own.
>
> If you are using ASA7, it uses a different file syasa70.xdb.
> I have attached it as well, same rules apply.
>
> Hope that helps, I found it really annoying to. I had thought I managed
to
> get the PowerDesigner group to add that to the 7.0 release. I will try to
> get it into a maintenance patch.
>
>
> --
> David Fishburn
> Sybase - iAnywhere Solutions
> Please only post to the newsgroup
>
> TechWave 2000 July 30 - August 3 /2000
> http://www.sybase.com/techwave2000
>
> Sybase Developers Network
> http://sdn.sybase.com/sdn/mec/mec_home.stm
> http://sdn.sybase.com/sdn/mec/mec_down_ebf.stm
>
> "Mike Packard" <mpac...@mail.sandi.net> wrote in message
Breck Carter
Current DBMS - General tab - Script - Sql - Keywords - ReservedDefault
Breck
Mike Pasveer
Mike Pasveer
the one I'm looking for, since I need it for both inserts and updates
Mike Pasveer
"David Fishburn" <spamoff_...@sybase.com> wrote in message
news:SD$Nnh05$GA....@forums.sybase.com...
> Sorry there Mike you got it backwards.
>
> DEFAULT TIMESTAMP does both insert and update
> DEFAULT CURRENT TIMESTAMP only does INSERT.
>
>
>
> --
> David Fishburn
> Sybase - iAnywhere Solutions
> Please only post to the newsgroup
>
> TechWave 2000 July 30 - August 3 /2000
> http://www.sybase.com/techwave2000
>
> Sybase Developers Network
> http://sdn.sybase.com/sdn/mec/mec_home.stm
> http://sdn.sybase.com/sdn/mec/mec_down_ebf.stm
>
> "Mike Packard" <mpac...@mail.sandi.net> wrote in message
Mike Packard
Mike Packard
david wrote>> DEFAULT TIMESTAMP does both insert and update
david wrote>> DEFAULT CURRENT TIMESTAMP only does INSERT.
David,
Are you sure? You may want to check again!
Here is cut and paste from 5.5.05 SQLA Help.
This says to me that CURRENT TIMESTAMP can be used to record the last update.
What does it say to you? (Maybe the Help is misleading?)
Actually, I have had quite a difficult time in trying to pin down the exact
meaning from the Help file.
Either the information is not there, or the indexing is not including it.
(Where's Tom Slee when you need him?)
--------------- quote from Section "Constants in Expressions"
--------------------
When combined with the CURRENT TIMESTAMP, a default value of LAST USER can be
used to record (in separate columns) both the user and the date and time a row
was last changed..
----------------- end -----------------
Mike Packard
>> the one I'm looking for, since I need it for both inserts and updates
You'd better wait until we get definitive results back -- see my reply to David.
I'd swear that CURRENT means both insert and update, and the other one only does
insert.
It makes sense that without the keyword CURRENT the default would act like a
normal default (insert only).
Mike
(Staking his reputation on this one... for what that's worth....)
David Fishburn
> This says to me that CURRENT TIMESTAMP can be used to record the last
update.
> What does it say to you? (Maybe the Help is misleading?)
This says to me:
update table_name
set last_modified = CURRENT TIMESTAMP
where id = 1;
Not what we were discussing.
--
David Fishburn
Sybase - iAnywhere Solutions
Please only post to the newsgroup
TechWave 2000 July 30 - August 3 /2000
http://www.sybase.com/techwave2000
Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
http://sdn.sybase.com/sdn/mec/mec_down_ebf.stm
"Mike Packard" <mpac...@mail.sandi.net> wrote in message
news:3964E83D...@mail.sandi.net...
Breck Carter
day they added DEFAULT TIMESTAMP. It is wrong for Version 5, it is
wrong for Version 6, and it has been finally fixed for Version 7.
In summary, DEFAULT TIMESTAMP and DEFAULT LAST USER go together. They
are not really "defaults" because they get used on update as well as
insert. DEFAULT TIMESTAMP was added first, as part of Transact SQL
compatibility, but its value goes far beyond that.
Breck
Mike Packard
for Version 5,
I was not aware of that ... until now.
I'm thankful I don't use this default. On first look, I it looked like a great
idea. Then I realized that my app puts summary values in a column in a table I
would have used it on and this default would cause me to lose the editing
timestamp, so I decided not to use it.
The fact that if you update any column in a table where you're using this, it
would update the LAST USER and TIMESTAMP also makes this less desirable for
someone who only wants user-edited updates to change those columns (not system
or app updated).
Thanks for the clarification.
Here I was defending the documentation. What a fool. I should have known the
docs were wrong <g>....
Kevin Shrieve
Kevin
David Fishburn
great
> idea.
As I am sure Breck will agree with me, this is one of the BEST features of
ASA.
Many, many, many times you want to know when a record was last changed.
WithOUT this feature, you must write a trigger that performs the update to
the column, now you don't.
So you could do something like this:
create table customer (
id int default autoincrement
name varchar(50) not null,
phone varchar(30) null,
added timestamp default CURRENT TIMESTAMP,
added_by varchar(128) default CURRENT USER,
modified timestamp default TIMESTAMP,
modified_by varchar (128) DEFAULT LAST USER,
primary key( id )
);
No triggers, everything maintained automatically by the database.
insert into customer( name, phone ) values ( 'Fishburn', '555-1212' );
Wahoo!!
--
David Fishburn
Sybase - iAnywhere Solutions
Please only post to the newsgroup
TechWave 2000 July 30 - August 3 /2000
http://www.sybase.com/techwave2000
Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
http://sdn.sybase.com/sdn/mec/mec_down_ebf.stm
"Mike Packard" <mpac...@mail.sandi.net> wrote in message
news:3965F2E5...@mail.sandi.net...
Breck Carter
fan of default timestamp Sybase's ever had, AND the Sybase folks
LAUGHED at me when I got so excited about it because THEY originally
thought of it as a compatibility-with-Transact-SQL-timestamp-datatype
feature only.
And, as we all know, Transact SQL timestamps have nothing to do with
dates or times or audit trails at all.
The companion default last user came much later, after much lobbying
(i.e., shouted demands at the Techwave bar).
Breck
Breck Carter
you could override the changes by updating them with the old values (I
think)
Breck
Breck Carter
your specific need. However, with zero application code changes you
CAN track the who and when of every update by simply adding two
columns to every table. It really does satisfy most audit
requirements.
Breck
Kevin Shrieve
David Fishburn
--
David Fishburn
Sybase - iAnywhere Solutions
Please only post to the newsgroup
TechWave 2000 July 30 - August 3 /2000
http://www.sybase.com/techwave2000
Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
http://sdn.sybase.com/sdn/mec/mec_down_ebf.stm
"Kevin Shrieve" <ke...@NOSPAMlumiere.net> wrote in message
news:39664683...@NOSPAMlumiere.net...
Mike Packard
I've been thinking about this.
It means that everytime I insert a record from a batch process, such as:
(a) When we want to recalcuate all de-normalized aggregate totals (yes, it's for
performance)
(b) When we replicate the data to other systems
At these times, I do NOT want the system to say it updated the record.
I want timestamps only when the user changes data.
This is why I've been reluctant to use the auto updating.
It's much easier to have manual and positive control over when the timestamp and
last user gets updated.
If there was a way to turn it off...
But there is -- it's just too much of a hassle to have to remember to include it
every time.
All you have to do is:
update foo set col1='blah', timestampCol=timestampCol,
lastUserCol=lastUserCol where blah blah...
Bottom line is, I'm still not convinced that the automatic way is foolproof (and
I'm the fool who'll muff it).
So I'll continue to send update timestamp and user manually.
Mike
Jim Egan
know much about each other. <g>
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com