Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^5: DBI, and date type SQLite

by NetWallah (Abbot)
on Oct 09, 2011 at 23:47 UTC ( #930509=note: print w/ replies, xml ) Need Help??


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

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."


Comment on Re^5: DBI, and date type SQLite
Download Code
Re^6: DBI, and date type SQLite
by NetWallah (Abbot) on Oct 10, 2011 at 06:08 UTC
    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."

      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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2014-12-29 04:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (184 votes), past polls