Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: [FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Nov 7, 2017 2:44 AM Last Post By: Dmitry Vavilov
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
[FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 1, 2017 4:07 AM
The FDQuery component specifies the SQL text of the request:
 
SELECT * FROM (
     SELECT ID, Name FROM Table1
     UNION ALL
     SELECT ID, Name FROM Table2
) as Table1


After importing the fields, at each field I set
 
ReadOnly = False


But I can not add new records to the table, because error occurs "[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'ID', table 'Table1'; column does not allow nulls. INSERT fails".
I found that the request to insert records incorrectly.
 
INSERT INTO Table1 DEFAULT VALUES


How to solve a problem?
Robert Triest

Posts: 687
Registered: 3/24/05
Re: [FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 1, 2017 5:07 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
See if your insert sql follows the update rules:

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Changing_DataSet_Data_(FireDAC)

See if RequestLive property must be set on true..
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: [FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 1, 2017 9:30 PM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:
See if your insert sql follows the update rules:

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Changing_DataSet_Data_(FireDAC)

See if RequestLive property must be set on true..

Not really!
The SELECT must be without the DISTINCT, GROUP BY, UNION, etc phrases. That is a logical rule, rather than "a must".

I use UNION and I can not avoid this.
The problem is in FDQuery1.Table.Columns [n] .Attributes. It contains the flag "caReadOnly" even if I set the ReadOnly = False property of the field. Because of this, new values for fields are ignored on insertion.
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: [FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 1, 2017 10:15 PM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
All works well if FDQuery1 in the AfterOpen event handler specify the code
 
procedure TForm1.FDQuery1AfterOpen(DataSet: TDataSet);
var i: Integer;
begin
  for i := 0 to FDQuery1.Table.Columns.Count - 1 do
  begin
    if (caReadOnly in FDQuery1.Table.Columns[i].Attributes) and (FDQuery1.Table.Columns[i].ReadOnly = False) then
      FDQuery1.Table.Columns[i].Attributes := FDQuery1.Table.Columns[i].Attributes - [caReadOnly];
  end;
end;

But this should be checked in the component itself
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: [FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 5, 2017 2:26 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
To enable updates to a field, set ReadOnly = False and include pfInUpdates into ProviderFlags.

--
With best regards,
Dmitry
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: [FDQuery + MS SQL]Can not insert records if SQL text contains UNION ALL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 7, 2017 2:44 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
To enable updates to a field, set ReadOnly = False and include pfInUpdates into ProviderFlags.

--
With best regards,
Dmitry

The field is set up like you said, but the problem remained. The problem is solved only after manual adjustment of the attributes of the fields after opening the data set.
for i := 0 to FDQuery1.Table.Columns.Count - 1 do
begin
  if (caReadOnly in FDQuery1.Table.Columns[i].Attributes) and (FDQuery1.Table.Columns[i].ReadOnly = False) then
    FDQuery1.Table.Columns[i].Attributes := FDQuery1.Table.Columns[i].Attributes - [caReadOnly];
end;
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02