l3nz has asked for the wisdom of the Perl Monks concerning the following question:

O wise brothers,
I have came across a couple of problems using Ado with Perl under Win32 connecting to a Ms SqlServer 7.0 database. I have no problems when executing a single Sql query but in a case I have to compute variables in the same query being sento to the database engine, like 
declare @VAR int;
set @VAR = (select max(id) from table);
select * from table2 where id = @VAR

[download]
If I do this, the resulting recordset object shows strange behaviours, like Of course the query I'm using is by far more complex than the one I'm reporting here so replacing variables with subqueries is not a viable option.

Anybody got an idea on how to overcome this result? I cannot use DBI because it would mean a major rewrite of existing code. Thank you.

Replies are listed 'Best First'.
Re: Problem with database under Win32
by NetWallah (Canon) on Nov 22, 2003 at 01:57 UTC
    I would try one of two approaches:

    1. Do your SQL in a stored procedure

    2. Separate the 2 queries. Get the result of the MAX into a perl variable, then build and run the second query.

[reply]
      Thanks for your reply - but how do I call a stored procedure on SQL Server from Perl?
[reply]
Re: Problem with database under Win32
by runrig (Abbot) on Nov 22, 2003 at 10:03 UTC
    Depending on the uniqueness of the id field in table1, and how close your real problem is to this example, but maybe this would work:
    select table2.*
from table1, table2
where table1.id = table2.id
order by id desc

    [download]
    And fetch until id changes. And I don't know if DBI would help either, but if you have to rewrite the bits that don't work anyway, and if DBI does work, I'd consider that as an option. DBD::Sybase supports multiple result sets (and it supports SQL Server to some extent), I don't know if that is what you are referring to, or if that's applicable here.
[reply]
[d/l]
      Using SELECT TOP 1 From ..... ORDER BY ID DESC may be faster than trying a "Max".

      I am not a SQL weenie though - you may want to check with your DBA as to why the subquery seems "slower".

[reply]
        I am not sure - if you MAX() an index, you usually get top performance with no disk access at all; in any other case you need a table scan to know all the values, so you can max() them. My actual query is not any way similar to the one I am presenting here (it's a five-page belemoth, just in the case you wonder, even with all repeated subqueries extracted!)
[reply]
[d/l]
Re: Problem with database under Win32
by NetWallah (Canon) on Nov 22, 2003 at 07:04 UTC
    Oh - here is a third, and perhaps simpler option - Combine the queries to get what you want like this :

    select * from table2 where id =(select max(id) from table)

    Untested (Because I don't have access to your database), but I have used SQL syntax similar to this.

[reply]
      This is a subquery approach - I used it first, but query resolution gets much slower and query in itself gets way messier.
[reply]
Re: Problem with database under Win32
by l3nz (Friar) on Nov 24, 2003 at 13:48 UTC
[reply]

Back to Seekers of Perl Wisdom