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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.