[teiid-issues] [JBoss JIRA] (TEIID-3004) TEIID30183 Message:TEIID30183 Text parse error: Non-whitespace character found between the qualifier and the delimiter in text line 16, 161 in Unknown. SQLState: TEIID30183

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3004?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12976193#comment-12976193 ] 

Steven Hawkins commented on TEIID-3004:
---------------------------------------

> Sorry Steve how can I inspect the content read from the webservice translator before that is parsed? To know if it got truncated.

You'd select the clob value with different sql and inspect it in a client.  However it's now clear what is going on, so that won't be necessary.

> The content are rows whose fields are delimited by TAB and not quote, but the row 16161 is the only one that has double quote as content (I believe that even if I declared the TAB as delimiter TEIID find the double quote and complain).

With TAB as the delimiter (rather than the single space, which I had initially assumed) and the default double quote qualifier, then yes that row is invalid.

> you can see that there is only the 16161 row with some double quote in the field, but is not a delimiter (not understand what you mean by qualifier)

Qualifier or quote character are used interchangeably.  It means the character that should go around an entire value.  So by having the quote character as ", it would need to appear around the entire entry 
valid: "abc""xyz"
not valid: abc"xyz

The take away here is that this data set is not using a qualifier/quote character.  You should instead tell text table that an escape is being used.  The escape character can appear before the row or column delimiter to indicate that it's part of the current value.  If there is no such character - for example if it's assumed that tab / newline will never appear in the data values - then just use the null character:

TEXTTABLE(.... ESCAPE '\u0000')


> TEIID30183 Message:TEIID30183 Text parse error: Non-whitespace character found between the qualifier and the delimiter in text line 16,161 in Unknown. SQLState:  TEIID30183
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-3004
>                 URL: https://issues.jboss.org/browse/TEIID-3004
>             Project: Teiid
>          Issue Type: Bug
>      Security Level: Public(Everyone can see) 
>    Affects Versions: 8.8
>            Reporter: luca gioppo
>            Assignee: Steven Hawkins
>            Priority: Minor
>
> On the same use case of TEIID30181 I now got the error in object:
> TEIID30183 Message:TEIID30183 Text parse error: Non-whitespace character found between the qualifier and the delimiter in text line 16,161 in Unknown.
> SQLState:  TEIID30183
> the row should be:
> istsc_pgee021002	Direzione Didattica Statale "Franco Rasetti"	Castiglione del Lago	ANTONIETTA	MANNUCCI	06061	PG	Umbria	www.dirclago.it	Via Carducci, 25	Dirigente Scolastico	Istituti di Istruzione Statale di Ogni Ordine e Grado	Pubbliche Amministrazioni		S	80005650546	pgee021002 at pec.istruzione.it	pec	pgee021002 at istruzione.it	altro	null	null	null	null	null	null					0
> the only difference I find between the previous rows that passed without problems is that a field has double quote in its content.
> The delimiter is tabulation so there should not be any problem.
> Could it be this the problem and how to solve it?
> Thanks



--
This message was sent by Atlassian JIRA
(v6.2.6#6264)