Our company is currently evaluating Toad Data Point for use against Netezza (through ODBC driver) and we have recently come across an issue that may be a deal-breaker for us.
When executing a “Create or Replace Procedure,” the formatting is stripped (removes the line breaks) when it is saved to Netezza. It ends up appearing as a single line of code between the BEGIN and END… by comparison, when using Aginity (also through the same ODBC driver) it does NOT strip the formatting of the stored procedure.
Has anyone experienced this before? Any suggestions or setting tweaks I might try? Toad Data Point has so much to offer our team, but I honestly can’t imagine using it if we are unable to maintain the readability of complex stored procedures.
I have never connected to Netezza, but try looking at Tools/Options in the Editor section (general and formatter areas). I have not played with these but there is a default language in the general section try changing that to SQL(ODBC) and see if that makes a difference. Then there are options in the formatter including custom. Best find out if Dell plans on making a native connection to Netezza.
Thanks for the reply. I’ve been through the custom formatting options quite extensively and I didn’t see anything that might apply to my situation.
The custom formatting in TOAD works fine for me… I’m able to take a bunch of SQL and have it formatted in a structured manner. However, when I execute a Create or Replace statement for a stored procedure… even though the SQL is formatted perfectly, when it is stored in Netezza, the stored procedure loses all structure. I’ve verified that this is not a Netezza issue, but rather a TOAD issue.
It’s almost as if TOAD forces the use of the ODBC driver’s “Advanced DSN Option” of “Strip CR/LF”.
I will however check into Dell’s plans of making native connections to Netezza. Hopefully I will discover an affirmative.
Hi this is not specifically a Toad problem - Aginity has a similar problem when saving stored procedures:
However I do not think we can implement below (which is a quote from their web site) in Toad - I will raise it with Debbie the next time I speak to her.
The formatting is lost because the driver removes all comments and formatting. Fortunately, there is a workaround.
If you switch your query editor to Procedure mode, the tool will know you are creating or altering a stored procedure and act differently than just using Block mode of execution.
To switch to the Procedure mode, right click on your editor, select Options from the context menu and specify SP/Function in the Query Kind drop down.
This mode is switched on by default if you use Edit Stored Procedure menu item.
I entered QAT-5316 but I can’t guarantee when we would implement. This is the type of thing we support when we have a native connector for a platform. Netezza is still being supported through the ODBC provider.
