Page 1 of 2
Problem with auto new connection
Posted: Fri 14 Aug 2009 11:46
by Romano
Hi, I have 2 queries:
Q1, Q2: TMSQuery;
Q1.SQL.Add('SELECT 0 SELECT 1')'
Q2.SQL.Add('SELECT 2');
Q1.Active := True; -> open connection with one SPID
Q2.Active := True; -> open the second connection with another SPID
Q1.Fields[0].AsInteger returns 0, so the SELECT 1 is still waiting...
Is it possible to avoid this situation? It can be mistake in SQL script and I don't want to open the second connection.
I want this:
Q1.Fields[0].AsInteger returns 0 (SELECT 1 and all next are discarded) and only one connection is alive (one SPID).
Thanks a lot
Roman
Posted: Tue 18 Aug 2009 12:49
by Challenger
We could not reproduce this situation. I see only one active connection to the database. Please give a more detailed description of the problem.
Posted: Tue 18 Aug 2009 16:59
by Romano
Hi,
how can I send you a test application?
Thanks
Roman
Posted: Tue 18 Aug 2009 21:11
by Romano
Unit1.pas:
Code: Select all
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, MemDS, DBAccess, MSAccess, Grids, DBGrids, StdCtrls;
type
TForm1 = class(TForm)
MSConnection1: TMSConnection;
MSQuery1: TMSQuery;
MSQuery2: TMSQuery;
Button1: TButton;
DBGrid1: TDBGrid;
DataSource1: TDataSource;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
begin
MSQuery1.Active := True;
MSQuery2.Active := True;
end;
end.
Unit1.dfm:
Code: Select all
ClientHeight = 335
ClientWidth = 427
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 265
Top = 60
Width = 75
Height = 25
Caption = 'Start'
TabOrder = 0
OnClick = Button1Click
end
object DBGrid1: TDBGrid
Left = 60
Top = 190
Width = 320
Height = 120
DataSource = DataSource1
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object MSConnection1: TMSConnection
Database = 'tempdb'
Authentication = auWindows
Left = 90
Top = 55
end
object MSQuery1: TMSQuery
Connection = MSConnection1
SQL.Strings = (
'select 0 select 1')
Left = 135
Top = 55
end
object MSQuery2: TMSQuery
Connection = MSConnection1
SQL.Strings = (
'select 2')
Left = 180
Top = 55
end
object DataSource1: TDataSource
DataSet = MSQuery1
Left = 115
Top = 140
end
end
You have to fill server name, start application and click button Start.
SDAC 4.70.0.48
Delphi 2009 Update 3 (DB Pack Update 4)
SQL Server 2005 SP 3 / 2008
Native client 2005 / 2008
Profiler 2008:
Code: Select all
27
1
10
18
16
17
13
12
14
2009-08-18T18:54:16.397+02:00
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Project1.exe
52
2009-08-18T18:54:20.417+02:00
SET LOCK_TIMEOUT 2000
0
0
Project1.exe
52
2009-08-18T18:54:20.417+02:00
0
0
SET NO_BROWSETABLE ON
0
0
Project1.exe
52
2009-08-18T18:54:20.417+02:00
0
0
select 0 select 1
0
0
Project1.exe
52
2009-08-18T18:54:20.417+02:00
0
0
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Project1.exe
53
2009-08-18T18:54:20.427+02:00
SET NO_BROWSETABLE ON
0
0
Project1.exe
53
2009-08-18T18:54:20.427+02:00
0
0
select 2
0
0
Project1.exe
53
2009-08-18T18:54:20.43+02:00
0
0
3000
0
Project1.exe
53
2009-08-18T18:54:20.427+02:00
0
0
8693000
0
Project1.exe
52
2009-08-18T18:54:20.417+02:00
0
0
Regards
Roman
Posted: Fri 21 Aug 2009 07:13
by Challenger
Try to enable the MultiplceActiveResultSets option.
Posted: Fri 21 Aug 2009 08:33
by Romano
Thanks, it works.
But unfortunately we have to support MS SQL Server 2000 yet. And MARS is from SQL 2005...
Is it possible to detect the second SELECT in one query (I mean that there is a "select 1" in "select 0 select 1")? Some function that query containts several result sets?
Thanks
Roman
Posted: Fri 21 Aug 2009 08:45
by Challenger
You can call the OpenNext method of TMSQuery.
Posted: Fri 21 Aug 2009 15:00
by Romano
But if I call OpenNext and query contains only one statement, query is closed.
I need some detection. Something like this:
Q: TMSQuery;
...
Q.Open;
if Q.ContainsMoreStatementsThanOne then
raise EMyError.Create('Too many statement in query');
...
Thanks
Posted: Tue 25 Aug 2009 13:34
by Dimon
Neither MS SQL Server nor the TDataSet component provide such functionality and therefore you can use only the OpenNext method.
Posted: Fri 04 Sep 2009 12:22
by Romano
Hi,
I did some investigation a found this solution. But I don't know if it is OK...
Code: Select all
type
TMyMSQuery = class(TMSQuery)
public
procedure Execute; override;
end;
procedure TMyMSQuery.Execute;
begin
inherited;
// if there is a next result set then discard it
if (FICommand nil) and (not FICommand.IUnknownNextIsNull) then
begin
FICommand.ClearIMultipleResults;
FICommand.ClearIUnknownNext;
end;
end;
Is it possible to use that code?
Can you add to TCustomMSDataSet (or to some predecessor)
Code: Select all
function IsNextResultSet: Boolean;
procedure FreeNextResultSet;
I cannot see all relationships in your code and I don't want to generate some Access Violation Error...
Thanks a lot
Roman
Posted: Mon 07 Sep 2009 08:20
by Dimon
To solve the problem with multiple result sets, you can use the TMSDataSetOptions.DisableMultipleResults property which can forbid the usage of multiple results. You should set this property to True.
Also we will add the IsNextResultSet method in the next SDAC buid.
Posted: Mon 07 Sep 2009 10:31
by Romano
Thank you
!
When do you plan to release next build?
Thanks again
Roman
Posted: Tue 08 Sep 2009 08:54
by Dimon
The next SDAC build will be released by the end of this week.
Posted: Sun 13 Sep 2009 20:06
by Romano
Hi,
I tried build 51 with DisableMultiplyResults and found a problem.
Let's have a sql query (for example):
Code: Select all
var
MyQuery: TMSQuery;
with MyQyery do
begin
SQL.Add(
'USE MyDtabase'#13+
'SELECT 1');
Options.DisableMultiplyResults := True;
Open;
end;
In this case I get error 'Query must return exactly one result set - use Execute'. So I use Execute. But after Execute MyQuery.FieldCount = 0...
Code: Select all
var
MyQuery: TMSQuery;
with MyQyery do
begin
SQL.Add(
'USE MyDtabase '#13+
'SELECT 1');
Options.DisableMultiplyResults := False;
Open;
if HasNextResultSet then
raise Exception.Create('...HasNextResultSet...');
end;
In this case all works OK. HasNextResultSet = False and FieldCount = 1.
I think that is some mistake in implementation of Options.DisableMultiplyResults. USE [dbname] doesn't return any result set, so SELECT 1 shouldn't be ignored.
Thanks
Roman
Posted: Mon 14 Sep 2009 11:31
by Dimon
The DisableMultiplyResults option disables execution of any SQL queries except a first query in the SQL batch. This behaviour is connected with the specific of SQL server, but not with SDAC. Therefore we can't change this behaviour.