Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

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

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

Replies are listed 'Best First'.
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?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://930509]
[erix]: one would hope evolutionists haven't any prayers anyway
[marto]: obviously someone could be religious, but not creationist
[erix]: "Nothing in Intelligent Design makes sense except in the light of Creationism" <-- I made that one up myself (free after Dobzhansky )
[erix]: yes. Deplorable marto, deplorable.
[marto]: the situation seemed similar to this one, majority of the contributrions are nonsense, doesn't address any questions ...
[marto]: meh, I've been called worse :P
[marto]: deplorable is actually not bad for this point in the day :P

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (12)
As of 2017-07-28 15:45 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (431 votes). Check out past polls.