Real Software Forums
http://forums.realsoftware.com/

SQL - Update Statement - how to write empty Date value
http://forums.realsoftware.com/viewtopic.php?f=3&t=47923
Page 1 of 1

Author:  superjacent [ Sun May 19, 2013 11:09 pm ]
Post subject:  SQL - Update Statement - how to write empty Date value

How do I code/write an empty date value in an SQL Update statement? I'm using ODBC to connect to an Access database.

Writing a valid date value is not the problem, I'm merely enclosing in single quotes the SQLDate of a date object. This works.

I've found if I merely pass through two single quotes, effectively containing nothing, the database is spitting out an error message, not liking this approach.

Any hints or suggestions appreciated.

Author:  Thom McGrath [ Mon May 20, 2013 12:15 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Well, assuming the column allows an empty date, the correct value then would be null.

INSERT INTO table (column) VALUES (null);

Author:  superjacent [ Mon May 20, 2013 12:59 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Thanks Thom,

I'm having trouble purposely assigning Null to a variant value, the compiler is spitting out an error message "the item doesn't exist" referring to "Null". Here's my snippet of code preparing the date field and value that will form part of an Update statement.

dim d as date
if ParseDate(pVarFieldValue, d) then
pVarFieldValue = d.SQLDate
pVarFieldValue = k_QUOTE_S + pVarFieldValue + k_QUOTE_S // enclosed in single quotes.
else
pVarFieldValue = Null // error's out here.
end if


The date column does allow 'no dates' that is, can be empty.

You referred to an Insert statement but I'm assuming that entering a null is the same for an Update statement along along the lines of :
UPDATE Table SET datefield = null WHERE ........


The above is part of the process whereby all data from a window (varying types) are cycled through and added to a variant array, afterwhich an SQL Update statement is strung together and executed. As mentioned, it works, except for empty date values.

Author:  Thom McGrath [ Mon May 20, 2013 1:06 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Yeah sorry, I used an insert rather than update, but your code is correct in that sense.

Real Studio does not support nulls, but it does nil. The reason your code fails is that the compiler is looking for a variable called Null. The solution should be very simple. Since you're storing strings in pVarFieldValue anyway, just wrap Null in quotes to produce "Null" and presto.

Author:  timhare [ Mon May 20, 2013 1:18 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Except that will fail because it will try to wrap "Null" in single quotes,

UPDATE Table SET datefield = 'Null' WHERE ...

So you still have to check for the value "Null" and skip the single quotes when you create the sql statement. Nil might be a better alternative.

Author:  Thom McGrath [ Mon May 20, 2013 1:26 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

timhare wrote:
Except that will fail because it will try to wrap "Null" in single quotes,

UPDATE Table SET datefield = 'Null' WHERE ...

So you still have to check for the value "Null" and skip the single quotes when you create the sql statement. Nil might be a better alternative.

Not according to his code. When it detects a correct date, that if block inserts the single quotes around the date value. The code turns pVarFieldValue into SQL syntax.

Personally though (and I feel like I say this every SQL topic) I'd use a prepared statement:
Dim UserDate As Date
Call ParseDate(pVarFieldValue,UserDate)
Statement.Bind(0,UserDate)

That way you can let the engine figure it out. In this code, we don't even care if ParseDate succeeded or not. If it did, then we'll have a non-nil date object, and it'll get inserted according to the schema. If it fails, UserDate will be nil, and the engine will interpret that as a null.

It's a little different if your engine is SQLite though, as you'll need to BindType differently in the case of a null.

Author:  superjacent [ Mon May 20, 2013 1:28 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Thanks fella's, got it working. For completeness here's my snippet of code, merely wrapped the Null in double quotes so it's a string (as suggested).

dim d as date
if ParseDate(pVarFieldValue, d) then
pVarFieldValue = d.SQLDate
pVarFieldValue = k_QUOTE_S + pVarFieldValue + k_QUOTE_S
else
pVarFieldValue = "Null" // is now a string value (thanks Thom)
end if

And the generated SQL Update statement is as follows,
UPDATE tbl_Contacts  SET DateOfBirth = Null,  Gender = 'F',  Middle_Name = 'Janet',  First_Name = 'Jenny',  Last_Name = 'SMITH',  Details = 'Some stuff goes in here',  IsCriminal = True WHERE tbl_Contacts.Contact_ID = 5;

Author:  superjacent [ Mon May 20, 2013 1:35 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Thom McGrath wrote:
Personally though (and I feel like I say this every SQL topic) I'd use a prepared statement:
Dim UserDate As Date
Call ParseDate(pVarFieldValue,UserDate)
Statement.Bind(0,UserDate)

That way you can let the engine figure it out. In this code, we don't even care if ParseDate succeeded or not. If it did, then we'll have a non-nil date object, and it'll get inserted according to the schema. If it fails, UserDate will be nil, and the engine will interpret that as a null.

It's a little different if your engine is SQLite though, as you'll need to BindType differently in the case of a null.


Thom, that's on my to-do list, check out more thoroughly Prepared statements.

Author:  timhare [ Mon May 20, 2013 1:45 am ]
Post subject:  Re: SQL - Update Statement - how to write empty Date value

Thom McGrath wrote:
Not according to his code. When it detects a correct date, that if block inserts the single quotes around the date value. The code turns pVarFieldValue into SQL syntax.

You're right. I missed that.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/