×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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

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

(OP)
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

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

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

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

(OP)
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

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

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

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

(OP)
Hi Mirtheil,

Yes, it passed consistency checks after the columns were added

Regards

Jeremy

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

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
http://www.mirtheil.com

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

(OP)
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

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

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

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

(OP)
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

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

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

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

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

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

Did you get any further with this?

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

(OP)
Tom,

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

Regards

Jeremy

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

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

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

(OP)
Tom,

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

Regards

Jeremy

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

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

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

(OP)
Tom,

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

Cheers

Jeremy

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

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
 

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

Sorry about that.  Tom is right there DDFs (FILE, FIELD, and INDEX) describe each table and present the logical view of each Btrieve file.  Each Btrieve file is typically defined as one table within the DDFs and a set of DDF defines multiple tables.  You can get the file name of the underlying Btrieve file in the Properties tab. Also, BUTIL is a command line tool provided by Pervasive and is installed in the PVSW\BIN directory (along with the rest of the Pervasive components).

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close