Batch loading VFP9 data into Lianja via scripting

**chrisg@pso.siu.edu** · 2013-11-08, 11:26

I have an in-house developed (by me) VFP9 application that was based on the Codemine framework. So I doubt it will import and I'd rather not spend time hunting down import errors. Its a monolithic type VFP EXE app and that is not the design mode of Lianja anyway: http://www.lianja.com/resources/blog...-in-a-nutshell

I need to know how to script the data migration from my VFP DBFs into Lianja. The approach I'm looking to follow is to create new tables in Lianja and then populate them data from the existing DBFs. In the process there may be some data translation that I'd need to script.

As I begin breaking down the VFP app into Lianja app modules, I would have a script to import/refresh the tables used in each module. At the point of go-live, one last import script execution would get the data updated to current.

So my question is do I write these scripts in VFP using the Lianja ODBC driver to upload the data or write them in Lianja to pull in the VFP DBF records?

Additionally, I have batch imports that run periodically, and I would like to be able to run them from VFP for a period of time until I can develop a similar batch import in Lianja.

**davefoss** · 2013-11-08, 11:42

You can simply drag/drop your VFP data files into Lianja and they will be converted automatically:

http://www.lianja.com/documentation/...l-FoxPro-files

Handling the batch imports will depend on how they're currently being done, but I'm sure that there will be an easy solution in Lianja.

**HankFay** · 2013-11-08, 12:24

Hi Chris,

a lot depends, on Dave says, on the nature of your imports, etc.

If what you want to do is make the Lianja app be sync'd with the current app, that would suggest the need to ZAP tables on the Lianja side, and then populate with what is on the VFP side (absent timestamps on records, etc., that would allow delta updates). You can do this in a couple of ways.

1) you can actually open fox tables in Lianja, but fields over 10 chars in length will have names truncated.

2) you can use the Sybase Advantage odbc driver in Lianja (direction given in a forum posting: search on sybase advantage) to pull the data. However, you can run into issues with indexes on your DBF's containing VFP functions not recognized by Sybase. (DataDirect sells a driver as part of a $2K package, but from the trial I saw no difference in performance from the Sybase driver: neither optimizes SELECTs beyond 1 join, for example).

All-in-all, if you are thinking of syncing for testing, I'd suggest setting up a Stored Procedure in your Lianja DB that zaps all the tables, run that from VFP using the Lianja ODBC driver, and then push all new data up.

BTW: it's great to see that you've grokked the Lianja way of writing apps. I'm a slow initial learner, so it took me a multitude of WTF's before I got it. I like to think it was because of my slowness in getting it that Barry wrote all that he has trying to help others get it. <s>

**chrisg@pso.siu.edu** · 2013-11-12, 17:20

Hi Hank,

I would say your suggestion of using a stored procedure in the Lianja DB to zap the tables and then call that from VFP via an ODBC connection to the Lianja DB right before pushing up new data is just what I'm looking to do.

The ODBC Driver page: http://www.lianja.com/doc/index.php/Lianja_ODBC_Driver

Seems to be fairly straight forward. I'll give it a shot and post back my results.

Regarding the procedure creation, from this page: http://www.lianja.com/doc/index.php/CREATE_PROCEDURE

I'm assuming that this will work:

OPEN DATABASE myapp_data
CREATE PROCEDURE zap_some_data AS
ZAP mytable1
ZAP mytable2
ZAP mytable3
ENDPROCEDURE

Then in VFP call the stored procedure:

lnConn = SQLCONNECT('LianjaDSN')
SQLEXEC(m.lnConn, 'exec zap_some_data')
SELECT myVFPtable1
DO WHILE NOT EOF('myVFPtable1')
SQLEXEC(m.lnConn, "INSERT INTO mytable1 (field1, field2, field3, ...);
VALUES ('Value1','Value2','Value3', ...)")
SKIP
ENDDO
SELECT myVFPtable2
DO WHILE NOT EOF('myVFPtable2')
SQLEXEC(m.lnConn, "INSERT INTO mytable2 (field1, field2, field3, ...);
VALUES ('Value1','Value2','Value3', ...)")
SKIP
ENDDO
SELECT myVFPtable3
DO WHILE NOT EOF('myVFPtable3')
SQLEXEC(m.lnConn, "INSERT INTO mytable3 (field1, field2, field3, ...);
VALUES ('Value1','Value2','Value3', ...)")
SKIP
ENDDO

**barrymavin** · 2013-11-12, 17:38

Why would you not just use the COPY TO command?

you can USE the VFP tables and then COPY TO and they will be converted to Lianja format and added to the darabase.

**barrymavin** · 2013-11-12, 17:50

COPY TO WITH PRODUCTION also has the advantage that it will convert the VFP indexes too if there are any in one command. Thats what the VFP database importer does. The code is in the library directory so you can go and read it.

**chrisg@pso.siu.edu** · 2013-11-12, 20:05

At this point, I'm expecting that I won't want an exact copy of the existing VFP data in Lianja, especially indexes. The source VFP data will be use to populate an enhanced database in the Lianja DB. Since the VFP data is currently the "live" data, it seems more logical to me to script the data migration from that side.

**barrymavin** · 2013-11-12, 20:33

Ok understood, then using ODBC is the best way to pump data into Lianja.

**chrisg@pso.siu.edu** · 2013-11-12, 21:45

How can I make an ODBC connection to the App Builder data? Would that be to just install the SQL Server on the same computer as the App Builder and configure it to use C:\Lianja\data instead of C:\Lianja\cloudserver\tenants\public\data? I need to be able to populate the data in the App Builder during development.