Report form closing a table? 1

[alan232]

Hi All;

I can't figure this out.

I'm using a 'select' statement based on a table, zpeople, to read records into a cursor. The cursor is then checked for a record count > 0. If there are records, then 'report form...' is used to print records. The initial problem was that printing directly from the cursor occasionally would fail with 'variable..not found'. So I made a view out of the select statement and placed it in the datasession of the report. Now occasionally--not always--upon return from the report generator, 'report form...', the zpeople table queried by the view is closed, whereas it was open and 'used' before starting the 'report form..' statement.

zPeople is NOT in the dataenvironment of the report but the dataenvironment is not private...Should it be? Anybody know why the 'report form...' is closing the table, zpeople ?

Any help is appreciated,
Thanks,
Alan

 

[robsuttonjr]

I never have a cursor or table in the data environment of the report. Just make sure you have the cursor built before launching the form. You can alway initialize a blank cursor in the init of the form or program. Then fill it as needed. Then just before calling the report, select the zpeople cursor and do the report. Then you will never get the variable not found and you won't have to bind the cursor to the report. Also I avoid using the name of the cursor/table in the fields of the report but simply ust the column names.

Regards,

Rob

 

[alan232]

Hi,

That's exactly the way I had it before--didn't work. I would occasionally get the 'variable...not found' error. That's why I created the view and tied it to the dataenvironment. Before creating the view, I was simply using a cursor from a select statement without table names in the report variables. Didn't work reliably. Now I no longer get the 'variable...not found' error, but end up with it occasionally closing a table--that was never in the dataenvironment to start with.

I am wondering what would happen if I did put zPeople into the dataenvironment, but created another datasession just for the report.

Any ideas?
Thanks,
Alan

 

[robsuttonjr]

I am thinking that there is a tie between the occasional variable not found and the view being closed. Something in your code is possibly closing that view/cursor prematurely. I would focus on debugging what is causing this to happen. The occasional variable not found should never happen unless the program has a bug which either closes or does create the view/cursor.

Regards,

Rob

 

[alan232]

Hi Rob,

My thoughts exactly..

The original code used four sequential statements:
1. select * from ...zpeople into cursor _prn
2. if reccount() > 0
3. report form c:\....\rx.frx to printer noconsole
4. endif

The report, rx.frx, used no table names, but only the columns in _prn.

The whole setup is called as part of a timer program set to go off every 15 seconds--regardless of what the rest of the system is doing. My hope was that since this was executing code, it would go to completion before windows (or vfp) responded to any other event by the user. I am now thinking that maybe windows or vfp IS responding to the user while 'report form..' is generating the output. i.e., that a user event is occuring durring the report generation, and that this user event is, at least temporarily, changing the workarea away from _prn. Such would explain both the 'variable...not found' error and the table closure problem.

Is there anyway to stop an interruption in the 'report...' command such that a seperate user event is not allowed?

Thanks again,
Alan

 

[robsuttonjr]

You could insert into the timer block statements that disable user clicks etc. But really I would evaluate whether this timer process is the best way to handle the task. Timers can be sloppy and are often used somewhat lazily. Timers have their place. But in this case if you want to leave user controls enabled which is the best approach you should look at some other method of running the report. If I knew more about why the report is run by a timer I could possibly probe more.

Regards,

Rob

 

[alan232]

Hi Rob;

O.K. ... here goes...

I've written this application over the last year or so at the request of BlueCross BlueShield of Tennessee. My original app was written to run on a single machine in which I started the process, then went to my PDA, input data, then came back to the machine and printed out the forms. As long as I was the only user, there was no problems of either timing or concurrency. BCBS liked it so much as to suggest I market it. But honestly, while it may have worked for them, it did not work the way I wanted it to. So before going any further with it, I changed the structure.

The current structure is using one machine--windows xp-- (at the front desk) to be a 'server' for my PDA while my receptionist is using the same machine as a 'client' to both print and input data. In addition to the machine at the front desk, there are at least two other 'client' machines on the network.

I've rewritten the dataenvironment of my main app to change to using the database on the server machine directly (done by the cursors in the dataenvironment of the main form). I have not as yet gone to views as they would appear to incurr both more programming and machine cycles. Having all the machines use the same database on the server has made things very easy, quick, and reliable.

However, since data is now being input into the server at random times from the client machines, I constructed three job ques to handle different problems. My initial design had the server polling client machines on a timed basis for data updates. I found that to be relatively slow and ineffecient. So the next design opened the job ques on the server as shared and let the applications on the client machines both update the database directly and notify the 'server' by way of the job ques when new data was ready.

In addition to the client machines inputing data to the database and the job ques, my PDA via wireless 802.11b is serviced by a seperate stay resident program on the 'server' such that when I transmit to the server record changes or additions on the pda, the data is moved into specific files--not part of the database--on the server.

When all is said and done, the single 'timer' event (which I actually modified from cppTimer (freeware) ) then reads the PDA files for changes, moves the data from the pda into the database...and also checks the job ques for pending data to be moved to the pda. One function which is needed immediately is perscription print outs since this has to be done in real time and can't wait to the end of the day or anyother time. It must be done while the patient is still here and it must be 100% perfect 100% of the time.

Hence, in a nutshell, the timer event is needed to force the system to read input from the PDA files and update the PDA files for transfer back to the PDA. In addition to all the many problems, the vfp app runs over a thousand times faster then file sync does from the pda and it seems (I don't have acces to the source code) that the pda is using a record lock scheme via ODBC. The result is I can't seem to figure out when the PDA in syncing with the server and VFP is (and does) completely read/write to the PDA files WHILE the pda is syncing. The result is totally messed up indices and lost data. I have figured out ways arround most of it, but clearly still have problems whenever vfp is syncing with the same file that the pda is using...but that is a different story. The report file here in question does not use the PDA files at all.

I can't think of a better way to force the 'server' machine to update or be updated by the pda.

If you know of a better trigger, I'm open to suggestions.

As it is, I'm getting about 97% error free run-time...I won't be happy with anything less then 100%

If I haven't totally bored you by now, you get a medal!

I hope I've explained the problem,
Thanks again,
Alan

 

[alan232]

Hi ;

Just a thought (maybe a new thread?)...

It's my impression that the cpptimer forces a system interrupt to stop every and all ongoing processes to service the timer. Is it possible that the vfp report generator, i.e., 'report form...' is also causing a system interrupt in such a way as to allow at least momentary control back to the windows graphical interface (and hence be allowing user interrupts)? If such user interrupts are allowed durring the run of 'report form...', then the report environment could be changed before the report generator is finished.

any thoughts?
alan

 

[robsuttonjr]

I don't understand why you can't have say a sql database engine on the server and all clients including the PDA update the table via stored procedures. Having files which are updated than timers push the data from those files to a database is not a good plan. Without really looking to much further into your code and how you do it I would suggest the best method to accomplish your project goals is to have a single database on the server say sql server. Have the pda send data to the server via stored procedure calls as well as the client machines. The clients can still use foxpro as a front end client. But the pda must use some other app built in either c#, java, etc. Bottom line is not always is it best to make your original application fit the mold but stand back and evaluate whether your current developments have exceeded their ability to meet the new requirements, ie real time. But that said you can still perhaps make what you have work by simply making sure that your server is the home for all data and that no data is stored elsewhere. The pda should pulll data for a prescription directly from server then directly update the server data via stored procedure calls with parameters. Keeping all data centralized makes for less headaches and timers.

Regards,

Rob

 

[alan232]

Hi Rob,

I don't have a good answer (and not even a bad one). The forms used by the client machines were all initially developed for use by one machine...i.e., the forms all communicate directly to the tables. In fact, the dataenvironment on each form was initially developed assuming the data resideded on that machine. It was relatively recent that I learned how to redirect the vfp cursors on form load to address a different machine on startup. I may be missing your point, but non of my forms (front end) use either views or stored procedures. I've used views enough to do the necessary recoding, but it would be an awful lot of recoding! Unfortunately, I don't have any experience using stored procedures, so I don't know how the front-end form would use them. I've been essentially using 'tableupdate()' to write out the form data from cursor to table.

A suggestion...or short example on using a stored procedure... would be most helpful.

The PDA, on the other hand, is using ODBC as the interface to read/write to the vfp tables...and I don't have the code for this. So I'm not sure how I could get the PDA to use stored procedures unless a timer event is made to service the PDA files and move the data in/out of the database proper (which is essentally what I'm doing now). But that again brings me back to a related problem of 'exclusive' not being enforceable while the PDA is syncing with the vfp files. And (as I noted on another thread), for some strange reason once the PDA has syncronized with the VFP tables, VFP can no longer gain exlusive use of those tables...so again I'm forced into read/writes by vfp while the pda is also read/writing...not a good thing.

However, I totally agree with you...having all the data on one machine has definitely made things easier and faster. I also think that if I had one machine that did nothing but handle the data, there would be less complications. However, I don't have the luxury of an extra machine around. Which essentially brings me back to finding a way of locking the user interface out while the 'server' functions of the machine are being executed. I'm also betting that 'report form' generator is executing it's own interrupt to place the data in the printer driver spool of windows and that windows is executing an interrupt to send the data to the printer..

I don't know.

Bottom line is your right. But I don't know enough about the PDA communications to write the code ... and nor do I know of any products, other then mEnable (which I'm using), that communicates between a PDA and a database. Unfortunately, I'm fairly sure the designers of mEnable anticipated only read/writes to a table; a table not used by anybody but the pda.

Welcome to my dilemma,
Thanks for helping...and putting up with my ignorance,
Alan

P.S. I guess the crux of the matter is that mEnable is geared to directly read/write to files..and I don't know how to get the PDA to use stored procedures.

 

[alan232]

Hi,

"the pda should pull data for a perscription directly from the server and then directly update the server..."

This would be great, except:
1. vfp still needs access to the table and I can't seem to detect when the ODBC interface is updating the table, and
2. I still need a way to force print outs immediately (or close to it), and
3. even if I could figure out how to force a print out without a timer, I still endup comming back to the start of this thread, that the 'report form...' generator is losing its environment and closing one of its source tables.

Even so, if I could get arround #1 and solve #3, a timer could still be used for report generation. (??)

Thanks again,
alan