All Apps and Add-ons

DBConnect 3.0.3 reading SQL DB with columns of nvarchar(max)

jeffbat
Path Finder

We just upgraded our DBConnect from 2.2.0 to 3.0.3 and are connecting to a SQL database (was an existing connection) and have found that columns which are (nvarchar(max),null) now come back to Splunk as Non-Displayable Column Type ntext.

These worked perfectly fine in 2.2.0.

Has anyone else seen this before? And is there something we need to do to get around it? Or is the only solution going back to the previous version of DBConnect?

ianyu
New Member

Try this:

     convert(varchar(64),sql_handle,1) sql_handle, 

it's workable for me after upgraded to DBX v3 🙂

0 Karma

axm4814
Engager

We cannot go through our db and update all our nvarchar((max), null) definitions in order to fix this issue because we'd end up breaking a bunch of applications. When is this going to be resolved? There shouldn't be anything wrong with importing a nullable field with the max size. The only solution so far is to revert dbconnect.

0 Karma

axm4814
Engager

Is there ever going to be a fix for this? We cannot feasibly go through all the columns in our db and change our datatypes from nvarchar((max), null) to a fixed value - we need the max field because truncation errors could occur and break our applications.

Is the only fix for now is to downgrade back to DB Connect 2.x ?

0 Karma

j0shrice
Path Finder

Has there been any update to this problem?

0 Karma

jeffbat
Path Finder

Sorry if when I posted the (nvarchar(max),null) listing that you thought it meant the data coming back in the column was NULL. That is just the column type in SQL.

The data that comes back from that can be different values (some are just test, some seem to be XML formatted).

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

Hi,

The same change applies to *LOB fields, only that one was implemented in v2. IIRC v1 would run them through strings to see if something came out... v2 and v3 just throw an error.

0 Karma

jeffbat
Path Finder

The fields (db columns) that are no longer showing the data when reading work fine in 2.3.1 (the version we have been running).

When reading this database with these 2 columns, the AlertName one is read fine but the AlertDescription throws the Non-Displayable Column Type ntext.

  • AlertName (nvarchar(256), not null)
  • AlertDescription (nvarchar(max), null)

Is there anything I can do in DBConnect 3.0.3 to get the AlertDescription field to read correctly like it did in 2.3.1?

0 Karma

johann_romero
New Member

Have you been able to find a workaround to this issue?

0 Karma

jeffbat
Path Finder

Never found a workaround.

For now, we reverted back to the 2.3.1 version of DBConnect to allow us to use the connections we were already using.

Hopefully someone will figure out some way around the issue.

0 Karma

johann_romero
New Member

Worked with support on this earlier today and this seems to be a UI issue. I went ahead and created the inputs and the data comes in fine for the columns that show Non-Displayable Column Type

0 Karma

jeffbat
Path Finder

would the columns show the data fine from those columns if you just did a dbxquery command instead of an input pull?

I do both here and when I set it up before, the field just would not even be there to show data.

Did you make any changes to ensure that the data would come in (even when it shows the Non-Displayable Column Type ?

0 Karma

johann_romero
New Member

It seems if you just do a dbxquery it presents same behavior as the SQL Explorer.
I can confirm however that defining the inputs with the query does bring in the data properly. I did not make modifications to the tables or db itself.

Hope it helps.

0 Karma

jeffbat
Path Finder

What version of Splunk are you running?

we are still on 6.4.3 and I am wondering if we see some of this behavior due to that.

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

Hi,

I apologize, we did change the behavior in v3. The SQL standards have never been very clear about what to do with columns that don't have data in them. Review https://en.wikipedia.org/wiki/Null_(SQL) for more background. DB Connect and Splunk do not render the NULL character, but instead replace it.

In DBX 2.x, a database NULL was transformed to the ASCII string "NULL", which is misleadingly ambiguous. Is this empty, or the last name "Null"? Upon reflection and review of bugs, we settled on the principle that we should not alter customer data any more.

Because database schemas may change and behavior has changed over the evolution of DB Connect, customers should consider using eval or fillnull to ensure that SQL searches are robust.
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Eval

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!