What is the proper way to save a Boolean true/false from an angular form checkbox and have it saved to a backend database like Oracle?

Question

For instance using Angular Material's and using [(ngModel)]="isActive" name="isActive"

this will submit the form with "isActive" = true

What is the proper way in the database to save this? I know in Oracle there is not really a boolean datatype....

Should it be saved as a Char datatype? but if that is so then its expecting 'T' or 'F' not true or false.

should it be saved as a varchar? so it can except the true and false?

Thanks for any information or proper data structure on this.

https://stackoverflow.com/questions/3726758/is-there-any-boolean-type-in-oracle-databases — Reactgular, Aug 30 '19 at 15:30
I @Reactgular understand that there are ways to save it in an Oracle Database as a boolean but how should that data be passed from an Angular Form or any form that has something like a checkbox — Rob DePietro, Aug 30 '19 at 15:34
*boolean* is a valid type in JavaScript. It is a valid type in *JSON*. If you could have a conversation over a cup of coffee with JavaScript. It would explain that it has no care of the problems the back-end has with *booleans*, and if you asked Oracle to join you in that conversation (it would drink Tea because it's that kind of different). Oracle would say "I have no care about booleans or this other guy named JavaScript". So you would all agree to disagree and since you're the programmer, then you would just have to fake it to make it work. — Reactgular, Aug 30 '19 at 15:45

DanGo · Answer 1 · 2019-08-30T16:26:13.623

1

In Angular I would keep it as Boolean when it is sent to the backend I would convert it to a char of either 1 or 0 In oracle I would set the column to:

col CHAR(1),
CONSTRAINT cons_atable_col1 CHECK (col1 IN ('1','0'))

So that it will only accept 0 or 1.

Let me know if there is more detail or clarification needed.

edited Aug 30 '19 at 16:26

answered Aug 30 '19 at 15:53

DanGo

391
1
13

score 1 · Accepted Answer · answered Aug 30 '19 at 17:21

How you store your Boolean values depends very much on the semantics of the field in which the value is stored. It's sometimes reasonable to store Boolean values as character fields containing "T"/"F", "Y"/"N", "IN"/"OUT", "BLACK"/"WHITE", "PINK"/"BLUE", or whatever suits your data. Sometimes you may want to store that Boolean as a NUMBER in Oracle, with allowable values of 1 and 0, or 100 / 0, or 1 / -1, or whatever suits your needs. We often use Booleans to mean any number of different things - which is why Oracle doesn't supply a Boolean type. You should use a data type and values which are appropriate for your application.

As far as the CHAR vs. VARCHAR2 argument I recommend using VARCHAR2 because CHAR doesn't always behave as you (or at least "I" :-) expect. By way of explanation, consider the following block:

DECLARE 
  PROCEDURE test_string(pinString IN VARCHAR2) IS
    strTest  VARCHAR2(3) := pinString;
    cTest    CHAR(3)     := pinString;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('strTest=' || CASE
                                         WHEN strTest IS NULL THEN 'NULL'
                                         ELSE '''' || strTest || ''''
                                       END ||
                         '  cTest=' || CASE
                                         WHEN cTest IS NULL THEN 'NULL'
                                         ELSE '''' || cTest || ''''
                                       END);
  END test_string;

BEGIN
  test_string('A');
  test_string('AB');
  test_string('ABC');
  test_string('');
  test_string(NULL);
END;

When run this outputs:

strTest='A'  cTest='A  '
strTest='AB'  cTest='AB '
strTest='ABC'  cTest='ABC'
strTest=NULL  cTest='   '
strTest=NULL  cTest=NULL

The first three lines and the last line aren't a surprise, but the fourth line is a bit surprising to me. I've always understood that an empty string is equivalent to NULL. However, that fourth and fifth lines of output seem to be contrary to that assumption. In the fourth line we've assigned a zero-length string to the VARCHAR2 input parameter, which was subsequently assigned to both strTest and cTest. In the case of strTest when we test it in the VARCHAR2 test code it's detected as NULL. However, when this zero-length string is assigned to cTest, cTest is padded on the right with spaces until it's a full 3 characters long. This is not surprising when considered in the context of the CHAR data type, but it produces a result I don't expect based on my use of VARCHAR2. It's also interesting because it implies that A) a zero-length VARCHAR2 value is actually stored as a zero-length string and not as a NULL, and B) the "zero-length-string-is-NULL" determination is done at runtime.

What is the proper way to save a Boolean true/false from an angular form checkbox and have it saved to a backend database like Oracle?

2 Answers2