Selon Michael Stassen <[EMAIL PROTECTED]>:
> Kapoor, Nishikant wrote:
>
> > [Sorry for cross-posting.]
> >
> > This is in continuation with the above mentioned subject - I am trying to
> > find the 'display data order' for the returned resultset. The following
> > thread very well answers my question:
> >
> > http://lists.mysql.com/mysql/185626
> >
> > a) SET @row=0;
> > b) SELECT (@row:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY
> empno;
> >
> > +-----+--------+-------+
> > | row | ename | empno |
> > +-----+--------+-------+
> > | 1 | SMITH | 7369 |
> > | 2 | ALLEN | 7499 |
> > | 3 | WARD | 7521 |
> > | 4 | JONES | 7566 |
> > | 5 | MARTIN | 7654 |
> > +-----+--------+-------+
> >
> > However, I am trying to use it in a perl script instead of from command
> > line, and I am not sure how exactly to do it. I need to execute both
> > statements a & b together ...
>
> You cannot execute them together. You must execute them one at a time, in
> the
> same connection.
>
> > ... or else I get
> >
> > +-----+--------+-------+
> > | row | ename | empno |
> > +-----+--------+-------+
> > |NULL | SMITH | 7369 |
> > |NULL | ALLEN | 7499 |
> > |NULL | WARD | 7521 |
> > |NULL | JONES | 7566 |
> > |NULL | MARTIN | 7654 |
> > +-----+--------+-------+
>
> If you are getting this, you've made a mistake in your perl code. It's hard
> to say what, though, as you haven't shown us your code.
>
> Hmmm. User variables are connection specific. Are you making the mistake of
> opening and closing a connection for each query? That's unneccessary, and it
> adds a lot of overhead.
>
> > How can I execute both (a) and (b) in my perl script?
>
> The same way you would execute any two statements, one at a time. Something
> like:
>
> $conn->do('SET @row=0');
> my $sql = 'SELECT @row:[EMAIL PROTECTED] as row, <fields> FROM <tables>
> WHERE ...';
> my $sth = $conn->prepare($sql);
> $sth->execute();
> return $sth->fetchall_arrayref( {} );
>
> > Thanks for any help.
> > Nishi
>
> Mathias wrote:
>
> > Hi,
> > You don"t need to use @row in perl,
> > just use :
> >
> > $n=0;
> > while (fetch..) {
> > $n++;
> > print "$n"."$ename ...\n";
> >
> > }
>
> That would work.
>
> Kapoor, Nishikant wrote:
>
> > I could, but I am assigning the entire resultset in one shot to another
> > construct as follows:
> >
> > my $str = "SELECT [EMAIL PROTECTED]:[EMAIL PROTECTED] as row, <fields>
> FROM <tables> WHERE
> ...";
> > my $sth = $conn->prepare($st);
> > $sth->execute();
> > return $sth->fetchall_arrayref( {} );
> >
> > Thanks,
> > -Nishi
>
> You are returning an arrayref! One row in your results equals one row in
> your
> array -- in the same order! Arrays are indexed, so display data order is
> already built into your array. Display position = array position + 1. Why
> do
> you need a redundant field in each row?
>
> Harald Fuchs wrote:
>
> > Just change the last line to
> >
> > my $n = 0;
> > return [ map { [ ++$n, @$_ ] } @{$sth->fetchall_arrayref} ];
> >
> > What's the problem?
>
> Are you sure? I get "Can't coerce array into hash at...".
>
> Mathias wrote:
>
> > Then alter your table to add an auto_increment column, update it and play
> > your query without @row.
> >
> > Mathias
>
> No, no, no! This is what we call using a shotgun to kill a gnat. It also
> doesn't yield the order of the query results.
>
> Michael
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> No, no, no! This is what we call using a shotgun to kill a gnat. It also
> doesn't yield the order of the query results.
sure that ${$ligne[$row]}{$n}:=$n with n perl operations can be faster !:)
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
