Drop and delete tables from a database

**zehnernet** · 2020-12-01, 07:23

Hi there,

what's going wrong at this point?

Code:

open database myDatabase
if used("myTable")
	use in myTable
endif 	
drop table myDatabase!myTable

The table ist still in the database and as well as a file in the data folder.

**josipradnik** · 2020-12-01, 10:41

Try this:

Code:

if used("myTable")
    close myTable
endif     
drop table myDatabase!myTable

See: http://www.lianja.com/community/show...previous-table

**zehnernet** · 2020-12-02, 01:37

Thanks, josipradnik!

Maybe it would be a nice idea to combine "USE" under "See also" with "CLOSE" in the documentation.

Code:

if used("myTable")
	close myTable
endif 	
drop table myDB!myTable

Result: The table is out of the database container. It's still on the hard drive.

Target was to DELETE it. Like I can do in workarea Data, right click on a table -> Delete... Only I want to do it programmatically.
Therefore I found in the documentation "DROP" (DROP TABLE [<database>!]<table> [NODELETE]). Using it without "NODELETE" should do that.

So, sorry, but my question again: What's going wrong here?

**josipradnik** · 2020-12-02, 10:07

When I do not use "myDB!" in DROP TABLE, the file is gone.

Try

Code:

if used("myTable")
    close myTable
endif     
drop table myTable

and submit the ticket for bug for not erasing the file without NODELETE.

**HankFay** · 2020-12-02, 13:43

Hi Stefan,

Looks like you should send in a ticket. a) recreate by creating a database (create an empty database, copy the employees files over from the Southwind database, rebuild the database) b) executing the set of steps If it replicates, c) write up a ticket with all the steps taken for replication.

Hank

**barrymavin** · 2020-12-02, 21:59

Hi Stefan,

DROP commands (TABLE INDEX etc) that operate on database objects e.g a TABLE should not be prefixed with name! as that is a path name.

**zehnernet** · 2020-12-03, 02:34

Oh man! Actually, I assume that as a newbie I am making mistakes here and not that there is a bug ...

Anyway, I'll try to create an example with the southwind database.
First of all, an overview of what I'm trying to do here. Maybe an example is superfluous because I am doing something fundamentally wrong, because it is different in Linja compared to VFP.

I have a VFP database that I've simply dragged and dropped into Lianja. I would like to transfer the data from this database to a new, existing Lianja-Database. The whole thing has to run programmatically because the database design is to be changed. Means that fields of one table come into other tables, etc..

Here is a rudimentary code:

Code:

CLOSE DATABASES ALL
OPEN DATABASE targetdatabase EXCLUSIVE
...
USE sourcedatabase!Sourcetable IN 0 SHARED
... further source tables are opened
IF USED ('targettable')
     CLOSE targettable
ENDIF
DROP targetdatabase!Targettable               // removed from the database, but not deleted on harddrive. Or:
DROP targettable                              // Error: Permission denied
                                              // I just don't get this error if I close all databases outside of my code in the App Builder on the data workspace. 
                                              // A CLOSE DATABASES ALL in the program is apparently not enough ...
CREATE TABLE targetdatabase!Targettable (...) // probably also without "targetdatabase!" possible...
SELECT sourcetable
SCAN
     ... shovel data into the newly created table
ENDSCAN

RETURN

With the indication of the "databasename!" I always played around to avoid deleting or writing the wrong table. Most of the names of the target tables are identical to the source tables...

**barrymavin** · 2020-12-03, 02:49

Hi Stefan

DROP TABLE targettable

not

DROP targettable

**barrymavin** · 2020-12-03, 02:51

And by the way... you don'y need any SCAN ENDSCAN for that

and VFP table can be converted just by use COPY TO.

use the_VFP_table
copy to the_LIANJA_table