Output to HTML is not reliable

[stanlyn]

Hi,

Using VFP9sp2 I am having an issue where outputting a dataset to a webpage via the "report form" is un-reliable. It either produces it correctly or it produces a zero length file. A couple of times I found the offending record and I can take a chunk of the data from one of its fields and paste it elsewhere and it works. I've checked the fields for non-ascii characters and there none.

If I output it to a printer, it works great every time.

Can anyone help? What should I do next? How can I find the issue?

Select 'curHtml'
GO top
Set Classlib To '.\libs\_REPORTLISTENER'
On Error Set Step On
ox = Createobject('htmlListener')
lcHtmlFolder = '\\SlaSQL\G-Drive$\Webs\StanLyn\private\'
ox.TargetFileName = (lcHtmlFolder) + 'ActionLog.htm'
ox.Quietmode = .F.

lcOldSetTime = Set("Date")
Set Date ymd

lcTimeTime = Alltrim(Ttoc(Datetime(),1))
lcFileName = (lcHtmlFolder) + 'ActionLog.htm'

Set Date (lcOldSetTime)
Set Console Off

Select 'curHtml'
*SET FILTER TO pk != 51
Go Top

Report Form 'TaskList' Object ox       * Not reliable
*Report Form 'TaskList' preview         * Works every time

If SizeArray(Adir(SizeArray, ox.TargetFileName) + 1) = 0
	Messagebox("The file size for: 'ActionLog.htm' is zero...", 0, "File Size Error", 30000)
Endif

Thanks,
Stanley

 

[stanlyn]

Hi,

Anyone have a clue on how to handle/troubleshoot this?

Thanks, Stanley

 

[Tore Bleken]

Can you provide sample data which makes the generation of the html file fail? A simple Create Cursor curhtml ... followed by a few Insert Into curhtml... lines will do.

 

[stanlyn]

Hi tbleken,

This code creates the cursor

Select ;
	task.pk, task.TASK_NAME, task.INITIATOR, task.time_added, task.PRIORITY, task.Status, task.CUSTOMER, task.ISSUE, ;
	task.NEEDED_BY, task.DEADLINE, task.time_RESOLVED, task.NOTES, ActionDetail.pk As ad_pk, ActionDetail.task_pk, ;
	ActionDetail.action_time, ActionDetail.ACTION_DESC, ActionDetail.TECH, ActionDetail.Qty, ;
	ActionDetail.NOTES As ad_notes, ActionDetail.action_time As ReportSpec1 ;
	From task ;
	left Join 'ActionDetail' On task.pk = ActionDetail.task_pk ;
	WHERE !Deleted('task') ;
	INTO Cursor 'curHtml' ;
	readwrite

When sending to a htm file it randomly prints a zero length file. If the same data is sent to a printer or preview, it prints everything as expected.

Thanks, Stanley

 

[stanlyn]

Hi,

More testing...

Originally, the destination folder was a unc on a lan. I changed it to a local drive "D:\tmp". Did not help...

Thanks, Stanley

 

[Koen Piller]

Hi,

Please try with "No Filter" parameter and delete the ' around the destination cursor name

Select myFields from Task into cursor curHTML nofilter readwrite

Are you / have you instantiated FoxyPreviewer.app in this project?

Regards,

Jockey2

 

[stanlyn]

Hi Jockey2,

Are you / have you instantiated FoxyPreviewer.app in this project?

No, as I wanted to keep things simple as this particular process does not have any interactions with the user.

Please try with "No Filter" parameter and delete the ' around the destination cursor name

Just wondering what your logic for this is. If there were an issue with that step, then wouldn't printing to paper or preview have the same issue that I'm having with to html? The issue appears to be data and not program. I will/am try as you suggest and report back...

Thanks,
Stanley

 

[stanlyn]

Hi Jockey2,

When you say

try with "No Filter" parameter

I'm not using any unless you are talking about the where clause. Can you be more specific.

Thanks,
Stanley

 

[stanlyn]

Jockey2,

I just tried removing the single quotes from the cursor name and I also removed the where clause. Issue still persists. The procedure runs perfectly complete with the progress bar all the way through completion. Its just that "to printer" and preview works perfectly everytime with output as expected while sending to htm many times produces a zero length .htm file. Currently the table has about 150 records.

Thanks,
Stanley

 

[Olaf Doschke]

You cannot call DELETED() within a query. No matter if you work with alias names or not. This is not working out.

SQL engine always opens DBFs in new workareas, you can check that by a set step on during a query:

USE browser.dbf
SELECT * FROM Browser Where ExecScript("set step on"+CHR(13)+"return .T.") NOFILTER

You'll see, that while browser stays open, new workareas B,C,D are populated and used during the query:

That's the situation after a few iterations, notice the record pointer in the Browser window is still at top, the query scans through workarea "B". DELETED("Browser") in this case would always and only reflect the deleted status of the first record of BROWSER.DBF

That's why DELETED() always returns either .T. or .F., depending on the state of the current record of "task" in your case, so you either get all or no rows. This has nothing to do with HTML listeners. The nature of sql queries makes it impossible to make use of DELETED with only one exception: DELETED() without any alias works, if you do a single table query. You do a join, here, though, which makes use of DELETED impossible. You now know the sql engine does not iterate the table you already have open nor does itself reference the workareas with the alias names you give it.

SET DELETED ON is the setting you do to avoid reading out deleted records, It's the only way to work with the deleted flag in sql in general. If you want to use deleted status of tasks as a boolean field, then you lost.

Bye, Olaf.

Side note: Workarea C has the same structure as Browser and stays empty, maybe something happens to it later, I don't know. Workarea D is where the result is built, in this situation it has the previous two records of browser.dbf, which already were tested against the where condition. Of course, your mileage varies if even more workareas are already used, but the nature of SQL is not touching a current workarea, but reopening the DBF file. Otherwise bufferes and filters would influence the query result and leaving a record could even trigger saving data, if a workarea only is row buffered. All that influences can only be avoided by the sql engine, if it only uses it's own workareas, and that's what it does. As a consequence you can't predict the workarea names, even if you make use of AS alias clauses, the sql engine has it's own mind for these sql alias names, not reflecting in the workarea names. Again that could cause trouble with already used alias names, especially of the tables you really query in. All in all what happens is different from what you think happens.

 

[stanlyn]

Hi Olaf,

OK, if sql is thinking that way, I'll do it the vfp way...

create cursor 'curHtml' (field........... readwrite,
iterate and copy the task table field values into the curHtml table,
iterate the ActionDetail table and populate the related values

so I'll have the same data in curHtml as I currently have from running the sql statement mentioned above which has the correct data.

How would this cause my problem with creating zero size .htm files and good print and previews. I do not see the connection here. I see my issue as totally different from the sql query results. As stated earlier, my sql statement is returning what is expected. To paper and preview there IS data, and to .htm there is SOMETIMES data.

Is there any reason to believe the sql statement I used above is causing this? If so, then why would to paper or preview work?

Thanks,
Stanley

 

[Tore Bleken]

I repeat: Please provide some sample data so that we can test ourselves. I described how you can provide the data in my other reply.

Added: I totally agree with Olaf. If you still have problems after changing your code according to his recommendations, here's a link describing how you can easily provide sample data:

https://www.foxite.com/faq/default.aspx?id=50

 

[Olaf Doschke]

Is it really that hard to follow what I said? The only thing you need to do is SET DELETED ON and remove your DELETED() condition:

SET DELETED ON

Select ;
	task.pk, task.TASK_NAME, task.INITIATOR, task.time_added, task.PRIORITY, task.Status, task.CUSTOMER, task.ISSUE, ;
	task.NEEDED_BY, task.DEADLINE, task.time_RESOLVED, task.NOTES, ActionDetail.pk As ad_pk, ActionDetail.task_pk, ;
	ActionDetail.action_time, ActionDetail.ACTION_DESC, ActionDetail.TECH, ActionDetail.Qty, ;
	ActionDetail.NOTES As ad_notes, ActionDetail.action_time As ReportSpec1 ;
	From task ;
	left Join ActionDetail On task.pk = ActionDetail.task_pk ;
	INTO Cursor curHtml Readwrite

All the other stuff is just showing why DELETED doesn't work in your query.

SET DELETED ON should be your default setting you rarely turn OFF to locate deleted data, which rarely is necessary. Mostly to avoid double primary or secondary keys.

Bye, Olaf.

 

[stanlyn]

Hi,

Here is what I just finished running which delivers the same problem... (no change, same issue)

SET DELETED ON

Select ;
	task.pk, task.TASK_NAME, task.INITIATOR, task.time_added, task.PRIORITY, task.Status, task.CUSTOMER, task.ISSUE, ;
	task.NEEDED_BY, task.DEADLINE, task.time_RESOLVED, task.NOTES, ActionDetail.pk As ad_pk, ActionDetail.task_pk, ;
	ActionDetail.action_time, ActionDetail.ACTION_DESC, ActionDetail.TECH, ActionDetail.Qty, ;
	ActionDetail.NOTES As ad_notes, ActionDetail.action_time As ReportSpec1 ;
	From task ;
	left Join 'ActionDetail' On task.pk = ActionDetail.task_pk ;
	INTO Cursor 'curHtml' ;
	readwrite

*	WHERE !Deleted('task') ;

And as I've said all along, this way or the other way, my data results are the same... And yes, I do have some deleted records in the table and they are not showing which is what is wanted.

So, with that out of the way, any idea on what is causing our original issue, and or how to troubleshoot? How do we get to see what is happening when the progress bar reaches 100 percent? I know of no way stepping thru code while inside the report generator... Any ideas here?

tbleken, you ask for sample data and I don't know how to give you that, because of its randomness. It works sometimes and sometimes it doesn't.

Thanks,
Stanley

 

[Olaf Doschke]

Try it with a different targetfilename per run, SYS(2015) will help here, eg ox.TargetFileName = (lcHtmlFolder) + SYS(2015)+ '_ActionLog.htm'. It hasn't to be the final solution, but it will rule out you don't have file access problems to a file name already used previously.

Bye, Olaf.

 

[Mike Lewis]

tbleken, you ask for sample data and I don't know how to give you that, because of its randomness. It works sometimes and sometimes it doesn't

I think what he's asking for is some data that he can use to reproduce the problem - in other words, whatever data resulted in it not working.

An easy way to do that would be to temporarily change your SELECT statement's INTO CURSOR to INTO TABLE, and then post the contents of the table - not the whole thing, just the first few rows. (Of course, you will want to edit any confidential information such as customers' names.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

If it's "random", it's probably the data. Please provide data for an unsuccessful run, maybe we can find a pattern so that you can "clean" the data.

 

[stanlyn]

OK, I've tried alot without a fix...

Olaf, I did the "+ SYS(2015) +" into the filepath,. It creates a zero length file such as "_4YP10ECX7ActionLog.htm".
I added user "everyone" to the folder with full privs as well.
I moved the whole project to a XP machine from Win10, same issue,
I commented out all the sql stuff and built the cursor the old fashion way with create cursor, with append and replaces... Same problem.

tbleken and mike, concerning sample data...

temporarily change your SELECT statement's INTO CURSOR to INTO TABLE, and then post the contents

That won't help as the data is the same (successful or unsuccessful runs) and is all there in all conditions (to paper, preview and to .htm.) Problem is "to .htm" creates a zero length htm file, "sometimes". Its like something is killing the .htm creation after the report gets created and ready for its final destination. In this case "create the htm".

Any other ideas,
Stanley

 

[stanlyn]

Hi again,

To prove the data was the same for both cases, I ran these two lines and the results were first line produced a zero length .htm file, and the second line produced a perfect preview with all the data on it.

Report Form 'TaskList' Object ox

Report Form 'TaskList' TO PRINTER preview

So how do we debug the report generating .htm files?

Thanks, Stanley

 

[Tore Bleken]

When the html file is zero length, copy the cursor to a "real" table. Then run the program I referred to which will generate a prg to regenerate the data. If you can post this prg, we can also reproduce your problem. Hopefully. Without this data we are hardly able to help you.