Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Drop and delete tables from a database

  1. #11
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    37
    Quote Originally Posted by barrymavin View Post
    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
    No! The structure of the new table is different to the old one. So I have to do it field by field and record by record...
    Thanks
    Stefan

  2. #12
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Can you explain in the terms of southwind database tables what do you want to achieve?
    Maybe there is a simpler way.

    For example,
    after converting data VFP-> Lianja you got the table CUSTOMERS in Lianja database, right?
    And you need to change the field REGION from c(15) to integer, or something?
    Or you need to move out the field REGION into the table EMPLOYEES ?

    ...something like that, explaining you intentions.
    Last edited by josipradnik; 2020-12-03 at 03:21.

  3. #13
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    37
    Quote Originally Posted by josipradnik View Post
    Can you explain in the terms of southwind database tables what do you want to achieve?
    Maybe there is a simpler way.

    For example,
    after converting data VFP-> Lianja you got the table CUSTOMERS in Lianja database, right?
    And you need to change the field REGION from c(15) to integer, or something?
    Or you need to move out the field REGION into the table EMPLOYEES ?

    ...something like that, explaining you intentions.

    I'll try my best.

    Using the fictitious example of CUSTOMER:

    1. I have manually created a database "VFPSOUTHWIND" in the data workspace.
    2. I dragged and dropped all tables of the Southwind database from VFP into this database.
    3. I have manually created a database "SOUTHWINDNEW" in the data workspace.
    4. I want to use a script to import data from the manually imported table VFPSOUTHWIND.CUSTOMER into several new tables to be created in the SOUTHWINDNEW database.

    I have to do this because:
    - Data that should now be split into several NEW tables in the manually created database SOUTHWINDNEW.
    - New fields are added in the new table CUSTOMER, which in turn come from other import tables.
    - other column changes take place (I could of course do that with ALTER.

    As a result, I have e.g. in the new database:
    - CUSTOMER ADDRESS -> Name and Address
    - CUSTOMERPHOTOS -> Photo-Field
    - CUSTOMER -> all the rest plus new fields

    not that this would make sense in terms of content in THIS example.

    Note: I dragged and dropped the original VFP database instead of accessing it via ODBC (I just don't even know how to do it ), because otherwise I don't need it either and I wanted to make sure that I didn't tear up the original tables and database.


    BTW: I make this example now with employees of the southwind database. Exept: Origin was the lianja version and I don't split the data into several tables. Just 1:1 only to reproduce the behaviour. Her is my COde:

    Code:
    // Import from testdb (with Lianja table employees) into the new database testdbnew
    set debug on 
    SET DATE to german
    SET CENTURY ON
    SET HOURS TO 24
    SET SECONDS ON
    
    set separator to "."
    set point to ","
    // ***********************************************************************
    // close all DBs, open new database
    // ***********************************************************************
    close databases all 
    open database testdbnew exclusive 
    // ***********************************************************************
    // Open Import-Table
    // ***********************************************************************
    if used("impemployees")
    	close impemployees
    endif 	
    use testdb!employees alias impemployees in 0 shared
    // ***********************************************************************
    // create new table in new database
    // ***********************************************************************
    wait wind "Create Employees..." nowait noclear
    if used("employees")
    	close employees
    endif 	
    drop table testdbnew!employees
    create table testdbnew!employees ;
    	(employeeid Numeric(10) Description "Employee ID" default 0, ;
    	lastname Char(20) Description "Nachname" default "", ;
    	firstname Char(10) description "Vorname" default "", ;
    	title Char(30) description "Anrede" default "",;
    	titleofcourtesy Char(25) description "Titel" default "", ;
    	birthdate DATE description "Geburtsdatum" default {}, ;
    	hiredate DATE description "Angestellt seit" default {}, ;
    	address Char(60) description "Straße" default "", ;
    	city Char(15) description "Ort" default "", ;
    	region Char(15) description "Region" default "", ;
    	postalcode Char(10) description "PLZ" default "", ;
    	country Char(15) description "Land" default "", ;
    	homephone Char(24) description "Telefon" default "", ;
    	extension Char(4) description "Durchwahl" default "", ;
    	notes LONG VARCHAR description "Notizen" default "", ;
    	reportsto Numeric(10) description "?" default 0, ;
    	photo Blob description "Foto")
    
    // ***********************************************************************
    // Import of data
    // ***********************************************************************
    
    select impemployees
    lcGesamt = allt(transform(reccount(impemployees), '9.999.999'))
    lnAkt = 0
    
    scan all 
    	lnAkt = lnakt + 1 
    	lcWaitText = "Import - Employee " + trans(impemployees.employeeid) + ", " + allt(transform(lnAkt, '9.999.999')) + " von " + lcGesamt
    	wait wind lcWaitText nowait noclear 
    	insert into employees (employeeid) values (impemployees.employeeid)
    	replace	in employees 	lastname 				with impemployees.lastname,;
    					 		firstname 				with impemployees.firstname,;
    							title					with impemployees.title,;
    							titleofcourtesy 		with impemployees.titleofcourtesy,;
    							birthdate 				with impemployees.birthdate,;
    							hiredate	 			with impemployees.hiredate,;
    							address	 				with impemployees.address,;
    							city	 				with impemployees.city,;
    							region	 				with impemployees.region,;
    							postalcode 				with impemployees.postalcode,;
    	 						country	 				with impemployees.country,;
    							homephone 				with impemployees.homephone,;
    							extension	 			with impemployees.extension,;
    							notes		 			with impemployees.notes,;
    							reportsto	 			with impemployees.reportsto
    	select impemployees
    endscan 
    wait clear
    
    =messagebox("Finish!", 0+64, "Dataimport")
    close databases all 
    return
    Thanks
    Stefan

  4. #14
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    In 5.6 drop table have been enhanced to support database!table but in fact that is not valid in VFP.
    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

  5. #15
    Member
    Join Date
    May 2013
    Location
    Bergkamen, Germany
    Posts
    37
    Quote Originally Posted by barrymavin View Post
    In 5.6 drop table have been enhanced to support database!table but in fact that is not valid in VFP.
    Thanks barrymavin.

    I never said this was like VFP. "As in VFP", it was all about "browse()" and record pointers ...

    Here I did it according to the Lianja documentation. It just doesn't say that you have to do a "REBUILD databse" afterwards. That's all.
    But it claims, with the parameter "NODELETE" the file will NOT be deleted. That implies that it will be deleted without parameters.

    In my ticket I described that there was an error with "DROP database! Table". However, the ticket clearly referred to the fact that the table ONLY disappeared from the database after a "DROP table", but was not deleted as a file. That was all. :-)

    I'm sorry for the confusion.
    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