Parametrized table in query business object

[Andrea C.]

Hi to all,

Is it possibile to parametrize the table name in a query B.O. or to have pieces of sql statements dinamically changed at "runtime"?

Thanks in advance

Andrea

[Paul]

I can't see parameterizing a table name working, for a start how would you define the column names and how would you bind these? The designer needs to know what columns exist in a BO so that you can define how these are going to be bound.  You could construct an exec statement in a query BO to do this but you will have the same issue.

It depends what you are trying to do. You can construct any SQL statement you like (and therefore dataset) at runtime but it depends what you are trying to achieve whether this is a suitable option for you.

[Jerome҉]

---

Andrea C. wrote:

Hi to all,

Is it possibile to parametrize the table name in a query B.O. or to have pieces of sql statements dinamically changed at "runtime"?

Thanks in advance

Andrea

---

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). The problem with this is that it opens the system to extreme danger of 'SQL Injection' and is massively dangerous.

see here for a solution:

http://metastorm.processmapping.com.au/post/Dynamic-SQL-for-query-forms-5681052

[Andrea C.]

Ok Jerome,

It's clear, unfortunately we're on Oracle, workaround proposed on ProcessMappingtools solution is good but limited to the amount of data you have to "copy" with the insert...select . I think we'll try to develop an external asp.net form and adding it as iframe in the admin form.

Thanks

Andrea

[Jerome҉]

I am not sure why Oracle would present any problems here. This works perfectly well on Oracle too. It is 'pure' SQL, not native to SQL Server in any way.

I know of no such limitation with Oracle regarding the amount of data you can insert in this manner. We do it all the time.

[Andrea C.]

Sorry, Jerome, I was not clear,

the reference to Oracle was only to specify our environment and the "limit" I referred to was about the time involved in the copy of data (I could have 1.000.000 rows to transfer), I'm worrying that the copy may take long time....