Category: VFP ODBC driver

ODBC [examples]

Published on by homunizamLeave a comment
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

VFP ODBC drivers

Published on by homunizamLeave a comment

Q:

Generally we write our apps to hit more than one backend.

Various of our routines (e.g., “what is the current database”) have to know the backend type in order to use the appropriate command to return the database name.

To do that, we need to know the connection type before we have created a cursor (if we have created one, the cursoradapter dbtype property does the trick). Note that from the application’s perspective, it was given a connection string and create a connection with it.

To summarize:

1) how to use a handle to find the dbtype in the absence of a cursor?

2) with regard to Lianja ODBC, how to find the current database? Essentially, return database() from the Lianja SQL Server. Note that if the Catalog Column from systables is always the name of the database, this one is solved. If not, then I need another way. IOW: is there a 1:1 correspondence between the database directory name and the .cat file name?

A:

There is no way with ODBC to determine the type of data that you are connected to.

Lianja uses heuristics to do this internally and then sets dbtype as you mentioned.

There is also no way to determine the database that is specified in the ODBC connection string. You can however evaluate an expression and get its value e.g.

select database() from sysresultset

Yes the database directory name and the .cat file are the same.

Q:

I need to be able to do select statements from Lianja against the vfp table created by xcase so I can scan though and do the work needed to crate / update table, create indexes in Lianja etc.

Since the tables don’t belong to a datatbase what is the best way to do this? I assume I have to have database to use ODBC?

I have tried with a test table ( this one does belong to a database)

select * from c:\1temp\daddress into cursor temp1

and get an error

Fri Apr 7 18:12:12 2017
**** Lianja error ****
DADDRESS.LA10AUTHOR
^
Fatal I/O error writing record 1 to table – errno 22

Q2:

I’ve just tried to copy one of the tables to a Lianja table using
use c:\1temp\ddent noupdate
copy to c:\1temp\l_ddent with production

and get

Fri Apr 7 21:10:46 2017
**** Lianja error ****
copy to c:\1temp\l_ddent with production
^
ERROR – invalid record length

Hank tells me that the Xcase case tables are in foxpro 2.x rather than VFP type so maybe that is an issue.

A:

I am able to USE that table and COPY TO another_name to put it in Lianja format.

SQL SELECT is working Ok in 3,.4 with that table too although its not really supported as you need to convert to Lianja format so that the query optimizer can do its work.

Name: Screen Shot 2017-04-08 at 2.27.43 PM.jpg Views: 27 Size: 89.1 KB

Just USE tablename ALIAS whatever then perform your SQL SELECTSs.

Just an FYI, Lianja supports the following XBase file formats natively readonly.

VFP
FoxPro 2.x
Clipper

A2:

in my opinion, the best thing is to create an ODBC link to your xcase table, then create a VT on that table.

With a old FoxPro table, I do that.

Q:

Can you make ODBC links to tables that are not part of a database?

The code I have looked at for the ODBC connection wants the name of the database

A:

For free VFP table you need VFP ODBC driver.