Handling errors when calling a stored procedure in PowerFX

(1) Share

Report

Posted on by maxpower45255

223

Does anyone have any suggestions on how to handle a sql exception within a mobile canvas app, right on the screen where it's called and not in the App OnError handler? 
I'd like to be able to capture the error right after the call. Below is the call to a stored procedure. I understand how errors are handled/reported in PowerFX and realize the error is not reported during the Set() and will only be thrown when Tabledata is used (much like in Excel). But I'm not sure how to catch it. any thoughts? 
 
//1
Set(TableData, MyDB.dboGetTransactions({employeeCode: "123invalid", beginOn: "01/01/2025", endOn: "02/01/2025"}).ResultSets.Table1) ;
 
//This won't throw the error:
IfError(
    Set(TableData, MyDB.dboGetTransactions({employeeCode: "123invalid", beginOn: "01/01/2025", endOn: "02/01/2025"}).ResultSets.Table1), 
    Notify("There was a problem retrieving data.", NotificationType.Error)
);
 
//2
//This is where TableData is being referenced.  
Set(
    Results,
    ForAll(
        TableData, // extract JSON from table1
        {
            transactionId: Text(ThisRecord.'transactionId'),            
            tripStartedOn: Text(ThisRecord.'tripStartedOn'), 
            employeeCode: Text(ThisRecord.'employeeCode'),              
            startMileage: Text(ThisRecord.'startMileage'), 
            endMileage: Text(ThisRecord.'endMileage'), 
            totalMileage: Text(ThisRecord.'totalMileage')            
        }
    )
);

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (5)

Answers (0)

Sort by

HaiVuVIE 27 on at

Like (0)

Report
Copy link

Link copied!

I think this learning section can have you: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-iferror.
You can use the "FirstError.Message" to get the first error message when it happen
Example:
- ButtonCanvas1:
Control: Button@0.0.44
Properties:
OnSelect: =IfError(Notify(1/0),Notify(FirstError.Message,NotificationType.Error))
when select this will return the error: "Invalid Operation: division by zero

Was this reply helpful? Yes No
Suggested answer

Gabriel G. 831 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi!

I suppose you gonna need to handle errors on your last set formula. Here it is:

IfError(

Set(

Results,

ForAll(

TableData, // extract JSON from table1

{

transactionId: Text(ThisRecord.'transactionId'),

tripStartedOn: Text(ThisRecord.'tripStartedOn'),

employeeCode: Text(ThisRecord.'employeeCode'),

startMileage: Text(ThisRecord.'startMileage'),

endMileage: Text(ThisRecord.'endMileage'),

totalMileage: Text(ThisRecord.'totalMileage')

}

)

);

Notify(FirstError.Message; NotificationType.Error); //Here, you will get error details, you can add operation to handle error process

Notify("Success!"; NotificationType.Success) //Here, you can add operation to handle succes process

//It could even get a 'finally' process here in another argument

)

I hope it helps!

___________________________________________________

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.

Was this reply helpful? Yes No
maxpower45255 223 on at

Like (0)

Report
Copy link

Link copied!

@GabGadou

Thanks for the suggestion. I've tried it before I posted. It still doesn't catch it. I get the "Success!" notification, instead of the error (2nd argument to ifError). When I comment out the iffError code, the error propagates to the app onError handler and I get the notification from the app OnError logic.

The issue I think is in the "Set()". The app isn't aware of it yet until the object is referenced. So it bubbles up. There has to be a way/workaround to handle exceptions during Set().

App OnError:

Notify("An unhandled error occurred. The support team has been notified.");

Was this reply helpful? Yes No
Suggested answer

CarlosFigueira Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
You can check if the variable is an error using something like the IsError function:

Set(temp, MyDB.dboGetTransactions({employeeCode: "123invalid", beginOn: "01/01/2025", endOn: "02/01/2025"}).ResultSets.Table1) ; If( IsError(temp), Notify("There was a problem retrieving data", NotificationType.Error), Set(TableData, temp); Set( Results, ForAll( TableData, // extract JSON from table1 { transactionId: Text(ThisRecord.'transactionId'), tripStartedOn: Text(ThisRecord.'tripStartedOn'), employeeCode: Text(ThisRecord.'employeeCode'), startMileage: Text(ThisRecord.'startMileage'), endMileage: Text(ThisRecord.'endMileage'), totalMileage: Text(ThisRecord.'totalMileage') } ) ); )

Hope this helps!

Was this reply helpful? Yes No
Suggested answer

Gabriel G. 831 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi again,

You probably have a asynchonous operation issue. Since your stored procedure is running in the back and not terminated, you try to set the result from it, does it make sense ?

You need to make sure your transaction is terminated before setting your variable (using timer, it can be done easily).

Let us know if it works!

___________________________________________________

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.

Was this reply helpful? Yes No