ContrAcct
asked on
Crystal Reports Viewer ActiveX Control in Visual FoxPro 9 Keeping Foxpro Tables Open
We have a form in our software package that runs the Crystal Reports Viewer 9.2 ActiveX control written in Visual FoxPro 9 SP2. The crystal reports we use have been written to read our VFP table data through ODBC. The crystal reports viewer works well, as I've used Experts' Exchange several times to get that piece to operate.
However, we have several features in our software that requre large and secure transaction processing, therefore we sometime need exclusive use of specific tables in our database. Since our application is typically shared over a network by many users, one user might have the crystal report viewer open with a report that uses a table which another user needs to use exclusively.
My question is: can the crystal reports viewer (or perhaps the crystal reports themselves) be written to close the VFP tables it uses once the report has been run without having to completely close the form containing the crystal report viewer?
I have tried to read the DownloadFinished property of the ActiveX control, but that has never returned true. Perhaps there is another property I can read, or a method I can run to close the VFP tables or data connection without having to close the resulting report.
FYI - These same reports, when run with the full Crystal Reports 2008 also leaves the VFP tables open without being able to get exclusive use.
However, we have several features in our software that requre large and secure transaction processing, therefore we sometime need exclusive use of specific tables in our database. Since our application is typically shared over a network by many users, one user might have the crystal report viewer open with a report that uses a table which another user needs to use exclusively.
My question is: can the crystal reports viewer (or perhaps the crystal reports themselves) be written to close the VFP tables it uses once the report has been run without having to completely close the form containing the crystal report viewer?
I have tried to read the DownloadFinished property of the ActiveX control, but that has never returned true. Perhaps there is another property I can read, or a method I can run to close the VFP tables or data connection without having to close the resulting report.
FYI - These same reports, when run with the full Crystal Reports 2008 also leaves the VFP tables open without being able to get exclusive use.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem is you arent opening the connection in code, the report or the viewer is opening it.
You may be able to close the connection but then if the user wants to view a different page, you may not be able to or you will have to reopen the connection.
In earlier versions of Crystal the connection could even remain open for 15-20 minutes after you closed the report.
mlmcc
You may be able to close the connection but then if the user wants to view a different page, you may not be able to or you will have to reopen the connection.
In earlier versions of Crystal the connection could even remain open for 15-20 minutes after you closed the report.
mlmcc
ASKER
"A work around exists, of course: you may close open tables by calling VFP trigger fired on a table designed for this purpose..."
pcelba, could you please explain your work-around further? I don't understand how to do that.
pcelba, could you please explain your work-around further? I don't understand how to do that.
Don't know how you would establich a table trigger to close tables, as Crystal report only reads data and there is no select-trigger, only insert, update and delete, all operations Crystal does not do on data.
Also via VFP ODBC you can't connect to a DBC with DBC-Triggers, otherwise you could program some routine running on an OpenTable event.
I don't use Crystal Reports but I think you can run stored procedures instead of sql queries. So you could put data retrieval into such a stored procedure, that will query data into a cursor it creates and afterwards could already close tables. That would be an option.
If you have no DBC, then create one. Tables don't necessarily need to be added to it, you can use ot to simply have your data access code as VFP code in a stored proc. Don't ask me though, how to modfiy your reports to call stored procs instead of direct querying of data.
Besides this, why should that matter? Even if you use technical transactions BEGIN TRANSACTION ... END TRANSACTION is possible on tables used shared. There are operations like PACK or ALTER TABLE needing exclusive access, but not transactions, otherwise the concept of transactions would be quite useless.
What's true is, that transactions can lock out others, but not from read operations, so a report can run, while a transaction takes place, it will only not see all data modified ar added by the running transaction unless it's committed.
Bye, Olaf.
Also via VFP ODBC you can't connect to a DBC with DBC-Triggers, otherwise you could program some routine running on an OpenTable event.
I don't use Crystal Reports but I think you can run stored procedures instead of sql queries. So you could put data retrieval into such a stored procedure, that will query data into a cursor it creates and afterwards could already close tables. That would be an option.
If you have no DBC, then create one. Tables don't necessarily need to be added to it, you can use ot to simply have your data access code as VFP code in a stored proc. Don't ask me though, how to modfiy your reports to call stored procs instead of direct querying of data.
Besides this, why should that matter? Even if you use technical transactions BEGIN TRANSACTION ... END TRANSACTION is possible on tables used shared. There are operations like PACK or ALTER TABLE needing exclusive access, but not transactions, otherwise the concept of transactions would be quite useless.
What's true is, that transactions can lock out others, but not from read operations, so a report can run, while a transaction takes place, it will only not see all data modified ar added by the running transaction unless it's committed.
Bye, Olaf.
I've missed the question, sorry.
To close tables in the trigger is very easy. But it seems it will be better to create an UDF instead of trigger, so:
1) Suppose you have the Datbase container (DBC) already created
2) Create a table (e.g. DummyClose) having one column of any data type
3) Add one record into this table, value is not relevant
4) Create a user defined function in the DBC:
FUNCTION CloseTable
LPARAMETER lcAlias
IF USED(ALLTRIM(lcAlias))
USE IN (ALLTRIM(lcAlias))
RETURN .T.
ELSE
RETURN .F.
ENDIF
And now you may close any open table(s) by executing the following command:
SELECT CloseTable("MyFirstAlias")
The only table you cannot close by above command is the DummyClose itself.
To close tables in the trigger is very easy. But it seems it will be better to create an UDF instead of trigger, so:
1) Suppose you have the Datbase container (DBC) already created
2) Create a table (e.g. DummyClose) having one column of any data type
3) Add one record into this table, value is not relevant
4) Create a user defined function in the DBC:
FUNCTION CloseTable
LPARAMETER lcAlias
IF USED(ALLTRIM(lcAlias))
USE IN (ALLTRIM(lcAlias))
RETURN .T.
ELSE
RETURN .F.
ENDIF
And now you may close any open table(s) by executing the following command:
SELECT CloseTable("MyFirstAlias")
The only table you cannot close by above command is the DummyClose itself.
Of course, you may update the function to close all tables open in the current connection:
FUNCTION CloseAllTables
LOCAL lnAliases, lnI, laTbl[1], lnCloseCount
lnAliases = AUSED(laTbl)
lnCloseCount = lnAliases * 1000
FOR lnI = 1 TO lnAliases
IF RIGHT(DBF(laTbl[lnI,1]), 4) <> ".TMP"
IF ! "DUMMYCLOSE" $ DBF(laTbl[lnI,1])
USE IN (laTbl[lnI,1])
lnCloseCount = lnCloseCount + 1
ENDIF
ENDIF
NEXT
RETURN lnCloseCount
The return value informs about the total number of open tables and also about the number of tables closed by the function call.
FUNCTION CloseAllTables
LOCAL lnAliases, lnI, laTbl[1], lnCloseCount
lnAliases = AUSED(laTbl)
lnCloseCount = lnAliases * 1000
FOR lnI = 1 TO lnAliases
IF RIGHT(DBF(laTbl[lnI,1]), 4) <> ".TMP"
IF ! "DUMMYCLOSE" $ DBF(laTbl[lnI,1])
USE IN (laTbl[lnI,1])
lnCloseCount = lnCloseCount + 1
ENDIF
ENDIF
NEXT
RETURN lnCloseCount
The return value informs about the total number of open tables and also about the number of tables closed by the function call.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
A work around exists, of course: you may close open tables by calling VFP trigger fired on a table designed for this purpose...