Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Re^6: DBI, and date type SQLite

by NetWallah (Canon)
on Oct 10, 2011 at 06:08 UTC ( #930533=note: print w/replies, xml ) Need Help??

in reply to Re^5: DBI, and date type SQLite
in thread DBI, and date type SQLite

I guess I was mistaken about the "ORDER BY ?" provided at run-time - it seems to work fine.

The following code does deliver selected records:

use CGI; use DBI; use Encode; my $q = CGI->new; my $fecha= $q->param('theDate'); my $orden= $q->param('orden'); print $q->header, $q->start_html(-lang=>'es-SP',-title=> 'Reservas CPA +'), $q->h2({-align=>'CENTER'},decode("utf-8","Reservas para el día: " +)."$fecha ordenadas por $orden\n\n"); my $dbh = DBI->connect("dbi:SQLite:dbname=cpa-simple.db", "", "", {Rai +seError =>1 } ); my $sth = $dbh->prepare( q ( select id_reserva,activo,tipo_reserva,entidad_nombre_reserva,nombre,apellid +os,telefono,inicio_reserva, fin_reserva,periodicidad,horai, horaf,nombre_admin from reserva,activos,nombre,administ where reserva.activos_reserva_id=activos.id_activos and reserva.nombre_reserva_id=nombre.id_dni and reserva.administ_res +erva_id=administ.id_administ and julianday( ? ) between julianday ( inicio_reserva ) and julianda +y ( fin_reserva ) order by ?)) or die "No puedo preparar la sentencia: " .$dbh->errstr; my @data; $sth->execute("$fecha","$orden") or die "Algo está pasando... ¡Llama a + los bomberos!: " . $sth->errstr; my @datautf8; print $q->start_table({-border=>'1', -align=>'center',-cellpadding=>'6'} +),"\n\n", $q->start_Tr, map ({$q->td({align=>'center'},$q->b(decode("utf-8",$_))) . "\n"} qw|Núm. Material Carácter Entidad Nombre Apellidos Teléfono +|,"Inicio de reserva","Fin de reserva", "Periódica","Hora inicio","Hora finalización","Administra +dor" ), $q->end_Tr,"\n"; while ( @data = $sth->fetchrow_array()){ print $q->start_Tr,"\n", # This '\n' is useful only in case someone read +s the HTML source map ({ $q->td({-align=>'left'},decode("utf-8",$_) )."\n" } @data), $q->end_Tr,"\n"; } print $q->end_table,"\n", $q->end_html,"\n"; $sth->finish; $dbh->disconnect; exit 0;
when run from the command line like this:
\test>perl theDate=2011-12-25 orden=id_reserva Content-Type: text/html; charset=ISO-8859-1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns="" lang="es-SP" xml:lang="es-S +P"> <head> <title>Reservas CPA</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1 +" /> </head> <body> Wide character in print at line 11. <h2 align="CENTER">Reservas para el d&#8745;&#9488;&#9564;a: 2011-12-2 +5 ordenadas por id_reserva </h2><table align="center" border="1" cellpadding="6"> Wide character in print at line 33. .... 8< snip --- 8<---- .... <tr> <td align="left">39</td> <td align="left">CANON FS-2 <td align="left">particular <td align="left">DGA</td> <td align="left">David</td> <td align="left">Divo</td> <td align="left">111222336< <td align="left">2011-12-21 <td align="left">2012-03-04 <td align="left">7</td> <td align="left">8</td> <td align="left">21</td> <td align="left">Alberto</t </tr> </table> </body> </html>

            "XML is like violence: if it doesn't solve your problem, use more."

Replies are listed 'Best First'.
Re^7: DBI, and date type SQLite
by Corion (Pope) on Oct 10, 2011 at 06:25 UTC

    Using a placeholder within the ORDER BY clause happens to work with for example MySQL, but is not supported everywhere. Specifically when preparing a statement, the database driver must be enabled to create a query plan for the statement, whether placeholders are present or not. Placeholders for (e.g.) the table name or the order by clause prevent that.

      Thank you. There are many details to consider ... Therefore it is very interesting ;)

      NetWallah has proven that we can use placeholder with SQLite driver in clause ORDER BY

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://930533]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2018-06-21 01:47 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (117 votes). Check out past polls.