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


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

Who is Participating?
momi_sabagConnect With a Mentor Commented:
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
how do you know the record was inserted?
llorensLAuthor Commented:
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)
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

what happens if you cancel the operation? does the record remain in the database or is it deleted?
llorensLAuthor Commented:
The record remains but no CommitTrans has been executed on my software. I think the problem is more: why the process gets blocked?.
llorensLAuthor Commented:
The record remains but no CommitTrans has been executed on my software. I think the problem is more: why the process gets blocked?.
you first need to find out if the process is connected to the database when it is blocked or not
llorensLAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.