Coding puzzle of WITH... ENDWITH

[bfwriter]

Hello friends,

Can anyone fill me in on the general principle involved in the following code failure (so I don't lose a lot of hair-pulling time trying to figure it out, as I did on this one)?

The following code fails to compile, giving an error message: "Command contains unrecognized phrase/keyword"

cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
	FROM checkreg, acctlist INTO dbf temptbl ;
	WHERE checkreg.acctidn = acctlist.acctidn ;
	AND LOWER(cSrchVar) $ LOWER(descr) ;
	and BETWEEN(YEAR(date), thisform.nYear1, thisform.nYear2) ;
	ORDER BY date"
&cCmdStr

whereas this (seemingly equivalent) one works just fine...

WITH thisform	
cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
	FROM checkreg, acctlist INTO dbf temptbl ;
	WHERE checkreg.acctidn = acctlist.acctidn ;
	AND LOWER(cSrchVar) $ LOWER(descr) ;
	and BETWEEN(YEAR(date), .nYear1, .nYear2) ;
	ORDER BY date"
&cmdStr
ENDWITH

I would have thought these two statements are syntactically identical.

Any thoughts?

Thanks.

Bill V
Rochester, Michigan

 

[Olaf Doschke]

I actually can't tell you, but I find it better to define multiline texts via TEXT...ENDTEXT anyway.

I general the concatenation of lines works the way you use it:

cStr = "this ;
does ;
work"

This results in a sing line string: "this does work". I can't put my finger on the spot not working in your case, but I'd use TEXT..ENDTEXT anyway.

In this case you wouldn't need to use macro substitution at all anyway.

Bye, Olaf.

 

[Auguy]

I have no idea either, but I think I ran into somethibg like this problem with strings a few years ago. I changed my code for long strings to look like the following. More work but I haven't had any problems.

WITH thisform    
cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum" ;
    + " FROM checkreg, acctlist INTO dbf temptbl" ;
    + " WHERE checkreg.acctidn = acctlist.acctidn" ;
    + " AND LOWER(cSrchVar) $ LOWER(descr)" ;
    + " and BETWEEN(YEAR(date), .nYear1, .nYear2)" ;
    + " ORDER BY date"
&cmdStr
ENDWITH

Auguy
Sylvania/Toledo Ohio

 

[craigber]

Is that the actual code from the program? If so, why are you doing it that way? You can just do the SELECT statement without the macro expansion and it will run faster.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[NasibKalsi]

May be there is an invisible char in the string, happened to me few times. I will try commenting the non working code and then retype again after leaving a space line.

 

[mjcmkrsr]

Hi,

SQL interpretes your 1st statement as parameterized view - hence you have to preceed thisform.nYear... with ? like:

?thisform.nYear1 or depending on the version

(?thisform.nYear1)

cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
FROM checkreg, acctlist INTO dbf temptbl ;
WHERE checkreg.acctidn = acctlist.acctidn ;
AND LOWER(cSrchVar) $ LOWER(descr) ;
and BETWEEN(YEAR(date), (?thisform.nYear1), (?thisform.nYear2)) ;
ORDER BY date"
&cCmdStr

No idea though why the second statement works.

hth

Mark

 

[mjcmkrsr]

Hi Bill,
Forget my previous post.
It seems as if one or more of your variables were not defined.
The code below works fine in VFP6 and VFP9

***** Start of code

IF NOT FILE("TBLRANDOM.DBF")
CREATE TABLE TBLRANDOM (FLDRANDOM I)

FOR I = 1 TO 10000
INSERT INTO TBLRANDOM (FLDRANDOM) VALUES (Rand() * 10000)
ENDFOR

USE
ENDIF

SELECT 0
USE TBLRANDOM

LOCATE

oForm = CreateObject("Form1")
oForm.AddObject("grdRandom","Grid1")
oForm.AddObject("txtText1","Text1")
oForm.AddObject("txtText2","Text1")
oForm.txtText2.Left = oForm.txtText1.Left + oForm.txtText1.Width + 12

oForm.AddObject("cmdClick","cmdButton")
oForm.cmdClick.Left = oForm.txtText1.Left + oForm.txtText1.Width + 12 + oForm.txtText2.Width + 12

oForm.AddProperty("iValue1",0)
oForm.AddProperty("iValue2",0)

oForm.Show
Read Events

DEFINE CLASS Form1 As Form

AutoCenter = .T.
BackColor = RGB(255,255,0)
Name = "frmRandom"
Caption = "Random"
MinWidth = This.Width
MinHeight = This.Height

PROCEDURE RESIZE
WITH This.grdRandom
.Width = This.Width - 12 - 12
.Height = This.Height - 12 - 48
ENDWITH
ENDPROC

PROCEDURE DESTROY
Close All
Clear Events
ENDPROC

ENDDEFINE

DEFINE CLASS Grid1 AS GRID
Left = 12
Top = 48
Width = oForm.Width - 2*(This.Left)
Height = oForm.Height - 12 - This.Top
Visible = .T.
ColumnCount = -1
RecordSource = "tblRandom"
RecordSourceType = 2

ENDDEFINE

DEFINE CLASS Text1 AS TEXTBOX
Left = 12
Top = 12
Height = 24
InputMask = "999,999"
Visible = .T.
Value = 0

ENDDEFINE

DEFINE CLASS cmdButton AS Commandbutton
Top = 12
Height = 24
Caption = "Click"
Visible = .T.

PROCEDURE Click()
Local cmdCommand
ThisForm.iValue1 = Thisform.txtText1.Value
ThisForm.iValue2 = ThisForm.txtText2.Value

cmdCommand = "Select fldRandom from tblRandom ;
where Between(fldRandom, ?ThisForm.iValue1, ?Thisform.iValue2) ;
Order by fldRandom"

&cmdCommand

ENDPROC

ENDDEFINE

***** End of code

hth
Mark

 

[Olaf Doschke]

Just for the record, the error "Command contains unrecognized phrase/keyword" already occurs in the line:

cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
    FROM checkreg, acctlist INTO dbf temptbl ;
    WHERE checkreg.acctidn = acctlist.acctidn ;
    AND LOWER(cSrchVar) $ LOWER(descr) ;
    and BETWEEN(YEAR(date), thisform.nYear1, thisform.nYear2) ;
    ORDER BY date"

This is just setting cCmdstr to a string, no evaluation or macro substitution occurs here. That's what is so weird about this.

It's valid syntax to do line conntinuation within a string, although it's unusual. I still recommend TEXT...EDNTEXT and in this special case no macro substituion is needed at all.

Bye, Olaf.

 

[Koen Piller]

Hi,

It seems indeed something wrong, the error is there with the line "ORDER BY date"

The statement without the 'with thisform / endwith' works ok without 'the order' clause.

(The more correct writing as Auguy shows works in both cases

Regards,

Jockey(2)

 

[Olaf Doschke]

No, Jockey, when an error is detected in a miltiline statement, foxpro simply always points to the last line of the statement.

If you reformat to

cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
    FROM checkreg, acctlist INTO dbf temptbl ;
    WHERE checkreg.acctidn = acctlist.acctidn ;
    AND LOWER(cSrchVar) $ LOWER(descr) ;
    and BETWEEN(YEAR(date), thisform.nYear1, thisform.nYear2) ORDER BY date"

The debugger will error on "and BETWEEN..."

But that doesn't show you at all, what exact portion foxpro doesn't like of the string.

It's got nothing to do with thisform, eg you can also remove thisform from the string and foxpro still errors:

cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
    FROM checkreg, acctlist INTO dbf temptbl ;
    WHERE checkreg.acctidn = acctlist.acctidn ;
    AND LOWER(cSrchVar) $ LOWER(descr) ;
    and BETWEEN(YEAR(date), nYear1, nYear2) 
    ORDER BY date"

It's simply line continuation within strings is not always working, so the workaround is to stay away from that and use TEXT ... ENDTEXT.

For whatever reason the string continuation works within WITH ENDWITH is out of my knowledge, it has nothing to do with "thisform" at all.

Bye, Olaf.

 

[Olaf Doschke]

PS: forgot to add a ; in the example without thisform, still foxpro errors, if you add it.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

The cCmdStr is simply too long (>255C) you have to split it up either with TEXT ... ENDTEXT or like AUGUY suggested.

cCmdStr = "SELECT date, ctgname, SPACE(8) as specctg, descr, amt, shortnm, chknum ;
FROM checkreg, acctlist INTO dbf temptbl ;
WHERE checkreg.acctidn = acctlist.acctidn ;
AND LOWER(cSrchVar) $ LOWER(descr) ;
and BETWEEN(YEAR(date), thisform.nYear1, thisform.nYear2) ORDER BY date"

hth
Mark

 

[mjcmkrsr]

...

see VFP Help under Visual Foxpro System Capabilities - Miscellaneous - "Maximum length of a string literal"

hth
Mark

 

[mjcmkrsr]

...
of course it's "CAPACITIES"
mark

 

[Olaf Doschke]

Mark,

very well spotted. Even put into one line, removing some spaces and tabs it's still 260 chars.

Misleading error, though.

And you can also circumvent that by separating this into at least 2 literals, as it's just a limit for a literal, not for a variable holding a string.

Bye, Olaf.