Preceding semicolon corrupts SQL script

[yaz_ozzie]

Devart version: 9.6.725.

We are struggling with a very specific issue relating to the placement of a semicolon at the start of a SQL script which is corrupting the next SQL statement.

Given the following example:

Code: Select all

var sql = @"
;

CREATE TABLE foo(
  new_col CHAR(2 BYTE) NOT NULL,
  CONSTRAINT countries_pk PRIMARY KEY (new_col)
);
";

var script = new OracleScript(sql);

The resulting script has random letters missing and looks like this:

Code: Select all

CREATE TABLE oo(
  new_col CHAR(2 BYTE) NOT NLL,
  CONSTRAINT countries_pk PRIMARY KEY (new_ol)
)

Removing the empty line between the semicolon and the CREATE TABLE statement results in another corrupted script but with different letters omitted. Removing the leading semicolon entirely, or moving it to the same line as the CREATE TABLE statement produces the correct script.

The full example script can be found here:

Code: Select all

using System;
using Devart.Data.Oracle;

namespace oraclescript_test
{
    class Program
    {
        static void Main(string[] args)
        {
            // open a dummy connection to satisfy licensing requirements
            var oracleConnection = Environment.GetEnvironmentVariable("ORACLE_CONNECTION");
            var devartLicenseKey = Environment.GetEnvironmentVariable("DEVART_LICENSE_KEY");
            var connection = new OracleConnection($"{oracleConnection};License Key={devartLicenseKey}");
            connection.Open();

            var sql1 = @"
;

CREATE TABLE foo(
  new_col CHAR(2 BYTE) NOT NULL,
  CONSTRAINT countries_pk PRIMARY KEY (new_col)
);
            ";

            var script1 = new OracleScript(sql1);

            foreach (OracleSqlStatement statement in script1.Statements)
            {
                Console.WriteLine("--------------");
                Console.WriteLine(statement.Text);
            }

            var sql2 = @"
;
CREATE TABLE foo(
  new_col CHAR(2 BYTE) NOT NULL,
  CONSTRAINT countries_pk PRIMARY KEY (new_col)
);
            ";

            var script2 = new OracleScript(sql2);

            foreach (OracleSqlStatement statement in script2.Statements)
            {
                Console.WriteLine("--------------");
                Console.WriteLine(statement.Text);
            }

            var sql3 = @"
;CREATE TABLE foo(
  new_col CHAR(2 BYTE) NOT NULL,
  CONSTRAINT countries_pk PRIMARY KEY (new_col)
);
            ";

            var script3 = new OracleScript(sql3);

            foreach (OracleSqlStatement statement in script3.Statements)
            {
                Console.WriteLine("--------------");
                Console.WriteLine(statement.Text);
            }
        }
    }
}

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

We have fixed the bug with processing line breaks by OracleScript, when a script contains lines of only a semicolon character. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[yaz_ozzie]

Thank you very much for the update! I look forward to the next build :)

[Pinturiccio]

New build of dotConnect for Oracle 9.7.770 is available for download.
It can be downloaded from https://www.devart.com/dotconnect/oracle/download.html (trial version) or from Devart Account (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=38884

[yaz_ozzie]

Thank you