Board index » delphi » TADOCommand, ADO 2.7, Windows XP, Parameterized Queries, and SQL Server 7 -- Trials in Frustration

TADOCommand, ADO 2.7, Windows XP, Parameterized Queries, and SQL Server 7 -- Trials in Frustration

Hi all...

I'm wondering if anyone else is experiencing the same problems we're
currently dealing with.  And I'm hoping that someone out there knows what's
going on, and can point me in the right direction on what to do about this.
I'm going to try to be thorough with this, so as to help others in being
able to reproduce the errors we're seeing.  Please forgive me if this
message gets long, and please let me know if you have any questions.

BTW, in creating some test applications to use for this message, I did come
across one workaround which hopefully will help others if they are
experiencing similar problems to ours.  I'm including that workaround below.

I.  Our Environment:

Server:  Windows 2000 Server, MS SQL Server 7 SP3
Developer Machines:  Windows 2000 Professional, ADO 2.6 SP1, Delphi 6
Enterprise (Update Pack 2)
Standard User Machines:  Windows 98 SE, ADO 2.6 SP1
                                        Windows 98 SE, ADO 2.7 SP1
Power User Machines:  Windows 2000 Professional, ADO 2.6 SP1
                                     Windows XP Professional, ADO 2.7  SP1

II.  Our ADO usage style:

We strictly use TADODataset for our queries which return result sets, and
TADOCommand for queries which do not return result sets.  We do not use
TADOQuery, TADOTable, or TADOStoredProc.  If we use TADODataset to run
multiple, different queries, we try not to use Parameterized Queries.  We'll
simply build the CommandText without them, using the QuotedStr() or Format()
functions.

III.  The problem:

Recently, a number of our vendors for third-party software we use in-house
provided us updates to their applications.  According to their tech people,
these updates required the use of ADO 2.7.  Up until that point, we were
happily, and smoothly, using ADO 2.6 SP1 on all our Windows 98 user
machines.  After installing the updates for one application on some of our
Windows 98 machines, we started noticing random Access Violations from our
internally written Delphi apps.  Assuming we were simply doing something
wrong in our code, we revisited our code and attempted to track down our
bugs.  The problem was that it seemed to happen across multiple
applications, and only sporadically -- sometimes working, sometimes not.
It's been very hard to reproduce consistently.

While this was going on, we installed a few Windows XP Professional machines
to test their stability with our internal apps.  That's when we started to
notice more frequent, and reproducible problems.  Basically, almost every
time we tried to execute a query in a TADOCommand, it gave us the following
Access Violation:

Access violation at address 753723D8 in module 'sqloledb.dll'.  Read of
address 00000010.

Note the phrase, "almost every time":  In further research, we found the
following:  If we had a TADODataset connected to the same TADOConnection
object, and we opened a SELECT query *before* we executed a TADOCommand
query, the TADOCommand query would run successfully.  The access violation
only appeared if the TADOCommand query was the first query we tried to run
in the application.  This seems to happen every time on a Windows XP
machine.  We're still having trouble tracking down the specific cause of the
access violations on the Win98 machines; however, the common thread between
the two, and the time this first started was when ADO 2.7 came into the
picture.

IV.  Example #1

Note:  Since we do not have SQL Server 2000 in-house, I don't know if this
problem is reproducible under SQL Server 2000.  However, I was able to
reproduce it under SQL Server 7.

1) First, make sure you're running SQL Server 7 SP3 with NT Authentication
enabled.  Currently, we don't use SQL Authentication in-house.
    You may also want to make sure your username on the WinXP machine
belongs to a group in the DOMAIN which has access to create and drop tables
     in SQL Server.  You can test this by trying to create and drop tables
in Query Analyzer (using NT Authentication).
2) In Delphi 6, drop a TADOConnection and a TADOCommand on the form.  Build
the TADOConnection connection string for the remote SQL Server.
    Set its LoginPrompt property to False.  Set the Connection property for
TADOCommand to the TADOConnection object.
3) Next, drop a TMemo component and a TButton component on the form.
4) In the OnClick() event of the TButton, enter the following code:

procedure TForm1.BtnExecuteClick(Sender: TObject);
var
  Rows : Integer;
begin
  Rows := 0;

  if Trim(Memo1.Text) = '' then
    ShowMessage('Please enter a valid SQL Statement')
  else
    with AdoCommand1 do
      try
        { Assign CommandText and Execute }
        CommandText := Memo1.Text;
        Execute(Rows, NULL);

        ShowMessage('Rows Affected: ' + InttoStr(Rows));
      finally
        { For Our Testing Purposes:  Make sure Connection is Closed }
        if Connection.Connected then
          Connection.Close;
      end;
end;

5) Compile and run it on a Windows XP SP1 machine (our WinXP machine is
fairly vanilla with SQL Client Tools installed and not Delphi).
6) Enter the following:

CREATE TABLE JB_TempTable (
  Field1 INTEGER,
  Field2 VARCHAR(20)
)

7) Click the TButton and you should get the Access Violation.  This will
most likely work on a Windows 98 or Windows 2000 machine, but it should
crash on a Windows XP machine (it does every time for us, regardless of
which WinXP machine we use).

{===========================================================================
==}

Interestingly enough, in creating this little example, and testing a few
scenarios, I found that the following DOES work in Windows XP:

procedure TForm1.BtnExecuteClick(Sender: TObject);
begin
  if Trim(Memo1.Text) = '' then
    ShowMessage('Please enter a valid SQL Statement')
  else
    with AdoCommand1 do
      try
        { Assign CommandText and Execute }
        CommandText := Memo1.Text;
        Execute;                                                    //
Removed the Row Count

        ShowMessage('Execute Successful');
      finally
        { For Our Testing Purposes:  Make sure Connection is Closed }
        if Connection.Connected then
          Connection.Close;
      end;
end;

{===========================================================================
==}

That led me to the following workaround for this first example.  Based on
Delphi's AdoDB unit, I changed this procedure to read:

V.  Solution #1

procedure TForm1.BtnExecuteClick(Sender: TObject);
var
  Rows : Integer;
begin
  Rows := 0;

  if Trim(Memo1.Text) = '' then
    ShowMessage('Please enter a valid SQL Statement')
  else
    with AdoCommand1 do
      try
        { Assign CommandText and Execute }
        CommandText := Memo1.Text;
        Execute(Rows, EmptyParam);                        //  Use EmptyParam
instead of NULL

        ShowMessage('Rows Affected: ' + InttoStr(Rows));
      finally
        { For Our Testing Purposes:  Make sure Connection is Closed }
        if Connection.Connected then
          Connection.Close;
      end;
end;

Viola... all of the sudden, this example works on a Windows XP machine!

{===========================================================================
==}

VI.  Example #2

This example I have yet to solve, and is the one I really need help with.

1) Create a table in SQL Server containing a few fields -- let's use the
classic employee example:

CREATE TABLE SalaryHistory (
  Period VARCHAR(7),
  EmployeeID INTEGER,
  Salary MONEY
)

2) Next, create a Stored Procedure in SQL Server to use this table, as well
as the above table:

CREATE PROCEDURE GetSalaryReport (
  @Period VARCHAR(7)
) AS

DROP TABLE JB_TempTable

SELECT *
INTO JB_TempTable
FROM SalaryHistory
WHERE Period = @Period

3) Next, in Delphi, change the TADOCommand's CommandText to read:

EXEC GetSalaryReport :Period

4) In the OnClick() event of the TButton, enter the following code:

procedure TForm1.BtnExecuteClick(Sender: TObject);
begin
  if Trim(Memo1.Text) = '' then
    ShowMessage('Please enter a valid Period')
  else
    with AdoCommand1 do
      try
        { Assign the Parameter and Execute }
        Parameters.ParamByName('Period').Value := Memo1.Text;
        Execute;

        ShowMessage('Execute Successful');
      finally
        { For Testing Purposes:  Make sure Connection is Closed }
        if Connection.Connected then
          Connection.Close;
      end;
end;

5) Compile and run it on Windows XP.

6) Enter "2003-06" in the Memo box.

7) Click the TButton and you should get the Access Violation.  Again, this
application will most likely work on a Windows 98 or Windows 2000 machine,
but it should crash on Windows XP.

{===========================================================================
==}

VII.  Questions

1)  According to Delphi's AdoDB unit, the "Parameters" parameter of the
Command's Execute method is of type "const OleVariant".  Did Microsoft
change the specs for this to a "var OleVariant"?  Is that why passing NULL
doesn't work under Windows XP with ADO 2.7 SP1?  If so, why does it work
under Windows 98 with ADO 2.7 SP1?

2)  Does Windows XP do something funky with NT Authentication that's
different from Windows 98 or Windows 2000 Pro?

3)  Why can't I seem to use Parameterized Queries if the client machine is a
WinXP machine?  Why does it work under Win98 or Win2000, but cause Access
Violations under WinXP?  This could force us to rewrite *A LOT* of our code
if we needed to migrate our users to Windows XP in the future.

4)  Or, is ADO 2.7 just that buggy with Command Objects, and we should
refuse to install it on client workstations?  If so, we're going to have a
real uphill battle convincing our third-party software vendors to certify
their applications under ADO 2.6 SP1.

{===========================================================================
==}

Thanks again for being so patient with me through this lengthy message.  I
appreciate any help anyone can provide.  This has been giving us quite a lot
of headaches the ...

read more »

 

Re:TADOCommand, ADO 2.7, Windows XP, Parameterized Queries, and SQL Server 7 -- Trials in Frustration


Hi

I've always used ...

    Execute(Rows, EmptyParam)

don't know why are you passigng a "null" there.

Regards

"John Bowman" <johnjbow...@nospam.yahoo.com> escribi en el mensaje
news:3ef4b08a@newsgroups.borland.com...

Quote
> Hi all...

> I'm wondering if anyone else is experiencing the same problems we're
> currently dealing with.  And I'm hoping that someone out there knows
what's
> going on, and can point me in the right direction on what to do about
this.
> I'm going to try to be thorough with this, so as to help others in being
> able to reproduce the errors we're seeing.  Please forgive me if this
> message gets long, and please let me know if you have any questions.

> BTW, in creating some test applications to use for this message, I did
come
> across one workaround which hopefully will help others if they are
> experiencing similar problems to ours.  I'm including that workaround
below.

> I.  Our Environment:

> Server:  Windows 2000 Server, MS SQL Server 7 SP3
> Developer Machines:  Windows 2000 Professional, ADO 2.6 SP1, Delphi 6
> Enterprise (Update Pack 2)
> Standard User Machines:  Windows 98 SE, ADO 2.6 SP1
>                                         Windows 98 SE, ADO 2.7 SP1
> Power User Machines:  Windows 2000 Professional, ADO 2.6 SP1
>                                      Windows XP Professional, ADO 2.7  SP1

> II.  Our ADO usage style:

> We strictly use TADODataset for our queries which return result sets, and
> TADOCommand for queries which do not return result sets.  We do not use
> TADOQuery, TADOTable, or TADOStoredProc.  If we use TADODataset to run
> multiple, different queries, we try not to use Parameterized Queries.
We'll
> simply build the CommandText without them, using the QuotedStr() or
Format()
> functions.

> III.  The problem:

> Recently, a number of our vendors for third-party software we use in-house
> provided us updates to their applications.  According to their tech
people,
> these updates required the use of ADO 2.7.  Up until that point, we were
> happily, and smoothly, using ADO 2.6 SP1 on all our Windows 98 user
> machines.  After installing the updates for one application on some of our
> Windows 98 machines, we started noticing random Access Violations from our
> internally written Delphi apps.  Assuming we were simply doing something
> wrong in our code, we revisited our code and attempted to track down our
> bugs.  The problem was that it seemed to happen across multiple
> applications, and only sporadically -- sometimes working, sometimes not.
> It's been very hard to reproduce consistently.

> While this was going on, we installed a few Windows XP Professional
machines
> to test their stability with our internal apps.  That's when we started to
> notice more frequent, and reproducible problems.  Basically, almost every
> time we tried to execute a query in a TADOCommand, it gave us the
following
> Access Violation:

> Access violation at address 753723D8 in module 'sqloledb.dll'.  Read of
> address 00000010.

> Note the phrase, "almost every time":  In further research, we found the
> following:  If we had a TADODataset connected to the same TADOConnection
> object, and we opened a SELECT query *before* we executed a TADOCommand
> query, the TADOCommand query would run successfully.  The access violation
> only appeared if the TADOCommand query was the first query we tried to run
> in the application.  This seems to happen every time on a Windows XP
> machine.  We're still having trouble tracking down the specific cause of
the
> access violations on the Win98 machines; however, the common thread
between
> the two, and the time this first started was when ADO 2.7 came into the
> picture.

> IV.  Example #1

> Note:  Since we do not have SQL Server 2000 in-house, I don't know if this
> problem is reproducible under SQL Server 2000.  However, I was able to
> reproduce it under SQL Server 7.

> 1) First, make sure you're running SQL Server 7 SP3 with NT Authentication
> enabled.  Currently, we don't use SQL Authentication in-house.
>     You may also want to make sure your username on the WinXP machine
> belongs to a group in the DOMAIN which has access to create and drop
tables
>      in SQL Server.  You can test this by trying to create and drop tables
> in Query Analyzer (using NT Authentication).
> 2) In Delphi 6, drop a TADOConnection and a TADOCommand on the form.
Build
> the TADOConnection connection string for the remote SQL Server.
>     Set its LoginPrompt property to False.  Set the Connection property
for
> TADOCommand to the TADOConnection object.
> 3) Next, drop a TMemo component and a TButton component on the form.
> 4) In the OnClick() event of the TButton, enter the following code:

> procedure TForm1.BtnExecuteClick(Sender: TObject);
> var
>   Rows : Integer;
> begin
>   Rows := 0;

>   if Trim(Memo1.Text) = '' then
>     ShowMessage('Please enter a valid SQL Statement')
>   else
>     with AdoCommand1 do
>       try
>         { Assign CommandText and Execute }
>         CommandText := Memo1.Text;
>         Execute(Rows, NULL);

>         ShowMessage('Rows Affected: ' + InttoStr(Rows));
>       finally
>         { For Our Testing Purposes:  Make sure Connection is Closed }
>         if Connection.Connected then
>           Connection.Close;
>       end;
> end;

> 5) Compile and run it on a Windows XP SP1 machine (our WinXP machine is
> fairly vanilla with SQL Client Tools installed and not Delphi).
> 6) Enter the following:

> CREATE TABLE JB_TempTable (
>   Field1 INTEGER,
>   Field2 VARCHAR(20)
> )

> 7) Click the TButton and you should get the Access Violation.  This will
> most likely work on a Windows 98 or Windows 2000 machine, but it should
> crash on a Windows XP machine (it does every time for us, regardless of
> which WinXP machine we use).

{===========================================================================

- Show quoted text -

Quote
> ==}

> Interestingly enough, in creating this little example, and testing a few
> scenarios, I found that the following DOES work in Windows XP:

> procedure TForm1.BtnExecuteClick(Sender: TObject);
> begin
>   if Trim(Memo1.Text) = '' then
>     ShowMessage('Please enter a valid SQL Statement')
>   else
>     with AdoCommand1 do
>       try
>         { Assign CommandText and Execute }
>         CommandText := Memo1.Text;
>         Execute;                                                    //
> Removed the Row Count

>         ShowMessage('Execute Successful');
>       finally
>         { For Our Testing Purposes:  Make sure Connection is Closed }
>         if Connection.Connected then
>           Connection.Close;
>       end;
> end;

{===========================================================================

- Show quoted text -

Quote
> ==}

> That led me to the following workaround for this first example.  Based on
> Delphi's AdoDB unit, I changed this procedure to read:

> V.  Solution #1

> procedure TForm1.BtnExecuteClick(Sender: TObject);
> var
>   Rows : Integer;
> begin
>   Rows := 0;

>   if Trim(Memo1.Text) = '' then
>     ShowMessage('Please enter a valid SQL Statement')
>   else
>     with AdoCommand1 do
>       try
>         { Assign CommandText and Execute }
>         CommandText := Memo1.Text;
>         Execute(Rows, EmptyParam);                        //  Use
EmptyParam
> instead of NULL

>         ShowMessage('Rows Affected: ' + InttoStr(Rows));
>       finally
>         { For Our Testing Purposes:  Make sure Connection is Closed }
>         if Connection.Connected then
>           Connection.Close;
>       end;
> end;

> Viola... all of the sudden, this example works on a Windows XP machine!

{===========================================================================

- Show quoted text -

Quote
> ==}

> VI.  Example #2

> This example I have yet to solve, and is the one I really need help with.

> 1) Create a table in SQL Server containing a few fields -- let's use the
> classic employee example:

> CREATE TABLE SalaryHistory (
>   Period VARCHAR(7),
>   EmployeeID INTEGER,
>   Salary MONEY
> )

> 2) Next, create a Stored Procedure in SQL Server to use this table, as
well
> as the above table:

> CREATE PROCEDURE GetSalaryReport (
>   @Period VARCHAR(7)
> ) AS

> DROP TABLE JB_TempTable

> SELECT *
> INTO JB_TempTable
> FROM SalaryHistory
> WHERE Period = @Period

> 3) Next, in Delphi, change the TADOCommand's CommandText to read:

> EXEC GetSalaryReport :Period

> 4) In the OnClick() event of the TButton, enter the following code:

> procedure TForm1.BtnExecuteClick(Sender: TObject);
> begin
>   if Trim(Memo1.Text) = '' then
>     ShowMessage('Please enter a valid Period')
>   else
>     with AdoCommand1 do
>       try
>         { Assign the Parameter and Execute }
>         Parameters.ParamByName('Period').Value := Memo1.Text;
>         Execute;

>         ShowMessage('Execute Successful');
>       finally
>         { For Testing Purposes:  Make sure Connection is Closed }
>         if Connection.Connected then
>           Connection.Close;
>       end;
> end;

> 5) Compile and run it on Windows XP.

> 6) Enter "2003-06" in the Memo box.

> 7) Click the TButton and you should get the Access Violation.  Again, this
> application will most likely work on a Windows 98 or Windows 2000 machine,
> but it should crash on Windows XP.

{===========================================================================

- Show quoted text -

Quote
> ==}

> VII.  Questions

> 1)  According to Delphi's AdoDB unit, the "Parameters" parameter of the
> Command's Execute method is of type "const OleVariant".  Did Microsoft
> change the specs for this to a "var OleVariant"?  Is that why passing NULL
> doesn't work under Windows XP with ADO 2.7 SP1?  If so, why does it work
> under Windows 98 with ADO 2.7 SP1?

> 2)  Does Windows XP do something funky with NT Authentication that's
> different from Windows 98 or Windows 2000 Pro?

> 3)  Why can't I seem to use Parameterized Queries if the client machine is
a
> WinXP machine?  Why does it work

...

read more »

Other Threads