Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Drop and delete tables from a database

  1. #1
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    37

    Drop and delete tables from a database

    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.
    Thanks
    Stefan

  2. #2
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Try this:
    Code:
    if used("myTable")
        close myTable
    endif     
    drop table myDatabase!myTable
    See: http://www.lianja.com/community/show...previous-table
    Last edited by josipradnik; 2020-12-01 at 09:49.

  3. #3
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    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?
    Last edited by zehnernet; 2020-12-02 at 00:47.
    Thanks
    Stefan

  4. #4
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    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.
    Last edited by josipradnik; 2020-12-02 at 09:10.

  5. #5
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,071
    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

  6. #6
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  7. #7
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    37
    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...
    Last edited by zehnernet; 2020-12-03 at 01:40.
    Thanks
    Stefan

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    Hi Stefan

    DROP TABLE targettable

    not

    DROP targettable
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    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
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  10. #10
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    37
    Quote Originally Posted by barrymavin View Post
    Hi Stefan

    DROP TABLE targettable

    not

    DROP targettable
    Yes typo in here. It's fake code...
    Thanks
    Stefan

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us