Android Question jRDC2 and DATE Column NULL while Insert data

[Anser]

Hi,

I am using jRDC2 to work with a Remote MySQL Database. I have a table containing a DATE type column named 'BirthDate'. I need to insert data into this table. The DEFAULT value of the DATE type Column 'BirthDate' is set as NULL

From the B4A app the Value of 'BirthDate' column can be either empty or any valid date.

The SQL statement for an insert with a valid BirthDate would be as follows

INSERT INTO MyTable ( User_ID,  BirthDate ) VALUES (6,  '2010-06-30' )

To insert a record when the 'BirthDate' is unspecified OR empty, the following SQL statement will be considered as Job.Success = False in jRDC2. Even in MySQL it will throw a warning

INSERT INTO MyTable ( User_ID,  BirthDate ) VALUES (6,  '' )

So the VALID SQL statement to insert a record with an empty date is as follows

INSERT INTO MyTable ( User_ID,  BirthDate ) VALUES (6,  DEFAULT )

The following SQL code is the Entry in Config.Properties file for the above

sql.AddUserDOB=INSERT INTO MyTable ( User_ID,  BirthDate ) VALUES (?, ? )

B4A Code would be

'cBirthDate is String Type

'Tried this code, but didn't worked
If cBirthDate = "" Then
    cBirthDate = "DEFAULT"
End If

'Tried this code also, but didn't worked
If cBirthDate = "" Then
    cBirthDate = Null
End If

'Tried this code also, but didn't worked
If cBirthDate = "" Then
    cBirthDate = "Null"
End If
   
'Write to DB
Dim cmd As DBCommand
cmd.Initialize
cmd.Name="AddUserDOB"
cmd.Parameters=Array As Object( Starter.nUserID, cBirthDate  )
Try
    reqManager.ExecuteCommand(cmd,"AddUserDOB")
Catch
    Msgbox("Could not establish connection with the server","Check Internet")
End Try

So my question is how do I handle this situation from B4A ?

I know that I can make a different approach to resolve this issue by having two different sql commands in config.properties file and calling the right one if the date is empty.
ie something as given below OR to use a Stored Procedure

sql.AddUserDOB_1=INSERT INTO MyTable ( User_ID,  BirthDate ) VALUES (?, ? )
sql.AddUserDOB_2=INSERT INTO MyTable ( User_ID,  BirthDate ) VALUES (?, DEFAULT )

I would like to know whether any other elegant way is already there to handle the above described situation, so that I don't have to keep two different SQL statements in my Config.Properties file. In my database, I have many different tables with DATE type column and where the DATE can be empty.

Any help will be appreciated.

Regards

Anser

 

[Erel]

It should be Null without quotes. However for this to work cBirthDate type should be Object.