Save ZERO values as null for selected fields

sql
Tags: #<Tag:0x00007f4f147950f8>

#1

I have to save NULL values into sql database instead zero for some columns. I know there is SETNULL function in clarion, but it should be used before every insert/update in code.
Is any method to set this once i.e. in dct as field option (or sth whatever) for all app using this dct ?


#2

just use prop:sql for generate pure SQL INSERT/UPDATE statement like

table{prop:sql} = 'INSERT INTO [dbo].[mytabe] ([APPLID],[USERID],[RESOURCEID],[DEFAULTPROP],[CompanyID]) VALUES (26, 2, 2534,NULL,18)'


#3

Where ? In any UpdateForm in application i have to change insert/update into sql command ?
in application where i have about 800 tables and almost the same processing procedures for bulk updates. I looking how to do this in easy way.


#4

You could call SETNULL in dictionary triggers.


#5

then try what Mike said


#6

In PostgreSQL, I handle this using a before insert/update trigger and NULLIF.
Much preferred over putting the code in Clarion.


#7

Also there is column level switch READONLY:

| READONLY
Adding the READONLY switch to the External Name tells the driver not to insert the field when the record is added OR updated. This is necessary for certain back ends that do not allow auto incrementing key fields to be set to null.