How to do with tables in my database are growing day by day.

[duongduong12345]

Dear all,

Here is my situation. I have a constantly growing table of records with a date/time field.

I want to move some records with old date/time to another tables in new folder and available move back when i need. (It's same Bakup, but not entire table, just some records in table and database files.).

But when i try to do, the problem is relationship in root database not have effect on new tables in new folder.

How should i do ?

 

[mmerlinn]

Its going to be hard to point you in the right direction unless we can see what you are doing. Please post the relevant code showing how you are relating your tables and how you are accessing them.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Olaf Doschke]

I have no idea what you mean, and I think I have no chance to see, as you don't post your code?

Do you create backup dbfs via COPY TO? Do you first run GenDBC to copy the root database structure?

Or are you one of those meaning a table, if they talk about a database (That was the term way back then for single tables, too, as there was no dbc).

Bye, Olaf.

 

[duongduong12345]

Dear all,

Below are relevant code:

***************
* Moving (store) which records for date/time field = _nam to new table in new folder.
***************

*------------------------------
Procedure _copydata && First: Copy database (.dbc & .dct)
Copy File Data\data1.Dbc To tm_moveappend+'\'+"data1.dbc"
Copy File Data\data1.dct To tm_moveappend+'\'+"data1.dct"
Return
*------------------------------
Procedure _copytable && Second: Copy entire tables and .cdx and .fpt
Parameters tenfile, namxoa

Wait Window "Store ... Data\"+Upper(tenfile)+" ---> " +tm_moveappend Nowait

nvlHandle = Fopen("Data\&tenfile..dbf",12)
If nvlHandle < 0
Fclose(nvlHandle)
chuoiban=chuoiban+', '+'&tenfile'
Else
Fclose(nvlHandle)
Copy File Data\&tenfile..Dbf To tm_moveappend +'\'+"&tenfile..dbf"

If File("data\&tenfile..cdx")
Copy File Data\&tenfile..Cdx To tm_moveappend +'\'+"&tenfile..cdx"
Endif

If File("data\&tenfile..fpt")
Copy File Data\&tenfile..fpt To tm_moveappend +'\'+"&tenfile..fpt"
Endif
Endif
Return
*---------------------------------
Procedure _delete
Parameters tenfile, namxoa, filecon1, filecon2

* Delete all records for date/time field =_nam in root tables.
Select 0
Use Data\&tenfile
Delete All For &namxoa = _nam
Use

* Delete all records for date/time field <> _nam in new tables.
Select 0
Use tm_moveappend+'\'+"&tenfile..dbf" Exclusive
Delete All For &namxoa <> _nam
Pack
Use

If !Empty(filecon1)
Select 0
Use tm_moveappend+'\'+"&filecon1..dbf" Exclusive
Pack
Use
Endif

If !Empty(filecon2)
Select 0
Use tm_moveappend+'\'+"&filecon2..dbf" Exclusive
Pack
Use
Endif

Return

 

[Olaf Doschke]

You posted several procedures, now how do you use them?

Bye, Olaf.

 

[duongduong12345]

Hi Olaf;

I use them sequence as above:
-----------
Do _copydata
Do _copytable With Alltrim(table_name), Alltrim(field_year)
Do _delete With Alltrim(table_name), Alltrim(field_year), Alltrim(father_of1), Alltrim(father_of2)


-----------
* table_name is the name of father table.
* Father_of1 and father_of2 are the name of child table

Thanks

 

[Olaf Doschke]

OK, and I assume you expect these lines to not only delete in the father, but also in the child tables? Is that the problem?

Use Data\&tenfile
Delete All For &namxoa = _nam

DELETE can work in child tables via SET RELATION, which you don't do, or via delete-trigger in father table.

As you copy the dbc (by the way the dcx file should also be copied) you also copy table relations and stored procedures containing and executing the refential integrity rules.

Does this at least work for one of the two databases? If so, then switch to the other one via SET DATABASE, before you delete from that database, otherwise the triggers call code from the wrong database, working in that tables, problem is databases only differ in location, not in table names and trigger names. So triggers trigger the code in the active database, which in turn open tables from that database.

Bye, Olaf.

 

[Olaf Doschke]

I would go about this different, and in the first place execute gendbc generated prg once to generate the empty backup database, then only copy the data into it you want copied and delete it from the original database, no need to delete from the archive database, as you only copy to it what you want in it.

Bye, Olaf.

 

[JRB-Bldr]

I want to move some records with old date/time to another tables in new folder and available move back when i need.

tables in different directories??? - The first thing that I would question is your data table architecture.

It sounds as though you might be using a not-very-good data table architecture.

You should be using a normalized data table architecture.

If your data tables are not well normalized, I'd first correct that.

Then, after that was in place, I'd look at getting your archive routine to work with it.

Finally I'd look into how to not need to move some records with old date/time to another tables in, but instead have your application retrieve the appropriate data records directly from the archive tables and work with them as needed.

Good Luck,
JRB-Bldr