ODBC [examples]

Connect to an ODBC data source 
SQLCONNECT
nStatHand = sqlconnect("mylocal")
// or
nStatHand = sqlconnect("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mydb","myuser","mypass")

https://www.lianja.com/doc/index.php/SQLCONNECT()


Disconnect from a data source 
SQLDISCONNECT
nStatHand = sqlstringconnect("Driver={SQL Server};Server=MYPC-PC\INST1;Trusted_Connection=yes;Database=AdventureWorks;")
if nStatHand < 1
  messagebox("Could not connect")
else
  messagebox("Connected")
  // ...
  sqldisconnect(nStatHand)
endif

Commit a transaction 
SQLCOMMIT
nStatHand=sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqltransaction(nStatHand)
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no, lastname, balance) values ('00889','Brown', 12000)")
	if messagebox("Commit Insert?",36,"Transaction") = 6
		sqlcommit(nStatHand)
	else
		sqlrollback(nStatHand)
	endif
	sqlexec(nStatHand,"select * from dbo.doc1")
	list
	sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLCOMMIT()


Store column information to a cursor
SQLCOLUMNS
nStatHand = sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	nColEnd = sqlcolumns(nStatHand, "sales.currency")
	if nColEnd = 1
		list
	else
		messagebox("Table of Table Information could not be created")
	endif
	sqldisconnect(nStatHand)
endif

Request that an executing SQL statement be cancelled 
SQLCANCEL
sqlcancel(nStathand)

https://www.lianja.com/doc/index.php/SQLCANCEL()


Error message for the last ODBC data connection error 
SQLERROR
nStatHand = sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqlexec(nStatHand,"create table doc1 (account_no char(5), lastname char(25), balance dec(10,2))")
	messagebox("Table created")
	m_accno =  "00699"
	m_lastname = "Smith"
	m_balance = 12345.67
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no,lastname,balance) VALUES ('&m_accno','&m_lastname',&m_balance)")
	messagebox("Record inserted")
	nRet = sqlexec(nStatHand,"select * from dbo.doc1","mycursor")
	if nRet = -1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected")
		list
	endif
	// Using prepared statement
	sqlprepare(nStatHand,"select * from dbo.doc1 where account_no = ?m_accno","mycursor")
	nRet = sqlexec(nStatHand)
	if nRet < 1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected using prepared statement")
		list
	endif
	sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLERROR()


Send an SQL statement to an ODBC data source
SQLEXEC
nStatHand = sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqlexec(nStatHand,"create table doc1 (account_no char(5), lastname char(25), balance dec(10,2))")
	messagebox("Table created")
	m_accno =  "00699"
	m_lastname = "Smith"
	m_balance = 12345.67
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no,lastname,balance) VALUES ('&m_accno','&m_lastname',&m_balance)")
	messagebox("Record inserted")
	nRet = sqlexec(nStatHand,"select * from dbo.doc1","mycursor")
	if nRet = -1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected")
		list
	endif
	// Using prepared statement
	sqlprepare(nStatHand,"select * from dbo.doc1 where account_no = ?m_accno","mycursor")
	nRet = sqlexec(nStatHand)
	if nRet < 1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected using prepared statement")
		list
	endif
	sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLEXEC()


Query property settings for a connection or the environment 
SQLGETPROP
nStatHand = sqlconnect("awhr")
if nStatHand < 1
  messagebox("Cannot make connection", 16, "SQL Connect Error")
else
  eGetProp = sqlgetprop(nStatHand,"ConnectString")
  if type("eGetProp") = "N" and eGetProp < 0
    messagebox("Error Occurred")
  else
    messagebox(etos(eGetProp))
  endif
endif

Check if more results sets are available and if so, copy next results set to a cursor
SQLMORERESULTS
nStatHand = sqlconnect("awhr")
if nStatHand < 1
  messagebox('Cannot make connection', 16, 'SQL Connect Error')
else
  messagebox('Connection made', 48, 'SQL Connect Message')
  store "00010" to myVar
  sqlexec(nStatHand, "select * from dbo.doc1 where account_no = ?myVar", "restab")
  list
  sqlmoreresults(nStatHand)
  sqldisconnect(nStatHand)
endif

Prepare an SQL statement that will be executed by the SQLEXEC() function 
SQLPREPARE
nStatHand = sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqlexec(nStatHand,"create table doc1 (account_no char(5), lastname char(25), balance dec(10,2))")
	messagebox("Table created")
	m_accno =  "00699"
	m_lastname = "Smith"
	m_balance = 12345.67
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no,lastname,balance) VALUES ('&m_accno','&m_lastname',&m_balance)")
	messagebox("Record inserted")
	nRet = sqlexec(nStatHand,"select * from dbo.doc1","mycursor")
	if nRet = -1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected")
		list
	endif
	// Using prepared statement
	sqlprepare(nStatHand,"select * from dbo.doc1 where account_no = ?m_accno","mycursor")
	nRet = sqlexec(nStatHand)
	if nRet < 1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected using prepared statement")
		list
	endif
	sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLPREPARE()


Rollback a transaction
SQLROLLBACK
nStatHand=sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqltransaction(nStatHand)
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no, lastname, balance) values ('00889','Brown', 12000)")
	if messagebox("Commit Insert?",36,"Transaction") = 6
		sqlcommit(nStatHand)
	else
		sqlrollback(nStatHand)
	endif
	sqlexec(nStatHand,"select * from dbo.doc1")
	list
	sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLROLLBACK()


Set property settings for a connection 
SQLSETPROP
nStatHand = sqlconnect("awhr")
if nStatHand < 1
  messagebox("Could not connect")
else
  nSetEnd = sqlsetprop(nStatHand,"Transactions",2)
  if nSetEnd = 1
    messagebox("Manual Transactions Enabled")
  else
    messagebox("Unable to enable Manual Transactions")
  endif
  sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLSETPROP()


Connect to a data source 
SQLSTRINGCONNECT
// Store the return value to use as the nStatementHandle for subsequent function calls
// Specify an ODBC DSN
nStatHand = sqlstringconnect("awhr")
// or an ODBC connection string
nStatHand = sqlstringconnect("Driver={SQL Server};Server=MYPC-PC\INST1;Trusted_Connection=yes;Database=AdventureWorks;")

Store data source table names to a table
SQLTABLES
nStatHand = sqlstringconnect("awhr2")
if nStatHand < 1
  messagebox("Could not connect")
else
  // Store names for all table types to default results table
  nTabEnd = sqltables(nStatHand)
  if nTabEnd = 1
    select sqlresult
    list
  endif
endif
 
nStatHand = sqlstringconnect("Driver={SQL Server};Server=USER-PC\INST1;Trusted_Connection=yes;Database=AdventureWorks;")
if nStatHand < 1
  messagebox("Could not connect")
else
  // Store names for specified table types to specified results table
  nTabEnd = sqltables(nStatHand, "'TABLE','VIEW'", "myresults")
  if nTabEnd = 1
    select myresults
    list
  endif
endif

Start a transaction 
SQLTRANSACTION

 

nStatHand=sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqltransaction(nStatHand)
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no, lastname, balance) values ('00889','Brown', 12000)")
	if messagebox("Commit Insert?",36,"Transaction") = 6
		sqlcommit(nStatHand)
	else
		sqlrollback(nStatHand)
	endif
	sqlexec(nStatHand,"select * from dbo.doc1")
	list
	sqldisconnect(nStatHand)
endif

https://www.lianja.com/doc/index.php/SQLTRANSACTION()


Cloud ODBC Data Access

Using the ODBC drivers from https://www.devart.com/odbc/#cloud you can connect to the most popular cloud resources directly via HTTP.

The Devart ODBC Drivers provide high-performance and feature-rich connectivity solutions for ODBC-based applications to access the most popular databases directly from Windows, MacOS, Linux, both 32-bit and 64-bit. Drivers fully support standard ODBC API functions and data types, multiple server data types and features.

Use these ODBC drivers seamlessly in Lianja with Virtual Tables.

The easiest way to test ODBC connectivity is to use the following commands in the console workspace.

use dummy connstr "odbc-connection-string" as select * from tablename 
list

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


Advertisements

Lianja Server [examples]

Hostname of the server that is running the Lianja Server
GETLOCALHOST
mCurrentHostName = getlocalhost()

https://www.lianja.com/doc/index.php/GETLOCALHOST()


IP address of the client that is connected to the Lianja Server
GETREMOTEADDR
mCurrentIP = getremoteaddr()

https://www.lianja.com/doc/index.php/GETREMOTEADDR()


Hostname of the client that is connected to the Lianja Server
GETREMOTEHOST
mCurrentHostName = getremotehost()

https://www.lianja.com/doc/index.php/GETREMOTEHOST()


User name of the client that is connected to the Lianja Server
GETREMOTEUSER
mCurrentUser = getremoteuser()

https://www.lianja.com/doc/index.php/GETREMOTEUSER()


Configure SQL statement syntax
SET_SQL
set sqldialect to vfp
 
set sql to mysql

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


Configure SQL statement syntax
SET_SQLDIALECT
set sqldialect to vfp
 
set sql to mysql

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


 

Lianja ODBC [examples]

Linux

DSNless Connection

To connect to the Lianja ODBC Driver without creating a DSN, use the following string format:

DRIVER=Lianja;SERVERNAME=;USERNAME=;PASSWORD=;DATABASE=

For example:

lcDSNLess="DRIVER=Lianja;" ; 
+ "SERVERNAME=192.168.123.123;USERNAME=myuser;PASSWORD=mypassword;DATABASE=southwind"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
if lnConnHandle > 0
	sqlexec(lnConnHandle,"select * from shippers")
	list
	sqldisconnect(lnConnHandle)
endif

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

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


Windows

DSNless Connection

To connect to the Lianja ODBC Driver without creating a DSN, use the following string format:

DRIVER=Lianja ODBC Driver;SERVERNAME=;
USERNAME=;PASSWORD=;DATABASE=

For example:

lcDSNLess="DRIVER=Lianja ODBC Driver;" ; 
+ "SERVERNAME=192.168.123.123;USERNAME=myuser;PASSWORD=mypassword;DATABASE=southwind"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
if lnConnHandle > 0
	sqlexec(lnConnHandle,"select * from shippers")
	list
	sqldisconnect(lnConnHandle)
endif

Local Host

To connect to the local host, the SERVERNAME can be localhost or ? and the USERNAME and PASSWORD can be ?.

lcDSNLess="DRIVER=Lianja ODBC Driver;" ; 
+ "SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE=southwind"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)

Including Optional Parameters

The optional parameters can be included as follows:

lcDSNLess="DRIVER=Lianja ODBC Driver;" ; 
+ "SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE=southwind;" ;
+ "CLIENTLOGGING=TRUE;CLIENTLOGFILE=clientlog.txt;" ;
+ "ROWID=TRUE;EXCLUSIVE=TRUE;READONLY=TRUE;ENCRYPTION=TRUE"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)

Lianja ODBC Driver Additional Functionality

Full Lianja SQL documentation can be found here: SQL.

Calling Stored Procedures

Stored Procedures can be called using the call command. Stored procedures are .prg Lianja/VFP script files residing in a database’s directory. For example, the script sp_demo.prg has been created in the southwind database in the Lianja App Builder:

It takes a parameter, selects data based on the parameter and returns the data as a resultset:

// Store Procedure: sp_demo.prg
lparameters lcState
select account_no, state from example;
 where state = lcState;
 into cursor curExample
return setresultset("curExample")

After being deployed to the Lianja SQL Server database directory, it can be called when an ODBC connection to the southwind database is active:

// Call a Stored Procedure
nhand = sqlconnect("Lianja_Southwind")
if nhand > 0
	nret = sqlexec(nhand,"call sp_demo('MA')","mycursor")
	if nret > 0
		select mycursor
		browse
	endif
	sqldisconnect(nhand)
endif

Issuing Lianja Commands

Non-UI Lianja commands can be sent to the Lianja SQL Server by prefixing them with the lianja command. For example, Set Commands can be issued or status information output to a file.

// List status information to a file
nhand = sqlconnect("Lianja_Southwind")
if nhand > 0
	nret = sqlexec(nhand,"lianja list status to file 'C:\Temp\status.txt")
	sqldisconnect(nhand)
endif

Evaluating Expressions

The sysresultset system table can be used to return the singleton result from any Lianja expression. For example, the following will display the current SET EXCLUSIVE setting and system time:

// Evaluate expressions on the server
nhand = sqlconnect("Lianja_Southwind")
if nhand > 0
	nret = sqlexec(nhand,"select set('EXCLUSIVE') as Excl, time() as Time from sysresultset","mycursor")
	if nret > 0
		select mycursor
		display
	endif
	sqldisconnect(nhand)
endif

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


Expand the ? parameter values from a SQL statement

cName = "%Mountain%"
cColor = "Black"
 
cSQL = "select * from Production.Product where name like ?cName " + ;
       "and color = ?cColor"
nStatHand = sqlconnect("lianja_mssqltest")
sqlprepare(nStatHand,cSQL)
sqlexec(nStatHand)
list
 
? sqlparams(cSQL, "MSSQL")
// Returns: select * from Production.Product where name like '%Mountain%' and color = 'Black'
sqldisconnect(nStatHand)

https://www.lianja.com/doc/index.php/SQLPARAMS()


DSNless Connection

To connect to the Lianja ODBC Driver without creating a DSN, use the following string format:

DRIVER=Lianja ODBC Driver;SERVERNAME=;
USERNAME=;PASSWORD=;DATABASE=

For example:

lcDSNLess="DRIVER=Lianja ODBC Driver;" ; 
+ "SERVERNAME=192.168.123.123;USERNAME=myuser;PASSWORD=mypassword;DATABASE=southwind"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
if lnConnHandle > 0
	sqlexec(lnConnHandle,"select * from shippers")
	list
	sqldisconnect(lnConnHandle)
endif

Local Host

To connect to the local host, the SERVERNAME can be localhost or ? and the USERNAME and PASSWORD can be ?.

lcDSNLess="DRIVER=Lianja ODBC Driver;" ; 
+ "SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE=southwind"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)

Including Optional Parameters

The optional parameters can be included as follows:

lcDSNLess="DRIVER=Lianja ODBC Driver;" ; 
+ "SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE=southwind;" ;
+ "CLIENTLOGGING=TRUE;CLIENTLOGFILE=clientlog.txt;" ;
+ "ROWID=TRUE;EXCLUSIVE=TRUE;READONLY=TRUE;ENCRYPTION=TRUE"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)

Calling Stored Procedures

Stored Procedures can be called using the call command. Stored procedures are .prg Lianja/VFP script files residing in a database’s directory. For example, the script sp_demo.prg has been created in the southwind database in the Lianja App Builder:

It takes a parameter, selects data based on the parameter and returns the data as a resultset:

// Store Procedure: sp_demo.prg
lparameters lcState
select account_no, state from example;
 where state = lcState;
 into cursor curExample
return setresultset("curExample")

After being deployed to the Lianja SQL Server database directory, it can be called when an ODBC connection to the southwind database is active:

// Call a Stored Procedure
nhand = sqlconnect("Lianja_Southwind")
if nhand > 0
	nret = sqlexec(nhand,"call sp_demo('MA')","mycursor")
	if nret > 0
		select mycursor
		browse
	endif
	sqldisconnect(nhand)
endif

Issuing Lianja Commands

Non-UI Lianja commands can be sent to the Lianja SQL Server by prefixing them with the lianja command. For example, Set Commands can be issued or status information output to a file.

// List status information to a file
nhand = sqlconnect("Lianja_Southwind")
if nhand > 0
	nret = sqlexec(nhand,"lianja list status to file 'C:\Temp\status.txt")
	sqldisconnect(nhand)
endif

Evaluating Expressions

The sysresultset system table can be used to return the singleton result from any Lianja expression. For example, the following will display the current SET EXCLUSIVE setting and system time:

// Evaluate expressions on the server
nhand = sqlconnect("Lianja_Southwind")
if nhand > 0
	nret = sqlexec(nhand,"select set('EXCLUSIVE') as Excl, time() as Time from sysresultset","mycursor")
	if nret > 0
		select mycursor
		display
	endif
	sqldisconnect(nhand)
endif

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


 

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


 

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.