PDA

View Full Version : Dynamically change table path



Clayton
30-Sep-2010, 08:46 PM
Hi,

Is there a way to change a table path dynamically from my application?

I found psTableSchema property, but I couldn't find a way to add individual tables to my report in VRW (as mentioned on property description at user's guide).

Also, I Can't change the DF_FILE_ROOT_NAME on filelist because this table path change is simply temporary.

Thanks.

Michael Mullan
30-Sep-2010, 09:30 PM
Have you looked at Dave Martinko's "TempFile" package?

It automagically creates a local copy of a file, which you can then populate & report on.

1. Create Temp File
2. Populate Temp File
3. Run report.

it creates the temp file by copying the structure of an existing file in the filelist, and opeining it AS the original file, so as far as your report knows, it IS the local file. Kinda sweet really.

Clayton
1-Oct-2010, 06:47 AM
That's exactly what I'm doing, not with his package but by myself.

I have this working for years in Crystal by changing table location, but VRW read data from the default (and empty) table instead of the local copy because it opens the table from the filelist path, not the open as table.

Anyway, thanks.

Clayton
1-Oct-2010, 01:59 PM
Looks like I got the table path changed dinamically issue solved by coding some stuff in my VRW subclasses.

Now I fell into another problem: Before running the report, I have a zerofile to erase data in the temporary local table. At first running all works fine, then I close the preview panel and run the report again and I got the following error: Access violation, file in use (in zerofile statement).

As I'm sure the local copy of the table is not opened by any application, the question is, does the VRW report keeps the tables opened after the preview panel is closed? If yes, how do I do to close the tables?

Thanks.

Michael Mullan
1-Oct-2010, 07:17 PM
That's a reported bug in the vrw client. It does not close the files it opens after the report completes. I thought that since this was reported in an early beta though that it was closed.....

JimNC9
1-Oct-2010, 08:24 PM
Add
Close Tablename
Before the Open Tablename

Vincent Oorsprong
3-Oct-2010, 07:56 PM
Michael,

It is an open issue on the list.

Clayton
4-Oct-2010, 06:33 AM
Didn't saw that bug report. Thanks for clarifying.

Regards.

Clayton
4-Oct-2010, 06:35 AM
The problem isn't on VDF side.

Regards.

Clayton
4-Oct-2010, 06:39 AM
It will be solved on the next release?

Let's close the issue and the tables. :)

Regards.

Vincent Oorsprong
5-Oct-2010, 02:07 AM
Clayton,

This is indeed implemented in the upcoming release.

Rafael M Heise
5-Oct-2010, 07:07 AM
Hi Clayton. I don't know if you already done it, but I did it in my application. I create files dynamically and I need to change the filename on my report.

To do that, I override the procedure AutoLocateDFFiles to call a function mind, like:



Procedure AutoLocateDFFiles String sReportId
Send Update_Temporary_Files sReportId
Forward Send AutoLocateDFFiles sReportId
End_Procedure


This is my Update_Temporary_Files



Procedure Update_Temporary_Filename String sReportId
Integer iTables iTable
String sTableSchema
String sAux
Get TableCount sReportId to iTables
If (iTables >= 1) Begin
// Enumerate the tables and change the tableschema
Decrement iTables
For iTable from 0 to iTables
Get psTableSchema sReportId iTable to sTableSchema
// sTableSchema is blank when the table was opened via the filelist
// here, TMP_FILE is the root name
If (Uppercase(sTableSchema) contains "TMP_FILE") Begin
Move (ExtractFileName (sTableSchema)) to sTableSchema
Get FindTablePath sTableSchema to sTableSchema
Set psTmpFilenameOld to sTableSchema
Get psTmpFilename to sAux
Set psTableSchema sReportId iTable to sAux
End
Loop
End
End_Procedure


And then, when the user closes the report, I need to back the old filename. And I do that in CloseReport method, as following:



Procedure CloseReport String sReportId
Send Backout_Temporary_Filename sReportId
Send RefreshReport
Forward Send CloseReport sReportId
End_Procedure

And this is my Backout_Temporary_Filename method:



Procedure Backout_Temporary_Filename String sReportId
Integer iTables iTable
String sTableSchema
String sAux
Get TableCount sReportId to iTables
If (iTables >= 1) Begin
// Enumerate the tables and change the tableschema
Decrement iTables
For iTable from 0 to iTables
Get psTableSchema sReportId iTable to sTableSchema
// sTableSchema is blank when the table was opened via the filelist
If (Uppercase(sTableSchema) contains Uppercase(psTmpFilename(Self))) Begin
Get psTmpFilenameOld to sAux
Set psTableSchema sReportId iTable to sAux
End
Loop
End
End_Procedure


In this case, I have only one tempory file. And to do these changes I created a new VRWPreviewPanel object. I copied the current VRWPreviewPanel (I don't remeber the exact filename) to my Workspace and use that.

I really belive that the current VRWPreviewPanel could use a relative object (eg a global variable) of cVRWReportViewReport class. So we all could do our changes and set this relative object (like Object_Error_Id). Or maybe it could use some pattern. I don't know. Anyway, that what I did. Maybe there is other ways to do the same thing.

Clayton
5-Oct-2010, 08:02 AM
Rafael.

Yes, I got it working, in a different approach.

I already had created my own set of VRW subclasses to make possible to set psFunctions in a VRWPreviewPanel view way. Now I just added some stuff to those subclasses to solve it: I'm using arrays to store the table names and paths in StartReport (in RV file) and then change the path in OnInitializeReport (in my subclasses).

Anyway, thanks for sharing the code. Always good see a different point of view.

I hope the upcoming release has changed something about those features and I can drop my subclasses.

Regards.

tnguyen
26-Oct-2011, 01:37 AM
I am using VRW1.5. Can we dynamically change table path in this version yet ? If yes, how do i do it ?

Thank you.

Vincent Oorsprong
26-Oct-2011, 06:33 AM
Hi tnguyen.

What do you really mean?

And do you mean in the Visual Report Designer or via integration?

Via integration it is possible to change the path either automatic (pbAutoLocateDFFiles) or coded which is not that much work.

tnguyen
26-Oct-2011, 01:36 PM
Hi Vincent

Thank you for your reply.

The problem I face at the moment. We have table more 100,000 records. I have to use psfilterfunction to filter complex date selection to output report. And it takes quite long to page up and down.
So, it comes to my mind that I should use a temp table and populate it with records I wants and VRW will use that table to output (less records). The temp table can be initialised each time the report is run. And again, It will have problem in multi-user mode.

Please advise what is the best way to achieve that for now.

Vincent Oorsprong
27-Oct-2011, 12:40 AM
Hi tnguyen,

First I think you should look at the psFilterFunction. The filter is offloaded to server or used for jump-into-out-of-index if it does not contain ORs situations or a function. This means don't do:

return {@ProjectDate}or

return {orders.orderingdate} = DateSerial(2011,10,27)
Try to use a filter expert setting if possible as that one can always be offloaded/used for index search operations.
Of course if there is no index available the whole issue written above does not help at all.

The ability to create an in memory table, an array of data, is called RDS (Runtime Data Source) and comes with version 2.0. We are working hard on the product and hope to produce a first alpha build soon.

Clayton
27-Oct-2011, 05:19 AM
Hi Vincent,

Great news, RDS sounds very promising.

Edgar H. Peņa C.
25-Jul-2014, 09:57 AM
hi,
I'm using this procedure to do reporting with VRW 3., When using tables with instruction OPEN AS ..


Procedure AbreArchivos Global String sReportId
String sTableName sTableSchema
String sPath sFileName sSubReportId
// String [] sFunctionName
Integer iTable iTables iSubReport

Move (RutaActual(1)) to sPath
Get TableCount sReportId to iTables
Get SubReportCount sReportId to iSubReport


Decrement iTables
For iTable from 0 to iTables
Get psTableName sReportId iTable to sTableName
Move (BuscaNombreAlias(sTableName,giAnoTarea)) to sTableName
Set psTableSchema sReportId iTable to sTableName //(BuscaNombreAlias(sTableName,giAnoTarea))
Loop
Decrement iSubReport
While (iSubReport>=0)
Get SubReportId sReportId iSubReport to sSubReportId
Get TableCount sSubReportId to iTables
For iTable from 0 to (iTables-1)
Get psTableName sSubReportId iTable to sTableName
Move (BuscaNombreAlias(sTableName,giAnoTarea)) to sTableName
// Set psTableSchema sSubReportId iTable to sTableName
Set psTableName sSubReportId iTable to sTableName
Loop
Decrement iSubReport
Loop
End_Procedure