Below is an SQL script for SQL Server. It will create a table with a nvarchar column and insert a row with a control character of NCHAR(0x81).
In FluidShell I then try to export the table and data to a file with INSERT statements and then import it back by running it through the source command. I use the following commands :
sqlexport --output-file savedfile.txt --format 'INSERT' -II true -OQ 'square-bracket' --charset 'UTF-8' ControlNiels
source --charset 'UTF-8' savedfile.txt
The error message I get is :
source: savedfile.txt: Script error on line 2:
Command line contains control character(s) which is not allowed.
I've attached a screenshot illustrating the issue.
The exception is thrown by CommandLineInterpreter.java line 1352. It also references issues #7098 & #7224.
The original issue was #7009. The TerminalControlChar.java file specifically filters the characters with :
public static boolean isControlChar(int code) {
return (isC0Set(code) || isC1Set(code));
}
public static boolean isC0Set(int code) {
// C0 set is 00-1F (in hex)
return isInRange(code, 0x00, 0x1F);
}
public static boolean isC1Set(int code) {
// C1 set is 80-9F (in hex)
return isInRange(code, 0x80, 0x9F);
}
========= SQL Script ===========
drop table [dbo].[ControlNiels]
go
CREATE TABLE [dbo].[ControlNiels] (
[ControlId] int NOT NULL,
[Company] nvarchar(80) NULL,
CONSTRAINT [PK_ControlNiels] PRIMARY KEY CLUSTERED([ControlId])
)
go
INSERT INTO [Chinook].[dbo].[ControlNiels]([ControlId], [Company])
VALUES(1, N'AquaFold' + NCHAR(0x81))
GO
select * from ControlNiels
60 KB
What would be the alternative to using the "source" command if we want to export and then import data from a table where a NVARCHAR column has control characters?
This seems to work :
sqlexport --output-file savedfile.csv --format 'csv' -II true -OQ 'square-bracket' --charset 'UTF-8' ControlNiels
sqlimport --format csv ControlNiels savedfile.csv
I reviewed related issues and source code:
(1) Issue #7009 says when writing a string to fluid shell terminal (e.g. cat filename), C0/C1 control chars (C0: 00-1F, C1: 80-9F in hex) defined by ISO-8859 (extended ASCII) table cannot be included; otherwise, the vt220/xterm interpreter will mess its state and unexpected behavior will result. See this link for details.
(2) Issue #7098 indicates when reading a command from fluid shell terminal for execution, the command itself cannot contain control chars; otherwise, terminal emulator might not be able to process the command string and exception will be thrown. See this link for stack trace of exception.
The fix of #7009 will remove C0/C1 control chars from a string before sending it to terminal. The fix of #7098 does not allow a command to have control chars embedded. The only exception is CR and LF, they will be passed through. Issue #7224 allows TAB char to pass through as well.
The exception is thrown by CommandLineInterpreter.java line 1352. It also references issues #7098 & #7224.
This is a check added by #7098 for taking care of item #2 above.
sqlimport --format csv ControlNiels savedfile.csv
This works because it has nothing to do with item (1) or item (2) above.
It seems to me there is nothing we can modify to get source command work in this particular case, sqlimport probably is the only alternative we have now.
I reviewed related issues and source code:
(1) Issue #7009 says when writing a string to fluid shell terminal (e.g. cat filename), C0/C1 control chars (C0: 00-1F, C1: 80-9F in hex) defined by ISO-8859 (extended ASCII) table cannot be included; otherwise, the vt220/xterm interpreter will mess its state and unexpected behavior will result. See this link for details.
(2) Issue #7098 indicates when reading a command from fluid shell terminal for execution, the command itself cannot contain control chars; otherwise, terminal emulator might not be able to process the command string and exception will be thrown. See this link for stack trace of exception.
The fix of #7009 will remove C0/C1 control chars from a string before sending it to terminal. The fix of #7098 does not allow a command to have control chars embedded. The only exception is CR and LF, they will be passed through. Issue #7224 allows TAB char to pass through as well.
The exception is thrown by CommandLineInterpreter.java line 1352. It also references issues #7098 & #7224.
This is a check added by #7098 for taking care of item #2 above.
sqlimport --format csv ControlNiels savedfile.csv
This works because it has nothing to do with item (1) or item (2) above.
It seems to me there is nothing we can modify to get source command work in this particular case, sqlimport probably is the only alternative we have now.
Issue #14236 |
New |
Completion |
No due date |
No fixed build |
No time estimate |
3 issue links |
relates to #7224
Issue #7224TAB char not allowed inside comment lines |
relates to #7098
Issue #7098source command and source files like pdf, gif gives NPE |
relates to #7009
Issue #7009NPE on cat command |
What would be the alternative to using the "source" command if we want to export and then import data from a table where a NVARCHAR column has control characters?
This seems to work :
sqlexport --output-file savedfile.csv --format 'csv' -II true -OQ 'square-bracket' --charset 'UTF-8' ControlNiels
sqlimport --format csv ControlNiels savedfile.csv