Data connection manager Wraps SQL Code causing errors

[Scott_Kane]

I have noticed when using Data Connection Manager for ODBC or OCI connections it will wrap your SQL Code. If you have a comment in the code then the wrapping causes errors as other lines, like perhaps a from statement, will be commented out too. Regular ODBC or OCI connections don't do this. DCM connections using OleDB also don't do this. I think this is a bug but has anyone else experienced this issue too? I'm not sure how to submit a bug report

 

 

[pmpknface]

I too am have this error, any help would be appreciated!  In the meantime, I'm just using a comment tool to save code in a more readable format for reference.  

[simonaubert_bd]

Hello,

You can vote for this related idea : https://community.alteryx.com/t5/Alteryx-Designer-Ideas/In-Database-SQL-Compatibility-including-comm...

"comments are not supported for In-Database tools. The way
the tools work is that they generate a giant SQL query from each tool which is then wrapped in a
SELECT statement which explains why Alteryx is trying parse the comments as a query.
Comments ARE supported for the Input tool but at this time it's not possible to utilize them with
the In-DB functionality."

Best regards,

Simon

[Scott_Kane]

Hi Simon, That's good to know about InDB tools but I'm just using a regular input tool. If they fix one of these bugs they hopefully should fix both

[Crossair]

I have the same problem, not an in-DB topic but changing from "normal connection" to "DCM the sql editor removes all CR/LF.

Funny is: the notepad++ shows CR/LF, copying it to the sql editor shows a statement with! CR/LF. Only after closing the tool, open it again right away, the CR/LF are deleted.

This happens with our wothout any comments, and btw. comments are supported, you can't use -- because the requiresd CR/LF, otherwise, the rest of the statement is a comment ;-)

Using /*comment*/ works fine, but I agree on the statement above, it renders the statment unreadable, so, pls., also from my side (proxy for a number of Alteryx Designers in my comany), fix this bug!

[ArnaldoSandoval]

Hi All, @Crossair , @Scott_Kane 

This behaviour had been part of writing SQL queries for years, have you try to comments with open-close comment tags, like /* , s3_location */ instead.

Arnaldo

[Crossair]

Hi Arnaldo,

no, it hasn't, but I'm refering to the deletion of CR/LF in the editor. Using non-DCM connection keeps them in the editor, As soon as I switch to DCM managed connection the editor deletes all CR/LF just before reopening the editor.

You can stil enter CR/LFs, the editor displays them correctly, but closing and reopening deletes them...

Best Regards,

Thomas

[ArnaldoSandoval]

@Crossair 

Yes, I am familiar with that behaviour, you have three options.

1. Open a support ticket with Alteryx and wait for their reply.
2. Use the /* my comment */ approach, it works although I haven't try it in Alteryx.
3. Remove the column altogether from the query.

I believe the double dash open the door for SQL Injection vulnerability.

I understand it is a pain, but it has been part of submitting queries to database since the early 2000s 😮

Arnaldo

Arnaldo