how to insert null in mysql

[shood38]

Given something similar to below, how do I insert NULL in to a mysql table? The word 'NULL' below returns an error, so do double or single quotes and the function null_value().


args.add("vvesselid", if(vVesselID=0,NULL,vVesselID))
args.add("vclientid", if(vClientID=0,NULL,vClientID))

[Selwyn Rabins]

Re: how to insert null in mysql

Given something similar to below, how do I insert NULL in to a mysql table? The word 'NULL' below returns an error, so do double or single quotes and the function null_value().


args.add("vvesselid", if(vVesselID=0,NULL,vVesselID))
args.add("vclientid", if(vClientID=0,NULL,vClientID))

you need to set the optional isNull property when you add the argument. since the IsNull property is the 4th argument, that will force you to also set the optional 3rd argument.

e.g.
this sets an argument for a nominally numeric field to a null value:
args.add("foo",1,sql::argumentusage::inputargument,.t.)



this set a time value argument to a null value:
args.add("whattime",ctodt(""),sql::argumentusage::inputargument,.t.)


you can also do this

args[1].isNull = .t.

[Steve Wood]

Re: how to insert null in mysql

Selwyn,

Thank you, testing this now. This is for my client. Note - the wiki does not mention this fourth IsNull parameter. I sent a note to Martin.

http://wiki.alphasoftware.com/SQL+Ar...s+Add+Function

[aburningflame]

Re: how to insert null in mysql

Hi,
what is wrong with this code?
dim args as SQL::Arguments
args.XML=e.argsXML
debug(1)
if args.ArgumentNumber("new_client_id")>-1 then
if args.Find("new_client_id").data==0 then
args.set("new_client_id",0,sql::argumentusage::inputargument,.t.)
e.argsXMLModified=args.XML
e.sqlModified=e.sql
e.SQLCommandWasHandled=.f.
end if
end if


Everytime I attempt to use the e.argsXMLModified and the e.sqlModified - it never works - I always have to resort to setting command handled to true and writing code to e.cn.execute(sql,args).

Is what im trying to do not supported? - Despite being documented?

[aburningflame]

Re: how to insert null in mysql

Can a -1 Is null or a 0 Is Null flag be added to the field list please? The same way 'Blank Is Null' is added. Please. or just a 'NULL VAlue: ' prompt so that I can enter 0 or -1 or 'testvalue'

[aburningflame]

Re: how to insert null in mysql

dim args as SQL::Arguments
args.XML=e.argsXML

	if args.ArgumentNumber("new_client_id")>-1 then
	     if convert_type(args.Find("new_client_id").data,"N")==0 then
       		 args.set("new_client_id",0,sql::argumentusage::inputargument,.t.)
    	end if
	end if
  
    if args.ArgumentNumber("new_main_contact_id")>-1 then
	     if convert_type(args.Find("new_main_contact_id").data,"N")==0 then
	        args.set("new_main_contact_id",0,sql::argumentusage::inputargument,.t.)
	    end if
    end if
    
     if args.ArgumentNumber("new_main_invoice_profile")>-1 then
	     if convert_type(args.Find("new_main_invoice_profile").data,"N")==0 then
	        args.set("new_main_invoice_profile",0,sql::argumentusage::inputargument,.t.)
	    end if
	 end if
    
    e.SQLCommandWasHandled=.t.
        
        if e.cn.execute(e.sql,args) then
            if lower(e.sqlType)=="insert" then
                e.lastInsertedIdentityValue=e.cn.CallResult.LastInsertedIdentity
            else if lower(e.sqlType)=="update" .OR. lower(e.sqlType)=="delete" then
            	e.rowsAffected=e.cn.CallResult.RowsAffected
            end if
        else
        	e.flagError=.t.
        	e.errorText="Could not "+lower(e.sqlType)+" record. Please try again later"
        end if

But i would much prefer having to do it the easy way.

[Steve Wood]

Re: how to insert null in mysql

args.add should work out whether it should insert a NULL or not. I find with args.add() I need to include conditional code to ensure I deal with the possibility of a null value in an INSERT.

However if I ensure the source table field have a default Empty String, rather than NULL, the add.args() works without checking if the field is null. But if the source field is null, I find that args.add() has problems.