My application uses an ODBC connection to insert data into a 2000i SP4
database table acquired from external industrial equipment. A totalizer
register in one field device exceeded 32767 for a column defined as a real
value and the inserts began to fail with a column data range exceeded type
error. This occurred over a weekend and the plant personal did not catch
the error for 2 days. After poking around a bit, I found a series of log
files in the pvsw\bin\mkde\log folder. Browsing these files with a text
editor shows several entries for the table but the data is represented in
binary and therefore is not readable. We desperately need to view certain
table column values from the missing days for governmental reporting
purposes. I understand that the logs are associated with the Transaction
Durability feature, but if a column data type value is exceeded, restarting
the database engine would not allow the records to be rolled into the table.

My questions are:

1. Would an SQL insert failure, due to an exceeded column value, result in
the data being written to the mentioned logs?

2. If the data is in the logs, is there any browser utility that can
display the log contents in readable format?

3. If the logs cannot be viewed, can anyone provide a layout of these logs
so I could access them programmatically?

4. If there is no way to access the logs external from Pervasive, can I
alter the table column from a real data type to a long integer and force a
Pervasive recovery?

My environment is:
WinXP, SP1
2000i SP4 Workgroup license.

Thanks to anyone that can assist us with the recovery of this critical data.

Kelly W. Zini
Control Products Unlimited, Inc.

If the failure was a SQL-level INSERT statement, then the data changes
never made it to the Microkernel level, which is what the MKDE Logs are
there for. Since the data inserts were never made, the MKDE never saw
the changes, and they are lost. It is up to the application to
properly detect such limitations and fail properly.

The only way in which this information could have been trapped is with
the use of an external auditing tool, something like Pervasive's
AuditMaster tool. However, AuditMaster does not work with the older
PSQL2000i engine.

Since it is your own application, the best bet is to log the data into
a running log file in text format as a backup to the database. If the
data is valuable, the ability to archive all data on a daily basis will
provide a lot of added value, with little extra overhead.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Sydney: Pervasive.SQL Service & Support Class - 02/27/06 ***
*** Chicago: Pervasive.SQL Service & Support Class - 03/27/06 ***

Kelly W. Zini wrote:

Thanks for the prompt response Bill. Actually my application does log all
failed statements to a text log file, but unfortunately my client had left
the "debug" mode active which writes a considerable amount of info to the
log file each minute. This resulted in the majority of the 2 day's data
falling off in the old bit bucket. I am considering moving the debug
information to a unique log to prevent this from occurring in the future.

Thanks again,

Kelly

"Bill Bach" <goldstar (AT) speakeasy (DOT) net> wrote

I am a little confused about the question. Real data types are not
limitted to 32K for a data range. It actually sounds more like a
Auto-Inc field which is actually an integer type that can be 2 or 4
bytes. And the statement about 32K would lead me to believe it was a
2 byte autoinc.

1) I agree with Bill on this one, if the data range ran out, then the
insert would not have happened.

2) If you are are talking about the database engine transaction logs
the insert may (but probably not) still be in the transaction logs.
The transaction logs are very temporary usually only sticking around
until the data in the transaction is committed to the data file.
Typically this is on the order of a minute or so.

3) The database engine logs probably are not there to read. They are
supposed to go away in a brief time period. You would probably need
to implement your own logging. As Bill indicates a seperate flat file
is typically low overhead, simple to implement and reliable.

4) Changing data types for the column in question sounds like it would
be a good idea to prevent this from happening in the future. To the
best of my knowledge there is no way to "force a recovery". The
application would have needed to catch the exception when it happened
for this scenario.

There is "Archival Logging". However archival logging has to be
configured in advance and is not well suited to 24x7 operations. Even
if you had archival logging configured in advance I do no believe you
could change the file before rolling the archival log forward. I know
the file structure has to match, but I wold not bet business critical
data integrity on it even just changing file contents (unlogged)
before rolling the archival log in.

Leonard

On Mon, 23 Jan 2006 16:17:00 -0600, "Bill Bach"
<goldstar (AT) speakeasy (DOT) net> wrote:

A few comments, inline below.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Sydney: Pervasive.SQL Service & Support Class - 02/27/06 ***
*** Chicago: Pervasive.SQL Service & Support Class - 03/27/06 ***

Leonard wrote:

My guess, without seeing the DDF, is that the field was a SMALLINT
field. Doesn't need to be AutoInc to exceed maximum values. Of
course, changing it to a USMALLINT may be appropriate. If you recall a
few years back, a similar problem took down an airline running on a
Unix (i.e. non-Btrieve) database.

As I mentioned in the comment about TransLog, the Insert probably never
happened. If the insert failed, it would not be in the Trans Log. It
would also not be caught in the Archive log, and even AuditMaster might
not be able to trap it.