Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Problem with database under Win32

by l3nz (Friar)
on Nov 21, 2003 at 21:35 UTC ( #309058=perlquestion: print w/replies, xml ) Need Help??

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
If I do this, the resulting recordset object shows strange behaviours, like
  • it does not test positive as empty (i.e. when both $rs->Bof and $rs->Eof true) so that my code tries to loop on it; nevertheless, the loop is infinite because $rs->Eof is never true even if $rs->MoveNext reports no errors and gets executed some thousand times before the Ctrl-C
  • I read somewhere that it is possible to access the different outcomes from each Sql statement separately but I do not know how to do it (in fact I'd like to access only the result from the third statement, as the first two are not supposed to return anything, and I wonder if that's the problem with the point above)
  • I casually noticed that loading the Data::Dumper module seems to break the ability for the Ado connection to open a recordset (it just freezes)
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.

      Thanks for your reply - but how do I call a stored procedure on SQL Server from Perl?
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
    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.
      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".

        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!)
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.

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://309058]
Approved by Enlil
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (1)
As of 2021-02-28 10:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?