PDF reports in SQL server (client server) environment

Topics: Developer Forum
Developer
Aug 14, 2013 at 10:10 PM
Hi Claudfox,

This is just beautiful. First I have to comment on your excellent work in making this system so stable and easy to use.

I have almost finished with my PDF module and would like to share my experience for anyone who may be thinking of going ‘mobile’ with this product.

Just a couple of points
  1. My requirement was to create PDF reports from multiple related tables. Since C/S does not have a DBC file, I had to create a empty DBC file to be shared by all web users. When the reports are created, I load the required dbf tables into this DBC and immediately unload and destroy them, once the PDF file is created.
I could not re-set pdfrun.print2pdf data session (when running it from IIS) therefore I was required to open and then relate multiple tables from within the oPDF object itself. This I did by changing the following line in MakePS()

&lcRecordSelect

To ..

EXECSCRIPT(lcRecordSelect)
This allowed me to inject multiple line commands into this exe.
  1. I also had to send a couple of variables into this report. I could not figure out how best to do that so I sent them as ‘public’ into the lcRecordSelect. I haven’t tried creating them as properties in oPDF object and poping them as private from within the MakePS()
Am I on the right track? Is there a better way to achieve this? I've never created public variables before.


An observation: However, it may be local to my system ....

oResponse.Redirect(lcNewPath) && redirect browser to created file
oPDF = .NULL.
release oPDF

needs to be...

oPDF = .NULL.
release oPDF
oResponse.Redirect(lcNewPath) && redirect browser to created file

Othewise, oPDF keeps its hold on all files and eating memory.
Coordinator
Aug 15, 2013 at 12:16 AM
Thank you - excellent feedback for PDF reports!
Developer
Aug 19, 2013 at 5:25 PM
An update to my previous post...

Having a common DBC file did not go well. It bloated to more than 100 MBs on first day itself..due to constant adding and deleting dbf's and it's not possible to pack in IIS (unless IIS is stopped) since it requires exclusive access.

Now, in addition to other files, I also create dbc, for each request, with sys(2015) name. While it is simple to alias the sys(2015) 'dbf' table with 'correct table' name... , it is not possible to alias the DBC. Therefore, this required little tweaking of the 'frx' file directly, however, since all the files (dbc, dbf,frx) have been created with unique names and are exclusive to each request, it has not created any problems in editing/deleting any of these files in multiuser environment.
Coordinator
Aug 24, 2013 at 7:34 AM
Hey Titu1,

What do you do with your PDF after the report is created? What I'm getting to is how do you clean up your PDF folder when the user downloaded/print their report on their local machine.
Developer
Aug 24, 2013 at 2:52 PM
Yes, that is marked as a potential security breach!, I am still trying to work it out since we cannot delete PDF files along with the rest of the temporary work files created in this PDF generator page. Currently I use a slightly modified version of DeleteFiles() on all pages (i.e in header) ,which deletes pdfs older than 4 minutes.

Eventually and if we don't find a better solution, I guess, we'll give permission to this temp folder to only the IIS and prevent all other network users from accessing them, except the super user. I really haven't tested it out. If you have a better way, it'll be great to hear it.

BTW. Security certificate or password protected approach is not an option in my case.
Developer
Aug 24, 2013 at 3:33 PM
Oh.. regading what potential security breach flag .

All my pages require authentication (and do cleanup) but within 4 minute period they can still be potentially hacked by our experienced web user. Like I said I haven't worked it out except to throw it back to network people to prevent any dir indexing or other such stuff.
Coordinator
Aug 26, 2013 at 8:53 PM
Titu1 wrote:
Yes, that is marked as a potential security breach!, I am still trying to work it out since we cannot delete PDF files along with the rest of the temporary work files created in this PDF generator page. Currently I use a slightly modified version of DeleteFiles() on all pages (i.e in header) ,which deletes pdfs older than 4 minutes.

Eventually and if we don't find a better solution, I guess, we'll give permission to this temp folder to only the IIS and prevent all other network users from accessing them, except the super user. I really haven't tested it out. If you have a better way, it'll be great to hear it.

BTW. Security certificate or password protected approach is not an option in my case.
I'm actually thinking of forcing the PDF to be saved to the local workstation (forcing the Save As dialog) instead of keeping it in a folder in the server. Once the file is downloaded, then the temporary PDF in our server can now be deleted. I just don't know yet if that can be done.
Developer
Aug 27, 2013 at 10:42 PM
Yes. That would be much better solution. Maybe append the pdf to a normal and simple response page and handle it at client side using timer, I guess.

I haven't started on client side of this system to cannot really comment.
Developer
Oct 11, 2013 at 3:15 PM
Hi apaustria

Did you get time to work on this?

I tried in JQuery Mobile/ajax but did not get any success. in fact, the best I could do was to open the PDF in a new browser tab/window by switching off Ajax calls. i.e. data-ajax="false" target="_blank", which is not what I want to do.
Coordinator
Oct 11, 2013 at 7:49 PM
Unfortunately, I have to create a service in the server to clean up the PDF folders where I store it temporarily. Part of the pdf file name contains a timestamp where this service can process (delete) files older than an hour. This is my solution for now until I can think of something integrated in in the actual website itself.
I’m also using your approach in conjunction with the service created where I open the PDF in a new browser/tab using target=”_blank”.
Developer
Oct 11, 2013 at 9:27 PM
Ok if that is the best we can do, .. although the new tab/window seems to confuse the users of their 'next step'

Also, every time a report is printed it seems to be creating a file with extension .ps in user's ..\AppData\Local\Temp folder. These files grow and waste space.
I am not sure if it's local to my system. Have you noticed this?
Developer
Oct 25, 2013 at 8:35 PM
Edited Oct 25, 2013 at 8:37 PM
Hi apaustria,

I ,sort of fixed, this problem. The PDFs will now be shown in the same page and it'll not move to another tab/window. It'll work if you are OK using google document previewer. See here..

1) Move the entire logic for creating the PDF file into a REST controller. This controller just returns the URL to the newly created PDF file ( instead of doing oResponse.redirect()).

2) Now, make a ajax call from the avfp page and pop the google previewer using iframe into a div. (say #response)
e.g.
function onSuccess(data, status)
{
   var odata =  JSON.parse(data);
  var cFrame = '<iframe src="http://docs.google.com/viewer?url='+odata.response+'&embedded=true" width="'+$("#content").width()+'" height="'+$("#page").height()+'"></iframe>';
 $("#response").html(cFrame);
}
Now, since we do not leave this avfp page, we can delete the PDF file when moving into another page. I.e. using another ajax call on 'unload' event to do the file cleanup.

Cheers to REST.
Coordinator
Oct 25, 2013 at 11:41 PM
Good job on the REST technique, TItu1. Although we have 2 different requirement (mine require the PDF's to be opened on a different tab/window), I might actually try your technique some of this if this can actually work as per my requirement.

Thank you again. //aldrin
Developer
Oct 28, 2013 at 5:56 PM
I just discovered a major problem in using google viewer. Seems that google reserves the right to cache these reports and place them in public domain. So these reports would be available to public, even after you have deleted the original PDF files.

Now, we can still use REST with ajax call and iframes and hope the user in on the browser that support PDF files ( e.g. chrome) or have their own local PDF viewer plugins. If not they will be prompted to first download the file and you can then delete the PDF file, as mentioned above.

Just remove the following from the above code
http://docs.google.com/viewer?url=
... if it's too good to be true.... then... we get rats.
Coordinator
Oct 28, 2013 at 6:19 PM
Seems that google reserves the right to cache these reports and place them in public domain. So these reports would be available to public, even after you have deleted the original PDF files.
If they really do this then it must affect anybody creating any reports using any tool, not just avfp reports, right? So there's got to be solutions out there already to combat this if people have a problem with it, I would think.
Developer
Oct 28, 2013 at 8:41 PM
Correct, it is not specific to AVFP.

I just wanted to ensure readers were aware of this potential issue, while incorporating this solution.

In fact google is tight lipped on this issue. They talk of docs security only with google apps accounts. Google just makes it clear that the document will be stored on their servers. That alone makes it unusable in my development.
Sep 8, 2014 at 11:52 AM
To solve the private datasession problem of pdfrun.print2pdf, I modified my frx to open its own tables (dataenvironment).

Then I hacked the frx at runtime to change the location of the dbf in the dataenvironment.
Sep 19 at 5:04 AM
I have a couple of questions about this. I am trying to use existing forms for an application which I don't have the original source code.

First off, I don't understand how I can make memory variables available to the form in its private datasession. Certain things in the form are controlled my config variables stored in mem files. For example, company name and address, whether discounts are taxed or not or whether shipping charges are taxed are not stored in any table.

Second, I created datasession in the form as mmarius28 suggested which eliminated problems accessing linked tables, but unless I can figure out problem #1 the form will not print. I have no idea if the datasession I created actually works.

Third, doesn't have to do with ActiveVPF, but my forms seem to contain code (functions which access variables which I can't make available to the form) which I can't see in the form editor at all. I opened the FRT file in notepad++ and I can see these variables and functions, but since I can't see them in the form editor I can't comment them out just to get the form to a minimally working state.

Both my original application and PocketFox have form editors built-in which look and act virtually identical. Do I need to get a copy of Visual Foxpro to access this hidden code in the forms? My web app is 90% complete and I haven't needed an actual copy of VFP yet. Basically, I need to know if VFP has things in its form editor that PocketFox does not have and whether I actually need that.

At the end of the day I could redesign the forms from scratch (actually just copy and paste what I can see), but wouldn't solve big problem 1.
Sep 19 at 7:12 AM
I created a new form from scratch and I am able to generate a PDF without the problem of hidden code/variables. I tried putting one of the object's built-in properties into my form and it printed.

I tried adding my own property to the oPDF object using addproperty(oPDF, "myvar", "my content"), but myvar was not found. I don't know why that wouldn't work.

However with just a single field in the form, pdfrun runs until it times out, eating over 1.5GB of RAM in the process and produces a file containing thousands of pages of the entire list of my sandbox table.

I removed the dataenvironment from the form, the PDF is generated quickly and is blank. The funny thing is now I can't even get it to show the report name like it did before. So I have to have a dataenvironment to display things that are not in a table, but if I do the select doesn't work. There is nothing in PocketFox that I can do with the dataenvironment except add tables. In notepad++ I can see the table has the alias cursor1.

The idea of changing the print2pdf.prg and re-building as Titu1 suggests makes sense as a way to get variables in, but cRecordSelect isn't working at getting the data I want into the form as it is.
Sep 21 at 5:27 AM
The dataenvironment inside the form editor is definitely a problem. If my table is in there, the form always contains all records.

What I did was I created a temporary table containing only the records I want to print. I consider this an ugly hack though, but at least it works.

It still doesn't fully work for me because the forms need access to variables and relations to other tables set which I have no idea how to make available to the PDF generator.

The way I see it right now I'll have to spend a lot of time changing the content of my forms and constructing customized queries to pass to each of my dozen or so forms to make available variables into columns and fake relations using joins. I'd rather not do all that if there is a better way.

USE PURCHASE IN SELECT("PURCHASE") ALIAS "PURCHASE" SHARED
cmd="SELECT * FROM purchase WHERE UPPER("+gcKeyName+")==["+lcKeyValue+"] INTO CURSOR tCursor"
&cmd
tmpdbf=SYS(2015)
cmd="COPY TO "+tmpdbf+" FOR UPPER("+gcKeyName+")==["+lcKeyValue+"]"
&cmd

USE IN (SELECT("purchase"))
USE IN (SELECT("tCursor"))
USE IN (SELECT(tmpdbf)) && close the tmpdbf to be able to delete it after we're done

oPDF.cRecordSelect="SELECT * FROM '"+oProp.DataPath+tmpdbf+"' INTO CURSOR tcursor"
oPDF.cReport=oProp.DataPath+lcReportFilename
oPDF.cPhysicalPath=oProp.UploadPath+zz+"\"+lcKeyValue+"\"
oPDF.cLogicalPath="http://"+oRequest.ServerVariables("HTTP_HOST")+JustPath(oRequest.ServerVariables("URL"))+"/"+oProp.UploadSubPath+"/"+zz+"/"+lcKeyValue+"/"
lcFile=oPDF.GetOutput()
Sep 22 at 8:02 AM
I found the hidden code in the Page Header band properties, in the on entry section, so no I didn't need VFP to find it. Unfortunately I can't do something simple like RESTORE FROM in there, so I still can't get access to any of my variables.

It looks like I'm going to try to do what Titu1 did. I'm going to add a new property for cExecScript so that the cRecordSelect property will still work though, the record select isn't actually the issue, it's getting some vars and relations passed over to this module before the select.

I will need VFP for this, just for this.

I basically consider the PDFRUN.EXE useless as it is and should be updated with this feature that Titu1 suggests.