Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Nov 18, 2017 2:38 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: SQL - Update Statement - how to write empty Date value
PostPosted: Sun May 19, 2013 11:09 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 12:15 am 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 12:59 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 1:06 am 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 1:18 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 1:26 am 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 1:28 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 1:35 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
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
 Profile  
Reply with quote  
 Post subject: Re: SQL - Update Statement - how to write empty Date value
PostPosted: Mon May 20, 2013 1:45 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
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
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group