When I first setup my Aqua Data Studio, I turned on the ability to right trim spaces on ‘char’ and ‘nchar’ data types from the Options > Results configuration menu. This works very well when I use Query Analyzer, but has no effect in my automated Fluid scripts. My char fields are exported with the additional whitespace padding.
Is there a way to enable right trim spaces in FluidShell?
FluidShell does respect "right trim" option when performing a select statement. However, neither the GUI nor FS respects this option when doing a sqlexport.
FluidShell does respect "File -> Options -> Results -> Right trim spaces on 'char' and 'nchar' datatypes" option when executing a SELECT statement, so do the values assigned to the variables specified by the -i and -n options on the \go command. This works for both ADS 12.0 and ADS trunk.
The 'Right trim' option is not currently supported by the FS \sqlexport command, nor the ADS 'Export Data' tool.
Not sure if we are going to support 'right trim' in \sqlexport and GUI Export Data tool. If we do, please reassign this issue back to me.
Both \sqlexport and Export Data tool use ETLUtil.buildRecord(...) to construct output data, this method also used by SchemaObject.generateInsert(...). If we want to support the requested feature, then we need to modify ETLUtil.buildRecord(...) which seems to be straight forward; the change will affect SchemaObject.generateInsert(...) as well, assume it is OK.
Not sure if we are going to support 'right trim' in \sqlexport and GUI Export Data tool. If we do, please reassign this issue back to me.
Both \sqlexport and Export Data tool use ETLUtil.buildRecord(...) to construct output data, this method also used by SchemaObject.generateInsert(...). If we want to support the requested feature, then we need to modify ETLUtil.buildRecord(...) which seems to be straight forward; the change will affect SchemaObject.generateInsert(...) as well, assume it is OK.
Please go ahead & implement this feature in v12
Please go ahead & implement this feature in v12
This feature is supported as:
SVN r30916 - 12.0 branch
SVN r30917 - 13.0 branch
SVN r30918 - trunk
There are two implementations of the buildRecord(...) method in the ETLUtil.java, both of them are modified:
(1) The first one is used by FS \sqlexport command, ADS Export Data tool and SchemaObject.generateInsert(...); tested using \sqlexport, Export Data tool and Schema Script Generator respectively.
(2) The second one is used by Save Results dialog (Data Format: INSERT Statements) which actually applies 'right trim' to the values displayed in the grid. If this is not desired please let me know.
This feature is supported as:
SVN r30916 - 12.0 branch
SVN r30917 - 13.0 branch
SVN r30918 - trunk
There are two implementations of the buildRecord(...) method in the ETLUtil.java, both of them are modified:
(1) The first one is used by FS \sqlexport command, ADS Export Data tool and SchemaObject.generateInsert(...); tested using \sqlexport, Export Data tool and Schema Script Generator respectively.
(2) The second one is used by Save Results dialog (Data Format: INSERT Statements) which actually applies 'right trim' to the values displayed in the grid. If this is not desired please let me know.
Tested the trimming cases using ADStudio 14 Dev 8.
The test scenario involves calling first the next commands in FS:
Using MySQL the values for CHAR and NCHAR are automatically trimmed upon INSERT, therefor results trimming for CHAR and NCHAR does not apply.
However, if using SQLite, where no trim occurs on INSERT, the results trimming does not seem to work.
The results for the following cmds are always the same despite enabling or disabling the 'File > Options > Results > Right trim spaces on 'char' and 'nchar' datatypes" option.
Reopened for this reason. Shouldn't right trim work in the above cases?
Tested the trimming cases using ADStudio 14 Dev 8.
The test scenario involves calling first the next commands in FS:
Using MySQL the values for CHAR and NCHAR are automatically trimmed upon INSERT, therefor results trimming for CHAR and NCHAR does not apply.
However, if using SQLite, where no trim occurs on INSERT, the results trimming does not seem to work.
The results for the following cmds are always the same despite enabling or disabling the 'File > Options > Results > Right trim spaces on 'char' and 'nchar' datatypes" option.
Reopened for this reason. Shouldn't right trim work in the above cases?
The reason it does not work for SQLite is independent of FS. You'll notice that even through the GUI export, these values are not right trimmed for SQLite. It doesn't work w/ SQLite b/c char & nchar are not native data types in SQLite. W/ SQLite, you can pretty much declare any 'data type' that you wish but these are not native to SQLite. Do you have access to SQL Server or some other DB? If not, no issue. We can test it some more internally prior to marking as closed.
The reason it does not work for SQLite is independent of FS. You'll notice that even through the GUI export, these values are not right trimmed for SQLite. It doesn't work w/ SQLite b/c char & nchar are not native data types in SQLite. W/ SQLite, you can pretty much declare any 'data type' that you wish but these are not native to SQLite. Do you have access to SQL Server or some other DB? If not, no issue. We can test it some more internally prior to marking as closed.
Yes, I wasn't quite sure about the SQLite behavior.
Do you have access to SQL Server or some other DB?
No, currently all the tests I've ran are performed only using MySQL and SQLite.
Yes, I wasn't quite sure about the SQLite behavior.
Do you have access to SQL Server or some other DB?
No, currently all the tests I've ran are performed only using MySQL and SQLite.
Issue #8172 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build 12.0/r30916, 13.0/r30917, trunk/r30918 |
No time estimate |
FluidShell does respect "File -> Options -> Results -> Right trim spaces on 'char' and 'nchar' datatypes" option when executing a SELECT statement, so do the values assigned to the variables specified by the -i and -n options on the \go command. This works for both ADS 12.0 and ADS trunk.
The 'Right trim' option is not currently supported by the FS \sqlexport command, nor the ADS 'Export Data' tool.