Win process blocked when performing insert into DB2 database (ADOConnection)

Posted on 2008-11-16
Last Modified: 2013-11-23

I have developed a Delphi 7 application that writes information on a DB2 UDB AS400 database. I use an ADOConnection to be able to access the DB2 database, and an ADOQuery to write into the DB2 database.

The code is as follows:

  with ADOqrWrite_ERP_COM_SGA_ERP do
    Parameters.ParamByName('iID').Value             := iID ;
    Parameters.ParamByName('sMensID').Value         := sMensID ;
    Parameters.ParamByName('sMensPar1').Value       := sMensPar1 ;
    Parameters.ParamByName('sMensPar2').Value       := sMensPar2 ;
    Parameters.ParamByName('sMensPar3').Value       := sMensPar3 ;
    Parameters.ParamByName('sMensPar4').Value       := sMensPar4 ;
    Parameters.ParamByName('sMensPar5').Value       := sMensPar5 ;
    Parameters.ParamByName('sMensPar6').Value       := sMensPar6 ;
    Parameters.ParamByName('sMensPar7').Value       := sMensPar7 ;
    Parameters.ParamByName('sMensPar8').Value       := sMensPar8 ;
    Parameters.ParamByName('sMensPar9').Value       := sMensPar9 ;
    Parameters.ParamByName('sMensPar10').Value      := sMensPar10 ;
    Parameters.ParamByName('sEstado').Value         := sEstado ;
    Parameters.ParamByName('iEstProc').Value        := iEstProc ;
    Parameters.ParamByName('sTMPEmision').Value     := sTMPEmision;
    Parameters.ParamByName('sTMPTrato').Value       := sTMPTrato;

This code is inside a procedure, and previous to the call is ADOConnERP.BeginTrans (ADO connection) call, and after the procedure is ADOConnERP.CommitTrans call.

The SQL command to execute is as follows:

insert into COM_INBOX(secuencia,tipo,param1,param2,param3,
values (:iID,:sMensID,:sMensPar1,:sMensPar2,:sMensPar3,:sMensPar4,

(where COM_INBOX is a database table in the AS400 system)

The Connection String for the ADOConnection is:
Provider=IBMDA400.DataSource.1;Password="";Persist Security Info=True;User ID=sga;Data Source=;Protection Level=None;Extended Properties="";Initial Catalog=S655F80D;Transport Product=Client Access;SSL=DEFAULT;Force Translate=65535;Default Collection=SGA;Convert Date Time To Char=TRUE;Catalog Library List=QGPL;Cursor Sensitivity=3;Use SQL Packages=False;SQL Package Library Name="";SQL Package Name="";Add Statements To SQL Package=True;Unusable SQL Package Action=1;Block Fetch=True;Data Compression=True;Sort Sequence=0;Sort Table="";Sort Language ID="";Query Options File Library="";Trace=0;Hex Parser Option=0;Maximum Decimal Precision=31;Maximum Decimal Scale=31;Minimum Divide Scale=0

The problem is that the process gets blocked when performing the ExecSql operation (this can be seen when debbuging step by step the procedure). The process does not display any error message, it's just blocked, and it never goes to "Post" operation.

Also if you go to the DB2 database you can see the new row inserted by the process, but it seems to me that it's not completely OK, because there's a process in the AS400 system reading this rows and transfering them to another table, and with the rows inserted this way is not doing anything, they are not transfered.

When the process is forced to end an exception is returned (see error.jpg) picture. "It's not possible to make the operation while executing in asynchronous way)

It's my first time using ADOConnections and also I don't usually work with AS400 systems (I have no chance to change anything on that system). Any help will be very appreciated

Tanks to all

Question by:llorensL
    LVL 37

    Expert Comment

    how do you know the record was inserted?

    Author Comment

    I know it becasue I can see the record in the DB2 database (from my windows server with sqlexplorer software and an ODBC connection to the DB2 in the AS400 system).
    Also mention that the drivers used to connect to the AS400 are IBM iSeries Client Access (used to generate both ODBC connnection and TADOConnection connection string)
    LVL 37

    Expert Comment

    what happens if you cancel the operation? does the record remain in the database or is it deleted?

    Author Comment

    The record remains but no CommitTrans has been executed on my software. I think the problem is more: why the process gets blocked?.
    LVL 37

    Accepted Solution

    if your insert is processed then it is a good question why you get blocked
    i'd suggest you ask your dba to start a trace on the db2 part so he could tell you how long it takes to perform the insert statement and what happens after it

    Author Comment

    The record remains but no CommitTrans has been executed on my software. I think the problem is more: why the process gets blocked?.
    LVL 37

    Expert Comment

    you first need to find out if the process is connected to the database when it is blocked or not

    Author Closing Comment

    Finally the problem was on the db2 side, I didn't get to know what was has been done, but they changed something about libraries and it worked out. Thanks and regars

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
    Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
    THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
    The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now