Difference between revisions of "Lianja ODBC Driver on Windows"

From Lianjapedia
Jump to: navigation, search
(→‎DSNless Connection)
 
(18 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
==See Also==
 +
[[Lianja ODBC Driver on Linux]], [[Lianja ODBC Manager on Windows]], [[Lianja Server Manager on Linux]], [[Lianja Server Manager on Windows]], [[SQL Remote Data Connectivity Functions]], [[SQL System Tables]]
 +
 
==Before Using the Lianja ODBC Driver==
 
==Before Using the Lianja ODBC Driver==
 
Before using the Lianja ODBC Driver, complete the following steps:
 
Before using the Lianja ODBC Driver, complete the following steps:
Line 5: Line 8:
 
# Copy or [[Deploying to Local Directory|Deploy]] your database to the Lianja SQL Server [[Lianja SQL Server Manager#Settings|Database directory]].
 
# Copy or [[Deploying to Local Directory|Deploy]] your database to the Lianja SQL Server [[Lianja SQL Server Manager#Settings|Database directory]].
 
# Make sure the Lianja SQL Server [[Lianja SQL Server Manager#Service Manager|service is running]].
 
# Make sure the Lianja SQL Server [[Lianja SQL Server Manager#Service Manager|service is running]].
−
# Create a DSN for your data source in the [[Lianja ODBC Manager]] and [[Lianja ODBC Manager#Connection_Information|test the connection]].
+
# Create a DSN for your data source in the [[Lianja ODBC Manager]] and [[Lianja ODBC Manager#Connection_Information|test the DSN]] or test out a DSNless Connection.
 +
 
 +
<div style="height:70px;margin-top:15px;padding:5px;border:0px solid orange;border-left:5px solid orange;background:#fff8dc;vertical-align:middle;position:relative;">
 +
[[File:bm-noteicon.png|top|40px|link=]]<div style="position:absolute;top:7px;bottom:5px;margin-left:50px;">
 +
From Lianja v5.0 Lianja [[Users and Roles]] are used to authenticate ODBC clients on the Lianja Server.<br>This means that [[Users_and_Roles#Row_level_Security_.28RLS.29|Row level Security]] and [[Users_and_Roles#Dynamic_Data_Masks|Dynamic Data Masks]] are also applied to Lianja data accessed via ODBC.
 +
</div>
 +
</div>
 +
 
 +
==DSNless Connection==
 +
To connect to the Lianja ODBC Driver without creating a DSN, use the following string format:
 +
 
 +
<pre>DRIVER=Lianja ODBC Driver;SERVERNAME=<IP or Hostname>;
 +
USERNAME=<user>;PASSWORD=<user-password>;DATABASE=<database></pre>
 +
 
 +
For example:
 +
<code lang="recital">
 +
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
 +
</code>
 +
 
 +
===Local Host===
 +
To connect to the local host, the SERVERNAME can be ''localhost'' or ''?'' and the USERNAME and PASSWORD can be ''?''.
 +
 
 +
<code lang="recital">
 +
lcDSNLess="DRIVER=Lianja ODBC Driver;" ;
 +
+ "SERVERNAME=?;USERNAME=?;PASSWORD=?;DATABASE=southwind"
 +
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
 +
</code>
 +
 
 +
===Including Optional Parameters===
 +
The [[Lianja_ODBC_Manager#Settings|optional parameters]] can be included as follows:
 +
<code lang="recital">
 +
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)
 +
</code>
  
 
==Using ODBC Data Sources in the Lianja App Builder==
 
==Using ODBC Data Sources in the Lianja App Builder==
Line 14: Line 60:
 
* By creating a [[CREATE VIRTUALTABLE|Virtual Table]] in a local database
 
* By creating a [[CREATE VIRTUALTABLE|Virtual Table]] in a local database
 
* In the ODBC Console
 
* In the ODBC Console
−
[[{{ns:file}}:odbc_console.png|150px|thumb|left|ODBC Console]]
+
[[{{ns:file}}:odbc_console.png|150px|thumb|left|link={{filepath:odbc_console.png}}|ODBC Console]]
 
<br clear=all>
 
<br clear=all>
 
* By Importing the data into a new local database
 
* By Importing the data into a new local database
−
[[{{ns:file}}:ImportODBCDatabase.png|150px|thumb|left|Import ODBC Database]]
+
[[{{ns:file}}:ImportODBCDatabase.png|150px|thumb|left|link={{filepath:ImportODBCDatabase.png}}|Import ODBC Database]]
 
<br clear=all>
 
<br clear=all>
  
Line 24: Line 70:
  
 
===Calling Stored Procedures===
 
===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:
+
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:
−
[[{{ns:file}}:StoredProcedure.png|150px|thumb|left|Stored Procedure]]
+
[[{{ns:file}}:StoredProcedure.png|150px|thumb|left|link={{filepath:StoredProcedure.png}}|Stored Procedure]]
−
It takes a parameter, selects data and returns the data as a resultset:
+
It takes a parameter, selects data based on the parameter and returns the data as a [[SETRESULTSET()|resultset]]:
  
 
<code lang="recital">
 
<code lang="recital">
−
// sp_demo.prg
+
// Store Procedure: sp_demo.prg
 
lparameters lcState
 
lparameters lcState
 
select account_no, state from example;
 
select account_no, state from example;
Line 38: Line 84:
 
After being [[Deploying to Local Directory|deployed]] to the Lianja SQL Server database directory, it can be called when an ODBC connection to the southwind database is active:
 
After being [[Deploying to Local Directory|deployed]] to the Lianja SQL Server database directory, it can be called when an ODBC connection to the southwind database is active:
 
<code lang="recital">
 
<code lang="recital">
 +
// Call a Stored Procedure
 
nhand = sqlconnect("Lianja_Southwind")
 
nhand = sqlconnect("Lianja_Southwind")
 
if nhand > 0
 
if nhand > 0
Line 48: Line 95:
 
endif</code>
 
endif</code>
  
−
===Issuing Liana Commands===
+
===Issuing Lianja Commands===
 
Non-UI Lianja commands can be sent to the Lianja SQL Server by prefixing them with the '''lianja''' command.  For example, [[:Category:Set Commands|Set Commands]] can be issued or [[LIST STATUS|status information]] output to a file.
 
Non-UI Lianja commands can be sent to the Lianja SQL Server by prefixing them with the '''lianja''' command.  For example, [[:Category:Set Commands|Set Commands]] can be issued or [[LIST STATUS|status information]] output to a file.
 
<code lang="recital">
 
<code lang="recital">
Line 71: Line 118:
 
sqldisconnect(nhand)
 
sqldisconnect(nhand)
 
endif</code>
 
endif</code>
 +
 +
[[Category:Lianja SQL Server]]
 +
[[Category:Lianja Server]]

Latest revision as of 05:42, 5 September 2019

See Also

Lianja ODBC Driver on Linux, Lianja ODBC Manager on Windows, Lianja Server Manager on Linux, Lianja Server Manager on Windows, SQL Remote Data Connectivity Functions, SQL System Tables

Before Using the Lianja ODBC Driver

Before using the Lianja ODBC Driver, complete the following steps:

  1. 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.
  2. Select your Lianja database or create it in the Lianja App Builder Data Workspace or using SQL.
  3. Copy or Deploy your database to the Lianja SQL Server Database directory.
  4. Make sure the Lianja SQL Server service is running.
  5. Create a DSN for your data source in the Lianja ODBC Manager and test the DSN or test out a DSNless Connection.
Bm-noteicon.png

From Lianja v5.0 Lianja Users and Roles are used to authenticate ODBC clients on the Lianja Server.
This means that Row level Security and Dynamic Data Masks are also applied to Lianja data accessed via ODBC.

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=<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:

ODBC Console


  • By Importing the data into a new local database
Import ODBC 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/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:

Stored Procedure

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
Retrieved from "https://www.lianja.com/doc/index.php?title=Lianja_ODBC_Driver_on_Windows&oldid=22132"