1) In several tables, in my MySQL version, I created columns using
something like the following:
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)
2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.
3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.
Thanks
Ted 2 2682
Ted wrote:
1) In several tables, in my MySQL version, I created columns using
something like the following:
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)
2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.
3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.
Thanks
Ted
1) You can use an UPDATE trigger to simulate the same functionality.
Usually though it is better to use stored procedures to perform all
your data access. That way you can easily include the timestamp as part
of your update procs.
2) You don't need separate scripts for each View / Function. You do
need separate batches. A batch is separated using the GO keyword in
Query Analyzer.
3) Don't confuse TIMESTAMP with DATETIME. They are not at all the same!
You are referring to DATETIME values, which ARE permitted as parameters
and return values in functions. BTW, you don't need to write a function
to do date arithmetic - it already exists as a built-in function:
DATEADD().
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
Ted (r.*********@rogers.com) writes:
1) In several tables, in my MySQL version, I created columns using
something like the following:
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it?
You will have to modify all that code. To have to be portable is indeed
painful, and a good start is to pay attention to what is in ANSI standards,
what is commonly supported. And not to the least to what are extensions
added by a certain vendor. My knowledge of ANSI and other engines are
poor (since I live in a sheltered world where I only need to support
SQL Server), but I would suspect that the ON UPDATE clause for the
default constraint is properitary to MySQL.
The way to do this in SQL Server is to use a trigger. Not that triggers
usually are very portable...
You can also modify the UPDATE statements so that they read:
UPDATE tbl
SET ...,
ab_timestamp = DEFAULT,
WHERE ...
I believe this syntax is portable.
(Yes, I know 'timestamp' is deprecated in MS SQL and that I should use
datetime instead, and in fact have already done so.)
timestamp is not deprecated in SQL Server, but it's a completely different
data type, which is used to implement optimistic locking. A timestamp
is an 8-bit value is updated each time the row is updated and it's
unique within the database. Further more timestamp value are monotonically
increasing. But there is no correlation with time. Timestamp is
proprietary to SQL Server, so you should probably stay away from it
entirely.
2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.
Not a separate script, but a separate batch. Batches are separated with
"go" in all query tools. The graphic tools permit you to specify a
different batch separator.
Why? Because else it would be difficult to tell where a procedure ends.
Say that you have:
CREATE PROCEDURE .... AS
....
CREATE TABLE ....
Is that CREATE TABLE part of the procedure or not? (Yes, if you have
BEGIN END it's clear. But of legacy BEGIN END is not required in
stored procedures.)
3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
As noted above, there is little reason for your to use the timestamp
data type.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: cdr |
last post by:
I have been ask to 'port' my asp applications from a windows/iis/sql
box to a unix/linux box...running apache with mysql and chiliASP
First of all, is this possible, with a 'little' or a 'lot' of code
changes in database calls?
Second the unix box is 1200 mi away. Are there windows or browser utilities
to allow for things such as data...
|
by: Massimo Fiorentino |
last post by:
Hello there!
I am a bit of a newbee into the mySQL world and I have a question regarding
switching from one DB to another. I have for a couple of years used a very
simple CMS-system created by myself but now I want to move to textpattern
(http://www.textpattern.com/). Using a mySQL tool called NaviCat, I can
easily create queries regarding...
|
by: Jonathan |
last post by:
I have to port a MS Access DB to anotherdatabase engine and I would
like to use MySQL because I am familiar with it and is easy to
integrate with the web and php what is the ned point of my 'quest'.
However there are paramter queries in the access database and I would
really like to keep the access database as a backup as long as I'm in
the...
|
by: Errol Neal |
last post by:
Hi all,
Not sure if this is a question for a php list or this one, but I'll give it
a shot and
if I am wrong, please do not crucify me. :-)
There is a php based sourceforge project called mailwatch.
(http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
Mailscanner security product into a mysql database. Now, I...
|
by: Chris Travers |
last post by:
Hi all;
A few years ago, I set about porting a PHP application from MySQL to
PostgreSQL, after realizing that MySQL wasn't going to be able to handle it.
In order to do this, I built a light, fast database abstraction layer which
conforms to the behavior of the MySQL functions in PHP. This means that a
large amount of porting work could be...
| |
by: Ted |
last post by:
Understand, I have developed a number of applications using RDBMS,
including MySQL, PostgreSQL and MS Access, but this is my first
experience with MS SQL. I'd bet my bottom dollar that MS SQL supports
what I need, but I just haven't found where it is explained in any
detail in the documentation I have. The pages I have found strike me
as a...
|
by: Elizabeth Barnwell |
last post by:
This is a tool for learning the PHP programming language:
http://www.yoyobrain.com/subjects/show/3120
You can look through the tabs to find information, and you can have
the site quiz you to help you retain more of the language. Our
developers have found this method useful, and are building YoYoBrain
to help others learn prorgmming...
|
by: Andrey |
last post by:
Hi,
I will be hiring a php guru to help us architect a highly scalable web
site/web application; the problem is I am coming from Microsoft .NET
world and not too much familiar with the platform.
What kinds of questions would you advice to ask the person on the
interview to see if he/she is:
1. Proficient with php
2. Proficient with MySQL...
|
by: ssnaik84 |
last post by:
Hi Guys,
Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of things (stored procedures, functions).. we have to manually edit.
That time, we face some interesting challenges.. I failed to document all of them,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |