[Answers] VFP ODBC Driver

Printable View

2015-10-16, 03:35
josipradnik

[Answers] VFP ODBC Driver

MS VFP ODBC Driver does not support the 'Integer (AutoInc)' data type. If a table has AutoInc fields, the VFP ODBC Driver will report (can be seen in Lianja debug file):
'error=[Microsoft][ODBC Visual FoxPro Driver]Not a table.'
Q:
Is there anyway of connecting to an existing backend VFP database?
A:
Use an ODBC Driver for VFP and Lianja Virtual Tables.

Q:
I know vfpoledb is really just activex.. Are there any issues using vfpoledb to connect to my vfp tables and doing updates... I'm going to be running parallel with our old system... I was told that lianja itself can open vfp tables and update them, however, the indexes don't update...
A:
I have not tried it but if you install the vfpoledb driver then in Lianja;

Code:

SET DEBUG ON vfpda = createObject("OLEDB.Connection")

I'd be interested to see if it loads and what is in debug_client.txt

Q:
I'll use odbc then.. I was going to only use the prgs for reports.. this way I could tweak them on the fly without a recompile...
A:
Then you would need to set the connectionstring.
vfpda.ConnectionString = "Provider=VFPOLEDB.1;.....

All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352
2016-03-14, 04:01
josipradnik

Q:
I'm using the app builder in a windows environment and the data is stored on a Linux machine and accessed via ODBC.
1. How do I verify what indexes are created on a table?
- Using the ODBC console tab doesn't seem to help since it appears to just be creating a temporary table and LIST STAT doesn't show indexes (a .DBX file may or may not exist).
2. How do I create an index for a table?
- I don't know if INDEX on fieldname tag tagname is working (see point number 1).
3. How do I set and use an index when virtual tables are used?
A:
If you are doing it all remotely from the ODBC Console in the App Builder, with the DSN open, you can:
1. Use the SQL CREATE INDEX command to create indexes.
2. Query the SYSINDEXINFO system table to determine which indexes exist, their keys etc. Just use a WHERE clause (the structure is listed on that wiki page) to restrict to the table and/or key you are interested in.

Q:
In the ODBC tab, I tried the following:

Code:

use <tablename> SELECT * FROM sysindexinfo

A:
Try in a new ODBC connection,without opening <tablename>, but specifying it in the SELECT statement:

Code:

select * from sysindexinfo where table_name = "<tablename>"

All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352
2017-02-10, 06:07
josipradnik

Q:
I'm try to access FoxPro 2.6 data. From console, if I write:
use E:\cogema\dati\articoli
the table are opened correctly, I can use this way?
I've also try to configure ODBC to access the data through ODBC, then I've created a VT.
The first time, I've opened the table... then, close and open Lianja, the table is showed as "Encrypted".. Why?
A:
Read/Write to FoxPro tables is not supported. You should be able to import it into a Lianja database
'encrypted' will be displayed if there is a problem with the VT and the data cannot be accessed.
Try closing the database and deleting the VT files in the C:\Lianja\data\cogema\ folder, then rebuild the VT.
Q2:
Found! the problem is the MEMO field... now I try to reinsert the general.

Q:
I have been poking about Virtual Tables and decided to investigate their use on a little project using data from native VFP tables.
Followed samples and it seems to work fine except for an odd occurrence when the VT reads the VFP table, all integers are read as floats.
I'm not sure how VFP treats ints internally, but they are used in these tables as PK and FKs. I found a topic here on MySQL floats, so I set precision=0 and the VT now looks correct
A:
The VFP ODBC driver is not terribly good but it may work for your use case. It reports Int values with incorrect precision which cause them to be seen as float. It appears that you have resolved that with the precision=0 ODBC DSN setting.

Q:
I would like to use ODBC to pull data from an existing foxpro table into a Lianja virtual table.
As Microsoft no longer supports its VFP ODBC driver (and does not appear to offer a download link anymore), is there a different ODBC driver that I should use? As the foxpro tables are part of an active application that will continue to be used alongside the Lianja app, importing them into Lianja is not an option. I need to be able to retrieve data from the foxpro dbf frequently using an SQL SELECT command. Updating the foxpro table will not be required.
A:
In fact if you USE a foxpro table NOUPDATE then you can access the data and COPY it to a Lianja table.

Q:
My goal is to be able to query the Foxpro data files in real time, rather than resorting to populating the Lianja database overnight.
A:
You would not need to use VTs at all in this use case. You would just use sqlconnect(), sqlexec() etc with an ODBC driver.

I don't know what the selection criteria is going to be. If it is from a known last record number you can GOTO that record and then use COPY REST.

Another solution is to perform your queries in VFP itself as a stored procedure and return the resultset.
For example you could create a known file and have a VFP job looking for its existence.
When it finds it, it performs the query (contained within the file) then leaves the resultset in a known table and deletes the text file. The Lianja side then just copies the data from that table.

The tables can be open by VFP and Lianja if you open non exclusive.

Lianja ODBC driver communicates with the server using TCP/IP so it can in fact be used across the Internet.

When the cloud server us running Lianja SQL is running too.

Q:
as long as VFP9 accesses the Lianja SQL Server via the Lianja ODBC driver, either configuration would work. Would this work using an Amazon AWS-hosted instance of Lianja Cloud Server?
A:
Yes. Lianja SQL Server is part of the Lianja Cloud Server install.
When you create an AWS EC2 instance it has a FQDN which you can point to from DNS e.g. You purchase a meaningful domain name from network solutions (or other) and set it up to point at your AWS FQDN. So yes is the answer.

So you can have web apps sharing the data with Lianja SQL server and VFP accessing the data using an ODBC connection to Lianja SQL server.

Q:
How to connect by ODBC to PostgresSQL or other ODBC driver ?
A:

1) on a 64-bit windows system, use the correct ODBC manager (in the %windir%\syswow64 directory, the exe is odbcad32.exe).
2) you have to install the ODBC drivers (the 32-bit versions, using the 32-bit ODBC manager).
3) create System DSN's.

Q:
I am using Lianja ODBC from VFP. I tried to set SQLSETPROP(ConHandle,"Transactions",2) to manual setting but it gave me this error

Warning: The connection transaction mode cannot be set.
How can I control transaction via Lianja ODBC from other applications ?
A:
SQLEXEC() command can be prefixed with lianja to execute any SET commands or other connection initialization.
? sqlexec(ConnHandle,"Lianja set date british")
A2:
Any Lianja command can be run, I believe.
Presumably this would include running a prg, which if it return a sysresultset, could return a cursor.
I haven't tried it, but it's an interesting thought (a lot of our complex reports do a lot of selects, and then munge the data into a cursor -- having it all run on the server would be neat).

All topics in [Answers] alphabetically:http://www.lianja.com/community/showthread.php?2717-Answers
2018-01-29, 03:35
josipradnik

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)

Code:

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

and get an error

Quote:

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

Code:

use c:\1temp\ddent noupdate copy to c:\1temp\l_ddent with production

and get

Quote:

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.

https://www.lianja.com/community/att...9&d=1491636510

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.

All topics in [Answers] alphabetically: https://www.lianja.com/community/sho...ll=1#post13748

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...p-odbc-driver/