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!
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 188.8.131.52.
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.