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
Lianja developer 