Re: [SQL] placeholders

At 03:05 7/01/99 , you wrote:
>> Hi,
>>
>> I am using
>> - Postrges 6.3.2
>> - DBI 0.93
>> - DBD-pg 0.73
>> - Slackware 3.3 Linux
>>
>> I am accessing the database using a perl
>> CGI program.
>>
>> I need to be able to list records from a
>> database in various sort orders. The user
>> needs to be able to select the sort order
>> using a web form.
>
>[...]
>
>>
>> I want to be able to change the code so the
>> field name in the order by clause is variable
>>
>> eg. instead of
>>       ORDER BY title DESC
>>
>> I want to say
>>       ORDER BY :4 DESC
>>
>> and in the execute statement
>> $sth1->execute (("$category\%"),("$keywords\%"), ("$postedby\%"))
>>
>> add a fourth variable ("$orderby") which
>> will be set to title, dateposted or
>> userlastupdate
>>
>> However if I use ("$orderby") as the
>> fourth variable and set $orderby to
>> title then my trace shows DBI puts
>> 'title' into the SQL SELECT statement
>> not title and I get an invalid syntax
>> error. I need to find a way to stop
>> putting the ' ' around title.
>>
>> Is there any way I can make the order by
>> operand a variable ??
>>
>
>    What's wrong with:
>
>    @column = ("catalogid", "productid", ..., "category");
>
>    ... ORDER BY $column[3] DESC ...

Gene, Thanks for your reply.

I don't think I can put a perl variable $column[3]
into an sql prepare statement as it gets passed
straight through as $column[3] to DBI and I get
a syntax error from DBI.
ERROR:  parser: parse error at or near "column"

However the sort in Perl looks like the
answer so I will try that next.

Regards, Martin

>
>If you want it real smart, you can run a query to obtain column names
>from the database.
>
>Also, since you are using perl anyway, you might as well delegate
>ordering to perl. If you read your entire query output to an array of
>strings where values are delimited with something, e. g., "\t", you
>could say:
>
>    $colToSortOn = 2;
>    $descending = 1; # otherwise $descending = undef;
>    foreach ( sort {
>            @a = split "\t", $a;
>            @b = split "\t", $b;
>                ($descending ? $b[$colToSortOn] <=> $a[$colToSortOn] : $a[$colToSortOn] <=> $b[$colToSortOn])
>        } @result # @result comes from your query
>    ) {
>        @values = split "\t";
>        # do your display stuff here
>    }
>
>See man perlfunc /sort SUBNAME for more details on sort()
>