Problem with auto new connection

Romano · Post by **Romano** » Fri 14 Aug 2009 11:46

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

Challenger · Post by **Challenger** » Tue 18 Aug 2009 12:49

We could not reproduce this situation. I see only one active connection to the database. Please give a more detailed description of the problem.

Romano · Post by **Romano** » Tue 18 Aug 2009 16:59

Hi,
how can I send you a test application?

Thanks
Roman

Romano · Post by **Romano** » Tue 18 Aug 2009 21:11

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

Challenger · Post by **Challenger** » Fri 21 Aug 2009 07:13

Try to enable the MultiplceActiveResultSets option.

Romano · Post by **Romano** » Fri 21 Aug 2009 08:33

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

Challenger · Post by **Challenger** » Fri 21 Aug 2009 08:45

You can call the OpenNext method of TMSQuery.

Romano · Post by **Romano** » Fri 21 Aug 2009 15:00

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

Dimon · Post by **Dimon** » Tue 25 Aug 2009 13:34

Neither MS SQL Server nor the TDataSet component provide such functionality and therefore you can use only the OpenNext method.

Romano · Post by **Romano** » Fri 04 Sep 2009 12:22

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

Dimon · Post by **Dimon** » Mon 07 Sep 2009 08:20

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.

Romano · Post by **Romano** » Mon 07 Sep 2009 10:31

Thank you

!
When do you plan to release next build?

Thanks again
Roman

Dimon · Post by **Dimon** » Tue 08 Sep 2009 08:54

The next SDAC build will be released by the end of this week.

Romano · Post by **Romano** » Sun 13 Sep 2009 20:06

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

Dimon · Post by **Dimon** » Mon 14 Sep 2009 11:31

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.