Incorrect syntax near <changes>

FredS · Post by **FredS** » Tue 10 Jul 2018 23:23

Using 7.3.8 (09-Jul-2018) I get this error on MSSQL, tested on LocalDB and Express.

Works fine with the only version that has run without errors: 7.1.4 (29-Sep-2017).
7.2.7 (11-Apr-2018) has the same issue, but I didn't use that due to some other bug.

Monitor Log section with an entry that shows the actual data, no PIPE char.

Code: Select all

:FOLDERID(LargeInt)=<ARRAY[10]> 
:USERGROUPSID(WideString)=<ARRAY[10]> 
:ACCESS(WideString)=<ARRAY[10]> 
:ACEFLAGS(LargeInt)=<ARRAY[10]> 
:MASK(LargeInt)=<ARRAY[10]> Pending
  7/10/2018 3:56:20 PM 0.000 Bulk INSERT INTO FOLDERACCESSLIST values (449682, 'S-1-5-21-1252972849-895940921-1336054014-500', 'A', 4294967294, 2032127) Complete
  7/10/2018 3:56:20 PM 0.000 Bulk INSERT INTO FOLDERACCESSLIST values (449682, 'S-1-1-0', 'A', 19, 2032127)Error: Incorrect syntax near '|'.

more from another Azure test:

Code: Select all

main thread ($a6c), inner exception level 1: <priority:2>
>> EMSError, Incorrect syntax near '-'.
02136a66 PA.exe OLEDBAccessUni TOLEDBConnector.OLEDBError

Back to 7.1.4 (29-Sep-2017)..

FredS · Post by **FredS** » Wed 11 Jul 2018 01:59

I am pretty sure the issue is with multi threading because examining the log shows that the error occurs on the first simultaneous threaded Bulk Insert.

First the working one:

Code: Select all

[size=50]
  7/10/2018 6:44:44 PM 0.000 Bulk (1|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-5-32-544', 'A', 4294967294, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (1|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-5-21-1252972849-895940921-1336054014-500', 'A', 4294967294, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (2|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-1-0', 'A', 19, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (2|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-1-0', 'A', 19, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (3|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-5-32-544', 'A', 16, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (4|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-3-0', 'A', 27, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (3|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-5-21-1252972849-895940921-1336054014-500', 'A', 16, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (5|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-5-21-1252972849-895940921-1336054014-513', 'A', 16, 1179817) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (4|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-3-0', 'A', 27, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (6|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-3-1', 'A', 27, 1179817) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (5|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-5-21-1252972849-895940921-1336054014-513', 'A', 16, 1179817) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (7|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-5-21-1252972849-895940921-1336054014-512', 'A', 19, 1245631) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (6|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-3-1', 'A', 27, 1179817) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (8|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-5-21-1252972849-895940921-1336054014-514', 'A', 19, 1179817) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (7|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-5-21-1252972849-895940921-1336054014-512', 'A', 19, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk (9|$2A48) INSERT INTO FOLDERACCESSLIST values (533940, 'S-1-5-32-544', 'A', 19, 2032127) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk ($2A48) FOLDERACCESSLIST.Execute(9) Complete
  7/10/2018 6:44:44 PM 0.000 SQL Execute: INSERT INTO FOLDERACCESSLIST values (:FOLDERID, :USERGROUPSID, :ACCESS, :ACEFLAGS, :MASK)
.. irrelevant parts removed..
  7/10/2018 6:44:44 PM 0.000 Bulk (8|$207C) INSERT INTO FOLDERACCESSLIST values (533941, 'S-1-5-21-1252972849-895940921-1336054014-513', 'A', 19, 1245631) Complete
  7/10/2018 6:44:44 PM 0.000 Bulk ($207C) FOLDERACCESSLIST.Execute(8) Complete[/size]

Now the first collision using 7.3.8:

Code: Select all

 [size=50] 7/10/2018 6:12:14 PM 0.000 Bulk (1|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-5-32-544', 'A', 4294967294, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (1|$22BC) INSERT INTO FOLDERACCESSLIST values (533639, 'S-1-1-0', 'A', 3, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (2|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-1-0', 'A', 19, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (3|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-5-32-544', 'A', 16, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (2|$22BC) INSERT INTO FOLDERACCESSLIST values (533639, 'S-1-3-0', 'A', 11, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (4|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-3-0', 'A', 27, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (3|$22BC) INSERT INTO FOLDERACCESSLIST values (533639, 'S-1-3-1', 'A', 11, 1179817) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (5|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-5-21-1252972849-895940921-1336054014-513', 'A', 16, 1179817) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (4|$22BC) INSERT INTO FOLDERACCESSLIST values (533639, 'S-1-5-21-1252972849-895940921-1336054014-512', 'A', 3, 2032127) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (6|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-3-1', 'A', 27, 1179817) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (5|$22BC) INSERT INTO FOLDERACCESSLIST values (533639, 'S-1-5-21-1252972849-895940921-1336054014-513', 'A', 3, 1245631) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk ($22BC) FOLDERACCESSLIST.Execute(5) Complete
  7/10/2018 6:12:14 PM n/a SQL Execute: INSERT INTO FOLDERACCESSLIST values (:FOLDERID, :USERGROUPSID, :ACCESS, :ACEFLAGS, :MASK)
.. irrelevant parts removed..
  7/10/2018 6:12:14 PM 0.000 Bulk (7|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-5-21-1252972849-895940921-1336054014-512', 'A', 19, 1245631) Complete
  7/10/2018 6:12:14 PM 0.000 Bulk (8|$120C) INSERT INTO FOLDERACCESSLIST values (533641, 'S-1-5-21-1252972849-895940921-1336054014-514', 'A', 19, 1179817)Error: Incorrect syntax near '-'.[/size]

Stellar · Post by **Stellar** » Wed 11 Jul 2018 12:25

Unfortunately, we can't reproduce the issue. To investigate this behavior of UniDAC, please compose a full sample demonstrating the issue and send it to us, including database objects creating scripts. You can send the project using the contact form at our website:
devart.com/company/contactform.html

FredS · Post by **FredS** » Tue 17 Jul 2018 20:03

Use Berlin 10.1.2 and Win64 to reproduce.
Do I get a free year for each bug submission and demo I have to build?

Table:

Code: Select all

[size=85]SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
Drop Table [dbo].[PrimeNumbers]
GO

CREATE TABLE [dbo].[PrimeNumbers](
	[ThreadID] [bigint],
	[Primes] [bigint],
	[Folder] nvarchar(4000),
	[SID] nvarchar(4000),
 CONSTRAINT [PK_PrimeNumbers] PRIMARY KEY CLUSTERED 
(
	[ThreadID] ASC,
	[Primes] ASC	
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY][/size]

Console App:

Code: Select all

[size=85]{$A8,B-,C+,D+,E-,F-,G+,H+,I+,J-,K-,L+,M-,N-,O+,P+,Q-,R-,S-,T-,U-,V+,W-,X+,Y+,Z1}
{$MINSTACKSIZE $00004000}
{$MAXSTACKSIZE $00100000}
{$IMAGEBASE $00400000}
{$APPTYPE GUI}
{$WARN SYMBOL_DEPRECATED ON}
{$WARN SYMBOL_LIBRARY ON}
{$WARN SYMBOL_PLATFORM ON}
{$WARN SYMBOL_EXPERIMENTAL ON}
{$WARN UNIT_LIBRARY ON}
{$WARN UNIT_PLATFORM ON}
{$WARN UNIT_DEPRECATED ON}
{$WARN UNIT_EXPERIMENTAL ON}
{$WARN HRESULT_COMPAT ON}
{$WARN HIDING_MEMBER ON}
{$WARN HIDDEN_VIRTUAL ON}
{$WARN GARBAGE ON}
{$WARN BOUNDS_ERROR ON}
{$WARN ZERO_NIL_COMPAT ON}
{$WARN STRING_CONST_TRUNCED ON}
{$WARN FOR_LOOP_VAR_VARPAR ON}
{$WARN TYPED_CONST_VARPAR ON}
{$WARN ASG_TO_TYPED_CONST ON}
{$WARN CASE_LABEL_RANGE ON}
{$WARN FOR_VARIABLE ON}
{$WARN CONSTRUCTING_ABSTRACT ON}
{$WARN COMPARISON_FALSE ON}
{$WARN COMPARISON_TRUE ON}
{$WARN COMPARING_SIGNED_UNSIGNED ON}
{$WARN COMBINING_SIGNED_UNSIGNED ON}
{$WARN UNSUPPORTED_CONSTRUCT ON}
{$WARN FILE_OPEN ON}
{$WARN FILE_OPEN_UNITSRC ON}
{$WARN BAD_GLOBAL_SYMBOL ON}
{$WARN DUPLICATE_CTOR_DTOR ON}
{$WARN INVALID_DIRECTIVE ON}
{$WARN PACKAGE_NO_LINK ON}
{$WARN PACKAGED_THREADVAR ON}
{$WARN IMPLICIT_IMPORT ON}
{$WARN HPPEMIT_IGNORED ON}
{$WARN NO_RETVAL ON}
{$WARN USE_BEFORE_DEF ON}
{$WARN FOR_LOOP_VAR_UNDEF ON}
{$WARN UNIT_NAME_MISMATCH ON}
{$WARN NO_CFG_FILE_FOUND ON}
{$WARN IMPLICIT_VARIANTS ON}
{$WARN UNICODE_TO_LOCALE ON}
{$WARN LOCALE_TO_UNICODE ON}
{$WARN IMAGEBASE_MULTIPLE ON}
{$WARN SUSPICIOUS_TYPECAST ON}
{$WARN PRIVATE_PROPACCESSOR ON}
{$WARN UNSAFE_TYPE OFF}
{$WARN UNSAFE_CODE OFF}
{$WARN UNSAFE_CAST OFF}
{$WARN OPTION_TRUNCATED ON}
{$WARN WIDECHAR_REDUCED ON}
{$WARN DUPLICATES_IGNORED ON}
{$WARN UNIT_INIT_SEQ ON}
{$WARN LOCAL_PINVOKE ON}
{$WARN MESSAGE_DIRECTIVE ON}
{$WARN TYPEINFO_IMPLICITLY_ADDED ON}
{$WARN RLINK_WARNING ON}
{$WARN IMPLICIT_STRING_CAST ON}
{$WARN IMPLICIT_STRING_CAST_LOSS ON}
{$WARN EXPLICIT_STRING_CAST OFF}
{$WARN EXPLICIT_STRING_CAST_LOSS OFF}
{$WARN CVT_WCHAR_TO_ACHAR ON}
{$WARN CVT_NARROWING_STRING_LOST ON}
{$WARN CVT_ACHAR_TO_WCHAR ON}
{$WARN CVT_WIDENING_STRING_LOST ON}
{$WARN NON_PORTABLE_TYPECAST ON}
{$WARN XML_WHITESPACE_NOT_ALLOWED ON}
{$WARN XML_UNKNOWN_ENTITY ON}
{$WARN XML_INVALID_NAME_START ON}
{$WARN XML_INVALID_NAME ON}
{$WARN XML_EXPECTED_CHARACTER ON}
{$WARN XML_CREF_NO_RESOLVE ON}
{$WARN XML_NO_PARM ON}
{$WARN XML_NO_MATCHING_PARM ON}
{$WARN IMMUTABLE_STRINGS OFF}
program ParallelErrConsole;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Classes, System.Threading, Winapi.ActiveX, Data.DB,
  MemDS, DBAccess, Uni, UniProvider, SQLServerUniProvider;

const
  MaxBulk = 10;
  Max = 150*1000;

function IsPrime(N: Integer): Boolean;
var
  Test, k: Integer;
begin
  if N <= 3 then IsPrime := N > 1
  else if ((N mod 2) = 0) or ((N mod 3) = 0) then IsPrime := False
  else begin
    IsPrime := True;
    k := Trunc(Sqrt(N));
    Test := 5;
    while Test <= k do begin
      if ((N mod Test) = 0) or ((N mod (Test + 2)) = 0) then begin
        IsPrime := False;
        break; { jump out of the for loop }
      end;
      Test := Test + 6;
    end;
  end;
end;

procedure CalcPrimes(const ATaskID: Integer; const quBulkInsert : TUniQuery);
var
  i, RecCount: LargeInt;
  Con: TUniConnection;
begin
  RecCount := 0;
  Con := quBulkInsert.Connection;
  Con.StartTransaction;
  for i := 1 to Max do begin
    if IsPrime(i) then begin
      quBulkInsert.Params[0][RecCount].asLargeInt := ATaskID;
      quBulkInsert.Params[1][RecCount].asLargeInt := i;
      quBulkInsert.Params[2][RecCount].asString := Format('\%d\%0:d\%0:d\%0:d', [i]);
      quBulkInsert.Params[3][RecCount].asString := Format('S-%d-%0:d-%0:d-%0:d', [i]);
      Inc(RecCount);
      if RecCount = MaxBulk then begin
        quBulkInsert.Execute(RecCount);
        Con.Commit;
        Con.StartTransaction;
        RecCount := 0
      end;
    end;
  end;
  quBulkInsert.Execute(RecCount);
  Con.Commit;
end;

procedure RunAsThread(const ATaskID: Integer; const AConnectString: String);
var
  Con: TUniConnection;
  quBulkInsert : TUniQuery;
begin
  CoInitializeEx(nil, COINIT_APARTMENTTHREADED);
  Con := TUniConnection.Create(nil, AConnectString);
  try
    Con.Connect;
    quBulkInsert := TUniQuery.create(nil);
    try
      quBulkInsert.Connection := Con;
      quBulkInsert.SQL.Text := 'INSERT INTO PrimeNumbers VALUES (:ThreadID, :Primes, :Folder, :SID)';
      quBulkInsert.Params[0].DataType := ftLargeint;
      quBulkInsert.Params[1].DataType := ftLargeint;
      quBulkInsert.Params[2].DataType := ftWideString;
      quBulkInsert.Params[3].DataType := ftWideString;
      quBulkInsert.Params.ValueCount  := MaxBulk;
      quBulkInsert.Prepare;
      CalcPrimes(ATaskID, quBulkInsert);
    finally
      quBulkInsert.Free;
    end;
    Con.Disconnect;
  finally
    Con.Free;
    CoUninitialize;
  end;
end;

var
  UniConnection: TUniConnection;
  TaskList : TArray<ITask>;
  LConnectString : string;
begin

  try
    CoInitializeEx(nil, COINIT_APARTMENTTHREADED);
    UniConnection := TUniConnection.Create(nil);
    try
      UniConnection.Server := 'VMW7';   // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
      UniConnection.ProviderName := 'SQL Server';
      UniConnection.Database := 'ParallelError';
      UniConnection.SpecificOptions.Add('SQL Server.Authentication=auWindows');
      UniConnection.LoginPrompt := False;
      UniConnection.Connect;
      LConnectString := UniConnection.ConnectString;
      UniConnection.ExecSQL('Delete PrimeNumbers');
      WriteLn('Calculating Prime Numbers....');

      TaskList := TaskList + [TTask.Run( procedure begin RunAsThread(1, LConnectString) end)];
      TaskList := TaskList + [TTask.Run( procedure begin RunAsThread(2, LConnectString) end)];
      TaskList := TaskList + [TTask.Run( procedure begin RunAsThread(3, LConnectString) end)];
      TaskList := TaskList + [TTask.Run( procedure begin RunAsThread(4, LConnectString) end)];
      TTask.WaitForAll(TaskList);

      WriteLn('Done!');
      ReadLn;
    finally
      UniConnection.Free;
      CoUninitialize;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.
[/size]

ViktorV · Post by **ViktorV** » Wed 18 Jul 2018 11:27

Thank you for the information. We have reproduced the problem and it will be fixed in the next build.

Devart Forums

Incorrect syntax near <changes>

Incorrect syntax near <changes>

Re: Incorrect syntax near <changes>

Re: Incorrect syntax near <changes>

Re: Incorrect syntax near <changes>

Re: Incorrect syntax near <changes>