SQL query with macros pops up the browse windows with query results! 1

[Rajesh Karunakaran]

Hi Team!

I have a SQL query, which is dynamically created with variables I set for relation expression and the usual filtering expressions which would go into the WHERE clause. But, now when that SQL executes I get a browse window showing the results. I know if you issue SQL without a destination, i.e. INTO CURSOR, INTO ARRAY etc, it will show the query results in a browse. But, in this case, I have a destination. Even with NOCONSOLE, it shows the browse window. What could be the reason?

SELECT &lcField as fld FROM &lcAliases WHERE &lcWhere INTO ARRAY laDummy NOCONSOLE

Thanks in advance
Rajesh

 

[Tom Borgmann]

Hi Rajesh,

it might help to extract the macro contents because each of the used variables can lead to some unexpected reactions.
You could do this by concatenating your SQL within TEXT...ENDTEXT like this:

TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 2+4+8
SELECT <<lcField>> as fld FROM <<lcAliases>> WHERE <<lcWhere>> INTO ARRAY laDummy NOCONSOLE
ENDTEXT
&lcSQL

That way you could assign lcSQL to _CLIPTEXT and place the real SQL in a posting.

-Tom

https://www.blogger.com/profile/08903165976787522012

 

[Rajesh Karunakaran]

Hi Tom Borgmann,

Thank you for your reply.
Yes, in fact, I had tested with placing the complete SQL in a variable lcCmd and doing &lcCmd. But that also brings up the browse.
I don't think the SQL is invalid in some way. I am getting the result as expected. The only problem is it brings up a browse window for the query result. This is exactly as if I have not specified any destination for the result.

Really confused and not able to catch it!

I will try your suggestion as well, for sure!

Rajesh

 

[Mike Lewis]

This is strange. Rajesh, are you sure that it is the SQL statement that is opening the Browse window, not something else in your code? I know that's unlikely, but - as you rightly say - adding the INTO clause should prevent the Browse from opening (but I don't think NOCONSOLE will make any difference).

A couple of things to check:

- After running the query, is there a cursor open named Query? If so, does it contain the expected results of the SQL? By default, SELECT sends its results to that cursor when you don't specify a destination.

- Does the array laDummy actually exist, and does it contain the expected results?

Neither of those points explain the behaviour you are seeing, but they might help us figure what is going on here.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Dear Tom Borgmann,
When I followed your way, there is no problem!
So, it's something in the SQL statement I am generating. There is no syntax problem anyway and the SQL is valid as it gives me the result.

Mike,
Yes, it's strange. I believe, I am finding something...

Dear all, I will get back here for sure... Let me check.

Thank you all for your time.
Rajesh

 

[Rajesh Karunakaran]

Hi Team!

I found the problem and it's solved!

lcCmd = 'SELECT ' + lcUseField + ' as usefield FROM ' + lcAliases + ;
	' WHERE ' + lcWhere + ;
	' INTO ARRAY laDummy NOCONSOLE'

In the above line, the content of lcWhere is coming from a Memo field of a table. Somehow, at string end, the end of line characters 'CHR(13) & CHR(10)' where present. I know in Memo fields this can happen (say, eg, if you save from an editBox control in a form). In this case, I think, when the content was copy pasted from a specification document, these characters also might have got stored.
So, even if I have written 'INTO ARRAY laDummy NOCONSOLE' at the end, when executed, VFP ignores that due to presence of EOL characters at the end of 'lcWhere'. So, then it becomes an SQL Query without a destination specification and hence I get a browse window.

So, I strip those through:

lcCmd = CHRTRAN(lcCmd, CHR(13), '')
lcCmd = CHRTRAN(lcCmd, CHR(10), '')

Then, there is no problem.

However, if I use the method by Tom Borgmann, even without doing anything, I don't have the problem.
Must be because TEXT...ENDTEXT native Only Text behavior.

Thank you all very much,
Rajesh

 

[Mike Lewis]

Well done in locating the error, Rajesh. In general, using TEXT / ENDTEXT to construct SQL statements is preferable to the way you were doing it originally. I suspect that most VFP folk use that method.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Thank you very much for the advice Mike.
I will follow that.

Thanks to you too dear Tom Borgmann

Rajesh

 

[Tom Borgmann]

Hi Rajesh,

indeed, well done with locating the reason for the misbehaviour.

and yes, it comes by the 2+4+8 values at the end of the first TEXT TO line.
1 eliminates spaces before each line
2 eliminates tabs
4 eliminates blank lines
8 eliminates line feeds
therefore you don't have the need for any CHRTRAN() actions.

If usually use 2+4+8 because I can structure my SQLs with TABS, LINE FEEDS and empty lines and TEXT TO removes them at runtime.
I once started composing my SQLs with endless concatenations but when TEXT ... ENDTEXT came up (was it VFP7?) I switched and never looked back

-Tom

https://www.blogger.com/profile/08903165976787522012