moving NULL value to SQL table
in [Cobol]
|
Prev: Having trouble declaring/fetching in Pro*Cobol
Next: solutions manual for Fundamentals of Corporate Finance 9th edition by Ross
From: Rene_Surop on 16 Mar 2010 00:33 Hi, Didn't realize this before. Well, not until I'm going to use it :) Reading NULL value in Cobol is easy for it signifies a 'negative' value to it. Moving NULL datestamp fields from SQL to my Cobol working fields with a space PIC X(29) on a NULL date format. So I have a space Cobol alphanumeric value.... did some processing with the Cobol data fields and blah-blah-blah. Now I need to return back the values into the SQL table... and UPDATE the record. Question; how could I update it if the Cobol date field is SPACES?? How to set a value NULL into a SQL table datestamp? Rene
From: Anonymous on 16 Mar 2010 07:40 In article <7131080d-3480-4518-9bbe-cdbb8223ed37(a)x1g2000prb.googlegroups.com>, Rene_Surop <infodynamics_ph(a)yahoo.com> wrote: [snip] >Now I need to return back the values into the SQL table... and UPDATE >the record. > >Question; how could I update it if the Cobol date field is SPACES?? >How to set a value NULL into a SQL table datestamp? If there is something like a :null-indicator-fldnam defined after the :host-fldnam then moving -1 to it before the UPDATE might be beneficial. DD
From: Pete Dashwood on 16 Mar 2010 10:25 docdwarf(a)panix.com wrote: > In article > <7131080d-3480-4518-9bbe-cdbb8223ed37(a)x1g2000prb.googlegroups.com>, > Rene_Surop <infodynamics_ph(a)yahoo.com> wrote: > > [snip] > >> Now I need to return back the values into the SQL table... and UPDATE >> the record. >> >> Question; how could I update it if the Cobol date field is SPACES?? >> How to set a value NULL into a SQL table datestamp? > > If there is something like a :null-indicator-fldnam defined after the >> host-fldnam then moving -1 to it before the UPDATE might be >> beneficial. Doc is right that indicator variables are provided for this purpose, but using them is tedious and there are many ways to avoid using them. Here's an example using SQL Server: EXEC SQL IF ISDATE(:MyDateHV) = 1 UPDATE mytable SET myDate = :MyDateHV <may put other SET statements here...> WHERE... etc ELSE UPDATE mytable SET myDate = NULL <may put other SET statements here...> WHERE... etc END-EXEC .... or you could apply the other SET statements here with a separate update... An advantage of this approach is that it works for invalid dates as well as spaces. If an invalid date is passed it will be set on the database as NULL. My immediate thought was to use COALESCE and I got quite excited until I realised that you can't give COALESCE a NULL operand because it will just ignore it :-) HTH, Pete. -- "I used to write COBOL...now I can do anything."
From: Rene_Surop on 16 Mar 2010 22:36 Doc/Pete, Several datestamp fields are in a single table so it's kind of annoying looking at them in NULL value. Converting these NULL datestamp value into a 'new' table (using Cobol program conversion) is a very long process and needs to be re-checked everytime, or else the INSERT command into the new table will fail. Thanks for all the reply. Jimmy; got the small code you emailed. Thanks.
From: Pete Dashwood on 17 Mar 2010 00:14
Rene_Surop wrote: > Doc/Pete, > > Several datestamp fields are in a single table so it's kind of > annoying looking at them in NULL value. Some RDBMS only allow ONE Date/TimeStamp column per row. (It's because some systems update this field automatically and there is no point in having more than one of them. Different systems implement the content of this field differently, too; it can be a sequential number or it can be a real time.) About 6 years back when I first started looking at Migration of COBOL I realised that COBOL doesn't implement a DATETIME data type and this could be problematic. I discussed it with the potential clients and asked them what would be better than NULLs in a date field, to indicate it was not in use. This discussion went on for some weeks with some very good points being considered. We all agreed that the RDB required a date and we should ensure it got one (NOBODY wanted NULLs for very much the same reason you outlined above), but the question was: WHAT date? If you use a future date it is risky. If you use January 1st 1900 there is a risk that some applications will convert it to 2000, and so on. In the end, we decided on 08/08/1988, although not everyone was happy with this, as some had records going back that far. At least if it is consistent, it is pretty easy to change it to some other base for a given installation, or even to make it null, if somebody really wants that. Today the Toolset has date detection tools built into it and it uses heuristics to decide if a COBOL definition is actually a date. It generates DDL with dates supported as DATETIME types. The DAL objects contain date filters that ensure that only valid dates can arrive on the DB and they are converted back to date strings for the COBOL Host Variables in ESQL. The general rules have evolved to be: 1. USE date types on the Database. (I tried not doing this for a while, figuring it works for COBOL, but I seriously regretted it. The richness of SQL date functions is not to be sniffed at ... have a look at this: http://www.sqlusa.com/bestpractices/datetimeconversion/ ) Doing this in COBOL is just pointless; it is like re-inventing a cart wheel only to find that there are already mag radials available... Given that dates and the handling of them are a large part of most information systems, it is only to be expected that the World has met the need. I spent a lot of time over decades writing date routines that today are really unnecessary; nowadays I simply use what's there. There is huge support for everything, including automatic conversion to other languages. 2. Ensure your database date fields have valid dates in them, rather than NULLs. (This one is very arguable, some people feel strongly in favour of NULLs) 3. DECIDE on a date that will be used to indicate "not in use". Pete. -- "I used to write COBOL...now I can do anything." |