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

Re^3: DBI, and date type SQLite

by Corion (Pope)
on Oct 09, 2011 at 21:50 UTC ( #930503=note: print w/ replies, xml ) Need Help??


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

So, you didn't show us the part where you call ->fetchrow or ->fetchall. The warning means that you didn't fetch all rows. Maybe you meant to do that, but you don't show the part of your code that relates to that warning at all.


Comment on Re^3: DBI, and date type SQLite
Select or Download Code
Re^4: DBI, and date type SQLite
by nando (Acolyte) on Oct 09, 2011 at 22:00 UTC

    Thanks!... The code is:

    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,ent +idad_nombre_reserva,nombre,apellidos,telefono,inicio_reserva,fin_rese +rva,periodicidad,horai,horaf,nombre_admin from reserva,activos,nombre +,administ where reserva.ac\ tivos_reserva_id=activos.id_activos and reserva.nombre_reserva_id=nomb +re.id_dni and reserva.administ_reserva_id=administ.id_administ and ju +lianday( ? ) between julianday ( inicio_reserva ) and julianday ( 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, $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Núm.")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Material")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Carácter")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Entidad")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Nombre")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Apellidos")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Teléfono")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Inicio de reserva")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Fin de reserva")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Periódica")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Hora inicio")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Hora finalización")),"\n", $q->end_td, "\n", $q->start_td({align=>'center'}),"\n", $q->p(decode("utf-8","Administrador")),"\n", $q->end_td, "\n", $q->end_Tr,"\n"; while ( @data = $sth->fetchrow_array()){ @datautf8 = map(decode("utf-8",$_), @data); print $q->start_Tr, $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[0]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[1]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[2]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[3]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[4]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[5]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[6]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[7]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[8]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[9]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[10]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[11]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[12]"),"\n", $q->end_td,"\n", $q->start_td({-align=>'left'}),"\n", $q->p("$datautf8[13]"),"\n", $q->end_td,"\n", $q->end_Tr,"\n\n"; } $q->end_table; print $q->end_html; $sth->finish; $dbh->disconnect; exit 0;
      Hola, Nando ..

      Disclaimer : I'm neither a DBA nor a DBI expert but...
      It seems to me that the binding for "ORDER BY xxx", the xxx needs to be a FIELD-NAME, not a value, so, the resolution of the variable at that location should be completed before the "prepare" is executed. In other words, I don't think that can be bound to a "?" for "execute-time" binding.

      Other style comments : (You did not ask, but this is a perl website, and sometimes we provide unsolicited advice that you are free to reject):

      In your table row filling code, you add "\n", which will be ignored in HTML. I would suggest:

      print $q->start_table({-border=>'1', -align=>'center',-cellpadding=>'6'} +),"\n\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 ({ td({-align=>'left'},decode("utf-8",$_) )."\n" } @data), $q->end_Tr,"\n"; } print $q->end_table,"\n";
      (Untested)

      Another thing that would help is if you provided the SQL schema, including just a few records of your Sqlite db, so that we could recreate your problem.

      Also, passing along something I just learned:
      the parameter for 'julianday' is TEXT, not a 'DATE' type. Hopefully, your sqlite database has the fields defined as TEXT fields, and the bind parameter is also a date in text form.
      In my tests, the dash (-) separator works, but slash (/) does not.

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

        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:

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (8)
As of 2014-08-20 05:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (105 votes), past polls