SQLite Forum

how to refuse write empty value
Login

how to refuse write empty value

(1) By lazna (plazna) on 2020-04-19 13:02:19 [link] [source]

utilizing sqlite inside of windows shell script, need to generate error if some variables are evalute to nothing (default behaviour if variable is not defined).

INSERT into tab (col1,col2,col3) values ('%val1%','%val2%','%val3%')

if variable %val2% is not defined, need the INSERT is rejected, but now it vrite record with col2 empty value. Have set-up NOT NULL constraint but it does not help. As I am just read that in sqlite NOT NULL is not same as NOT EMPTY, trying to add != '' constraint also but it does not help. What am I doing wrong?

(2) By Larry Brasfield (LarryBrasfield) on 2020-04-19 14:54:52 in reply to 1 [link] [source]

Without seeing the constraints you summarized, I cannot venture what went wrong with them. Done right, that may be the best solution.

Also, I'm obliged to mention: If you do not check those %val?% inputs carefully before using them in some DML, you have exposed yourself (or your client/employer) to SQL injection woes.

And if you are checking them, why not detect empty values there?

Did you know that "windows shell script" could refer either to command.exe input or Powershell input? If you used Powershell, complex code (such as checking inputs carefully) would be nowhere near as tedious to write (and debug.)

Now, getting to SQL and/or SQLite ...

If you have a working NOT NULL constraint, you could use, as the insert value, an expression which resolves to NULL for an empty string literal. You have not done that; instead you are inserting empty TEXT values (which are not NULLs.)

See the CASE ... WHEN ... syntax for how to write such expressions.

(5) By lazna (plazna) on 2020-04-19 20:41:46 in reply to 2 [link] [source]

Thanks for reply, trying this:

invoice VARCHAR CONSTRAINT "CHECK(invoice != '';)" NOT NULL,

or

invoice VARCHAR CONSTRAINT "CHECK(invoice <> 0)" NOT NULL,

but have no luck. There are too few examples on the web...

(8) By Igor Tandetnik (itandetnik) on 2020-04-19 22:02:48 in reply to 5 [link] [source]

CONSTRAINT "CHECK(invoice != '';)" NOT NULL is not a CHECK constraint. It's a NOT NULL constraint whose name is "CHECK(invoice != '';)".

You want

invoice VARCHAR CHECK(invoice != '')
-- or, if you want a named constraint
invoice VARCHAR CONSTRAINT 'SomeName' CHECK(invoice != '')

(11) By lazna (plazna) on 2020-04-20 09:54:47 in reply to 8 [link] [source]

Thats it, thanks a lot! Seems I still does not fully understand concept of conditions and constraints in sqlite. Have to read some tutorials later...

(3) By Keith Medcalf (kmedcalf) on 2020-04-19 17:48:27 in reply to 1 [link] [source]

IF (%val2%)==() goto HellFrozeOver
...

:HellFrozeOver
echo ERROR!  Hell Froze Over and val2 is empty

(4) By Larry Brasfield (LarryBrasfield) on 2020-04-19 18:42:59 in reply to 3 [link] [source]

IF (%val2%)==() goto HellFrozeOver ...

:HellFrozeOver echo ERROR! Hell Froze Over and val2 is empty

      REM (and K. Medcalf discovered teaching BATCH programming 101. ;-)

Can you imagine writing SQL injection defense so that cmd.exe can run it?

To the OP:

With some care, you could get recent versions of the SQLite shell to run parameterized queries and stuff in the values that made their way into and out of your batch script. See .help for the .parameter command.

(7) By lazna (plazna) on 2020-04-19 20:49:14 in reply to 4 [link] [source]

Thank you, for your answer, thats what I trying. Your reply is very valuable.

(6) By lazna (plazna) on 2020-04-19 20:42:31 in reply to 3 [link] [source]

Thans for reply. I am aware how to do it in shell, my question is how to do it in SQLite.

(9) By Keith Medcalf (kmedcalf) on 2020-04-20 01:22:09 in reply to 6 [source]

See the documentation: https://sqlite.org/syntax/column-constraint.html

(You start at https://sqlite.org/lang_createtable.html then fondle the column-def "show" button so you will be showed what a column definition looks like, and then from that you fondle the column-constraint "show" button to show you what a column constraint looks like.

So the syntax you want to create a column constraint according to that documentation would look something like the following:

create table tab
(
  col1,
  col2 check (col2 !=''),
  col3
);

insert into tab (col1, col2, col3) values ('','','');

Error: CHECK constraint failed: tab

(10) By Keith Medcalf (kmedcalf) on 2020-04-20 01:26:01 in reply to 6 [link] [source]

See the documentation: https://sqlite.org/syntax/column-constraint.html

(You start at https://sqlite.org/lang_createtable.html then fondle the column-def "show" button so you will be showed what a column definition looks like, and then from that you fondle the column-constraint "show" button to show you what a column constraint looks like.)

So the syntax you want to create a column constraint according to that documentation would look something like the following:

create table tab
(
  col1,
  col2 check (col2 !=''),
  col3
);

insert into tab (col1, col2, col3) values ('','','');

Error: CHECK constraint failed: tab