macro substitution (> 255 characters) trouble 1

[Gameboy9]

Well I've got this massive select statement... well not beating the 8,192 character limit I wager, but still...

I'm having a problem with the lc_where macro substitution. This SELECT SQL statement works fine until the lc_where variable exceeds 255 characters, then I get an "unrecognizable phrase/keyword" error. This variable adds no more than fifty characters at a time.

SELECT DISTINCT transact.a, transact.b, transact.c, ;
transact.d, transact.e, transact.f, ;
LEFT(transact.g, 20), distrib.h as distamt, ;
distrib.i, distrib.j, distrib.k, distrib.l, ;
distrib.m, Employee.n, Employee.o ;
FROM vice!transact INNER JOIN vice!employee ON employee.p = transact.q ;
INNER JOIN vice!distrib ON transact.a = distrib.r ;
&lcSWCards ;
&lcSWOffices ;
&lc_where ; && trouble variable here
&lc_sort ;
INTO CURSOR historywork

Is there a way around this problem? Thanks again. (Field names one letter for security purposes)

 

[StewartUK]

GameBoy9,

Instead of a being a length problem, it may be that the macro in lc_where has not been completed properly - missing bracket, missing comma, missing space.

Double check the variable lc_where in the Debugger and/or post the text of the variable here.

Hope that helps,

Stewart

 

[Mike Lewis]

Gameboy,

I'm inclined to agree with Stewart. In genral, macro substitution is limited only to the length of a VFP statement, not to 255 characters. Also, I think I have done the same thing that you are trying to do, and I don't recall having the same problem.

Try this. Run the program, but instead of executing the SELECT statement, copy the contents of the lcWhere to the clipboard (using _CLIPTEXT). Then open a code editing window, type in the rest of the SELECT command (the fixed part), and paste in the Where clause. That will give you the same Select statement, without the macro. Try executing that statement and see if you still get an error.

If you do, there is clearly some sort of syntax error or something similar in the lcWhere.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[KeithTrangmar]

Another approach would be to build the whole SELECT statement as one big string (which you could then examine easily) and then use macros substitution to run it. Something like this:

TEXT TO strSQL
SELECT DISTINCT transact.a, transact.b, transact.c, ;
transact.d, transact.e, transact.f, ;
LEFT(transact.g, 20), distrib.h as distamt, ;
distrib.i, distrib.j, distrib.k, distrib.l, ;
distrib.m, Employee.n, Employee.o ;
FROM vice!transact INNER JOIN vice!employee ON employee.p = transact.q ;
INNER JOIN vice!distrib ON transact.a = distrib.r ;
<<lcSWCards>> ;
<<lcSWOffices>> ;
<<lc_where>> ;
<<lc_sort>> ;
INTO CURSOR historywork
ENDTEXT
&strSQL




Keith Trangmar
Harlend Computer Services
Maidstone, Kent. UK.

 

[MirekZvolsky]

I think, you are true with 254 characters limitation for &..
Use: EXECSCRIPT( m.tcSQLCommand )

 

[craigsboyd]

If you were hitting some kind of limit it would be the following error:

Line is too long (Error 18)

The maximum length for a command line (2048 bytes) has been exceeded.

Macro substitution might have caused the line to expand beyond the 2048 byte limit.

...I don't think this is the cause of this. Now if I try and place a string literal into a variable that is more than 255 characters then I get the error as you describe. Such as:

X = &quot;adsghaghla...256 characters in all...jghlsadgsdk&quot;

However I can in fact place more than 255 characters in to variable X by breaking it up:

y = &quot;...255 characters or less...&quot;
z = &quot;...255 characters or less...&quot;

x = y + Z

...and if X is an SQL statement it will run using macro substitution so there is not a 255 character limit on the commands that a variable can hold and execute via macro substitution.

So, I agree with the others that your problem with that where clause macro substitution has something to do with what you are putting into the variable...the command does indeed hold an unrecognizable phrase/keyword. Just to be sure I created a where clause variable that was over 400 characters in length and VFP7 had no problems running it.

Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

[/sup]

 

[EzLogic]

If the data is not too large, do 2 sql statements, where you split the Where in both statment, and then combine the results in one final cursor...

I did that couple of times.. though it might be a little slower.. but, if you aren't dealing with large data, then you won't notice the speed difference, yet you get the solution resolved.

Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[baltman]

I agree with Keith. When your error pops up, SUSPEND, do a STRTOFILE(lc_where,'lc_where.txt') and MODIFY COMMAND lc_where.txt to see what you're really asking for.

Brian

 

[StewartUK]

Boy, that's a lot of ideas! Hope one of them works for you Gameboy

Stewart

 

[Gameboy9]

Hmmmm... I think I fixed it. It's a WHERE blah IN () clause, and I think inside the parentheses I had numbers that should have had quotes on both sides, but didn't. (I had ('16C, 16C, 16C, 16C... 16C'), I should have had (&quot;16C&quot;,&quot;16C&quot;,&quot;16C&quot;,&quot;16C&quot;...&quot;16C&quot)

What's interesting though, is that the first one took until 255 characters reached... hmmmmmm...

Thanks for the CLIPTEXT idea, Mike.

 

[mcoupal]

I have a very large =sqlconnectstring command to run, but haven't figured out how to do this. I put the whole command into a textfile and tried doing =execscript(fileto('a.txt')) with no luck.

I've also tried populating it with variables like:

=execscript(lcText1+lcText2) -but it still doesn't like this. Any suggestions would be welcome. I have spent all day trying to get VFP8 to connect to my as400. It works fine in development mode, but from the compiled .EXE it fails to populate the cursor (i was using sqlexec()). I have also tried using a Connection in the dbc with the same results. I would love to hear suggestions as I'm ready to toss the pc out the window.

 

[Mike Gagnon]

mcoupal

Try lcText3 =lcText1+lcText2

=execscript(lcText3)

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.