[Bulk] Is anyone using "DEFAULT DATE IS NULL" in a database statement?

[Ken Langendock]

I had a similar problem when implementing foreign key constraints in an
Oracle database.

You do not have to use QTP to get the database set up correctly.

You can do it all within SQL and I believe you don't even have to compile
unless you do not have the PDL set up correctly.

Powerhouse plays well with the following setup.

Hope this helps.

 

; In your dictionary:

 

System Options &

   Century Included &

   Default Century 20 &

   Date Separator "/" &

   Date Format MMDDYYYY &

   Input Century 19 from Year 50 &

   Release 0   Version  0   Show &

 

   Special Name Characters "-" & ; characters allowed in field/file names

   Decimal "." &

   Generic Retrieval Character "@" &

   Multiline Heading Character "^" &

   Picture Substitution Character "^" &

   Message Substitution Character "^" &

   Null Value Character " " &

@if ORACLE

   Shift Upshift &

@else

   Shift Noshift &

@endif

   Default Transaction Model Concurrency &

   Default Entry and Find In Consistency &

                   Select In Concurrency &

 

; In your SQL for each table:

Create table Users

      (

       UserId Char(8) Default ' ' Not Null,

       UserName Char(40) Default ' ' Not Null,

       ActiveRecord Char(1) Default ' ' Not Null,

       LanguageId Char(2),

       UserLocationId Char(8),

       EmailAddress Char(60) Default ' ' Not Null

      );

 

 

; The following lets the database set the null if a blank is passed in.
Therefore no coding changes are required in PH

CREATE OR REPLACE TRIGGER br_Users

  BEFORE INSERT OR UPDATE ON Users

  REFERENCING NEW AS NEW OLD AS OLD

  FOR EACH ROW DECLARE

  BEGIN

    :NEW.LanguageId := NULLIF(:NEW.LanguageId,' ');

    :NEW.UserLocationId := NULLIF(:NEW.UserLocationId,' ');

  END;

/

 

; Foreign Key Constraints will fail if the value is not a NULL and does not
exist on the master file.

Alter table Users

      Add Constraint fk_UsersLanguageId

          Foreign Key

         (

          LanguageId

         )

          References Languages

         (

          LanguageId

         ) 

          On Delete Set Null;

 

; To get your database correct use the following example in SQL.

ALTER TABLE Users MODIFY (LanguageId Char(2));

Update Users SET LanguageId = NULLIF(LanguageId,' ');

- add the trigger here

 

 

Ken

 

 

From: powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com] On
Behalf Of Stephen Hulbert
Sent: November 9, 2010 6:37 AM
To: powerh-l at lists.sowder.com
Subject: [Bulk] Is anyone using "DEFAULT DATE IS NULL" in a database
statement?

 

Hi listers,

Ten years ago, when we moved over to an Oracle back end from CISAM, we made
the decision to keep using zero for dates with no value.

This is fine if you only want to use Powerhouse to access the data, but
anything else (including Oracle) complains loudly and at length about
invalid dates.

Talking to Cognos, there's a "DEFAULT DATE IS NULL" option on the database
statement which entirely transparently translates NULL dates in the database
into zero value dates for consumption by Powerhouse.

Does it work? Does it work transparently?

Does anyone have experience of using it? I guess if you're using a SQL
Server back end or an ODBC connection you have no choice.

More importantly, does anyone have any experience of turning this option on
for a very mature database?

The upgrade path (make columns nullable, use QTP to set them to null, turn
option on, recompile) would seem to be fairly simple, but are there any
gotchas?

Many thanks in advance,

Stephen.

 

 

--

Stephen Hulbert

Senior Analyst Programmer

For and on behalf of

Littlejohn LLP

Direct: 020 7516 2201

 

 

 





Littlejohn
1 Westferry Circus
Canary Wharf
London
E14 4HD

T: 020 7516 2200
F: 020 7516 2400
www.littlejohnllp.com

Littlejohn LLP, Chartered Accountants

The information contained in this communication is confidential and may be
legally privileged. It is intended solely for the use of the individual or
entity to whom it is addressed and others authorised to receive it. If you
are not the intended recipient you are hereby notified that any disclosure,
copying, distribution or taking of any action in reliance on the contents of
this information is strictly prohibited and may be unlawful.
 
Littlejohn LLP reserves the right to monitor the content of any
message sent to or from littlejohnllp.com.

Littlejohn LLP is registered as a limited liability partnership in England
and Wales No. OC342572.  
Registered office as above.
 
Any reference to a partner is to a member of the limited liability
partnership. 
A list of members is available at the registered office.

Littlejohn Advisors LLP is a limited liability partnership registered in 
England and Wales No. OC331599. Registered office as above.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20101109/f96851c3/attachment-0001.htm