rado
Posts: 221
|
| Posted: 06/20/2009, 2:28 PM |
|
Hi everybody,
I have a record which in custom insert using SQL Stored Procedure and I don't have any message in my record form in case that record is duplicate (for example: room already reserved). So, originally after the SP is done successfully I got return values in table in this form:
1.
============================================================================
mysql> call ReserveRoom (32212117,'2009-6-6 10:00:00','2009-6-30 15:00:00','2009-12-10 11:00:00');
+---------+------+
| success | id |
+---------+------+
| 1 | 42 |
+---------+------+
1 row in set (0.06 sec)
============================================================================
and when the room is already reserved for specified date range (insert NOT successfull) I got this
2.
============================================================================
mysql> call ReserveRoom (11011,'2009-6-6 10:00:00','2009-6-30 15:00:00','2009-12-10 11:00:00');
+---------+----+
| success | id |
+---------+----+
| 0 | 0 |
+---------+----+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
=============================================================================
What I MUST to do is to pickup return value of SP after unsuccessful insert (2.), create message that room is already reserved and set the message in error row in record form.
I don't know how to retrieve the return values of SP since they are available just after SP is executed. I know (?) that this picking up the return values should be run in "On Validate" event, but how, NO IDEA.
Is there anybody that could point me in right direction what to do?
Thanks,
Rado
|
 |
 |
cabrera
Posts: 10
|
| Posted: 06/22/2009, 9:49 AM |
|
Hi Rado,
Where did you put the store procedure you describe above? You don't necessarily have to put it in the On Validate event. I solve a simmilar problem as follows:
1) In the IDE, set the "Custom Insert" box to "Select 1;", that will be a dummy sql that won't do much, but it is ok, since you will handle what needs to get done in the events.
2) In the events section, choose the event that best fit your needs and call your store procedure from there. It doesn't have to be the On Validate, I put my store procedures on the Before Insert.
3) Call your store procedure just like any other query: $db->query("SELECT <your store procedure call here>");
4) Load a php variable with the result, this is an example of how I load a function return into a PHP variable:
$next_record_id_ = "SELECT function_nextvalue() AS next_record_id";
$next_record_id = $db->query($next_record_id_);
$db->next_record();
$record_id = $db->f(next_record_id);
This is a function call, not a store procedure, but I am pretty sure you can use store procedures too if they are returning values.
5) User the return value stored in your PHP variable to determine what to do next. The way I handle errors is setting errors in the URL as parameters, calling the page where I want to display them, in that page insert Labels that will trigger if error parameters exist on the URL:
To return to a given page in case of error (example):
if($last_category_id == '100'){ // 100 is an error code resulting from a function call
$redirect_str = "category_maint.php?Category_Id=" . $last_category_id . "&error=<error code or message here>";
header('Location: ' . $redirect_str);
die();
}
In category_maint.php Before Show event, I check the URL to see if there are errors set:
$Category->ErrorsLabel->SetValue(CCGetFromGet("error",""));
ErrorsLabel is a Label that has no content, except when there is an error in the "Get" part of the current's page URL.
Hope that helps.
|
|
|
|
rado
Posts: 221
|
| Posted: 06/22/2009, 11:58 AM |
|
Thanks a lot Cabrera.
Rado
|
|
|
|
|
