Log in to AnswerLog in to Follow
UserImage.jpg
Lea Marie Magbalot
21Views
4Comments
SQL Output Error: Invalid cast from System.Int64
Question
SQL
Reactive
Application Type
Reactive
Platform Version
11.15.0 (Build 34858)

Hello,

I have a very simple query that searches for a given keyword in a specific field. When I use the TableName entity directly as my output entity everything works, but once I've used a structure (data type set as the TableName) it gets me the following error. 

'465' is an identifier of that TableName entity and it is also used in a one-to-one relationship. I don't seem to get why I'm getting this particular error when the structure's data type is set to the TableName entity. I need to use a Structure for manipulating the data after and don't want to mess up with the TableName entity. 


Thanks in advance!

0
0
20 Mar

Hello Lea Marie Magbalot,

Best practice to use Advanced SQL output as structure.

The structure should include only the attributes which you need to fetch from the entity, Selecting Tablename.* is not a best practice if you are not going to use all the fields.

In your case, I doubt mismatch in ID datatype, kindly recheck the datatype for your ID attribute in structure.

0
0
20 Mar

Hi Lea,

Agree with Muhammed that * is to be avoided, always, even if you are using all of the attributes.

It breaks, or even worse gives wrong results, as soon as the sequence of attributes gets changed.

For example, if you publish an initial definition of your entity, then change sequence of attributes and publish again, the sequence of columns in the underlying table doesn´ t change.

If you then later base a structure definition for an SQL widget on the entity definition, you get this type of problems, as it doesn´ t match the underlying table definition.

So in the SQL, explicitly name the attributes you want to select in a sequence matching your structure.

Dorine 


1
0
20 Mar
Thank you Muhammed and Dorine. It works perfectly when I just select the attributes I need, but what if I need all the attributes of that entity? Should I manually enter each attributes name in the SELECT statement?
1
0
20 Mar

Hello Leo,


As Dorine suggested it is best practice to manually specify the column name in advanced SQL, because if you had any changes in your entity after initial publish - like deleted attributes/changes in attribute name/sequence changes, etc. OutSystems will not delete an old attribute but create new ones, old attributes will be saved for history - means if you want to republish previous versions then the old structure will be available.

In such scenarios, it's better to specify attribute names explicitly.

If you use "*" for fetching all the details - there can be a chance of a mismatch in your entity structure.

1
0
21 Mar
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.