At 08:14 PM 24/01/2005 +0000, you wrote:

>Now, FoxPro accesses non-xBase data either by using ODBC or ADO
>drivers and I have set up a number of the trial versions of both
>drivers for the evaluation. ADO works without problem and returns the
>correct recordsets as expected but it is not so easy to use an ADO
>recordset in FoxPro so I'd prefer to use ODBC which returns an xBase
>table. However there seems to be a problem with all the drivers for ODBC.
>
>When trying to create a remote view on a table in the test database
>using the view designer I get the error:
>
>Connectivity error: Dynamic SQL Error:
>SQL Error Code = -104
>Token unknown - line 1, char 21
>,

First of all, a "connectivity error" would be that the driver can't can't
make a connection to the server; or, if it can find the server, it can't
find the database.

>No matter which of the drivers I use the same error is returned. The
>test database and one empty table was created using the following script:
>
>/* Test.fdb */
>create database "d:\Data\Databases\Test.fdb"
>page_size 4096
>user 'SYSDBA'
>password 'masterkey'
>;

One problem I see with the above is the use of double quotes around the
file path string. It should be single quotes. If you were able to create
this database, then possibly the driver has fixed this up for you; or
(unhappily) the driver doesn't support the Firebird on-disk structure or
Dialect 3, and thinks it is creating an InterBase 5 database.

You would need a COMMIT; statement in your script following the CREATE
DATABASE statement. If the database can actually be created, COMMIT will
cause the engine to
1) create a file in the path given
2) create the database header, system tables and some other needed
structures, including database pages (chunks of disk that the server buys
from the filesystem as and when required)
3) create the system tables and other objects needed to manage them
4) and finally, connects to the database.

The missing COMMIT here is the probable cause of the error. When the
engine receives the CREATE TABLE request, it is not connected to a
database. The driver supplies the path of the supposed database but the
server can't find a database file at that location, because it doesn't
exist yet.

>create table Lng
>(
> nLngPK integer not null,
> cLngKey varchar(30),
> cActive char(1)
>)
>;
>
>The ODBC conection was created using the Windows ODBC administration
>wizard (WinXP) using a predefined data source rather than a
>connectivity string, although a quick test using a connection string
>gave the same result.

Use Firebird's own ODBC driver.

>If, however, I build the view manually, a tedious process to be
>avoided whenever possible, the process is sucessful.

The code you supplied doesn't "build a view". Firebird supports several
table types, of which TABLE and VIEW are two. TABLE is a physical
structure that stores data; VIEW is a logical definition that stores a
compiled SELECT specification on one or more tables, that delivers output
as though the structure were a table itself, i.e. it is one form of what's
known as "virtual tables".

>Has anyone any constructive ideas?
>
>I suspect that, since it is only the FoxPro view designer that has the
>problem, it is a problem with the view designer rather than anything
>else, but there is also the possibility that I'm doing something wrong
>and hence the call for help.

It should be reasonably OK (in a limited sense) if you use the correct ODBC
driver. I'd be especially concerned to make sure that any databases you
create are dialect 3 databases, and that the ODBC datasource is set up to
connect as a dialect 3 client -- unless you just want to "mess about", that
is. A dialect 1 database is just a mostly-compatible InterBase 5
database; it has different data types, different data structure rules for
number types; and it doesn't support the numerous language enhancements
that Firebird has.

>I should mention that since we have over 7 years of developement of
>our products using FoxPro we will not be changing to any other
>development language in the near future, so answers to the effect of
>use VB/Java/C++/... etc will be ignored.

Firebird is independent of any development language; however, you will
encounter some "linguistic" limitations by using a language that was
designed for a low-end, file-based data management system. You need to
take account of transactions - which you can deal with by the appropriate
ODBC driver settings (get the oil on this by joining the
firebird-odbc-devel mail list) and live with the fairly crippled sort of
interface that is necessary to drag Firebird down to the level of database
support that the VFP language is capable of.

I'd guess that you will reach a point where you will recognise that these
limitations are standing in the way of doing an effective upscaling. While
using Firebird as the back-end will offer improvements in robustness and
(with good object structures and optimal SQL) improved performance, you'll
be denying yourself the real benefits of a properly separated client/server
architecture.

./hb