INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Automatic Timestamp?

Automatic Timestamp?

(OP)

I am using UPS WorldShip to write to a Pervasive V8 table. After each shipment, the software sucessfully writes the fields that I specified to correct columns in the table. The WorldShip software, however, does not have a time or date field anywhere that I can find that I can use to timestamp shipments. Nor can I change the query that WorldShip uses to write to the table.

My question to you then is this: Can I create a column in my table that will automatically fill in the date and time, a "timestamp", for each row that is written?

It would be similar to a numeric auto-increment column that 'adds one' each time a row is added.

Thanks in advance!

RE: Automatic Timestamp?

You might be able to add a Timestamp column.  it might affect any other applications using the table though. For example, Btrieve applications that also use that table.  
Here's a sample CREATE TABLE that creates a Timestamp column and sets the default value so you don't have to specify anything on an Insert or Update:
CREATE TABLE Tab8
(col8 TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Automatic Timestamp?

(OP)
Thanks for getting me going, but I can't make it work.

Using Pervasive Control Center and Data Manager, I tried creating the table with no luck, syntax error. Putting 'CURRENT_TIMESTAMP' in single quotes, got me a little further, invalid timestamp. So I see where sending it a default timestamp would work, I can not seem to create one, or get google to retreive me the proper format. I read about septa-seconds, and tried a default of '2011-01-01 12:00:00' Invalid date, time or timestamp value.

So, I added a column to my existing table, with a type of 'timestamp':

INSERT INTO "TrackingNumbers" VALUES('12345','67890','1234567890','Joe Blow','1.5','1','Ground','2007-5-21 17:17:51')
Invalid date, time or timestamp value.

Then if I try to specify a default value in the table design, I get an 'Error in assignment' if I leave it NULL:

ODBC Error: SQLSTATE = 22005, Native error code = -1065 Error in assignment.

For everything else, I get:

You provided value "xxxx" for cell (xxxx) which is invalid or out of range for this type (SQL_TIMESTAMP). Enter a value that is within the supported range for this datatype.

But even if I do find the proper syntax and default value, it's not going to help me unless I can send the field a null, or garbage, and it (the table) populate the current time.

 

RE: Automatic Timestamp?

Looks like the Pervasive documentation is wrong.  The statement should be:
CREATE TABLE Tab8
(f1 char(10), col8 TIMESTAMP default now())
With the now() as the default, it will write the current timestamp on insert even if nothing is specified.   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Automatic Timestamp?

(OP)
CREATE TABLE TrackingNumbers
(OrderNumber CHAR(10) NOT NULL,
PONumber CHAR(25),
TrackingNumber CHAR(25),
CustomerName CHAR(25),
Weight DOUBLE,
NumberPkgs INTEGER,
ServiceType CHAR(10),
DateShipped TIMESTAMP DEFAULT now())

ODBC Error: SQLSTATE = 23000, Native error code = 0
No default value assigned for column <DateShipped>.
 

RE: Automatic Timestamp?

I've been running with V11 thinking it was all the same.  I see you're running v8.  Are you using v8.7 or some other version?  

You might try:

CODE

CREATE TABLE TrackingNumbers
(OrderNumber CHAR(10) NOT NULL,
PONumber CHAR(25),
TrackingNumber CHAR(25),
CustomerName CHAR(25),
Weight DOUBLE,
NumberPkgs INTEGER,
ServiceType CHAR(10),
DateShipped TIMESTAMP DEFAULT CURRENT_TIMESTAMP())

I know that the two parenthesis after CURRENT_TIMESTAMP may not seem important but the v8 docs indicate they are needed.  
I don't have a V8 machine to test with so I'm kind of guessing.  Any chance of upgrading to something a little newer?   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Automatic Timestamp?

(OP)
Looks like I have v8.6. I don't know if an upgrade will be feasable in our future or not because of the software that is using Pervasive, I will have to look into it.

This table is in a seperate database that I setup just for our web-apps that we use to 'gather more info', so I'm not worried about our software accessing it.

I didn't realize 'current_timestamp' was reserved until I saw it in blue. I saw another post here on tek-tips with alternatives:

Here is what I get when I run the create table with current_timestamp, current_date, or current_time:

CODE

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: CREATE TABLE ... DateShipped TIMESTAMP DEFAULT CURRENT_TIMESTAMP<< ??? >>())

... DateShipped TIMESTAMP DEFAULT CURDATE(), CURTIME(), and NOW() all get me:

CODE

ODBC Error: SQLSTATE = 23000, Native error code = 0
No default value assigned for column <DateShipped>.

Tried it with and without the ()'s.

I abandoned the timestamp for both CHAR and DATE and still can't get it:

'DateShipped CHAR(20) DEFAULT now()' returns the same 'No default value assigned'

(sigh)

RE: Automatic Timestamp?

Okay, I guess my timeline was off.  PSQL v8 does not support any scalar function as a default value.  PSQL v9.0 adds this functionality.
It looks like it's not going to work without changing the INSERT statement.   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close