Lianja ODBC Driver on Windows
Contents
See Also
Lianja ODBC Manager, Lianja SQL Server Manager, Lianja SQL Server Service on Linux, SQL Remote Data Connectivity Functions, SQL System Tables
Lianja ODBC Driver on Linux
For information on the Lianja ODBC Driver on Linux, please see Lianja ODBC Driver on Linux.
Before Using the Lianja ODBC Driver
Before using the Lianja ODBC Driver, complete the following steps:
- Download and install the driver. The driver is included in the Lianja SQL Server distribution and a client driver-only Lianja ODBC Driver distribution. The driver requires a local or remote Lianja SQL Server to operate.
- Select your Lianja database or create it in the Lianja App Builder Data Workspace or using SQL.
- Copy or Deploy your database to the Lianja SQL Server Database directory.
- Make sure the Lianja SQL Server service is running.
- Create a DSN for your data source in the Lianja ODBC Manager and test the DSN or test out a DSNless Connection.
DSNless Connection
To connect to the Lianja ODBC Driver without creating a DSN, use the following string format:
DRIVER=Lianja ODBC Driver;SERVERNAME=<IP or Hostname>;USERNAME=<server-user>;PASSWORD=<user-password>;DATABASE=<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)
Using ODBC Data Sources in the Lianja App Builder
Your Lianja ODBC DSN (as well as third-party ODBC data sources) can be accessed from the Lianja App Builder in the following ways:
- Using the SQL Remote Data Connectivity Functions
- Using the USE...CONNSTR NoSQL Command
- By creating a Virtual Table in a local database
- In the ODBC Console
- By Importing the data into a new local database
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 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