NETProvider
NETProvider copied to clipboard
FirebirdSQL
When command contains comment and there is a "@something" you got error that you need to declare parameter [DNET176]
Submitted by: @cincuranet
Relate to DNET564 Is related to DNET761
Attachments: FbCommand.patch FbCommand.patch
Votes: 2
When command contains comment and there is a "@something" you got error that you need to declare parameter.
I.e.: select foo from bar --abc @xyz
Commented by: Gareth Goslett (gareth)
A patch to fix this issue, not extensively tested.
The comment text is removed from the statement but the comment characters '--' are not. Would it be more efficient to parse the complete statement, removing whitespace, carriage returns and comments?
One reason to keep comments in is that you can now view the statement from the monitoring tables.
I ran the following "sql" text and it executed fine.
"INSERT --An insert statement\r\n" + " INTO -- Define the table next\r\n" + " TESTING_PK\r\r\n--The table\r\n" + " ( -- Separator @data1 \r\n" + " FIELD_01, -- First field, links to @FIELD_01\r\n" + " FIELD_02 -- Second field, links to @FIELD_02\r\n" + " ) -- Separator @data2 \r\n" + " VALUES" + " ( -- Separator @data3 \r\n" + " COALESCE((SELECT MAX(FIELD_01) FROM TESTING_PK) + @FIELD_01, 1), --@FIELD_01 - Get the next --@Max of field.\r\n" + " '--' || @FIELD_02 || '--'--@Add text parameter\r\n" + " ) -- Separator @data4 \r\n" + " RETURNING FIELD_01-- Comment @td"
Modified by: Gareth Goslett (gareth)
Attachment: FbCommand.patch [ 11373 ]
Commented by: @cincuranet
First, the patch is wrong. Try query like "select 'foo' from rdb$database /* @foo */". Secondly, I don't wanna to go this way. We need fast, simple and reliable way to detect and skip (the comment can be pretty long) comments in query, not to extending the method with thousands of ifs.
Commented by: Gareth Goslett (gareth)
Can we strip comments and control characters entirely, only send the query text to the server ? If so, we can first strip all comments and others, then parse for parameters.
I was not aware that firebird handled the c style comments, I thaught it only allowed the '--' comment characters, thanks.
Commented by: @cincuranet
We can, if somebody creates some reliable function for it. But for now, I don't see this as a big issue.
Commented by: @cincuranet
This is final version from the mailing list thread [http://www.nabble.com/FbCommand-sql-parser-td22215029.html] from Gareth Goslett.
Looks good, I'll just create couple of coding style tweaks.
string ParseNamedParameters(string sql) { // Clear old parameters. this.namedParameters.Clear();
// We do not need to parse if there are no parameters\.
if \(sql\.IndexOf\('@'\) == \-1\)
\{
return sql;
\}
int index;
int end = sql\.Length \- 1;
StringBuilder resultBuilder = new StringBuilder\(sql\.Length\);
for \(int i = 0, j = sql\.Length; i < j; i\+\+\)
\{
switch \(sql\[i\]\)
\{
// Read a named parameter\.
case '@':
for \(index = i \+ 1; index < j &&
(Char.IsLetterOrDigit(sql[index]) || sql[index] == '_' || sql[index] == '$'); index++); resultBuilder.Append('?'); this.namedParameters.Add(sql.Substring(i, index - i)); i = index - 1; continue;
// Read quoted text\.
case '\\'':
case '"':
for \(index = i \+ 1; index < j && sql\[index\] \!= sql\[i\]; index\+\+\);
if\(index \>= j\)
\{
resultBuilder\.Append\(sql\.Substring\(i\)\);
\}
else
\{
resultBuilder\.Append\(sql\.Substring\(i, index \+ 1 \- i\)\);
\}
i = index;
continue;
// Read a single line comment\.
case '\-':
if \(\(i < end\) && \(sql\[i \+ 1\] == '\-'\)\)
\{
for \(index = i \+ 1; index < j && \(sql\[index\] \!= '\\r'
&& sql[index] != '\n'); index++); resultBuilder.Append(sql.Substring(i, index - i)); i = index - 1; continue; }
resultBuilder\.Append\('\-'\);
continue;
// Read a block comment\.
case '/':
if \(\(i < end\) && \(sql\[i \+ 1\] == '\*'\)\)
\{
while \(i < j\)
\{
resultBuilder\.Append\(sql\[i\]\);
if \(sql\[i\] == '\*' && i < end && sql\[i \+ 1\] == '/'\)
\{
break;
\}
i\+\+;
\}
continue;
\}
resultBuilder\.Append\('/'\);
continue;
// Read content\.
default:
resultBuilder\.Append\(sql\[i\]\);
continue;
\}
\}
return resultBuilder\.ToString\(\);
\}
Modified by: Gareth Goslett (gareth)
Attachment: FbCommand.patch [ 11440 ]