FireDAC connection lost on setting TFDQuery's SQL.Text

[Mark Williams 14]

I am using FireDAC component within an ISAPI DLL to access a PostgreSQL database

 

I create the FDMAnager component on startup:

       

FDManager := TFDManager.Create(Nil);
        with FDManager do
        begin
          ResourceOptions.SilentMode := true;
          ResourceOptions.AutoReconnect := true;
          UpdateOptions.CheckRequired := true;
          UpdateOptions.CheckReadOnly := false;
          UpdateOptions.CheckUpdatable := false;
          AddConnectionDef(FD_POOLED_CONN, 'PG', oParams);
          FDManager.Active := true;
        end;

Each function then creates a TFDQuery component as required and connects to the pooled connection. I then commence a series of transactions within a try...finally block with a rollback if one of the transaction fails. This works fine except I am encountering odd behaviour within one of the transactions. The connection is lost after setting the query's sql.text property even though there is no error when actually setting the query. I get an error when executing the query and this is due to my client app providing the wrong data. This I can fix. I am more concerned as to why the setting of the query property should cause the connection to be lost without without an error being triggered. The relevant code is:

try
	With FDQuery do
	begin
  		Connection.StartTransaction;

            if length(insertArr) > 0 then
            begin
              AddToLog('1 Before call queryConnection assigned=' +
                ord(assigned(Connection)).ToString, leDevelopment); ///connection assigned here
             
              try
                SQL.Text :=
                  'INSERT INTO doc_cat_profiles_detail (doc_cats_id,  priority, '
                  + 'auto_disclose, profile_id) VALUES(:DOCCAT, :PRIORITY, :AUTOD, :PROFID)';
                AddToLog('SET SQL OK', leDevelopment); //quuery sets fine here
              except
                on E: Exception do
                  AddToLog('Exception while setting SQL: ' + E.Message,
                    leCriticalError);
              end;
                        
              AddToLog('2 Before call query Connection assigned=' +
                ord(assigned(Connection)).ToString, leDevelopment); //Connection is lost here

              Params.BindMode := pbByNumber;
              Params[0].DataType := ftInteger;
              Params[1].DataType := ftInteger;
              Params[2].DataType := ftSmallInt;
              Params[3].DataType := ftInteger;
            
              Params.ArraySize := length(insertArr);

			  for i := 0 to high(insertArr) do
              begin
                Params[0].AsIntegers[i] := Profile[insertArr[i]].doc_cat;
                Params[1].AsIntegers[i] := Profile[insertArr[i]].Priority;
                Params[2].AsSmallInts[i] := Profile[insertArr[i]].auto_disclose;
                Params[3].AsIntegers[i] := Profile[insertArr[i]].profile_id;                
              end;

              try
                execute(Params.ArraySize);
              except
                on E: Exception do
                begin
                  AddToLog('SaveCategoriesAndProfile - Failed on insert new profile detail'
                    + '. ' + E.Message, leCriticalError);
                  Success := false;
                  exit;
                end;
              end;
            end;

		 Success := true;
       finally
            if Success then
            begin
              Connection.Commit;
              SetResponse(Response, 200, 'Success');
            end
            else
            begin
              if Connection.InTransaction then
                Connection.Rollback;
               SetResponse(Response, 500, 'Internal Server Error',
                  'SaveCategoriesAndProfile failed', leCriticalError);
            end;
		end;

The query executes with this error: :insert or update on table "doc_cat_profiles_detail" violates foreign key constraint "ct_delete_prof_details".

 

II am providing invalid profile-ids in the client app. This I can sort. But I can't figure out why setting the SQL.Text property is causing the connection to drop. A FireDAC trace shows nothing. The PostgreSQL log logs the error re the foreign key constraint, but mention no loss of connection.

 

I've added code following the setting of the sql.text:

              if not assigned(Connection) then
              begin
                Connection := FDManager.FindConnection('pooled_connection');
                // Restore the connection
                AddToLog('Connection re-assigned after SQL.Text!',
                  leDevelopment);
              end;

This appears to solve this particular issue, but I am concerned that I may encounter this issue elsewhere in my DLL (which is chunky) and, as it effectively causes the DLL to hang in terms of database communication that could be a major issue.

 

[Zoran Bonuš 15]

Setting SQL forces the FDQuery to close. My guess is that in combination with pooled connection, it might release the connection too. Have you tried setting the SQL before starting the transaction or even setting the FDQuery.Connection ?

 

You might try to debug and trace into the line where you set the SQL to see what after-effects it has in your context.

[Mark Williams 14]

 

1 hour ago, Zoran Bonuš said:

Setting SQL forces the FDQuery to close. My guess is that in combination with pooled connection, it might release the connection too. 

That makes sense. Then presumably calling execute reopens the connection, but this is not happening in this case due to the breach of the specific constraint?

 

1 hour ago, Zoran Bonuš said:

Have you tried setting the SQL before starting the transaction 

 

 

I haven't tried setting the SQL before starting the transaction. As there are a series of transactions with rollback on fail, it needs to come after StartTransaction.

 

1 hour ago, Zoran Bonuš said:

or even setting the FDQuery.Connection ?

Is that not what the additional code I added (FindConnection) does?

 

1 hour ago, Zoran Bonuš said:

You might try to debug and trace into the line where you set the SQL to see what after-effects it has in your context.

I've tried debugging, tracing, around the setting of the SQL it provides no information whatsoever. 

 

Thinking about it, it seems that the most sensible thing to do is to check in the finally block whether or not the connection has been dropped and restore it with FindConnection if needs be before calling commit or rollback,

 

Thanks for the insight.

Edited 6 hours ago by Mark Williams

[Roger Cigol 132]

2 hours ago, Mark Williams said:

Setting SQL forces the FDQuery to close. My guess is that in combination with pooled connection, it might release the connection too. 

Closing a query does not close the corresponding database connection (this applies if it is pooled connection or not pooled)

[Olli73 6]

What is the setting for Connection.ResourceOptions.KeepConnection ?