Executing SQL within BP method

Data Abstract
1

I have a slightly weird scenario.

Within the AfterProcessChange method of my business processor, I need to execute a simple SELECT statement.
Basically, in a three-level table hierarchy (parent → child → grandchild), when I’m processing a change to a grandchild record, I need to determine the affected parent record ID.

When I try this, it blocks, which I’m presuming is due to the fact that I’m still within the original transaction for the processing of the delta.

Is there any way you can think of that I can execute such a SQL SELECT but within the context of the same transaction?

I’m currently constructing a TDALocalCommand object and calling the Execute method. I presume what I need is some way to get this object to exist/execute within the same transaction as the business process from which I’m calling it?

2

Hi,

I can suggest to don’t use TDALocalCommand in this scenario because it uses default transaction logic, i.e. TDALocalCommand.Execute causes start/rollback/commit transaction.

just use Connection.NewCommand or Connection.NewDataset. you can use SQL from your schema.

also you can control transaction manually with these events:

3

Thanks, but how do I get hold of a connection from within the AfterProcessChange method?

Ahh found it, can get it from the service instance.

4

Sorry but I’m struggling to wrap my head around this.

Can you give a basic explanation of how I can use the Connection.NewCommand?
I already have the relevant command configured in my Schema, I just want to execute it, passing a single parameter and retrieving a single result but within the context of the currently running transaction so it doesn’t block.

I can’t work out how to achieve this.

5

Hi,

something like

l_command := Command.NewCommand('test', stStoredProc, 'temp');
l_command.PrepareParams;
l_command.ParamByName('param1').asInteger := 1;
l_command.Execute;
y := l_command.ParamByName('res').asInteger;
6

Ok thanks.

So, in this example, would ‘temp’ be the name of the command in my Schema?
If so, are the ‘test’ and stStoredProc parameters effectively redundant?

7

Hi,

not yet. temp is just name that can be omitted:

l_command := Command.NewCommand('test', stStoredProc);
l_command.PrepareParams;
l_command.ParamByName('param1').asInteger := 1;
l_command.Execute;
y := l_command.ParamByName('res').asInteger;

you can use something like

cmd := ServiceSchema.Commands.SQLCommandByName('mycommand');
l_command := Command.NewCommand(cmd.statements[0].sql, cmd.statements[0].StatementType);
...
8

Thanks. Can’t find a PrepareParams method on the IDASQLCommand interface though.

9

Hi,

use RefreshParams instead

10

Cool that’s working. Think I’ll try creating a helper function to pull the necessary info from a schema command.

11

Could I use the NewCommand function of the schema itself?

This appears to do a bit more of the work for me, specifically with parameters?

12

Hi,

yes, of course - it does the same