ADOQuery in thread hangs

[Mikael Lenfors]

Hello!

Having a real nightmare problem at the moment. In my main program I have a
ADOConnection and several associated ADOQuerys. They are all connected to an
Access file.
Then I have a TThread containing it's own ADOQuery, connecting to the same
Access file doing some selects. I can run the thread as many times as I want
without any problem. If I then do an Insert statement in the main program a
strange thing happenes. The next time I'm running the thread it DIES at the
first line where I do ADOQuery.SQL.Add...

ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('Select * From Units Where UnitID = ' +
IntToStr(UnitRecord.UnitID));
ADOQuery.Open;

The above is sample code from the thread, if I trace it and press F8 at the
Add line it just dies there, no exceptions what so ever.

Running Win XP Pro and Delphi Studio 2006

Please any ideas?

[Brian Bushay TeamB]

Post by Mikael Lenfors
Hello!
Having a real nightmare problem at the moment. In my main program I have a
ADOConnection and several associated ADOQuerys. They are all connected to an
Access file.
Then I have a TThread containing it's own ADOQuery, connecting to the same
Access file doing some selects. I can run the thread as many times as I want
without any problem. If I then do an Insert statement in the main program a
strange thing happenes. The next time I'm running the thread it DIES at the
first line where I do ADOQuery.SQL.Add...
ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('Select * From Units Where UnitID = ' +
IntToStr(UnitRecord.UnitID));
ADOQuery.Open;
The above is sample code from the thread, if I trace it and press F8 at the
Add line it just dies there, no exceptions what so ever.
Running Win XP Pro and Delphi Studio 2006
Please any ideas?

When you use a thread you need to create the TadoQuery as well as its connection
in the thread. You didn't indicate if that is what you are doing.
--
Brian Bushay (TeamB)
***@NMPLS.com

[Mikael Lenfors]

Yes I create a TADOQuery local to the thread. In the thread I don't have any
ADOConnection, I just use a connectionstring directly in the ADOQuery.

Mikael

Post by Brian Bushay TeamB

Post by Mikael Lenfors
Hello!
Having a real nightmare problem at the moment. In my main program I have a
ADOConnection and several associated ADOQuerys. They are all connected to an
Access file.
Then I have a TThread containing it's own ADOQuery, connecting to the same
Access file doing some selects. I can run the thread as many times as I want
without any problem. If I then do an Insert statement in the main program a
strange thing happenes. The next time I'm running the thread it DIES at the
first line where I do ADOQuery.SQL.Add...
ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('Select * From Units Where UnitID = ' +
IntToStr(UnitRecord.UnitID));
ADOQuery.Open;
The above is sample code from the thread, if I trace it and press F8 at the
Add line it just dies there, no exceptions what so ever.
Running Win XP Pro and Delphi Studio 2006
Please any ideas?

When you use a thread you need to create the TadoQuery as well as its connection
in the thread. You didn't indicate if that is what you are doing.
--
Brian Bushay (TeamB)

[Mikael Lenfors]

I think I have localiced where the problem begins...

In my main program (not the thread) i have the following code. The
EditSchedulesForm is a maintenance form and it adds a procedure hook for all
insert events. When an insert triggers it sets some default values including
an ID which i get through another functioncall to GetNextScheduleId. This is
where it all goes wrong! As soon as this GetNextScheduleId is executed the
thread!! stops working with the error i mentioned earlier. If I in
GetNextScheduleId just put a "Result := 100; Exit;" then it works.
The really strange thing is that my main program works all the time, also
after the code below s run.

-- From maintenance form

Procedure TEditSchedulesForm.FormShow(Sender: TObject);
DBForm.ADOQuery.AfterInsert := InsertRecord;

Procedure TEditSchedulesForm.FormDestroy(Sender: TObject);
DBForm.ADOQuery.AfterInsert := Nil;

Procedure TEditSchedulesForm.InsertRecord(DataSet: TDataSet);
Begin
DBForm.ADOQuery.FieldByName('ScheduleID').AsInteger :=
DBForm.GetNextScheduleId;
DBForm.ADOQuery.FieldByName('ScheduleCreationDate').AsDateTime := Now;
DBForm.ADOQuery.FieldByName('ScheduleChangeDate').AsDateTime := Now;
End;

--- From DBForm

Function TDBForm.GetNextScheduleId: Integer;
Begin
Result := 0;
Try
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('Select Max(ScheduleId) As MaxId');
ADOQuery3.SQL.Add('From Schedules');
ADOQuery3.Open;
If ADOQuery3.RecordCount = 1 Then
Result := Max(1, ADOQuery3.FieldByName('MaxId').AsInteger + 1)
Else
Result := 1;
LogForm.Log(1, 'Retreiving next free Schedule ID as ' +
IntToStr(Result), 0);
Except
On E: Exception do
Begin
LogForm.Log(4, 'Failed retreiving next free schedule Id!', 0);
LogForm.LogSQL(0, ADOQuery3.SQL, 0);
LogForm.Log(0, 'Error: ' + E.Message, 0);
End;
End;
ADOQuery3.Close;
End;

Post by Mikael Lenfors
Yes I create a TADOQuery local to the thread. In the thread I don't have
any ADOConnection, I just use a connectionstring directly in the ADOQuery.
Mikael

Post by Brian Bushay TeamB

Post by Mikael Lenfors
Hello!
Having a real nightmare problem at the moment. In my main program I have a
ADOConnection and several associated ADOQuerys. They are all connected to an
Access file.
Then I have a TThread containing it's own ADOQuery, connecting to the same
Access file doing some selects. I can run the thread as many times as I want
without any problem. If I then do an Insert statement in the main program a
strange thing happenes. The next time I'm running the thread it DIES at the
first line where I do ADOQuery.SQL.Add...
ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('Select * From Units Where UnitID = ' +
IntToStr(UnitRecord.UnitID));
ADOQuery.Open;
The above is sample code from the thread, if I trace it and press F8 at the
Add line it just dies there, no exceptions what so ever.
Running Win XP Pro and Delphi Studio 2006
Please any ideas?

When you use a thread you need to create the TadoQuery as well as its connection
in the thread. You didn't indicate if that is what you are doing.
--
Brian Bushay (TeamB)

[Mikael Lenfors]

Now a LONG time after the thread "hung" it suddenly popped up an error
message saying "System error 1309" and "A call to an OS function failed"
+ $2[51F1A494]{rtl100.bpl } SysUtils.SysUtils.RaiseLastOSError (Line
16275, "sysutils.pas" + 7) + $2
+ $5[51F1A41D]{rtl100.bpl } SysUtils.SysUtils.RaiseLastOSError (Line
16262, "sysutils.pas" + 0) + $5
+ $0[51F40BB0]{rtl100.bpl } Classes.Classes.StdWndProc (Line 11572,
"classes.pas" + 8) + $0
+ $6A[77D38731]{USER32.dll } GetDC + $6A
+ $14A[77D38811]{USER32.dll } GetDC + $14A
+ $122[77D389C8]{USER32.dll } GetWindowLongW + $122
+ $A[77D396C2]{USER32.dll } DispatchMessageA + $A

Can this help?

Post by Mikael Lenfors
I think I have localiced where the problem begins...
In my main program (not the thread) i have the following code. The
EditSchedulesForm is a maintenance form and it adds a procedure hook for
all insert events. When an insert triggers it sets some default values
including an ID which i get through another functioncall to
GetNextScheduleId. This is where it all goes wrong! As soon as this
GetNextScheduleId is executed the thread!! stops working with the error i
mentioned earlier. If I in GetNextScheduleId just put a "Result := 100;
Exit;" then it works.
The really strange thing is that my main program works all the time, also
after the code below s run.
-- From maintenance form
Procedure TEditSchedulesForm.FormShow(Sender: TObject);
DBForm.ADOQuery.AfterInsert := InsertRecord;
Procedure TEditSchedulesForm.FormDestroy(Sender: TObject);
DBForm.ADOQuery.AfterInsert := Nil;
Procedure TEditSchedulesForm.InsertRecord(DataSet: TDataSet);
Begin
DBForm.ADOQuery.FieldByName('ScheduleID').AsInteger :=
DBForm.GetNextScheduleId;
DBForm.ADOQuery.FieldByName('ScheduleCreationDate').AsDateTime := Now;
DBForm.ADOQuery.FieldByName('ScheduleChangeDate').AsDateTime := Now;
End;
--- From DBForm
Function TDBForm.GetNextScheduleId: Integer;
Begin
Result := 0;
Try
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('Select Max(ScheduleId) As MaxId');
ADOQuery3.SQL.Add('From Schedules');
ADOQuery3.Open;
If ADOQuery3.RecordCount = 1 Then
Result := Max(1, ADOQuery3.FieldByName('MaxId').AsInteger + 1)
Else
Result := 1;
LogForm.Log(1, 'Retreiving next free Schedule ID as ' +
IntToStr(Result), 0);
Except
On E: Exception do
Begin
LogForm.Log(4, 'Failed retreiving next free schedule Id!', 0);
LogForm.LogSQL(0, ADOQuery3.SQL, 0);
LogForm.Log(0, 'Error: ' + E.Message, 0);
End;
End;
ADOQuery3.Close;
End;

Post by Mikael Lenfors
Yes I create a TADOQuery local to the thread. In the thread I don't have
any ADOConnection, I just use a connectionstring directly in the ADOQuery.
Mikael

Post by Brian Bushay TeamB

Post by Mikael Lenfors
Hello!
Having a real nightmare problem at the moment. In my main program I have a
ADOConnection and several associated ADOQuerys. They are all connected to an
Access file.
Then I have a TThread containing it's own ADOQuery, connecting to the same
Access file doing some selects. I can run the thread as many times as I want
without any problem. If I then do an Insert statement in the main program a
strange thing happenes. The next time I'm running the thread it DIES at the
first line where I do ADOQuery.SQL.Add...
ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('Select * From Units Where UnitID = ' +
IntToStr(UnitRecord.UnitID));
ADOQuery.Open;
The above is sample code from the thread, if I trace it and press F8 at the
Add line it just dies there, no exceptions what so ever.
Running Win XP Pro and Delphi Studio 2006
Please any ideas?

When you use a thread you need to create the TadoQuery as well as its connection
in the thread. You didn't indicate if that is what you are doing.
--
Brian Bushay (TeamB)

[Brian Bushay TeamB]

Post by Mikael Lenfors
Now a LONG time after the thread "hung" it suddenly popped up an error
message saying "System error 1309" and "A call to an OS function failed"
+ $2[51F1A494]{rtl100.bpl } SysUtils.SysUtils.RaiseLastOSError (Line
16275, "sysutils.pas" + 7) + $2
+ $5[51F1A41D]{rtl100.bpl } SysUtils.SysUtils.RaiseLastOSError (Line
16262, "sysutils.pas" + 0) + $5
+ $0[51F40BB0]{rtl100.bpl } Classes.Classes.StdWndProc (Line 11572,
"classes.pas" + 8) + $0
+ $6A[77D38731]{USER32.dll } GetDC + $6A
+ $14A[77D38811]{USER32.dll } GetDC + $14A
+ $122[77D389C8]{USER32.dll } GetWindowLongW + $122
+ $A[77D396C2]{USER32.dll } DispatchMessageA + $A
Can this help?

It doesn't help me but you might try changing your error handling code in
GetNextScheduleId so you can give us the Delphi error
--
Brian Bushay (TeamB)
***@NMPLS.com

[Guillem]

Post by Mikael Lenfors
Yes I create a TADOQuery local to the thread. In the thread I don't
have any ADOConnection, I just use a connectionstring directly in the
ADOQuery.
Mikael

do you also call coInitializeEx (or coInitialize) and coUninitialize in
each thread that uses ADO?
--
Best regards :)

Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Mikael Lenfors]

No, I didn't know this was nessessary? What is this?

regards, Mikael

Post by Guillem

Post by Mikael Lenfors
Yes I create a TADOQuery local to the thread. In the thread I don't
have any ADOConnection, I just use a connectionstring directly in the
ADOQuery.
Mikael

do you also call coInitializeEx (or coInitialize) and coUninitialize in
each thread that uses ADO?
--
Best regards :)
Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Mikael Lenfors]

Tried to put CoInitialize(Null, COINIT_APARTMENTTHREADED); at the
beginning of my thread Execute but it's an unknown command. What Uses am I
supposed to provide?

Regards Mikael

Post by Mikael Lenfors
No, I didn't know this was nessessary? What is this?
regards, Mikael

Post by Guillem

Post by Mikael Lenfors
Yes I create a TADOQuery local to the thread. In the thread I don't
have any ADOConnection, I just use a connectionstring directly in the
ADOQuery.
Mikael

do you also call coInitializeEx (or coInitialize) and coUninitialize in
each thread that uses ADO?
--
Best regards :)
Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Kevin Frevert]

Post by Mikael Lenfors
Tried to put CoInitialize(Null, COINIT_APARTMENTTHREADED); at the
beginning of my thread Execute but it's an unknown command. What Uses am I
supposed to provide?

ActiveX

[Mikael Lenfors]

Thanks, now it compiled.

Tried to put CoInitialize(Nil); and CoUninitialize; at the beginning /
end of my Execute medthod but it didn't solve my problem...

Regards, Mikael

Post by Kevin Frevert

Post by Mikael Lenfors
Tried to put CoInitialize(Null, COINIT_APARTMENTTHREADED); at the
beginning of my thread Execute but it's an unknown command. What Uses am
I supposed to provide?

ActiveX

[Guillem]

Post by Mikael Lenfors
Hello!
Having a real nightmare problem at the moment. In my main program I
have a ADOConnection and several associated ADOQuerys. They are all
connected to an Access file. Then I have a TThread containing it's
own ADOQuery, connecting to the same Access file doing some selects.
I can run the thread as many times as I want without any problem. If
I then do an Insert statement in the main program a strange thing
happenes. The next time I'm running the thread it DIES at the first
line where I do ADOQuery.SQL.Add...
ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('Select * From Units Where UnitID = ' +
IntToStr(UnitRecord.UnitID)); ADOQuery.Open;
The above is sample code from the thread, if I trace it and press F8
at the Add line it just dies there, no exceptions what so ever.

does it die or does it simply halt?
--
Best regards :)

Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Mikael Lenfors]

Hello!

I finally solved the problem (I hope) after 10 hours of work.

For those not interested in reading the hole story the problem was that ADO
queries stopped working in my TThread.

It seems like the problem is the Max() function in SQL!

If I run the query "Select Max(MyID) From MyTable" from my main program,
efter this ADOqueries didn't work any more in my thread. The thread just
hung when trying to do a ADOQuery.SQL.Add(...)

After rewriting the select To "Select MyID From Mytable Order By MyID Desc"
in the main program and picking the first returned record (giving the same
result as above) everyting works!

Can anyone explain this strange error? Of course my workaround is not wery
good for large tables....

Regards, Mikael

[Vitali Kalinin]

Looks like a locking problem to me. Do you use transactions?

[Guillem]

Mikael Lenfors wrote:

<snip text>

I agree with Vitali. It sounds like some deadlock in the database
layer. Check for unfinished transactions when you try this.
--
Best regards :)

Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Brian Hollister]

I'm not sure what RDBMS your using but you could add TOP to your select and
that would help in the case of a larger table , at least you would not bring
all the records back.

Brian
--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com

Post by Guillem
<snip text>
I agree with Vitali. It sounds like some deadlock in the database
layer. Check for unfinished transactions when you try this.
--
Best regards :)
Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Mikael Lenfors]

Thanks, thats's a good idea....

Post by Brian Hollister
I'm not sure what RDBMS your using but you could add TOP to your select and
that would help in the case of a larger table , at least you would not bring
all the records back.
Brian
--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com

Post by Guillem
<snip text>
I agree with Vitali. It sounds like some deadlock in the database
layer. Check for unfinished transactions when you try this.
--
Best regards :)
Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[Mikael Lenfors]

Hello!

Tried to really exclude all SQL code down to a minimum. Can't find anywhere
where it could be a lock! Only simple select statements everywhere! As soon
as I use a Max() in any select my thread dies! All other select in the main
program still working....

regards, Mikael

Post by Guillem
<snip text>
I agree with Vitali. It sounds like some deadlock in the database
layer. Check for unfinished transactions when you try this.
--
Best regards :)
Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
--
Contribute to the Indy Docs project: http://docs.indyproject.org
--
In order to contact me remove the -nospam

[unknown]

If you are running it against MSSql 2000 or greater
then try adding (nolock) as in

Select * from atable (nolock)

[Kevin Frevert]

Post by Mikael Lenfors
Hello!
Tried to really exclude all SQL code down to a minimum. Can't find
anywhere where it could be a lock! Only simple select statements
everywhere! As soon as I use a Max() in any select my thread dies! All
other select in the main program still working....

This may sound like a silly question, but does what you are doing require
threading?