dBASE Questions and Answers - Token Error in SQL property

Author

Token Error in SQL property

john marshall

2005-07-26, 8:24 pm

I have a number of dated folders that I need to get into. I create a SQL line, like the one below, and set it into the form.quary1.sql := and requery(). I get a token error / invalid use of keyword select ... any suggestions?

sql = 'select * from " z:\aloha\20050705\ad
jtime.DBF" order by employee'

I have requestlive set false. Is there something else that I am missing?

PS: I have a routine that can get into these folders using Xbase, but wanted to convert to dbl and sql to set the order.

Roland Wingerter

2005-07-27, 3:25 am

john marshall wrote:
> I have a number of dated folders that I need to get into. I create a
> SQL line, like the one below, and set it into the form.quary1.sql :=
> and requery(). I get a token error / invalid use of keyword select
> ... any suggestions?
>
> sql = 'select * from " z:\aloha\20050705\ad
jtime.DBF" order by
> employee'
>
> I have requestlive set false. Is there something else that I am
> missing?
>
> PS: I have a routine that can get into these folders using Xbase,
> but wanted to convert to dbl and sql to set the order.
-------
I set up a query with a path like yours and it works as expected.

Here is the code I used:

function form_onOpen
cSql = [select * from ".\20050705\test.dbf" order by field1]
form.query1.sql = cSql
form.query1.requery()

form.grid1.datalink = form.query1.rowset
return

Roland

John Marshall

2005-07-27, 7:27 am

Is there any chance that the db table level of less than 7 could affect it in this way? These are 3rd party tables, and I noticed that the index files were cdx, not mdx. Is there a way to check what level a file was created under? Also, if this is the
case, I can't imagine that it is efficient to modify the bde directory over pointing to a file in the sql stmt. I noticed that in trying different combinations that there was an error saying error creating or finding a lock file. I'm really baffled and
frustrated here. I have these two projects that are being difficult. I am temped just to stick with xbase (currently working) rather than continuing to pursuing the more efficient method. As always, I'd appreciate any further insights.

JM

Roland Wingerter Wrote:

> john marshall wrote:
> -------
> I set up a query with a path like yours and it works as expected.
>
> Here is the code I used:
>
> function form_onOpen
> cSql = [select * from ".\20050705\test.dbf" order by field1]
> form.query1.sql = cSql
> form.query1.requery()
>
> form.grid1.datalink = form.query1.rowset
> return
>
> Roland
>
>

Roland Wingerter

2005-07-27, 7:27 am

John Marshall wrote:
> These are 3rd party tables, and I noticed
> that the index files were cdx, not mdx.
------
Looks like your dbf table is not a dbase table. The file extension cdx is
used by Visual Foxpro.

There are different versions of Foxpro. In any case you have to set up a
database alias in the BDE, then you should be able to open the tables from
dBASE. The BDE has a native Foxpro driver which should work for older
Foxpro versions. If it is a more recent Foxpro table format, you need to set
up an ODBC driver. This message describes how to do that (watch line wrap):

>
[url]http://64.132.211.166/App/FmtNws.dbw? NwsFile=f%3A%5Ccorpu
snws%5Cen%5Cgetting[
/url]
%2Dstarted%5C8884%2E
nws& Subject=Re%3A+What%2
7s+up+with+this+file
%3F

Hope this helps

Roland

Mervyn Bick

2005-07-27, 7:27 am

On Wed, 27 Jul 2005 13:04:02 +0200, John Marshall
<john. marshall@mainstreetp
rinceton.com> wrote:

> Is there any chance that the db table level of less than 7 could affect
> it in this way? These are 3rd party tables, and I noticed that the
> index files were cdx, not mdx. Is there a way to check what level a
> file was created under?

Someone else may come up with something better but bits 0-2 of the first
byte of a .dbf table give the level. Level 7 tables are 4, any lower
level is 3. You can use the lowlevel file functions to extract the first
byte of the file or you can use a hex editor like Xvi32 to inspect the
file. (Google xvi32 if you dont have it) Have a look at the Knowledgebase
Intermediate section for a description of the level 7 file structure.

Mervyn

--
The ns_ in my address is an attempt to thwart SPAM.

john marshall

2005-07-27, 7:27 am

That link said that there was nothing found. Do you have a title I can search on? Also, since I must change the dated folder for each day, how do I integrate that into an alias (since they are folder specific)? Or is there a way to specify the db alias
and still search in a folder beneath it?

JM

Roland Wingerter Wrote:

> John Marshall wrote:
> ------
> Looks like your dbf table is not a dbase table. The file extension cdx is
> used by Visual Foxpro.
>
> There are different versions of Foxpro. In any case you have to set up a
> database alias in the BDE, then you should be able to open the tables from
> dBASE. The BDE has a native Foxpro driver which should work for older
> Foxpro versions. If it is a more recent Foxpro table format, you need to set
> up an ODBC driver. This message describes how to do that (watch line wrap):
>
> [url]http://64.132.211.166/App/FmtNws.dbw? NwsFile=f%3A%5Ccorpu
snws%5Cen%5Cgetting[
/url]
> %2Dstarted%5C8884%2E
nws& Subject=Re%3A+What%2
7s+up+with+this+file
%3F
>
> Hope this helps
>
> Roland
>
>

Roland Wingerter

2005-07-27, 9:23 am

john marshall wrote:
> That link said that there was nothing found. Do you have a title I
> can search on?
-------
The subject was: "What's up with this file?"

> Also, since I must change the dated folder for each
> day, how do I integrate that into an alias (since they are folder
> specific)? Or is there a way to specify the db alias and still
> search in a folder beneath it?
-------
Sorry, I don't know and right now I don't have the time to try it out. Hope
someone else will jump in. If not, open a new thread.

Roland

john marshall

2005-07-27, 9:23 am

Is there a way to change just the directory using the bdealias/ex? (not create or delete) Since I need to flip through several folders, this may be an answer to reading variable VFoxPro dbf's.

JM

Roland Wingerter Wrote:

> john marshall wrote:
> -------
> The subject was: "What's up with this file?"
>
> -------
> Sorry, I don't know and right now I don't have the time to try it out. Hope
> someone else will jump in. If not, open a new thread.
>
> Roland
>
>

Roland Wingerter

2005-07-27, 11:37 am

john marshall wrote:
> Is there a way to change just the directory using the bdealias/ex?
> (not create or delete)
> Since I need to flip through several folders,
> this may be an answer to reading variable VFoxPro dbf's.
------
There is no method in bdeAlias.cc to change the directory path of a BDE
alias other than deleting an alias and recreating it with another database
path.

If the alias name already exists the createAlias() method will fail.

Deleting and recreating the alias may be a solution if the bde.restart()
method works for you - close all tables, programmatically make your changes
in the BDE and restart the BDE while your app is running.

Roland

john marshall

2005-07-27, 11:37 am

Do you find it strange that I can navigate through these files using xbase, but not using sql? I can perform replace ops, seeks, etc. My less than elegant solution was to sort to another file and loop through it. I'd prefer not to use that kind of over
head. Could this be a bug?

JM

Roland Wingerter Wrote:

> john marshall wrote:
> ------
> There is no method in bdeAlias.cc to change the directory path of a BDE
> alias other than deleting an alias and recreating it with another database
> path.
>
> If the alias name already exists the createAlias() method will fail.
>

> Deleting and recreating the alias may be a solution if the bde.restart()
> method works for you - close all tables, programmatically make your changes
> in the BDE and restart the BDE while your app is running.
>
> Roland
>
>

john marshall

2005-07-27, 11:37 am

David Kerber

2005-07-27, 11:37 am

In article <EdPmfvrkFHA.1796@news-server>,
john. marshall@mainstreetp
rinceton.com says...
>
> Is there a way to change just the directory using the bdealias/ex? (not create or delete) Since I need to flip through several folders, this may be an answer to reading variable VFoxPro dbf's.

You could change the registry entry directly, if the logged-on user has
the appropriate permissions.

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Roland Wingerter

2005-07-27, 1:28 pm

john marshall wrote:
> Do you find it strange that I can navigate through these files using
> xbase, but not using sql? I can perform replace ops, seeks, etc.
-------
Strange indeed. Have you set up a BDE alias?

Note that there is an important difference between these two queries:

- select * from " z:\aloha\20050705\ad
jtime"
- select * from :foxpro:adjtime // insert your alias name

The first query will use the default driver. The second query will use the
driver defined for that database in the BDE.

The same would apply to the corresponding xBase commands:

- use " z:\aloha\20050705\ad
jtime.DBF"
- open database foxpro ; use :foxpro:adjtime

Roland

john marshall

2005-07-27, 8:24 pm

Xbase just works fine. Is there a way to set the default driver to something else temporarily (foxpro)? The alias is fine, except the dated folder needs to change. The routine normally is processing blocks of 14 days. Either I delete and reset the BDE
alias (is there a danger in this?), or I may have to copy or 'sort to' a new file, although that is not terribly machine efficient either.

Back to the xbase default driver ... is it possible that xbase is using a different default dbase driver than the sql/dbl side? How might one tell? Is it possible to use an alias and specify a file/folder below the alias location i.e. ( :alias1:\2005070
5\adjtime.dbf) almost as one would specify a server in front of a path ?

I noticed that you extended the functionality of the BDEAliase.cc, what (in your opinion) would it take to make another tweak to allow just a path change of an existing BDE?

Thanks,

JM

Roland Wingerter Wrote:

> john marshall wrote:
> -------
> Strange indeed. Have you set up a BDE alias?
>
> Note that there is an important difference between these two queries:
>
> - select * from " z:\aloha\20050705\ad
jtime"
> - select * from :foxpro:adjtime // insert your alias name
>
> The first query will use the default driver. The second query will use the
> driver defined for that database in the BDE.
>
> The same would apply to the corresponding xBase commands:
>
> - use " z:\aloha\20050705\ad
jtime.DBF"
> - open database foxpro ; use :foxpro:adjtime
>
> Roland
>
>
>

Ivar B. Jessen

2005-07-27, 8:24 pm

On Wed, 27 Jul 2005 11:00:05 -0400, john marshall
<john. marshall@mainstreetp
rinceton.com> wrote:

>
>Is there a way to change just the directory using the bdealias/ex? (not create or delete) Since I need to flip through several folders, this may be an answer to reading variable VFoxPro dbf's.
>

In your original posting you gave this example which does not use an
BDE alias,

sql = 'select * from " z:\aloha\20050705\ad
jtime.DBF" order by
employee'

could you explain a little more detailed why you now need the alias
and how you run the form, from an exe or from the IDE.

Ivar B. Jessen

Roland Wingerter

2005-07-27, 8:24 pm

Ivar B. Jessen wrote:
>
> In your original posting you gave this example which does not use an
> BDE alias,
>
> sql = 'select * from " z:\aloha\20050705\ad
jtime.DBF" order by
> employee'
>
> could you explain a little more detailed why you now need the alias
-------
As stated in John's first message, the above sql statement leads to an
error.

The index file for the dbf has the extension cdx it seems the dbf is a
Foxpro table, so I suggested to set up a BDE alias with the appropriate
driver.

Roland

Roland Wingerter

2005-07-27, 8:24 pm

john marshall wrote:
> Xbase just works fine. Is there a way to set the default driver to
> something else temporarily (foxpro)?
------
Yes, by using a database alias.

oDB = new database("foxpro")
oDB.active := true
? oDB.driverName

inspect(oDB)

> The alias is fine, except the
> dated folder needs to change. The routine normally is processing
> blocks of 14 days. Either I delete and reset the BDE alias (is there
> a danger in this?), or I may have to copy or 'sort to' a new file,
> although that is not terribly machine efficient either.
------
IIRC it has been reported that the bdeRestart() method in bdeAliasEx.cc for
some reason does not work on all computers.

> Back to the xbase default driver ... is it possible that xbase is
> using a different default dbase driver than the sql/dbl side?
------
I don't think so.

> How
> might one tell? Is it possible to use an alias and specify a
> file/folder below the alias location i.e. (
> :alias1:\20050705\ad
jtime.dbf) almost as one would specify a server
> in front of a path ?
------
I just tried, but could not get it to work.

> I noticed that you extended the functionality of the BDEAliase.cc,
> what (in your opinion) would it take to make another tweak to allow
> just a path change of an existing BDE?
------
I would not know how to do it, other than deleting and recreating the alias.
The extension I made was just a simple modification of code written by
others...

Roland

Hans-Peter Neuwirth

2005-07-27, 8:24 pm

> Xbase just works fine. Is there a way to set the default driver to
> something else temporarily (foxpro)? The alias is fine, except the
> dated folder needs to change.

Seems, that this is no FoxPro-Table. CDX was used by Clipper and ComiX, or
by CA Visual Objelcts (not only by Fox). And this products work with ASCII
and not ANSI. If the Index is invalid with the FoxPro-Driver you will get a
problem.

Please check first which type of table and index you have.

What do you mean with Xbase .... do you mean XBASE++ (the product) or
something else. XBASE ist most time used to describe a group of different
dialects or programming languages derived from dBASE.

If it is a table created with XBASE++ the index is not compatible with the
BDE.

Please give us more information!

hpn

--
dBKON 2005 - 4. deutschsprachige dBASE Entwickler-
und Anwenderkonferenz am 11. und 12. November 2005
Neueste Infos unter: www.dbase-konferenz.de

Roland Wingerter

2005-07-28, 3:24 am

Hans-Peter Neuwirth wrote:
>
> Seems, that this is no FoxPro-Table. CDX was used by Clipper and
> ComiX, or by CA Visual Objelcts (not only by Fox). And this products
> work with ASCII and not ANSI. If the Index is invalid with the
> FoxPro-Driver you will get a problem.
>
> Please check first which type of table and index you have.
------
Hans-Peter,

thanks for jumping in, I should have known other xBase products use the cdx
extension too.

Roland

Ivar B. Jessen

2005-07-28, 3:24 am

On Wed, 27 Jul 2005 22:00:42 +0200, "Roland Wingerter" <RW@germany.de>
wrote:

>-------
>As stated in John's first message, the above sql statement leads to an
>error.
>
>The index file for the dbf has the extension cdx it seems the dbf is a
>Foxpro table, so I suggested to set up a BDE alias with the appropriate
>driver.

Roland,

Thanks, I am a little late this discussion here. If I understand
correctly the problem is now how to use a BDE alias and then point the
sql to a table in a different subfolder without changing the alias
name or touching the BDE at all.

It should be doable, see the code below my signature.

Ivar B. Jessen

//-----
d = new database()
d.databasename = "dbasesamples"
d.active = true

cPath = " Contax\Data\Appoints
.dbf"

q = new query()
q.database = d
q.sql = 'select * from &cPath'
q.active = true

clear
q.rowset.first()
?
do while not q.rowset.endofset
for i = 4 to 8 //q.rowset.fields.size
?? q.rowset.fields[i].value,
next i
q.rowset.next()
?
enddo
//-----

Roland Wingerter

2005-07-28, 7:23 am

Ivar B. Jessen wrote:
>
> Thanks, I am a little late this discussion here. If I understand
> correctly the problem is now how to use a BDE alias and then point the
> sql to a table in a different subfolder without changing the alias
> name or touching the BDE at all.
>
> It should be doable, see the code below my signature.
-------
Ivar,

I tried your code and it works fine. I'll store this away for later
reference, I am sure it will be useful one day.

Thanks

Roland

Ivar B. Jessen

2005-07-28, 7:23 am

On Thu, 28 Jul 2005 11:01:33 +0200, "Roland Wingerter" <RW@germany.de>
wrote:

>-------
>Ivar,
>
>I tried your code and it works fine. I'll store this away for later
>reference, I am sure it will be useful one day.

Thank you for the confirmation.

It also works when you leave out the dbf extension in the path.

cPath = 'Contax\Data\Appoint
s'

Ivar B. Jessen

john marshall

2005-07-28, 11:36 am

Roland:

When I had a problem with an Access bde requiring a login/password combination, you recommended using the dflup.cc and it worked fine. In pursuit of this 'mystery' .dbf and in attempting to set up a bde alias for Visual Foxpro, when I do it in the BDE
Administrator I get the same login/password issue. I am considering using your bdealiasex.cc, but I am not sure which driver to specify. Any insights? The one I think should work is the "Microsoft dBase VFP Driver ..." - I just don't know what to assi
gn to the cDriver variable.

Thanks,

JM

Roland Wingerter Wrote:

> Ivar B. Jessen wrote:
> -------
> Ivar,
>
> I tried your code and it works fine. I'll store this away for later
> reference, I am sure it will be useful one day.
>
> Thanks
>
> Roland
>
>
>

Roland Wingerter

2005-07-29, 3:24 am

john marshall wrote:
>
> When I had a problem with an Access bde requiring a login/password
> combination, you recommended using the dflup.cc and it worked fine.
> In pursuit of this 'mystery' .dbf and in attempting to set up a bde
> alias for Visual Foxpro, when I do it in the BDE Administrator I get
> the same login/password issue. I am considering using your
> bdealiasex.cc, but I am not sure which driver to specify. Any
> insights? The one I think should work is the "Microsoft dBase VFP
> Driver ..." - I just don't know what to assign to the cDriver
> variable.
-------
As Hans-Peter Neuwirth said, the cdx extension is also used by other xBase
products like Clipper and Visual Objects. So setting up a foxpro driver
might not help, but you can try nevertheless. You have to find out what sort
of dbf format you have. This site might help:

http://www.clicketyclick.dk/databases/xbase/format/

The driver "FOXPRO" is for older (DOS-based) Foxpro versions.
The "Microsoft dBase VFP Driver ..." is an ODBC driver for Visual Foxpro.

I don't know about drivers for Clipper and Visual Objects.

Roland

Sponsored Links