how can I get a coalesce like function in workflow.
when trying to assign mydbfield to metadata containing null and trying to “OR” with an empty string “”
thisDoc.fields.add2(“_vger_fld_myfield”, (rs.fields(“mydbfield”) || “”),2);
I get an exception even typeof rs.fields(“mydbfield”) || “” shows it as a string.
A plain typeof rs.fields(“mydbfield”) show it as being an “object”.
Have you tried JSON.stringify(rs.fields(“mydbfield”)) ?
If it works, then you could use a syntax such as:
thisDoc.fields.add2("_vger_fld_myfield", rs.fields("mydbfield") ? JSON.stringify(rs.fields("mydbfield")) : ""),2);
Phil, thx, but:
result is all strings containing data are empty afterwards.
What happens if you just do this:
thisDoc.fields.add2("_vger_fld_myfield", JSON.stringify(rs.fields("mydbfield"))),2);
doesn’t work either. Searched a lot and every solution with stringify or even a function to replace null didn’t work, same as:
(" " + rs.fields(“mydbfield”)).trim
generally the || “” should do the trick, but here it won’t.
Well it would therefore seem like your issue is with how you initially stored the value in your database…
What does that field actually contain? Have you tried
Watch.Log(rs.fields("mydbfield"),3);
to make sure the content actually makes sense?
sure I tested that: Log output of
Watch.Log(rs.fields("mydbfield"),2); is null
Watch.Log(typeof rs.fields(.....) returns: object
in fact it’s just an empty “null” cell in SQL db, I can solve that by doing a
select coalesce(mydbfield,‘’) as mydbfield
so there are no null values in the output of SQL, but that isn’t very nice.
The following works just fine. I am “simulating” your field by creating two nullObject variables, one containing null and the other containing a string.
var myMeta = new ActiveXObject("Metadatalib.metafile");
myMeta.LoadFromFile(Watch.GetMetadataFileName());
var nullObject1=null;
var nullObject2="Goodbye";
var myGroup = myMeta.Job().Group(0);
myGroup.Fields.Add2("Hello", (nullObject1 || "world"),2)
myGroup.Fields.Add2("Hello", (nullObject2 || "should not be used"),2)
myMeta.SaveToFile(Watch.GetMetadataFileName());
The code inserts fields in the first document of the metadata. When you inspect the results, you’ll see the values are inserted exactly as expected.
yes, that was my first thaught, but in fact it doesn’t work when doing it that way:
thisDoc.fields.add2("_vger_fld_myfield", (rs.fields("mydbfield") || ""),2);
if I try that just by assigning a null object, it works, e.g.
var nullobject=null;
thisDoc.fields.add2("_vger_fld_myfield", (nullobject||"hello"),2);
stores “hello” in metadata.
thisDoc.fields.add2("_vger_fld_myfield", (nullobject||""),2);
stores and empty string in metadata. Correct.
but:
thisDoc.fields.add2("_vger_fld_myfield", (rs.fields("mydbfield") || ""),2);
spawns an error (type conflict).
All right then, try this code to specifically filter out null values:
thisDoc.fields.add2("_vger_fld_myfield", rs.fields("mydbfield")!== null ? rs.fields("mydbfield") : ""),2);
thisDoc.fields.add2("_vger_fld_myfield", (rs.fields("mydbfield")!==null ? rs.fields("mydbfield") : "") ,2);
spawns a type conflict, too.
When does it spawn a type conflict? When it’s null, when it’s not null, both?
In any event, at this stage you may want to use the coalesce SQL method to ensure you don’t have to go through these hoops. Better yet, configure your database to insert an empty string in empty fields (rather than a null value). I am no database expert but I know most if not all DB Engines allow you to do that.
The error only occurs when it’s null.
Phil, yes, I already changed the select statement (using coalesce or isnull) so everything works, but I don’t want to give attention to every incoming field, esp. 'cause data comes from our customers. I’ll give vbscript a try, maybe it’s about the jscript. the “OR” method stated above should work (as mentioned in several blogs) but there seems to be something wrong with Planet Press implementation.
But thx a lot for your efforts.
As I’ve demonstrated before, the OR method works. It’s the null content of your field that apparently cannot be converted to something JScript understands.
Our implementation of JScript is that of Microsoft’s Windows Scripting Host, so it’s not something proprietary.
since I’m using the ActiveX ADODB component the issue might be there, backend SQL is MSSQL 2014. I’ll try another dataprovider.
Best way to get data out of MSSQL is the ADODB connection with provider sqloledb, that’s what I actually use. So who to blame? Open support ticket?
seems that there’s no way to do it in jscript. I recoded the db-lookup in vbscript, so the assignment (with a dirty trick to overcome the null values by adding & “”) now is:
thisDoc.fields.add2 “_vger_fld_myfield”, rs.fields(“mydbfield”) & “” ,2
and it works, though the jscript rs.fields("mydbfield")+'' won’t if mydbfield is null.