CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> IDE/GUI

 custom sql parameters not working

Print topic Send  topic

Author Message
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?
View profile  Send private message
mamboBROWN


Posts: 1712
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)??
View profile  Send private message
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?
View profile  Send private message
mamboBROWN


Posts: 1712
Posted: 03/25/2010, 3:00 PM

maclonghorn,
Have you had any more luck with this??
View profile  Send private message
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.
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.