DATA is located on another Host

Topics: User Forum
Dec 14, 2014 at 1:53 PM
Hi Claude (and others) - thanks for your previous guidance.

Building our Model is going well given most of this Web coding stuff is new to a hard core desktop coder like myself! Anyway have a VM 2008R2 Server running IE 7.5 with ActiveVFP 6.03 plus Victors extensions. All working great locally and will soon hook our LAN data up to test the model further.

QUESTION: Our end concept is a WEB API Service into a Client LAN Desktop Data Source. I am considering having a GoDaddy ActiveVFP instance sourcing Data from an remote Web Source which is the Client LAN Data. Is this possible?

The ultimate solution (and really cool!) would be to have 1 instance of ActiveVFP (GoDaddy):
->receives a Service request for Data with a Client Descriptor;
->Client Description evaluates the IP location from a local table describing all remote Data Hosts;
->ActiveVFP "does its thing" prefixed with the IP location (Source Domain) and returned the Data i.e. Service to Return 1 Member Record.

If the data cannot be across the internet then all Client LAN Data would need to be duplicated on GoDaddy (as its a desktop App) OR each client would need to establish a Web Server on their LAN.

I look forward to your and other participants who may of had experience with this issue, many thanks, Paul.
Coordinator
Dec 14, 2014 at 6:04 PM
You'll have to have a public IP for each Client LAN Desktop Data Source. If this is all VFP data I would recommend setting up IIS and AVFP at those sites returning vfp data to the GoDaddy server. The ActiveVFP component on the clients would be a RESTful web server. Performance should be really good.

If you were using SQL, MySQL data etc. , you could just set up the connection strings to those data sources using the IP address. The demo at GoDaddy is sort of doing this now since the SQL and MySQL databases are on physically different servers at GoDaddy.

VFP Databases are files only, there is no server to connect to. That's why ODBC and also OLEDB Provider connectionstrings need a path to a folder of DBFs or a DBC.
There is no default active component on the backend side listening to a port for SQL etc.
USE \ipadress\folder\some.dbf in fact can work, but that needs security settings out of discussion and will not perform well anyway.


BTW, for access to LAN VFP data from AVFP (VFP data is not on the same server as AVFP), create a new Application Pool in IIS (or change the existing one) and impersonate it with a user account that has access to the data folder.
Dec 15, 2014 at 12:59 AM
Thanks Claude.
When you say "recommend setting up IIS at site" is it compulsory? I would rather only have 1 instance of AVFP and not having GoDaddy Instance -> to Client LAN Instance -> LAN Data but yes it leaves security as another issue to deal with. Most of our clients are NFP clients that do not spend dollar on infrastructure so finding a PC with specs to run IIS would just be another hindrance. Also having 1 AVFP instance means there is only 1 infrastructure we need to install and support.

I had thought of using SQL Express as an intermediary but wanted to see if AVFP could do the job first as it is an area I need to get involved in.

So to be clear I am suggesting:
BROWSER <--> GoDaddy AVFP <--> Client LAN Data (but you say may suck in performance)

But you reccommend:
BROWSER <--> GoDaddy AVFP <--> Client AVFP & IIS <--> Client LAN Data (client AVFP is RESTful web server)

I guess if my option over the internet is going to lag then:
BROWSER <--> Client AVFP & IIS Express <--> Client LAN Data

In the last case I would try to use IIS Express for the clients due to the point noted above. Again all great info - I will continue on with my testing and look for to confirmations on the above, Paul.
Coordinator
Dec 15, 2014 at 10:20 AM
Yes. But you might just want to experiment first and get a proof of concept going before making any final judgements.
Dec 15, 2014 at 12:49 PM
So I guess I run with:
"BROWSER <--> Client AVFP & IIS Express <--> Client LAN Data "
Which installs everything LOCALLY at a client's site correct? Further to that I authenticate outside BROWSER requests before processing any requests for data?
Coordinator
Dec 15, 2014 at 3:09 PM
Edited Dec 15, 2014 at 3:11 PM
Yes, that sounds like it should work good. You could shoot an e-mail to Victor (see the People section) and get his opinion.
Aug 10, 2015 at 11:52 AM
Hi Claude,

Thank you for helping us. I have VFP desktop apps development experience as most of vfp developers. Can you guide us a step by step tutorial for the following as I need to access VFP free tables in my ActiveVFP apps from anohter PC over my LAN ?

claudefox wrote:
BTW, for access to LAN VFP data from AVFP (VFP data is not on the same server as AVFP), create a new Application Pool in IIS (or change the existing one) and impersonate it with a user account that has access to the data folder.
I want to give VFP tables/data access to only my AVFP apps and secure it from outside world (i.e. No one can hack / browse data folders or access it). For example

Web Browsers (or Clients) can send data request to my AVFP server acting as as web service and in response my AVFP apps query vfp tables.

Regards,
Naeem
Coordinator
Aug 13, 2015 at 1:15 PM
I have a REST web service for AVFP called JSONDB that allows you to access any DBF table you have using a REST approach. For example, if you have a table called CUSTOMERS.DBF, you can list all records by requesting:

GET /yourapp/jsondb/customers/

No need to code a "customers.prg" controller en AVFP. The JSONDB rest service support all basic CRUD operations. Let me know if this may work for you.


Victor
Aug 14, 2015 at 10:02 PM
I tested jSon.prg and it was not exporting data in memo fields.

1) I would appreciate if you could send me JSONDB with usage instructions.

2) As I asked before about setting my AVFP application pool for impersonate it with an user account to access my data over LAN.

Regards,
Naeem
Coordinator
Aug 16, 2015 at 7:29 PM
Take a look at the JSONDB test page. This shows two new feaures:

a) Layout Pages (lp tags)
b) JSON setup and usage

If this is what you are looking for, let me know and I will prepare a download for you.


Victor Espina
<lp:layout Source="bootstrap.avfp" pageTitle="JsonDB Test">

  <lp:content name="links">
     <!-- Load jsondb client library -->
     <script src="@home@/prg/rest/controllers/jsondb/jsondb.js"></script>
  </lp:content>
  
  <lp:content name="pageLoaded">

      // Configure jsonDB service
      jsondb.url = "@home@/jsondb/"; // jsondb REST service
      jsondb.configure("dbf",null,function(response) {
       console.log(response);
       if (response.result) {
         loadCustomers();         
       } else {
         $("#customers").html(response.error);       
       }
      });   // use DBF repositories. If no folder is indicated (2nd parameter), service will look on the REST/CONTROLLERS folder.
      
      

      // loadCustomers
      // Load a customer list and show it on the page. First parameter is the filtering values (if null, then all
      // rows will be returned) and 2nd parameter is the desired order for the data to be returned.
      //      
      function loadCustomers(conn) {

        $("#headermsg").html("Please wait while the customer list is loaded:");
        $("#customers").html("");


        // Connect to a repository in the jsondb
        var customers = jsondb.connect("customers");

        // Retrieve all entries in "customers" resource, ordered by "custName"      
        customers.retrieve(null, "custName", function(response) {
          if (response.result) {
            var chtml = "";
            $("#headermsg").html("This list of customers was retrieved using jsondb:<br>"+
                                 '<a class="btn btn-default" onclick="loadCustomers();">Reload</a> ' + 
                                 '<a class="btn btn-info" href="showhtmlsource.avfp?file=jsondbtest">Show code</a><br><br>');
                                 
            for(var i = 0; i < response.data.rows.length; i++) {
             var o = response.data.rows[i];
             chtml = chtml + '<li><a href="#" onclick="'.concat("showCustomer('",o.custid,"');") + '">' + o.custname + "</a>\r";             
            }
            chtml = "<i>" + response.data.rowcount + " customers found</i><br>\r<ul>\r" + 
                    chtml + 
                    "</ul>\r";
            $("#customers").html(chtml);
          }
          else {
           $("#customers").html(response.error);
          }
        });
      }
      
      
      // showCustomer
      // Takes a customer's id and retrieve its data using jsondb
      //
      function showCustomer(custid) {
        // Retieve customer info using customer's id
        var customers = jsondb.connect("customers");
        customers.retrieve({custid: custid}, null, function(response) {
          if (response.result) {
           var row = response.data.rows[0];
           alert('This data was retrieved using jsondb:\r\r' +
                 'id: ' + row.custid + '\r' +
                 'name: ' + row.custname + '\r' +
                 'status: ' + row.custstat);
          } else {
           $("#customers").html(response.error);
          }
        });
      }

  </lp:content>
  
  <lp:content Name="body">
    <div class="jumbotron">
      <h1>JsonDB Demo Page</h1>
      <p>This page shows how to use ActiveVFP's JsonDB REST service to query data from server in JSON format.</p>
    </div>
    <hr>
    <p id="headermsg"> </p>
    <p/>
    <div id="customers"/>
    <br>
    <br>
    <div id="trace"> </div>
  </lp:content>

</lp:layout>
Aug 17, 2015 at 11:25 AM
I need to access / update my VFP tables using AVFP. I will appreciate if you can send me the download link.

Naeem
Coordinator
Aug 17, 2015 at 12:31 PM
http://www.noiqs.com/sos/avfp603ext.rar
  1. USE IT AT YOUR OWN RISK
  2. Install this files on top of a normal AVFP 6.03 installation
  3. Look at jsondbtest.avfp file
Victor Espina
Aug 17, 2015 at 1:54 PM
Thank you victor. I will let you know if help required.

Naeem
Coordinator
Aug 17, 2015 at 3:14 PM
Be aware that I hadn´t use jsondb in any real app, so it may contains some bugs. If you find any, let me know.

Victor