Re: Insert null into SQLServer Table from JSONtoSQL processor

[Bryan Bende <bb...@gmail.com>]

Hello,

I am not totally sure if this will work, but there is a property in
ConvertJSONToSQL called "Unmatched Column Behavior" which defaults to
"fail"... this means if the JSON doesn't have a field for one of the
columns in the table then it is failure.

Could you set that to "ignore" and send in a JSON that doesn't have the
field that you want to be null? This way it skips over that field in the
json all together and it wouldn't be part of the insert statement at all.

So if you had "mytable" with "col1", "col2", "col3" and you sent in JSON
like { "col1" : "val1", "col2" : "val2" } it would generate something like
"INSERT INTO mytable (col1, col2) VALUES (?, ?)".

-Bryan


On Mon, Aug 8, 2016 at 9:27 AM, Guillaume Pool <gp...@live.co.za> wrote:

> Hi,
>
>
>
> Is there any way I can specify the null value for a field to be the
> equivalent of SQL null value in an update attributes statement using
> expression language.
>
>
>
> The output is either NULL (seems to try to load that as a string) or ‘’,
> and I get an error that it cannot convert that string value into GUID.
>
>
>
> Thanks
>
> Guillaume
>
>
>

Re: Insert null into SQLServer Table from JSONtoSQL processor

[Guillaume Pool <gp...@live.co.za>]

I was hoping that i would not have to check the field for a null value before inserting / then deleting the attributes.

Is there a way to make sure flowfiles routed in different directions still flow out in thesame order as it came in? I would have to do additional steps to the row and still make sure it is inserted before the rows behind that does not have null values.

Thanks

Get Outlook for iOS<https://aka.ms/o0ukef>




On Mon, Aug 8, 2016 at 9:42 PM +0200, "Bryan Bende" <bb...@gmail.com>> wrote:

Hello,

I am not totally sure if this will work, but there is a property in ConvertJSONToSQL called "Unmatched Column Behavior" which defaults to "fail"... this means if the JSON doesn't have a field for one of the columns in the table then it is failure.

Could you set that to "ignore" and send in a JSON that doesn't have the field that you want to be null? This way it skips over that field in the json all together and it wouldn't be part of the insert statement at all.

So if you had "mytable" with "col1", "col2", "col3" and you sent in JSON like { "col1" : "val1", "col2" : "val2" } it would generate something like "INSERT INTO mytable (col1, col2) VALUES (?, ?)".

-Bryan


On Mon, Aug 8, 2016 at 9:27 AM, Guillaume Pool <gp...@live.co.za>> wrote:
Hi,

Is there any way I can specify the null value for a field to be the equivalent of SQL null value in an update attributes statement using expression language.

The output is either NULL (seems to try to load that as a string) or '', and I get an error that it cannot convert that string value into GUID.

Thanks
Guillaume