Using Remote Commands with Data Abstract for Xcode

Recently, we have added some new functionality to the DARemoteDataAdapter for working with Data Abstract Commands.

Let me briefly refresh your memory about what Commands are and when they can be helpful.

Data Abstract Commands are part of the DASchema and represent a reference to certain Database Stored Procedures or just keep custom SQL expressions we need to execute.

The most common commands perform INSERT/UPDATE/DELETE actions for given tables.
In addition, they can be used to perform calculations in the database and return the results to the user.

Before now, we could only perform command calls by using the DataAbstractService_Proxy directly.
I’ve posted the workaround for that some days ago Support for RemoteCommands in DA/Xcode?.

But with the next release it will be much simpler.

To execute commands from the sample mentioned above, we just have to perform the following code:

[remoteDataAdapter executeCommand:@"Insert_BPWorkers" withParameterValuesAndNames:@"John1", @"FirstName", @"Doe1", @"LastName", @"444-22-33-22", @"Phone", [NSDate date], @"BirthDate", @"Manager", @"Position", nil];
The same can easily be done in an *asynchronous* way.

But let me show you how it works on a more complex sample with stored procedures that have output parameters.
Imagine you have an MSSQL database with the following simple TestStoredProcedure:

CREATEPROCEDURE[dbo].[TestStoredProcedure] @groupId INT=0, @name nvarchar(255) OUTPUT, @COUNTINT OUTPUT ASBEGINSET NOCOUNT ON; SELECT TOP 1 @name = p.Name FROM Products p WHERE p.GroupId = @groupId; SELECT @COUNT=COUNT(*)FROM Products p WHERE p.GroupId = @groupId; RETURN157; END
As you have noticed, this stored procedure returns *@name* & *@count* as output parameters and some *“magic number”* as the result. So let’s use it.

Note Don't forget that Data Abstract doesn't work with the database objects directly, but with schema objects (tables, commands etc).

If you need to get back the output parameters, you need to use the ExecuteCommandEx method.

Beside the usual arguments like command name and parameters, it takes another NSDictionary as the output argument which will contain the output values.

The code for the asynchronous call will look something like this:

  -(IBAction)callTheCommandEx:(id)sender {NSString*COMMAND_NAME =@"TestStoredProcedure"; NSDictionary*params =[NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithInt:10], @"groupId", nil]; DAAsyncRequest *request =[dataAdapter beginExecuteCommandEx:@"TestStoredProcedure" withParameters:params start:NO]; [request setDelegate:self]; [request start]; }   -(void)asyncRequest:(DAAsyncRequest *)request didFinishExecutingCommand:(NSString*)commandName withResult:(int)result andOutputParams:(NSDictionary*)outParams { NSLog(@"Called ExecuteCommandEx for %@ with result %i", commandName, result); NSLog(@"%@", outParams); }
Or the same but using *blocks*:
-(IBAction)callTheCommandEx:(id)sender {[dataAdapter beginExecuteCommandEx:@"TestStoredProcedure" withParameters:params withBlock:^(int result, NSDictionary*outParams){ NSLog(@"Called ExecuteCommandEx for %@ with result %i", @"TestStoredProcedure", result); NSLog(@"%@", outParams); }];
In both cases we’ve got the following results:
Called ExecuteCommandEx for TestStoredProcedure with result -1{"RETURN_VALUE"=157; count =42; name ="APC ProtectNet PNET1GB"; }