Accessing Oracle through an extension

Accessing Oracle through an extension

  
Accessing Oracle through an extension
I'm trying to access an old table in Oracle through a referenced entity.

I've linked the table through the Integration Studio and can access and update it through my eSpace but am running into a few unsettling issues.

The Oracle data type char does not get mapped to a true type.  I had to manually set them to text.  This wouldn't be a big deal except for the fact that if I need to modify the table in Oracle it appears that Integration studio will overwrite all of my changes.

I'm aware that the Agile Platform does not allow null values for the database.  According to the documentation a text field should be mapped to the empty string (which Oracle considers NULL).  In my tests I'm getting a single space rather than a blank string.

I can't figure out how to input dates.  If I set the field on the Edit Record objet to be date I cannot type my date in the format that oracle wants (dd-mmm-yyyy) because AP considers is an invalid date.  If I set the field as a valid date for AP, Oracle gives me an error.

Any thoughts on these issues?
Tom Hubbard
Hello Tom, thank you for your questions!

The Oracle data type char does not get mapped to a true type.  I had to manually set them to text.  This wouldn't be a big deal except for the fact that if I need to modify the table in Oracle it appears that Integration studio will overwrite all of my changes.

In fact the Integration Studio does not map the ORACLE CHAR data type directly to Text. That issue is identified and I have already submitted it to our Product Support team. When you refresh an External Entity, Integration Studio introspects the database again, and given the CHAR problem that is already identified, each CHAR columns will be mapped as a Entity Field without data type - that's why Integration Studio removes your changes. The workaround is the one you described - setting the data type of those fields as Text manually (after importing or refreshing the External Entity).

I'm aware that the Agile Platform does not allow null values for the database.  According to the documentation a text field should be mapped to the empty string (which Oracle considers NULL).  In my tests I'm getting a single space rather than a blank string.

As you said, ORACLE recognizes and empty string as NULL. Because of that, the OutSystems platform represents an OutSystems empty string in the ORACLE database as a 1 space string (otherwise, you would have a NULL).

I can't figure out how to input dates.  If I set the field on the Edit Record objet to be date I cannot type my date in the format that oracle wants (dd-mmm-yyyy) because AP considers is an invalid date.  If I set the field as a valid date for AP, Oracle gives me an error.

Tom, do you confirm that the Entity Field in which you are writing the date is of Date type (in the OutSystems Entity Model)?
Which is the Date Format that you have configured in your Service Center? (see below)



Regards,

Daniel Lourenço
OutSystems

Daniel,

Thanks for the response.

I was able to deal with the single space issue by setting the conver to/from null values in Integration Studio.

I was also able to deal with the dates in the same way you pointed out.

As for the CHAR to Text conversion in Oracle, it looks like the Oracle Date and Float datatypes do not get mapped either.  I'm not sure if there are more.

Tom
Hi Tom,

Thank you for the additional information. I'm glad you were able to fix you previous errors.

You can see the data types that are mapped automatically by the OutSystems Agile Platform in the OutSystems Integration Studio help section  OutSystems and Database Data Types.

When you say that the FLOAT and DATE ORACLE types are not mapped, do you mean they were not automatically mapped in Integration Studio? Or do you mean that after mapping them manually (to the OutSystems Decimal and Date data types) it is not working in runtime?

Regards,

Daniel Lourenço
OutSystems
Daniel,


Sorry for the confusion.

I ment to say that the FLOAT and DATE datatype are not getting mapped in Integration Studio in the same way that the CHAR datatype is not getting mapped to TEXT.

Tom
I have the exact same issue, also columns defined as 'NUMBER' with no precision or scale specified do not get mapped. Defaults for fields if defined on the Oracle table also do not get 'wizarded' across. All of this can be fixed manually but still, it takes a lot of time to do so.

Cheers,
Hans