Background service [examples]

The Background Scripts tab of Lianja Server Manager allows for the creation, modification and configuration of background service scripts.

Background service scripts run with no UI. They are primarily used to perform data collection tasks or batch job tasks such as checking for new or updated data and emailing out notifications to Lianja users. Typically a background service will sleep for a period of time and then repeat its work. Background service scripts have the standard program extensions of prg/dbo and reside in the Services folder. Whenever the Lianja Server service is started, any programs in the Services folder with their Startup Mode set to Automatic will be run.

//============================================================================
//
// FILE         :   example.prg
//
// PURPOSE      :   Example code for a Lianja background service script on Windows.
//
//----------------------------------------------------------------------------
// 
// Background service scripts are called with two parameters:
//
// script       :   full path name of the script
// action       :   Either "start", "stop" or "restart"
// 
//-----------------------------------------------------------------------------
//
// Functions
//
// start()      :   Called when the service is started. Creates a 
//                  filename.active and processes all actions while this
//                  file exists.
//
// stop()       :   Removes the filename.active file so the service will
//                  stop when it next wakes up to do any work.
//
// doWork()     :   This is the place to add your main processing code.
//
//============================================================================
 
//-----------------------------------------------------------------------------
// declare global variables
//-----------------------------------------------------------------------------
public p_active_file
public p_error_file
public p_sleep_seconds
 
//-----------------------------------------------------------------------------
// Start action
//-----------------------------------------------------------------------------
function start( )
    // Check to see if a ".error" file already exists for this script 
    // and delete it if it does.
    if file(p_error_file)
        erase &p_error_file
        if file(p_active_file)
             erase &p_active_file
        endif
    endif
 
    // Check to see if a ".active" file already exists for this script 
    // and return if its already active
    if file(p_active_file)
        return .f.
    endif
 
     // Create the ".active" file for this script
    fp = fcreate(p_active_file)
    if ferror() = -1
        return .f.
    else
        fclose(fp)
    endif
 
    // process any work for this script
    do while file(p_active_file)
        sleep p_sleep_seconds
        doWork()
    enddo  
return .t.
 
//-----------------------------------------------------------------------------
// Stop action
//-----------------------------------------------------------------------------
function stop( )
    // Erase active file so service will stop
    erase &p_active_file
 
    // return result
    if file(p_active_file)
        rc = .f.
    else
        rc = .t.
    endif
return  rc
 
//-----------------------------------------------------------------------------
// error handler
//-----------------------------------------------------------------------------
function errorHandler( )
    on error
    save error to &p_error_file
    stop()
return
 
//-----------------------------------------------------------------------------
// function to handle the requested script action 
//-----------------------------------------------------------------------------
function handle_action(action, sleepTime, scriptName)    
 
    // Setup error handler
    on error errorHandler()
 
    // by convention we use files prefixed by the script name and postfixed with 
    // .active and .error to keep us informed of the script execution status
    p_active_file = lower(basename(left(scriptName, at(".", scriptName) -1)+".active"))
    p_error_file = lower(basename(left(scriptName, at(".", scriptName) -1)+".error"))
    p_sleep_seconds = sleepTime
 
    // Change the default directory as thats where the .active status file is written
    set default to "C:\Lianja\server\services"
 
    // handle the specified action
    do case
        // Start service
        case upper(action) = "START"
            result = start()
 
        // Stop service
        case upper(action) = "STOP"
            result = stop()
 
        // Restart service
        case upper(action) = "RESTART"
            stop()
            result = start()
 
        // Unknown command
        otherwise
            result = .f.
    endcase
return result
 
//-----------------------------------------------------------------------------
// this is the function that will be executed by this script to handle its work
//-----------------------------------------------------------------------------
function doWork( )
 
    // TODO: add your own custom code here
 
return
 
//-----------------------------------------------------------------------------
// Call the Lianja background service script action handler
//-----------------------------------------------------------------------------
return (handle_action(_para1, 10, procname()))

https://www.lianja.com/doc/index.php/Lianja_Server_Manager_on_Windows


Background Tasks

It is a common requirement to be able to schedule the running of background scripts.

Typical uses of background scripts are the loading of data from external data sources, generating and emailing HTML reports, periodic database admin, data consolidation and analysis and much more.

Since Lianja 4.1.2 you can run background scripts written in Lianja/VFP, Python, PHP and/or JavaScript by specifying command line options to the lianjarun command.

These scripts run independently of Lianja but have full access to the Lianja Framework (GUI and non-GUI) including the Database and Recordset classes.

This provides the ability to schedule the running of scripts using cron on linux, task scheduler on windows or launchd on macOS.

You specify the input file using the -i (–input) command line switch and the output file using the -o (–output) command line switch.

You can, for example, generate an HTML report and email that to someone using the Amazon AWS Simple Email Service if you have an AWS account.

Examples:

Run a Lianja/VFP script in the current directory.

lianjarun -i myscript.dbo -o myreport.html

Run a python script in the lianjademo app.

lianjarun -i app:/lianjademo/lianjademo_pythondb.py -o myreport.html

Run a PHP script in the lianjademo app.

lianjarun -i app:/lianjademo/lianjademo_phpdb.php -o myreport.html

Run a JavaScript script in the lianjademo app.

lianjarun -i app:/lianjademo/lianjademo_javascriptdb.js -o myreport.html

Notice that these examples will access the development directories as they run lianjarun. If you want to access the runtime directories use the lianjarun command with the –runtime command line switch.

You can find a full list of command line switches here.

You can perform database admin tasks from the console or in background scripts.

The -c (–command) command line switch allows multiple commands to be concatenated into a script. Each line should be separated with a semi colon and no block statements are supported e.g if, for, do while etc.

lianjarun -c "open database southwind;select top 5 customerid,contactname,companyname \
from customers"
 CUSTOMERID CONTACTNAME                    COMPANYNAME                             
 ALFKI      Maria Anders                   Alfreds Futterkiste
 ANATR      Ana Trujillo                   Ana Trujillo Emparedado
 AROUT      Thomas Hardy                   Around the Horn again
 BERGS      Christina Berglund             Berglunds snabbkop
 BLAUS      Hannah Moos                    Bill's furniture store
5 records selected in <1ms
lianja -c "backup database southwind;"
lianja -c "optimize database southwind;"

If required you can redirect output using the -o switch.

lianjarun -c "open database southwind;select top 5 customerid,contactname,companyname \
from customers" -o mydata.txt

https://www.lianja.com/doc/index.php/Background_Tasks

https://www.lianja.com/doc/index.php/Lianja_Server_Manager


 

Advertisements

Lianja SQL Server

Q:
is possible to connect to the lianja database that is used in development (not deployed)? I was hoping to generate a database programmatically, then write an app against that database and then deploy.
A:
Databases and tables opened in the App Builder are opened exclusively. Those in the deployed path are opened shared by default.

The
Data directory used by the Lianja Server is set in the Lianja Server Manager ‘Settings’ tab
You could set this to your developer data directory (drive:\lianja\data\) temporarily to run your utility, then set it back to the deployed directory once complete.
Remember to make sure that you do not have the database being altered already open in the Lianja App Builder.


Q:
When I try to launch the Lianja Server Manager from within the App Builder I get a message that I cannot launch the Lianja Server Manager because I don’t have Administrator Privileges.
I started the App Builder using Run As Administrator. I am Admin in the App Builder
I was launching using the Lianja Server Manager icon at the top of the App Builder screen
I’m on Win 8.1 pro, not on Active Directory although I get the same notification on a server where I am an admin.
No problem launching from the shortcut of course.
A:
It is probably better to launch it from the desktop shortcut or the control panel.
This functionality is being deprecated.
Please use the Lianja Server Manager desktop shortcut.


Q:
How can you get the Lianja Server to not use port 80? That screwed up my IIS installation because it could not start sites.
A:
Uncheck the port 80 checkbox in the server manager. It’s the top-right checkbox on the HTTP Settings page of the server manager.


Q:
I wanted to create a DSN to use with Crystal report XI prof for testing advanced reporting options.
I created teh DSN following the sample DSN provided for the Southwind DB unfortunately it is not connecting to my DB.
If I get you properly, I can’t create the DSN until the database is published?
A:
yes, you have to deploy the database. The Lianja ODBC driver connects to the Lianja SQL Server, which (unless you have changed the location of the databases in the Server Manager Settings page) looks for the database in the normal deployed data directory.
When you “deploy” you are pushing to a test environment. While the APaaS Cloud Server can’t be hit from outside the network you are on, other than that the environment is identical to production deployment. I wrote a script that allows me to deploy while still in the app, because as you note it’s possible that some things can only be tested in deployment.

I make a distinction between test (which I do as above), QA (separate server, wide variety of data to try to force edge conditions), staging (the customer’s data, final QA run-through) and production. The staging is sometimes important for verifying that a gnarly database structure update will run flawlessly.


Q:
If the Lianja app is using a local database (i.e. not SQL server), VFP cannot INSERT into the dbfs. But if a SQL Server is implemented (Lianja or other) it would be possible using ODBC. Is that correct?

Now if the Lianja app is running under Lianja Cloud Server, would it still be possible for VFP (running locally/not in the cloud) to connect to the Lianja SQL Server?

So, in summary, this is my scenario: I have a VFP6 app running on a local machine. I have a Lianja app running in the cloud using the Lianja SQL Server. Then I have a VFP9 program that retrieves query instructions from the Lianja app, and processes the query by scanning the VFP6 tables and inserting the matching records into the Lianja SQL database in the cloud. Is it feasible to do this?

Also, if the Lianja front end is developed on a local machine, I am assuming that it would be fairly straight-forward to move it to the cloud by changing the database connections
A:
yes to all questions. I am assuming that you are on the same network.
If you are really running Lianja on a remote server, you have a couple of ways of doing this: using oData or by creating a web service using an .rsp page.
Either way you will need an http client on the VFP end.
We use the
Chilkat controls for this purpose. The error messaging with Chilkat is great and saves a lot of time when things don’t work as you might expect. They also can handle SSL connections: if you are in the cloud, you will want to put the LCS behind IIS, in order to use https — unless you block every address except those permitted to connect.
You will also need a
JSON library for VFP: the nfJSON project in VFPX works very nicely on the VFP side.
You will want to base64 encode/decode the JSON messaging.

If the Lianja server is not in the cloud, but on the same network, it all gets easier. You could put the query instructions in a Lianja table, use the Lianja ODBC driver to pick up the query from VFP, and the same driver to insert the records.

A2:
Here is a skeleton of a generic rsp page that I use to read from a SQL Server.
Obviously, you would change the read to a write, but I wanted to illustrate the json object for others.

Lets say I call it SQLcode.rsp and save it to the cloud server as
c:\lianja\cloudserver\tenants\public\apps\myrspapp \SQLcode.rsp

(The preferred way would be to create an app called myrspapp, add an webview rsp page called sqlcode.rsp then deploy it to the cloud server)

I would then call it as”http://myserver/apps/myrspapp/sqlcode.rsp?param1=x&param2=y
Or if you are using the Lianja default port, then you would call it as
http://myserver:8001/apps/myrspapp/sqlcode.rsp?param1=x&param2=y

Code:
%@ language=VFP %>

<%
private m_param1 = getParameter("param1")
private m_param2 = getParameter("param2")

private aresult
set strcompare off

gnConnHandle = SQLSTRINGCONNECT("driver={SQL server};server=;uid=;pwd=")

strw1 = ""
SQLPREPARE(gnConnHandle,strw1,'v_SQLresult')
= SQLEXEC(gnConnHandle)

select * from v_SQLresult into arrayofobjects aresult


response.addheader("Content-type", "application/json")

print_json(aresult)

=sqldisconnect(gnConnHandle)
%>

A3:
Just a note for those wanting to connect remotely to any SQL Database, not just the LCS.

First, you will need to protect your open IP address. Not doing so leaves you open to brute-force password attacks. You can set up a VPN; or you can limit the addresses that are able to access the port.

Second, you will need to have protection from Denial of Service attacks.

Third, as Herb shows in his example, you should SqlPrepare your queries in order to thwart SQL Injection attacks.

Fourth, to prevent data sniffing if not using a VPN, I would put the LCS behind IIS in order to get an SSL connection. The LCS will get its own SSL ability in a future version, according to the roadmap.

All of these points apply to any “in the cloud” service. They simply apply “more” in this instance, where your data doesn’t have the protective layer of a web service. Building dynamic AES-256 encrypted keys into a web service is simple (using Chilkat’s encryption module). That extra level of security comforts me. Keeping unallowed IP addresses outside the firewall comforts me more — put up an endpoint in the cloud and log all failed logins and you’ll have a sleepless night.

Especially when you see the IP address ranges coming from places where you have no users.
They will start showing up in hours, literally
.



 

Lianja SQL Server

Q:

Code:
lcDSNLess="DRIVER=Lianja ODBC Driver;SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE =C:\LIANJA\DATA\BDFICC"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
if lnConnHandle > 0
sqlexec(lnConnHandle,"select * from historia")
list
sqldisconnect(lnConnHandle)
else 
messagebox("Sin Conexion","Error")
endif

I cant connect.

A:

Development databases used in the app builder are opened non shared.
When connecting using ODBC from VFP the ODBC driver expects the database to be deployed. It does not access the development database.
Databases that have been deployed (runtime databases) are opened shared.
Which only works if the southwind database is not open by the app builder.


Q:
The embedded high-performance cross-platform 64-bit database is a kind of SQL or a file database, like the old one from VFP?
A:
Lianja database tables support SQL and NoSQL access. Tables, multiple indexes, table dictionaries etc. are stored as individual files.



Lianja SQL Server

Q:
Differences between the embedded database and Lianja SQL Server?
A:
There is no difference, the Lianja SQL Server is a means to allow you access to Lianja data from 3rd party programs or databases, primarily via ODBC.


Q:
partial uninstall
A:
work around is to double click the uninstall.exe in the C:\Lianja\sqlserver\ directory directly.


Q:
I am using Lianja SQL server from the same LAN. How can I tell my network admin to setup IP/hostnames to reduce delay.
A:
Try putting the IP/hostname mapping in the C:\Windows\System32\drivers\etc\hosts file on the client.


Background Scripts you can create and run via the Lianja Server on Windows, so for anyone who is interested in using these, I hope this will guide you in their use and design.
Background scripts can be used to perform data transformation, data synchronization or other custom operation on your data. Background scripts run with no UI. Each runs as a separate LianjaRunScript process. They are primarily used to perform data collection tasks or batch job tasks such as checking for new or updated data and emailing out notifications to users. Typically a background script will sleep for a period of time and then repeat its work. Background scripts have the standard program extensions of prg (Lianja/VFP script source) / dbo (compiled Lianja/VFP script) and reside in the Services folder (Lianja Server Manager Settings tab, by default ‘C:\Lianja\server\services\‘). The Background Scripts tab in the Lianja Server Manager allows for the creation, modification and configuration of background scripts.
http://www.lianja.com/community/show…=8626#post8626


The Lianja SQL Server uses the operating system user authentication/permissions. Specify a valid username and password for the server’s host OS. You can also deny/allow connections based on ranges of or individual IP addresses in the Security settings of the Lianja Server Manager.


The lianja database engine is full 64 bit and has no practical file size limits as is detailed on the product pages.
It has self healing indexes and is widely deployed around the world in defense, banking and finance systems.
It is embedded in lianja app builder, lianja sql server and lianja cloud server.
It is fast and lightweight.
It is easy to install.
It comes with both an odbc driver and a jdbc driver.
It handles transparent connectivity to third party databases using virtual tables.
You can write stored procedures and triggers in VFP.


Lianja SQL server is a SQL server as stated in the name. Saying that however, it does allow you to write stored procedures in VFP using the NoSQL commands. Lianja SQL Server as with native Lianja uses indexes automatically when performing queries.
Lianja does not support table buffering.


Yes any type of binary file of any size can be stored as the database engine uses 64 bit file access. Space is automatically reclaimed on updates.
yes, you are correct, with this functionality Lianja can easily be used as a NoSQL key/value database just create a table with two fields, key and value.

Code:
create table NoSQL (key char(20) foreign key, value varchar)

you can then perform very fast key/value lookups of JSON or XML encoded data stored in the value column.
And of course the inherent problem of distributed updates in NoSQL databases does not exist in Lianja. Everyone sees the updates immediately.


Q:
I’ve used the Lianja ODBC Manager to setup a connection to my VFP data and it tests out fine. I’ve setup a new database in the App Builder and used the option Create Virtual Tables from ODBC. It takes a a few minutes to complete and then I see all of my tables prefaced with “vt_”. The problem is though that many of the tables, when clicked on, display a message say “File [vt_tablename] does not exist”. A few tables are accessible – seems to be those with a very small number of fields. Is there something I missed?
A:
The Lianja ODBC Manager is for connecting to Lianja SQL Server data.
For other data sources you need to use the ODBC driver specific to it.
in your case you need to use the VFP ODBC driver.


The Lianja SQL Server is a complete SQL database server used for building client / server Apps. It is not just for providing third party access to Lianja data.
You can install and run it on a separate server machine (Windows or Linux).
It is the equivalent of MySQL or MSSQL.
One big advantage is that you can write stored procedures in Lianja/VFP scripting.