Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
JDPglenman

JDPglenman

IS-IT--Management
Jan 23, 2007
16
IE
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
 
Sort by date Sort by votes
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

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Hi Mirtheil,

Yes, it passed consistency checks after the columns were added

Regards

Jeremy
 
Upvote 0 Downvote
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.
Code: 
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:
Code: 
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
 
Upvote 0 Downvote
Mirtheil,

Here is the ALTER TABLE Statement:

Code: 
ALTER TABLE Projects ADD COLUMN ContractDURUnits varchar(10)

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

Cheers

Jeremy
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
The record length is: 2941
The Field adjacent to the new fields is called ParentID and is type Integer
 
Upvote 0 Downvote
Tom,

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

Regards

Jeremy

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Tom,

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

Regards

Jeremy
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Tom,

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

Cheers

Jeremy
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ianbtheog
  • Locked
  • Question
Error Code 7009: Data is unavailable when using DTO
Replies
3
Views
533
mirtheil
mirtheil
michellepace
  • Locked
  • Question
Basic SQL: Add column to select statement (ie value from another table)
Replies
1
Views
410
michellepace
michellepace
Ambientguitar
  • Locked
  • Question
Need help opening a Pervasive Database or BTR files
Replies
3
Views
674
mirtheil
mirtheil
Alvin Chong
  • Locked
  • Question
Invisible Space In my PSQL database
Replies
7
Views
604
Alvin Chong
Alvin Chong
deathseeker
  • Locked
  • Question
Normalize database (unpivot month columns)
Replies
0
Views
183
deathseeker
deathseeker

Part and Inventory Search

Sponsor

Back
Top