Automatic Timestamp?

[SuperComputing]

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!

 

[mirtheil]

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

 

[SuperComputing]

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.

 

[mirtheil]

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

 

[SuperComputing]

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>.

 

[mirtheil]

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:

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

 

[SuperComputing]

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:

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:

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)

 

[mirtheil]

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