Multiple SQL statements in DBJoin or DBInputTable

CloverDX Platform
1

Hi there, is it possible to use multiple SQL statements in a DBJoin or DBInputTable?
E.g. SELECT into a temp table, and then SELECT from temp table with joins
E.g. DECLARE variables, and then SELECT values into variables, and then do a final SELECT depending on variables.

This used to work in version 3.3, but we are upgrading to 4.0 and it doesn’t work any more.
Thanks!
Jus

2

Hi,

Could you please post here an example of the graph that runs on 3.3 ,but not on 4.0 so we could get a better picture of this issue.

Thanks!

3

Hi Jakub,

Please see attached Old and New graphs.

The Old graph works in 3.3 but doesn’t work in 4.0.
The New graph (created with 4.0 designer) also doesn’t work in 4.0.

We have a several things like this in our current 3.3 production environment, using DBJoin and DBInputTable. Basically we put together 2 or more SQL statements in one component, to do 2 step “queries”. e.g.:
A. Step 1) open up encryption key and then Step 2) select encrypted values
B. Step 1) select complicated query into temp table X and then Step 2) select another more complicated query joining temp table X
C. Step 1) update staging table using filter and then Step 2) select from staging table with complicated joins

They work fine now, but when we try them out in 4.0 they don’t work anymore. For examples B and C above, they came back with this error: “The executeQuery method must return a result set.” For example A above, the error is not even relevant: " Error on field ‘Account_Id’ Invalid parameter index 1."

Is there a setting or something that I am missing? Or did things change and we cannot use more than one SQL statements anymore?

Thanks!
Jus

4

Hi Jus,

The DBJoin component was designed for just a single SQL select statement for joining and so it is not suitable for such complex statements. I suggest to split the statement into three graph phases as per below and configure Database connection to not be a thread safe connection. In order to do that you need to open your current connection configuration dialog and uncheck the threadSafeConnection box in the Advanced panel. Opening and closing the key can be performed by the DBExecute component.

1. Phase 0 - Open symmetric key, DBExecute
2. Phase 1 - Execute SELECT query, DBJoin
3. Phase 2 - Close the key, DBExecute

In case you don’t use the connection drivers that comes with the designer for some reason, please let us know the following in order to come with the most suitable solution :

1. The database version
2. The connection driver

5

That works. Thanks Jakub!