I am running ASA 7 and using ISQL to select all fields from a table using a simple select clause. After I get the results I write them to file using the OUTPUT command. One of the columns I am selecting is a timestamp type with a default value of CURRENT_TIMESTAMP. All values get added to the DB using DEFAULT in the INSERT statement. This timestamp column is a key part of the primary key.

My problem arises when I try and reload these records into a new DB. I am getting a -193 error (primary key not unique) due to the fact that the timestamp values are identical in some cases. For instance, I am seeing 2 records that in the reload file containing a timestamp that looks like:

2010-04-12 14:21:36.234

I am wondering how the original DB ever was able to add these two records due to the fact that the timestamp is also the primary key in that DB. I'm thinking it may be a precision thing whereby ISQL is chopping off the miliseconds part of the timestamp and it should be something like:

2010-04-12 14:21:36.23450 (note the extra 2 digits)

Is this possible? Is there a way to select them out of the DB with more precision so I don't encounter the -193 on the INPUT statement?

Thanks!!!!

asked 22 Jun '11, 11:45

Codecranker's gravatar image

Codecranker
466232535
accept rate: 0%

edited 22 Jun '11, 17:25

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676


It's entirely possible that's what you're seeing (i.e. the timestamp is being truncated on output)

Have a look at the timestamp_format option to see what alternatives are available.

permanent link

answered 22 Jun '11, 12:33

Phil%20Mitchell's gravatar image

Phil Mitchell
1.9k1831
accept rate: 27%

1

...and use

select connection_property('timestamp_format')

to find out the current setting in the old database.

(22 Jun '11, 12:48) Volker Barth

I set the property to use ss.sssss (all 5 on the milliseconds) but it did not affect the output.

(22 Jun '11, 12:50) Codecranker
Replies hidden

That's unusual, AFAIK.

Make sure you set this for the appropriate level (i.e. current connection, PUBLIC group, whatsoever).

I would try the above select ... in DBISQL (just before doing the outptu) to make sure the option is set as wanted.

(22 Jun '11, 13:06) Volker Barth

What API are you using from your application to connect to the database?

(22 Jun '11, 15:40) Glenn Paulley
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×25
×22
×5

question asked: 22 Jun '11, 11:45

question was seen: 1,228 times

last updated: 22 Jun '11, 17:25