Execute SQL support for nvarchar(max) datatype

[Venugopal Iyengar <iy...@gmail.com>]

 Hello there,
I am using Execute SQL, Split AVro and put file processors to read some pdf
documents stored in SQL database and store that in the file system.

When I use varbinary(max)  I was able to pull and view the pdf without any
issues.

When the datatype is nvarchar(max) I am unable to open the pdf file.

I appreciate if some body can shed some light on this. How can I debug this?

Thanks
Venu


[image: image.png]

Re: Execute SQL support for nvarchar(max) datatype

[Endre Kovács <ek...@cloudera.com.INVALID>]

Hi Venu,
I belive it would only be possible if the PDF would be base64 encoded
before DB insertion, and then in NiFi it would need to be base64 decoded.
Seems possible, but adds a bit of computing overhead to the flow.
Best regards,
Endre

On Thu, Apr 18, 2019, 5:09 PM Venugopal Iyengar <iy...@gmail.com>
wrote:

> Thanks Peter.
> We are trying to avoid using nvarchar going forward. However, for some
> schema it is implemented that way by some consultants. If you can give me
> the steps to debug that would be helpful. Is it possible to get this
> working by using some converters as part of the flow? I appreciate your
> help on this.
>
> Venu
>
> On Tue, Apr 16, 2019 at 5:23 AM Peter Turcsanyi
> <tu...@cloudera.com.invalid> wrote:
>
> > Hi Venu,
> >
> > If I'm not wrong, you're using MS SQL Server and nvarchar / varbinary are
> > the database column types.
> > nvarchar can be used for storing character data (strings) but it is not
> > suitable for binary data (like pdf).
> >
> > I think NiFi tries to handle the bytes of the pdf file as Unicode
> > characters. It could be debugged but my short answer is that nvarchar
> > should not be used for binary data.
> >
> > Regards,
> > Peter
> >
> > On Mon, Apr 15, 2019 at 10:05 PM Venugopal Iyengar <
> > iyengar.g.venu@gmail.com>
> > wrote:
> >
> > > Hello there,
> > > I am using Execute SQL, Split AVro and put file processors to read some
> > > pdf documents stored in SQL database and store that in the file system.
> > >
> > > When I use varbinary(max)  I was able to pull and view the pdf without
> > any
> > > issues.
> > >
> > > When the datatype is nvarchar(max) I am unable to open the pdf file.
> > >
> > > I appreciate if some body can shed some light on this. How can I debug
> > > this?
> > >
> > > Thanks
> > > Venu
> > >
> > >
> > > [image: image.png]
> > >
> >
>

Re: Execute SQL support for nvarchar(max) datatype

[Venugopal Iyengar <iy...@gmail.com>]

Thanks Peter.
We are trying to avoid using nvarchar going forward. However, for some
schema it is implemented that way by some consultants. If you can give me
the steps to debug that would be helpful. Is it possible to get this
working by using some converters as part of the flow? I appreciate your
help on this.

Venu

On Tue, Apr 16, 2019 at 5:23 AM Peter Turcsanyi
<tu...@cloudera.com.invalid> wrote:

> Hi Venu,
>
> If I'm not wrong, you're using MS SQL Server and nvarchar / varbinary are
> the database column types.
> nvarchar can be used for storing character data (strings) but it is not
> suitable for binary data (like pdf).
>
> I think NiFi tries to handle the bytes of the pdf file as Unicode
> characters. It could be debugged but my short answer is that nvarchar
> should not be used for binary data.
>
> Regards,
> Peter
>
> On Mon, Apr 15, 2019 at 10:05 PM Venugopal Iyengar <
> iyengar.g.venu@gmail.com>
> wrote:
>
> > Hello there,
> > I am using Execute SQL, Split AVro and put file processors to read some
> > pdf documents stored in SQL database and store that in the file system.
> >
> > When I use varbinary(max)  I was able to pull and view the pdf without
> any
> > issues.
> >
> > When the datatype is nvarchar(max) I am unable to open the pdf file.
> >
> > I appreciate if some body can shed some light on this. How can I debug
> > this?
> >
> > Thanks
> > Venu
> >
> >
> > [image: image.png]
> >
>

Re: Execute SQL support for nvarchar(max) datatype

[Peter Turcsanyi <tu...@cloudera.com.INVALID>]

Hi Venu,

If I'm not wrong, you're using MS SQL Server and nvarchar / varbinary are
the database column types.
nvarchar can be used for storing character data (strings) but it is not
suitable for binary data (like pdf).

I think NiFi tries to handle the bytes of the pdf file as Unicode
characters. It could be debugged but my short answer is that nvarchar
should not be used for binary data.

Regards,
Peter

On Mon, Apr 15, 2019 at 10:05 PM Venugopal Iyengar <iy...@gmail.com>
wrote:

> Hello there,
> I am using Execute SQL, Split AVro and put file processors to read some
> pdf documents stored in SQL database and store that in the file system.
>
> When I use varbinary(max)  I was able to pull and view the pdf without any
> issues.
>
> When the datatype is nvarchar(max) I am unable to open the pdf file.
>
> I appreciate if some body can shed some light on this. How can I debug
> this?
>
> Thanks
> Venu
>
>
> [image: image.png]
>