How to set empty date?

Databases
1

Hi All,

I am using Firebird as database backend. I am not able to set a Field which is DATE or TIMESTAMP to null:

dim rs as recordset
dim d as new date

rs = app.GetRecord("Host", "NewDevelopmentDetail", 8, "", 1)   //my self defined function used to get record
if rs <> nil then
  rs.edit
  rs.Field("ExpiryDate").value = nil
  rs.update
  msgbox "done"
end if

The record can only be updated if I set the field to a valid date, i.e. : rs.Field(“ExpiryDate”).value = d

Any ideas?

2

Although the code above was presented as ‘the solution’ in this post, there are useful other things to read in it, such as ‘is the field defined as NOT NULL’ in the schema’

https://forum.xojo.com/41034-saving-an-empty-date

3

Oh I have forgotten I have raised this question before. No, I didn’t set NOT NULL in the schema.

4

Last time you asked about MySQL

5

rs.Field("ExpiryDate").value = NULL ?

6

Yes, but this time is Firebird

7

NULL seems is not valid in Xojo?

8 
UPDATE mytable SET mydatefield=NULL WHERE mytable_unique_integer_pk_rowid_field=nnnn

avoid using rs.edit and rs.update, there are far more efficient commands in direct sql.

9

@Jean-Yves Pochez But I have setup a standard method that accepts a window or container as a parameter, then set all field values of a record according to corresponding fields automatically, then update the table. If I need to change to direct sql, I have to re-write that method again that causes a lot of work to do. Anything that can get around?

10

Could you show us the definition for that table and it’s columns?

11

It would also be helpful to see what your GetRecord method contains.

12


https://ibb.co/i8bPpd

app.GetRecord:

dim LocDB as new HostDatabase

LocDB.DataSource="DRIVER=Firebird/Interbase(R) driver;" +_
"JDBCDRIVER=IscDbc;QuotedIdentifier=Y;READONLY=N;NOWAIT=N;" +_
"DBNAME=" + app.DatabaseName + ";" +_
"CharacterSet=UTF8;" +_
"DIALECT=3;" +_
"UID=" + app.DatabaseLogin + ";" +_
"PWD=" + app.DatabasePassword + ";"

if Not locDB.Connect Then
  MsgBox("app.GetRecord Error (1): " + ModuleName + "_" + str(CheckPoint) + chr(13) + chr(10) + LocDB.ErrorMessage + "(" + str(LocDB.Error) + ")")
else
  dim rs as RecordSet
  rs = LocDB.SQLSelect("SELECT * FROM " + trim(TableName) + "  WHERE ID=" + str(GUID))
  if rs = nil then
    MsgBox("app.GetRecord Error (2): " + ModuleName + "_" + str(CheckPoint) + chr(13) + chr(10) + LocDB.ErrorMessage + "(" + str(LocDB.Error) + ")")
  end if
  
  return rs
end if
13

You ought to check locDB.errorMessage just after calling the update method. It might tell you what’s wrong.

14

Checked recordset content correct, no error posted

15

leave the other fields like they are, update your table with rs.update
juste after, apply an update query like I mentionned to clear the date, with the PK of the record.

16

My method updates the entire record with corresponding fields on the window/container, I can’t skip 1 or 2 fields, I think this is not the way I designed the method…

17

Apparently there is a setNull() function in JDBC drivers. I don’t know if it’s supported in Xojo though (I’ve never used JDBC). More info here:

https://stackoverflow.com/questions/36136961/how-to-set-null-in-prepared-statement-if-the-value-is-zero

I use a similar algorithm for MySQL and MSSQL, but I’m using the MBS SQL plugin and it provides setAsNull.

18

Get an eye here:

https://www.sqlite.org/nulls.html

I do not read it, but it seems to have a pretty complete information.

19

[quote=392145:@Emile Schwarz]Get an eye here:

https://www.sqlite.org/nulls.html

I do not read it, but it seems to have a pretty complete information.[/quote]
SQLite handles NULL vastly differently than other database engines, so I would not assume that if it works (or doesn’t work in SQLite) that the same applies elsewhere

20

I have setup a simple project, when I tried to set rs.Field(“Date”).value = nil and preform a rs.update, it returns an error: -303 conversion error from string “”

Does this mean there is a bug in Xojo ODBC plugin?

https://drive.google.com/open?id=1xkU7RCccfN4tFXTUgAejLp2XAQuBy8bC