Can I use TEXT... ENDTEXT inside a DO CASE statement? 1

[TheLazyPig]

Can I use TEXT... ENDTEXT inside a DO CASE statement?

The data I'm retrieving is from an oracle db.

I tried IF statement it won't work so I used CASE but still have error.

The error points to COPY TO line..

IF SQLEXEC(gnHandle, lcSQL , 'MyCursor') > 0
   COPY TO (lcDir+"HP_Summary_per_Account\lcMonth+"_HP_Extraction_perAccount") TYPE XLS
   MESSAGEBOX("File ["+lcMonth+"_HP_Extraction_perAccount.xls] Created!")
ELSE

Before I use the DO CASE statement it works.

Below is my query for OK button in DO CASE.
grpFYRenewal is the name of Radio Button.

DO CASE
	CASE thisform.grpFYRenewal.value = 1
		lcSql = ""
		TEXT TO lcSQL TEXTMERGE NOSHOW
	
		SELECT CASE
	        	WHEN poltype=30001 THEN 'Individual'
	        	WHEN poltype=30003 THEN gen.refdesc
	       	   END AS ACCNT_GRP,plangrp,COUNT(plandesc) AS PLAN_COUNT
		      ,SUM(basicprem) AS PREMIUM
		  FROM xag_prod_summary prd
		  LEFT JOIN inb_moamst moa ON moa.moa_seqno = prd.moaseqno
		  LEFT JOIN cxx_geninfo_ref gen ON gen.refseqno = moa.market_segment
		 WHERE TO_CHAR(prd.referencedate,'YYYYMM') = ?dDate
			AND prd.plangrp IN ('HPSP','HPSD','FHIPP','FHIPD','HRPD','FLHP','HPNP','HPND','HPWP','HPWD','HDRP','HDRD','HDSP','HRSD','HRPP','HRPD','HPSP','HPSD','HRFD')  
		    AND policyyear = 1
		 GROUP BY gen.refdesc,prd.plangrp,prd.poltype
		 ORDER BY 1
		 
		 ENDTEXT
	
	CASE thisform.grpFYRenewal.value  = 2
		lcSql = ""
		TEXT TO lcSQL TEXTMERGE NOSHOW
	
		SELECT CASE
		      	WHEN poltype=30001 THEN 'Individual'
		       	WHEN poltype=30003 THEN gen.refdesc
		   	   END AS ACCNT_GRP,plangrp,COUNT(plandesc) AS PLAN_COUNT
		      ,SUM(basicprem) AS PREMIUM
		  FROM xag_prod_summary prd
		  LEFT JOIN inb_moamst moa ON moa.moa_seqno = prd.moaseqno
		  LEFT JOIN cxx_geninfo_ref gen ON gen.refseqno = moa.market_segment
		 WHERE TO_CHAR(prd.referencedate,'YYYYMM') = ?dDate
				AND prd.plangrp IN ('HPSP','HPSD','FHIPP','FHIPD','HRPD','FLHP','HPNP','HPND','HPWP','HPWD','HDRP','HDRD','HDSP','HRSD','HRPP','HRPD','HPSP','HPSD','HRFD')  
			    AND policyyear > 1
		 GROUP BY gen.refdesc,prd.plangrp,prd.poltype
		 ORDER BY 1
		 
		 ENDTEXT
		 
ENDCASE

Thank you!

 

[Olaf Doschke]

There's nothing wrong as long as the TEXT..ENDTEXT is within one case, and VFP won't get confused with a CASE within the TEXT...ENDTEXT Block. It's just a string/text to it, the only keyword it reacts to is ENDTEXT.

Your error in the COPY TO has nothing to do with any previous code, it's simply missing something. You have 3 quotation marks. They should come in pairs only 2,4,6..., never 3.

COPY TO (lcDir+[COLOR=#EF2929][highlight #FCE94F]"[/highlight][/color]HP_Summary_per_Account\lcMonth+[COLOR=#EF2929][highlight #FCE94F]"[/highlight][/color]_HP_Extraction_perAccount[COLOR=#EF2929][highlight #FCE94F]"[/highlight][/color]) TYPE XLS

Next time post the error message, perhaps? Because I'm sure it says something about unrecognized phrase. And that error is about the COPY TO, not about the SQL, so take a close look.

I'm sure you wanted to write

COPY TO (lcDir+"HP_Summary_per_Account\"+lcMonth+"_HP_Extraction_perAccount") TYPE XLS

What helps extremely with such string delimiting errors is setting the syntax highlighting color for strings to red:
Menu Tools->options
Editor tab, Syntax color settings: Area: pick Strings. pick foreground red. Set as Default.

And it also helps, when you start using the debugger, single-step through code, you'll see the code gets along fine with your setting of lcSQL and executing it, you'll see the result in the workarea alias 'MyCursor' and then it fails. So how should that be related to the TEXT...ENDTEXT blocks at all? Along what logic are you thinking would this first work and then break, how would COPY TO induce an error back to code it already executed?

And how do I know? Because you only get to COPY TO, when SQLEXEC(...)>0, and when SQLEXEC returns a positive number, that's the sign of success, it proves it's succeeding to do the query. So even if you don't think it's worth learning to debug code running the debugger and a few keys/toolbars in it, you know you are at the stage after the query already ran and produced a result.

Besides, you have been advised to check for <0 (add an ELSE branch here) to be able to find out about SQL errors with AERROR, just look back into older threads. It's really important, that you cover that outcome, too.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Tom Borgmann]

The reason for that is the missing TEXT...ENDTEXT parameter called PRETEXT.
With this param and its additive values you can get rid of unwanted empty lines, carriage returns and tabs or blanks at the beginning of each row.

In your case you want to create a sql command that has to be a 1-liner. What you create is a multiline select and that won't be accepted.

So try to change your "TEXT TO" line to something like this:

TEXT TO lcSql TEXTMERGE NOSHOW PRETEXT 2+4+8

2 - eliminates tabs at the beginning of each line
4 - eliminates carriage returns and blank lines
8 - eliminates line feed

That way you can write readable sql and create a correct sql statemente for your db.

AND: you can use TEXT...ENDTEXT within IF...ENDIF, DO CASE...END CASE without problem. As said, the reason is the missing PRETEXT

HTH

ADDED: And the missing " as Olaf already found out

-Tom

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

 

[Olaf Doschke]

The line erroring is just the COPY TO, the query already succeeded.
So PRETEXT isn't necessary, the query also works with the obsolete whitespace, it doesn't break anything.

As the TEXT..ENDTEXT here does no actual textmerge, though the option is used, all it is for is enabling to put the long query inside the code without endless lines of lcSQL=lcSQL+"next line of the query". You can always postporcess the result of TEXT..ENDTEXT and replace CHR(13)+CHR(10) with a space to let the SQL statement look like one line to the remote database. but I don't know a database that would take offense in too much whitespace or line feeds in a query. And when it's part of string literals in the query you also better not remove it. So unless there is a special difference in handling whitespace in a database, you can go with or without the pretext.

It's good to know this option so indentation of the source code can be counteracted and you get the lcSQL value you actually meant, without the indentation.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Tom Borgmann]

Maybe I'm to much influenced by Informix. And even it may have changed even there. But when we started Informix ODBC back in 1999 you got lots of errors with multiline statemente and CR/LF.
In the beginning we did lots of ""+""+""+""+"" concatenations and when TEXT..ENDTEXT was introduced we automatically placed the PRETEXT 2+4+8 and never got any problems with the odbc driver.

Perhaps its time to do a little testing with the current driver, though. But as it comes from IBM, I'd be really surprised it they changed that behaviour ))

-Tom

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

 

[Olaf Doschke]

But that's just about CRLF, isn't it?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Tom Borgmann]

To be honest, I can't remember anymore. The CR/LFs definetly where ODBC based. The TABS and blank lines, I don't know. way toooooo long ago

IIRC at that time there were several drivers available (some by IBM and some written by others, INTERSOLVE or something like that) and each driver had its own way of handling the statements. We never new which driver would be used by our clients.

But its also possible that I defined the '2' and '8' for our sql logging at that time. As I said...time for a little testing!!

Nevertheless, using 2+4+8 is a secure practice and it does no harm sending such strings via ODBC.

-Tom

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

 

[Mike Lewis]

Next time post the error message, perhaps?

That's right. And with questions like this, it also helps to know which back-end database you are using.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TheLazyPig]

Thank you for the response.

When I corrected the quotation marks I still got error so I re-check my query from COPY TO line. I found that the path I use for testing does not have a folder name called HP_Summary_per_Account that's why I still get error. I fixed it now and it's working Hehe. Thank you Thank you.

 

[TheLazyPig]

Okay sir Mike and Olaf, I'll remember that. I just re-read my thread it does not point to the TEXT.. ENDTEXT that I asked about, still I only tried the DO CASE.. TEXT.. ENDTEXT today and I didn't know that it'll work. That's why when I got error I thought it's from the TEXT... ENDTEXT and assume that my COPY TO is correct.

 

[Mike Lewis]

LazyPig,

Glad to hear you have got it working.

There's another tip to keep in mind. Most back-end databases have some sort of command line or code window that lets you enter code and see it executed straight away. If you have a complex SELECT statement in your VFP code, you can copy if from your TEXT / ENDTEXT and paste it into the code window, then see the result - or the error message. You can then use the back-end's debugging features to fix it. (It won't work if you are using TEXTMERGE or other substitutions, but that is not the case here).

This wouldn't have helped in this case because the error was to do with the path on the local system, but it is something you might like to keep in mind for the future.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I pointed out that we previously recommended AERROR for the case SQLEXEC is <=0. But good that you got it working.
It couldn't be the CASE within TEXT...ENDTEXT as said, the query already ran, ran successfully.

And COPY TO can't only fail on not having a source workarea to copy, also when the path to copy to doesn't exist. So just remember, the error message is telling something more (especially once the syntax is correct about the string composition). The behavior you would see from COPY TO not having a source workarea is a file open dialog popping up. And if the reason for empty workarea is the query not working, you don't get to the COPY TO line, SQLEXEC then isn't>0, you wouldn't even get to the COPY TO line.

You had actually two or three indicators, that it is in fact the COPY TO line failing. And only that.
Again, start learning to use the debugger, it helps to see a lot more things, for example, the query result when you open up the data session windows in the main Foxpro Windows parallel to the debugger. Set a breakpoint or write SUSPEND in the line from which you'd like to start single-step mode. SET STEP ON Then opens up the debugger, if you don't already have it open on a second display. The debugger Locals window will list all variables and their values, you have the trace window showing your code. You can inspect so many things at once, but you don't need to understand everything at once to make use of it. Most important is the debugger toolbar. With F8 to execute the next single step. And, for example, to not do everything in single steps, when you step into a function or method you know works, you can step out (SHIFT+F7) to execute until code returns to the line which called it (the step "into" it). And finally, "run to cursor" also is extremely helpful to go through things like loops and still just up to a line you next want to pause on and inspect the state. You can use the command window to execute something in the context of the current function, for example execute a mended line of code, then skip it in the single stepping through code by right click and "set next statement".

These few hints are surely not a full blown debugger tutorial, but you have the help reference at hand, and these are the first things you need to know about the debugger. You'll discover more the more you use it.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name