DA and inject dynamic SQL

Data Abstract
1

Hi,

on the project I’m working on I need to implement dynamic queries. Let me explain. The database (Oracle) has a main schema and a series of secondary schemas that may not exist in production as they depend on the license that the customer pays. So if I call A the main scheme and B, C the two secondary schemes I need this:

a query that always starts from schema A but that if schema B or C exists it can dynamically refer to the tables of these schemas.

So I need a system that allows me to dynamically inject SQL code into the query (I was thinking of using DASQL)

An example:

Exist only schema A

SELECT
  A.TABLE.FIELD1,
  A.TABLE.FIELD2
FROM
  A.TABLE

Exist schema A and schema B

SELECT
  A.TABLE.FIELD1,
  A.TABLE.FIELD2,
  B.TABLE.FIELD3
FROM
  A.TABLE
  INNER JOIN B.TABLE1 ON A.TABLE.FIELD1 = B.TABLE1.FIELD1

where B.TABLE.FIELD3 and INNER JOIN B.TABLE1 ON A.TABLE.FIELD1 = B.TABLE1.FIELD1 I can inject it at runtime

Any idea?

Thank you very much

2

Hi,

In Delphi, you can check what tables exist with Oracle_DoGetNames method.
after this you can use DASQL for creating required request.

3

Hi EvgenyK

sorry, yes in Delphi :wink:

So I add all tables (for all oracle schema that interest me) in the DASchema and after I checked if the tables exist I can use DASQL for create join and dynamic fields? Quite right?

Thank you very much

4

Hi,

yes.
Oracle_DoGetNames works with the server metadata directly w/o TDASchema.
after validation, you can use schema with all tables or load required schema to TDASchema according to user’s license.
in this case, you can work with usual tables in schema w/o DASQL.

schema1 may contain

SELECT
  A.TABLE.FIELD1,
  A.TABLE.FIELD2
FROM
  A.TABLE

and schema2:

SELECT
  A.TABLE.FIELD1,
  A.TABLE.FIELD2,
  B.TABLE.FIELD3
FROM
  A.TABLE
  INNER JOIN B.TABLE1 ON A.TABLE.FIELD1 = B.TABLE1.FIELD1
5

Hi EvgenyK

unfortunately working with dynamic DAschemas becomes difficult because I can have many combinations. Let me explain.

Can I have an integration with a “Patient Queue Management” system (which has its own schema). Or have an integration with a management system for external requests (which has its own schema) from which I have to retrieve some data or an integration with a document management system (which has its own scheme) from which the number of documents is retrieved by position etc etc…

In production I can have integration with all schemes or a combination of these

Since all this is highly dynamic and there are so many DAschemas, I having to create many combinations of schemes which then become impossible to manage and maintain.

6

Hi EvengyK

I did some tests with DA SQL but unfortunately in some cases it doesn’t work and I have errors.

Specifically I need in the select statment invoke some custom functions that are in another schema and this thing does not like the DA SQL.

Instead enabling the AllowExecuteSQL flag on the service and on the client side by setting in the RemoteDataAdapter the method GetDataCall method to SQLGetDataEx setting the parameters everything works fine.

I don’t really like enabling the AllowExecuteSQL flag but to have the dynamism I need I don’t see alternatives .

Is there a run-time way to enable the AllowExecuteSQL flag only under certain conditions so that it is active only for queries that need to be built at run-time and not always and only for the specific call?

Given the security problems, what do you recommend to manage SQL execution correctly and safely?

Thank you very much and best regards

7

Hi,

DASQL can’t work with different schemas

SQLGetDataEx method is deprecated with DA SQL.

you can store this value in session and then enable/disable it

I need some testcase for reproducing your issue. Probably you are doing something wrong.

8

Hi EvgenyK

Ok, but can this work with calling a function? In Oracle I can create a synonym on the main schema that link to the original function of the other schema

So you advise against using it?

but I should enable it when I open a specific dataset but I have not found an event that allows me to manage it without first giving the error that the AllowExecuteSQL flag is not enabled

No, I have no errors. My question is, since enabling the use of SQL execution can run into security issues if there are particular things to be aware of.

Thank you very much

9

Hi,

I mean DASQL cannot work with different TDASchema components, but you can use objects from different DB schemas in one TDASchema.

someone can pass dangerous commands like DROP xxx; as SQL so you could lose everything.

you can override standard DataAbstract service methods like GetData.

10

You could create a database view or define a static SQL statement for a table with calls to these functions as “virtual” fields. Then they can be used in DA SQL

11

Hi antonk

thank you for your suggestion.

The problem I see is that the schema that contains the functions may not be installed … but I do an evaluation on this idea :wink:

Best regards