Another notification problem

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Another notification problem

Post by LHSoft » Wed 02 Mar 2016 16:28

Hello,

in this never-ending-story I do have a new problem with notifications I do not understand.
On form1 I use notifications with some Tables, on Form2 I do not.
Recently I have added a small table on Form1 to use notifications.
This table can be modified on Form2, changes should be notified to all users with opened form1.
This works unless I do not insert or delete a row from the table.

So here is the log,
the table being in notification on form1 is TbKonLP,
TbKonGR2 is not in notification but in master-detail to TbKonLP
  • 17:16:23 624: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TBStmLie
    OWNER: Form2
    SELECT * FROM MoStmLie
    -----------------------------------------------------------------------------------------------------------
    17:16:23 659: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonLP
    OWNER: Form2
    SELECT * FROM MoKonLP
    WHERE Lieferantennummer = :Lieferantennummer
    :Lieferantennummer(Integer,IN)=103
    -----------------------------------------------------------------------------------------------------------
    17:16:26 085: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonLP
    OWNER: Form2
    DELETE FROM MoKonLP
    WHERE
    Lieferantennummer = ? AND Raster = ?

    :Old_Lieferantennummer(Integer,IN)=103
    :Old_Raster(WideString[5],IN)='1: 44'
    -----------------------------------------------------------------------------------------------------------
    17:16:26 087: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonGR2
    OWNER: Form2
    SELECT * FROM MoKonGR
    WHERE Bezeichnung = :Raster
    :Raster(WideString[0],IN)=<NULL>
    -----------------------------------------------------------------------------------------------------------
    17:16:26 088: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_90_QUEUE), TIMEOUT 2147483647
    -----------------------------------------------------------------------------------------------------------
    17:16:26 177: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonLP
    OWNER: Form1
    select Lieferantennummer,Raster,Gr01,Gr02,Gr03,Gr04,Gr05,Gr06,Gr07,Gr08,Gr09,Gr10,Gr11,Gr12,Gr13,Gr14,Gr15,Gr16 from dbo.MoKonLP
    -----------------------------------------------------------------------------------------------------------
    17:16:26: Exception:
    Statement does not support notifications

    EDatabaseError
    EDatabaseError
    -----------------------------------------------------------------------------------------------------------
    17:16:27 691: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_90_QUEUE), TIMEOUT 2147483647
    -----------------------------------------------------------------------------------------------------------
Again my question:
do you see anything problematic?

best regards
Hans

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Another notification problem

Post by LHSoft » Wed 02 Mar 2016 22:43

Hello,

I have found the problem: the table in notification was set to read only.
Can you tell me if this behavior is correct?
It looks suspicious cause this problem only occurs if in an table- or datasource event any other table is opened, even if this table is not in any notification.
So this works (FormLaqgBes/TbKonGR is in notification, others are not):
  • 22:50:52 352: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonGR
    OWNER: FormKoDat
    UPDATE MoKonGR
    SET
    Gr11 = ?
    WHERE
    Bezeichnung = ?

    :Gr11(WideString[1],IN)='w'
    :Old_Bezeichnung(WideString[7],IN)='10: 0-5'
    -----------------------------------------------------------------------------------------------------------
    22:50:52 355: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_51_QUEUE), TIMEOUT 2147483647
    -----------------------------------------------------------------------------------------------------------
    22:50:52 427: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonGR
    OWNER: FormLagBes <<-- notification
    select Bezeichnung,Gr01,Gr02,Gr03,Gr04,Gr05,Gr06,Gr07,Gr08,Gr09,Gr10,Gr11,Gr12,Gr13,Gr14,Gr15,Gr16 from dbo.MoKonGR
    -----------------------------------------------------------------------------------------------------------
but this does not work:
  • 22:47:40 479: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonGR
    OWNER: FormKoDat
    UPDATE MoKonGR
    SET
    Gr11 = ?
    WHERE
    Bezeichnung = ?

    :Gr11(WideString[1],IN)='w'
    :Old_Bezeichnung(WideString[7],IN)='10: 0-5'
    -----------------------------------------------------------------------------------------------------------
    22:47:40 482: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_51_QUEUE), TIMEOUT 2147483647
    -----------------------------------------------------------------------------------------------------------
    22:47:40 508: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    select SUM(Menge01), SUM(Menge02), SUM(Menge03), SUM(Menge04), SUM(Menge05), SUM(Menge06), SUM(Menge07), SUM(Menge08), SUM(Menge09), SUM(Menge10), SUM(Menge11), SUM(Menge12), SUM(Menge13), SUM(Menge14), SUM(Menge15), SUM(Menge16), SUM(SummeMenge) from MoLagMgB WHERE Raster='10: 0-5'
    -----------------------------------------------------------------------------------------------------------
    22:47:40 517: Flag: tfObjDestroy, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    Object destroyed: TMSQuery ($041380F0)
    -----------------------------------------------------------------------------------------------------------
    22:47:40 568: Flag: tfQExecute, Object: MSAccess/TMSTable: TMSTable
    Name: TbKonGR
    OWNER: FormLagBes <<-- notification
    select Bezeichnung,Gr01,Gr02,Gr03,Gr04,Gr05,Gr06,Gr07,Gr08,Gr09,Gr10,Gr11,Gr12,Gr13,Gr14,Gr15,Gr16 from dbo.MoKonGR
    -----------------------------------------------------------------------------------------------------------
    22:47:40: Exception:
    Statement does not support notifications

    EDatabaseError
    EDatabaseError
    -----------------------------------------------------------------------------------------------------------
    22:47:42 942: Flag: tfQExecute, Object: MSAccess/TMSQuery: TMSQuery
    Name:
    WAITFOR (RECEIVE message_body, status, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_contract_name, service_contract_id, message_type_name, message_type_id, validation FROM SDAC_NS_51_QUEUE), TIMEOUT 2147483647
    -----------------------------------------------------------------------------------------------------------
This listings are the same beside one query to another table which is not involved in notifications.


I have just run into another problem:
If setting a table to FetchAll=False and this table is not in notification and have this table open, all notifications on other forms will fail:
  • 23:19:06 405: Flag: tfTransact, Object: MSAccess/TCustomMSTransaction: TCustomMSTransaction
    Name:
    OWNER: SQLMain <<-- SQLMain: TMSConnection
    Start:
    -----------------------------------------------------------------------------------------------------------
    23:19:06 406: Flag: tfError, Object: OLEDBAccess/EMSError: Eine neue Transaktion ist unzulässig, weil in dieser Sitzung andere Threads ausgeführt werden.
    Error: Eine neue Transaktion ist unzulässig, weil in dieser Sitzung andere Threads ausgeführt werden.
    -----------------------------------------------------------------------------------------------------------
    23:19:06: Exception:
    Eine neue Transaktion ist unzulässig, weil in dieser Sitzung andere Threads ausgeführt werden.

    EMSError
    TDBAdvGlowButton
    -----------------------------------------------------------------------------------------------------------
Error translation: a new transaction is not allowed cause there are other threads in this session.
SQLMain is the connection to the Database

Can you tell me something about this second error?
It's very surprising for me.

best regards
Hans

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Another notification problem

Post by azyk » Thu 14 Apr 2016 12:46

In case of using Pessimistic Mode, on calling the TMSQuery.Edit method SDAC itself opens a transaction. If you open another dataset for notification after that not having closed the transaction, SQL Server will generate the error message you have specified. This is the behavior of SQL Server, which we cannot affect.

Post Reply