Issue Details (XML | Word | Printable)

Key: DNET-176
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Jiri Cincura
Reporter: Jiri Cincura
Votes: 2
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

When command contains comment and there is a "@something" you got error that you need to declare parameter

Created: 14/Sep/08 07:28 AM   Updated: 09/May/17 02:53 PM
Component/s: ADO.NET Provider
Affects Version/s: 2.5.0 Alpha 2
Fix Version/s: None

File Attachments: 1. Text File FbCommand.patch (5 kB)
2. Text File FbCommand.patch (1 kB)

Issue Links:
Relate


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Gareth Goslett added a comment - 20/Feb/09 07:59 PM
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"

Jiri Cincura added a comment - 21/Feb/09 08:22 AM
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.

Gareth Goslett added a comment - 21/Feb/09 06:51 PM
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.

Jiri Cincura added a comment - 22/Feb/09 06:14 AM
We can, if somebody creates some reliable function for it. But for now, I don't see this as a big issue.

Jiri Cincura added a comment - 12/Apr/09 07:45 AM
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();
        }

Gareth Goslett added a comment - 14/Apr/09 10:51 AM
Patch for svn, 908.