PDF reports in SQL server (client server) environment

Topics: Developer Forum
Developer
Aug 14, 2013 at 9: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 14, 2013 at 11:16 PM
Thank you - excellent feedback for PDF reports!
Developer
Aug 19, 2013 at 4: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 6: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 1: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 2: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 7: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 9: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 2: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 6: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 8: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 7:35 PM
Edited Oct 25, 2013 at 7: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 10: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 4: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 5: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 7: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.