SQL Gotcha: insert

#1

I got bit in an interesting way today. I have not seen this
documented elsewhere.

Just before the following statement, wonbr is defined.
Nonetheless, the following statement throws the error "Variable
'WONBR' is not found.".

insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,wonbr)

The catch is that wonbr is a column in another table.
Apparently, while executing the insert, VFP internally changes the
work area. This results in wonbr no longer being a valid name, and
the error occurs.

I replaced the above with
local extwonbr
extwonbr=wonbr
insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,extwonbr)
and all was fine.

Sincerely,

Gene Wirchenko

#2

Gene,
You could also just add the table's alias. e.g.
insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr, mytable.wonbr)

Rick

"Gene Wirchenko" <genew (AT) ucantrade (DOT) com.NOTHERE> wrote

Quote:

I got bit in an interesting way today. I have not seen this
documented elsewhere.

Just before the following statement, wonbr is defined.
Nonetheless, the following statement throws the error "Variable
'WONBR' is not found.".

insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,wonbr)

The catch is that wonbr is a column in another table.
Apparently, while executing the insert, VFP internally changes the
work area. This results in wonbr no longer being a valid name, and
the error occurs.

I replaced the above with
local extwonbr
extwonbr=wonbr
insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,extwonbr)
and all was fine.

Sincerely,

Gene Wirchenko

#3

Or if you wanted to explicitly use the memvar, use the m. prefix.

Dan

Gene Wirchenko wrote:

Quote:

I got bit in an interesting way today. I have not seen this
documented elsewhere.

Just before the following statement, wonbr is defined.
Nonetheless, the following statement throws the error "Variable
'WONBR' is not found.".

insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,wonbr)

The catch is that wonbr is a column in another table.
Apparently, while executing the insert, VFP internally changes the
work area. This results in wonbr no longer being a valid name, and
the error occurs.

I replaced the above with
local extwonbr
extwonbr=wonbr
insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,extwonbr)
and all was fine.

Sincerely,

Gene Wirchenko

#4

I always include the table alias when refering to fields.
It makes the code *much* easier to read and work with later.
And it also avoids conflicts like you encountered.

-- TRW

Rick Bean seemed to utter in news:u9ErLY$AFHA.2804 (AT) TK2MSFTNGP15 (DOT) phx.gbl:

Quote:

Gene,
You could also just add the table's alias. e.g.
insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr, mytable.wonbr)

Rick

"Gene Wirchenko" <genew (AT) ucantrade (DOT) com.NOTHERE> wrote in message
news:r15gv0li69v5n3dnm044uiojoknch1r1n2 (AT) 4ax (DOT) com...
I got bit in an interesting way today. I have not seen this
documented elsewhere.

Just before the following statement, wonbr is defined.
Nonetheless, the following statement throws the error "Variable
'WONBR' is not found.".

insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,wonbr)

The catch is that wonbr is a column in another table.
Apparently, while executing the insert, VFP internally changes the
work area. This results in wonbr no longer being a valid name, and
the error occurs.

I replaced the above with
local extwonbr
extwonbr=wonbr
insert into (woxref);
(currwonbr,nmwonbr) values (savewonbr,extwonbr)
and all was fine.

Sincerely,

Gene Wirchenko

--
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

#5

On Thu, 27 Jan 2005 16:56:34 GMT, trw7at (AT) ixdot (DOT) netcomdotcom (Tim
Witort) wrote:

Quote:

I always include the table alias when refering to fields.
It makes the code *much* easier to read and work with later.

I avoid aliases where possible for exactly that reason. If the
expressions are long, it makes them that much harder to follow.

Quote:

And it also avoids conflicts like you encountered.

I would call what I ran into a minor bug.

[snip]

Sincerely,

Gene Wirchenko

#6

On Wed, 26 Jan 2005 16:24:32 -0800, "Dan Freeman" <spam (AT) microsoft (DOT) com>
wrote:

Quote:

Or if you wanted to explicitly use the memvar, use the m. prefix.

What memvar?

[snip]

Quote:

The catch is that wonbr is a column in another table.

[snip]

Sincerely,

Gene Wirchenko

#7

Gene,
extwonbr is a memory variable (memvar).

The insert should read:
insert into (woxref);
(currwonbr,nmwonbr) values (m.savewonbr, m.extwonbr)

Rick

"Gene Wirchenko" <genew (AT) ucantrade (DOT) com.NOTHERE> wrote

Quote:

On Wed, 26 Jan 2005 16:24:32 -0800, "Dan Freeman" <spam (AT) microsoft (DOT) com
wrote:

Or if you wanted to explicitly use the memvar, use the m. prefix.

What memvar?

[snip]

The catch is that wonbr is a column in another table.

[snip]

Sincerely,

Gene Wirchenko

#8

Gene Wirchenko wrote:

Quote:

On Thu, 27 Jan 2005 16:56:34 GMT, trw7at (AT) ixdot (DOT) netcomdotcom (Tim
Witort) wrote:

I always include the table alias when refering to fields.
It makes the code *much* easier to read and work with later.

I avoid aliases where possible for exactly that reason. If the
expressions are long, it makes them that much harder to follow.

And it also avoids conflicts like you encountered.

I would call what I ran into a minor bug.

[snip]

Sincerely,

Gene Wirchenko

Actually I would not call this a bug as the docs do state more than once

that nothing can be assumed about the environment when any of the SQL
commands are being run. Any time I do something SQL wise that doesn't
imply the alias/table name I put it in there as the work are your in not
only isn't guaranteed to be the same as what you thought it would be,
it's not really even guaranteed to be the same each time.

#9

On Thu, 27 Jan 2005 21:24:09 GMT, Cyrus Welch <cywelch (AT) hotmail (DOT) com>
wrote:

[snip]

Quote:

Actually I would not call this a bug as the docs do state more than once
that nothing can be assumed about the environment when any of the SQL
commands are being run. Any time I do something SQL wise that doesn't

And after, too. SQL leaves tables open. If you are also using
xBASE commands, this can be deadly. I wrote a short routine for
closing what SQL opens.

Quote:

imply the alias/table name I put it in there as the work are your in not
only isn't guaranteed to be the same as what you thought it would be,
it's not really even guaranteed to be the same each time.

And it bit me this time.

Sincerely,

Gene Wirchenko

#10

On Thu, 27 Jan 2005 09:47:51 -0800 in comp.databases.xbase.fox,
Gene Wirchenko <genew (AT) ucantrade (DOT) com.NOTHERE> wrote:

Quote:

On Thu, 27 Jan 2005 16:56:34 GMT, trw7at (AT) ixdot (DOT) netcomdotcom (Tim
Witort) wrote:

I always include the table alias when refering to fields.
It makes the code *much* easier to read and work with later.

I avoid aliases where possible for exactly that reason. If the
expressions are long, it makes them that much harder to follow.

uhhh, no. code is much easier to follow if the full data names

are used. a good code editor makes such things easy to type in,
and even verbose code is easier to read if it has meaningful data
names.

Quote:

And it also avoids conflicts like you encountered.

I would call what I ran into a minor bug.

in YOUR code, not in foxpro. it worked AS DESIGNED and as

documented. the error was your fault for NOT using the full data
name according to the foxpro specs.

To send an email, remove the nospam...