Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: question about VFP remote view

  1. #1
    Senior Member
    Join Date
    Jul 2019
    Posts
    175

    question about VFP remote view

    Hi guys,

    is there a way to import VFP Remote View Table into the Lianja App Builder and make it into a Virtual Table?

    Thanks & Best Regards

  2. #2
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,924
    This page covers importing VFP databases and tables: https://www.lianja.com/doc/index.php...ses_and_Tables

    Hank

  3. #3
    Senior Member
    Join Date
    Jul 2019
    Posts
    175
    Hi Hank

    Thanks for the tip!

  4. #4
    Senior Member
    Join Date
    Jul 2019
    Posts
    175
    Hi Hank,

    Is there any reason why the table structure is empty when I import a remote view table from VFP Database to Lianja?

    Name:  Capture 0091.jpg
Views: 46
Size:  58.7 KB

  5. #5
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,099
    I would recommend these two articles:

    http://www.lianja.com/community/show...d-remote-views

    https://www.lianja.com/community/sho...-VFP-migration

    Q:
    I have an app containing approx 70 tables and 130+ local views.
    Lianja does not import a DBC's local and remote views.
    I assume that it is possible in Lianja to replace a DBC's views with local and remote virtual tables.
    how to work around the absence of views?
    A:
    The importers that are used to import databases and projects are all written in Lianja and the source code of these is included in the distribution. Look in the library directory for all files like vfp_import_*.prg.

    There is no reason why views could not be imported and a Virtual Table definition created for them insofar as the base tables are imported also. It's probably not a difficult task to accomplish as all the hard work is done in the vfp_import_dbc.prg script. All that is required is to recognize a view then create a virtual table with the same name as the view.

    Code:
    Code:
    CREATE VIRTUALTABLE viewname CONNSTR "local" AS sql_select_command
    A "local" CONNSTR causes the CursorAdapter to execute the SQL SELECT locally using the embedded database engine rather than connect remotely.

    The ALTER VIRTUALTABLE command can be used to alter any of the clauses used in the CREATE TABLE command including altering any properties such as those that exclude columns and define the basetable for inserts and updates.

    Special note: Remember a dbc database container in VFP is just a regular table so you can USE "fullpath_name.dbc" and inspect all of the columns from the console. Also, in Lianja you can inspect memo fields just by issuing ? nameoffield in the console.

  6. #6
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,924
    Hi WJ,

    In VFP, a Remote View is not a table. That's why you can't import it directory.

    However, you can use VFP's DBGETPROP() property to get all the information you need (except the connection string) to create a Virtual Table in Lianja. Or, for that matter, to have the SQL to use a SQLEXEC() where you aren't working in a UI.

    In VFP you can use ADBOBJECTS() to get an array of Views.

    Hank

  7. #7
    Senior Member
    Join Date
    Jul 2019
    Posts
    175
    Hi guys,

    Thank you so much for replying to my earlier message! May I know which part of the code I need to make some changes to in order to recognize the remote view table from VFP?

    //
    // Imports Visual FoxPro .dbc databases into Lianja
    //
    lparameter cFilename

    // initialization
    private cDir = justpath(cFilename)
    private cDatabase = juststem(cFilename)
    private cTimeline = set("TIMELINE")
    set timeline off

    ? "Importing Visual FoxPro database '&cDatabase'"

    // create the database
    create database "&cDatabase"
    open database "&cDatabase"

    // Open the VFP .dbc database container
    select 0
    use "&cFilename" alias dbc noupdate
    copy to array _tables fields objectid, objectname for upper(objecttype) = "TABLE"
    set order tag objecttype

    // foreach table in the database container
    for i = 1 to alen(_tables,1)

    // foreach field in the table add to the "fieldmap"
    release map
    declare map[]
    cTablename = trim(_tables(i,2))
    seek str(_tables(i,1))+"Field"
    do while not eof() and upper(objecttype) = "FIELD"
    map[] = trim(objectname)
    skip
    enddo

    // now all long field names are in the map[] dynamic array
    // they will automatically be mapped when he VFP table is opened
    set fieldmap to "map"


    // dbcgetprop() operates in a similar way to VFP dbgetprop() except that
    // it operates on the current cursor as Lianja supports opening the .dbc
    // files as tables and navigating through the records in them
    path = dbcgetprop(cTablename, "Table", "path")
    if len(path) = 0
    path = cTablename
    endif
    dbf = cDir + justfname(path)
    ?? "Importing table '&cTablename' from '&dbf'"
    select 0
    try
    use "&dbf"
    ? " " + etos(reccount()) + " records."
    wait "Importing table "+cTablename+" with "+etos(reccount())+" records" nowait
    copy to "&cTablename" with production
    catch
    ? " - failed to import table"
    endtry
    use
    set fieldmap to ""
    select dbc
    flush
    next

    // close the VFP .dbc database container
    wait clear
    use
    close database
    set timeline &cTimeline
    ? "Import of Visual FoxPRO database '&cDatabase' completed successfully"

    return .t.

  8. #8
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,924
    Hi WJ,

    a) get the sql out of the remote view using dbcgetprop()
    b) get other components out of the view, matching the argument of the CREATE Virtual Table command in SQL (the properties).
    c) get the connection string value being used in the remote view (which could be a connection object, in which case you need to work your way to it -- it's all in the VFP help)
    d) use the Lianja CREATE Virtual Table command to create the VT

    Do that for one view, manually.

    Then, using the ADBOBJECTS() array I mentioned iterate through all the views, having changed the code you used for the one view to use variables instead of values.

    Hank

  9. #9
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,099
    Hi,

    In VFP, do open a DBC example, i.e.
    Code:
    use "C:\Program Files (x86)\Microsoft Visual FoxPro 9\Samples\Northwind\northwind.dbc" EXCLUSIVE
    browse
    You'll see all Objecttypes values, including Table and View.

    In Lianja, do replace "TABLE" with "VIEW" in your code and see what you get and what you can use in Hank's suggestion.


    Code:
    USE select 0
    use "&cFilename" alias dbc noupdate
    copy to array _tables fields objectid, objectname for upper(objecttype) = "VIEW"
    Last edited by josipradnik; 2019-12-19 at 04:53.

  10. #10
    Senior Member
    Join Date
    Jul 2019
    Posts
    175
    Hi Guys,

    Can you guys please help me check my code because the console output is still showing blank.

    //
    // Imports Visual FoxPro .dbc databases into Lianja
    //
    lparameter cFilename

    // initialization
    private cDir = justpath(cFilename)
    private cDatabase = juststem(cFilename)
    private cTimeline = set("TIMELINE")
    //set timeline off

    ? "Importing Visual FoxPro view from database '&cDatabase'"

    create database "&cDatabase"
    open database "&cDatabase"


    // Open the VFP .dbc database container as a table
    select 0
    use "&cFilename" alias dbc noupdate
    brow

    copy to array _views fields objectid, objectname for upper(objecttype) = "VIEW"
    set order tag objectname

    // foreach view in the database container
    for i = 1 to alen(_views,1)

    //for i = 1 to 10
    release map
    declare map[]
    cViewname = trim(_views(i,2))
    seek str(_views(i,1))+"Field"
    do while not eof() and upper(objecttype) = "FIELD"
    map[] = trim(objectname)
    skip
    enddo

    set fieldmap to "map"
    // dbcgetprop() operates in a similar way to VFP dbgetprop() except that
    // it operates on the current cursor as Lianja supports opening the .dbc
    // files as tables and navigating through the records in them


    csql = dbcgetprop(cViewname, "View", "SQL")


    wait window 'View code for view ' + cViewname + csql timeout 1

    select dbc
    flush
    next

    // close the VFP .dbc database container
    wait clear
    use
    close database
    //set timeline &cTimeline
    ? "Import of views from Visual FoxPRO database '&cDatabase' completed successfully"

    return .t.


    Name:  Capture 0091.jpg
Views: 32
Size:  58.7 KB

    Thanks & Best Regards

Page 1 of 3 123 LastLast

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