Bookings Assignment - Get Available Room SQL Error

Bookings Assignment - Get Available Room SQL Error

  

Hi,

I just used Outsystems for just several days and now I'm working on the Bookings Assignment

Upon creating the SQL, I receive the following error upon testing : 

And upon publishing, I got the following error upon clicking "Get Available Rooms". I believe this is related to the SQL error above : 

FYI, the code for the XY Exception for the room price is the following : 

BookingForm.Record.Room.RoomNumber + " at " + BookingForm.Record.Room.Price + "$ per night" 

Anyone can help to explain? I'm very clueless at this poin


Thanks

Hi Billy

The error is happening because one of the Query Parameters is with the wrong data type.

Check that the two Check... query parameters have the Date data type, NumberOfAdults and NumberOfChildren are of Integer data type and CanceledStatus is of Status Identifier data type.

This should solve the problem.

Cheers.

EDIT: See the answer bellow...

Hum...

Looking better to your image I notice something that is more probably the cause of the error.

Also, instead of using a structure as the Output, just use the Room entity, as this is what the Select is retrieving.

Your structure probably does not have all the Room fields and / or they are not in the correct order, than the error.

Cheers.

Billy Suherman wrote:

Hi,

I just used Outsystems for just several days and now I'm working on the Bookings Assignment

Upon creating the SQL, I receive the following error upon testing : 

And upon publishing, I got the following error upon clicking "Get Available Rooms". I believe this is related to the SQL error above : 

FYI, the code for the XY Exception for the room price is the following : 

BookingForm.Record.Room.RoomNumber + " at " + BookingForm.Record.Room.Price + "$ per night" 

Anyone can help to explain? I'm very clueless at this poin


Thanks

Hi Billy,

I think your GetRoom Query OUTPUT Structure is not  correct sequence that’s why giving conversion error.

Please check carefully query output and structure output datatype.

Hope this helps !!! 

Best Regards,

Amit Verma

Eduardo Jauch wrote:

Hum...

Looking better to your image I notice something that is more probably the cause of the error.

Also, instead of using a structure as the Output, just use the Room entity, as this is what the Select is retrieving.

Your structure probably does not have all the Room fields and / or they are not in the correct order, than the error.

Cheers.

Thanks for the reply

Could you explain the details? I'm still kinda clueless about this part

Of course.

Basically, the fields selected by the SELECT clause in your SQL statement (Room.*) must match the records you have in yout OutputStructure.

So, as you are fetching all columns of the entity Room, and they are in a certain order in the Entity (First, Id, than RoomNumber, than... and so on), you need to have an output structure that have those fields in the same order.

As you are fetching ALL attributes of Room  (because of the '*' you're using), the easiest is, instead of creating and using a structure, is to use as output the Room entity itself (just drag the entity from the Data layer to the Output folder of the SQL, and remove the other structure.

The error is just because your structure probably does not have all the fields of the Room, and so, the sql is trying to put, for example, a date in an attribute of type integer.

cheers.

Eduardo Jauch wrote:

Of course.

Basically, the fields selected by the SELECT clause in your SQL statement (Room.*) must match the records you have in yout OutputStructure.

So, as you are fetching all columns of the entity Room, and they are in a certain order in the Entity (First, Id, than RoomNumber, than... and so on), you need to have an output structure that have those fields in the same order.

As you are fetching ALL attributes of Room  (because of the '*' you're using), the easiest is, instead of creating and using a structure, is to use as output the Room entity itself (just drag the entity from the Data layer to the Output folder of the SQL, and remove the other structure.

The error is just because your structure probably does not have all the fields of the Room, and so, the sql is trying to put, for example, a date in an attribute of type integer.

cheers.

The SQL worked like a charm as your suggestion! Either I missed that part on the tutorial or else...

Now the only issue left is the text for available room and price per night

Any ideas?


Thanks for your responses

Solution

So,

You hhave this in the XY Expression:

BookingForm.Record.Room.RoomNumber + " at " + BookingForm.Record.Room.Price + "$ per night" 

This means that when the page is build (or refreshed), the expression is executed and it will show, for example, "201 at 44.00§ per night".

The "201" is comming from the Attribute RoomNumber, of the type Room of the Record of the BookingForm, while the price is comming from the attribute Price of the same type Room in the Record of the BookingForm.

This is happening because the Form.Record has the same type as its Source, in this case, an aggregate in the preparation (where you are joining the Booking and the Room entities).

This makes sense, as if you are entering this screen to see an existing booking, you wanna know the NUMBER and PRICE of the booked room. This room id is the one stored in the Booking.RoomId.

So, when you're asking for a room (GetAvailable... action), you want to show the NUMBER and PRICE in the same expression. All you need to do is to store, in the BookingForm.Record.Room.RoomNumber and BookingForm.Record.Room.Price, the values returned by the SQL. 

If your button is defined with method SUBMIT, the page will be reconstruct and the expression will be re-evaluated, showing the values found.

Don't forget to store the Room.Id returned by the SQL into the BookingForm.Record.Booking.RoomId, so when you save this into the entity Booking, the chosen Room.Id is there.

Cheers.


Solution

Eduardo Jauch wrote:

So,

You hhave this in the XY Expression:

BookingForm.Record.Room.RoomNumber + " at " + BookingForm.Record.Room.Price + "$ per night" 

This means that when the page is build (or refreshed), the expression is executed and it will show, for example, "201 at 44.00§ per night".

The "201" is comming from the Attribute RoomNumber, of the type Room of the Record of the BookingForm, while the price is comming from the attribute Price of the same type Room in the Record of the BookingForm.

This is happening because the Form.Record has the same type as its Source, in this case, an aggregate in the preparation (where you are joining the Booking and the Room entities).

This makes sense, as if you are entering this screen to see an existing booking, you wanna know the NUMBER and PRICE of the booked room. This room id is the one stored in the Booking.RoomId.

So, when you're asking for a room (GetAvailable... action), you want to show the NUMBER and PRICE in the same expression. All you need to do is to store, in the BookingForm.Record.Room.RoomNumber and BookingForm.Record.Room.Price, the values returned by the SQL. 

If your button is defined with method SUBMIT, the page will be reconstruct and the expression will be re-evaluated, showing the values found.

Don't forget to store the Room.Id returned by the SQL into the BookingForm.Record.Booking.RoomId, so when you save this into the entity Booking, the chosen Room.Id is there.

Cheers.



Sorry for late reply! I was very busy with college and work

Thanks for your help! After some corrections and rework it finally works!

Hello Billy.

No problem.
It's nice to hear that you make it :)

Cheers