Registered: 1158198299 Posts: 5,507
Reply with quote
The whole issue of 'dynamic' SQL has been difficult in version 9. In version 7 we could do this easily as the 'code' was interpreted at run time. Now we have 'real' code, and it is not so easy.
For Business Objects, we do not have the ability to build dynamic SQL statements. In code, we can, however, as we can pass in any string we have built. Several workarounds have been suggested by Metatsorm, but all involve the 'execute' facility in SQL Server (not sure what is required in Oracle, and that has net been addressed). The problem with this is that it opens the system to extreme danger of 'SQL Injection' (look it up if you do not know it). In my experience, no well-managed development environment would even consider allowing such practices, it is massively dangerous. So, what can we do with Business Objects? We have a workaround that works very well for us in a limited way. We have a FolderQuery table with two fields. A QueryFolderId, and a ResultFolderId. We run any SQL we like from selections and entries made by a use in a query form, and insert values into the QueryFolder table based in the results. Typically this is managed in one SQL statement using a "INSERT INTO .... SELECT ... FROM ...". The select from part is dynamically built and passes in the query folder id and the results of the query. You can see this in action in our Folder Query form in the Admin Tools: http://www.processmapping.com.au/freestuff/freemetastormbpm9solutions/AdminTools.html The advantages are that we do not need to have a dynamic BO, and therefore the BO we do build can make full use of parameters and a well-indexed table. The limitation is that it only uses the FolderId, so this approach only works on the eFolder or Process tables. It can, however, be used for other tables with modifications. __________________ Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.