differentiating the data types of date or time from an entity imported from DB2

differentiating the data types of date or time from an entity imported from DB2

  
I imported a DB2 table via extension for read/write purposes in my outsystems application. Currently the app also uses another extension called "RecordIntrospection.xif" which has an function GetAttributeValues() to enumerate the property type of each entity attrubute. However, no matter the attribute is a date or time or datetime, the function always returns datetime. It is very difficult for me to determine the proper type so that i can write the null date, null time back to DB2 table, e.g. "null date" in DB2 is 0001-01-01 which is different from SQL null date "1900-01-01". The RecordIntrospection.xif uses .net framework system.reflection namespaces to parse the property info where system.datetime is the only one valid in terms of types of date/time/datetime. Besides hardcoding each date/time field in my logic, is there another better way to get the valid property type of attribute? Thanks so much for the answer.
Let me lend another explanation for this problem.  We have written several help routines for our integration to the AS400 DB2/400 database that is supposed to relieve us of maticulously writing heavy and cumbersome SQL INSERT and SELECT statements to try and preserve some of the productivity that we should be getting with Outsystems.  The reason for this is because Outsystems does not integrate directly with the AS400 DB2/400 database.  You must use OPENQUERY in order to use it.  Obviously that means Advanced Queries for the simplest to complex queries.  We wanted to preserve the inherent advantage of Outsystems model driven architecture so that when we change tables, we would just change the structure in Outsystems and we wouldn't have to touch every single SQL statement.  

So we created several methods that would use .Net RecordInstrospection or reflection to give us the name and attribute of every column in the structure passed.  With this information, we could build a string of column names or values to build a SQL string for SELECT or INSERT statements.  Fine in Theory.  But, there are subtle differences between MS SQL and DB2/400 that you have to take care of- especially when you are reading data such as a Date or DateTime and writing it to a DB2/400 record.  For example, a null date in SQL is 01-01-1753, a null date in Outsystems is 01-01-1900.  A null date in DB2/400 is 01-01-0001.  We could easily build these types of conversions in our helper methods IF we can accurately determine if the attribute in the Outsystems Structure was a Date, DateTime, or Time.  But what the .Net reflection gives us in always DateTime- no matter if it's a Date, DateTime, or Time.  It always gives us a DateTime.  So we end up having to hard code these SQL Statements and loosing the productivity we expected to gain using Outsystems.  Besides that, our model is a mixture of hard-coded SQL statements and others using these streamlined helper methods.  
What we need is the ability to pass an Outsystems structure to a method and having it return a value pair list of Name, Attribute which includes whether its a Date, Time, or DateTime.  

Help!
Hi Caleb,

Unfortunatly like you are experiencing there is no information in the generated code that can differentiate those 3 types. They are treated like DateTimes in all 3 cases once the code is generated, so reflection does not help.

Do you have many columns with just Date and Time (instead of full DateTimes)?
If they were not many you could have a whitelist approach where you could hardcode the excptions.

Another idea whould be to have an extra attribute in your structures that would be ignored by your methods and could contain extra information about your structure.
For example:


Then your reflection could use what is written on your special attribute to have more information when necessary, and you would also need to ignore it to not be included in the queries.


Regards,
João Rosado