New topic: SQL - Update Statement - how to write empty Date value
<http://forums.realsoftware.com/viewtopic.php?t=47923> Page 1 of 1 [ 9 posts ] Previous topic | Next topic Author Message superjacent Post subject: SQL - Update Statement - how to write empty Date valuePosted: Sun May 19, 2013 11:09 pm Joined: Sat Oct 01, 2005 4:47 am Posts: 115 Location: Melbourne, Australia 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. _________________ Steve rs2012 r2.1 Windows 7. Top Thom McGrath Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 12:15 am Site Admin Joined: Tue May 06, 2008 1:07 pm Posts: 1463 Location: NotEvenOnTheMap, CT Well, assuming the column allows an empty date, the correct value then would be null. INSERT INTO table (column) VALUES (null); _________________ Thom McGrath - @tekcor Web Framework Architect, Real Software, Inc. Top superjacent Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 12:59 am Joined: Sat Oct 01, 2005 4:47 am Posts: 115 Location: Melbourne, Australia 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. _________________ Steve rs2012 r2.1 Windows 7. Top Thom McGrath Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 1:06 am Site Admin Joined: Tue May 06, 2008 1:07 pm Posts: 1463 Location: NotEvenOnTheMap, CT 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. _________________ Thom McGrath - @tekcor Web Framework Architect, Real Software, Inc. Top timhare Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 1:18 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12351 Location: Portland, OR USA 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. Top Thom McGrath Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 1:26 am Site Admin Joined: Tue May 06, 2008 1:07 pm Posts: 1463 Location: NotEvenOnTheMap, CT 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. _________________ Thom McGrath - @tekcor Web Framework Architect, Real Software, Inc. Top superjacent Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 1:28 am Joined: Sat Oct 01, 2005 4:47 am Posts: 115 Location: Melbourne, Australia 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; _________________ Steve rs2012 r2.1 Windows 7. Top superjacent Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 1:35 am Joined: Sat Oct 01, 2005 4:47 am Posts: 115 Location: Melbourne, Australia 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. _________________ Steve rs2012 r2.1 Windows 7. Top timhare Post subject: Re: SQL - Update Statement - how to write empty Date valuePosted: Mon May 20, 2013 1:45 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12351 Location: Portland, OR USA 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. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 9 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
