maclonghorn
Posts: 22
|
Posted: 03/19/2010, 1:17 PM |
|
I am doing a custom update using SQL (MySQL). The reason I'm not using the normal Table update is b/c I'm updating a parent and child record. Everything works fine when there's data present, but when the field is empty (eg, a blank date field) or there is nothing selected (eg, dropdown list with nothing selected) I get an error. This is due to the SQL being created incorrectly b/c it seems to ignore the default value I set for the parameters.
Here's a sample of the SQL i created:
UPDATE property
SET SomeDate='{SomeDate}'
, OwnerId={OwnerId}
WHERE id={id}
So if I leave the date blank, it tries to use "" as the date, which generates '1-1-1900' in MySQL. And if I don't select an owner the SQL breaks b/c it looks like:
UPDATE property
SET
SomeDate='', OwnerId=
WHERE id=2
I've tried a number of different things in the default value (null, "null", "", " ") and nothing seems to work. Is this broken? Am I missing something?
|
|
|
mamboBROWN
Posts: 1713
|
Posted: 03/19/2010, 1:59 PM |
|
maclonghorn
I have a couple of questions. What version of MySQL are you using?? Have you tried checking the date value before you update the database and if it is empty then set it to NULL (BEFORE UPDATE EVENT)??
|
|
|
maclonghorn
Posts: 22
|
Posted: 03/22/2010, 8:06 AM |
|
MySQL 5.x
I guess I was trying to understand how to use the default value for parameters when using a custom SQL for updates. I wasn't sure how to set it for blank or null values. The null is particularly important when using date values b/c of the issue I noted above.
I tried the method you suggested but with no success.
Has anyone been able to use default parameter values in custom SQL?
|
|
|
mamboBROWN
Posts: 1713
|
Posted: 03/25/2010, 3:00 PM |
|
maclonghorn,
Have you had any more luck with this??
|
|
|
maclonghorn
Posts: 22
|
Posted: 03/26/2010, 7:56 AM |
|
No. I submitted a request to support, and their suggestion was to "manually" set the value on submission, but I've tried that as well. If you're able to specify a default value in the custom update SQL, then you should be able to specify NULL as a default value for a date field. They're going to run it by the developers.
What I've done for now is to add custom code to the After Update/Insert events to update the "child" table.
|
|
|
|