New post
Question
· Nov 4, 2020

Avoid $c(0) in globals

#.NET #Globals #SQL #Caché

Good Morning,

I have this question for a long time. When I make an insert by SQL from an external application in the fields of type %String, if they are empty, it writes the character $c(0) in the global.

Is there a way so that if an Insert is received in a field of type% String with null value instead of the $ c (0) it leaves it empty?:

I have the class defined like this:

Class User.MiClase Extends (% Persistent,% XML.Adaptor)

and the property like this:

Property myProperty As% String;

I have tried creating a trigger or setting default values but it doesn't work.

Trigger DespuesInsert [ Event = INSERT, Time = AFTER ]
{
  S:({myProperty  }=$c(0)) {myProperty  }=""
}

Thank you!

Discussion (7)1
Log in or sign up to continue
David Van De Griek · Nov 4, 2020

$c(0) is the internal representation of the SQL empty string ''.  Your insert must be using '' as the value for myProperty instead of null if you are seeing $c(0) for the field values.  SQL null and empty-string string are very different.  null means unknown, while empty string is a 0 length string value.

If you always want to convert '' to null for myProperty values, write a myPropertyNormalize property member method that converts the value to null.  This will override the %String Normalize() method and convert $c(0) to "" whenever the property/field's value is normalized by the filers.

Trying to do this in a trigger will not work as triggers are not meant to be used to modify property/field values being filed.

2 0
Daniel Aguilar · Nov 9, 2020

Good morning,

Sorry, I was wrong when writing the question, effectively what I am receiving is a empty string and what I want to store is an null.

David,
I have been trying to overwrite the Normalize method but it is not working for me, maybe I am not overwriting the method well

ClassMethod myPropertyNormalize(%val As %CacheString) As %String [ CodeMode = generator, ServerOnly = 1 ]
{
s str=""
//s code="%val" //i %parameter("TRUNCATE"),%parameter("MAXLEN")'="" s code="$e(%val,1,"_(+%parameter("MAXLEN"))_")"
//$$$GENERATE(" Q "_code)
$$$GENERATE(" Q "_$tr(code,$c(0),""""))
QUIT $$$OK
}

I am doing it right? (I guess not xD )

Thanks!.

0 0
David Van De Griek · Nov 9, 2020

Daniel,

I guess it looks like we don't support CodeMode = generator for overriding property methods. CodeMode = code should work, but you need to access type parameters from the property definition in order to generate the proper code. I believe you will need to extend the %String datatype and override Normalize, then use the new datatype class in your property definition. Here is an example:

Class User.StringoNoEmpty Extends %String [ Language = objectscript ] {

ClassMethod Normalize(%val As %RawString) As %String [ CodeMode = generator, ServerOnly = 1 ]

{

{
    set code="%val"
    if %parameter("TRUNCATE"),%parameter("MAXLEN")'="" { set code="$e(%val,1,"_(+%parameter("MAXLEN"))_")" }
    $$$GENERATE("    RETURN $tr("_code_",$c(0),"""")")
    RETURN $$$OK
}

}

Property myString As User.StringoNoEmpty(MAXLEN = 54, TRUNCATE = 1);

Hope this helps,

-dave

0 0
Nigel Salm · Nov 5, 2020

Hi

if you have a simple Insert SQL statement in the form:

INSERT into {Table} VALUES ('abc','zyx',123,'',NULL)

then Cache SQL will replace '' with $c(0) in the global record and "" for NULL.

NULL is a valid keyword in SQL not only for testing conditions such as "WHERE {column} IS NOT NULL" but also when being used in the context of values in my INSERT example and also in Class Methods or Class Queries that are projected as SQL Stored Procedures. 

So if you have a Class Method projected as a Stored Procedure then if you call the stored procedure as follows:

call MyTable_MyProcedure('abc','xyz',123,'',NULL) 

then the values for the parameters will be

p1 = "abc"

p2 = "xyz"

p3 = 123

p4 = $c(0)

p5 = ""

Yours

Nigel

0 0
Vitaliy Serdtsev · Nov 9, 2020

There is another way.
If you want any empty strings to always be treated as null/"" instead of "/$c(0), then there is an documented setting (within the scope of namespace), namely:

^%SYS("sql","sys","namespace",<YOUR_NAMESPACE>,"empty string")

Here is a small example:

Class dc.test Extends %Persistent
{

Property i As %Integer;

Property str As %String;

/// do ##class(dc.test).Test()
ClassMethod Test()
{
 do ..%KillExtent()
 
 try{
  
  do $system.SQL.Purge()
  set ^%SYS("sql","sys","namespace",$namespace,"empty string")=$c(0)

  ; '' -> $c(0)
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(1,'')")
  
  ; null -> ""
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(2,null)")
  
  do $system.SQL.Purge()
  set ^%SYS("sql","sys","namespace",$namespace,"empty string")=""

  ; '' -> ""
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(11,'')")
  
  ; null -> ""
  do ##class(%SQL.Statement).%ExecDirect(,"insert into dc.test(i,str) values(22,null)")

  zwrite ^dc.testD
  kill ^%SYS("sql","sys","namespace",$namespace,"empty string")
 }
 catch(ex){
  write ex.DisplayString(),!
 }
}

}

USER>do ##class(dc.test).Test()
^dc.testD=4
^dc.testD(1)=$lb("",1,$c(0))
^dc.testD(2)=$lb("",2,"")
^dc.testD(3)=$lb("",11,"")
^dc.testD(4)=$lb("",22,"")

1 0