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]
