Devart Forums

OlliWW

Hi,

we are using SDAC 10.1.1 at the moment. We already discoverd the Bug in 9.4.3. If you use a stored procedure in a BatchExec with an insert statement, it crashes with:

inorrect Syntax near ")"

update statements are working fine. Also only crashes if rowcount > 1. With 1 row it works, too.

Could you assist me, to resolve this issue?

Greetings from Germany

Code:

Code: Select all

unit SDAC;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBAccess, SdacVcl, Data.DB, MSAccess;

type
  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
  private
    MSConnection1: TMSConnection;
    MSConnectDialog1: TMSConnectDialog;
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const
  cTempTableName = '#BatchTemp';

procedure TForm1.FormCreate(Sender: TObject);
var
  qryExec: TMSQuery;
  objParam: TMSParam;
begin
  MSConnection1.Connect;

  // Create Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text :=
      'create table ' + cTempTableName + ' ( ' +
        '_GEN INT IDENTITY(1, 1) primary key, ' +
        '_COL_STRING varchar(max), ' +
        '_COL_DATE datetime ' +
      ')';
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;


  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    //##### Query only crashes by using insert (update works)
    qryExec.SQL.Text := 'insert into ' + cTempTableName + ' (_COL_DATE, _COL_STRING) values (getDate(), :STRING) ';

    qryExec.Params.ValueCount := 1;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[0].Value := 'Hello';

    qryExec.Params.ValueCount := 2;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[1].Value := 'World';

    qryExec.Params.ValueCount := 3;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[2].Value := 'Foobar';

    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;


  // Drop Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text := 'drop table ' + cTempTableName;
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;
end;

end.

OlliWW

Here is the sample code, tests with SDAC 10.0.2 and SQL Server 2019.

The issue occours if a field is "0x". In SDAC 9.3. and lower the bug does not exists. AsString returns a empty string.
After 9.3. you get an access violation.

I'll send you the code also via email.

Code: Select all

unit Unit3;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBAccess, SdacVcl, Data.DB, MSAccess,
  CREncryption;

type
  TForm3 = class(TForm)
    MSConnection1: TMSConnection;
    MSConnectDialog1: TMSConnectDialog;
    MSEncryptor1: TMSEncryptor;
    procedure FormCreate(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

const
  cTempTableName = '#EncryptionTemp';

procedure TForm3.FormCreate(Sender: TObject);
var
  qryExec: TMSQuery;
  myStringVar: String;
begin
  MSConnection1.Connect;

  // Create Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text :=
      'create table ' + cTempTableName + ' ( ' +
        '_GEN INT IDENTITY(1, 1) primary key , ' +
        '_COL_TEST varbinary(200) NULL ' +
      ')';
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;


  // Insert "0x" into the VarBinary Field
  qryExec := TMSQuery.Create(nil);
  try
    qryExec.Connection := MSConnection1;
    qryExec.SQL.Text := 'insert into ' + cTempTableName + ' (_COL_TEST) values (CONVERT(varbinary(10),''''))';
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;

  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text := 'SELECT _COL_TEST FROM ' + cTempTableName;

    qryExec.Encryption.Encryptor := MSEncryptor1;
    qryExec.Encryption.Fields := '_COL_TEST';
    MSEncryptor1.Password := '11111';

    qryExec.DataTypeMap.AddFieldNameRule ('_COL_TEST', ftString);

    qryExec.Open;

    // Set Breakpoint here. The "AsString" Function results in a access violation
    myStringVar := qryExec.FieldByName('_COL_TEST').AsString;
  finally
    FreeAndNil(qryExec);
  end;


  // Drop Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text := 'drop table ' + cTempTableName;
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;
end;

end.

OlliWW

Here is a sample code, read the comments in the code, i'll send it to you via contact form as well.

This code works up to 9.3.x. After 9.3. not.

Code: Select all

unit Unit3;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBAccess, SdacVcl, Data.DB, MSAccess;

type
  TForm3 = class(TForm)
    MSConnection1: TMSConnection;
    MSConnectDialog1: TMSConnectDialog;
    procedure FormCreate(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

const
  cTempTableName = '#BatchTemp';

procedure TForm3.FormCreate(Sender: TObject);
var
  qryExec: TMSQuery;
  objParam: TMSParam;
begin
  MSConnection1.Connect;

  // Create Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text :=
      'create table ' + cTempTableName + ' ( ' +
        '_GEN INT IDENTITY(1, 1) primary key , ' +
        '_COL_STRING varchar(max) ' +
      ')';
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;


  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    //#####################
    // This Part is normally a procedure to dynamiclly add Values
    // For simplification i replicated the code 3 times

    qryExec.SQL.Text := 'insert into ' + cTempTableName + ' (_COL_STRING) values (:STRING) ';

    qryExec.Params.ValueCount := 1;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[0].Value := 'Hello';

    qryExec.Params.ValueCount := 2;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[1].Value := 'World';

    // Set a Breakpoint BEFORE here
    // Watch the values: objParam.Values[1].Value AND objParam.Values[0].Value
    // 0 contains "Hello" and 1 contains "World"
    // After the next row 0 and 1 are "not set", it happens wenn you execute this row:
    qryExec.Params.ValueCount := 3;
    objParam := qryExec.ParamByName('STRING');
    objParam.DataType := ftMemo;
    objParam.Values[2].Value := 'Foobar';

    // No need to execute the BatchExecute because the values are already wrong...

    //#####################
  finally
    FreeAndNil(qryExec);
  end;


  // Drop Temp Table
  qryExec := TMSQuery.Create(nil);
  qryExec.Connection := MSConnection1;
  try
    qryExec.SQL.Text := 'drop table ' + cTempTableName;
    qryExec.Execute;
  finally
    FreeAndNil(qryExec);
  end;
end;

end.

OlliWW

This problem also exists in SDAC up to Version 10.0.2.

These errors are currently a show stopper for Delphi 11

OlliWW

Hi,

We are using SDAC 9.3.x at the moment. We testes 9.4.x and 10.x but we have some issues with batch executes:

We are using a procedure to add insert statements dynamicly to a batch execute.

We do this the following way:

Code: Select all

procedure BatchExec.prcSetAsString(const _sParam: String; _iRow: Integer; const _sValue: String);
var
  objParam: TMSParam;
begin
  qry.Parameters.ValueCount := iRowCount + 1;
  Inc(iRowCount);

  objParam := qry.ParamByName(_sParam);
  objParam.DataType := ftMemo;
  objParam.Values[_iRow].Value := _sValue;
end;

The problem is the first line:
If you increase the ValueCout Value String parameters are resetted. (Only string fields, other fields work just fine). In 9.3.x and lower there is no issue with this code. Since this issue only exists with string fields i think this is a bug.

Could you assist me, to resolve this issue?

Greetings from Germany

OlliWW

Hello,

I'm using SDAC 9.4.3 with Delphi 10.4.2

The previous version was: 9.3.1

Since 9.4.3 i get an access violation when i'm selecting encrpyted fields which are null (not filled with data [yet]).

My Code is exact the same as in the documentation. I'm reading the field with "FieldByName"

Code: Select all

MSQuery.SQL.Text := 'SELECT * FROM EMP';

MSQuery.Encryption.Encryptor := MSEncryptor;
MSQuery.Encryption.Fields := 'ENAME, HIREDATE, SAL, FOTO';
MSEncryptor.Password := '11111';

MSQuery.DataTypeMap.AddFieldNameRule ('ENAME', ftString);
MSQuery.DataTypeMap.AddFieldNameRule ('HIREDATE', ftDateTime);
MSQuery.DataTypeMap.AddFieldNameRule ('SAL', ftFloat);

MSQuery.Open;

myStringVar := MSQuery.FieldByName('ENAME').AsString;

When calling "FieldByName" i get an access violation.

Here is the callstack:

Code: Select all

0082d4e7 +003b Project.exe       CRDataTypeMap                  TDataConverters.ExtBytesToAStr
008632ea +00fa Project.exe       CRAccess                       TCRRecordSet.GetFieldData
007cb59f +00c3 Project.exe       MemData                        TData.GetField
00f7d0ce +002e Project.exe       OLEDBAccess                    TOLEDBRecordSet.GetField
007e57db +0083 Project.exe       MemDS                          TMemDataSet.GetFieldData
007e5592 +0016 Project.exe       MemDS                          TMemDataSet.GetFieldData
00799b53 +0027 Project.exe       Data.DB             13131   +2 TDataSet.GetFieldData
007e5613 +0047 Project.exe       MemDS                          TMemDataSet.GetFieldData
007876ab +0057 Project.exe       Data.DB              5149   +9 TField.GetData
00789767 +0013 Project.exe       Data.DB              6142   +1 TStringField.GetValue
0078954a +000a Project.exe       Data.DB              6073   +1 TStringField.GetAsAnsiString
0078950c +0020 Project.exe       Data.DB              6063   +2 TStringField.GetAsString
0172299d +1211 Project.exe       myClass       570 +282 TmyClass.doread

With 9.3.1 the code above works

OlliWW

Hi Stellar,

Thank you, thats what i've done as a workarround. But isn't the dataset manager needed?

OlliWW

Hi,

After todays upgrade from Delphi 10.4. to 10.4.1. i got an access violation (null pointer?) at delphi startup.

I the crash is in DataSetManager270.bpl. The only work arround at this time is to disable the DataSetManager270.bpl Package.

Sadly i'm not able to post an attachment.

If you need the bugreport generated by the ide, let me know.

Here are some snippets:

Code: Select all

exception class    : EAccessViolation
exception message  : Zugriffsverletzung bei Adresse 11E53DA4 in Modul 'DataSetManager270.bpl'. Lesen von Adresse 00000000.

...
thread $3dd4:
11e53da4 +018 DataSetManager270.bpl Dsmtoolwindow                GetPreferences
11e5365a +04e DataSetManager270.bpl Dsmtoolwindow                TDsmToolWindow.FormCreate
50e4fd89 +031 vcl270.bpl            Vcl.Forms          3986   +3 TCustomForm.DoCreate
50e4f965 +011 vcl270.bpl            Vcl.Forms          3867   +1 TCustomForm.AfterConstruction
50060379 +01d rtl270.bpl            System            19276   +2 @AfterConstruction
11e4b19d +019 DataSetManager270.bpl Dsmtoolwindow                TDsmToolWindow.CreateForm

OlliWW

Hi,

According to:
https://www.devart.com/sdac/docs/devart ... cation.htm
You have to use the SQL Native Client Provider to get Change Notifications working.

But Microsoft says that the native client is deprecated and you should use the MsSQL Ole DB Driver:
https://docs.microsoft.com/en-us/sql/re ... erver-2017

Are changenotifications supported with ole db driver, too? If no: Is it planed for the future? Is there an alternative to change notifications with native client? Since i'm implementing it right now in a new project i don't want to use methods that are deprecated since i'm developing a long term project.

Best regards

OlliWW

Hey,
Could you please provide me some informations, when the next version will be released? Because at the moment i can not encrypt fields.
If no new version is expected very soon: is there a workarround?

OlliWW

Hi Stellar,

Thank you for your response. I'll wait for further notice.

OlliWW

Hi,

I'm using Encryption with SDAC.

When updating, i'm using this statement:

Code: Select all

        qry.SQL.Text := 'select FIELD1, FIELD2 from dbo.MyTable;
        qry.Encryption.Fields := 'FIELD1, FIELD2;
        qry.DataTypeMap.AddFieldNameRule ('FIELD1', ftString);
        qry.DataTypeMap.AddFieldNameRule ('FIELD2', ftString);
        qry.Open;
        qry.Edit;
        qry.FieldByName('FIELD1').AsString := myString1;
        qry.FieldByName('FIELD2').AsString := myString2;
        qry.Post;

(Encriptor end encryption password is set earlier)

The tables fields are varbinary fields.

On some tables i get an acess violation in TData.InternalCompareFieldValue. Since i have no source code i can't debug the cause. Maby somebody has an idea?

Here is the full call stack:

Code: Select all

00961b13 +0ca3 MyApp.exe MemData                   TData.InternalCompareFieldValue
00962126 +0206 MyApp.exe MemData                   TData.CompareFieldValue
00a46c27 +0297 MyApp.exe DASQLGenerator            TDASQLGenerator.FieldModified
00a46d95 +0035 MyApp.exe DASQLGenerator            TDASQLGenerator.FieldModified
00a480de +006e MyApp.exe DASQLGenerator            TDASQLGenerator.GenerateUpdateSQL
01494d5f +008f MyApp.exe MSSQLGenerator            TCustomMSSQLGenerator.GenerateUpdateSQL
00a494c4 +0154 MyApp.exe DASQLGenerator            TDASQLGenerator.GenerateSQLforUpdTable
00a49725 +0115 MyApp.exe DASQLGenerator            TDASQLGenerator.GenerateSQL
00a82412 +0562 MyApp.exe DBAccess                  TDADataSetUpdater.CheckUpdateSQL
00a85d9f +008f MyApp.exe DBAccess                  TDADataSetUpdater.PerformSQL
00a8736d +011d MyApp.exe DBAccess                  TDADataSetUpdater.PerformUpdateDelete
00a87550 +0010 MyApp.exe DBAccess                  TDADataSetUpdater.PerformUpdate
0098afa0 +0040 MyApp.exe MemDS                     TDataSetUpdater.DoPerformUpdate
00983b3b +001b MyApp.exe MemDS                     TMemDataSet.DoPerformUpdate
0095e3b4 +0014 MyApp.exe MemData                   TData.InternalUpdate
01479dec +001c MyApp.exe OLEDBAccess               TOLEDBRecordSet.InternalUpdate
00967f97 +0057 MyApp.exe MemData                   TMemData.UpdateRecord
01340d34 +0004 MyApp.exe SqlClasses                TSqlRecordSet.UpdateRecord
0095e1ae +006e MyApp.exe MemData                   TData.PostRecord
00967c7c +004c MyApp.exe MemData                   TMemData.PostRecord
00982e59 +0169 MyApp.exe MemDS                     TMemDataSet.InternalPost
00a75993 +0123 MyApp.exe DBAccess                  TCustomDADataSet.InternalPost
0091a310 +0040 MyApp.exe Data.DB                   TDataSet.CheckOperation
00919bb2 +0062 MyApp.exe Data.DB                   TDataSet.Post
00982f4c +000c MyApp.exe MemDS                     TMemDataSet.Post
014be8fb +000b MyApp.exe MSAccess                  TCustomMSDataSet.Post
0529205e +2bbe MyApp.exe Objects 1116 +558 TMyObject.prcSave

OlliWW

Hi Stellar,

I've found the problem:
Some of my tables contianed a computed column - which is not allowed.

OlliWW

Hi Stellar,

Thank you for your response. I had already knowlage over that microsoft link.

My Query is always the same:

select primary_key from table

very simple. That works for one table but not for the other table on the same database.

On the not working table i get the result before the error. and it fires a (invalid) query notification (type: query).

Here is a link what it looks like:
https://imgur.com/vHwoSxo

OlliWW

Hi,

I'm currently playing with change notifications in SDAC.

My Database consits of two example tables. I do very simple change notification - queries, like:
select <primary key> from dbo.mytable

With table1 everything works fine. On table2 (on the same database) my changenotification fires on open:

'SELECT statement does not meet the requirements for query notification'

What requierements are exactly meant?

Edit:
Some more information: When i try this on a table which is not working it imediatly fires a query notification of the type "query"

Devart Forums

Search found 25 matches

Stored Procedure in BatchExecute crashes

Re: Encrypted Fields Access Violation after Update to 9.4.3

Re: BatchExecute issues since 9.4.X

Re: Encrypted Fields Access Violation after Update to 9.4.3

BatchExecute issues since 9.4.X

Encrypted Fields Access Violation after Update to 9.4.3

Re: SDAC Startup Error after Update to 10.4.1

SDAC Startup Error after Update to 10.4.1

TMSChangeNotification and NativeClient

Re: Encrytion causes access violation

Re: Encrytion causes access violation

Encrytion causes access violation

Re: Changenotifications not working on some tables

Re: Changenotifications not working on some tables

Changenotifications not working on some tables