Input a Null or blank value in an integer-type table field

[greb]

Hello,

I have an Insert Into SQL command in a macro which inputs new record in a table, using values (some fields are string-type, some are integer-type) coming a dialog. Basically, I enter new records, and some of them can be referring to previous records, and some other not. This is done by entering the ID (int) of the previous record the new one is reffering.

So for the field indicating the linkage to a previous record-ID, sometimes, there is no value (it’s normal). The problem is that when there is an expected value, it is an integer value. Thus my variable in the macro is an integer one (as well as the targeted field in the database table), and when dealing with ‘no-value’ case, the variable takes the value “0”.

I’ve been trying to modify the type of my variable in the macro, from integer to string. So when no value is expected: variable = ""
But then, when executing the SQL insert into command, I get an error conversion error from string. (in a case where a value is expected, there is apparently no conversion error from the string during the SQL command).

Is there a way to get a blank/empty value, instead of a “0”? I guess that modify my table field type from integer to string may be useful, but I think I will need to keep it in integer-type.

For information, I am using Base with a Firebird embedded database.

Thanks for any suggestion!

Benjamin

[EDIT]

According to the comments of @Ratslinger it may be a problem of table/field declaration. Here is attached a test file where I created a table in the same way as I described above. (For the moment, there is no macro included in it. I’ll add one later if it reveals that my problem is not due to the table/field declaration).
As an additional precision, I use SMALLINT field instead of INT field.
And LibreOffice version is 6.3.0.4.

It seems that entering any value in this field yields in an integer value: having tried entering NULL, “NULL”, null, “null”, eee, all provide 0.
However, I feel that actually, a blank correspond to a NULL value. Indeed I’ve tested the following SQL command:

select "link_previous_case" from "Case" where "link_previous_case" is null

resulting in a series of records with no value. And

select "link_previous_case" from "Case" where "link_previous_case" is not null

provides the records where there is a linked previous case.

[Ratslinger]

Hello,

You just need to set the variable as NULL:

sVariable = "NULL"

Example macro code:

sVariable = "NULL"
oStatement.execute("INSERT INTO ""TestNull"" VALUES ( 3, " & sVariable & ", 'Hello3');")

[greb]

Thanks a lot @Rastlinger for your suggestion.

I tried, it works, no error, and the record is saved, but still with a ‘0’ value in the table, and not a ‘NULL’…

But perhaps what I request is something not possible…?

Meanwhile, to avoid ‘0’ values, I coded to get a ‘-1’ value, to avoid any confusion in further database queries. I guess it is not the best, but at least I am sure that a ‘-1’ value will never refer to a previous record ID as these one are automatically computed by Base-Firebird and so ≥ 0.

[Ratslinger]

@greb,

My testing shows this will work and actually place a NULL in the field - not 0. Your situation may be because of the method you have established in you table. It may be due to a required field or you may also have a default value set for the field. If you continue to have this problem you can post a sample (scrubbed) so it can be examined.

[greb]

@Ratslinger,

Thanks again for your help. Indeed, it may be coming from the table or the field declaration (while I couldn’t understand why up to now, because the field is declared as no required value, and no default value is asked for.

I attached in the first post an edit, with an test file, showing a table declared in the same way as in my working file. Maybe from that is it possible to identify a problem in the declaration?

Thanks in advance!

[greb]

@Ratslinger,

I marked your suggestion as the correct one, thanks a lot!

Just add that it my case, it worked only by combining with a specific declaration of the BASIC sVariable as VARIANT (and not INTEGER or STRING):

DIM sVariable AS VARIANT
sVariable = "NULL"
oStatement.execute("INSERT INTO ""TestNull"" VALUES ( 3, " & sVariable & ", 'Hello3');")

[Ratslinger]

@greb,

Sorry, but I have a bad habit of not including DIM statements when testing or answering questions. Without a DIM statement the variable is noted as VARIANT.

Will continue to try to include this in future.

I am glad you did get all working now.

[greb]

@Ratslinger,

My fault as well, I didn’t specify in my first post that I was attributing a DIM statement as VARIANT in my macro.

And thanks for this comment: I learnt that without a DIM statement, VARIANT is default. Good to know!