Newly added column has copy of random data from other columns - help

[JDPglenman]

When I add a new column to an existing table the new column is automatically populated with what appears to be random copies of data in other columns in the same table.

It has happened twice - the first time with longvarchar type columns and the second more recent one with varchar (length 50)

I would appreciate any advise anyone can give.

Jeremy

 

[TomKane]

Hi,

When you say you are adding a new column to one of your tables, are you saying you are changing your DDF?

Would it be possible to see the before and after changes you have made?

Regards,
Tom

 

[JDPglenman]

Tom,

Thanks for your rapid response.

I have never been near the DDF (as far as I know). I added the columns to the table using ALTER TABLE sql statement.

I'm not sure I am able to give you a before and after scenario - other than a list of columns in the table. For what it is worth the table has 104 columns in it!

I appreciate your assistance

Regards

Jeremy

 

[mirtheil]

Are you issuing and ALTER TABLE statement with an IN DICTIONARY clause?
Are you trying to add fields to the DDFs that already exist in the Btrieve file?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JDPglenman]

Mirtheil,

No, I am not issuing the ALTER TABLE statement with an IN DICTIONARY clause.

Let me try an lay down the exact steps I have followed to date:

1. I created the table from within the Pervasive Control centre using SQL (not the GUI)
2. I imported all of the data from an existing MS Access .mdb file.
3. Using the ALTER TABLE statement I added the new columns to the existing table

I have not touched the DDFs

Thanks for your assistance

Regards

Jeremy

 

[mirtheil]

Actually, issuing an ALTER TABLE will modify the DDFs.
Do the DDFs pass a COnsistency Check before or after you issue the ALTER TABLE statement?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JDPglenman]

Hi Mirtheil,

Yes, it passed consistency checks after the columns were added

Regards

Jeremy

 

[mirtheil]

Did it pass the consistency check before you issue the ALTER TABLE?
Post your ALTER TABLE statement.
I tried with the following simple statements and do not see the behavior you are seeing.

create table altertest (f1 char(10), f2 char(20), f3 char(30))#
insert into altertest (f1,f2,f3) values ('f1','f2','f3')#
ALTER TABLE altertest(add col1 INT, add col2 INT)#
select * from altertest#

With the results being:

Get Data All:
"f1", "f2", "f3", "col1", "col2"
"f1        ", "f2                  ", "f3                            ", <Null>, <Null>
1 row fetched from 5 columns.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JDPglenman]

Mirtheil,

Here is the ALTER TABLE Statement:

ALTER TABLE Projects ADD COLUMN ContractDURUnits varchar(10)

The problem only seems to occur in the one table (Projects)

Cheers

Jeremy

 

[mirtheil]

I'm not sure why it would be occurring. It's almost like there was a field there and by adding the column it got some of that old data.
You might use an UPDATE statement to clear it out. Something like:
UPDATE Projects set ContractDURUnits = ''
or
UPDATE Projects set ContractDURUnits = null
Depending on what you want to be in the field.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JDPglenman]

The problem only seems to occur for varchar and longvarchar type columns.

Your suggestion will probably be an adequate work-around but I am still a little worried about the root-cause of the problem.

Of all the tables in the database, the Project table is by far the largest with the most records (around 10,000) and the most number of columns (and hence the biggest record length). Could this have anything to do with it?

Regards


Jeremy

 

[mirtheil]

It could. What's the actual size of the record length? What's the field type before these varchar/longvarchar fields?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JDPglenman]

The record length is: 2941
The Field adjacent to the new fields is called ParentID and is type Integer

 

[TomKane]

Did you get any further with this?

 

[JDPglenman]

Tom,

No, I haven't had any success getting a resolution. Very frustrating!

Regards

Jeremy

 

[TomKane]

Jeremy,

What I'm wondering is whether your DDF is in synch with the underlying data table. The DDF is the definition of your table for the purposes of relational/SQL access.

You mention that the record length is 2941 - where are you getting that value from?

Regards,
Tom

 

[JDPglenman]

Tom,

In the Control Center, I right-hand click on the Table and select Properties.

Regards

Jeremy

 

[mirtheil]

What's the record size and page size for the underlying Btrieve file? Can you post a BUTIL -STAT report?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JDPglenman]

Tom,

I'm probably showing my ignorance with this question!!! Is there meant to be one DDF file per table?

Cheers

Jeremy

 

[TomKane]

Hi Jeremy,

The DDF has the information for all the tables within each 'database'.

What Mirtheil is asking is whether the table properties in the control center agrees with the BUTIL -STAT report - if there is a difference in could explain why you are having data problems.

Regards,
Tom