PDA

View Full Version : Batch loading VFP9 data into Lianja via scripting



chrisg@pso.siu.edu
2013-11-08, 12: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/38-development-news/375-lianja-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, 12:42
You can simply drag/drop your VFP data files into Lianja and they will be converted automatically:

http://www.lianja.com/documentation/documentation-index/307-importing-visual-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, 13: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, 18: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, 18: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, 18: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, 21: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, 21:33
Ok understood, then using ODBC is the best way to pump data into Lianja.

chrisg@pso.siu.edu
2013-11-12, 22: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.

barrymavin
2013-11-13, 02:11
Yes you can just create a symbolic link but bear in mind that the database is opened for exclusive access during development mode in the App Builder.

chrisg@pso.siu.edu
2013-11-13, 19:00
Create a link to the App Builder or SQL Server directory?

mklink /J C:\Lianja\data C:\Lianja\cloudserver\tenants\public\data

or

mklink /J C:\Lianja\cloudserver\tenants\public\data C:\Lianja\data

What will SQL server do while App Builder is running and has opened the database exclusively?

Should the SQL Server service be stopped while in development mode?

Just need to know if I should script the starting and stopping of the service.

barrymavin
2013-11-13, 21:37
Hi Chris,

Lianja purposely lets you build Apps with a local copy of the data and then publish it.

The App Builder requires exclusive access to the database and its tables.

Lianja SQL Server (as you know already) accesses the live database i.e. the ones you deploy.

If you have a database open in the App Builder and you create a symbolic link *from* c:\lianja\data to c:\lianja\cloudserver\tenants\public\data (either way makes no difference) then I can see a few issues.

1. When you upgrade to new versions of Lianja
2. If the database is open in the App Builder then SQL server will not be able to access it as it is opened for exclusive access

There are few things you can do.

1. Always push your data into a "staging" database and then in a script copy the data over into the development directory
2. Stop SQL server while you are developing and restart it when you want the new data.

Basically you can't have SQL server and the App builder access the same data at the same time... unless the tables are all Virtual Tables that are defined in your database (App Builder directory) that use ODBC to access the *real* data via the Lianja SQL Server. The way you have described about zapping and overwriting tables will probably not work doing this also.

So. It would be better to have a staging area or just allow refreshing the databases from VFP when no App Builder has them open. In fact SQL server will just fail to open the database and will automatically succeed when the App Builder is closed.